xtrabackup实现全量+增量+binlog恢复库

#一、利用xtrabackup实现完全备份及还原 

1.下载并安装xtrabackup包 
[root@centos8 ~]#wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.23/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
[root@localhost ~]# yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm

2.安装mariadb数据库并启动服务
[root@localhost ~]# yum install -y mariadb-server
[root@localhost ~]# systemctl enable mariadb --now
[root@localhost ~]# mysql_secure_installation（初始化设置，设置root密码为123456，移除匿名用户等）

3.在原主机做完全备份到/backup目录
[root@localhost ~]# mkdir /backup
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
[root@localhost ~]# ls /backup/base/
backup-my.cnf  mysql               xtrabackup_checkpoints  xtrabackup_logfile
ibdata1        performance_schema  xtrabackup_info

4.目标主机无需创建/backup目录,直接复制目录本身
[root@centos7 ~]#scp -r /backup/ 目标主机:/backup1

5.删除数据库数据模拟目标主机
[root@localhost ~]# systemctl stop mariadb
[root@localhost ~]# rm -rf /var/lib/mysql/*

6.还原数据
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/base

2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@localhost ~]# ll /var/lib/mysql/
total 40976
-rw-r----- 1 root root 18874368 Nov  9 16:47 ibdata1
-rw-r----- 1 root root  5242880 Nov  9 16:47 ib_logfile0
-rw-r----- 1 root root  5242880 Nov  9 16:47 ib_logfile1
-rw-r----- 1 root root 12582912 Nov  9 16:47 ibtmp1
drwxr-x--- 2 root root     4096 Nov  9 16:47 mysql
drwxr-x--- 2 root root     4096 Nov  9 16:47 performance_schema
-rw-r----- 1 root root      440 Nov  9 16:47 xtrabackup_info
-rw-r----- 1 root root        1 Nov  9 16:47 xtrabackup_master_key_id

3）还原属性
[root@centos7 ~]#chown -R mysql:mysql /var/lib/mysql
[root@localhost ~]# ll /var/lib/mysql/
total 40976
-rw-r----- 1 mysql mysql 18874368 Nov  9 16:47 ibdata1
-rw-r----- 1 mysql mysql  5242880 Nov  9 16:47 ib_logfile0
-rw-r----- 1 mysql mysql  5242880 Nov  9 16:47 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Nov  9 16:47 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Nov  9 16:47 mysql
drwxr-x--- 2 mysql mysql     4096 Nov  9 16:47 performance_schema
-rw-r----- 1 mysql mysql      440 Nov  9 16:47 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Nov  9 16:47 xtrabackup_master_key_id

4）启动服务
[root@centos7 ~]#systemctl start  mariadb

7.验证数据恢复成功
[root@localhost ~]# mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)


#二、完全，增量+binlog还原
#完全备份
[root@centos7 ~]#yum install -y percona-xtrabackup-24-2.4.23-1.el7.x86_64.rpm
[root@centos7 ~]#mkdir /backup/
[root@centos7 ~]#xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
#新增数据
MariaDB [(none)]> create database testdb
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

#第一次增量备份
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

#第二次新增数据
MariaDB [(none)]> create database testdb2
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb2            |
+--------------------+
5 rows in set (0.00 sec)

#第二次增量备份
[root@localhost ~]# xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

#二次增量备份后查看二进制位置，防止二次备份后再次有少量数据写入，这时就需要二进制日志来恢复数据
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       1645 |
+--------------------+-----------+
1 row in set (0.00 sec)

#第二次增备后新增数据：
MariaDB [(none)]> create database testdb3
    -> ;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb2            |
| testdb3            |
+--------------------+
6 rows in set (0.00 sec)
#拷贝binlog日志留作恢复
[root@localhost mysql]# cp -p /var/lib/mysql/mariadb-bin.000001  /backup/


#拷贝到还原主机
 [root@localhost ~]#scp -r /backup/ 目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2} 

#数据还原过程：（数据库未启动状态）
1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base
2）合并第1次增量备份到完全备份
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/base --incrementaldir=/backup/inc2
4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/base
5）还原属性： 
[root@localhost ~]# chown -R  mysql:mysql /var/lib/mysql/
 6）启动服务：
[root@localhost ~]# systemctl start mariadb

#截至第二次增备的数据都已经恢复，但是第二次增备后原数据库还有可能会写入数据，最后的少量数据使用binlog恢复
[root@localhost mysql]# cat /backup/inc2/xtrabackup_binlog_info
mariadb-bin.000001      1645
#查看二进制文件，从1645开始，并导入到sql文件
[root@localhost ~]# mysqlbinlog  /backup/mariadb-bin.000001 --start-position=1645 >/backup/binlog.sql

#临时关闭binlog记录功能，导入sql文件，再开机binlog功能
MariaDB [(none)]> set sql_log_bin=0;
MariaDB [(none)]> source /backup/binlog.sql
MariaDB [(none)]> set sql_log_bin=1;
#验证
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| testdb             |
| testdb2            |
| testdb3            |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

本栏目推荐文章
• dremio jdbc 插件时间增量处理的一些问题
• Logstash全量、增量数据同步
• MySQL数据库 （1 增量添加列和alter的用法）
• unbutun安装xtrabackup
• MySQL备份之xtrabackup（Python）
• 【Xtrabackup】Xtrabackup全备、增量备份及恢复示例
• MySQL三大日志(binlog、redo log和undo log)详解
• oracle 增量导入导出参考链接
• mysql-binlog恢复命令
• kettle使用时间戳增量回滚同步，实现增删改数据同步

增量 xtrabackup binlog增量xtrabackup binlog xtrabackup增量 示例 备份 增量xtrabackup备份docker 数据备份 增量xtrabackup备份 增量xtrabackup mariadb数据库 xtrabackup xtrabackup组件 xtrabackup备份 percona-xtrabackup xtrabackup8