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

这篇文章主要介绍了MySQL备份与恢复之保证数据一致性，感兴趣的小伙伴们可以参考一下

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

示意图

保证数据一致性模拟

第一步，验证数据

1. [root@serv01 databackup]# rm -rf * 
2. [root@serv01 databackup]# ls 
3.  
4. mysql> use larrydb; 
5. Database changed 
6. mysql> show tables; 
7. +-------------------+ 
8. | Tables_in_larrydb | 
9. +-------------------+ 
10. | class | 
11. | stu | 
12. +-------------------+ 
13. 2 rows in set (0.00 sec) 
14.  
15. mysql> select * from class; 
16. +------+--------+ 
17. | cid | cname | 
18. +------+--------+ 
19. | 1 | linux | 
20. | 2 | oracle | 
21. +------+--------+ 
22. 2 rows in set (0.00 sec) 
23.  
24. mysql> select * from stu; 
25. +------+---------+------+ 
26. | sid | sname | cid | 
27. +------+---------+------+ 
28. | 1 | larry01 | 1 | 
29. | 2 | larry02 | 2 | 
30. +------+---------+------+ 
31. 2 rows in set (0.00 sec) 

第二步，备份数据

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

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

1. mysql> show binary logs; 
2. +------------------+-----------+ 
3. | Log_name | File_size | 
4. +------------------+-----------+ 
5. | mysql-bin.000001 | 27320 | 
6. | mysql-bin.000002 | 1035309 | 
7. | mysql-bin.000003 | 1010 | 
8. | mysql-bin.000004 | 22809 | 
9. | mysql-bin.000005 | 9860 | 
10. | mysql-bin.000006 | 5659 | 
11. | mysql-bin.000007 | 126 | 
12. | mysql-bin.000008 | 10087 | 
13. | mysql-bin.000009 | 8293 | 
14. | mysql-bin.000010 | 476 | 
15. | mysql-bin.000011 | 218 | 
16. | mysql-bin.000012 | 126 | 
17. | mysql-bin.000013 | 1113 | 
18. | mysql-bin.000014 | 1171 | 
19. | mysql-bin.000015 | 126 | 
20. | mysql-bin.000016 | 107 | 
21. | mysql-bin.000017 | 107 | 
22. | mysql-bin.000018 | 13085 | 
23. +------------------+-----------+ 
24. 18 rows in set (0.00 sec) 
25.  
26. mysql> reset master; 
27. Query OK, 0 rows affected (0.01 sec) 
28.  
29. mysql> show binary logs; 
30. +------------------+-----------+ 
31. | Log_name | File_size | 
32. +------------------+-----------+ 
33. | mysql-bin.000001 | 107 | 
34. +------------------+-----------+ 
35. 1 row in set (0.00 sec) 

第四步，更新数据

1. mysql> insert into class values(3,'Devel'); 
2. Query OK, 1 row affected (0.01 sec) 
3.  
4. mysql> update class set cname="dab" where cid=2; 
5. Query OK, 1 row affected (0.01 sec) 
6. Rows matched: 1 Changed: 1 Warnings: 0 
7.  
8. mysql> select * from class; 
9. +------+-------+ 
10. | cid | cname | 
11. +------+-------+ 
12. | 1 | linux | 
13. | 2 | dab | 
14. | 3 | Devel | 
15. +------+-------+ 
16. 3 rows in set (0.00 sec) 
17.  
18. mysql> select * from stu; 
19. +------+---------+------+ 
20. | sid | sname | cid | 
21. +------+---------+------+ 
22. | 1 | larry01 | 1 | 
23. | 2 | larry02 | 2 | 
24. +------+---------+------+ 
25. 2 rows in set (0.00 sec) 
26.  
27. mysql> delete from stu where cid=2; 
28. Query OK, 1 row affected (0.00 sec) 
29.  
30. mysql> update stu set sname="larry007" where sid=1; 
31. Query OK, 1 row affected (0.00 sec) 
32. Rows matched: 1 Changed: 1 Warnings: 0 
33.  
34. mysql> select * from stu; 
35. +------+----------+------+ 
36. | sid | sname | cid | 
37. +------+----------+------+ 
38. | 1 | larry007 | 1 | 
39. +------+----------+------+ 
40. 1 row in set (0.00 sec) 
41.  
42. [root@serv01 data]# date 
43. Tue Sep 10 19:38:24 CST 2013 

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

