mysql binlog 有问题 请大家帮帮忙

一棵菠菜

1. root@mysql1:/usr/local/mysql# bin/mysqlbinlog var/mysql-bin.000001
   
2. /*!40019 SET @@session.max_insert_delayed_threads=0*/;
   
3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
   
4. DELIMITER /*!*/;
   
5. # at 4
   
6. #111101 21:01:55 server id 128  end_log_pos 106         Start: binlog v 4, server v 5.1.37-log created 111101 21:01:55 at startup
   
7. # Warning: this binlog is either in use or was not closed properly.
   
8. ROLLBACK/*!*/;
   
9. BINLOG '
   
10. w+2vTg+AAAAAZgAAAGoAAAABAAQANS4xLjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
    
11. AAAAAAAAAAAAAAAAAADD7a9OEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
    
12. '/*!*/;
    
13. ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 78, event_type: 2
    
14. DELIMITER ;
    
15. # End of log file
    
16. ROLLBACK /* added by mysqlbinlog */;
    
17. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

复制代码

我在主库上创建了一个aaa的数据库，但是binlog确实这样，我刷过日志，也没用
主库的信息

1. mysql> show processlist;
   
2. +----+------------+----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
   
3. | Id | User       | Host                 | db   | Command     | Time | State                                                          | Info             |
   
4. +----+------------+----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
   
5. |  2 | mysqlrsync | 192.168.11.129:40801 | NULL | Binlog Dump |  417 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             |
   
6. |  3 | root       | localhost            | NULL | Query       |    0 | NULL                                                           | show processlist |
   
7. +----+------------+----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
   
8. 2 rows in set (0.00 sec)

复制代码

slave 是信息

1. mysql> show slave status\G;
   
2. *************************** 1. row ***************************
   
3.                Slave_IO_State: Waiting for master to send event
   
4.                   Master_Host: 192.168.11.128
   
5.                   Master_User: mysqlrsync
   
6.                   Master_Port: 3306
   
7.                 Connect_Retry: 60
   
8.               Master_Log_File: mysql-bin.000001
   
9.           Read_Master_Log_Pos: 184
   
10.                Relay_Log_File: mysql-relay-bin.000002
    
11.                 Relay_Log_Pos: 251
    
12.         Relay_Master_Log_File: mysql-bin.000001
    
13.              Slave_IO_Running: Yes
    
14.             Slave_SQL_Running: No
    
15.               Replicate_Do_DB:
    
16.           Replicate_Ignore_DB:
    
17.            Replicate_Do_Table:
    
18.        Replicate_Ignore_Table:
    
19.       Replicate_Wild_Do_Table:
    
20.   Replicate_Wild_Ignore_Table:
    
21.                    Last_Errno: 1594
    
22.                    Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
    
23.                  Skip_Counter: 0
    
24.           Exec_Master_Log_Pos: 106
    
25.               Relay_Log_Space: 484
    
26.               Until_Condition: None
    
27.                Until_Log_File:
    
28.                 Until_Log_Pos: 0
    
29.            Master_SSL_Allowed: No
    
30.            Master_SSL_CA_File:
    
31.            Master_SSL_CA_Path:
    
32.               Master_SSL_Cert:
    
33.             Master_SSL_Cipher:
    
34.                Master_SSL_Key:
    
35.         Seconds_Behind_Master: NULL
    
36. Master_SSL_Verify_Server_Cert: No
    
37.                 Last_IO_Errno: 0
    
38.                 Last_IO_Error:
    
39.                Last_SQL_Errno: 1594
    
40.                Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
    
41. 1 row in set (0.00 sec)
    
42. 
    
43. ERROR:
    
44. No query specified

复制代码

一棵菠菜

1. 主库的配置文件
   
2. [client]
   
3. port            = 3306
   
4. socket          = /tmp/mysql.sock
   
5. 
   
6. [mysqld]
   
7. port            = 3306
   
8. socket          = /tmp/mysql.sock
   
9. key_buffer_size = 16M
   
10. max_allowed_packet = 1M
    
