xtrabackup安装&增量备份

xtrabackup安装

在opt下面创建一个目录，用来存放xtrabackup的包

[root@localhost opt]# mkdir xtrabackup
[root@localhost opt]# ls
data  xtrabackup
[root@localhost opt]# cd xtrabackup/
[root@localhost xtrabackup]# ls
Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar
[root@localhost xtrabackup]# tar xf Percona-XtraBackup-2.4.22-rc99a781-el8-x86_64-bundle.tar 
[root@localhost 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@localhost xtrabackup]# dnf -y install percona-xtrabackup-24-2.4.22-1.el8.x86_64.rpm

在这里做一个软连接到var/lib/mysql/mysql.sock里面去

[root@localhost xtrabackup]# mkdir /var/lib/mysql
[root@localhost xtrabackup]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

创建备份目录

[root@localhost opt]# mkdir /backups
[root@localhost opt]# mkdir /backups/all
[root@localhost opt]# mkdir /backups/x1
[root@localhost opt]# mkdir /backups/x2

将密码写入配置文件方便登录

[root@localhost ~]# vim .my.cnf
[client]
user=root
password=ZHANGde12+Jun
[innobackupex]
user=root
password=ZHANGde12+Jun

增量备份

进入mysql查看数据库是否有内容

[root@localhost opt]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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                |
| zdj                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zdj;
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_zdj |
+---------------+
| student       |
+---------------+
1 row in set (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 |
+----+-------------+------+
10 rows in set (0.00 sec)

全量备份

[root@localhost opt]# 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 10:38:03  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
210509 10:38:03  version_check Connected to MySQL server
210509 10:38:03  version_check Executing a version check against the server...
210509 10:38:03  version_check Done.
210509 10:38:03 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
Using server version 5.7.33
xtrabackup version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781)

210509 10:38:04 Executing UNLOCK TABLES
210509 10:38:04 All tables unlocked
210509 10:38:04 [00] Copying ib_buffer_pool to /backups/all/ib_buffer_pool
210509 10:38:04 [00]        ...done
210509 10:38:04 Backup created in directory '/backups/all/'
210509 10:38:04 [00] Writing /backups/all/backup-my.cnf
210509 10:38:04 [00]        ...done
210509 10:38:04 [00] Writing /backups/all/xtrabackup_info
210509 10:38:04 [00]        ...done
xtrabackup: Transaction log of lsn (2935153) to (2935162) was copied.
210509 10:38:05 completed OK!

这里查看已经备份好了

[root@localhost xtrabackup]# ll /backups/all
总用量 12336
-rw-r-----. 1 root root      487 5月   9 10:38 backup-my.cnf
-rw-r-----. 1 root root      339 5月   9 10:38 ib_buffer_pool
-rw-r-----. 1 root root 12582912 5月   9 10:38 ibdata1
drwxr-x---. 2 root root     4096 5月   9 10:38 mysql
drwxr-x---. 2 root root     8192 5月   9 10:38 performance_schema
drwxr-x---. 2 root root     8192 5月   9 10:38 sys
-rw-r-----. 1 root root      135 5月   9 10:38 xtrabackup_checkpoints
-rw-r-----. 1 root root      417 5月   9 10:38 xtrabackup_info
-rw-r-----. 1 root root     2560 5月   9 10:38 xtrabackup_logfile
drwxr-x---. 2 root root       58 5月   9 10:38 zdj

[root@localhost opt]# cat /backups/all/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 2936259
last_lsn = 2936268
compact = 0
recover_binlog_info = 0
flushed_lsn = 2936268

向表中插入点数据

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 |
+----+-------------+------+


mysql> insert student (name,age) value('zhangdejun',21),('yhheng',3);
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 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | zhangdejun  |   21 |
| 13 | yhheng      |    3 |
+----+-------------+------+
12 rows in set (0.00 sec)

在全量备份的基础上增量备份

[root@localhost opt]# xtrabackup --backup --target-dir /backups/x1/ --incremental-basedir /backups/all/
xtrabackup: recognized server arguments: --datadir=/opt/data 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/x1/ --incremental-basedir=/backups/all/ 
210509 10:45:19  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
210509 10:45:19  version_check Connected to MySQL server
210509 10:45:19  version_check Executing a version check against the server...
210509 10:45:19  version_check Done.

