Home / server / Questions / 326405
In Process
garconcn
Asked: 2011-11-01 09:56:32 +0800 CST

Setup a mysql replication, but new data are not updated

  • 772

I have setup a MySQL replication for an existing database server. The setup seems fine, but the database never update on slave server after I start the replication. Both servers are using CentOS 5.7 64-bit. They have enough disk space.

From MySQL Master server

mysql> show variables like 'server_id';
| server_id | 1 |

mysql> show processlist;
| 391673 | repl| mysqlslave.mydomain.com:47453 | NULL | Binlog Dump | 1453 | Has sent all binlog to slave; waiting for binlog to be updated | NULL

mysql> show slave hosts;
Empty set (0.00 sec)

mysql> show master status;
| mysql-bin.000008 |98 | db1, db2 |

From MySQL Slave server

mysql> show variables like 'server_id';
server_id | 2

mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.2
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 98
Relay_Log_File: slave-relay-bin.000004
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: db1; db2

MySQL log

In slave server, the message log shows this

111031 10:53:16 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000008', position 98
111031 10:53:16 [Note] Error reading relay log event: slave SQL thread was killed
111031 10:53:16 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000008' at position 98, relay log './slave-relay-bin.000001' position: 4
111031 10:53:16 [Note] Slave I/O thread: connected to master '[email protected]:3306', replication started in log 'mysql-bin.000008' at position 98

Update

If I create a new database on master server, it will be replicated to the slave server, but the existing databases are not updated.

Last Update

Instead of dump all the databases and setup replication in one time, I do it one by one and it is working fine now.

mysql replication

1 Answers

  1. I think I see the problem

    You have the following in your question:

    Replicate_Do_DB: db1; db2

    That is not supposed to be a semicolon-separated list. It is supposed to be a comma-separated list.

    My guess is your have this in your slave's /etc/my.cnf

    replicate-do-db=db1; db2

    The correct way to set this up is

    replicate-do-db=db1
replicate-do-db=db2

    Please adjust this in the slave's /etc/my.cnf and restart mysql on the slave

    Give it a Try !!!

    UPDATE 2011-10-31 15:20 EDT

    You may want to clear out the relay log by doing this

    STOP SLAVE;
CHANGE MASTER TO master_log_file='mysql-bin.000008',master_log_pos-98;
START SLAVE;

    then run SHOW SLAVE STATUS\G and make sure replication is running by making sure you see this

    Slave_IO_Running: Yes
Slave_SQL_Running: Yes

    just as you posted in your question

    • 3