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

Bertha 。 2022-09-18 12:47 205阅读 0赞

## 一 数据一致性 ##

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

## 二 示意图 ##

![SouthEast][]

## 三 保证数据一致性模拟 ##

第一步，验证数据

>     [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

<table style="border-bottom:#48c5db 5px double; border-left:#48c5db 5px double; padding-bottom:5px; background-color:black; padding-left:5px; border-spacing:0; width:100%; padding-right:30px; height:90px; border-top:#48c5db 5px double; border-right:#48c5db 5px double; padding-top:5px"> 
 <tbody> 
  <tr> 
   <td><img alt="katoon" src="https://img-my.csdn.net/uploads/201301/06/1357456262_5409.jpg" style="height:80px"></td> 
   <td style="text-align:right"><a title="新浪微博求粉 ^_^" href="http://weibo.com/wentasy" rel="nofollow"><img title="新浪微博求粉 ^_^" alt="Sina" src="https://img-my.csdn.net/uploads/201210/26/1351182273_5476.png.thumb.jpg" style="height:40px"></a>&nbsp;<a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow"><img title="我的CSDN博客——HelloWorld" alt="CSDN" src="https://img-my.csdn.net/uploads/201210/26/1351182268_8254.png.thumb.jpg" style="height:40px"></a></td> 
  </tr> 
  <tr> 
   <td style="text-align:right; font-family:microsoft yahei; color:white; font-size:12px"> <a title="我的CSDN博客——HelloWorld" href="http://blog.csdn.net/Wentasy" rel="nofollow">@Wentasy</a> 博文仅供参考，欢迎大家来访。如有错误之处，希望批评指正。原创博文如需转载请注明出处，谢谢 :) [CSDN博客]</td> 
  </tr> 
 </tbody> 
</table>

[MySQL]: http://blog.csdn.net/justdb/article/details/15026833
[SouthEast]: /images/20220828/4faa5f7f812140d59b64deeb5f77a26f.png