mysql使用xtrbackup+relaylog增量恢复

实验说明

本次实验mysql5.7.19.使用了GTID，row格式的binlog

参数说明

[mysql]

password=root@1234



[mysqld]

server_id=1

log_bin

gtid_mode=1

binlog_format=row

basedir=/usr/local/mysql

datadir=/home/mysql/data

时点说明

bin.000001 1-154	创建test表
bin.000001 154	xtrabackup --backup
bin.000001 154-end	创建test_dml表,test表数据
bin.000002	test表插入数据

xtrabackup备份恢复

备份

[root@mysql57-1 tmp]# xtrabackup --user=root --password=root@1234 --socket=/tmp/mysql.sock --backup --target-dir=/tmp/bak

....



230508 15:48:12 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...

xtrabackup: The latest check point (for incremental): '2568864'

xtrabackup: Stopping log copying thread.

.230508 15:48:12 >> log scanned up to (2568873)



230508 15:48:13 Executing UNLOCK TABLES

230508 15:48:13 All tables unlocked

230508 15:48:13 [00] Copying ib_buffer_pool to /tmp/bak/ib_buffer_pool

230508 15:48:13 [00]        ...done

230508 15:48:13 Backup created in directory '/tmp/bak/'

MySQL binlog position: filename 'mysql57-1-bin.000001', position '154'

230508 15:48:13 [00] Writing /tmp/bak/backup-my.cnf

230508 15:48:13 [00]        ...done

230508 15:48:13 [00] Writing /tmp/bak/xtrabackup_info

230508 15:48:13 [00]        ...done

xtrabackup: Transaction log of lsn (2568864) to (2568873) was copied.

230508 15:48:13 completed OK!

prepare恢复

[root@mysql57-1 bak]# xtrabackup --prepare --target-dir=/tmp/bak





InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.

InnoDB: 32 non-redo rollback segment(s) are active.

InnoDB: 5.7.40 started; log sequence number 2569237

xtrabackup: starting shutdown with innodb_fast_shutdown = 1

InnoDB: FTS optimize thread exiting.

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 2569256

230511 16:54:37 completed OK!

复制回原目录

cp -r /tmp/bak /home/mysql/data

chown -R mysql:mysql /home/mysql/data

relaylog增量恢复初始

修改server_id

做为relay log来读取binlog，需要server id不能一致。

重启sever会自动启动slave sql thread。我个人尝试start slave sql_thread不能成功，但是重启实例可以。

vi /etc/my.cnf

server_id=2

初始化master

主要是通过该命令将relaylog的相关初始化出来。因为使用的GTID，不需要指定pos

CHANGE MASTER TO master_host='1',master_password='1',master_user='1',master_log_file='1',master_log_pos=4;

拷贝binlog为relaylog

binlog和relaylog的格式完全相同，只需要将名字处理成relaylog的样式即可

如需要恢复多个，拷贝多个过去即可，我这里的演示只拷贝了一个binlog

cp mysql57-1-bin.000001 /home/mysql/data/mysql57-1-relay-bin.000001

chown mysql:mysql /home/mysql/data/mysql57-1-relay-bin.00000*

修改index

INDEX同步修改

vi mysql57-1-relay-bin.index

./mysql57-1-relay-bin.000001

重启数据库

/etc/init.d/mysql restart

可以看到日志中io thread 错误，sql thread正常

2023-05-11T13:31:30.297886Z 1 [Warning] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider

using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.

2023-05-11T13:31:30.297994Z 1 [ERROR] Slave I/O for channel '': error connecting to master '1@1:3306' - retry-time: 60  retries: 1, Error_code: 2003

2023-05-11T13:31:30.299542Z 2 [Warning] Slave SQL for channel '': If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0

2023-05-11T13:31:30.299616Z 2 [Note] Slave SQL thread for channel '' initialized, starting replication in log '1' at position 4, relay log './mysql57-1-relay-bin.000001' position: 4

2023-05-11T13:31:30.304018Z 0 [Note] Event Scheduler: Loaded 0 events

2023-05-11T13:31:30.304223Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.

Version: '5.7.19-log'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Server (GPL)

查看数据确认

mysql> show tables;
+---------------+
| Tables_in_ddd |
+---------------+
| ddl_test      |
| dml_test      |
| pitr          |
| test          |
| ttt           |
+---------------+
5 rows in set (0.00 sec)

mysql> select * from test;
+---+---------------------+
| x | y                   |
+---+---------------------+
| 1 | 2023-05-08 15:51:37 |
| 2 | 2023-05-08 15:51:39 |
| 3 | 2023-05-08 15:51:42 |
| 4 | 2023-05-08 15:51:45 |
| 5 | 2023-05-08 15:51:49 |
+---+---------------------+
5 rows in set (0.00 sec)

mysql> select * from dml_test;
Empty set (0.00 sec)

relaylog增量恢复接续

接续的时候发现relay-log.info里记录的是binlog的名称，这个流程没有指定过pos。怀疑是relaylog内指定的下一个relaylog名称。

拷贝binlog为relaylog

cp mysql57-1-bin.000002 /home/mysql /data/mysql57-1-relay-bin.000002
chown mysql:mysql /data/mysql57-1-relay-bin.000002

修改index

INDEX同步修改

vi mysql57-1-relay-bin.index

./mysql57-1-relay-bin.000002

重启数据库

/etc/init.d/mysql restart

查看数据确认

mysql> use ddd;
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_ddd |
+---------------+
| ddl_test      |
| dml_test      |
| pitr          |
| test          |
| ttt           |
+---------------+
5 rows in set (0.00 sec)

mysql> select * from dml_test;
+------+------+
| x    | y    |
+------+------+
|    1 | 123  |
|    1 | qwe  |
|    1 | ttt  |
+------+------+
3 rows in set (0.00 sec)

slave信息清理

恢复流程完成后，清理slave信息，避免报错影响

注：reset slave不会清除同步信息。

reset slave all;

学习原理，孵化思路。积累工具，下笔有道。