MySQL基于Percona XtraBackup 实现全备&增量备份与恢复
一、Percona XtraBackup工具简介
Percona XtraBackup(简称PXB)是 Percona 公司开发的一个用于 MySQL 数据库物理热备的备份工具,支持 MySQL(Oracle)、Percona Server 和 MariaDB,并且全部开源。
官网介绍:https://www.percona.com/software/mysql-database/percona-xtrabackup
安装教程:https://www.percona.com/doc/percona-xtrabackup/2.4/installation.html
1.1 Percona XtraBackup工具集
[root@ansible ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/tarball/percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12.tar.gz [root@ansible ~]# tar xvf percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12.tar.gz [root@ansible ~]# mv percona-xtrabackup-2.4.21-Linux-x86_64.glibc2.12 percona-xtrabackup-2.4.21 [root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible percona-xtrabackup-2.4.21]# tree bin/ bin/ ├── innobackupex -> xtrabackup ├── xbcloud ├── xbcloud_osenv ├── xbcrypt ├── xbstream └── xtrabackup 0 directories, 6 files
1、innobackupex # 这个工具在之前的版本中是一个perl脚本,会调用xtrabackup这个二进制工具。从xtrabackup 2.3开始,该工具使用C语言进行了重写,当前它是xtabackup二进制工具的一个软连接,但是实际的使用方法却不同,并且在以后的版本中会删除该工具。 2、xtrabackup # 备份功能的主程序 3、xbcloud, xbcloud_osenv # 是xtrabackup新的高级特性云备份。 4、xbcrypt # 是加解密用的; 5、xbstream # 支持流式备份功能。可以将备份的内容打包并通过管道传递
1.2 Percona XtraBackup备份过程
详情解读参考:阿里数据库内核月报
【注】Percona XtraBackup 支持增量备份,但是只能对InnoDB做增量,MyISAM没有增量备份机制。
二、Percona XtraBackup全量备份
2.1 事前准备
(1)建用户及授权
mysql> CREATE USER 'xtrabackup'@'localhost' IDENTIFIED BY '123456'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT,super ON *.* TO 'xtrabackup'@'localhost'; mysql> FLUSH PRIVILEGES; mysql> show grants for "xtrabackup"@"localhost"; +--------------------------------------------------------------------------------------------------+ | Grants for xtrabackup@localhost | +--------------------------------------------------------------------------------------------------+ | GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost' | +--------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
(2)准备测试数据
测试数据可参考:UCloud MySQL与自建MySQL搭建主从同步中的数据源。
[root@ansible ~]# cd employees_db/ [root@ansible employees_db]# mysql -uroot -h10.25.25.25 -p123456 -t < employees.sql
(3)创建备份目录
[root@ansible ~]# mkdir -p /data/backup/
2.2 全备
[root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible bin]# ./xtrabackup --user=xtrabackup --password="123456" --backup --target-dir=/data/backup/ [root@ansible ~]# ll /data/backup/ #查看备份文件 total 12336 -rw-r----- 1 root root 487 Jul 10 11:33 backup-my.cnf # 备份的配置文件 drwxr-x--- 2 root root 272 Jul 10 11:33 employees -rw-r----- 1 root root 301 Jul 10 11:33 ib_buffer_pool -rw-r----- 1 root root 12582912 Jul 10 11:33 ibdata1 drwxr-x--- 2 root root 4096 Jul 10 11:33 mysql drwxr-x--- 2 root root 8192 Jul 10 11:33 performance_schema drwxr-x--- 2 root root 8192 Jul 10 11:33 sys -rw-r----- 1 root root 141 Jul 10 11:33 xtrabackup_checkpoints # 指明备份类型以及开始与结束的位置 -rw-r----- 1 root root 452 Jul 10 11:33 xtrabackup_info # 记录备份信息 -rw-r----- 1 root root 2560 Jul 10 11:33 xtrabackup_logfile # 备份的日志文件 [root@ansible ~]# cd /data/backup/ [root@ansible backup]# cat xtrabackup_info uuid = a2a803b6-e12f-11eb-8a4f-52540012414e name = tool_name = xtrabackup tool_command = --user=xtrabackup --password=... --backup --target-dir=/data/backup/ tool_version = 2.4.21 ibbackup_version = 2.4.21 server_version = 5.7.31 start_time = 2021-07-10 11:33:42 end_time = 2021-07-10 11:33:47 lock_time = 1 binlog_pos = innodb_from_lsn = 0 innodb_to_lsn = 370569585 partial = N incremental = N format = file compact = N compressed = N encrypted = N [root@ansible backup]# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 370569585 last_lsn = 370569594 compact = 0 recover_binlog_info = 0 flushed_lsn = 370569594
2.3 备份恢复
[root@ansible ~]# systemctl stop mysqld.service # 暂停数据库 [root@ansible ~]# cp -rf /mysql/data/ /mysql/data.bak # 备份MySQL数据目录 [root@ansible ~]# rm -rf /mysql/data/* # 清空MySQL数据目录 # 恢复数据 [root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible percona-xtrabackup-2.4.21]# cd bin/ [root@ansible bin]# ./xtrabackup --copy-back --target-dir=/data/backup # 修改恢复目录的授权 [root@ansible ~]# cd /mysql/ [root@ansible mysql]# chown -R mysql:mysql data/ [root@ansible mysql]# chmod -R 755 data/ [root@ansible ~]# systemctl restart mysqld # 启动MySQL服务器 # 校验数据 [root@ansible ~]# mysql -uroot -p123456 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | employees | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use employees; 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_employees | +---------------------+ | departments | | dept_emp | | dept_manager | | employees | | salaries | | titles | +---------------------+ 6 rows in set (0.00 sec) mysql> select count(*) from employees; +----------+ | count(*) | +----------+ | 300024 | +----------+ 1 row in set (0.12 sec)
三、Percona XtraBackup增量备份
3.1 基于全备的增备
(1)创建备份目录
[root@ansible ~]# mkdir -p /data/backup_incr
(2)插入新数据
mysql> use employees; mysql> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 9 rows in set (0.00 sec) mysql> show create table departments\G *************************** 1. row *************************** Table: departments Create Table: CREATE TABLE `departments` ( `dept_no` char(4) NOT NULL, `dept_name` varchar(40) NOT NULL, PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) # 插入数据 mysql> insert into departments(dept_no,dept_name) values('d010','Network'); Query OK, 1 row affected (0.00 sec) mysql> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d010 | Network | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 10 rows in set (0.00 sec)
(3)基于全备的增备
开始第一次增备,只要全备和多个增备的LSN号连续,那么就可以逐个进行恢复。可以再备份目录xtrabackup_checkpoints文件中看到,其中全备的from_lsn=0,增备的from_lsn应该等于上一个增备或者全备的to_lsn。
[root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible percona-xtrabackup-2.4.21]# cd bin/ [root@ansible bin]# ./xtrabackup --user=xtrabackup --password="123456" --backup --target-dir=/data/backup_incr --incremental-basedir=/data/backup/ [root@ansible bin]# ll /data/backup_incr/ total 136 -rw-r----- 1 root root 487 Jul 10 14:33 backup-my.cnf drwxr-x--- 2 root root 4096 Jul 10 14:33 employees -rw-r----- 1 root root 301 Jul 10 14:33 ib_buffer_pool -rw-r----- 1 root root 81920 Jul 10 14:32 ibdata1.delta -rw-r----- 1 root root 60 Jul 10 14:32 ibdata1.meta drwxr-x--- 2 root root 4096 Jul 10 14:33 mysql drwxr-x--- 2 root root 8192 Jul 10 14:33 performance_schema drwxr-x--- 2 root root 8192 Jul 10 14:33 sys -rw-r----- 1 root root 141 Jul 10 14:33 xtrabackup_checkpoints -rw-r----- 1 root root 498 Jul 10 14:33 xtrabackup_info -rw-r----- 1 root root 2560 Jul 10 14:33 xtrabackup_logfile
【注】--incremental-basedir是上次全备或者增备出来的文件夹。当第一次增备的时候,一般填上次全备,第二次增备的时候,如果--incremental-basedir填上次全备,那么本次增备就会包含上次全备到现在变化的内容。
3.2 基于增量备份的增备
(1)创建备份目录
[root@ansible ~]# mkdir -p /data/backup_all [root@ansible ~]# mkdir -p /data/backup_incr1 [root@ansible ~]# mkdir -p /data/backup_incr2
(2)完成一次全备
mysql> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 10 rows in set (0.00 sec) [root@ansible bin]# ./xtrabackup --user=xtrabackup --password="123456" --backup --target-dir=/data/backup_all [root@ansible bin]# ll /data/backup_all/ total 12336 -rw-r----- 1 root root 487 Jul 10 16:37 backup-my.cnf drwxr-x--- 2 root root 272 Jul 10 16:37 employees -rw-r----- 1 root root 301 Jul 10 16:37 ib_buffer_pool -rw-r----- 1 root root 12582912 Jul 10 16:37 ibdata1 drwxr-x--- 2 root root 4096 Jul 10 16:37 mysql drwxr-x--- 2 root root 8192 Jul 10 16:37 performance_schema drwxr-x--- 2 root root 8192 Jul 10 16:37 sys -rw-r----- 1 root root 135 Jul 10 16:37 xtrabackup_checkpoints -rw-r----- 1 root root 453 Jul 10 16:37 xtrabackup_info -rw-r----- 1 root root 2560 Jul 10 16:37 xtrabackup_logfile [root@ansible bin]# cat /data/backup_all/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 2736437 last_lsn = 2736490 compact = 0 recover_binlog_info = 0 flushed_lsn = 2736446
(3)基于全备的第一次增备
mysql> insert into departments(dept_no,dept_name) values('d010','Network'); Query OK, 1 row affected (0.00 sec) mysql> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d011 | Computer | | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d010 | Network | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 11 rows in set (0.00 sec) [root@ansible bin]# ./xtrabackup --user=xtrabackup --password="123456" --backup --target-dir=/data/backup_incr1 --incremental-basedir=/data/backup_all/ [root@ansible bin]# cat /data/backup_incr1/xtrabackup_checkpoints backup_type = incremental from_lsn = 2736437 to_lsn = 2737476 last_lsn = 2737485 compact = 0 recover_binlog_info = 0 flushed_lsn = 2737485
(4)基于第一次增备的增备
mysql> insert into departments(dept_no,dept_name) values('d011','Computer'); Query OK, 1 row affected (0.00 sec) mysql> select * from departments; +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d011 | Computer | | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d010 | Network | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales | +---------+--------------------+ 11 rows in set (0.00 sec) [root@ansible ~]# cd percona-xtrabackup-2.4.21/bin/ [root@ansible bin]# ./xtrabackup --user=xtrabackup --password="123456" --backup --target-dir=/data/backup_incr2 --incremental-basedir=/data/backup_incr1 [root@ansible bin]# cat /data/backup_incr2/xtrabackup_checkpoints backup_type = incremental from_lsn = 2737476 to_lsn = 2738465 last_lsn = 2738474 compact = 0 recover_binlog_info = 0 flushed_lsn = 2738474
3.3 prepare
这里需要做三次prepare,第一次是把全备给prepare,要加入--apply-log-only参数,因为在每个备份过程中,都会碰到一些事务进来执行,而备份结束时可能有些事务并没有执行完毕,所以在默认prepare中这些事务就会被回滚(rollback),而加入了--apply-log-only就不会回滚这些事务,而是等待prepare下次增备。第二次prepare就是准备第一次增备的目录,这个prepare目的就是把第一次增备期间发生的变化写入到全备的目录中去,然后第一次增备的目录就没有用处了,因为我们最终拷贝表空间文件是把增备目录拷贝过去。无论多少个增备,最后prepare的那个增备是不能加--apply-log-only参数的,因为最后一次增备还没有完成的事务就要彻底回滚来保证一致性。
(1)第一次prepare全备
[root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible percona-xtrabackup-2.4.21]# cd bin/ [root@ansible bin]# ./xtrabackup --prepare --apply-log-only --target-dir=/data/backup_all
(2)第二次prepare增备
[root@ansible bin]# ./xtrabackup --prepare --apply-log-only --target-dir=/data/backup_all --incremental-dir=/data/backup_incr1
(3)第三次prepare增备
[root@ansible bin]# ./xtrabackup --prepare --target-dir=/data/backup --incremental-dir=/data/backup_incr2
(4)备份恢复
[root@ansible ~]# systemctl stop mysqld.service # 暂停数据库 [root@ansible ~]# cp -rf /mysql/data/ /mysql/data.bak2 # 备份MySQL数据目录 [root@ansible ~]# rm -rf /mysql/data/* # 清空MySQL数据目录 # 恢复数据 [root@ansible ~]# cd percona-xtrabackup-2.4.21/ [root@ansible percona-xtrabackup-2.4.21]# cd bin/ [root@ansible bin]# ./xtrabackup --copy-back --target-dir=/data/backup_all # 修改恢复目录的授权 [root@ansible ~]# cd /mysql/ [root@ansible mysql]# chown -R mysql:mysql data/ [root@ansible mysql]# chmod -R 755 data/ [root@ansible ~]# systemctl restart mysqld # 启动MySQL服务器
作者:UStarGao
链接:https://www.starcto.com/mysql/181.html
来源:STARCTO
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-01-29MySQL多线程复制配置
- 2021-04-03Linux使用PaPing进行TCP端口连通性/网络平均延迟探测
- 2023-04-25Linux tcp_window_scaling关闭引发限速问题
- 2022-07-04DD命令压测、备份与还原磁盘一文详解
- 2021-02-21Docker创建与查看容器常用参数解读