我们就需要备份二进制文件了

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

保证数据一致性模拟第一步,验证数据

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

第二步,备份数据

[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 mysql> reset master;Query OK, 0 rows affected mysql> show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+| mysql-bin.000001 | 107 |+------------------+-----------+1 row in set  

第四步,更新数据

mysql> insert into class values;Query OK, 1 row affected mysql> update class set cname="dab" where cid=2;Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from class;+------+-------+| cid | cname |+------+-------+| 1 | linux || 2 | dab || 3 | Devel |+------+-------+3 rows in set mysql> select * from stu;+------+---------+------+| sid | sname | cid |+------+---------+------+| 1 | larry01 | 1 || 2 | larry02 | 2 |+------+---------+------+2 rows in set mysql> delete from stu where cid=2;Query OK, 1 row affected mysql> update stu set sname="larry007" where sid=1;Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from stu;+------+----------+------+| sid | sname | cid |+------+----------+------+| 1 | larry007 | 1 |+------+----------+------+1 row in set [root@serv01 data]# dateTue 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 [root@serv01 data]# cd /usr/local/mysql/data/[root@serv01 data]# lltotal 28736drwx------. 2 mysql mysql 4096 Sep 10 19:14 gamedrwx------. 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_logfile1drwxr-xr-x. 2 mysql mysql 4096 Sep 10 18:35 mntdrwxr-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.indexdrwx------. 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.piddrwx------. 2 mysql mysql 4096 Sep 7 00:13 test#可以使用mysqlbinlog命令查看日志文件[root@serv01 data]# mysqlbinlog mysql-bin.000001mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || game || hello || mnt || mysql || performance_schema || test |+--------------------+7 rows in set mysql> drop database larrydb;Query OK, 0 rows affected 

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

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

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

[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:45" mysql-bin.000001 | mysql -uroot -p123456mysql> select * from stu;+------+---------+------+| sid | sname | cid |+------+---------+------+| 1 | larry01 | 1 |+------+---------+------+1 row in set mysql> select * from class;+------+-------+| cid | cname |+------+-------+| 1 | linux || 2 | dab || 3 | Devel |+------+-------+3 rows in set #规律:恢复的时间点是发生事故的那个点再加上一秒。[root@serv01 data]# mysqlbinlog --stop-datetime "2013-09-10 19:37:46" mysql-bin.000001 | mysql -uroot -p123456mysql> select * from stu;+------+----------+------+| sid | sname | cid |+------+----------+------+| 1 | larry007 | 1 |+------+----------+------+1 row in set mysql> select * from class;+------+-------+| cid | cname |+------+-------+| 1 | linux || 2 | dab || 3 | Devel || 3 | Devel |+------+-------+4 rows in set [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=0SET 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=0SET TIMESTAMP=1378813065/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

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

本文由快3平台发布于快3正规平台数据库,转载请注明出处:我们就需要备份二进制文件了

相关阅读