分享、学习、提高
2010/05/14 11:47
文章作者:Enjoy 转载请注明原文链接。
早晨机房意外断电,导致了发现mysql从服务器同步异常。使用以前碰到的Slave_SQL_Running为No的解决办法无效,仍然无法同步。

查看一下状态show slave status
            Master_Log_File: mysqlmaster.000079
        Read_Master_Log_Pos: 183913228
             Relay_Log_File: hx-relay-bin.002934
              Relay_Log_Pos: 183913371
      Relay_Master_Log_File: mysqlmaster.000079
           Slave_IO_Running: No
          Slave_SQL_Running: Yes

主服务器show master status\G
File: mysqlmaster.000080
Position: 13818288
Binlog_Do_DB:
Binlog_Ignore_DB: mysql,test

mysql错误日志:
100512  9:13:17 [Note] Slave SQL thread initialized, starting replication in log 'mysqlmaster.000079' at position 183913228, relay log './hx-relay-bin.002934' position: 183913371
100512  9:13:17 [Note] Slave I/O thread: connected to master 'replicuser@192.168.1.21:3306',  replication started in log 'mysqlmaster.000079' at position 183913228
100512  9:13:17 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
100512  9:13:17 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log
100512  9:13:17 [Note] Slave I/O thread exiting, read up to log 'mysqlmaster.000079', position 183913228

这次是Slave_IO_Running为No,从日志上来看,服务器读mysqlmaster.000079这个Log的183913228这个位置时发生错误,这个位置不存在,于是无法同步。

查看一下这个Log的最后几行:
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
# at 4
#100511  9:35:15 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.27-standard-log created 100511  9:35:15
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.

尝试从损坏之前的位置开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysqlcncnmaster.000079', MASTER_LOG_POS=183913220;
SLAVE START;
无效!
只好从新的日志开始
SLAVE STOP;
CHANGE MASTER TO MASTER_LOG_FILE='mysqlcncnmaster.000080', MASTER_LOG_POS=0;
SLAVE START;
此时Slave_IO_Running恢复为Yes,同步进行了!观察了会儿,没有任何出错迹象,问题解决。

另外,出现Slave_IO_Running:NO还有一个原因是slave上没有权限读master上的数据。
发表评论
表情
emotemotemotemotemot
打开HTML
打开UBB
打开表情
隐藏
昵称   密码   游客无需密码
网址   电邮   [注册]
               

验证码 不区分大小写