Greetings,
We're having some problems with our one way replication "stalling".
There are no errors in the errors logs, and the status reports seem to
indicate things are still running, however, changes to the master are
not being received by our slaves.
We're running a farm of 4 slave mysql machines (MySQL version 3.23.41)
that talk to a single master (also version 3.23.41) machine. We're
running two databases on the machines (I'll call them db1 and db2), but
I only wish to replicate one of them (db2).
I've sync'd the databases, and turned on replication, and everything
works great until we load data into _db1_ via the 'load data infile'
command.
At that point, any further changes to db2 are not replicated to the
slave machines.
Running 'show processlist' at that point, on any of the slaves, gives
the following:
| 1294 | system user | none | NULL | Connect | 5233 | Reading master
update | NULL
Running 'show slave status' gives:
| master | user | 3306 | 60 | p23-bin.004 | 380 | Yes | db2 | db1
| 0 | | 0 |
Running 'show processlist' on the master reveals:
| 4 | repl | 10.1.1.108 | NULL | Binlog Dump | 2666 | Reading from net
| NULL
Running 'show master status' on the master gives:
| p23-bin.004 | 11283 | db2 | db1 |
If I run 'mysqlbinlog -j 380 p23-bin.004 |head' on master I get:
# at 380
#010830 15:39:21 server id 1 Query thread_id=32 exec_time=0
error_code=0
use db1;
SET TIMESTAMP=999203961;replace into time_last_updated (time,table_name)
values ('2001-08-30 15:24:16','person_function');
If I issue 'slave stop' and 'slave start' on a slave, it syncs up to the
master, and will continue replicating until db1 is updated. Restarting
the master will also allow all of the slaves to sync up.
Here's the mysqld section of my.cnf from the master:
[mysqld]
#port = 3306
#socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=16M
set-variable = table_cache=512
set-variable = sort_buffer=6M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache_size=4
set-variable = thread_concurrency=4 # Try number of CPU's*2
set-variable = tmp_table_size=4M
set-variable = net_read_timeout=300
set-variable = net_write_timeout=300
# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=64M
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Replication
log-bin
server-id=1
binlog-do-db=db2
binlog-ignore-db=db1
And from the slaves:
[mysqld]
#port = 3306
#socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=16M
set-variable = table_cache=512
set-variable = sort_buffer=5M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=128M
set-variable = thread_cache_size=4
set-variable = thread_concurrency=4 # Try number of CPU's*2
set-variable = tmp_table_size=3M
set-variable = net_read_timeout=300
set-variable = net_write_timeout=300
set-variable = max_connections=500
log-update
# Replication
master-host=master
master-user=user
master-password=password
master-port=3306
server-id=3
replicate-do-db=db2
replicate-ignore-db=db1
log-slave-updates
#log-bin
If anyone could shed some light on this is happening, and more
importantly, how we can keep the replication going, or if you need any
other information, please let me know.
Thanks,
Jeff Adams
Webmaster
Hoover's Online
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