11. table_open_cache = 64
    
12. sort_buffer_size = 512K
    
13. net_buffer_length = 8K
    
14. read_buffer_size = 256K
    
15. read_rnd_buffer_size = 512K
    
16. myisam_sort_buffer_size = 8M
    
17. log-bin=mysql-bin
    
18. server-id=128
    
19. skip-name-resolve
    
20. [mysqldump]
    
21. quick
    
22. max_allowed_packet = 16M
    
23. 
    
24. [mysql]
    
25. no-auto-rehash
    
26. 
    
27. [myisamchk]
    
28. key_buffer_size = 20M
    
29. sort_buffer_size = 20M
    
30. read_buffer = 2M
    
31. write_buffer = 2M
    
32. 
    
33. [mysqlhotcopy]
    
34. interactive-timeout

复制代码

一棵菠菜

1. 从库的
   
2. [client]
   
3. port            = 3306
   
4. socket          = /tmp/mysql.sock
   
5. 
   
6. 
   
7. [mysqld]
   
8. port            = 3306
   
9. socket          = /tmp/mysql.sock
   
10. key_buffer_size = 16M
    
11. max_allowed_packet = 1M
    
12. table_open_cache = 64
    
13. sort_buffer_size = 512K
    
14. net_buffer_length = 8K
    
15. read_buffer_size = 256K
    
16. read_rnd_buffer_size = 512K
    
17. myisam_sort_buffer_size = 8M
    
18. relay_log = mysql-relay-bin
    
19. server-id=129
    
20. skip-name-resolve
    
21. slave-skip-errors=all
    
22. [mysqldump]
    
23. quick
    
24. max_allowed_packet = 16M
    
25. 
    
26. [mysql]
    
27. no-auto-rehash
    
28. 
    
29. [myisamchk]
    
30. key_buffer_size = 20M
    
31. sort_buffer_size = 20M
    
32. read_buffer = 2M
    
33. write_buffer = 2M
    
34. 
    
35. [mysqlhotcopy]
    
36. interactive-timeout

复制代码

一棵菠菜

mysql版本是mysql-5.1.37

cenalulu

master 上执行
mysqlbinlog -vv var/mysql-bin.000001

slave 上执行
mysqlbinlog -vv var/mysql-relay-bin.000002

看看哪个会提示日志错误，如果只是slave有错，那就change master to 这条记录 重新start slave。
如果主库日志就有错，那就只能在slave上跳过这条日志了

一棵菠菜

我所有的binlog都基本一样  就像上面那样。

yinlenc

我这样回答你的问题吧：

1. 
   
2. [root@Asterisk data]# mysqlbinlog mysql-bin.000029|wc -l
   
3. ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 75, event_type: 19
   
4. Could not read entry at offset 1058:Error in log format or read error
   
5. 53
   
6. [root@Asterisk data]# ../bin/mysqlbinlog mysql-bin.000029|wc -l
   
7. 11834
   

复制代码

mysql版本问题，可能之前你有另外一个版本。

yinlenc

我这样回答你的问题吧：

1. 
   
2. [root@Asterisk data]# mysqlbinlog mysql-bin.000029|wc -l
   
3. ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 75, event_type: 19
   
4. Could not read entry at offset 1058:Error in log format or read error
   
5. 53
   
6. [root@Asterisk data]# ../bin/mysqlbinlog mysql-bin.000029|wc -l
   
7. 11834
   

复制代码

mysql版本问题，可能之前你有另外一个版本。

yinlenc

我这样回答你的问题吧：

1. 
   
2. [root@Asterisk data]# mysqlbinlog mysql-bin.000029|wc -l
   
3. ERROR: Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 75, event_type: 19
   
4. Could not read entry at offset 1058:Error in log format or read error
   
5. 53
   
6. [root@Asterisk data]# ../bin/mysqlbinlog mysql-bin.000029|wc -l
   
7. 11834
   

复制代码

mysql版本问题，可能之前你有另外一个版本。

yinlenc

晕，怎么一下子提交了3次了呢。。。