mysql增量备份与还原工具_mysql全备和增量备份以及恢复过程（percona工具）

实验环境

系统环境，内核版本和xtrabackup工具版本

[root@linux-node1 mysql]# cat /etc/redhat-release

CentOS Linux release 7.1.1503 (Core)

[root@linux-node1 mysql]# uname -rm

3.10.0-229.el7.x86_64 x86_64

[root@linux-node1 mysql]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock -e 'select version();'

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

| version() |

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

| 5.6.30-log |

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

[root@linux-node1 mysql]# rpm -qa | grep percona

percona-xtrabackup-2.3.4-1.el7.x86_64

[root@linux-node1 mysql]#

全备和增量备份

先查看当前的数据，t1库的tab1表里只有1和2这2条数据

[root@linux-node1 opt]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 18

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| t1 |

| test |

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

5 rows in set (0.00 sec)

mysql> use t1;

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_t1 |

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

| tab1 |

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

1 row in set (0.00 sec)

mysql> select * from tab1;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 19

Current database: t1

+------+

| id |

+------+

| 1 |

| 2 |

+------+

2 rows in set (0.01 sec)

mysql>

执行第一次全备

[root@linux-node1 ~]# innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock /opt/full/

结尾提示OK

170405 02:00:21 [00] Writing backup-my.cnf

170405 02:00:21 [00] ...done

170405 02:00:21 [00] Writing xtrabackup_info

170405 02:00:21 [00] ...done

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

170405 02:00:21 completed OK!

查看备份的数据

[root@linux-node1 incr]# ll /opt/full/2017-04-05_02-00-19/

total 12316

-rw-r----- 1 root root 387 Apr 5 02:00 backup-my.cnf

-rw-r----- 1 root root 12582912 Apr 5 02:00 ibdata1

drwx------ 2 root root 4096 Apr 5 02:00 mysql

drwx------ 2 root root 4096 Apr 5 02:00 performance_schema

drwx------ 2 root root 49 Apr 5 02:00 t1

drwx------ 2 root root 19 Apr 5 02:00 test

-rw-r----- 1 root root 69 Apr 5 02:00 xtrabackup_binlog_info

-rw-r----- 1 root root 113 Apr 5 02:00 xtrabackup_checkpoints

-rw-r----- 1 root root 603 Apr 5 02:00 xtrabackup_info

-rw-r----- 1 root root 2560 Apr 5 02:00 xtrabackup_logfile

[root@linux-node1 incr]#

添加点数据

[root@linux-node1 ~]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock -e 'insert into t1.tab1 values (4);'

[root@linux-node1 ~]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock -e 'select * from t1.tab1;'

+------+

| id |

+------+

| 1 |

| 2 |

| 4 |

+------+

[root@linux-node1 ~]#

执行第1次增量备份,在第1次全备的基础上执行增量

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --incremental /opt/incr/ --incremental-basedir=/opt/full/2017-04-05_02-00-19/

查看第1次增量 备份目录

[root@linux-node1 incr]# ll /opt/incr/

total 4

drwx------ 6 root root 4096 Apr 5 02:02 2017-04-05_02-02-08

[root@linux-node1 incr]#

[root@linux-node1 incr]# ll /opt/incr/2017-04-05_02-02-08/

total 160

-rw-r----- 1 root root 387 Apr 5 02:02 backup-my.cnf

-rw-r----- 1 root root 131072 Apr 5 02:02 ibdata1.delta

-rw-r----- 1 root root 44 Apr 5 02:02 ibdata1.meta

drwx------ 2 root root 4096 Apr 5 02:02 mysql

drwx------ 2 root root 4096 Apr 5 02:02 performance_schema

drwx------ 2 root root 75 Apr 5 02:02 t1

drwx------ 2 root root 19 Apr 5 02:02 test

-rw-r----- 1 root root 69 Apr 5 02:02 xtrabackup_binlog_info

-rw-r----- 1 root root 117 Apr 5 02:02 xtrabackup_checkpoints

-rw-r----- 1 root root 676 Apr 5 02:02 xtrabackup_info

-rw-r----- 1 root root 2560 Apr 5 02:02 xtrabackup_logfile

[root@linux-node1 incr]#

继续插入一条数据

[root@linux-node1 ~]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock -e 'insert into t1.tab1 values (5);'

[root@linux-node1 ~]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock -e 'select * from t1.tab1;'

+------+

| id |

+------+

| 1 |

| 2 |

| 4 |

| 5 |

+------+

[root@linux-node1 ~]#

执行第2次增量备份(在第一次增量的基础上)

这里只须要把最后的目录改为第一次增量备份的数据目录即可

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --incremental /opt/incr/ --incremental-basedir=/opt/incr/2017-04-05_02-02-08/

查看第2次的增量目录

