逻辑备份: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指令就能够直接运用了。

[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来进行数据补偿的康复。

逻辑备份:mysqldump vs物理备份:XtraBackup