xtrabackup下载与安装
- 下载(我这里是官网找2.4版本,centos8)
[root@host ~]# mkdir xtrabackup
[root@host ~]# cd xtrabackup/
[root@host xtrabackup]# 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 16:26:07-- 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.221
正在连接 downloads.percona.com (downloads.percona.com)|162.220.4.221|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:76595200 (73M) [application/x-tar]
正在保存至: “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar”
Percona-XtraBackup-2.4.22-rc9 100%[==============================================>] 73.05M 746KB/s 用时 47s
2021-05-09 16:26:57 (1.55 MB/s) - 已保存 “Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar” [76595200/76595200])
[root@host xtrabackup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
- 解压并安装
[root@host xtrabackup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
[root@host xtrabackup]# 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@host xtrabackup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm
将MySQL的套接字文件链接到/var/lib/mysql/mysql.scok下
[root@host ~]# mkdir /var/lib/mysql
[root@host ~]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock
[root@host ~]# cd /var/lib/mysql
[root@host mysql]# ls
mysql.sock
增量备份
- 创建一个存放备份文件的目录方便等下放置备份文件
[root@host /]# mkdir backup
[root@host /]# cd backup
[root@host backup]# mkdir all
[root@host backup]# mkdir all1
[root@host backup]# mkdir all2
- 将密码和用户写入配置文件
[root@host ~]# vim ~/.my.cnf
[root@host ~]# cat ~/.my.cnf
[client]
user=root
password=xialuo123!
[innobackupex]
user=root
password=xialuo123!
- 先进行全备
查看数据库和表
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| xkq |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from xkq.student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangwing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | jot | 30 |
| 13 | roo | 40 |
+----+-------------+------+
12 rows in set (0.04 sec)
进行备份
[root@host ~]# xtrabackup --backup --target-dir /backup/all
[root@host ~]# ll /backup/all
总用量 12336
drwxr-x---. 2 root root 6 5月 9 17:02 2021-05-09_17-02-52
drwxr-x---. 6 root root 234 5月 9 17:03 2021-05-09_17-03-03
-rw-r-----. 1 root root 487 5月 9 17:04 backup-my.cnf
-rw-r-----. 1 root root 0 5月 9 17:04 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月 9 17:04 ibdata1
drwxr-x---. 2 root root 4096 5月 9 17:04 mysql
drwxr-x---. 2 root root 8192 5月 9 17:04 performance_schema
drwxr-x---. 2 root root 8192 5月 9 17:04 sys
drwxr-x---. 2 root root 58 5月 9 17:04 xkq
-rw-r-----. 1 root root 21 5月 9 17:04 xtrabackup_binlog_info
-rw-r-----. 1 root root 135 5月 9 17:04 xtrabackup_checkpoints
-rw-r-----. 1 root root 462 5月 9 17:04 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 17:04 xtrabackup_logfile
[root@host ~]# cat /backup/all/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 5859918
last_lsn = 5859927
compact = 0
recover_binlog_info = 0
flushed_lsn = 5859927
- 向表中插入数据
mysql> use xkq;
mysql> insert into student (id,name,age) values (14,'hh',29);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangwing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | jot | 30 |
| 13 | roo | 40 |
| 14 | hh | 29 |
+----+-------------+------+
13 rows in set (0.00 sec)
- 在全备的基础上进行增备
[root@host /]# xtrabackup --backup --target-dir /backup/all1/ --incremental-basedir /backup/all/
[root@host backup]# ll all1
总用量 180
-rw-r-----. 1 root root 487 5月 9 17:09 backup-my.cnf
-rw-r-----. 1 root root 0 5月 9 17:09 ib_buffer_pool
-rw-r-----. 1 root root 131072 5月 9 17:09 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 17:09 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 17:09 mysql
drwxr-x---. 2 root root 8192 5月 9 17:09 performance_schema
drwxr-x---. 2 root root 8192 5月 9 17:09 sys
drwxr-x---. 2 root root 88 5月 9 17:09 xkq
-rw-r-----. 1 root root 21 5月 9 17:09 xtrabackup_binlog_info
-rw-r-----. 1 root root 139 5月 9 17:09 xtrabackup_checkpoints
-rw-r-----. 1 root root 505 5月 9 17:09 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 17:09 xtrabackup_logfile
[root@host all1]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5859918
to_lsn = 5860312
last_lsn = 5860321
compact = 0
recover_binlog_info = 0
flushed_lsn = 5860321
- 再次插入数据
mysql> insert into student (id,name,age) values (15,'vv',19);
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangwing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | jot | 30 |
| 13 | roo | 40 |
| 14 | hh | 29 |
| 15 | vv | 19 |
+----+-------------+------+
- 在第一次的增备上进行第二次增备
[root@host /]# xtrabackup --backup --target-dir /backup/all2/ --incremental-basedir /backup/all1/
[root@host /]# ll backup/all2
总用量 232
-rw-r-----. 1 root root 487 5月 9 17:30 backup-my.cnf
-rw-r-----. 1 root root 368 5月 9 17:30 ib_buffer_pool
-rw-r-----. 1 root root 180224 5月 9 17:30 ibdata1.delta
-rw-r-----. 1 root root 60 5月 9 17:30 ibdata1.meta
drwxr-x---. 2 root root 4096 5月 9 17:30 mysql
drwxr-x---. 2 root root 8192 5月 9 17:30 performance_schema
drwxr-x---. 2 root root 8192 5月 9 17:30 sys
drwxr-x---. 2 root root 88 5月 9 17:30 xkq
-rw-r-----. 1 root root 21 5月 9 17:30 xtrabackup_binlog_info
-rw-r-----. 1 root root 139 5月 9 17:30 xtrabackup_checkpoints
-rw-r-----. 1 root root 506 5月 9 17:30 xtrabackup_info
-rw-r-----. 1 root root 2560 5月 9 17:30 xtrabackup_logfile
[root@host /]# cat backup/all2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5860312
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648
恢复备份
- 模拟误删
[root@host /]# mysql
mysql> drop database xkq;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
- 防止全备和增量备份日志回滚(最后一次的增备除外)
[root@host /]# xtrabackup --prepare --apply-log-only --target-dir /backup/all/
[root@host /]# xtrabackup --prepare --apply-log-only --target-dir /backup/all/ --incremental-dir /backup/all1/
- 防止最后一次增备日志回滚
[root@host /]# xtrabackup --prepare --target-dir /backup/all/ --incremental-dir /backup/all2/
[root@host /]# cat /backup/all/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648
[root@host /]# cat /backup/all1/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5859918
to_lsn = 5860312
last_lsn = 5860321
compact = 0
recover_binlog_info = 0
flushed_lsn = 5860321
[root@host /]# cat /backup/all2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 5860312
to_lsn = 5861639
last_lsn = 5861648
compact = 0
recover_binlog_info = 0
flushed_lsn = 5861648
- 恢复数据
[root@host /]# service mysqld stop
Shutting down MySQL.. SUCCESS!
[root@host /]# rm -rf /opt/mysql-data/*
[root@host /]# xtrabackup --copy-back --target-dir /backup/all/
[root@host /]# chown -R mysql.mysql /opt/mysql-data
[root@host /]# ll /opt/mysql-data
总用量 122920
drwxr-x---. 2 mysql mysql 6 5月 9 17:44 2021-05-09_17-03-03
-rw-r-----. 1 mysql mysql 0 5月 9 17:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月 9 17:44 ibdata1
-rw-r-----. 1 mysql mysql 50331648 5月 9 17:44 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 5月 9 17:44 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 5月 9 17:44 ibtmp1
drwxr-x---. 2 mysql mysql 4096 5月 9 17:44 mysql
drwxr-x---. 2 mysql mysql 8192 5月 9 17:44 performance_schema
drwxr-x---. 2 mysql mysql 8192 5月 9 17:44 sys
drwxr-x---. 2 mysql mysql 58 5月 9 17:44 xkq
-rw-r-----. 1 mysql mysql 21 5月 9 17:44 xtrabackup_binlog_pos_innodb
-rw-r-----. 1 mysql mysql 506 5月 9 17:44 xtrabackup_info
-rw-r-----. 1 mysql mysql 1 5月 9 17:44 xtrabackup_master_key_id
[root@host /]# service mysqld start
Starting MySQL.Logging to '/opt/mysql-data/host.err'.
. SUCCESS!
[root@host /]# mysql
mysql> show databases;
+------------------------------+
| Database |
+------------------------------+
| information_schema |
| #mysql50#2021-05-09_17-03-03 |
| mysql |
| performance_schema |
| sys |
| xkq |
+------------------------------+
6 rows in set (0.00 sec)
mysql> use xkq;
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_xkq |
+---------------+
| student |
+---------------+
1 row in set (0.00 sec)
mysql> select * from student;
+----+-------------+------+
| id | name | age |
+----+-------------+------+
| 1 | tom | 20 |
| 2 | jerry | 23 |
| 3 | wangwing | 25 |
| 4 | sean | 28 |
| 5 | zhangshan | 26 |
| 7 | lisi | 50 |
| 8 | chenshuo | 10 |
| 9 | wangwu | 100 |
| 10 | qiuyi | 15 |
| 11 | qiuxiaotian | 20 |
| 12 | jot | 30 |
| 13 | roo | 40 |
| 14 | hh | 29 |
| 15 | vv | 19 |
+----+-------------+------+
14 rows in set (0.00 sec)