MySQL資料備份與恢複

一、簡介

資料在當今的網際網路行業中非常重要，掌握了資料能夠從中發現價值，是以資料對于公司來書再重要不過了，當我們面臨資料的丢失時，比如資料意外删除、自然災害等，然而資料的備份和恢複就顯得尤為重要。

二、MySQL/MariaDB資料備份與恢複

常見的備份類型：

根據備份時資料庫是否線上：

冷備：cold backup,伺服器離線，線上業務終止，無法執行讀寫操作，但是此方法備份資料最為靠譜，丢資料少熱備：hot backup,伺服器線上，線上業務正常進行，讀寫操作都可進行，此方法備份要求複雜

溫備：warn backup,伺服器線上，但是對資料庫施加全局鎖，隻讀操作正常進行，不可寫

根據備份時的接口：

實體備份：physical backup，直接從資料庫的資料目錄進行cp複制歸檔的方式

邏輯備份：logical backup，把資料從庫中提取出來儲存為文本檔案，主要用到的工具是mysqldump

注意：在資料量很大時使用邏輯備份很實用，當資料量小于10G時可以考慮使用mysqldump實作邏輯備份

根據備份的資料集：

完全備份：full backup,指的是備份整個庫

部分備份：partial backup,指的是備份資料庫中的部分資料

根據備份時是否備份整個資料還是僅僅備份變化的資料

完全備份： full backup,備份整個資料庫

增量備份：incremental backup,僅僅備份上一次完全備份之後所改變的資料做備份的為增量備份

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

MYISAM與InnoDB差別：

MYISAM存儲引擎不支援熱備，而InnoDB支援 InnoDB記錄資料時都會給資料一個序列号，是以在備份時基于MVCC(多版本并發控制)的機制自動加快照，每啟動一個事務都會建立目前集的一個快照，而後基于MVCC的機制把每一個序列号都給它記錄一份下來，備份時隻備份序列号或序列号之前的資料，往後發生的将不做備份，如果事務的隔離級别不是特别高的話，它并不會影響事務的讀寫操作，而這樣備份出來的資料一定是時間點一緻的資料，是以要完成熱備份，通常是基于事務的存儲引擎才能夠完成的。

Innobase：提供了商業備份工具為Innobackup，可以實作InnoDB的熱備支援增量備份；但是對于MyISAM不支援增量備份，隻能實作完全備份，屬于實體備份，速度比較快。

Xtrabackup：由Percona組織提供的開源備份工具，實體備份，速度快；

mysqldump：常用的備份工具，也是個邏輯備份工具，用于小資料備份，一般都是在10G以下的小資料進行備份；可以使用文本進行二次處理；相當于MySQL的用戶端工具

三、資料備份與恢複實戰

常見備份工具：

mysqldump:邏輯備份工具，備份和恢複過程都比較慢

mysqldumper:多線程，的mysqldump，很難實作差異或增量備份

lvm-snapshot:

接近于熱備的工具，因為要事先請求全局鎖，而後建立快照，并在建立快照完成後釋放全局鎖。使用cp,tar等工具進行實體備份；備份和恢複速度快，很難實作增量備份，并且請求全局需要一段時間，

邏輯備份工具，快于mysqldump

Innobase:商業備份工具，innobackup

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

innodb熱備，增量備份

myisam溫備，不支援增量

實體備份，速度快

mysqlhotcopy:幾乎冷備

mysqldump:僅适用于資料集較小的資料庫，資料量達到10G以上時建議不使用，備份太慢

<code>mysqldump:</code>

<code>mysqldump [options] [db_name [tbl_name ...]] </code>

<code>mysqldump -uroot -hlocalhost -p testdb></code><code>/tmp/testdb</code><code>.sql </code><code>#備份單個表</code>

<code>mysql -uroot -hlocalhost -p testdb<</code><code>/tmp/testdb</code><code>.sql </code><code>#恢複時要事先建立資料庫</code>

<code>mysqldump -uroot -hlocalhost -p --databases db1 db2 db2 ></code><code>/tmp/dbs</code><code>.sql </code><code>#使用此備份方法恢複時無需連入mysql建立資料庫</code>

<code>mysqldump -uroot -hlocalhost -p --all-databases ></code><code>/tmp/all</code><code>.sql </code><code>#備份所有資料庫，适用于冷備</code>

<code>備份單庫或多庫：--databases 備份所有資料庫：--all-databases</code>

<code>注意：線上備份時，在實施溫備時需施加鎖對單表或多表施加鎖，讓使用者隻能讀而不能寫</code>

<code>mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs></code><code>/tmp/testdb</code><code>.sql</code>

<code>mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs ></code><code>/tmp/testdb1</code><code>.sql </code><code>#僅對Innodb存儲引擎實作熱備，不支援myisam引擎</code>

<code>mysqldump -uroot -hlocalhost --databases testdb --lock-all-tables --flush-logs --master-data=2 ></code><code>/tmp/testdb2</code><code>.sql</code>

<code>mysqldump -uroot -hlocalhost --databases testdb --single-transaction --flush-logs --master-data=2 ></code><code>/tmp/testdb3</code><code>.sql</code>

<code>--lock-all-tables </code><code>#請求鎖定所有表之後備份 --single-transaction #能夠對innodb存儲引擎實作熱備</code>

