您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记

马哥linux运维学习笔记-MySQL系列之十四——MySQL备份和恢复

发布时间：2019-03-19 21:42:44编辑：雪饮阅读（）

假定备份策略为：每周完全备份+每日增量备份

下面模拟下该备份策略及恢复的演练。

模拟一次完全备份

[root@localhost ~]# mysqldump -u root -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/alldatabases.sql

Enter password:

--master-data的值为2时则会在导出的sql文件中将导出前mysql所用的二进制日志文件及该文件中的position以注释的形式存在

--flush-logs用于导出前将二进制日志滚动一次

--all-databases表示要导出整个mysql中的所有库

--lock-all-tables用于在导出前将所有表都以加上读锁，即都只能读

将本次备份前的所有二进制日志文件删除以减少磁盘占用

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000005 |   8109669 |

| mysql-bin.000006 |      2114 |

| mysql-bin.000007 |      2765 |

| mysql-bin.000008 |      1756 |

| mysql-bin.000009 |       693 |

| mysql-bin.000010 |       150 |

| mysql-bin.000011 |       126 |

| mysql-bin.000012 |   3162624 |

| mysql-bin.000013 |       150 |

| mysql-bin.000014 |       150 |

| mysql-bin.000015 |       107 |

+------------------+-----------+

11 rows in set (0.00 sec)

 

mysql> purge binary logs to 'mysql-bin.000015';

Query OK, 0 rows affected (0.01 sec)

 

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000015 |       107 |

+------------------+-----------+

1 row in set (0.00 sec)

这里尽量别删除，而搬迁到别处，说不定有时候有救急之用

模拟完整备份后的1天内mysql数据库里所做的操作

mysql> delete from s_user where id>100;

mysql> commit;

模拟完整备份后1天内数据库所做的操作的一次增量备份

mysql> flush logs;

Query OK, 0 rows affected (0.01 sec)

 

mysql> show binary logs;

+------------------+-----------+

| Log_name         | File_size |

+------------------+-----------+

| mysql-bin.000015 |       525 |

| mysql-bin.000016 |       107 |

+------------------+-----------+

2 rows in set (0.00 sec)

[root@localhost ~]# mysqlbinlog /mydata/data/mysql-bin.000015 > /root/mon.sql

模拟第一次增量备份后数据库又操作了一次

mysql> insert into test(id) values(220807);

Query OK, 1 row affected, 1 warning (0.00 sec)

模拟备份第一次增量备份后数据库又操作了一次所产生的即时二进制文件(即时点文件备份)

[root@localhost ~]# cp /mydata/data/mysql-bin.000016 /root/

模拟一次数据库故障

[root@localhost ~]# rm -rf /mydata/data/*

模拟修复

此时停止mysql服务器都是困难的

[root@localhost ~]# service mysqld stop

MySQL server PID file could not be found!                  [FAILED]

只能通过killall才能杀死mysqld进程

[root@localhost ~]# killall mysqld

初始化数据库

[root@localhost ~]# cd /usr/local/mysql

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/

Installing MySQL system tables...

OK

Filling help tables...

OK

 

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

 

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

 

./bin/mysqladmin -u root password 'new-password'

./bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

 

Alternatively you can run:

./bin/mysql_secure_installation

 

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

 

See the manual for more instructions.

 

You can start the MySQL daemon with:

cd . ; ./bin/mysqld_safe &

 

You can test the MySQL daemon with mysql-test-run.pl

cd ./mysql-test ; perl mysql-test-run.pl

 

Please report any problems with the ./bin/mysqlbug script!

启动mysql服务

[root@localhost ~]# service mysqld start

Starting MySQL.                                            [  OK  ]

恢复刚才的完整备份

[root@localhost ~]# mysql -u root -p < /root/alldatabases.sql

Enter password:

恢复刚才的增量备份

[root@localhost ~]# mysql -u root -p < /root/mon.sql

Enter password:

即时点恢复(利用上面增量备份后所产生的即时点文件进行即时点恢复)

[root@localhost ~]# mysqlbinlog /root/mysql-bin.000016 > /root/temp.sql

[root@localhost ~]# mysql -u root -p < /root/temp.sql

Enter password: