MySQL备份与恢复之保证数据一致性(5)
网络编程 2021-07-05 15:28www.168986.cn编程入门
这篇文章主要介绍了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 | ame | +------+--------+ | 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 ame="dab" where cid=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select from class; +------+-------+ | cid | ame | +------+-------+ | 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..err -rw-rw----. 1 mysql mysql 5 Sep 10 18:36 serv01.host..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 | ame | +------+--------+ | 1 | linux | | 2 | oracle | +------+--------+ 2 rows in set (0.00 sec)
第七步,根据日志恢复数据
[root@serv01 data]# mysqlbinlog --s-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 | ame | +------+-------+ | 1 | linux | | 2 | dab | | 3 | Devel | +------+-------+ 3 rows in set (0.00 sec) #规律恢复的时间点(或者是Commit之后的那个时间点)是发生事故的那个点再加上一秒。 [root@serv01 data]# mysqlbinlog --s-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 | ame | +------+-------+ | 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/;
以上就是本文的全部内容,不知道大家是否有所收获,联系前几篇的内容进行理解,学习效果会更好哦
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南