xtrbakup的安装包下载
[root@czh ~]# mkdir xtrabakup
[root@czh ~]# cd xtrabakup/
[root@czh xtrabakup]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
--2021-05-09 13:04:32-- https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
正在解析主机 downloads.percona.com (downloads.percona.com)... 162.220.4.222
正在连接 downloads.percona.com (downloads.percona.com)|162.220.4.222|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:76595200 (73M) [application/x-tar]
正在保存至: “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar”
Percona-XtraBackup- 100%[==================>] 73.05M 3.38MB/s 用时 84s
2021-05-09 13:05:57 (893 KB/s) - 已保存 “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar” [76595200/76595200])
[root@czh xtrabakup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
解压并且安装
[root@czh xtrabakup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
[root@czh xtrabakup]# 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@czh xtrabakup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
CentOS
将mysql的套接字文件连接到/var/lib/mysql/mysql.sock
[root@czh ~]# mkdir /var/lib/mysql
[root@czh ~]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@czh ~]# cd /var/lib/mysql
[root@czh mysql]# ls
mysql.sock
进行增量备份
首先创建存放备份数据的目录
[root@czh ~]cd /
[root@czh /]# mkdir backups
[root@czh /]# cd backups/
[root@czh backup]# mkdir all
[root@czh backup]# mkdir all1
[root@czh backup]# mkdir all2
将密码写入配置文件
[root@czh backup]# cat ~/.my.cnf
[client]
user=root
password=Czh1.com
[innobaukupex]
user=root
password=Czh1.com
首先查看数据库里有什么
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzihan |
| czh |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
进行全备
mysql> select * from czh.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
+----+-------------+------+
10 rows in set (0.00 sec)
在进行全备
[root@czh /]# xtrabackup --backup --target-dir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/mysql_data
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/all/
210509 13:45:27 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
210509 13:45:27 version_check Connected to MySQL server
210509 13:45:27 version_check Executing a version check against the server...
210509 13:45:27 version_check Done.
···········
···········
210509 13:45:28 [00] ...done
xtrabackup: Transaction log of lsn (2835743) to (2835752) was copied.
210509 13:45:29 completed OK!
[root@czh /]# cd backups/
[root@czh backups]# ls
all all1 all2
[root@czh backups]# ll all
总用量 12336
-rw-r-----. 1 root root 487 5月 9 13:45 backup-my.cnf
drwxr-x---. 2 root root 58 5月 9 13:45 chenzihan
drwxr-x---. 2 root root 58 5月 9 13:45 czh
-rw-r-----. 1 root root 362 5月 9 13:45 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月 9 13:45 ibdata1
drwxr-x---. 2 root root 4096 5月 9 13:45 mysql
drwxr-x---. 2 root root 8192 5月 9 13:45 performance_schema
drwxr-x---. 2 root root 8192 5月 9 13:45 sys
-rw-r-----. 1 root root 135 5月 9 13:45 xtrabackup_checkpoints
-rw-r-----. 1 root root 417 5月 9 13:45 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 13:45 xtrabackup_logfile
在表中添加数据
mysql> use czh;
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 into student(name,age) values('tommao',10);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | tommao | 10 |
+----+-------------+------+
11 rows in set (0.00 sec)
在全备的基础上进行增备
[root@czh /]# xtrabackup --backup --target-dir /backups/all1/ --incremental-basedir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/mysql_data
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/all1/ --incremental-basedir=/backups/all/
············
············
210509 13:54:17 completed OK!
[root@czh /]# ll backups/all1
总用量 148
-rw-r-----. 1 root root 487 5月 9 13:54 backup-my.cnf
drwxr-x---. 2 root root 88 5月 9 13:54 chenzihan
drwxr-x---. 2 root root 88 5月 9 13:54 czh
-rw-r-----. 1 root root 362 5月 9 13:54 ib_buffer_pool
-rw-r-----. 1 root root 98304 5月 9 13:54 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 13:54 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 13:54 mysql
drwxr-x---. 2 root root 8192 5月 9 13:54 performance_schema
drwxr-x---. 2 root root 8192 5月 9 13:54 sys
-rw-r-----. 1 root root 139 5月 9 13:54 xtrabackup_checkpoints
-rw-r-----. 1 root root 460 5月 9 13:54 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 13:54 xtrabackup_logfile
再次插入数据
mysql> use czh;
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 into student(name,age) values('jerrylaosu',10);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | tommao | 10 |
| 13 | jerrylaosu | 10 |
+----+-------------+------+
12 rows in set (0.00 sec)
进行第二次增备
[root@czh /]# xtrabackup --backup --target-dir /backups/all2/ --incremental-basedir /backups/all1/
210509 14:01:10 completed OK!
[root@czh /]# ll backups/all2
总用量 116
-rw-r-----. 1 root root 487 5月 9 14:01 backup-my.cnf
drwxr-x---. 2 root root 88 5月 9 14:01 chenzihan
drwxr-x---. 2 root root 88 5月 9 14:01 czh
-rw-r-----. 1 root root 362 5月 9 14:01 ib_buffer_pool
-rw-r-----. 1 root root 65536 5月 9 14:01 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 14:01 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 14:01 mysql
drwxr-x---. 2 root root 8192 5月 9 14:01 performance_schema
drwxr-x---. 2 root root 8192 5月 9 14:01 sys
-rw-r-----. 1 root root 139 5月 9 14:01 xtrabackup_checkpoints
-rw-r-----. 1 root root 461 5月 9 14:01 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 14:01 xtrabackup_logfile
[root@czh /]#
恢复备份
误删数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzihan |
| czh |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database czh;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| chenzihan |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
防止全备和增量备份日志回滚(最后一次的增量备份除外)
[root@czh backups]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/
[root@czh backups]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/ --incremental-dir /backups/all1/
防止最后一次的增量备份回滚
[root@czh backups]# xtrabackup --prepare --target-dir /backups/all/ --incremental-dir /backups/all2
[root@czh backups]# cat all/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2836111
last_lsn = 2836120
compact = 0
recover_binlog_info = 0
flushed_lsn = 2836120
[root@czh backups]# cat all1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2835743
to_lsn = 2835917
last_lsn = 2835926
compact = 0
recover_binlog_info = 0
flushed_lsn = 2835926
[root@czh backups]# cat all2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2835917
to_lsn = 2836111
last_lsn = 2836120
compact = 0
recover_binlog_info = 0
flushed_lsn = 2836120
恢复数据库
[root@czh ~]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@czh ~]# rm -rf /opt/mysql_data/*
[root@czh ~]# xtrabackup --copy-back --target-dir /backups/all/xtrabackup: recognized server arguments: --datadir=/opt/mysql_data
210509 14:11:19 completed OK!
[root@czh ~]# chown -R mysql.mysql /opt/mysql_data/
[root@czh ~]# ll /opt/mysql_data/
总用量 122920
drwxr-x---. 2 mysql mysql 58 5月 9 14:11 chenzihan
drwxr-x---. 2 mysql mysql 58 5月 9 14:11 czh
-rw-r-----. 1 mysql mysql 362 5月 9 14:11 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 9 14:11 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 9 14:11 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 9 14:11 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 9 14:11 ibtmp1
drwxr-x---. 2 mysql mysql 4096 5月 9 14:11 mysql
drwxr-x---. 2 mysql mysql 8192 5月 9 14:11 performance_schema
drwxr-x---. 2 mysql mysql 8192 5月 9 14:11 sys
-rw-r-----. 1 mysql mysql 461 5月 9 14:11 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 5月 9 14:11 xtrabackup_master_key_id
[root@czh ~]# service mysqld start
Starting MySQL.Logging to '/opt/mysql_data/czh.err'.
. SUCCESS!
[root@czh ~]# 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> select * from czh.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangqing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | tommao | 10 |
| 13 | jerrylaosu | 10 |
+----+-------------+------+
12 rows in set (0.01 sec)