mysql主从

主从配置

mysql数据库主从设置

CHANGE MASTER TO MASTER_HOST='mysql8-cluster-primary', MASTER_USER='replicator', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000025',MASTER_LOG_POS=11817596;

开启slave

start slave;

停止slave

stop slave;

从库同步状态

show slave status;

主从状态查看

查看server id

主库和从库的server id要不一致

show variables like '%server_uuid%';

从库查看同步失败的具体报错

select * from performance_schema.replication_applier_status_by_worker\G;

主库查看报错的sql语句

mysqlbinlog --no-defaults -v --base64-output=decode-rows /bitnami/mysql/data/mysql-bin.000015 | grep -A 20 "1055009652" --color

主从重新同步

主库全局锁表与解锁

flush table with read lock;
unlock tables;主从状态恢复后主库解锁

重新设置主从

stop slave;
拷贝备份数据至从库
reset slave;

CHANGE MASTER TO MASTER_HOST='mysql8-cluster-primary', MASTER_USER='replicator', MASTER_PASSWORD='r0y20j1tmajc', MASTER_LOG_FILE='mysql-bin.000025',MASTER_LOG_POS=11817596;

start slave;

show slave status\G;

查看binlog模式

show variables like 'binlog_format';

查看slave_exec_mode

show variables like 'slave_exec_mode';


set global slave_exec_mode='IDEMPOTENT';
set global slave_exec_mode='STRICT';

跳过一个事件

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

查看被锁的表

show open tables where in_use > 0;

查看进程号

show processlist;
kill 进程号;