210509 10:45:21 Executing UNLOCK TABLES
210509 10:45:21 All tables unlocked
210509 10:45:21 [00] Copying ib_buffer_pool to /backups/x1/ib_buffer_pool
210509 10:45:21 [00]        ...done
210509 10:45:21 Backup created in directory '/backups/x1/'
210509 10:45:21 [00] Writing /backups/x1/backup-my.cnf
210509 10:45:21 [00]        ...done
210509 10:45:21 [00] Writing /backups/x1/xtrabackup_info
210509 10:45:21 [00]        ...done
xtrabackup: Transaction log of lsn (2936259) to (2936268) was copied.
210509 10:45:21 completed OK!

再次查看x1里面已经有了数据

[root@localhost opt]# cd xtrabackup/
[root@localhost xtrabackup]# ll /backups/x1
总用量 244
-rw-r-----. 1 root root    487 5月   9 10:45 backup-my.cnf
-rw-r-----. 1 root root    339 5月   9 10:45 ib_buffer_pool
-rw-r-----. 1 root root 196608 5月   9 10:45 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 10:45 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 10:45 mysql
drwxr-x---. 2 root root   8192 5月   9 10:45 performance_schema
drwxr-x---. 2 root root   8192 5月   9 10:45 sys
-rw-r-----. 1 root root    139 5月   9 10:45 xtrabackup_checkpoints
-rw-r-----. 1 root root    458 5月   9 10:45 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 10:45 xtrabackup_logfile
drwxr-x---. 2 root root     88 5月   9 10:45 zdj

再次插入点数据

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 | zhangdejun  |   21 |
| 13 | yhheng      |    3 |
+----+-------------+------+

mysql> insert student(name,age) values('guanglin',49),('daoan',26);
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 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | zhangdejun  |   21 |
| 13 | yhheng      |    3 |
| 14 | guanglin    |   49 |
| 15 | daoan       |   26 |
+----+-------------+------+
14 rows in set (0.00 sec)

接下来在第一次增备的基础上做第二次增量备份

[root@localhost opt]# xtrabackup --backup --target-dir /backups/x2/ --incremental-basedir /backups/x1/
xtrabackup: recognized server arguments: --datadir=/opt/data 
xtrabackup: recognized client arguments: --user=root --password=* --backup=1 --target-dir=/backups/x2/ --incremental-basedir=/backups/x1/ 
210509 10:57:07  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
210509 10:57:07  version_check Connected to MySQL server
210509 10:57:07  version_check Executing a version check against the server...
210509 10:57:07  version_check Done.

210509 10:57:09 Executing UNLOCK TABLES
210509 10:57:09 All tables unlocked
210509 10:57:09 [00] Copying ib_buffer_pool to /backups/x2/ib_buffer_pool
210509 10:57:09 [00]        ...done
210509 10:57:09 Backup created in directory '/backups/x2/'
210509 10:57:09 [00] Writing /backups/x2/backup-my.cnf
210509 10:57:09 [00]        ...done
210509 10:57:09 [00] Writing /backups/x2/xtrabackup_info
210509 10:57:09 [00]        ...done
xtrabackup: Transaction log of lsn (2937372) to (2937381) was copied.
210509 10:57:09 completed OK!

查看x2的数据

[root@localhost xtrabackup]# ll /backups/x2
总用量 212
-rw-r-----. 1 root root    487 5月   9 10:57 backup-my.cnf
-rw-r-----. 1 root root    339 5月   9 10:57 ib_buffer_pool
-rw-r-----. 1 root root 163840 5月   9 10:57 ibdata1.delta
-rw-r-----. 1 root root     60 5月   9 10:57 ibdata1.meta
drwxr-x---. 2 root root   4096 5月   9 10:57 mysql
drwxr-x---. 2 root root   8192 5月   9 10:57 performance_schema
drwxr-x---. 2 root root   8192 5月   9 10:57 sys
-rw-r-----. 1 root root    139 5月   9 10:57 xtrabackup_checkpoints
-rw-r-----. 1 root root    457 5月   9 10:57 xtrabackup_info
-rw-r-----. 1 root root   2560 5月   9 10:57 xtrabackup_logfile
drwxr-x---. 2 root root     88 5月   9 10:57 zdj

