mysql复制是不是有bug？

songpure520

我有两台数据库server1和server2
server1有test数据库
server2有sc数据库
我做了server1为test数据库的主服务器，server2为test数据库的从服务器
server2为sc数据库的主服务器，server1为sc数据库的从服务器

隔一两个小时看，发现两台的数据同步很慢！！从服务器老是落后主服务器一百多条数据，我的数据库的数据量不是很大，所以每分钟不可能有一百多条数据更新，两台服务器负载也很低。都在一个局域网！网络很稳定！

两台都用show slave status看都没出错，Seconds_Behind_Master也为 0，不知道哪位能帮我解释下这是什么原因啊!

songpure520

server1的

[client]
port            = 3306
socket          = /tmp/mysql.sock


[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
#sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 80
query_cache_size = 128M
thread_concurrency = 8
back_log = 500
#interactive_timeout = 7200
#key_buffer_size = 838860800
max_connections = 3000
record_buffer = 16773120
#sort_buffer = 16773120
wait_timeout = 100
log-slow-queries = /var/log/slow-que.log
long_query_time = 0.2
log-queries-not-using-indexes
expire_logs_days = 8
#skip-federated

log-bin=mysql-bin

server-id       = 2
binlog-do-db=test

binlog-ignore-db=mysql

#sync_binlog=1
#auto_increment-increment=2
#auto_increment_offset=1
master_host=192.168.2.5
master_user=bakup
master_password=000000
master-port=3306
master_connect-retry=10
replicate-do-db=sc
replicate-ignore-db=mysql


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout = 7200

[mysqld_safe]
log-error=/var/log/mysqld.log

server2的

[client]
port            = 3306
socket          = /tmp/mysql.sock


[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
#sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 128M
thread_concurrency = 8
back_log = 500
max_connections = 3000
record_buffer = 16773120
#sort_buffer = 16773120
wait_timeout = 100
log-slow-queries = /var/log/slow-que.log
long_query_time = 0.2
log-queries-not-using-indexes
#log=/var/log/select.log
#log_output='FILE'



#skip-federated

log-bin=mysql-bin

server-id       = 1
master_host=192.168.2.6
master_user=bakup
master_password=000000
master_port=3306
master_connect_retry=10
replicate-do-db=test
replicate-ignore-db=mysql
binlog-do-db=sc
binlog-ignore-db=mysql
#auto_increment_increment=2
#auto_increment_offset=2
#sync_binlog=1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout = 7200

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysql/mysqld.pid

枫影谁用了

这个厉害啊。。。这样同步！

如果这样行的话，不如选择M-M。

songpure520

因为我server1专门读写test数据库  server2专门读写sc数据库，你指的M-M是什么意思？

枫影谁用了

原帖由 songpure520 于 2009-2-19 09:07 发表
因为我server1专门读写test数据库  server2专门读写sc数据库，你指的M-M是什么意思？

即是两台机器既是Master,也是Slave。

枫影谁用了

yueliangdao0608  blog里好像有一文章讲的是m-m的配置的。

songpure520

你仔细看我第一帖和第二贴，我的就是这样的啊！！但是不知怎么了！！同步相当慢！！master更新了，slave半天都没更新！

[ 本帖最后由 songpure520 于 2009-2-19 09:19 编辑 ]

blackbox

更新语句里有跨库语句吗？例如 insert into db_name.table_name ........ 这样的语句？

songpure520

你是说会不会在server1上更新sc的数据，不会的！