<code>--flush-logs </code><code>#備份時滾動日志 --master-data=[0|1|2]設定為1:儲存為一個change master語句設定為2：注釋掉的change master語句，設定為0：不記錄</code>

<code>資料恢複：建議關閉二進制日志,關閉其他使用者連接配接：</code><code>set</code> <code>session sql_log_bin=0</code>

使用mysqldump實作備份及其使用二進制日志做恢複：

使用mysqldump實作備份,用二進制日志恢複資料，這裡我們以目前系統上的資料庫hellodb為例：

第一步：先把hellodb這個資料庫做一次完全備份，當資料庫的資料很大，比如說大于10G的話不建議使用mysqldump這個工具做備份，而使用實體備份更為合理：

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

第二步：在資料庫hellodb中修改或建立一些表或資料，使得之前備份的資料跟現有的資料庫中的資料存在差别，以完後後面的通過二進制日志進行資料恢複：

<code>MariaDB [(none)]> use hellodb;</code>

<code>Database changed</code>

<code>MariaDB [hellodb]> show tables;</code>

<code>| Tables_in_hellodb |</code>

<code>| classes |</code>

<code>| courses |</code>

<code>| scores |</code>

<code>| students |</code>

<code>| teachers |</code>

<code>MariaDB [hellodb]> drop table t1;</code>

<code>Query OK, 0 rows affected (0.01 sec)</code>

<code>MariaDB [hellodb]> create table tb1(</code><code>id</code> <code>int,name varchar(20));</code>

<code>Query OK, 0 rows affected (0.02 sec)</code>

<code>MariaDB [hellodb]> insert into tb1 values(1,</code><code>'alren'</code><code>),(2,</code><code>'tbname'</code><code>);</code>

<code>Query OK, 2 rows affected (0.01 sec)</code>

<code>Records: 2 Duplicates: 0 Warnings: 0</code>

<code>MariaDB [hellodb]> \q</code>

第三步：使用另外一台伺服器做恢複測試，将hellodb.sql,及發生改變的二進制日志導入出來拷貝至另一台主機

<code>[root@centos6 binlog]</code><code># mysqlbinlog --start-position=245 master-bin.000005</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;</code>

<code>/*!40019 SET @@session.max_insert_delayed_threads=0*/;</code>

<code>/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;</code>

<code>DELIMITER /*!*/;</code>

<code>#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</code>

<code># Warning: this binlog is either in use or was not closed properly.</code>

<code>BINLOG '</code>

<code>9OAsWA8BAAAA8QAAAPUAAAABAAQANS41LjMyLU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA</code>

<code>AAAAAAAAAAAALfrHBw==</code>

<code>#161117 7:01:18 server id 1 end_log_pos 352 Querythread_id=23exec_time=0error_code=0</code>

<code>use `hellodb`/*!*/;</code>

<code>SET TIMESTAMP=1479337278/*!*/;</code>

<code>SET @@session.pseudo_thread_id=23/*!*/;</code>

<code>SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;</code>

<code>SET @@session.sql_mode=0/*!*/;</code>

<code>SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;</code>

<code>SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;</code>

<code>SET @@session.lc_time_names=0/*!*/;</code>

<code>SET @@session.collation_database=DEFAULT/*!*/;</code>

<code>DROP TABLE `t1` /* generated by server */</code>

<code>#161117 7:01:51 server id 1 end_log_pos 459 Querythread_id=23exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1479337311/*!*/;</code>

<code>create table tb1(</code><code>id</code> <code>int,name varchar(20))</code>

<code>#161117 7:02:43 server id 1 end_log_pos 530 Querythread_id=23exec_time=0error_code=0</code>

<code>SET TIMESTAMP=1479337363/*!*/;</code>

<code>BEGIN</code>

<code>#161117 7:02:43 server id 1 end_log_pos 642 Querythread_id=23exec_time=0error_code=0</code>

<code>insert into tb1 values(1,</code><code>'alren'</code><code>),(2,</code><code>'tbname'</code><code>)</code>

<code>#161117 7:02:43 server id 1 end_log_pos 669 Xid = 1515</code>

<code>COMMIT/*!*/;</code>

<code>DELIMITER ;</code>

<code>ROLLBACK /* added by mysqlbinlog */;</code>

<code>/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;</code>

<code>/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;</code>

<code>[root@centos6 binlog]</code><code># mysqlbinlog --start-position=245 master-bin.000005 >inre.sql</code>

第四步：恢複資料，進入到mysql的指令行模式下或在指令行，把二進制日志關掉，恢複資料時不需要把恢複資訊記錄到二進制日志中去；

<code>MariaDB [(none)]></code><code>set</code> <code>sql_log_bin=0</code>

<code>MariaDB [hellodb]></code><code>source</code> <code>/root/hellodb1</code><code>.sql</code>

<code>MariaDB [hellodb]> </code><code>source</code> <code>/root/inre</code><code>.sql</code>

<code>MariaDB [hellodb]></code><code>set</code> <code>sql_log_bin=1</code>

如上訴結果說明資料已經恢複到原來最初始的狀态，基于mysqldump+二進制日志的備份和恢複完成。

本文轉自chengong1013 51CTO部落格，原文連結：http://blog.51cto.com/purify/1875343，如需轉載請自行聯系原作者

MySQL資料備份與恢複

繼續閱讀

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述