MySQL數據的備份還原及數據修復

在日常的生產環境中為什么要進行備份

??備份可以使生產中的數據進行有效的災難恢復：硬件故障、軟件故障、自然災害、誤操作測試等數據丟失場景。

備份注意要點

1. 能容忍最多丟失多少數據
2. 恢復數據需要在多長時間內完成
3. 需要恢復哪些數據

還原要點

1. 做還原測試，用于測試備份的可用性
2. 還原演練

備份類型

完全備份、不分備份

1. 完全備份：整個數據集
2. 部分備份：只備份數據子集，如部分庫或表

增量備份、差異備份

1. 增量備份：僅備份最近一次完全備份或增量備份（如果存在增量）以來變化的數據，備份較快，還原復雜
2. 差異備份：僅備份最近一次完全備份以來變化的數據，備份較慢，還原簡單

冷、溫、熱備份

1. 冷備：讀寫操作均不可進行
2. 溫備：讀操作可執行；但寫操作不可執行
3. 熱備：讀寫操作均可執行
   MyISAM：溫備，不支持熱備
   InnoDB：都支持

物理和邏輯備份

1. 物理備份：直接復制數據文件進行備份，與存儲引擎有關，占用較多的空間，速度快
2. 邏輯備份：從數據庫中“導出”數據另存而進行的備份，與存儲引擎無關，占用空間少，速度慢，可能丟失精度

備份時需要考慮的因素

1. 溫備的持鎖多久
2. 備份產生的負載
3. 備份過程的時長
4. 恢復過程的時長

備份什么

1. 數據
2. 二進制日志、InnoDB的事務日志
3. 程序代碼（存儲過程、函數、觸發器、事件調度器）
4. 服務器的配置文件

備份工具

1. cp, tar等復制歸檔工具：物理備份工具，適用所有存儲引擎；只支持冷備；完全和部分備份
2. LVM的快照：先加鎖，做快照后解鎖，幾乎熱備；借助文件系統工具進行備份
3. mysqldump：邏輯備份工具，適用所有存儲引擎，溫備；支持完全或部分備份；對InnoDB存儲引擎支持熱備，結合binlog的增量備份
4. xtrabackup：由Percona提供支持對InnoDB做熱備(物理備份)的工具，支持完全備份、增量備份
5. MariaDB Backup： 從MariaDB 10.1.26開始集成，基于Percona
   XtraBackup 2.3.8實現
6. mysqlbackup：熱備份， MySQL Enterprise Edition組件
7. mysqlhotcopy：PERL 語言實現，幾乎冷備，僅適用于MyISAM存儲引擎，使用LOCK TABLES、 FLUSH TABLES和cp或scp來快速備份數據庫

實驗操作部分

物理備份：借助cp、tar執行冷備份（適用于可以停止數據庫服務的業務）

1、停止服務器上的數據庫服務進程

[root@CentOS7 ~]#service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]

2、考慮到空間資源問題，將備份的數據文件進行打包壓縮

[root@CentOS7 data]#tar -zcvf mysql.tar.gz mysql/*
mysql/aria_log.00000001
mysql/aria_log_control
mysql/hello/
mysql/hello/db.opt
mysql/hello/classes.frm
mysql/hello/classes.ibd
mysql/hello/coc.frm
mysql/hello/coc.ibd
mysql/hello/courses.frm
mysql/hello/courses.ibd
mysql/hello/scores.frm
....

3、備份二進制文件至備份主機

[root@CentOS7 data]#mkdir /data/bin
[root@CentOS7 data]#chown -R mysql.mysql /data/bin/
scp -p 可以保留文件的所屬權限
[root@CentOS7 data]#scp -p /data/bin/* 192.168.36.6:/data/bin/

4、將備份的數據壓縮文件scp至備份主機上

[root@CentOS7 data]#scp -p /data/mysql.tar.gz 192.168.36.6:/data/

5、備份配置文件

[root@CentOS7 data]#scp -p /etc/my.cnf 192.168.36.6:/etc/

