MySQL備份與還原

一 了解MySQL備份與恢複

備份：存儲的資料副本;原始資料,持續改變。

恢複：把副本應用到線上系統;僅能恢複至備份操作時刻的資料狀态。

時間點恢複：binary logs。

為什麼備份？

1.災難恢複：硬體故障（備援）、軟體故障（bug）、自然災害、黑客攻擊、誤操作、...

2.用于測試。

備份時應該注意事項：

1.能容忍最多丢失多少資料；

2.恢複資料需要在多長時間内完成；

3.需要恢複哪些資料；

4.做恢複演練：

 測試備份的可用性；

 增強恢複操作效率；

 ...

備份類型：

1.備份的資料的集範圍：

 完全備份和部分備份

  完全備份：整個資料集;

  部分備份：資料集的一部分，比如部分表;

2.完全備份、增量備份、差異備份：

 完全備份

 增量備份：僅備份自上一次完全備份或 增量備份以來變量的那部資料;

 差異備份：僅備份自上一次完全備份以來變量的那部資料;

3.實體備份、邏輯備份：

 實體備份：複制資料檔案進行備份;

 邏輯備份：從資料庫導出資料另存在一個或多個檔案中;

4.根據資料服務是否線上：

 熱備：讀寫操作均可進行的狀态下所做的備份;

 溫備：可讀但不可寫狀态下進行的備份;

 冷備：讀寫操作均不可進行的狀态下所做的備份。

備份需要考慮因素：

1.鎖定資源多長時間？

2.備份過程的時長？

3.備份時的伺服器負載？

4.恢複過程的時長？

備份政策：

1.全量+差異

2.全量+增量

3.備份手段：實體、邏輯

備份什麼？

資料、二進制日志、InnoDB的事務日志、代碼（存儲過程、存儲函數、觸發器、事件排程器）、伺服器的配置檔案。

備份工具：

1.mysqldump：mysql服務自帶的備份工具;邏輯備份工具:

 完全、部分備份;

 InnoDB：熱備;

 MyISAM：溫備。

2.xtrabackup：

 由Percona提供，開源工具，支援對InnoDB做熱備，實體備份工具:

  全量備份、部分備份;

  全量備份、增量備份;

  全量備份、差異備份;

1.完全+差異+binlog

2.完全+增量+binlog

3.備份，多久一次？

 資料變化量

 可用的備份存儲空間

二 MySQL備份與恢複工具介紹

1.mysqldump：

邏輯備份、完全備份、部分備份；

二次封裝工具：

mydumper(perl腳本)

phpMyAdmin

msyqldump Usage:

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
      

選項：

MyISAM存儲引擎：支援溫備，備份時要鎖定表:

-x, --lock-all-tables          #鎖定所有庫的所有表，讀鎖
-l, --lock-tables           #鎖定指定庫所有表
      

InnoDB存儲引擎：支援溫備和熱備:

--single-transaction          #建立一個事務，基于此快照執行備份;
      

其它選項:

-R, --routines            #存儲過程和存儲函數;
--triggers             #備份觸發器
-E, --events            #備份排程器
--master-data[=#]
  1：記錄為CHANGE MASTER TO語句，此語句不被注釋
  2：記錄為CHANGE MASTER TO語句，此語句被注釋
--flush-logs            #鎖定表完成後，即進行日志重新整理操作
      

示例：

[root@node1 ~]# mysqldump -hlocalhost -uroot --databases hellodb > /tmp/hellodb-$(date +%F).sql
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
MariaDB [hellodb]> DELETE FROM students WHERE StuID=14;
MariaDB [hellodb]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       489 |
+------------------+-----------+
MariaDB [hellodb]> exit
[root@node1 ~]# mysqldump -hlocalhost -uroot --master-data=1 --databases hellodb > /tmp/hellodb-$(date +%F).sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=489;   (此行未被注釋)
[root@node1 ~]# mysqldump -hlocalhost -uroot --master-data=2 --databases hellodb > /tmp/hellodb-$(date +%F).sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=489;  (此行被注釋)
[root@node1 ~]# mysqldump -hlocalhost -uroot --master-data=2 --flush-logs --single-transaction --databases hellodb > /tmp/hellodb-$(date +%F).sql
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       532 |
| mysql-bin.000002 |       245 |
+------------------+-----------+
MariaDB [hellodb]> DELETE FROM students WHERE StuID=10;
MariaDB [hellodb]> DROP DATABASE hellodb;
MariaDB [(none)]> exit;
[root@node1 mysql]# mysqlbinlog -j 245 mysql-bin.000002 > /tmp/hellodb.binlog.sql
備份到另一主機：
[root@node1 mysql]# scp /tmp/hellodb* [email protected]:/tmp/
[root@node2 ~]# mysql < /tmp/hellodb-2017-06-03.sql
[root@node2 ~]# mysql
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
MariaDB [hellodb]> exit
[root@node2 ~]# mysql < /tmp/hellodb.binlog.sql 
[root@node2 ~]# mysql
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
      

實作了時間點還原，删除的第10行沒被還原

注意：

(1) 腳本化、周期性進行(進行充分測試)；

(2) 備份結果要另存，建議異地存儲；對備份的結果做測試；

(3) 配置mysqldump使用binlog做增量備份；    

MyISAM：溫備，不支援增量備份；

InnoDB：熱備，增量；

實體備份，速率快、可靠；備份完成後自動校驗備份結果集是否可用；還原速度快；

Usage: [innobackupex [--defaults-file=#] --backup | innobackupex [--defaults-file=#] --prepare] [OPTIONS]
The  innobackupex tool is a Perl script that acts as a wrapper for the xtrabackup C program.
      

備份 --> 應用日志 --> 還原

完全備份：

完全+binlog(總結)：

備份：innobackupex  --user  --password=  --host=  /PATH/TO/BACKUP_DIR

準備：innobackupex --apply-log  /PATH/TO/BACKUP_DIR

恢複：innobackupex --copy-back

注意：--copy-back需要在mysqld主機本地進行，mysqld服務不能啟動；

innodb_log_file_size可能要重新設定； 

總結：完全+增量+binlog

備份：完全+增量+增量+...

完全+差異

準備：

innobackupex --apply-log --redo-only BASEDIR 
innobackupex --apply-log --redo-only BASEDIR  --incremental-dir=INCREMENTAL-DIR
      

恢複：

    innobackupex --copy-back BASEDIR

備份單庫：

    --databases

總結：

 mysqldump+binlog

 lvm2+cp/tar+binlog

 xtrabackup(innodb)+binlog

備份：

MariaDB [hellodb]> GRANT ALL ON *.* TO buser@'172.18.67.%' IDENTIFIED BY 'bpass';
MariaDB [hellodb]> FLUSH PRIVILEGES;
[root@node2 ~]# mkdir /mydata/backup -pv
[root@node2 ~]# innobackupex --user=buser --host=172.18.67.12 --password=bpass --databases=hellodb /mydata/backup/
 備份hellodb庫
[root@node2 ~]# innobackupex --user=buser --host=172.18.67.12 --password=bpass /mydata/backup/
 備份全部
      

[root@node1 ~]# rm /var/lib/mysql/* -rf
[root@node2 backup]# scp -rp 2017-06-04_14-09-37 [email protected]:/tmp/
[root@node1 ~]# systemctl stop mariadb
[root@node1 ~]# innobackupex --user=root --host=localhost --copy-back /tmp/2017-06-04_14-09-37/
[root@node1 ~]# chown -R mysql.mysql /var/lib/mysql/
[root@node1 ~]# systemctl start mariadb