mysql备份之Xtrabackup备份工具的使用

简介

XtraBackup(PXB)工具是Percona公司用perl语言开发的一个用于 MySQL数据库物理热备的备份工具，能够非常快速地备份与恢复mysql数据库，且支持在线热备份（备份时不影响数据读写）。

Xtrabackup中包含两个工具：

• xtrabackup ：用于热备份innodb,xtradb引擎表的工具，不能备份其他表。
• innobackupex ：提供了用于myisam(会锁表)和innodb引擎，及混合使用引擎备份的能力。

优点

Xtrabackup的优点：

• 备份速度快，物理备份可靠
• 备份过程不会打断正在执行的事务（无需锁表）
• 能够基于压缩等功能节约磁盘空间和流量
• 自动备份校验
• 还原速度快
• 可以流传，将备份传输到另外一台机器上
• 在不增加服务器负载的情况备份数据
• 支持增量备份

语法

xtrabackup | innobackupex  [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]

选项

选项类别	命令全名	含义
通用选项	–user=name	数据库账号名
通用选项	–password	数据库密码
通用选项	–host=name	数据库主机IP地址
通用选项	–port=name	数据库主机端口号
通用选项	–defaults-file	定义包含默认配置的文件的路径。
通用选项	–socket=name	数据库socket文件地址
备份选项	–backup	创建备份并且放入–target-dir目录中
备份选项	–target-dir	备份文件的存放目录路径，如果目录不存在，xtrabakcup会创建。如果目录存在且为空则成功。不会覆盖已存在的文件。
备份选项	–databases=name	指定要备份的数据库
增量选项	–incremental-basedir	使用增量备份
压缩/解压选项	–compress	compress压缩
压缩/解压选项	–compress-threads=n	启用n个线程进行压缩
压缩/解压选项	–decompress	准备数据之前先解压
准备选项	–prepare	实现同步回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
准备选项	–apply-log-only	阻止回滚未提完成的事务（最后一次增量备份的准备不需要此选项）
准备选项	–incremental-dir	指定增量备份，与全备合并
还原选项	–copy-back	做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。
还原选项	–move-back	这个选项与–copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项会移除backup文件，用时候必须小心。

安装

[root@localhost ~]# yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
[root@localhost ~]# yum install -y percona-xtrabackup-24

查看版本

[root@localhost ~]# xtrabackup -v
xtrabackup: recognized server arguments: --datadir=/opt/data --server-id=1 --log_bin=mysql_bin 
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)

进行全备
首先创建一个存放数据的目录

[root@localhost ~]# mkdir -p /backup/databases
[root@localhost ~]# xtrabackup --backup --target-dir=/backup/databases -uroot -p123  -S /tmp/mysql.sock
xtrabackup: recognized server arguments: --datadir=/opt/data 
xtrabackup: recognized client arguments: --backup=1 --target-dir=/backup/databases --user=root --password=* --socket=/tmp/mysql.sock 
210829 05:00:51  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/tmp/mysql.sock' as 'root'  (using password: YES).
210829 05:00:51  version_check Connected to MySQL server
210829 05:00:51  version_check Executing a version check against the server...
210829 05:00:51  version_check Done.
210829 05:00:51 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /tmp/mysql.sock
Using server version 5.7.34-log
xtrabackup version 2.4.23 based on MySQL server 5.7.34 Linux (x86_64) (revision id: 3320f39)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /opt/data
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
InnoDB: Number of pools: 1
210829 05:00:51 >> log scanned up to (5915201)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 55 for mysql/servers, old maximum was 0
210829 05:00:51 [01] Copying ./ibdata1 to /backup/databases/ibdata1
## 如果有报错的话仔细看报错，我这边找不到套接字，指个套接字就好了

看看指的目录是否有文件了

[root@localhost backup]# cd databases/
[root@localhost databases]# ls
backup-my.cnf   ibdata1  performance_schema  test1  xtrabackup_binlog_info  xtrabackup_info
ib_buffer_pool  mysql    sys                 test2  xtrabackup_checkpoints  xtrabackup_logfile

