目前被这个问题弄得是焦头烂额,哪位高手帮帮忙吧。
情况是这样的:
我在配置了可以实现数据库复制的Master/Slave机制后,经常出现1062错误,间隔时间不定。提示:Error 'Duplicate entry 'xxx' for key 1' on query. 而且mysql并没有停止,而是继续复制这一行数据,导致数据库中出现大量重复数据,我只好手动删除这些数据,然后就能正常运行一段时间,很是麻烦。正常情况下,slave上的sql语句如果出错,它会停止的吧。可是很奇怪的是slave上的sql线程没有停止,不然也不会大量继续插入重复的数据了。
详细信息如下:
主服务器上配置信息如下:
mysqld]
basedir=F:/usr/local/mysql4
datadir=F:/usr/local/mysql4/data
port=3306
skip-locking
key_buffer=384M
max_allowed_packet=1M
table_cache=512
sort_buffer_size=2M
read_buffer_size = 2M
net_buffer_length=2K
query_cache_size = 32M
thread_stack=64K
default-character-set=gbk
tmp_table_size=40G
tmpdir=F:/usr/mysqltempserver-id=1
log-bin
binlog-do-db=gupiao
binlog-do-db=discuz
从服务器上配置信息如下:
basedir=D:/usr/local/mysql4
datadir=D:/usr/local/mysql4/data
port=3306
skip-locking
key_buffer=16K
max_allowed_packet=1M
max_connections=1000
table_cache=4
sort_buffer_size=64K
net_buffer_length=2K
thread_stack=64K
default-character-set=gbk server-id=2
master-host=........
master-user=........
master-password=............
master-port=3306
replicate-do-db=discuz
replicate-do-db=gupiao
replicate-wild-ignore-table=gupiao.jos_content_%
query_cache_size = 128M我从错误日志中截取了一段相关信息:
080422 8:50:25 InnoDB: Started
080422 8:50:25 Slave SQL thread initialized, starting replication in log 'tjtele-s1mg7462-bin.192' at position 315626155, relay log '.\tjtele-zw6fce3k-relay-bin.345' position: 238799426
D:\usr\local\mysql4\bin\mysqld-nt.exe: ready for connections.
Version: '4.0.22-nt' socket: '' port: 3306 Source distribution
080422 8:50:25 Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'tjtele-s1mg7462-bin.193' at position 150463891
ERROR: 1062 Duplicate entry '257959' for key 1
080422 14:33:31 Slave: Error 'Duplicate entry '257959' for key 1' on query. Default database: 'discuz'. Query: 'INSERT INTO `cdb_posts` ( `fid` , `tid` , `first` , `author`, `authorid` , `subject` , `dateline` , `message` , `useip`) VALUES ('600030','257932','1','songyouyou','10','降低印花税的确存在智力障碍','1208844028',' 降低印花税的确存在智力障碍 \r\n降低印花税的确存在智力障碍 \r\n\r\n\r\n','202.0.235.244')', Error_code: 1062
080422 14:33:31 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'tjtele-s1mg7462-bin.193' position 528993868
080422 14:57:46 D:\usr\local\mysql4\bin\mysqld-nt.exe: Normal shutdown080422 14:57:46 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)
080422 14:57:46 Slave I/O thread killed while reading event
080422 14:57:46 Slave I/O thread exiting, read up to log 'tjtele-s1mg7462-bin.193', position 572132543
080422 14:57:47 D:\usr\local\mysql4\bin\mysqld-nt.exe: Forcing close of thread 3210 user: 'root'080422 14:57:47 D:\usr\local\mysql4\bin\mysqld-nt.exe: Forcing close of thread 19 user: 'root'080422 14:57:47 InnoDB: Starting shutdown...
080422 14:57:49 InnoDB: Shutdown completed
080422 14:57:49 D:\usr\local\mysql4\bin\mysqld-nt.exe: Shutdown Complete
情况是这样的:
我在配置了可以实现数据库复制的Master/Slave机制后,经常出现1062错误,间隔时间不定。提示:Error 'Duplicate entry 'xxx' for key 1' on query. 而且mysql并没有停止,而是继续复制这一行数据,导致数据库中出现大量重复数据,我只好手动删除这些数据,然后就能正常运行一段时间,很是麻烦。正常情况下,slave上的sql语句如果出错,它会停止的吧。可是很奇怪的是slave上的sql线程没有停止,不然也不会大量继续插入重复的数据了。
详细信息如下:
主服务器上配置信息如下:
mysqld]
basedir=F:/usr/local/mysql4
datadir=F:/usr/local/mysql4/data
port=3306
skip-locking
key_buffer=384M
max_allowed_packet=1M
table_cache=512
sort_buffer_size=2M
read_buffer_size = 2M
net_buffer_length=2K
query_cache_size = 32M
thread_stack=64K
default-character-set=gbk
tmp_table_size=40G
tmpdir=F:/usr/mysqltempserver-id=1
log-bin
binlog-do-db=gupiao
binlog-do-db=discuz
从服务器上配置信息如下:
basedir=D:/usr/local/mysql4
datadir=D:/usr/local/mysql4/data
port=3306
skip-locking
key_buffer=16K
max_allowed_packet=1M
max_connections=1000
table_cache=4
sort_buffer_size=64K
net_buffer_length=2K
thread_stack=64K
default-character-set=gbk server-id=2
master-host=........
master-user=........
master-password=............
master-port=3306
replicate-do-db=discuz
replicate-do-db=gupiao
replicate-wild-ignore-table=gupiao.jos_content_%
query_cache_size = 128M我从错误日志中截取了一段相关信息:
080422 8:50:25 InnoDB: Started
080422 8:50:25 Slave SQL thread initialized, starting replication in log 'tjtele-s1mg7462-bin.192' at position 315626155, relay log '.\tjtele-zw6fce3k-relay-bin.345' position: 238799426
D:\usr\local\mysql4\bin\mysqld-nt.exe: ready for connections.
Version: '4.0.22-nt' socket: '' port: 3306 Source distribution
080422 8:50:25 Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'tjtele-s1mg7462-bin.193' at position 150463891
ERROR: 1062 Duplicate entry '257959' for key 1
080422 14:33:31 Slave: Error 'Duplicate entry '257959' for key 1' on query. Default database: 'discuz'. Query: 'INSERT INTO `cdb_posts` ( `fid` , `tid` , `first` , `author`, `authorid` , `subject` , `dateline` , `message` , `useip`) VALUES ('600030','257932','1','songyouyou','10','降低印花税的确存在智力障碍','1208844028',' 降低印花税的确存在智力障碍 \r\n降低印花税的确存在智力障碍 \r\n\r\n\r\n','202.0.235.244')', Error_code: 1062
080422 14:33:31 Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'tjtele-s1mg7462-bin.193' position 528993868
080422 14:57:46 D:\usr\local\mysql4\bin\mysqld-nt.exe: Normal shutdown080422 14:57:46 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013)
080422 14:57:46 Slave I/O thread killed while reading event
080422 14:57:46 Slave I/O thread exiting, read up to log 'tjtele-s1mg7462-bin.193', position 572132543
080422 14:57:47 D:\usr\local\mysql4\bin\mysqld-nt.exe: Forcing close of thread 3210 user: 'root'080422 14:57:47 D:\usr\local\mysql4\bin\mysqld-nt.exe: Forcing close of thread 19 user: 'root'080422 14:57:47 InnoDB: Starting shutdown...
080422 14:57:49 InnoDB: Shutdown completed
080422 14:57:49 D:\usr\local\mysql4\bin\mysqld-nt.exe: Shutdown Complete
你一定要确保你的SLAVE 上不要进行写操作。