快捷導(dǎo)航

MySQL備份與恢復(fù)之保證數(shù)據(jù)一致性(5)

更新時(shí)間：2015年08月14日 11:24:18 投稿：lijiao

這篇文章主要介紹了MySQL備份與恢復(fù)之保證數(shù)據(jù)一致性，感興趣的小伙伴們可以參考一下

在上一篇文章中我們提到熱拷貝（MySQL備份與恢復(fù)之熱拷貝），熱拷貝也就是在MySQL或者其他數(shù)據(jù)庫(kù)服務(wù)在運(yùn)行的情況下使用mysqlhotcopy命令進(jìn)行備份。這篇文章我們講解怎樣保證數(shù)據(jù)一致性?，F(xiàn)在假設(shè)有這樣一種情況，我們總是在凌晨對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份，假設(shè)在凌晨之后發(fā)生數(shù)據(jù)庫(kù)異常，并且導(dǎo)致數(shù)據(jù)丟失。這樣凌晨之前的數(shù)據(jù)我們已經(jīng)做了備份，但是凌晨到發(fā)生異常這段時(shí)間的數(shù)據(jù)就會(huì)丟失（沒(méi)有binlog的情況下）。好在InnoDB存儲(chǔ)引擎支持事務(wù)，也支持Binlog，凌晨到發(fā)生異常這段時(shí)間的數(shù)據(jù)就可以通過(guò)日志文件進(jìn)行備份。所以，日志文件是非常重要，非常關(guān)鍵的。我們備份不僅要對(duì)數(shù)據(jù)進(jìn)行備份，如果條件允許還需要對(duì)二進(jìn)制文件進(jìn)行備份。當(dāng)然備份好數(shù)據(jù)之后，可以清空二進(jìn)制文件，但如果為了長(zhǎng)遠(yuǎn)考慮，比如恢復(fù)出來(lái)的數(shù)據(jù)并不是我們想要的，我們就需要備份二進(jìn)制文件了。還有一點(diǎn)切記，恢復(fù)數(shù)據(jù)需要轉(zhuǎn)到測(cè)試數(shù)據(jù)庫(kù)中做，不要在生產(chǎn)環(huán)境中做。待測(cè)試庫(kù)中測(cè)試沒(méi)有問(wèn)題，再在生產(chǎn)環(huán)境中做。
示意圖

保證數(shù)據(jù)一致性模擬
第一步，驗(yàn)證數(shù)據(jù)

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

第二步，備份數(shù)據(jù)

[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

第三步，清空日志，因?yàn)橐呀?jīng)做了備份，所以不需要以前的日志

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)

第四步，更新數(shù)據(jù)

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

第五步，模擬數(shù)據(jù)丟失，刪除庫(kù)

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

第六步，導(dǎo)入更新之前的數(shù)據(jù)

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

第七步，根據(jù)日志恢復(fù)數(shù)據(jù)

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

#規(guī)律：恢復(fù)的時(shí)間點(diǎn)（或者是Commit之后的那個(gè)時(shí)間點(diǎn)）是發(fā)生事故的那個(gè)點(diǎn)再加上一秒。
[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*/;

以上就是本文的全部?jī)?nèi)容，不知道大家是否有所收獲，聯(lián)系前幾篇的內(nèi)容進(jìn)行理解，學(xué)習(xí)效果會(huì)更好哦

您可能感興趣的文章: