邏輯備份:mysqldump vs物理備份:XtraBackup_沃趣資料庫私有云

邏輯備份:mysqldump

mysqldump是MySQL官方提供的一款邏輯備份的工具，其備份原理是生成一組可以匯入資料庫中以重現原始資料庫中的資料和資料庫物件的SQL語句，然後用SQL語句對資料庫進行恢復，因此稱它為邏輯備份，另外它還支援生成CSV格式或者XML格式的檔案。

使用mysqldump進行備份時是需要一定的許可權的，備份表資料需要對錶的SELECT許可權，匯出檢視需要SHOW VIEW許可權，匯出觸發器需要TRIGGER許可權，在不使用--single-transaction選項進行鎖表時需要LOCK TABLES許可權。如果使用更多的選項，可能就需要有更多的許可權。

另外，如果我們需要用備份檔案進行恢復時，則必須具有執行這個檔案中所有語句的許可權，例如執行CREATE語句就需要有相應物件的CREATE許可權，執行LOCK TABLES語句時需要有LOCK TABLES許可權。

下面我們演示一下mysqldump的用法。

mysqldump是MySQL的原生命令，一般我們安裝完MySQL後，mysqldump命令就可以直接使用了。

[[email protected] ~]# mysqldump --version

mysqldump Ver 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)

首先進行一下全庫備份的演示，首先我們在將要備份的庫上製造一些測試資料。

mysql> SHOW DATABASES;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

mysql> CREATE DATABASE aa;

Query OK, 1 row affected (0.04 sec)

mysql> USE aa

Database changed

mysql> CREATE TABLE t1(id INT PRIMARY KEY,name VARCHAR(20));

Query OK, 0 rows affected (0.12 sec)

mysql> CREATE TABLE t2(id INT PRIMARY KEY,name VARCHAR(20));

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES(1,'aa'),(2,'bb'),(3,'cc'), **(4,'dd'),(5,'ee');

