文章目录
- 简介
- 优点
- 语法
- 选项
- 安装
简介
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增量数据存在,恢复成功