1. [root@serv01 data]# rm -rf /usr/local/mysql/data/larrydb/ 
2.  
3. mysql> show databases; 
4. +--------------------+ 
5. | Database | 
6. +--------------------+ 
7. | information_schema | 
8. | game | 
9. | hello | 
10. | mnt | 
11. | mysql | 
12. | performance_schema | 
13. | test | 
14. +--------------------+ 
15. 7 rows in set (0.00 sec) 
16.  
17. [root@serv01 data]# cd /usr/local/mysql/data/ 
18. [root@serv01 data]# ll 
19. total 28736 
20. drwx------. 2 mysql mysql 4096 Sep 10 19:14 game 
21. drwx------. 2 mysql mysql 4096 Sep 7 00:43 hello 
22. -rw-rw----. 1 mysql mysql 18874368 Sep 10 19:36 ibdata1 
23. -rw-rw----. 1 mysql mysql 5242880 Sep 10 19:36 ib_logfile0 
24. -rw-rw----. 1 mysql mysql 5242880 Sep 4 23:39 ib_logfile1 
25. drwxr-xr-x. 2 mysql mysql 4096 Sep 10 18:35 mnt 
26. drwxr-xr-x. 2 mysql mysql 4096 Sep 4 23:39 mysql 
27. -rw-rw----. 1 mysql mysql 998 Sep 10 19:37 mysql-bin.000001 
28. -rw-rw----. 1 mysql mysql 19 Sep 10 19:34 mysql-bin.index 
29. drwx------. 2 mysql mysql 4096 Sep 4 23:39 performance_schema 
30. -rw-r-----. 1 mysql root 26371 Sep 10 19:30 serv01.host.com.err 
31. -rw-rw----. 1 mysql mysql 5 Sep 10 18:36 serv01.host.com.pid 
32. drwx------. 2 mysql mysql 4096 Sep 7 00:13 test 
33.  
34. #可以使用mysqlbinlog命令查看日志文件 
35. [root@serv01 data]# mysqlbinlog mysql-bin.000001 
36.  
37. mysql> show databases; 
38. +--------------------+ 
39. | Database | 
40. +--------------------+ 
41. | information_schema | 
42. | game | 
43. | hello | 
44. | mnt | 
45. | mysql | 
46. | performance_schema | 
47. | test | 
48. +--------------------+ 
49. 7 rows in set (0.00 sec) 
50.  
51. mysql> drop database larrydb; 
52. Query OK, 0 rows affected (0.01 sec) 

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

1. [root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql  
2. ERROR 1050 (42S01) at line 33: Table '`larrydb`.`class`' already exists 
3. [root@serv01 databackup]# mysql -uroot -p123456 < larrydb.sql  
4.  
5. mysql> use larrydb; 
6. Database changed 
7. mysql> select * from stu; 
8. +------+---------+------+ 
9. | sid | sname | cid | 
10. +------+---------+------+ 
11. | 1 | larry01 | 1 | 
12. | 2 | larry02 | 2 | 
13. +------+---------+------+ 
14. 2 rows in set (0.00 sec) 
15.  
16. mysql> select * from class; 
17. +------+--------+ 
18. | cid | cname | 
19. +------+--------+ 
20. | 1 | linux | 
21. | 2 | oracle | 
22. +------+--------+ 
23. 2 rows in set (0.00 sec) 

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

1. [root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456 
2.  
3. mysql> select * from stu; 
4. +------+---------+------+ 
5. | sid | sname | cid | 
6. +------+---------+------+ 
7. | 1 | larry01 | 1 | 
8. +------+---------+------+ 
9. 1 row in set (0.00 sec) 
10.  
11. mysql> select * from class; 
12. +------+-------+ 
13. | cid | cname | 
14. +------+-------+ 
15. | 1 | linux | 
16. | 2 | dab | 
17. | 3 | Devel | 
18. +------+-------+ 
19. 3 rows in set (0.00 sec) 
20.  
21. #规律：恢复的时间点（或者是Commit之后的那个时间点）是发生事故的那个点再加上一秒。 
22. [root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456 
23.  
24. mysql> select * from stu; 
25. +------+----------+------+ 
26. | sid | sname | cid | 
27. +------+----------+------+ 
28. | 1 | larry007 | 1 | 
29. +------+----------+------+ 
30. 1 row in set (0.00 sec) 
31.  
32. mysql> select * from class; 
33. +------+-------+ 
34. | cid | cname | 
35. +------+-------+ 
36. | 1 | linux | 
37. | 2 | dab | 
38. | 3 | Devel | 
39. | 3 | Devel | 
40. +------+-------+ 
41. 4 rows in set (0.00 sec) 
42.  
43. [root@serv01 data]# mysqlbinlog mysql-bin.000001  
44. # at 7131 
45. #130910 19:37:45 server id 1 end_log_pos 7240 Query thread_id=20 exec_time=996 error_code=0 
46. SET TIMESTAMP=1378813065/*!*/; 
47. update stu set sname="larry007" where sid=1 
48. /*!*/; 
49. # at 7240 
50. #130910 19:37:45 server id 1 end_log_pos 7312 Query thread_id=20 exec_time=996 error_code=0 
51. SET TIMESTAMP=1378813065/*!*/; 
52. COMMIT 
53. /*!*/; 
54. DELIMITER ; 
55. # End of log file 
56. ROLLBACK /* added by mysqlbinlog */; 
57. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 

以上就是本文的全部内容，不知道大家是否有所收获，联系前几篇的内容进行理解，学习效果会更好哦