MySQL數(shù)據(jù)備份與恢復(fù)

    一、簡介

    數(shù)據(jù)在當(dāng)今的互聯(lián)網(wǎng)行業(yè)中非常重要，掌握了數(shù)據(jù)能夠從中發(fā)現(xiàn)價(jià)值，所以數(shù)據(jù)對(duì)于公司來書再重要不過了，當(dāng)我們面臨數(shù)據(jù)的丟失時(shí)，比如數(shù)據(jù)意外刪除、自然災(zāi)害等，然而數(shù)據(jù)的備份和恢復(fù)就顯得尤為重要。   

    二、MySQL/MariaDB數(shù)據(jù)備份與恢復(fù)

    常見的備份類型：

    根據(jù)備份時(shí)數(shù)據(jù)庫是否在線：

    冷備：cold backup,服務(wù)器離線，線上業(yè)務(wù)終止，無法執(zhí)行讀寫操作，但是此方法備份數(shù)據(jù)最為靠譜，丟數(shù)據(jù)少      熱備：hot backup,服務(wù)器在線，線上業(yè)務(wù)正常進(jìn)行，讀寫操作都可進(jìn)行，此方法備份要求復(fù)雜 

    溫備：warn backup,服務(wù)器在線，但是對(duì)數(shù)據(jù)庫施加全局鎖，只讀操作正常進(jìn)行，不可寫 

    根據(jù)備份時(shí)的接口：

    物理備份：physical backup，直接從數(shù)據(jù)庫的數(shù)據(jù)目錄進(jìn)行cp復(fù)制歸檔的方式

    邏輯備份：logical backup，把數(shù)據(jù)從庫中提取出來保存為文本文件，主要用到的工具是mysqldump 

    注意：在數(shù)據(jù)量很大時(shí)使用邏輯備份很實(shí)用，當(dāng)數(shù)據(jù)量小于10G時(shí)可以考慮使用mysqldump實(shí)現(xiàn)邏輯備份

    根據(jù)備份的數(shù)據(jù)集：

    完全備份：full backup,指的是備份整個(gè)庫 

    部分備份：partial backup,指的是備份數(shù)據(jù)庫中的部分?jǐn)?shù)據(jù)  

    根據(jù)備份時(shí)是否備份整個(gè) 數(shù)據(jù)還是僅僅備份變化的數(shù)據(jù)

    完全備份： full backup,備份整個(gè)數(shù)據(jù)庫

    增量備份：incremental backup,僅僅備份上一次完全備份之后所改變的數(shù)據(jù)做備份的為增量備份

    差異備份：differential backup,比如說周一做一次備份，到周二了就把周一和周二這兩天的做一次備份，到周三就把周一周二周三的做一次備份，這就叫差異備份。比較容易恢復(fù)。

    MYISAM與InnoDB區(qū)別：

    MYISAM存儲(chǔ)引擎不支持熱備，而InnoDB支持 InnoDB記錄數(shù)據(jù)時(shí)都會(huì)給數(shù)據(jù)一個(gè)序列號(hào)，所以在備份時(shí)基于MVCC(多版本并發(fā)控制)的機(jī)制自動(dòng)加快照，每啟動(dòng)一個(gè)事務(wù)都會(huì)創(chuàng)建當(dāng)前集的一個(gè)快照，而后基于MVCC的機(jī)制把每一個(gè)序列號(hào)都給它記錄一份下來，備份時(shí)只備份序列號(hào)或序列號(hào)之前的數(shù)據(jù)，往后發(fā)生的將不做備份，如果事務(wù)的隔離級(jí)別不是特別高的話，它并不會(huì)影響事務(wù)的讀寫操作，而這樣備份出來的數(shù)據(jù)一定是時(shí)間點(diǎn)一致的數(shù)據(jù)，所以要完成熱備份，通常是基于事務(wù)的存儲(chǔ)引擎才能夠完成的。           

     Innobase：提供了商業(yè)備份工具為Innobackup，可以實(shí)現(xiàn)InnoDB的熱備支持增量備份；但是對(duì)于MyISAM不支持增量備份，只能實(shí)現(xiàn)完全備份，屬于物理備份，速度比較快。
   Xtrabackup：由Percona組織提供的開源備份工具，物理備份，速度快；

    mysqldump：常用的備份工具，也是個(gè)邏輯備份工具，用于小數(shù)據(jù)備份，一般都是在10G以下的小數(shù)據(jù)進(jìn)行備份；可以使用文本進(jìn)行二次處理；相當(dāng)于MySQL的客戶端工具   

    三、數(shù)據(jù)備份與恢復(fù)實(shí)戰(zhàn)

    常見備份工具：

    mysqldump:邏輯備份工具，備份和恢復(fù)過程都比較慢 

    mysqldumper:多線程，的mysqldump，很難實(shí)現(xiàn)差異或增量備份 

    lvm-snapshot: 

    接近于熱備的工具，因?yàn)橐孪日埱笕宙i，而后創(chuàng)建快照，并在創(chuàng)建快照完成后釋放全局鎖。使用cp,tar等工具進(jìn)行物理備份；備份和恢復(fù)速度快，很難實(shí)現(xiàn)增量備份，并且請求全局需要一段時(shí)間，  

    邏輯備份工具，快于mysqldump  

    Innobase:商業(yè)備份工具，innobackup 

    xtrabackup:由percona提供的開源備份工具 

    innodb熱備，增量備份 

    myisam溫備，不支持增量  

    物理備份，速度快 

    mysqlhotcopy:幾乎冷備 

    mysqldump:僅適用于數(shù)據(jù)集較小的數(shù)據(jù)庫，數(shù)據(jù)量達(dá)到10G以上時(shí)建議不使用，備份太慢 

