mysql 1236错误_【[求助]主从复制1236错误的奇怪问题 ( server_errno=1236)】-MySQL论坛-ZOL中关村在线...

我有两台机器进行主从同步

但经常同步正常一段时间后， 用show slave statusG查看的结果就会这样*************************** 1. row ***************************

Slave_IO_State:

Master_Host: 192.168.1.109

Master_User: rsync

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 1185984

Relay_Log_File: host104-relay-bin.000002

Relay_Log_Pos: 1186121

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: No

Slave_SQL_Running: Yes

Replicate_Do_DB: abc,def

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: 1185984

Relay_Log_Space: 1186121

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查看mysql的错误日志提示：

081229 13:50:02 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)

081229 13:50:02 [ERROR] Got fatal error 1236: 'Client requested master to start replication from impossible position' from master when reading data from binary log

081229 13:50:02 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000002', position 1185984到master端用mysqlbinlog查找mysql-bin.000002文件中的1185984，发现没有，其末尾的position都是136901，整整 多了一位数，在13:50:02所在的position为74011然后我又在slave端执行命令slave stop;CHANGE MASTER TO MASTER_HOST='192.168.1.109',MASTER_USER='rsync',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=74011;slave start;

然后同步又开始正常了，在master端进行插入更新，slave端都能同步过来，可奇怪的事，过几个小时或者一天后又会出现上面的问题，我在网上找了很久都没找到怎么解决，

我有对全局变量sync_binlog进行设置

set global sync_binlog=1;以下是我的my.cnf配置

master端:[client]

port            = 3306

socket          = /tmp/mysql.sock[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

key_buffer = 64M

max_allowed_packet = 250M

table_cache = 128

sort_buffer_size = 16M

net_buffer_length = 64K

read_buffer_size = 1024K

read_rnd_buffer_size = 1024K

myisam_sort_buffer_size = 16M

thread_cache_size = 256

max_binlog_size= 100000000

binlog_cache_size = 1000000

tmp_table_size = 128M

log-slow-queries=/usr/local/mysql/var/mysql-slow.log

long_query_time=2

query_cache_limit  = 1M

query_cache_size   = 32M

log-bin=mysql-bin

server-id       = 1

binlog-do-db = abc

binlog-do-db = def[mysqldump]

quick

max_allowed_packet = 16M[mysql]

no-auto-rehash[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M[mysqlhotcopy]

interactive-timeout

slave端:[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-locking

key_buffer = 64M

max_allowed_packet = 250M

table_cache = 128

sort_buffer_size = 16M

net_buffer_length = 64K

read_buffer_size = 1024K

read_rnd_buffer_size = 1024K

myisam_sort_buffer_size = 16M

thread_cache_size = 256

max_binlog_size= 100000000

binlog_cache_size = 1000000

tmp_table_size = 128M

log-slow-queries=/usr/local/mysql/var/mysql-slow.log

long_query_time=2

query_cache_limit  = 1M

query_cache_size   = 32M

log-bin=host104-binserver-id   = 2

master-host = 192.168.1.109

master-user = rsync

master-password = 123456

master-port = 3306

master-connect-retry = 10

replicate-do-db = abc

replicate-do-db = def[mysqldump]

quick

max_allowed_packet = 16M[mysql]

no-auto-rehash[isamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M[myisamchk]

key_buffer = 20M

sort_buffer_size = 20M

read_buffer = 2M

write_buffer = 2M[mysqlhotcopy]

interactive-timeout在mysql的FAQ中找到

错误：1236 SQLSTATE: HY000 (ER_MASTER_FATAL_ERROR_READING_BINLOG)消息：从二进制日志读取数据时，获得来自主服务器的致命错误%d: '%s'。不太确定是不是类似的错误，如果是的话是什么引起的，如何解决?，如果不是又要如何解决?请高人指点一下，谢谢了！我查了下网上说这种 原因一般在机器断电才容易出现，可是我用uptime都显示运行时间有很多天了呀，一头雾心中。。。