最近遭遇從庫停止問題

成都創(chuàng)新互聯(lián)專業(yè)提供托管服務(wù)器服務(wù)，為用戶提供五星數(shù)據(jù)中心、電信、雙線接入解決方案，用戶可自行在線購買托管服務(wù)器服務(wù)，并享受7*24小時(shí)金牌售后服務(wù)。

通過命令查看

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確認(rèn)問題

查看binlog 日志錯(cuò)誤命令， 根據(jù)自己的目錄

/usr/local/mysql/bin/mysqlbinlog /data/mysql/var/mysql-bin.003196 | grep -10 76635940

如果發(fā)現(xiàn)sql錯(cuò)誤修改即可

為了防止類似問題，要通過java自動(dòng)監(jiān)控

1、主庫創(chuàng)建一個(gè)用于復(fù)制的用戶

mysql>create user 'repl'@'%' identified by '123456';

2、將replication slave,replication client 授權(quán)到repl用戶

給他所有權(quán)限，這樣才能通過java執(zhí)行show slave status 命令

mysql>grant all on *.* to 'repl'@'%';

3、刷新權(quán)限

mysql>flush privileges;

查看權(quán)限

mysql>show grants for 'repl'@'%';

如果權(quán)限不是all權(quán)限的話，會(huì)報(bào)錯(cuò)

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 驅(qū)動(dòng)名及數(shù)據(jù)庫 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{
            // 注冊(cè) JDBC 驅(qū)動(dòng)
            Class.forName(JDBC_DRIVER);

            // 打開鏈接
            logger.info("連接數(shù)據(jù)庫...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);

            // 執(zhí)行查詢
            logger.info(" 實(shí)例化Statement對(duì)象...");
            stmt = conn.createStatement();
            String sql;

            sql = "show slave status";
            ResultSet rs = stmt.executeQuery(sql);

            // 展開結(jié)果集數(shù)據(jù)庫
            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");
                }
            }
            // 完成后關(guān)閉
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 處理 JDBC 錯(cuò)誤
            se.printStackTrace();
        }catch(Exception e){
            // 處理 Class.forName 錯(cuò)誤
            e.printStackTrace();
        }finally{
            // 關(guān)閉資源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        logger.info("Goodbye!");
    }
}

你是否還在尋找穩(wěn)定的海外服務(wù)器提供商？創(chuàng)新互聯(lián)www.cdcxhl.cn海外機(jī)房具備T級(jí)流量清洗系統(tǒng)配攻擊溯源，準(zhǔn)確流量調(diào)度確保服務(wù)器高可用性，企業(yè)級(jí)服務(wù)器適合批量采購，新人活動(dòng)首月15元起，快前往官網(wǎng)查看詳情吧

當(dāng)前題目：Java監(jiān)控MYSQL關(guān)閉從庫自動(dòng)重啟-創(chuàng)新互聯(lián)
標(biāo)題網(wǎng)址：http://weahome.cn/article/dpejjd.html