使用mysqldump命令進行資料庫備份和恢復

一、環境準備
1 、建立存放備份的目錄以及存放二進位制日誌的目錄

   # mkdir /backup
   # chown -R mysql:mysql /backup
   # mkdir /binlogdir
   # chown mysql:mysql /binlogdir

2 、修改二進位制檔案存放路徑為新建目錄下，並設定 innodb 每表一檔案

# vim /etc/my.cnf
log-bin=/binlogdir/mysql-bin #二進位制日誌檔案存放位置
innodb_file_per_table = 1 #啟用 InnoDB 表每表一檔案，預設所有庫使用一個表空間

3 、啟動服務並建立資料庫

   # service mysqld start
   # mysql -uroot -ptsb-net
   mysql> create database testdb;
   mysql> use testdb;
   mysql> CREATE TABLE tb1 (ID TINYINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
   Name CHAR(30) NOT NULL UNIQUE KEY, Age TINYINT UNSIGNED);
   mysql> insert into tb1 values (1,’Huchong’,24);
   mysql> insert into tb1 values (2,’Huafeng’,24);
   mysql> insert into tb1 values (3,’Jim’,21);
   mysql> insert into tb1 values (4,’Tom’,25);

二、進行備份（完全備份+增量備份+節點恢復）
1 、完全備份

# mysqldump -uroot -ptsb-net –lock-all-tables –flush-logs –events –routines –master-data=2 –all-databases > /backup/fulldata-`date +%F`.sql
    –single-transaction: 基於此選項能實現熱備InnoDB表；mysql> show table statusG；可以檢視表的型別，如果表的型別不是全部為 InnoDB，則需要使用–lock-all-tables；
    –events：備份資料的同時，備份事件排程器程式碼；
    –master-data=2：在備份檔案中記錄當前二進位制日誌的位置
    –all-databases：備份所有庫
    –flush-logs：重新整理二進位制日誌，產生一個新的二進位制日誌檔案

2 、模擬增量備份並進行增量備份

三、產生新的二進位制事件並模擬資料庫損壞

# mysql -uroot -ptsb-net
mysql> use testdb;
mysql> delete from tb1 where id = 3;
mysql> drop database testdb; #模擬資料庫損壞

四、資料庫恢復
1 、恢復前的準備

mysql> set global sql_log_bin=0;                  #不需要記錄二進位制日誌
mysql> show global variables like ‘sql_log_bin’;  #檢視二進位制日誌狀態
mysql> flush logs;            #滾動日誌
mysql> show master status;

# mysql -u root -ptsb-net < /backup/fulldata-2015-07-17.sql # mysql -u root -ptsb-net < /backup/cactidb-2015-07-17.sql 3、節點恢復（目前日誌檔案是mysql-bin.000004，所以我們需要恢復的檔案是mysql-bin.000003） # mysqlbinlog /binlogdir/mysql-bin.000003 #檢視日誌檔案 # mysqlbinlog --stop-position=297 /binlogdir/mysql-bin.000003 > /backup/last.sql
# mysql -u root -ptsb-net < /backup/last.sql # mysql -u root -ptsb-net mysql> use testdb;
mysql> select * from tb1;

五、收尾工作

mysql> set global sql_log_bin=1; #開啟二進位制日誌記錄
mysql> quit;
# service mysqld restart

總結：mysqldump 工具對 InnoDB 儲存引擎的表可實現熱備，備份時可以不鎖定表，否則在備份前需要鎖定表，只能實現溫備。當備份的資料量較大時，恢復時間會很長，因為恢復是把一條一條的 sql 語句讀取後在 mysql 中執行，是寫入操作。所以建議在資料量不大時可以使用此工具進行備份操作，但當隨著資料增長此工具將不適合進行 mysql 的備份可恢復操作，應選取其他更優的資料備份恢復方案。

使用 mysqldump 命令進行資料庫備份和恢復

使用 mysqldump 命令進行資料庫備份和恢復