MySQL Xtrabackup物理增量备份实践

1.介绍

Xtrabackup命令用于InnoDB与XtraDB等事务引擎数据库备份，不能用于MyISAM等非事务引擎数据库。其特点是备份数据库时完全不用锁表。

Innobackupex可备份InnoDB与XtraDB等事务引擎数据库外，还可以备份MyISAM以及多种引擎混合使用的场景。其特点是是备份事务引擎数据库时完全不用锁表，备份非事务引擎数据库需要锁表。

2.基本用法

Innobackupex [–defaults-file=file] [–user=name] [–host=name] [–password=word] [–port=port] [–socket=socket] path

3.安装

[root@localhost ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@localhost ~]# yum list |grep xtrabackup
percona-xtrabackup-24.x86_64             2.4.18-1.el7                  @percona-release-x86_64
holland-xtrabackup.noarch                1.1.20-1.el7                  epel
percona-xtrabackup.x86_64                2.3.10-1.el7                  percona-release-x86_64
percona-xtrabackup-22.x86_64             2.2.13-1.el7                  percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64   2.2.13-1.el7                  percona-release-x86_64
percona-xtrabackup-24-debuginfo.x86_64   2.4.18-1.el7                  percona-release-x86_64
percona-xtrabackup-80.x86_64             8.0.9-1.el7                   percona-release-x86_64
......
[root@localhost ~]# yum install percona-xtrabackup-24.x86_64 -y     #安装2.4版本
[root@localhost ~]# rpm -qa |grep xtrabackup
percona-xtrabackup-24-2.4.18-1.el7.x86_64

4.模拟周六凌晨12点进行一次全备

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=密码 --socket=/tmp/mysql.sock --no-timestamp /root/backup/
[root@CentOS7 ~]# ll backup
total 75824
-rw-r-----. 1 root root      490 Jan 14 21:47 backup-my.cnf
-rw-r-----. 1 root root      853 Jan 14 21:47 ib_buffer_pool
-rw-r-----. 1 root root 77594624 Jan 14 21:47 ibdata1
drwxr-x---. 2 root root     4096 Jan 14 21:47 mysql
drwxr-x---. 2 root root     4096 Jan 14 21:47 performance_schema
drwxr-x---. 2 root root    12288 Jan 14 21:47 sys
drwxr-x---. 2 root root     4096 Jan 14 21:47 test
-rw-r-----. 1 root root       22 Jan 14 21:47 xtrabackup_binlog_info
-rw-r-----. 1 root root      135 Jan 14 21:47 xtrabackup_checkpoints
-rw-r-----. 1 root root      540 Jan 14 21:47 xtrabackup_info
-rw-r-----. 1 root root     2560 Jan 14 21:47 xtrabackup_logfile
[root@CentOS7 ~]#

5.模拟全备之后，数据持续写入

[root@CentOS7 ~]#  mysql -e "use test;insert into tab_test values(4,'four'),(5,'five');"
[root@CentOS7 ~]# mysql -e "select * from test.tab_test;"
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
+----+-------+

6.模拟周日凌晨进行第一次增量备份

[root@localhost ~]#  innobackupex --defaults-file=/etc/my.cnf  --user=root --password=密码 --socket=/tmp/mysql.sock --no-timestamp --incremental-basedir=/root/backup/ --incremental /root/increase
[root@CentOS7 ~]# ll increase/
total 372
-rw-r-----. 1 root root    490 Jan 14 21:53 backup-my.cnf
-rw-r-----. 1 root root    853 Jan 14 21:53 ib_buffer_pool
-rw-r-----. 1 root root 327680 Jan 14 21:53 ibdata1.delta
-rw-r-----. 1 root root     60 Jan 14 21:53 ibdata1.meta
drwxr-x---. 2 root root   4096 Jan 14 21:53 mysql
drwxr-x---. 2 root root   4096 Jan 14 21:53 performance_schema
drwxr-x---. 2 root root  12288 Jan 14 21:53 sys
drwxr-x---. 2 root root   4096 Jan 14 21:53 test
-rw-r-----. 1 root root     22 Jan 14 21:53 xtrabackup_binlog_info
-rw-r-----. 1 root root    139 Jan 14 21:53 xtrabackup_checkpoints
-rw-r-----. 1 root root    597 Jan 14 21:53 xtrabackup_info
-rw-r-----. 1 root root   2560 Jan 14 21:53 xtrabackup_logfile
[root@CentOS7 ~]#