mysqldump:
mysqldump [options] [db_name [tbl_name ...]]    
mysqldump -uroot -hlocalhost -p testdb>/tmp/testdb.sql #備份單個(gè)表
mysql -uroot -hlocalhost -p testdb</tmp/testdb.sql #恢復(fù)時(shí)要事先創(chuàng)建數(shù)據(jù)庫
mysqldump -uroot -hlocalhost -p --databases db1 db2 db2 >/tmp/dbs.sql #使用此備份方法恢復(fù)時(shí)無需連入mysql創(chuàng)建數(shù)據(jù)庫
mysqldump -uroot -hlocalhost -p --all-databases >/tmp/all.sql #備份所有數(shù)據(jù)庫，適用于冷備
備份單庫或多庫：--databases    備份所有數(shù)據(jù)庫：--all-databases
注意：線上備份時(shí)，在實(shí)施溫備時(shí)需施加鎖對(duì)單表或多表施加鎖，讓用戶只能讀而不能寫
mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs>/tmp/testdb.sql
mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs >/tmp/testdb1.sql #僅對(duì)Innodb存儲(chǔ)引擎實(shí)現(xiàn)熱備，不支持myisam引擎
mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs --master-data=2 >/tmp/testdb2.sql
mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs --master-data=2 >/tmp/testdb3.sql
--lock-all-tables #請求鎖定所有表之后備份    --single-transaction #能夠?qū)nnodb存儲(chǔ)引擎實(shí)現(xiàn)熱備
--flush-logs #備份時(shí)滾動(dòng)日志    --master-data=[0|1|2]設(shè)置為1:保存為一個(gè)change master語句 設(shè)置為2：注釋掉的change master語句，設(shè)置為0：不記錄
數(shù)據(jù)恢復(fù)：建議關(guān)閉二進(jìn)制日志,關(guān)閉其他用戶連接：set session sql_log_bin=0

使用mysqldump實(shí)現(xiàn)備份及其使用二進(jìn)制日志做恢復(fù)：                 

使用mysqldump實(shí)現(xiàn)備份,用二進(jìn)制日志恢復(fù)數(shù)據(jù)，這里我們以當(dāng)前系統(tǒng)上的數(shù)據(jù)庫hellodb為例：

   第一步：先把hellodb這個(gè)數(shù)據(jù)庫做一次完全備份，當(dāng)數(shù)據(jù)庫的數(shù)據(jù)很大，比如說大于10G的話不建議使用mysqldump這個(gè)工具做備份，而使用物理備份更為合理：

mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --flush-logs --master-data=2 >/tmp/hellodb.sql

   第二步：在數(shù)據(jù)庫hellodb中修改或創(chuàng)建一些表或數(shù)據(jù)，使得之前備份的數(shù)據(jù)跟現(xiàn)有的數(shù)據(jù)庫中的數(shù)據(jù)存在差別，以完后后面的通過二進(jìn)制日志進(jìn)行數(shù)據(jù)恢復(fù)：

MariaDB [(none)]> use hellodb;
Database changed
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| t1                |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> drop table t1;
Query OK, 0 rows affected (0.01 sec)
MariaDB [hellodb]> create table tb1(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
MariaDB [hellodb]> insert into tb1 values(1,'alren'),(2,'tbname');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)
MariaDB [hellodb]> \q
Bye

   第三步：使用另外一臺(tái)服務(wù)器做恢復(fù)測試，將hellodb.sql,及發(fā)生改變的二進(jìn)制日志導(dǎo)入出來拷貝至另一臺(tái)主機(jī)

[root@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#161117  6:43:00 server id 1  end_log_pos 245 Start: binlog v 4, server v5.5.32-MariaDB-log created 161117  6:43:00
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
9OAsWA8BAAAA8QAAAPUAAAABAAQANS41LjMyLU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAALfrHBw==
'/*!*/;
# at 245
#161117  7:01:18 server id 1  end_log_pos 352 Querythread_id=23exec_time=0error_code=0
use `hellodb`/*!*/;
SET TIMESTAMP=1479337278/*!*/;
SET @@session.pseudo_thread_id=23/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
DROP TABLE `t1` /* generated by server */
/*!*/;
# at 352
#161117  7:01:51 server id 1  end_log_pos 459 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337311/*!*/;
create table tb1(id int,name varchar(20))
/*!*/;
# at 459
#161117  7:02:43 server id 1  end_log_pos 530 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337363/*!*/;
BEGIN
/*!*/;
# at 530
#161117  7:02:43 server id 1  end_log_pos 642 Querythread_id=23exec_time=0error_code=0
SET TIMESTAMP=1479337363/*!*/;
insert into tb1 values(1,'alren'),(2,'tbname')
/*!*/;
# at 642
#161117  7:02:43 server id 1  end_log_pos 669 Xid = 1515
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@centos6 binlog]# mysqlbinlog --start-position=245 master-bin.000005 >inre.sql

第四步：恢復(fù)數(shù)據(jù)，進(jìn)入到mysql的命令行模式下或在命令行，把二進(jìn)制日志關(guān)掉，恢復(fù)數(shù)據(jù)時(shí)不需要把恢復(fù)信息記錄到二進(jìn)制日志中去；

MariaDB [(none)]>set sql_log_bin=0
MariaDB [hellodb]>source /root/hellodb1.sql
MariaDB [hellodb]> source /root/inre.sql
MariaDB [hellodb]>set sql_log_bin=1
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| tb1               |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.00 sec)

如上訴結(jié)果說明數(shù)據(jù)已經(jīng)恢復(fù)到原來最初始的狀態(tài)，基于mysqldump+二進(jìn)制日志的備份和恢復(fù)完成。