MySQL備份與恢復(fù)之保證數(shù)據(jù)一致性(5)
在上一篇文章中我們提到熱拷貝(MySQL備份與恢復(fù)之熱拷貝),熱拷貝也就是在MySQL或者其他數(shù)據(jù)庫服務(wù)在運(yùn)行的情況下使用mysqlhotcopy命令進(jìn)行備份。這篇文章我們講解怎樣保證數(shù)據(jù)一致性?,F(xiàn)在假設(shè)有這樣一種情況,我們總是在凌晨對數(shù)據(jù)庫進(jìn)行備份,假設(shè)在凌晨之后發(fā)生數(shù)據(jù)庫異常,并且導(dǎo)致數(shù)據(jù)丟失。這樣凌晨之前的數(shù)據(jù)我們已經(jīng)做了備份,但是凌晨到發(fā)生異常這段時間的數(shù)據(jù)就會丟失(沒有binlog的情況下)。好在InnoDB存儲引擎支持事務(wù),也支持Binlog,凌晨到發(fā)生異常這段時間的數(shù)據(jù)就可以通過日志文件進(jìn)行備份。所以,日志文件是非常重要,非常關(guān)鍵的。我們備份不僅要對數(shù)據(jù)進(jìn)行備份,如果條件允許還需要對二進(jìn)制文件進(jìn)行備份。當(dāng)然備份好數(shù)據(jù)之后,可以清空二進(jìn)制文件,但如果為了長遠(yuǎn)考慮,比如恢復(fù)出來的數(shù)據(jù)并不是我們想要的,我們就需要備份二進(jìn)制文件了。還有一點(diǎn)切記,恢復(fù)數(shù)據(jù)需要轉(zhuǎn)到測試數(shù)據(jù)庫中做,不要在生產(chǎn)環(huá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ù)丟失,刪除庫
[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ù)的時間點(diǎn)(或者是Commit之后的那個時間點(diǎn))是發(fā)生事故的那個點(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*/;
以上就是本文的全部內(nèi)容,不知道大家是否有所收獲,聯(lián)系前幾篇的內(nèi)容進(jìn)行理解,學(xué)習(xí)效果會更好哦
相關(guān)文章
MySQL級聯(lián)復(fù)制下如何進(jìn)行大表的字段擴(kuò)容
這篇文章主要介紹了MySQL級聯(lián)復(fù)制下進(jìn)行大表的字段擴(kuò)容,庫表信息環(huán)境是Mysql 8.0.22,本文給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-04-04win10下mysql 8.0.12 安裝及環(huán)境變量配置教程
這篇文章主要為大家詳細(xì)介紹了MySQL8.0的安裝、配置、啟動服務(wù)和登錄及配置環(huán)境變量,具有一定的參考價值,感興趣的小伙伴們可以參考一下2019-03-03使用Rotate Master實(shí)現(xiàn)MySQL 多主復(fù)制的實(shí)現(xiàn)方法
眾所周知,MySQL只支持一對多的主從復(fù)制,而不支持多主(multi-master)復(fù)制2012-05-05Mysql查詢語句執(zhí)行過程及運(yùn)行原理分析
這篇文章主要介紹了Mysql查詢語句執(zhí)行過程及運(yùn)行原理分析,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08mysql提示Changed limits: max_open_files: 2048 max_connections:
這篇文章主要介紹了mysql提示Changed limits: max_open_files: 2048 max_connections: 1910 table_cache: 64的解決,需要的朋友可以參考下2014-05-05MySQL 5.5.x my.cnf參數(shù)配置優(yōu)化詳解
今天正好看到一篇有關(guān)my.cnf優(yōu)化的總結(jié),雖然還沒經(jīng)過我自己的實(shí)踐檢驗(yàn),但從文章內(nèi)容來說已經(jīng)寫的很詳細(xì)了(當(dāng)然,事實(shí)上下面這篇文章很多地方只是翻譯了my.cnf原始配置文件的說明,呵呵),所以特地轉(zhuǎn)載收藏一下2015-08-08