7.模拟第一次增量备份后，数据持续写入

[root@CentOS7 ~]#  mysql -e "use test;insert into tab_test values(8,'eight');"
[root@CentOS7 ~]# mysql -e "select * from test.tab_test;"
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  8 | eight |
+----+-------+

8.模拟周一凌晨第二次增量备份，路径基于第一次增量备份路径

[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf  --user=root --password=密码 --socket=/tmp/mysql.sock --no-timestamp --incremental-basedir=/root/increase --incremental  /root/increase-two
[root@CentOS7 ~]# ll increase-two/
total 148
-rw-r-----. 1 root root   490 Jan 14 21:55 backup-my.cnf
-rw-r-----. 1 root root   853 Jan 14 21:55 ib_buffer_pool
-rw-r-----. 1 root root 98304 Jan 14 21:55 ibdata1.delta
-rw-r-----. 1 root root    60 Jan 14 21:55 ibdata1.meta
drwxr-x---. 2 root root  4096 Jan 14 21:55 mysql
drwxr-x---. 2 root root  4096 Jan 14 21:55 performance_schema
drwxr-x---. 2 root root 12288 Jan 14 21:55 sys
drwxr-x---. 2 root root  4096 Jan 14 21:55 test
-rw-r-----. 1 root root    22 Jan 14 21:55 xtrabackup_binlog_info
-rw-r-----. 1 root root   139 Jan 14 21:55 xtrabackup_checkpoints
-rw-r-----. 1 root root   602 Jan 14 21:55 xtrabackup_info
-rw-r-----. 1 root root  2560 Jan 14 21:55 xtrabackup_logfile
[root@CentOS7 ~]#

9.模拟第二次增量备份后，数据持续写入

[root@CentOS7 ~]# mysql -e "use test;insert into tab_test values(10,'ten');"
[root@CentOS7 ~]# mysql -e "select * from test.tab_test;"
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  8 | eight |
| 10 | ten   |
+----+-------+

10.模拟误删除数据库test

[root@CentOS7 ~]#  mysql -e "drop database test;show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
[root@CentOS7 ~]#

11.发现数据库被删除后，立即复制binlog文件，防止二次破坏

[root@CentOS7 ~]# cp -a /data/binlog/* binlog/
[root@CentOS7 ~]# ll binlog/
total 12
-rw-r-----. 1 mysql mysql  177 Jan 14 19:54 mysql-bin.000001
-rw-r-----. 1 mysql mysql 2254 Jan 14 21:57 mysql-bin.000002
-rw-r-----. 1 mysql mysql   60 Jan 14 19:58 mysql-bin.index
[root@CentOS7 ~]#

12.合并全备数据到全备目录

[root@localhost ~]# innobackupex --apply-log --use-memory=32M --redo-only /root/backup/
[root@CentOS7 ~]# ll backup
total 84020
-rw-r-----. 1 root root      490 Jan 14 21:47 backup-my.cnf
-rw-r-----. 1 root root      853 Jan 14 21:47 ib_buffer_pool
-rw-r-----. 1 root root 77594624 Jan 14 21:59 ibdata1
drwxr-x---. 2 root root     4096 Jan 14 21:47 mysql
drwxr-x---. 2 root root     4096 Jan 14 21:47 performance_schema
drwxr-x---. 2 root root    12288 Jan 14 21:47 sys
drwxr-x---. 2 root root     4096 Jan 14 21:47 test
-rw-r-----. 1 root root       22 Jan 14 21:47 xtrabackup_binlog_info
-rw-r--r--. 1 root root       22 Jan 14 21:59 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 root root      133 Jan 14 21:59 xtrabackup_checkpoints
-rw-r-----. 1 root root      540 Jan 14 21:47 xtrabackup_info
-rw-r-----. 1 root root  8388608 Jan 14 21:59 xtrabackup_logfile
-rw-r--r--. 1 root root        1 Jan 14 21:59 xtrabackup_master_key_id
[root@CentOS7 ~]#

13.合并第一次增量备份数据到全备目录

[root@localhost ~]# innobackupex --apply-log --use-memory=32M --redo-only --incremental-dir=/root/increase /root/backup/

14.合并第二次增量备份数据到全备目录，非最后一次合并增量数据一定要加–redo-only，最后一次不用加

[root@localhost ~]# innobackupex --apply-log --use-memory=32M  --incremental-dir=/root/increase-two /root/backup/

15.最后应用所有redo日志，并回滚未提交的数据

[root@localhost ~]# innobackupex --apply-log --use-memory=32M  /root/backup/

16.开始解析binlog日志数据，查看最后一次增量的binlog位置信息

[root@CentOS7 backup]# cat xtrabackup_binlog_info
mysql-bin.000002        1811
[root@CentOS7 backup]#
[root@localhost backup]# mysqlbinlog --no-defaults -d test mysql-bin.000002 --start-position=1811 -r bin.sql

17.开始恢复全备数据以及增量数据

[root@CentOS7 binlog]# /etc/init.d/mysqld stop  #需要停止数据库
Shutting down MySQL.. SUCCESS!
[root@CentOS7 binlog]# ll /data/mysql/
total 1124392
-rw-r-----. 1 mysql mysql        56 Jan 14 19:54 auto.cnf
-rw-r-----. 1 mysql mysql      7932 Jan 14 22:05 error.log
-rw-r-----. 1 mysql mysql       842 Jan 14 22:05 ib_buffer_pool
-rw-r-----. 1 mysql mysql  77594624 Jan 14 22:05 ibdata1
-rw-r-----. 1 mysql mysql 536870912 Jan 14 22:05 ib_logfile0
-rw-r-----. 1 mysql mysql 536870912 Jan 14 19:54 ib_logfile1
drwxr-x---. 2 mysql mysql      4096 Jan 14 19:54 mysql
drwxr-x---. 2 mysql mysql      4096 Jan 14 19:54 performance_schema
drwxr-x---. 2 mysql mysql     12288 Jan 14 19:54 sys
[root@CentOS7 binlog]# mv /data/mysql /data/mysql-bak  #备份原始数据
[root@CentOS7 binlog]# mkdir -p /data/mysql
[root@CentOS7 binlog]# mv /root/backup/* /data/mysql   #将备份数据复制到mysql数据目录
[root@CentOS7 binlog]# chown -R mysql.mysql /data/mysql
[root@CentOS7 binlog]# /etc/init.d/mysqld start 
Starting MySQL.Logging to '/data/mysql/error.log'.
. SUCCESS!
[root@CentOS7 binlog]# mysql -e "select * from test.tab_test;"
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  8 | eight |
+----+-------+

18.开始恢复binlog日志数据

[root@localhost test]# sed -i '/drop database test/d' bin.sql     #删除drop database test，否则恢复数据库后又会将数据库删除
[root@localhost test]# grep -w drop bin.sql
[root@CentOS7 binlog]# mysql <bin.sql
[root@CentOS7 binlog]# mysql -e "select * from test.tab_test;"
+----+-------+
| id | name  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  8 | eight |
| 10 | ten   |
+----+-------+


#数据恢复完成

#######################

二、分库分表备份

参数与格式：

--databases="库名"

--databases="库名.表名"

--databases="test.tab_test test.tab_test1"

物理分库分表备份恢复时，需要用--export来做恢复，另外需要初始化数据库

innobackupex --apply-log --export /root/backup/test

「文章如果对你有帮助，请点个赞哦^^ 」

若无特殊注明，文章均为本站原创或整理发布。

转载请注明本文地址：https://om.fangxiaoxiong.com/1835.html