MySQL备份与恢复之保证数据一致性

51 阅读 0 评论 34 点赞

我是靠谱客的博主清爽铃铛，最近开发中收集的这篇文章主要介绍MySQL备份与恢复之保证数据一致性，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一数据一致性

在上一篇文章中我们提到热拷贝（MySQL备份与恢复之热拷贝），热拷贝也就是在MySQL或者其他数据库服务在运行的情况下使用mysqlhotcopy命令进行备份。这篇文章我们讲解怎样保证数据一致性。现在假设有这样一种情况，我们总是在凌晨对数据库进行备份，假设在凌晨之后发生数据库异常，并且导致数据丢失。这样凌晨之前的数据我们已经做了备份，但是凌晨到发生异常这段时间的数据就会丢失（没有binlog的情况下）。好在InnoDB存储引擎支持事务，也支持Binlog，凌晨到发生异常这段时间的数据就可以通过日志文件进行备份。所以，日志文件是非常重要，非常关键的。我们备份不仅要对数据进行备份，如果条件允许还需要对二进制文件进行备份。当然备份好数据之后，可以清空二进制文件，但如果为了长远考虑，比如恢复出来的数据并不是我们想要的，我们就需要备份二进制文件了。还有一点切记，恢复数据需要转到测试数据库中做，不要在生产环境中做。待测试库中测试没有问题，再在生产环境中做。

二示意图

三保证数据一致性模拟

第一步，验证数据

[root@serv01 databackup]# rm -rf *
[root@serv01 databackup]# ls
mysql> use larrydb;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_larrydb |
+-------------------+
| class
|
| stu
|
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| cid
| cname
|
+------+--------+
|
1 | linux
|
|
2 | oracle |
+------+--------+
2 rows in set (0.00 sec)
mysql> select * from stu;
+------+---------+------+
| sid
| sname
| cid
|
+------+---------+------+
|
1 | larry01 |
1 |
|
2 | larry02 |
2 |
+------+---------+------+
2 rows in set (0.00 sec)

第二步，备份数据

[root@serv01 databackup]# mysqldump -uroot -p123456 --database larrydb > larrydb.sql
[root@serv01 databackup]# ll larrydb.sql
-rw-r--r--. 1 root root 2613 Sep 10 19:34 larrydb.sql

第三步，清空日志，因为已经做了备份，所以不需要以前的日志

mysql> show binary logs;
+------------------+-----------+
| Log_name
| File_size |
+------------------+-----------+
| mysql-bin.000001 |
27320 |
| mysql-bin.000002 |
1035309 |
| mysql-bin.000003 |
1010 |
| mysql-bin.000004 |
22809 |
| mysql-bin.000005 |
9860 |
| mysql-bin.000006 |
5659 |
| mysql-bin.000007 |
126 |
| mysql-bin.000008 |
10087 |
| mysql-bin.000009 |
8293 |
| mysql-bin.000010 |
476 |
| mysql-bin.000011 |
218 |
| mysql-bin.000012 |
126 |
| mysql-bin.000013 |
1113 |
| mysql-bin.000014 |
1171 |
| mysql-bin.000015 |
126 |
| mysql-bin.000016 |
107 |
| mysql-bin.000017 |
107 |
| mysql-bin.000018 |
13085 |
+------------------+-----------+
18 rows in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name
| File_size |
+------------------+-----------+
| mysql-bin.000001 |
107 |
+------------------+-----------+
1 row in set (0.00 sec)

第四步，更新数据

mysql> insert into class values(3,'Devel');
Query OK, 1 row affected (0.01 sec)
mysql> update class set cname="dab" where cid=2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> select * from class;
+------+-------+
| cid
| cname |
+------+-------+
|
1 | linux |
|
2 | dab
|
|
3 | Devel |
+------+-------+
3 rows in set (0.00 sec)
mysql> select * from stu;
+------+---------+------+
| sid
| sname
| cid
|
+------+---------+------+
|
1 | larry01 |
1 |
|
2 | larry02 |
2 |
+------+---------+------+
2 rows in set (0.00 sec)
mysql> delete from stu where cid=2;
Query OK, 1 row affected (0.00 sec)
mysql> update stu set sname="larry007" where sid=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1
Changed: 1
Warnings: 0
mysql> select * from stu;
+------+----------+------+
| sid
| sname
| cid
|
+------+----------+------+
|
1 | larry007 |
1 |
+------+----------+------+
1 row in set (0.00 sec)
[root@serv01 data]# date
Tue Sep 10 19:38:24 CST 2013

