逻辑备份: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命令就可以直接使用了。
[root@dk-14 ~]# 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)
此时，我们对数据库发起全库备份。
[root@dk-14 ~]# mkdir backup
[root@dk-14 ~]# cd backup/
[root@dk-14 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.
[root@dk-14 backup]# ls
all_back.sql
--single-transaction：此选项会对InnoDB表开启一个一致性快照，备份InnoDB表时不会锁表，对其他引擎的表无效。
--set-gtid-purged=OFF：在开启GTID的情况下，此选项会决定是否会在备份文件头部添加set global gtid_purged的语句，ON为添加，OFF为不添加，一般备份用于搭建从库的时候会设为ON。
备份完成后，会生成一个备份文件，我们可以查看一下备份文件的内容。
[root@dk-14 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 @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 */;
--
-- 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的数据，然后利用备份文件进行恢复。
[root@dk-14 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表进行备份。
[root@dk-14 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.
[root@dk-14 backup]# ls
all_back.sql t1.sql
查看一下备份文件的内容。
[root@dk-14 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 @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 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 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 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表的备份文件进行一次数据恢复。
[root@dk-14 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安装包进行安装。
[root@dk-14 tool]# tar -xf xtrabackup-2.4.4.tar.gz
[root@dk-14 tool]# mv xtrabackup244/ /usr/local/xtrabackup
[root@dk-14 tool]# ln -s /usr/local/xtrabackup/bin/innobackupex /usr/local/bin/
[root@dk-14 tool]# innobackupex --version
innobackupex version 2.4.4 Linux (x86_64) (revision id: df58cf2)
安装好XtraBackup后，我们对数据库做一次备份，数据就使用mysqldump篇遗留的数据，备份命令如下。
[root@dk-14 ~]# 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目录下查看备份生成的文件
[root@dk-14 ~]# ls backup/
2020-02-05_08-56-05
[root@dk-14 ~]# cd backup/2020-02-05_08-56-05/
[root@dk-14 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，备份时可以执行下面所示的命令。
[root@dk-14 ~]# 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文件，对备份期间进入的事务进行前滚，使数据库内的数据保持同一时间点。
[root@dk-14 3306]# innobackupex --apply-log /root/backup/2020-02-05_08-56-05/
apply log完成后，我们将丢失数据的数据库清掉，并移走数据文件,创建新的数据目录。
[root@dk-14 3306]# mysqladmin -uroot -p12345678 -S /tmp/mysql-3306.sock shutdown
[root@dk-14 3306]# cd /dbase/
[root@dk-14 dbase]# mv 3306 3306bak
[root@dk-14 dbase]# mkdir 3306
[root@dk-14 dbase]# cd 3306/
[root@dk-14 3306]# mkdir binlog data logs redo relaylog tmp undo
[root@dk-14 3306]# **chown -R mysql:mysql ***
执行恢复命令。
[root@dk-14 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。
恢复完成后，修改一下数据文件的权限，将数据库启动起来。
[root@dk-14 3306]# touch logs/err.log
[root@dk-14 3306]# **chown -R mysql:mysql ***
[root@dk-14 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的增量备份的功能。
增量备份也叫差异备份，就是在全量备份的基础上，将发起增量备份时刻和上次备份时产生改变的数据备份起来，和全量备份组成一份最新的数据。
下面我们演示一下增量备份的过程。
首先我们对数据库做一次全量备份。
[root@dk-14 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的基础上对数据库发起增量备份。
[root@dk-14 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的基础上再做一次增量。
[root@dk-14 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
查看备份的目录，可以看到以下文件夹。
[root@dk-14 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日志。
[root@dk-14 3306]# innobackupex --apply-log --redo-only
/root/backup/all_backup
[root@dk-14 3306]# innobackupex --apply-log --redo-only --incremental /root/backup/all_backup --incremental-dir=/root/backup/inc_backup_1/
[root@dk-14 3306]# innobackupex --apply-log --incremental /root/backup/all_backup --incremental-dir=/root/backup/inc_backup_2/
--redo-only是指在应用redo日志时有未提交的事务不做回滚，当后面还有其他增量备份要附加的时候需要加上这个参数。
应用完redo日志后，我们将丢失数据的数据库清掉，并移走数据文件,创建新的数据目录。
[root@dk-14 3306]# mysqladmin -uroot -p12345678 -S /tmp/mysql-3306.sock shutdown
[root@dk-14 3306]# cd /dbase/
[root@dk-14 dbase]# mv 3306 3306bak
[root@dk-14 dbase]# mkdir 3306
[root@dk-14 dbase]# cd 3306/
[root@dk-14 3306]# mkdir binlog data logs redo relaylog tmp undo
[root@dk-14 3306]# **chown -R mysql:mysql ***
执行恢复命令。
[root@dk-14 3306]# innobackupex --defaults-file=/etc/mysql/my-3306.cnf --copy-back /root/backup/all_backup
恢复完成后，修改一下数据文件的权限，将数据库启动起来。
[root@dk-14 3306]# touch logs/err.log
[root@dk-14 3306]# **chown -R mysql:mysql ***
[root@dk-14 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来进行数据补偿的恢复。
