Chinaunix首页 | 论坛 | 博客
  • 博客访问: 961900
  • 博文数量: 83
  • 博客积分: 32
  • 博客等级: 民兵
  • 技术积分: 2080
  • 用 户 组: 普通用户
  • 注册时间: 2010-08-12 09:45
个人简介

努力学习,努力分享,努力.....在努力..努力离开..

文章分类

全部博文(83)

文章存档

2017年(3)

2015年(1)

2014年(47)

2013年(32)

分类: Mysql/postgreSQL

2014-01-17 18:50:30

悲催的昨晚刚躺在床上准备睡觉,同事给我打电话说营销数据库主从不同步了,我靠.咋就这么悲剧呢! 废话不说,速度开电脑拨VPN解决.

1、登录从库查看主从同步状态,确认是否不同步
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.40.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000610
          Read_Master_Log_Pos: 343903964
               Relay_Log_File: relaylog.000577
                Relay_Log_Pos: 343904041
        Relay_Master_Log_File: mysql-bin.000610
             Slave_IO_Running: No
            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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 343903895
              Relay_Log_Space: 343904405
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000610' at 343903964, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 26
1 row in set (0.00 sec)

mysql> exit
Bye
错误信息明确的说binlog被截断了,去看看343903964这个点到底是在做什么操作.

2、查看从库的错误日志    
[root@YB-db-112~]# tail -f /log/error.log 
140116 13:32:40 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000605' at 248481943, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299. ( server_errno=1236)
140116 13:32:40 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000605' at 248481943, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299.', Error_code: 1236
140116 13:32:40 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000610', position 343903964
140116 14:11:28 [Note] Error reading relay log event: slave SQL thread was killed
140116 14:22:35 [Note] Slave I/O thread: connected to master 'repl@192.168.40.111:3306',replication started in log 'mysql-bin.000610' at position 343903964
140116 14:22:35 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000610' at position 343903895, relay log '/log/relaylog/relaylog.000577' position: 343904041
140116 14:22:35 [ERROR] Error reading packet from server: binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000610' at 343903964, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299. ( server_errno=1236)
140116 14:22:35 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000610' at 343903964, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299.', Error_code: 1236
140116 14:22:35 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000610', position 343903964
140116 14:22:56 [Note] Error reading relay log event: slave SQL thread was killed

查看磁盘空间
[root@YB-db-111~]# df -h
文件系统              容量  已用  可用 已用%% 挂载点
/dev/sda5              39G  4.2G   33G  12% /
tmpfs                  32G  4.0K   32G   1% /dev/shm
/dev/sda3              49G  5.6G   41G  13% /backup
/dev/sda1             194M   28M  157M  15% /boot
/dev/sda2              97G   73G   19G  80% /log
/dev/sda7             1.2T   44G  1.1T   4% /mysql

3、尝试更改Position位置恢复复制
mysql>change master to master_host='192.168.40.111', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000610', master_log_pos=343903964;
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.40.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000610
          Read_Master_Log_Pos: 343903964
               Relay_Log_File: relaylog.000577
                Relay_Log_Pos: 343904041
        Relay_Master_Log_File: mysql-bin.000610
             Slave_IO_Running: No
            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: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 343903895
              Relay_Log_Space: 343904405
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.000610' at 343903964, the last event read from '/log/binlog/mysql-bin.000610' at 343903964, the last byte read from '/log/binlog/mysql-bin.000610' at 343904299.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 26
1 row in set (0.00 sec)

mysql> exit
Bye

-_- 我去.坑爹,还是不行啊...郁闷

4、查看Master的binlog日志,按照错误点的标记去主库的日志中查找
[root@YB-db-111~]# mysqlbinlog --no-defaults --start-position=343903964 /log/binlog/mysql-bin.000610 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140115 14:33:48 server id 26  end_log_pos 107  Start: binlog v 4, server v 5.5.32-log created 140115 14:33:48
BINLOG '
gCTKUg7bCCCCCCCCCCCCCQANS41LjMyLWxvZwXXXXXXXXXXXXXXXXXXXXXXXXXX
AAAAAAAAAAAAAAAAAAAAAAAAEzgNSAgAEgAEBAQEEgAAVAAEGhgAAAAICAgCAA==
'/*!*/;
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 364, event_type: 2
ERROR: Could not read entry at offset 343903964: Error in log format or read error.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[root@YB-db-111~]# mysqlbinlog --no-defaults /log/binlog/mysql-bin.000610 > 610.txt
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 364, event_type: 2
ERROR: Could not read entry at offset 343903964: Error in log format or read error.

[root@YB-db-111~]# vim 610.txt
# at 343903435
#140115 14:35:46 server id 26  end_log_pos 343903504    Query   thread_id=19871 exec_time=0     error_code=0
SET TIMESTAMP=1389767746/*!*/;
BEGIN
/*!*/;
# at 343903504
#140115 14:35:46 server id 26  end_log_pos 343903868    Query   thread_id=19871 exec_time=0     error_code=0
SET TIMESTAMP=1389767746/*!*/;
UPDATE way........................................
/*!*/;
# at 343903868
#140115 14:35:46 server id 26  end_log_pos 343903895    Xid = 536002
COMMIT/*!*/;
# at 343903895
#140115 14:35:46 server id 26  end_log_pos 343903964    Query   thread_id=19870 exec_time=0     error_code=0
SET TIMESTAMP=xxxxxxxxx/*!*/;
BEGIN
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

这是在mysql-bin.000610的最后一个地方,显示开启了一个事务,但是事务没有继续往后执行,我已经把max_binlog_size设置的1G了,查看mysql官网的说明,当binlog最后遇到一个大事务时会将日志记录在这个日志,而不会跨binlog记录一个事务,于是我继续查看看mysql-bin.000611的日志头内容;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#140116 14:19:08 server id 26  end_log_pos 107  Start: binlog v 4, server v 5.5.32-log created 140116 14:19:08 at startup
BINLOG '
3HnXUg8aAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADceddSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#140115 14:34:48 server id 10  end_log_pos 166  Query   thread_id=211882628     exec_time=85473 error_code=0
SET TIMESTAMP=1389767688/*!*/;
SET @@session.pseudo_thread_id=211882628/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 166
# at 243
mysql-bin.000611这个日志的开头也没有说明是继续上一个binlog的最后一个事务.

5、从新change master指定新binlog恢复主从同步
mysql> change master to master_host='192.168.40.111', master_port=3306, master_user='repl', master_password='123456', master_log_file='mysql-bin.000611', master_log_pos=0;
mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.40.111
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000611
          Read_Master_Log_Pos: 7180578
               Relay_Log_File: relaylog.000005
                Relay_Log_Pos: 1302
        Relay_Master_Log_File: mysql-bin.000611
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 7180578
              Relay_Log_Space: 7181019
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 26
1 row in set (0.00 sec)

OK了.终于可以了.关机睡觉..^_^

顺便提一个问题哪位大侠知道告诉我,Google很多说是mysqlbinlog版本问题.关于mysqlbinlog报如下错误
ERROR: Error in Log_event::read_log_event(): 'read error', data_len: 364, event_type: 2
ERROR: Could not read entry at offset 343903964: Error in log format or read error.

阅读(6664) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~