Java监控MYSQL关闭从库自动重启-创新互联

最近遭遇从库停止问题

通过命令查看

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元起，快前往官网查看详情吧