Query OK, 5 rows affected (0.11 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t2 VALUES(1,'aaa'),(2,'bbb'),(3,'ccc'),

**(4,'ddd'),(5,'eee');

Query OK, 5 rows affected (0.00 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

5 rows in set (0.00 sec)

此時，我們對資料庫發起全庫備份。

[[email protected] ~]# mkdir backup

[[email protected] ~]# cd backup/

[[email protected] backup]# mysqldump -uroot -p12345678 -S /tmp/mysql-3306.sock --single-transaction --set-gtid-purged=OFF --all-databases > all_back.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[[email protected] backup]# ls

all_back.sql

--single-transaction：此選項會對InnoDB表開啟一個一致性快照，備份InnoDB表時不會鎖表，對其他引擎的表無效。

--set-gtid-purged=OFF：在開啟GTID的情況下，此選項會決定是否會在備份檔案頭部新增set global gtid_purged的語句，ON為新增，OFF為不新增，一般備份用於搭建從庫的時候會設為ON。

備份完成後，會生成一個備份檔案，我們可以檢視一下備份檔案的內容。

[[email protected] backup]# head -50 all_back.sql

-- MySQL dump 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)

-- Host: localhost Database:

-- Server version 5.7.21-log

/!40101 SET @[email protected]@CHARACTER_SET_CLIENT /;

/!40101 SET @[email protected]@CHARACTER_SET_RESULTS /;

/!40101 SET @[email protected]@COLLATION_CONNECTION /;

/!40101 SET NAMES utf8 /;

/!40103 SET @[email protected]@TIME_ZONE /;

/!40103 SET TIME_ZONE='+00:00' /;

/!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;

/!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;

/!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;

/!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 /;

-- Current Database: aa

CREATE DATABASE /!32312 IF NOT EXISTS/ aa /!40100 DEFAULT CHARACTER SET latin1 /;

USE aa;

-- Table structure for table t1

DROP TABLE IF EXISTS t1;

/!40101 SET @saved_cs_client = @@character_set_client /;

/!40101 SET character_set_client = utf8 /;

CREATE TABLE t1 (

id int(11) NOT NULL,

name varchar(20) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/!40101 SET character_set_client = @saved_cs_client /;

-- Dumping data for table t1

LOCK TABLES t1 WRITE;

/!40000 ALTER TABLE t1 DISABLE KEYS /;

INSERT INTO t1 VALUES (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');

/!40000 ALTER TABLE t1 ENABLE KEYS /;

UNLOCK TABLES;

通過備份檔案的內容，可以看到，檔案裡面儲存的是整個庫重建和重新插入資料的SQL語句，邏輯備份就是通過這種方式實現的。

下面我們製造一下資料丟失的場景，然後測試一下利用備份檔案進行恢復。

mysql> USE aa

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.02 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

5 rows in set (0.00 sec)

mysql> DELETE FROM t1 WHERE id=1;

Query OK, 1 row affected (0.04 sec)

mysql> DELETE FROM t2 WHERE id=1;

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.01 sec)

分別在t1和t2表中刪除id為1的資料，然後利用備份檔案進行恢復。

[[email protected] backup]# mysql -uroot -p12345678 -S /tmp/mysql-3306.sock < all_back.sql

驗證資料是否恢復為我們備份時的內容。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

5 rows in set (0.00 sec)

從上述結果來看，我們刪除的資料已經通過備份恢復了出來。

邏輯備份還可以用於指定表或指定庫的備份恢復，下面我們做一個演示，首先對t1表進行備份。

[[email protected] backup]# mysqldump -uroot -p12345678 -S /tmp/mysql-3306.sock --single-transaction --set-gtid-purged=OFF aa t1 > t1.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

[[email protected] backup]# ls

all_back.sql t1.sql

檢視一下備份檔案的內容。

[[email protected] backup]# cat t1.sql

-- MySQL dump 10.13 Distrib 5.7.21, for linux-glibc2.12 (x86_64)

-- Host: localhost Database: aa

-- Server version 5.7.21-log

/!40101 SET @[email protected]@CHARACTER_SET_CLIENT /;

/!40101 SET @[email protected]@CHARACTER_SET_RESULTS /;

/!40101 SET @[email protected]@COLLATION_CONNECTION /;

/!40101 SET NAMES utf8 /;

/!40103 SET @[email protected]@TIME_ZONE /;

/!40103 SET TIME_ZONE='+00:00' /;

/!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 /;

/!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 /;

/!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' /;

/!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 /;

-- Table structure for table t1

DROP TABLE IF EXISTS t1;

/!40101 SET @saved_cs_client = @@character_set_client /;

/!40101 SET character_set_client = utf8 /;

CREATE TABLE t1 (

id int(11) NOT NULL,

name varchar(20) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/!40101 SET character_set_client = @saved_cs_client /;

-- Dumping data for table t1

LOCK TABLES t1 WRITE;

/!40000 ALTER TABLE t1 DISABLE KEYS /;

INSERT INTO t1 VALUES (1,'aa'),(2,'bb'),(3,'cc'),(4,'dd'),(5,'ee');

/!40000 ALTER TABLE t1 ENABLE KEYS /;

UNLOCK TABLES;

/!40103 SET [email protected]_TIME_ZONE /;

/!40101 SET [email protected]_SQL_MODE /;

/!40014 SET [email protected]_FOREIGN_KEY_CHECKS /;

/!40014 SET [email protected]_UNIQUE_CHECKS /;

/!40101 SET [email protected]_CHARACTER_SET_CLIENT /;

/!40101 SET [email protected]_CHARACTER_SET_RESULTS /;

/!40101 SET [email protected]_COLLATION_CONNECTION /;

/!40111 SET [email protected]_SQL_NOTES /;

-- Dump completed on 2020-02-04 2:42:23

可以看到，備份檔案中是重建t1表和重新往t1表插入資料的SQL。

然後我們手動刪除一些資料。

mysql> USE aa

Database changed

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 1 | aaa |

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

5 rows in set (0.00 sec)

mysql> DELETE FROM t1 WHERE id=1;

Query OK, 1 row affected (0.01 sec)

mysql> DELETE FROM t2 WHERE id=1;

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.00 sec)

利用t1表的備份檔案進行一次資料恢復。

[[email protected] backup]# mysql -uroot -p12345678 -S /tmp/mysql-3306.sock aa < t1.sql

我們到庫中檢視一下恢復情況。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.01 sec)

可以看到t1表的資料已經正常恢復出來，t2表的資料並沒有恢復，符合我們只對t1表進行備份恢復的預期。

物理備份:XtraBackup

XtraBackup是Pecona公司的一款開源免費的MySQL備份軟體，是目前最流行的MySQL備份軟體之一，可以非阻塞的對InnoDB和XtraDB資料庫進行備份。其備份原理是通過備份資料庫的物理檔案，最後通過應用redo日誌來讓資料保持同一時間點，因為操作的是物理檔案，所以稱之為物理備份。

XtraBackup至少需要PROCESS,RELOAD,LOCK TABLES,REPLICATION CLIENT幾個許可權，PROCESS用於檢視MySQL相關連線的程序，RELOAD和LOCK TABLES用於執行FLUSH TABLES WITH READ LOCK、FLUSH ENGINE LOGS，REPLICATION CLIENT用於檢視二進位制日誌當前寫到了哪個位置。

下面我們演示一下XtraBackup的用法

首先，我們到percona的官網下載XtraBack的安裝包，下載地址如下。

Download Percona XtraBackup 2.4

我們選擇2.4.4版本的進行演示。

首先將下載好的XtraBackup安裝包進行安裝。

[[email protected] tool]# tar -xf xtrabackup-2.4.4.tar.gz

[[email protected] tool]# mv xtrabackup244/ /usr/local/xtrabackup

[[email protected] tool]# ln -s /usr/local/xtrabackup/bin/innobackupex /usr/local/bin/

[[email protected] tool]# innobackupex --version

innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)

