最近遭遇从库停止问题
通过命令查看
show slave status\G
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.3.26
Master_User: repl_user02
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.003197
Read_Master_Log_Pos: 818205249
Relay_Log_File: MySQL-Box-Tx-Slave02-relay-bin.005555
Relay_Log_Pos: 76561384
Relay_Master_Log_File: mysql-bin.003196
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1114
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 mysql-bin.003196, end_log_pos 76635940. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
可以通过排查binlog确认问题
查看binlog 日志错误命令， 根据自己的目录
/usr/local/mysql/bin/mysqlbinlog /data/mysql/var/mysql-bin.003196 | grep -10 76635940
如果发现sql错误修改即可
为了防止类似问题，要通过java自动监控
1、主库创建一个用于复制的用户
mysql>create user 'repl'@'%' identified by '123456';
2、将replication slave,replication client 授权到repl用户
给他所有权限，这样才能通过java执行show slave status 命令
mysql>grant all on *.* to 'repl'@'%';
3、刷新权限
mysql>flush privileges;
查看权限
mysql>show grants for 'repl'@'%';
如果权限不是all权限的话，会报错
Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation
public class SlaveDemo {
private static Logger logger = LoggerFactory.getLogger(SlaveDemo.class);
// MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://103.131.169.71:3306/cloudbox";
static final String USER = "repl";
static final String PASS = "123456";
public static void test() {
Connection conn = null;
Statement stmt = null;
try{
// 注册 JDBC 驱动
Class.forName(JDBC_DRIVER);
// 打开链接
logger.info("连接数据库...");
conn = DriverManager.getConnection(DB_URL,USER,PASS);
// 执行查询
logger.info(" 实例化Statement对象...");
stmt = conn.createStatement();
String sql;
sql = "show slave status";
ResultSet rs = stmt.executeQuery(sql);
// 展开结果集数据库
while(rs.next()) {
logger.info("Slave_IO_State:"+rs.getString(1));
logger.info("Master_Host:"+rs.getString(2));
logger.info("Master_User:"+rs.getString(3));
logger.info("Master_Port:"+rs.getString(4));
logger.info("Connect_Retry:"+rs.getString(5));
logger.info("Master_Log_File:"+rs.getString(6));
logger.info("Read_Master_Log_Pos:"+rs.getString(7));
logger.info("Relay_Log_File:"+rs.getString(8));
logger.info("Relay_Log_Pos:"+rs.getString(9));
logger.info("Relay_Master_Log_File:"+rs.getString(10));
logger.info("Slave_IO_Running :" + rs.getString(11));
String Slave_SQL_Running = rs.getString(12);
logger.info("Slave_SQL_Running :" + Slave_SQL_Running);
logger.info("Seconds_Behind_Master:" + rs.getString(33));
if ("No".equals(Slave_SQL_Running)) {
logger.info("Last_Error :" + rs.getString(20));
logger.info("Last_SQL_Error_Timestamp :" + rs.getString(49));
logger.info("stop slave");
stmt.execute("stop slave");
logger.info("set GLOBAL SQL_SLAVE_SKIP_COUNTER=1");
stmt.execute("set GLOBAL SQL_SLAVE_SKIP_COUNTER=1");
logger.info("start slave");
stmt.execute("start slave");
logger.info("done");
}
}
// 完成后关闭
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
// 处理 JDBC 错误
se.printStackTrace();
}catch(Exception e){
// 处理 Class.forName 错误
e.printStackTrace();
}finally{
// 关闭资源
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}// 什么都不做
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
logger.info("Goodbye!");
}
}你是否还在寻找稳定的海外服务器提供商？创新互联www.cdcxhl.cn海外机房具备T级流量清洗系统配攻击溯源，准确流量调度确保服务器高可用性，企业级服务器适合批量采购，新人活动首月15元起，快前往官网查看详情吧
