MySQL8的主从复制操作

– 锁表
– FLUSH TABLES WITH READ LOCK;

– mysqldump
mysqldump -udbadmin -p’1688Jxzj@250011!!’ --single-transaction --source_data=1 --set-gtid-purged=OFF trade_engine > /home/ecs-user/trade_engine.sql

– 传输数据文件到从服务器
scp trade_engine.sql 172.30.125.125:/home/ecs-user

– 从库导入数据
mysql -uroot -pJxzj@1688.com trade_engine < trade_engine.sql

– head -n 30 trade_engine.sql

SHOW BINARY LOG STATUS;
show REPLICA STATUS;
show variables like ‘%server_id%’;
SELECT @@server_id

STOP REPLICA IO_THREAD FOR CHANNEL ‘’;

– 建立主从配置关系
CHANGE REPLICATION SOURCE TO source_host=‘172.30.125.120’,source_user=‘replication’,source_port=12306,source_password=‘Jxzj@16880727’,SOURCE_LOG_FILE=‘mysql-bin.000004’, SOURCE_LOG_POS=5544547;

– 启动从库
start replica;

– RESET replica ALL
stop replica;

– SHOW MASTER STATUS; – 记录下Position值和File名称
– RESET MASTER;

– 解锁
UNLOCK TABLES;