安裝好XtraBackup後，我們對資料庫做一次備份，資料就使用mysqldump篇遺留的資料，備份命令如下。

[[email protected] ~]# innobackupex --user=root --password=12345678 --socket=/tmp/mysql-3306.sock /root/backup/

當備份成功後，螢幕上會打印出completed OK的字樣，如下所示

200205 08:56:07 [00] Copying ib_buffer_pool to /root/backup/2020-02-05_08-56-05/ib_buffer_pool

200205 08:56:07 [00] ...done

200205 08:56:07 Backup created in directory '/root/backup/2020-02-05_08-56-05'

MySQL binlog position: filename 'mysql-bin.000002', position '777218', GTID of the last change 'ac3ef50f-17fd-11ea-9f95-0242ac12000e:1-170'

200205 08:56:07 [00] Writing backup-my.cnf

200205 08:56:07 [00] ...done

200205 08:56:07 [00] Writing xtrabackup_info

200205 08:56:07 [00] ...done

xtrabackup: Transaction log of lsn (3845814) to (3845823) was copied.

200205 08:56:07 completed OK!

去/root/backup目錄下檢視備份生成的檔案

[[email protected] ~]# ls backup/

2020-02-05_08-56-05

[[email protected] ~]# cd backup/2020-02-05_08-56-05/

[[email protected] 2020-02-05_08-56-05]# ls

aa ibdata1 sys undo003 xtrabackup_info

backup-my.cnf mysql undo001 xtrabackup_binlog_info xtrabackup_logfile

ib_buffer_pool performance_schema undo002 xtrabackup_checkpoints

可以看到backup目錄下有一個時間戳命名的資料夾，這個就是XtraBackup生成的存放備份檔案的目錄，如果我們不想要時間戳命名的資料夾，可以使用--no-timestamp引數，然後自定義目錄名稱就可以了，例如我們想將備份檔案儲存為all_backup，備份時可以執行下面所示的命令。

[[email protected] ~]# innobackupex --user=root --password=12345678

--socket=/tmp/mysql-3306.sock -no-timestamp /root/backup/all_backup

進入到存放備份檔案的目錄，可以看到XtraBackup實際上就是將資料庫的物理檔案copy了一份，包括undo資訊以及備份期間生成的redo檔案(xtrabackup_logfile)，用來對備份期間進入的事務進行前滾，使資料庫內的資料保持同一時間點。

另外還有xtrabackup_info、xtrabackup_binlog_info、xtrabackup_checkpoints等幾個XtraBackup生成的檔案，用於記錄資料庫的binlog位置以及檢查點等資訊，可用於搭建從庫。

下面我們再資料庫裡面刪除一些資料，然後利用備份恢復一下。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.01 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.00 sec)

mysql> DELETE FROM t1 WHERE id=5;

Query OK, 1 row affected (0.03 sec)

mysql> DELETE FROM t2 WHERE id=5;

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

+----+------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

+----+------+

3 rows in set (0.00 sec)

