6.xtrabackup实现全量+增量+binlog恢复库

利用xtrabackup8.0 完全，增量备份及还原MySQL8.0

1 备份过程

1）完全备份：

[root@CentOS8 ~]# yum install -y percona-xtrabackup-80-8.0.26-18.1.el8.x86_64.rpm

[root@centos8 ~]#mkdir /backup/

[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base

[root@centos8 ~]# du -sh /backup/base/

71M /backup/base/

2）第一次修改数据

[root@CentOS8 backup]# mysql -uroot -pMmagedu0!

(root@localhost) [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| hellodb            |

| information_schema |

| mysql              |

| performance_schema |

| sys                |

+--------------------+

5 rows in set (0.00 sec)

(root@localhost) [(none)]> use hellodb;

(root@localhost) [hellodb]> show tables;

+-------------------+

| Tables_in_hellodb |

+-------------------+

| classes           |

| coc               |

| courses           |

| scores            |

| students          |

| teachers          |

| toc               |

+-------------------+

7 rows in set (0.00 sec)

(root@localhost) [hellodb]> select * from students;

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |

|     3 | Xie Yanke     |  53 | M      |       2 |        16 |

|     4 | Ding Dian     |  32 | M      |       4 |         4 |

|     5 | Yu Yutong     |  26 | M      |       3 |         1 |

|     6 | Shi Qing      |  46 | M      |       5 |      NULL |

|     7 | Xi Ren        |  19 | F      |       3 |      NULL |

|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |

|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |

|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |

|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |

|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |

|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |

|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |

|    15 | Duan Yu       |  19 | M      |       4 |      NULL |

|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |

|    17 | Lin Chong     |  25 | M      |       4 |      NULL |

|    18 | Hua Rong      |  23 | M      |       7 |      NULL |

|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |

|    20 | Diao Chan     |  19 | F      |       7 |      NULL |

|    21 | Huang Yueying |  22 | F      |       6 |      NULL |

|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |

|    23 | Ma Chao       |  23 | M      |       4 |      NULL |

|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |

|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |

+-------+---------------+-----+--------+---------+-----------+

(root@localhost) [hellodb]> insert students (name,age) values ('zhang wuji',18);

Query OK, 1 row affected (0.01 sec)

(root@localhost) [hellodb]> select * from students where name='zhang wuji';

+-------+------------+-----+--------+---------+-----------+

| StuID | Name       | Age | Gender | ClassID | TeacherID |

+-------+------------+-----+--------+---------+-----------+

|    26 | zhang wuji |  18 | F      |    NULL |      NULL |

+-------+------------+-----+--------+---------+-----------+

3）第一次增量备份

[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 -- incremental-basedir=/backup/base

[root@CentOS8 backup]# ls /backup/

base  inc1

[root@centos8 ~]#cat /backup/inc1/xtrabackup_info

[root@CentOS8 inc1]# cat xtrabackup_info

uuid = a0bce652-0f2c-11ed-8609-000c298fbddd

name =

tool_name = xtrabackup

tool_command = -uroot -pMmagedu0! --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

tool_version = 8.0.26-18

ibbackup_version = 8.0.26-18

server_version = 8.0.26

start_time = 2022-07-29 18:53:04

end_time = 2022-07-29 18:53:06

lock_time = 0

binlog_pos = filename 'binlog.000003', position '156'

innodb_from_lsn = 18242993

innodb_to_lsn = 18243411

partial = N

incremental = Y

format = file

compressed = N

encrypted = N

[root@centos8 ~]#cat /backup/inc1/xtrabackup_checkpoints

[root@centos8 ~]#cat /backup/inc1/xtrabackup_binlog_info

4）第二次修改数据

[root@CentOS8 inc1]# mysql -uroot -pMmagedu0!

(root@localhost) [(none)]> use hellodb;

(root@localhost) [hellodb]> insert into students (name,age) values ('zhao miin',17);

(root@localhost) [hellodb]> select * from students where name='zhao min';

+-------+----------+-----+--------+---------+-----------+

| StuID | Name     | Age | Gender | ClassID | TeacherID |

+-------+----------+-----+--------+---------+-----------+

|    27 | zhao min |  17 | F      |    NULL |      NULL |

+-------+----------+-----+--------+---------+-----------+

5）第二次增量

[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

[root@CentOS8 inc1]# ls /backup/

base  inc1  inc2

#查看xtrabackup相关文件

root@centos8 ~]#cat /backup/inc2/xtrabackup_info

[root@CentOS8 inc1]# cat /backup/inc2/xtrabackup_info

uuid = c5bc696a-0f2d-11ed-8609-000c298fbddd

name =

tool_name = xtrabackup

tool_command = -uroot -pMmagedu0! --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

tool_version = 8.0.26-18

ibbackup_version = 8.0.26-18

server_version = 8.0.26

start_time = 2022-07-29 19:01:16

end_time = 2022-07-29 19:01:18

lock_time = 0

binlog_pos = filename 'binlog.000004', position '156'

innodb_from_lsn = 18243411

innodb_to_lsn = 18243817

partial = N

incremental = Y

format = file

compressed = N

encrypted = N

[root@centos8 ~]#cat /backup/inc2/xtrabackup_binlog_info

6）[root@CentOS8 inc1]# scp -r /backup/* 10.0.0.18:/backup/

root@10.0.0.18's password:

xtrabackup_logfile                       100% 2560     1.1MB/s   00:00    

ibdata1                                  100%   12MB  28.7MB/s   00:00    

sys_config.ibd                           100%  112KB  16.8MB/s   00:00    

classes.ibd                              100%  112KB   6.6MB/s   00:00    

coc.ibd                                  100%  112KB  28.0MB/s   00:00    

courses.ibd                              100%  112KB  28.8MB/s   00:00    

scores.ibd                               100%  112KB  28.1MB/s   00:00    

students.ibd                             100%  112KB  30.1MB/s   00:00    

teachers.ibd                             100%  112KB  25.6MB/s   00:00    

toc.ibd                                  100%  112KB  23.3MB/s   00:00

#备份过程生成三个备份目录 /backup/{base，inc1，inc2}

[root@CentOS8 ~]# hostname -I

10.0.0.18

[root@CentOS8 ~]# ls /backup/

base  inc1  inc2

7) 模拟数据库破坏

Yum安装数据库目录在/var/lib/mysql中

[root@CentOS8 inc1]# ls /var/lib/mysql

 auto.cnf            '#ib_16384_1.dblwr'   mysqlx.sock

 binlog.000001        ib_buffer_pool       mysqlx.sock.lock

 binlog.000002        ibdata1              performance_schema

 binlog.000003        ib_logfile0          private_key.pem

 binlog.000004        ib_logfile1          public_key.pem

 binlog.index         ibtmp1               server-cert.pem

 ca-key.pem          '#innodb_temp'        server-key.pem

 ca.pem               mysql                sys

 client-cert.pem      mysql.ibd            undo_001

 client-key.pem       mysql.sock           undo_002

 hellodb              mysql.sock.lock

'#ib_16384_0.dblwr'   mysql_upgrade_info

[root@CentOS8 inc1]# mv  /var/lib/mysql /usr/local/

[root@CentOS8 inc1]# ls /var/lib/mysql

ls: cannot access '/var/lib/mysql': No such file or directory

[root@CentOS8 backup]# systemctl restart mysql

Failed to restart mysql.service: Unit mysql.service not found.

[root@CentOS8 backup]# mysql -uroot -pMmage0!

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

2还原过程

1）预准备完全备份，此选项--apply-log-only 阻止回滚未完成的事务

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

2）合并第1次增量备份到完全备份

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

3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only

[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base --incremental-

dir=/backup/inc2

4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动

[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base

5）还原属性：

[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql

6）启动服务：

[root@centos8 ~]#service mysqld start

可以看到数据库修好，能重新登录

[root@CentOS8 ~]# mysql -uroot -pMmagedu0!

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: 8.0.26 Source distribution

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.

(root@localhost) [(none)]> use hellodb;

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

(root@localhost) [hellodb]> select * from st

status             students.ClassID   students.StuID    

students           students.Gender    students.TeacherID

students.Age       students.Name     

(root@localhost) [hellodb]> select * from students;

+-------+---------------+-----+--------+---------+-----------+

| StuID | Name          | Age | Gender | ClassID | TeacherID |

+-------+---------------+-----+--------+---------+-----------+

|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |

|     2 | Shi Potian    |  22 | M      |       1 |         7 |