目录实验说明参数说明时点说明xtrabackup备份恢复备份prepare恢复复制回原目录relaylog增量恢复初始修改server_id初始化master拷贝binlog为relaylog修改index重启数据库查看
本次实验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表插入数据 |
[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!
[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
做为relay log来读取binlog,需要server id不能一致。
重启sever会自动启动slave sql thread。我个人尝试start slave sql_thread不能成功,但是重启实例可以。
vi /etc/my.cnf
server_id=2
主要是通过该命令将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的格式完全相同,只需要将名字处理成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同步修改
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)
接续的时候发现relay-log.info里记录的是binlog的名称,这个流程没有指定过pos。怀疑是relaylog内指定的下一个relaylog名称。
cp mysql57-1-bin.000002 /home/mysql /data/mysql57-1-relay-bin.000002
chown mysql:mysql /data/mysql57-1-relay-bin.000002
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信息,避免报错影响
注:reset slave不会清除同步信息。
reset slave all;
到此这篇关于mysql使用xtrbackup+relaylog增量恢复的文章就介绍到这了,更多相关mysql增量恢复内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!
--结束END--
本文标题: mysql使用xtrbackup+relaylog增量恢复注意事项
本文链接: https://www.lsjlt.com/news/202078.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-22
2024-05-21
2024-05-21
2024-05-21
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
一口价域名售卖能注册吗?域名是网站的标识,简短且易于记忆,为在线用户提供了访问我们网站的简单路径。一口价是在域名交易中一种常见的模式,而这种通常是针对已经被注册的域名转售给其他人的一种方式。
一口价域名买卖的过程通常包括以下几个步骤:
1.寻找:买家需要在域名售卖平台上找到心仪的一口价域名。平台通常会为每个可售的域名提供详细的描述,包括价格、年龄、流
443px" 443px) https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294.jpg https://www.west.cn/docs/wp-content/uploads/2024/04/SEO图片294-768x413.jpg 域名售卖 域名一口价售卖 游戏音频 赋值/切片 框架优势 评估指南 项目规模
0