6、啟動服務

[root@CentOS7 data]#service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

7、停止服務，刪除數據

[root@CentOS7 data]#service mysqld stop
Stopping mysqld (via systemctl):                           [  OK  ]
[root@CentOS7 data]#rm -rf /data/mysql/*

8、解壓備份的數據文件到/data/mysql目錄中

[root@CentOS7 data]#gzip -d mysql.tar.gz
[root@CentOS7 data]#tar -xvf mysql.tar -C ./

9、啟動服務測試,并進行數據檢查

[root@CentOS7 data]#service mysqld start
Starting mysqld (via systemctl):                           [  OK  ]

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> use hello;
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

MariaDB [hello]> 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 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)

基于LVM的備份

1、請求鎖定所有表

mysql> FLUSH TABLES WITH READ LOCK;

2、記錄二進制日志文件及事件位置

mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE

3、創建快照

lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME

4.、釋放鎖

mysql> UNLOCK TABLES;

5. 掛載快照卷，執行數據備份
6. 備份完成后，刪除快照卷
7. 制定好策略，通過原卷備份二進制日志

   注意：此實驗前提是數據文件處于LVM邏輯卷中

基于mysqldump工具進行備份

mysqldump常見選項

??-A， --all-databases 備份所有數據庫，含create database
??-B , --databases db_name… 指定備份的數據庫，包括create database語句
??-E, --events：備份相關的所有event scheduler
??-R, --routines：備份所有存儲過程和自定義函數
??--triggers：備份表相關觸發器，默認啟用,用--skip-triggers，不備份觸發器
??--default-character-set=utf8 指定字符集
??--master-data[=#]： 此選項須啟用二進制日志
???? 1：所備份的數據之前加一條記錄為CHANGE MASTER TO語句，非注釋，不指定#，默認為1
???? 2：記錄為注釋的CHANGE MASTER TO語句此選項會自動關閉--lock-tables功能，自動打開-x | --lock-all-tables功能（除非開啟--single-transaction）
??-F, --flush-logs ：備份前滾動日志，鎖定表完成后，執行flush logs命令,生成新的二進制日志文件，配合-A 或 -B 選項時，會導致刷新多次數據庫。 建議在同一時刻執行轉儲和日志刷新，可通過和--single-transaction或-x，--master-data 一起使用實現，此時只刷新一次日志
??--compact 去掉注釋，適合調試，生產不使用
??-d, --no-data 只備份表結構
??-t, --no-create-info 只備份數據,不備份create table
??-n,--no-create-db 不備份create database，可被-A或-B覆蓋
??--flush-privileges 備份mysql或相關時需要使用
??-f, --force 忽略SQL錯誤，繼續執行
??--hex-blob 使用十六進制符號轉儲二進制列，當有包括BINARY，VARBINARY，BLOB，BIT的數據類型的列時使用，避免亂碼
??-q, --quick 不緩存查詢，直接輸出，加快備份速度

mysqldump實驗部分

單個數據庫的備份

1、利用mysqldump生成備份文件

[root@CentOS7 data]#mysqldump hello >/data/backup/hello.sql

2、實現刪庫跑路的功能

[root@CentOS7 data]#mysql -e 'drop database hello'

3、創建一個數據庫，實現數據恢復

[root@CentOS7 data]#mysql -e 'create database hello'
[root@CentOS7 data]#mysql hello </data/backup/hello.sql

4、數據檢查

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> use hello;
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

注：mysqldump hello命令可以羅列出數據庫hello的數據表的結構以及字符集等數據，利用這些數據可完成數據的備份與恢復，缺陷是沒有創建數據庫的功能，還需要手動創建數據庫名稱。所以并不能確保創建的數據庫名稱與之前數據庫名稱的一致性

解決上述缺陷的辦法：使用 -B 參數（-B等價于--databases），可以備份出數據庫的定義，推薦使用

[root@CentOS7 data]#mysqldump -B hello >/data/backup/hello_bak.sql
[root@CentOS7 data]#grep '^CREATE DATABASE' /data/backup/hello_bak.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET latin1 */;

數據庫中表的備份

利用mysqldump可以實現數據庫中指定表的備份功能
1、備份hello庫中students表的數據

[root@CentOS7 data]#mysqldump hello students >/data/backup/students.sql

查看備份數據
[root@CentOS7 data]#cat /data/backup/students.sql
-- MySQL dump 10.16  Distrib 10.2.23-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: hello
-- ------------------------------------------------------
-- Server version   10.2.23-MariaDB-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `students`
--

DROP TABLE IF EXISTS `students`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `students` (
  `StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Age` tinyint(3) unsigned NOT NULL,
  `Gender` enum('F','M') NOT NULL,
  `ClassID` tinyint(3) unsigned DEFAULT NULL,
  `TeacherID` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `students`
--

LOCK TABLES `students` WRITE;
/*!40000 ALTER TABLE `students` DISABLE KEYS */;
INSERT INTO `students` VALUES (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);
/*!40000 ALTER TABLE `students` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2019-05-06 15:22:32

2、刪除數據庫中的students表

[root@CentOS7 data]#mysql -e 'drop tables hello.students'

3、數據恢復

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| teachers        |
| toc             |
+-----------------+
6 rows in set (0.00 sec)

MariaDB [hello]> source /data/backup/students.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....

MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

備份數據庫中所有數據

1、利用mysqldump -A功能進行備份數據庫所有數據

[root@CentOS7 data]#grep '^CREATE DATABASE' /data/backup/all.sql
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hello` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTE

2、清空數據庫數據

[root@CentOS7 data]#rm -rf /data/mysql/*

3、重啟服務，還原數據

[root@CentOS7 data]#service mysqld restart
Restarting mysqld (via systemctl):                         [  OK  ]

[root@CentOS7 data]#mysql < /data/backup/all.sql

[root@CentOS7 data]#ll /data/mysql/
total 110620
-rw-rw---- 1 mysql mysql    16384 May  6 15:41 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 May  6 15:41 aria_log_control
drwx------ 2 mysql mysql      272 May  6 15:41 hello
-rw-rw---- 1 mysql mysql      860 May  6 15:41 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May  6 15:41 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May  6 15:41 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 May  6 15:41 ib_logfile1
drwx------ 2 mysql mysql     4096 May  6 15:41 mysql
drwx------ 2 mysql mysql       20 May  6 15:41 test

關于mysqldump的擴展

MyISAM備份選項：

??支持溫備；不支持熱備，所以必須先鎖定要備份的庫，而后啟動備份操作
??鎖定方法如下：
????-x,--lock-all-tables：加全局讀鎖，鎖定所有庫的所有表，同時加--singletransaction或--lock-tables選項會關閉此選項功能
????注意：數據量大時，可能會導致長時間無法并發訪問數據庫
????-l,--lock-tables：對于需要備份的每個數據庫，在啟動備份之前分別鎖定其所有表，默認為on,--skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能
會造成數據不一致
??注：以上選項對InnoDB表一樣生效，實現溫備，但不推薦使用

InnoDB備份選項：

??支持熱備，可用溫備但不建議用
??--single-transaction
????此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執行START TRANSACTION指令開啟事務
????此選項通過在單個事務中轉儲所有表來創建一致的快照。 僅適用于存儲在支持多版本控制的存儲引擎中的表（目前只有InnoDB可以）; 轉儲不保證與其他存儲引擎保持一致。 在進行單事務轉儲時，要確保有效的轉儲文件（正確的表內容和二進制日志位置），沒有其他連接應該使用以下語句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE
????此選項和--lock-tables（此選項隱含提交掛起的事務）選項是相互排斥
????備份大型表時，建議將--single-transaction選項和--quick結合一起使用

InnoDB建議備份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建議備份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $BACKUP/fullbak_$BACKUP_TIME.sql

由于某公司指定時間進行數據的備份，然而備份完數據發生了修改，此時出現故障導致數據丟失，請嘗試恢復至最近數據

1、備份數據并壓縮

[root@CentOS7 data]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz

[root@CentOS7 data]#ll /data/all.sql.xz
-rw-r--r-- 1 root root 105104 May  6 16:42 /data/all.sql.xz

2、添加數據

MariaDB [hello]> insert students(name,age)values('Darius',23);
Query OK, 1 row affected (0.03 sec)

MariaDB [hello]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
....
|    26 | Darius        |  23 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

3、 刪庫

[root@CentOS7 data]#rm -rf mysql/*

[root@CentOS7 data]#ll mysql/
total 0

4、重啟服務

[root@CentOS7 data]#service mysqld restart

5、因無需二進制日志改變，所以臨時關閉二進制日志

MariaDB [(none)]> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)

6、恢復數據庫，先恢復完全備份

解壓
[root@CentOS7 data]#xz -d all.sql.xz
恢復完全備份數據
[root@CentOS7 data]#mysql <all.sql
[root@CentOS7 data]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.2.23-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hello              |
| information_schema |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use hello
Database changed
MariaDB [hello]> show tables;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

>> 此時備份之后的數據還沒有進行恢復

7、查看完全備份時二進制文件的位置

[root@CentOS7 data]#vim all.sql
....
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=4882960;
....

8、查看完全備份時二進制日志的位置，從此位置后的日志導出到inc.sql

[root@CentOS7 data]# mysqlbinlog --start-position=4882960 /data/bin/mysql-bin.000005 > /data/inc.sql

9、導入數據

MariaDB [mysql]> source /data/inc.sql;

開啟二進制日志
MariaDB [(none)]> set sql_log_bin=on;
Query OK, 0 rows affected (0.00 sec)

10、成功恢復數據

MariaDB [hello]> select *from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
....
|    26 | Darius        |  23 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
26 rows in set (0.00 sec)

基于 xtrabackup 工具完全備份及還原

簡介

??percona提供的mysql數據庫備份工具，惟一開源的能夠對innodb和xtradb數據庫進行熱備的工具

特點

1. 備份還原過程快速、可靠
2. 備份過程不會打斷正在執行的事務
3. 能夠基于壓縮等功能節約磁盤空間和流量
4. 自動實現備份檢驗
5. 開源，免費

關于xtrabackup

??xtrabackup 是用來備份 InnoDB 表的，不能備份非 InnoDB 表，和 MySQL Server 沒有交互

??innobackupex 腳本用來備份非 InnoDB 表，同時會調用 xtrabackup 命令來備份 InnoDB 表，還會和 MySQL Server 發送命令進行交互，如加全局讀鎖（FTWRL）、獲取位點（SHOW SLAVE STATUS）等。即innobackupex是在xtrabackup 之上做了一層封裝實現的

xtrabackup備份過程

xtrabackup的新版變化

xtrabackup版本升級到2.4后，相比之前的2.1有了比較大的變化：innobackupex 功能全部集成到 xtrabackup 里面，只有一個 binary程序，另外為了兼容考慮，innobackupex作為 xtrabackup 的軟鏈接，即xtrabackup現在支持非Innodb表備份，并且 Innobackupex 在下一版本中移除，建議通過xtrabackup替換innobackupex

xtrabackup安裝

xtrabackup工具在EPEL中，需要通過EPEL源進行下載
[root@CentOS7 ~]#yum install percona-xtrabackup

通過官網下載最新版本
https://www.percona.com/downloads/XtraBackup/LATEST/

貼心的人兒，給各位奉上阿里云的EPEL源
[epel]
name=epel
baseurl=http://mirrors.aliyun.com/epel/7/x86_64
gpgcheck=0

xtrabackup用法

?? 備份：innobackupex [option] BACKUP-ROOT-DIR
?? 選項說明：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html
?? ?? --user：該選項表示備份賬號
?? ?? --password：該選項表示備份的密碼
?? ?? --host：該選項表示備份數據庫的地址
?? ?? --databases：該選項接受的參數為數據庫名，如果要指定多個數據庫，彼此間需要以空格隔開；如："xtra_test dba_test"，同時，在指定某數據庫時，也可以只指定其中的某張表。如："mydatabase.mytable"。該選項對innodb引擎表無效，還是會備份所有innodb表
?? ?? --defaults-file：該選項指定從哪個文件讀取MySQL配置，必須放在命令行第一個選項位置
?? ?? --incremental：該選項表示創建一個增量備份，需要指定--incremental-basedir
?? ?? --incremental-basedir：該選項指定為前一次全備份或增量備份的目錄，與--incremental同時使用
?? ?? --incremental-dir：該選項表示還原時增量備份的目錄
?? ?? --include=name：指定表名，格式：databasename.tablename
?? Prepare：innobackupex --apply-log [option] BACKUP-DIR
?? 選項說明：
?? --apply-log：一般情況下,在備份完成后，數據尚且不能用于恢復操作，因為備
份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。此選項作用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處于一致性狀態
?? --use-memory：和--apply-log選項一起使用，當prepare 備份時，做crash recovery分配的內存大小，單位字節，也可1MB,1M,1G,1GB等，推薦1G
?? --export：表示開啟可導出單獨的表之后再導入其他Mysql中
?? --redo-only：此選項在prepare base full backup，往其中合并增量備份時候使用，但不包括對最后一個增量備份的合并
?? 還原：innobackupex --copy-back [選項] BACKUP-DIR
?? innobackupex --move-back [選項] [--defaults-group=GROUP-NAME] BACKUP-DIR
?? 選項說明：
?? --copy-back：做數據恢復時將備份數據文件拷貝到MySQL服務器的datadir
?? --move-back：這個選項與--copy-back相似，唯一的區別是它不拷貝文件，而是移動文件到目的地。這個選項移除backup文件，用時候必須小心。使用場景：沒有足夠的磁盤空間同事保留數據文件和Backup副本
?? 還原注意事項：
?? ?? 1.datadir 目錄必須為空。除非指定innobackupex --force-non-emptydirectorires選項指定，否則--copy-backup選項不會覆蓋
?? ?? 2.在restore之前,必須shutdown MySQL實例，不能將一個運行中的實例restore到datadir目錄中
?? ?? 3.由于文件屬性會被保留，大部分情況下需要在啟動實例之前將文件的屬主改為mysql，這些文件將屬于創建備份的用戶
?? ?? ?? chown -R mysql:mysql /data/mysql
?? ?? ?? 以上需要在用戶調用innobackupex之前完成
?? ?? ?? --force-non-empty-directories：指定該參數時候，使得innobackupex --copy-back或--move-back選項轉移文件到非空目錄，已存在的文件不會被覆蓋。如果--copy-back和--move-back文件需要從備份目錄拷貝一個在datadir已經存在的文件，會報錯失敗

備份生成的相關文件

使用innobackupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、 MERGE、 CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命名的目錄中,在備份時，innobackupex還會在備份目錄中創建如下文件：
?? (1)xtrabackup_info：innobackupex工具執行時的相關信息，包括版本，備份選項，備份時長，備份LSN(log sequence number日志序列號)，BINLOG的位置
?? (2)xtrabackup_checkpoints：備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN范圍信息,每個InnoDB頁(通常為16k大小)都會包含一個日志序列號LSN。 LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的
?? (3)xtrabackup_binlog_info：MySQL服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置，可利用實現基于binlog的恢復
?? (4)backup-my.cnf：備份命令用到的配置選項信息
?? (5)xtrabackup_logfile：備份生成的日志文件

使用舊版本 xtrabackup 工具進行備份及還原

1、在原主機

innobackupex --user=root /backup
scp -r /backup/2018-02-23_11-55-57/ 目標主機:/data/

2、在目標主機

innobackupex --apply-log /data/2019-05-06_20-34-35/

systemctl stop mariadb

rm -rf /var/lib/mysql/*

innobackupex --copy-back /data/2019-05-06_20-34-35/

chown -R mysql.mysql /var/lib/mysql/

systemctl start mariadb

使用新版本 xtrabackup 工具進行備份及還原

1、在原主機做完全備份到/backups

xtrabackup --backup --target-dir=/backup/
scp -r /backup/* 目標主機:/backup

2、在目標主機上

??1）預準備：確保數據一致，提交完成的事務，回滾未完成的事務

xtrabackup --prepare --target-dir=/backup/

??2）復制到數據庫目錄
???? 注意：數據庫目錄必須為空，MySQL服務不能啟動

xtrabackup --copy-back --target-dir=/backup/

??3）還原屬性

chown -R mysql:mysql /var/lib/mysql

??4）啟動服務

systemctl start mariadb

舊版xtrabackup完全，增量備份及還原

??1、在原主機

innobackupex /backup

mkdir /backup/inc{1,2}

??修改數據庫內容

innobackupex --incremental /backup/inc1 --incrementalbasedir=/backups/2019-05-06_20-34-35（完全備份生成的路徑）

??再次修改數據庫內容

innobackupex --incremental /backup/inc2 --incrementalbasedir=/backup/inc1/2019-05-06_20-34-35 （上次增量備份生成的路徑）

scp -r /backup/* 目標主機:/data/

??2、在目標主機

不啟動mariadb

rm -rf /var/lib/mysql/*

innobackupex --apply-log --redo-only /data/2019-05-06_20-34-35/

innobackupex --apply-log --redo-only /data/2019-05-06_20-34-35/ --incremental-dir=/data/inc1/2018-02-23_14-26-17

innobackupex --apply-log /data/2019-05-06_20-34-35/ --incrementaldir=/data/inc2/2018-02-23_14-28-29/

innobackupex --copy-back /data/2019-05-06_20-34-35/

chown -R mysql.mysql /var/lib/mysql/

systemctl start mariadb

新版xtrabackup完全，增量備份及還原

??1. 備份過程

??1）完全備份：

xtrabackup --backup --target-dir=/backup/base

??2）第一次修改數據
??3）第一次增量備份

xtrabackup --backup --target-dir=/backup/inc1 --incrementalbasedir=/backup/base

??4）第二次修改數據
??5）第二次增量

xtrabackup --backup --target-dir=/backup/inc2 --incrementalbasedir=/backup/inc1

??6）scp到目標主機

scp -r /backup/* 目標主機:/backup/

??備份過程生成三個備份目錄

/backup/{base，inc1，inc2}

??2. 還原過程

??1）預準備完成備份，此選項--apply-log-only 阻止回滾未完成的事務

xtrabackup --prepare --apply-log-only --target-dir=/backup/base

??2）合并第1次增量備份到完全備份

xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

??3）合并第2次增量備份到完全備份：最后一次還原不需要加選項--apply-log-only

xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

??4）復制到數據庫目錄，注意數據庫目錄必須為空，MySQL服務不能啟動

xtrabackup --copy-back --target-dir=/backup/base

??5）還原屬性

chown -R mysql:mysql /var/lib/mysql

??6）啟動服務

systemctl start mariadb

xtrabackup 單表導入導出

1、單表備份

innobackupex --include='hellodb.students' /backups

2、備份表結構

mysql -e 'show create table hellodb.students' > student.sql

3、刪除表

mysql -e 'drop table hellodb.students'

4、

innobackupex --apply-log --export /backups/2019-05-06_20-34-35/

5、創建表

mysql>CREATE TABLE `students` (

`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,

`Name` varchar(50) NOT NULL,

`Age` tinyint(3) unsigned NOT NULL,

`Gender` enum('F','M') NOT NULL,

`ClassID` tinyint(3) unsigned DEFAULT NULL,

`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8

6、刪除表空間

alter table students discard tablespace;

7、復制

cp /backups/2019-05-06_20-34-35/hellodb/students.{cfg,exp,ibd} /var/lib/mysql/hellodb/

8、添加屬組權限

chown -R mysql.mysql /var/lib/mysql/hellodb/

9 、

mysql>alter table students import tablespace;