进数据库随便创点东西

[root@localhost ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.34-log 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                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

恢复之前要进行准备备份

[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/databases

模拟数据丢失

[root@localhost ~]# cd /opt/data
[root@localhost data]# ls
auto.cnf         ib_buffer_pool  mysql             mysql_bin.000005  mysql_bin.index     server-cert.pem
ca-key.pem       ibdata1         mysql_bin.000001  mysql_bin.000006  mysql.pid           server-key.pem
ca.pem           ib_logfile0     mysql_bin.000002  mysql_bin.000007  performance_schema  sys
client-cert.pem  ib_logfile1     mysql_bin.000003  mysql_bin.000008  private_key.pem     test1
client-key.pem   ibtmp1          mysql_bin.000004  mysql_bin.000009  public_key.pem      test2
[root@localhost data]# rm -rf *
直接全部删除

开始恢复

[root@localhost data]# xtrabackup --copy-back --target-dir=/backup/databases

发现MySQL启动报错： Failed to start LSB: start and stop MySQL.
经过一番检查发现有进程冲突
杀掉进程后正常重启

[root@localhost ~]# pkill mysqld
[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# ss -antl
State       Recv-Q Send-Q                            Local Address:Port                                           Peer Address:Port              
LISTEN      0      128                                           *:111                                                       *:*                  
LISTEN      0      5                                 192.168.122.1:53                                                        *:*                  
LISTEN      0      128                                           *:22                                                        *:*                  
LISTEN      0      128                                   127.0.0.1:631                                                       *:*                  
LISTEN      0      100                                   127.0.0.1:25                                                        *:*                  
LISTEN      0      80                                           :::3306                                                     :::*     

进数据库查看

[root@localhost ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 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                |
| test1              |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql>   
#之前创建的东西都在

创建点增量数据

mysql> create database test3;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test1              |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.00 sec)

在之前的同级目录创建个增量备份的存放目录，然后开启增量备份

[root@localhost ~]# mkdir /backup/inc1
[root@localhost ~]# xtrabackup --backup  --target-dir=/backup/inc1/ --incremental-basedir=/backup/databases/  --uroot -p123 -S /tmp/mysql.sock

恢复阶段
准备全量备份

[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/databases 

融合全量与增量备份

[root@localhost ~]# xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc1

模拟数据丢失 全删

[root@localhost ~]# cd /opt/data/
[root@localhost data]# rm -rf *
[root@localhost data]# ls

恢复数据

[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/databases

吸取上次教训
先杀进程

[root@localhost data]# pkill mysqld

给数据库目录授权

[root@localhost opt]# chown -R mysql:mysql data/
[root@localhost opt]# cd data/
[root@localhost data]# ll
总用量 122924
-rw-r-----. 1 mysql mysql      496 8月  29 06:20 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月  29 06:20 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月  29 06:20 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月  29 06:20 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月  29 06:20 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月  29 06:20 mysql
drwxr-x---. 2 mysql mysql     8192 8月  29 06:20 performance_schema
drwxr-x---. 2 mysql mysql     8192 8月  29 06:20 sys
drwxr-x---. 2 mysql mysql      212 8月  29 06:20 test1
drwxr-x---. 2 mysql mysql       20 8月  29 06:20 test2
drwxr-x---. 2 mysql mysql       20 8月  29 06:20 test3
-rw-r-----. 1 mysql mysql       22 8月  29 06:20 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql      497 8月  29 06:20 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 8月  29 06:20 xtrabackup_master_key_id

最后重启mysql,查看数据是否恢复成功

[root@localhost data]# systemctl restart mysqld.service 
[root@localhost data]# cd
[root@localhost ~]# 
[root@localhost ~]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 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                |
| test1              |
| test2              |
| test3              |
+--------------------+
7 rows in set (0.00 sec)

test3增量数据存在，恢复成功