[root@linux-node1 ~]# ll /opt/incr/

total 8

drwx------ 6 root root 4096 Apr 5 02:02 2017-04-05_02-02-08

drwx------ 6 root root 4096 Apr 5 02:03 2017-04-05_02-03-42

[root@linux-node1 ~]# ll /opt/incr/2017-04-05_02-03-42/

total 128

-rw-r----- 1 root root 387 Apr 5 02:03 backup-my.cnf

-rw-r----- 1 root root 98304 Apr 5 02:03 ibdata1.delta

-rw-r----- 1 root root 44 Apr 5 02:03 ibdata1.meta

drwx------ 2 root root 4096 Apr 5 02:03 mysql

drwx------ 2 root root 4096 Apr 5 02:03 performance_schema

drwx------ 2 root root 75 Apr 5 02:03 t1

drwx------ 2 root root 19 Apr 5 02:03 test

-rw-r----- 1 root root 69 Apr 5 02:03 xtrabackup_binlog_info

-rw-r----- 1 root root 117 Apr 5 02:03 xtrabackup_checkpoints

-rw-r----- 1 root root 676 Apr 5 02:03 xtrabackup_info

-rw-r----- 1 root root 2560 Apr 5 02:03 xtrabackup_logfile

[root@linux-node1 ~]#

模拟故障场景，数据磁盘严重损坏。文件无法修复

[root@linux-node1 ~]# cd /usr/local/mysql/data/

[root@linux-node1 data]# ll

total 110724

-rw-rw---- 1 mysql mysql 56 Apr 4 15:25 auto.cnf

-rw-rw---- 1 mysql mysql 81970 Apr 5 01:59 error.log

-rw-rw---- 1 mysql mysql 12582912 Apr 5 02:03 ibdata1

-rw-rw---- 1 mysql mysql 50331648 Apr 5 02:03 ib_logfile0

-rw-rw---- 1 mysql mysql 50331648 Apr 4 14:42 ib_logfile1

-rw-rw---- 1 mysql mysql 174 Apr 4 15:34 linux-node1-bin.000001

-rw-rw---- 1 mysql mysql 2457 Apr 5 01:33 linux-node1-bin.000002

-rw-rw---- 1 mysql mysql 244 Apr 5 01:55 linux-node1-bin.000003

-rw-rw---- 1 mysql mysql 214 Apr 5 01:55 linux-node1-bin.000004

-rw-rw---- 1 mysql mysql 931 Apr 5 01:57 linux-node1-bin.000005

-rw-rw---- 1 mysql mysql 655 Apr 5 02:03 linux-node1-bin.000006

-rw-rw---- 1 mysql mysql 150 Apr 5 01:59 linux-node1-bin.index

-rw-rw---- 1 mysql mysql 6 Apr 4 16:54 linux-node1.nmap.com.pid

-rw-rw---- 1 mysql mysql 6 Apr 5 01:59 linux-node1.pid

drwx------ 2 mysql mysql 4096 Apr 4 14:42 mysql

drwx------ 2 mysql mysql 4096 Apr 4 14:42 performance_schema

drwx------ 2 mysql mysql 49 Apr 4 17:11 t1

drwx------ 2 mysql mysql 6 Apr 4 14:42 test

[root@linux-node1 data]#

[root@linux-node1 data]# mkdir /data-tmp/

[root@linux-node1 data]# mv * /data-tmp/

[root@linux-node1 data]# ll

total 0

[root@linux-node1 data]#

利用备份恢复过程

利用全部备份和增量备份恢复数据

停止mysql进程

[root@linux-node1 data]# /usr/local/mysql/bin/mysqladmin shutdown -S /usr/local/mysql/mysql.sock

[root@linux-node1 data]# ps -ef | grep mysql

root 40382 40009 0 01:33 pts/0 00:00:00 grep --colour=auto mysql

[root@linux-node1 data]#

先应用全备日志，

(1)在备份完成后， 数据尚且不能用于恢复操作， 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。

因此，此时数据文件仍处于不一致状态。–apply-log选项可用于实现此功能

(2)–redo-only是只将已提交的事务同步到数据文件中， 未提交的事务日志不在进行回滚了.可以让那增备之间日志可以无缝衔接起来

(3)--use-memory 可以指定内存大小，加快应用日志的速度

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --apply-log --redo-only --use-memory=1G /opt/full/2017-04-05_02-00-19/

执行结尾部分输出

InnoDB: from the doublewrite buffer...

xtrabackup: Last MySQL binlog file position 908, file name linux-node1-bin.000005

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1645798

170405 02:06:22 completed OK!

[root@linux-node1 ~]#

将全备和第1个增备合并

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --apply-log --redo-only --use-memory=1G \

/opt/full/2017-04-05_02-00-19/ --incremental-dir=/opt/incr/2017-04-05_02-02-08/

