我有两台机器进行主从同步
但经常同步正常一段时间后, 用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都显示运行时间有很多天了呀,一头雾心中。。。