刪除t1、t2表id為5的資料後，我們利用備份進行恢復一下。

首先對備份集進行apply log，這個操作的目的就是利用xtrabackup_logfile檔案，對備份期間進入的事務進行前滾，使資料庫內的資料保持同一時間點。

[[email protected] 3306]# innobackupex --apply-log /root/backup/2020-02-05_08-56-05/

apply log完成後，我們將丟失資料的資料庫清掉，並移走資料檔案,建立新的資料目錄。

[[email protected] 3306]# mysqladmin -uroot -p12345678 -S /tmp/mysql-3306.sock shutdown

[[email protected] 3306]# cd /dbase/

[[email protected] dbase]# mv 3306 3306bak

[[email protected] dbase]# mkdir 3306

[[email protected] dbase]# cd 3306/

[[email protected] 3306]# mkdir binlog data logs redo relaylog tmp undo

[[email protected] 3306]# chown -R mysql:mysql *

執行恢復命令。

[[email protected] 3306]# innobackupex --defaults-file=/etc/mysql/my-3306.cnf --copy-back /root/backup/2020-02-05_08-56-05/

恢復分為move-back和copy-back兩種，move back是將資料檔案移動到資料目錄，copy back是將資料檔案拷貝到資料目錄，上面使用的是copy back。

恢復完成後，修改一下資料檔案的許可權，將資料庫啟動起來。

[[email protected] 3306]# touch logs/err.log

[[email protected] 3306]# chown -R mysql:mysql *

[[email protected] 3306]# mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf --user=mysql &

驗證一下資料是否恢復。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.00 sec)

可以看到資料已經恢復到我們刪除資料之前的樣子。

上面講述的是XtraBackup全量備份的過程，但是生產中會存在一些資料量比較大的資料庫，如果經常進行全量備份會給資料庫帶來額外的壓力，為了減輕這種情況，我們可以使用XtraBackup的增量備份的功能。

增量備份也叫差異備份，就是在全量備份的基礎上，將發起增量備份時刻和上次備份時產生改變的資料備份起來，和全量備份組成一份最新的資料。

下面我們演示一下增量備份的過程。

首先我們對資料庫做一次全量備份。

[[email protected] 3306]# innobackupex --user=root --password=12345678

--socket=/tmp/mysql-3306.sock --no-timestamp /root/backup/all_backup

然後我們往資料庫裡面插入一些資料，模擬增量資料。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

+----+------+

5 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

+----+------+

4 rows in set (0.01 sec)

mysql> INSERT INTO t1 VALUES(6,'ff'),(7,'gg');

