MySQL備份與恢復之保證數據一致性

一 數據一致性

在上一篇文章中我們提到熱拷貝（MySQL備份與恢復之熱拷貝），熱拷貝也就是在MySQL或者其他數據庫服務在運行的情況下使用mysqlhotcopy命令進行備份。這篇文章我們講解怎樣保證數據一致性。現在假設有這樣一種情況，我們總是在凌晨對數據庫進行備份，假設在凌晨之後發生數據庫異常，並且導致數據丟失。這樣凌晨之前的數據我們已經做了備份，但是凌晨到發生異常這段時間的數據就會丟失（沒有binlog的情況下）。好在InnoDB存儲引擎支持事務，也支持Binlog，凌晨到發生異常這段時間的數據就可以通過日志文件進行備份。所以，日志文件是非常重要，非常關鍵的。我們備份不僅要對數據進行備份，如果條件允許還需要對二進制文件進行備份。當然備份好數據之後，可以清空二進制文件，但如果為了長遠考慮，比如恢復出來的數據並不是我們想要的，我們就需要備份二進制文件了。還有一點切記，恢復數據需要轉到測試數據庫中做，不要在生產環境中做。待測試庫中測試沒有問題，再在生產環境中做。

二 示意圖

MySQL備份與恢復之保證數據一致性示意圖

三 保證數據一致性模擬

第一步，驗證數據。

rm -rf *
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)

第二步，備份數據。

mysqldump -uroot -p123456 --database larrydb > larrydb.sql
ll 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)

記錄當前時間。

date
Tue Sep 10 19:38:24 CST 2013

第五步，模擬數據丟失，刪除庫。

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)

cd /usr/local/mysql/data/

# 可以使用mysqlbinlog命令查看日志文件
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)

第六步，導入更新之前的數據。

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)

第七步，根據日志恢復數據。

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之後的那個時間點）是發生事故的那個點再加上一秒。

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)

查看日志文件內容。

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*/;

–EOF–