xtrabackup
xtrabackup的介绍
MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。
Xtrabackup中主要包含两个工具：
xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
常用选项:
–host 指定主机
–user 指定用户名
–password 指定密码
–port 指定端口
–databases 指定数据库
–incremental 创建增量备份
–incremental-basedir 指定包含完全备份的目录
–incremental-dir 指定包含增量备份的目录
–apply-log 对备份进行预处理操作
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
–redo-only 不回滚未提交事务
–copy-back 恢复备份目录
xtrabackup的优点
（1）备份速度快，物理备份可靠
（2）备份过程不会打断正在执行的事务（无需锁表）
（3）能够基于压缩等功能节约磁盘空间和流量
（4）自动备份校验
（5）还原速度快
（6）可以流传将备份传输到另外一台机器上
（7）在不增加服务器负载的情况备份数据
安装xtrabackup
在xtrabackup官网下载，然后拖到虚拟机内
[root@localhost ~]# mkdir xtra //创建一个目录xtra，将下载好的安装包拖入
[root@localhost ~]# cd xtra
[root@localhost xtra]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
[root@localhost xtra]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar //解压xtrabackup安装包
[root@localhost xtra]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-24-debuginfo-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-24-debugsource-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-test-24-2.4.22-1.el8.x86_64.rpm
percona-xtrabackup-test-24-debuginfo-2.4.22-1.el8.x86_64.rpm
[root@localhost xtra]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm //安装
CentOS Stream 8 - AppStream 3.8 kB/s | 4.4 kB 00:01
CentOS Stream 8 - AppStream 1.7 MB/s | 8.5 MB 00:05
CentOS Stream 8 - BaseOS 4.1 kB/s | 3.9 kB 00:00
CentOS Stream 8 - BaseOS 820 kB/s | 2.7 MB 00:03
CentOS Stream 8 - Extras 246 B/s | 1.5 kB 00:06
依赖关系解决。
================================================================================
软件包 架构 版本 仓库 大小
================================================================================
安装:
percona-xtrabackup-24 x86_64 2.4.22-1.el8 @commandline 7.7 M
安装依赖关系:
libev x86_64 4.24-6.el8 appstream 52 k
mariadb-connector-c x86_64 3.1.11-2.el8_3 appstream 200 k
mariadb-connector-c-config noarch 3.1.11-2.el8_3 appstream 15 k
perl-DBD-MySQL x86_64 4.046-3.module_el8.3.0+419+c2dec72b
appstream 156 k
启用模块流:
perl-DBD-MySQL 4.046
事务概要
================================================================================
安装 5 软件包
总计：8.1 M
总下载：423 k
安装大小：32 M
下载软件包：
(1/4): mariadb-connector-c-config-3.1.11-2.el8_ 44 kB/s | 15 kB 00:00
(2/4): libev-4.24-6.el8.x86_64.rpm 129 kB/s | 52 kB 00:00
(3/4): perl-DBD-MySQL-4.046-3.module_el8.3.0+41 549 kB/s | 156 kB 00:00
(4/4): mariadb-connector-c-3.1.11-2.el8_3.x86_6 149 kB/s | 200 kB 00:01
--------------------------------------------------------------------------------
总计 206 kB/s | 423 kB 00:02
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
准备中 : 1/1
安装 : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 1/5
警告：/etc/my.cnf 已建立为 /etc/my.cnf.rpmnew
安装 : mariadb-connector-c-3.1.11-2.el8_3.x86_64 2/5
安装 : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64 3/5
安装 : libev-4.24-6.el8.x86_64 4/5
安装 : percona-xtrabackup-24-2.4.22-1.el8.x86_64 5/5
运行脚本: percona-xtrabackup-24-2.4.22-1.el8.x86_64 5/5
验证 : libev-4.24-6.el8.x86_64 1/5
验证 : mariadb-connector-c-3.1.11-2.el8_3.x86_64 2/5
验证 : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 3/5
验证 : perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64 4/5
验证 : percona-xtrabackup-24-2.4.22-1.el8.x86_64 5/5
Installed products updated.
已安装:
libev-4.24-6.el8.x86_64
mariadb-connector-c-3.1.11-2.el8_3.x86_64
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
percona-xtrabackup-24-2.4.22-1.el8.x86_64
perl-DBD-MySQL-4.046-3.module_el8.3.0+419+c2dec72b.x86_64
完毕！
//将mysql的套接字文件链接到/var/lib/mysql/mysql.scok下
[root@localhost xtra]# mkdir /var/lib/mysql
[root@localhost xtra]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.scok
创建备份目录
[root@localhost /]# mkdir /backups
[root@localhost /]# mkdir /backups/all
[root@localhost /]# mkdir /backups/p1
[root@localhost /]# mkdir /backups/p2
[root@localhost /]# ls
backups boot etc lib media opt root sbin sys usr
bin dev home lib64 mnt proc run srv tmp var
[root@localhost backups]# ls
all p1 p2
将登录密码写在配置文件中，这样的话方便后面备份操作
[root@localhost ~]# vim .my.cnf
[root@localhost ~]# cat .my.cnf
[client]
user=root
password=ZHANGde12+Jun
[innobackupex]
user=root
password=ZHANGde12+Jun
增量备份
查看数据库内容
mysql> select * from student;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | yuheng | 21 |
| 5 | zhangsan | 26 |
| 6 | lisi | 5 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
+----+----------+------+
8 rows in set (0.00 sec)
进行全量备份
[root@localhost ~]# xtrabackup --backup --target-dir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/data
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/all/
210509 23:08:11 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210509 23:08:11 version_check Connected to MySQL server
210509 23:08:11 version_check Executing a version check against the server...
# A software update is available:
210509 23:08:15 version_check Done.
..............
210509 23:08:18 Executing UNLOCK TABLES
210509 23:08:18 All tables unlocked
210509 23:08:19 [00] Copying ib_buffer_pool to /backups/all/ib_buffer_pool
210509 23:08:19 [00] ...done
210509 23:08:19 Backup created in directory '/backups/all/'
210509 23:08:19 [00] Writing /backups/all/backup-my.cnf
210509 23:08:19 [00] ...done
210509 23:08:19 [00] Writing /backups/all/xtrabackup_info
210509 23:08:19 [00] ...done
xtrabackup: Transaction log of lsn (2754848) to (2754857) was copied.
210509 23:08:19 completed OK!
[root@localhost ~]# ll /backups/all/
总用量 12336
-rw-r-----. 1 root root 487 5月 9 23:08 backup-my.cnf
-rw-r-----. 1 root root 350 5月 9 23:08 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月 9 23:08 ibdata1
drwxr-x---. 2 root root 4096 5月 9 23:08 mysql
drwxr-x---. 2 root root 8192 5月 9 23:08 performance_schema
drwxr-x---. 2 root root 8192 5月 9 23:08 sys
-rw-r-----. 1 root root 135 5月 9 23:08 xtrabackup_checkpoints
-rw-r-----. 1 root root 417 5月 9 23:08 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 23:08 xtrabackup_logfile
drwxr-x---. 2 root root 58 5月 9 23:08 yh
[root@localhost ~]# cat /backups/all/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2754848
last_lsn = 2754857
compact = 0
recover_binlog_info = 0
flushed_lsn = 2754857
向student表中插入数据
mysql> use yh;
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> insert student (name,age) value ('xiaoyu',4),('zhangdejun',5);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | yuheng | 21 |
| 5 | zhangsan | 26 |
| 6 | lisi | 5 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | xiaoyu | 4 |
| 10 | zhangdejun | 5 |
+----+------------+------+
10 rows in set (0.00 sec)
在全备的基础上进行增备
[root@localhost ~]# xtrabackup --backup --target-dir /backups/p1/ --incremental-basedir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/data
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/p1/ --incremental-basedir=/backups/all/
210509 23:15:55 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210509 23:15:55 version_check Connected to MySQL server
....................
210509 23:15:58 [00] ...done
210509 23:15:58 Backup created in directory '/backups/p1/'
210509 23:15:58 [00] Writing /backups/p1/backup-my.cnf
210509 23:15:58 [00] ...done
210509 23:15:58 [00] Writing /backups/p1/xtrabackup_info
210509 23:15:58 [00] ...done
xtrabackup: Transaction log of lsn (2755095) to (2755104) was copied.
210509 23:15:58 completed OK!
[root@localhost ~]# ll /backups/p1/
总用量 180
-rw-r-----. 1 root root 487 5月 9 23:15 backup-my.cnf
-rw-r-----. 1 root root 350 5月 9 23:15 ib_buffer_pool
-rw-r-----. 1 root root 131072 5月 9 23:15 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 23:15 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 23:15 mysql
drwxr-x---. 2 root root 8192 5月 9 23:15 performance_schema
drwxr-x---. 2 root root 8192 5月 9 23:15 sys
-rw-r-----. 1 root root 139 5月 9 23:15 xtrabackup_checkpoints
-rw-r-----. 1 root root 458 5月 9 23:15 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 23:15 xtrabackup_logfile
drwxr-x---. 2 root root 88 5月 9 23:15 yh
再次往 student表中插入数据
mysql> select * from student;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | yuheng | 21 |
| 5 | zhangsan | 26 |
| 6 | lisi | 5 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | xiaoyu | 4 |
| 10 | zhangdejun | 5 |
| 11 | wanglaowu | 30 |
| 12 | meimei | 19 |
+----+------------+------+
12 rows in set (0.00 sec)
再在第一次增备的基础上做第二次增备
[root@localhost ~]# xtrabackup --backup --target-dir /backups/p2/ --incremental-basedir /backups/p1/
xtrabackup: recognized server arguments: --datadir=/opt/data
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/p2/ --incremental-basedir=/backups/p1/
210509 23:25:19 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210509 23:25:19 version_check Connected to MySQL server
.........................
210509 23:25:21 Executing UNLOCK TABLES
210509 23:25:21 All tables unlocked
210509 23:25:21 [00] Copying ib_buffer_pool to /backups/p2/ib_buffer_pool
210509 23:25:21 [00] ...done
210509 23:25:21 Backup created in directory '/backups/p2/'
210509 23:25:21 [00] Writing /backups/p2/backup-my.cnf
210509 23:25:21 [00] ...done
210509 23:25:21 [00] Writing /backups/p2/xtrabackup_info
210509 23:25:21 [00] ...done
xtrabackup: Transaction log of lsn (2755338) to (2755347) was copied.
210509 23:25:21 completed OK!
[root@localhost ~]# ll /backups/p1/
总用量 180
-rw-r-----. 1 root root 487 5月 9 23:15 backup-my.cnf
-rw-r-----. 1 root root 350 5月 9 23:15 ib_buffer_pool
-rw-r-----. 1 root root 131072 5月 9 23:15 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 23:15 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 23:15 mysql
drwxr-x---. 2 root root 8192 5月 9 23:15 performance_schema
drwxr-x---. 2 root root 8192 5月 9 23:15 sys
-rw-r-----. 1 root root 139 5月 9 23:15 xtrabackup_checkpoints
-rw-r-----. 1 root root 458 5月 9 23:15 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 23:15 xtrabackup_logfile
drwxr-x---. 2 root root 88 5月 9 23:15 yh
模拟误删数据库
mysql> drop database yh;
Query OK, 1 row affected (0.20 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
恢复备份
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/
//防止全备日志回滚
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
xtrabackup: recognized client arguments: --prepare=1 --apply-log-only=1 --target-dir=/backups/all/
................
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2754866
InnoDB: Number of pools: 1
210509 23:27:47 completed OK!
[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/ --incremental-dir /backups/p1/
//防止增量备份日志回滚
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
...................
210509 23:28:07 [01] ...done
210509 23:28:07 [01] Copying /backups/p1/performance_schema/session_status.frm to ./performance_schema/session_status.frm
210509 23:28:07 [01] ...done
210509 23:28:07 [00] Copying /backups/p1//xtrabackup_info to ./xtrabackup_info
210509 23:28:07 [00] ...done
210509 23:28:07 completed OK!
[root@localhost ~]# xtrabackup --prepare --target-dir /backups/all/ --incremental-dir /backups/p2/ //最后一次增量备份不用使用appiy-log-only参数
xtrabackup: recognized server arguments: --innodb_checksum_algorithm=crc32 --innodb_log_checksum_algorithm=strict_crc32 --innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_files_in_group=2 --innodb_log_file_size=50331648 --innodb_fast_checksum=0 --innodb_page_size=16384 --innodb_log_block_size=512 --innodb_undo_directory=./ --innodb_undo_tablespaces=0 --server-id=0 --redo-log-version=1
................
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.32 started; log sequence number 2755605
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2755624
210509 23:31:46 completed OK!
[root@localhost ~]# cat /backups/all/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2755338
last_lsn = 2755347
compact = 0
recover_binlog_info = 0
flushed_lsn = 2755347
[root@localhost ~]# cat /backups/p1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2754848
to_lsn = 2755095
last_lsn = 2755104
compact = 0
recover_binlog_info = 0
flushed_lsn = 2755104
[root@localhost ~]# cat /backups/p2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2755095
to_lsn = 2755338
last_lsn = 2755347
compact = 0
recover_binlog_info = 0
flushed_lsn = 2755347
恢复数据
[root@localhost ~]# service mysqld stop //停止mysql服务
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# rm -rf /opt/data/* //删除data下的数据
[root@localhost ~]# xtrabackup --copy-back --target-dir /backups/all/ //使用全备将数据恢复
xtrabackup: recognized server arguments: --datadir=/opt/data
xtrabackup: recognized client arguments: --user=root --password=* --copy-back=1 --target-dir=/backups/all/
xtrabackup version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781)
210509 23:37:32 [01] Copying ib_logfile0 to /opt/data/ib_logfile0
210509 23:37:33 [01] ...done
.......................
[root@localhost ~]# chown -R mysql.mysql /opt/data //将data的属主属组改为mysql
[root@localhost ~]# ll /opt/data
总用量 122920
-rw-r-----. 1 mysql mysql 350 5月 9 23:37 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 9 23:37 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 9 23:37 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 9 23:37 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 9 23:37 ibtmp1
drwxr-x---. 2 mysql mysql 4096 5月 9 23:37 mysql
drwxr-x---. 2 mysql mysql 8192 5月 9 23:37 performance_schema
drwxr-x---. 2 mysql mysql 8192 5月 9 23:37 sys
-rw-r-----. 1 mysql mysql 457 5月 9 23:37 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 5月 9 23:37 xtrabackup_master_key_id
drwxr-x---. 2 mysql mysql 58 5月 9 23:37 yh
[root@localhost ~]# service mysqld start //启动mysql服务
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
... SUCCESS!
查看数据恢复是否成功
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.33 MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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 |
| sys |
| yh |
+--------------------+
5 rows in set (0.01 sec)
mysql> use yh;
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> select * from student;
+----+------------+------+
| id | name | age |
+----+------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | sean | 28 |
| 4 | yuheng | 21 |
| 5 | zhangsan | 26 |
| 6 | lisi | 5 |
| 7 | chenshuo | 10 |
| 8 | wangwu | 3 |
| 9 | xiaoyu | 4 |
| 10 | zhangdejun | 5 |
| 11 | wanglaowu | 30 |
| 12 | meimei | 19 |
+----+------------+------+
12 rows in set (0.00 sec)
105
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
