mysql gtid 主备搭建

最新推荐文章于 2022-11-25 11:02:48 发布

东方-phantom

最新推荐文章于 2022-11-25 11:02:48 发布

阅读量228

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/hkyw000/article/details/107952921

版权

mysql 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

主库刷新权限repl：

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED BY '123456'; 
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'192.168.8.82' IDENTIFIED BY '123456';

主备库执行：

set @@global.enforce_gtid_consistency=on;
set @@global.gtid_mode=on;
show status like 'ongoing_anonymous_transaction_count' 为0

修改my.cnf

vi /etc/my.cnf

gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates=1
binlog_format = row
log_bin=/u01/my3306/log/binlog/binlog

dump全库：

mysqldump  -uroot -p --socket='/u01/my3306/run/mysql.sock' --master-data=2 --single-transaction --set-gtid-purged=off --log-error=all.log -A >all81off.sql

传输备份到备库，开启限流，防止带宽被占满;

scp -P9822 -l 50000  all81.sql root@192.168.8.82:/tmp

备库首先reset master

stop slave;
reset master;
CHANGE MASTER TO MASTER_HOST='192.168.8.81', MASTER_USER='repl', MASTER_PORT=3306, master_password='123456',MASTER_AUTO_POSITION = 1;
start slave;

如果不行就先按照position同步：

change master to master_host='192.168.8.81',master_port=3306,master_user='repl',master_password='123456',master_log_file='binlog.000022',master_log_pos=136729776;