执行结尾部分输出

170405 02:07:51 [01] Copying /opt/incr/2017-04-05_02-02-08/test/db.opt to ./test/db.opt

170405 02:07:51 [01] ...done

170405 02:07:51 [00] Copying /opt/incr/2017-04-05_02-02-08//xtrabackup_binlog_info to ./xtrabackup_binlog_info

170405 02:07:51 [00] ...done

170405 02:07:51 [00] Copying /opt/incr/2017-04-05_02-02-08//xtrabackup_info to ./xtrabackup_info

170405 02:07:51 [00] ...done

170405 02:07:51 completed OK!

[root@linux-node1 ~]#

把第2个增备整合到上面已经整合了第一个增量的全备目录中

这里可以把--redo-only 参数取消了。这样未提交的事务被回滚。也符合事务特性

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --apply-log --use-memory=1G \

/opt/full/2017-04-05_02-00-19/ --incremental-dir=/opt/incr/2017-04-05_02-03-42/

执行结尾部分输出

nnoDB: Highest supported file format is Barracuda.

InnoDB: 128 rollback segment(s) are active.

InnoDB: Waiting for purge to start

InnoDB: 5.6.24 started; log sequence number 1646604

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 1646614

170405 02:13:00 completed OK!

[root@linux-node1 ~]#

把整合了全备和2次增量备份的数据恢复到data目录下。要先保证data目录下没有数据文件

innobackupex --defaults-file="/usr/local/mysql/my.cnf" --user=root --socket=/usr/local/mysql/mysql.sock --copy-back /opt/full/2017-04-05_02-00-19/

执行结尾部分输出

170405 02:16:28 [01] Copying ./performance_schema/session_account_connect_attrs.frm to /usr/local/mysql/data/performance_schema/session_account_connect_attrs.frm

170405 02:16:28 [01] ...done

170405 02:16:28 [01] Copying ./test/db.opt to /usr/local/mysql/data/test/db.opt

170405 02:16:28 [01] ...done

170405 02:16:28 [01] Copying ./xtrabackup_binlog_pos_innodb to /usr/local/mysql/data/xtrabackup_binlog_pos_innodb

170405 02:16:28 [01] ...done

170405 02:16:28 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info

170405 02:16:28 [01] ...done

170405 02:16:28 completed OK!

查看文件已经恢复过去了，但是里面的文件属组是root。需要改为mysql用户和组

[root@linux-node1 mysql]# ll data/ -d

drwxr-xr-x 6 mysql mysql 134 Apr 5 02:16 data/

[root@linux-node1 mysql]# ll data/

total 12304

-rw-r----- 1 root root 12582912 Apr 5 02:16 ibdata1

drwx------ 2 root root 4096 Apr 5 02:16 mysql

drwx------ 2 root root 4096 Apr 5 02:16 performance_schema

drwx------ 2 root root 49 Apr 5 02:16 t1

drwx------ 2 root root 19 Apr 5 02:16 test

-rw-r----- 1 root root 27 Apr 5 02:16 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root 676 Apr 5 02:16 xtrabackup_info

[root@linux-node1 mysql]#

改变属组

[root@linux-node1 mysql]# chown -R mysql:mysql data/

[root@linux-node1 mysql]# ll data/

total 12304

-rw-r----- 1 mysql mysql 12582912 Apr 5 02:16 ibdata1

drwx------ 2 mysql mysql 4096 Apr 5 02:16 mysql

drwx------ 2 mysql mysql 4096 Apr 5 02:16 performance_schema

drwx------ 2 mysql mysql 49 Apr 5 02:16 t1

drwx------ 2 mysql mysql 19 Apr 5 02:16 test

-rw-r----- 1 mysql mysql 27 Apr 5 02:16 xtrabackup_binlog_pos_innodb

-rw-r----- 1 mysql mysql 676 Apr 5 02:16 xtrabackup_info

[root@linux-node1 mysql]#

启动mysql

[root@linux-node1 mysql]# /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/my.cnf &

[1] 41106

[root@linux-node1 mysql]# 2017-04-05 02:19:20 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.6.30-log) starting as process 41106 ...

[root@linux-node1 mysql]#

登录检查数据，数据都还在

[root@linux-node1 mysql]# /usr/local/mysql/bin/mysql -S /usr/local/mysql/mysql.sock

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.6.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

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

mysql> select * from t1.tab1;

+------+

| id |

+------+

| 1 |

| 2 |

| 4 |

| 5 |

+------+

4 rows in set (0.01 sec)

mysql>

上述步骤仅仅是简化版的利用全备和增量备份恢复的过程。假如在最后一次增量备份和数据库损坏之间有一些binlog日志，最后还需要把这些binlog恢复了。当然了，这些binlog肯定要在恢复数据之前先备份出来