Query OK, 2 rows affected (0.05 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t2 VALUES (6,'ff'),(7,'gg');

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

| 6 | ff |

| 7 | gg |

+----+------+

7 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

| 6 | ff |

| 7 | gg |

+----+------+

6 rows in set (0.00 sec)

插入增量資料後，我們在all_backup的基礎上對資料庫發起增量備份。

[[email protected] 3306]# innobackupex --user=root --password=12345678 --socket=/tmp/mysql-3306.sock --incremental-basedir=/root/backup/all_backup --incremental --no-timestamp /root/backup/inc_backup_1

然後再進行一次增量資料的模擬。

mysql> INSERT INTO t1 VALUES (8,'hh'),(9,'ii');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> INSERT INTO t2 VALUES (8,'hh'),(9,'ii');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

| 9 | ii |

+----+------+

9 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

| 9 | ii |

+----+------+

8 rows in set (0.00 sec)

然後再在增量備份inc_backup_1的基礎上再做一次增量。

[[email protected] 3306]# innobackupex --user=root --password=12345678 --socket=/tmp/mysql-3306.sock --incremental-basedir=/root/backup/inc_backup_1 --incremental --no-timestamp /root/backup/inc_backup_2

檢視備份的目錄，可以看到以下資料夾。

[[email protected] 3306]# ls /root/backup/

all_backup inc_backup_1 inc_backup_2

all_backup、inc_backup_1、inc_backup_2分別代表全量備份、第一次增量備份、第二次增量備份。第一次增量備份包含了id為6、7的增量資料，第二次增量備份包含了id為8、9的增量資料，下面我們演示一下刪除一些資料，然後利用增量備份恢復的過程。

首先刪除一些資料。

mysql> DELETE FROM t1 WHERE id=9;

Query OK, 1 row affected (0.06 sec)

mysql> DELETE FROM t2 WHERE id=9;

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

+----+------+

8 rows in set (0.00 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

+----+------+

7 rows in set (0.00 sec)

然後對備份進行apply log，應用redo日誌。

[[email protected] 3306]# innobackupex --apply-log --redo-only

/root/backup/all_backup

[[email protected] 3306]# innobackupex --apply-log --redo-only --incremental /root/backup/all_backup --incremental-dir=/root/backup/inc_backup_1/

[[email protected] 3306]# innobackupex --apply-log --incremental /root/backup/all_backup --incremental-dir=/root/backup/inc_backup_2/

--redo-only是指在應用redo日誌時有未提交的事務不做回滾，當後面還有其他增量備份要附加的時候需要加上這個引數。

應用完redo日誌後，我們將丟失資料的資料庫清掉，並移走資料檔案,建立新的資料目錄。

[[email protected] 3306]# mysqladmin -uroot -p12345678 -S /tmp/mysql-3306.sock shutdown

[[email protected] 3306]# cd /dbase/

[[email protected] dbase]# mv 3306 3306bak

[[email protected] dbase]# mkdir 3306

[[email protected] dbase]# cd 3306/

[[email protected] 3306]# mkdir binlog data logs redo relaylog tmp undo

[[email protected] 3306]# chown -R mysql:mysql *

執行恢復命令。

[[email protected] 3306]# innobackupex --defaults-file=/etc/mysql/my-3306.cnf --copy-back /root/backup/all_backup

恢復完成後，修改一下資料檔案的許可權，將資料庫啟動起來。

[[email protected] 3306]# touch logs/err.log

[[email protected] 3306]# chown -R mysql:mysql *

[[email protected] 3306]# mysqld_safe --defaults-file=/etc/mysql/my-3306.cnf --user=mysql &

驗證一下資料是否恢復。

mysql> USE aa

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SHOW TABLES;

+--------------+

| Tables_in_aa |

+--------------+

| t1 |

| t2 |

+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM t1;

+----+------+

| id | name |

+----+------+

| 1 | aa |

| 2 | bb |

| 3 | cc |

| 4 | dd |

| 5 | ee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

| 9 | ii |

+----+------+

9 rows in set (0.02 sec)

mysql> SELECT * FROM t2;

+----+------+

| id | name |

+----+------+

| 2 | bbb |

| 3 | ccc |

| 4 | ddd |

| 5 | eee |

| 6 | ff |

| 7 | gg |

| 8 | hh |

| 9 | ii |

+----+------+

8 rows in set (0.00 sec)

可以看到已經恢復出我們刪除資料之前的資料。

總結

本文講述了MySQL資料庫兩種常用的備份恢復的方式，一種是邏輯備份方式mysqldump，一種是物理備份方式XtraBackup。

邏輯備份在備份發起時會先加一個全域性讀鎖，然後先備份非InnoDB表，並對InnoDB表開啟一個一致性快照讀，當非InnoDB表備份完成、備份例項的pos資訊獲取完成、一致性快照開啟成功後，邏輯備份就會釋放掉全域性讀鎖，因此邏輯備份中資料的時間是備份發起的時間。

物理備份則是先copy InnoDB表的ibd檔案，同時copy新生成的redo檔案，copy完成後給資料庫加一個全域性讀鎖，然後去copy非InnoDB表的資料檔案以及frm檔案，copy完成後釋放全域性讀鎖完成備份。最後利用redo前滾資料到備份完成的時刻，因此物理備份中資料的時間是備份結束的時間。

邏輯備份適用於一些資料量較小的資料庫或者是表資料的匯入匯出。物理備份適合資料量較大的資料庫，物理備份又有全量備份和增量備份兩種方式，發起一次全量備份會消耗大量的效能，可以採用全量+增量的方式來進行備份，比如週一做一次全量，週二到週日每天做一次增量。由於增量備份是copy差異的資料，所以對於一些交易量較大的OLTP系統來說，修改的資料量太多，可能不適合增量備份。

另外，由於備份並不是時刻都在進行的，如果我們想恢復資料到兩次備份之間的一個時間點，因為物理備份和邏輯備份都可以記錄備份資料對應的日誌位置，我們便可以在備份的基礎上，靈活的利用binlog來進行資料補償的恢復。