第五步，模拟数据丢失，删除库

[root@serv01 data]# rm -rf /usr/local/mysql/data/larrydb/
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| game
|
| hello
|
| mnt
|
| mysql
|
| performance_schema |
| test
|
+--------------------+
7 rows in set (0.00 sec)
[root@serv01 data]# cd /usr/local/mysql/data/
[root@serv01 data]# ll
total 28736
drwx------. 2 mysql mysql
4096 Sep 10 19:14 game
drwx------. 2 mysql mysql
4096 Sep
7 00:43 hello
-rw-rw----. 1 mysql mysql 18874368 Sep 10 19:36 ibdata1
-rw-rw----. 1 mysql mysql
5242880 Sep 10 19:36 ib_logfile0
-rw-rw----. 1 mysql mysql
5242880 Sep
4 23:39 ib_logfile1
drwxr-xr-x. 2 mysql mysql
4096 Sep 10 18:35 mnt
drwxr-xr-x. 2 mysql mysql
4096 Sep
4 23:39 mysql
-rw-rw----. 1 mysql mysql
998 Sep 10 19:37 mysql-bin.000001
-rw-rw----. 1 mysql mysql
19 Sep 10 19:34 mysql-bin.index
drwx------. 2 mysql mysql
4096 Sep
4 23:39 performance_schema
-rw-r-----. 1 mysql root
26371 Sep 10 19:30 serv01.host.com.err
-rw-rw----. 1 mysql mysql
5 Sep 10 18:36 serv01.host.com.pid
drwx------. 2 mysql mysql
4096 Sep
7 00:13 test
#可以使用mysqlbinlog命令查看日志文件
[root@serv01 data]# mysqlbinlog mysql-bin.000001
mysql> show databases;
+--------------------+
| Database
|
+--------------------+
| information_schema |
| game
|
| hello
|
| mnt
|
| mysql
|
| performance_schema |
| test
|
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database larrydb;
Query OK, 0 rows affected (0.01 sec)

第六步，导入更新之前的数据

[root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql
ERROR 1050 (42S01) at line 33: Table '`larrydb`.`class`' already exists
[root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql
mysql> use larrydb;
Database changed
mysql> select * from stu;
+------+---------+------+
| sid
| sname
| cid
|
+------+---------+------+
|
1 | larry01 |
1 |
|
2 | larry02 |
2 |
+------+---------+------+
2 rows in set (0.00 sec)
mysql> select * from class;
+------+--------+
| cid
| cname
|
+------+--------+
|
1 | linux
|
|
2 | oracle |
+------+--------+
2 rows in set (0.00 sec)

第七步，根据日志恢复数据

[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+---------+------+
| sid
| sname
| cid
|
+------+---------+------+
|
1 | larry01 |
1 |
+------+---------+------+
1 row in set (0.00 sec)
mysql> select * from class;
+------+-------+
| cid
| cname |
+------+-------+
|
1 | linux |
|
2 | dab
|
|
3 | Devel |
+------+-------+
3 rows in set (0.00 sec)
#规律：恢复的时间点（或者是Commit之后的那个时间点）是发生事故的那个点再加上一秒。
[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456
mysql> select * from stu;
+------+----------+------+
| sid
| sname
| cid
|
+------+----------+------+
|
1 | larry007 |
1 |
+------+----------+------+
1 row in set (0.00 sec)
mysql> select * from class;
+------+-------+
| cid
| cname |
+------+-------+
|
1 | linux |
|
2 | dab
|
|
3 | Devel |
|
3 | Devel |
+------+-------+
4 rows in set (0.00 sec)
[root@serv01 data]# mysqlbinlog mysql-bin.000001
# at 7131
#130910 19:37:45 server id 1
end_log_pos 7240
Query thread_id=20
exec_time=996 error_code=0
SET TIMESTAMP=1378813065/*!*/;
update stu set sname="larry007" where sid=1
/*!*/;
# at 7240
#130910 19:37:45 server id 1
end_log_pos 7312
Query thread_id=20
exec_time=996 error_code=0
SET TIMESTAMP=1378813065/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;


我的邮箱：wgbno27@163.com
  新浪微博：@jutdb

微信公众平台：JustOracle（微信号：justoracle）
数据库技术交流群：336882565（加群时验证 From CSDN XXX）
All is well
2013年11月24日
By Larry Wen


	@Wentasy 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]