恢复备份

现在模拟误删数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zdj                |
+--------------------+
5 rows in set (0.01 sec)

mysql> drop database zdj;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

使用prepare和apply-log-only防止日志回滚

[root@localhost opt]# 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 version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781)

[root@localhost opt]# xtrabackup --prepare --apply-log-only --target-dir /backups/all/ --incremental-dir /backups/x1/

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/ --incremental-dir=/backups/x1/ 
xtrabackup version 2.4.22 based on MySQL server 5.7.32 Linux (x86_64) (revision id: c99a781)
incremental backup from 2935153 is enabled.
xtrabackup: cd to /backups/all/

210509 11:09:36 [01]        ...done
210509 11:09:36 [01] Copying /backups/x1/performance_schema/session_status.frm to ./performance_schema/session_status.frm
210509 11:09:36 [01]        ...done
210509 11:09:36 [00] Copying /backups/x1//xtrabackup_info to ./xtrabackup_info
210509 11:09:36 [00]        ...done
210509 11:09:36 completed OK!

这里在最后一次增量备份不使用apply-log-only

[root@localhost opt]# xtrabackup --prepare --target-dir /backups/all/ --incremental-dir /backups/
xtrabackup: recognized server arguments: 
xtrabackup: recognized client arguments: --prepare=1 --target-dir=/backups/all/ --incremental-dir=/backups/ 
xtrabackup: Error: cannot open /backups//xtrabackup_checkpoints
xtrabackup: error: failed to read metadata from /backups//xtrabackup_checkpoints


查看一下三个备份的数据
[root@localhost opt]# cat /backups/all/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 2936259
last_lsn = 2936268
compact = 0
recover_binlog_info = 0
flushed_lsn = 2936268
[root@localhost opt]# cat /backups/x1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2935153
to_lsn = 2936259
last_lsn = 2936268
compact = 0
recover_binlog_info = 0
flushed_lsn = 2936268
[root@localhost opt]# cat /backups/x2/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2936259
to_lsn = 2937372
last_lsn = 2937381
compact = 0
recover_binlog_info = 0
flushed_lsn = 2937381

恢复数据

[root@localhost opt]# service mysqld stop
Shutting down MySQL.. SUCCESS!

[root@localhost opt]# rm -rf /opt/data/*
删除opt/data下面的所有数据

[root@localhost opt]# 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 11:30:20 [01] Copying ibdata1 to /opt/data/ibdata1

r_key_id
210509 11:30:21 [01]        ...done
210509 11:30:21 [01] Copying ./xtrabackup_info to /opt/data/xtrabackup_info
210509 11:30:21 [01]        ...done
210509 11:30:21 completed OK!

[root@localhost opt]# chown -R mysql.mysql /opt/data
设置属主属组为mysql

[root@localhost opt]# ll /opt/data

总用量 12328
-rw-r-----. 1 mysql mysql      339 5月   9 11:30 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 5月   9 11:30 ibdata1
drwxr-x---. 2 mysql mysql     4096 5月   9 11:30 mysql
drwxr-x---. 2 mysql mysql     8192 5月   9 11:30 performance_schema
drwxr-x---. 2 mysql mysql     8192 5月   9 11:30 sys
-rw-r-----. 1 mysql mysql      458 5月   9 11:30 xtrabackup_info
-rw-r-----. 1 mysql mysql        1 5月   9 11:30 xtrabackup_master_key_id
drwxr-x---. 2 mysql mysql       58 5月   9 11:30 zdj

[root@localhost opt]# service mysqld start
重启mysql服务
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
.. SUCCESS! 
[root@localhost opt]# mysql


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zdj                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zdj;
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 | wangqing    |   25 |
|  4 | sean        |   28 |
|  5 | zhangshan   |   26 |
|  7 | lisi        |   50 |
|  8 | chenshuo    |   10 |
|  9 | wangwu      |  100 |
| 10 | qiuyi       |   15 |
| 11 | qiuxiaotian |   20 |
| 12 | zhangdejun  |   21 |
| 13 | yhheng      |    3 |
+----+-------------+------+
12 rows in set (0.00 sec)