ShardingSphere主从复制遇到的问题

1、 Slave_SQL_Running: No

问题描述

SHOW SLAVE STATUS\G

Last_Error字段报错：

Coordinator stopped because there were error(s) in the worker(s).  The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log binlog. 000008, end_log_pos 725.  See error log and/or performance_schema. replication_applier_status_by_worker table for more details about this failure or others, if any

查询performance_schema库下的replication_applier_status_by_worker表，看到

Worker 1 failed executing transaction ‘ANONYMOUS’ at master log binlog.000008, end_log_pos 725; Error ‘Unknown table ‘db_user.t_user’’ on query. Default database: ‘db_user’. Query: ‘DROP TABLE t_user /* generated by server */’

解决

-- 查看从服务的状态
SHOW SLAVE STATUS\G

-- 查看最新的主服务信息，更新change SQL的对应值
SHOW MASTER STATUS;

-- 停止同步
STOP SLAVE;

-- 修改主服务的值，此操作为手动同步
CHANGE MASTER TO MASTER_HOST='192.168.229.128', 
MASTER_USER='dk_slave',MASTER_PASSWORD='123456', MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000010',MASTER_LOG_POS=157;

-- 开启同步
START SLAVE;

2、手动同步值报错

问题描述

执行

CHANGE MASTER TO MASTER_HOST='192.168.229.128', 
MASTER_USER='dk_slave',MASTER_PASSWORD='123456', MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000010',MASTER_LOG_POS=157;

报错

CHANGE MASTER cannot be executed when the slave was stopped with an error or killed in MTS mode. Consider using RESET SLAVE or START SLAVE UNTIL

解决

sql执行

RESET SLAVE;

-- 停止同步
STOP SLAVE;

-- 修改主服务的值，此操作为手动同步
CHANGE MASTER TO MASTER_HOST='192.168.229.128', 
MASTER_USER='dk_slave',MASTER_PASSWORD='123456', MASTER_PORT=3307,
MASTER_LOG_FILE='binlog.000010',MASTER_LOG_POS=157;

-- 开启同步
START SLAVE;