Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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 'repl@192.168.1.2: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.

share|improve this question
    
Please run this command on both slave and master, and post the output in your question: show databases –  RolandoMySQLDBA Oct 31 '11 at 18:12
    
Sorry, I don't want to post customer's db names here. It contains their userID and domainname. The master server has 44 rows, the slave server has 41 rows. When I dump the databases from master server, i skipped 3 corrupted databases. I thought it should be ok to replicate the other databases. Am I wrong? –  garconcn Oct 31 '11 at 18:22
    
I see another problem. Don't worry about showing DB names. I supplied an answer. –  RolandoMySQLDBA Oct 31 '11 at 18:27
    
What version of MySQL are you running on Master and Slave ? –  RolandoMySQLDBA Oct 31 '11 at 19:15
    
Both are 5.0.77 –  garconcn Oct 31 '11 at 19:26

1 Answer 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

share|improve this answer
    
Thanks. This is a problem. I've fixed it and the problem still exists. –  garconcn Oct 31 '11 at 19:02
    
One question: if i have multiple dbs waiting to be replicated, but in my.cnf, i only set one for testing, will that work? –  garconcn Oct 31 '11 at 19:04
    
Answer to this question is yes. Each time you change the replicate-do-db list, please restart mysql. –  RolandoMySQLDBA Oct 31 '11 at 19:18
    
Thanks. I've restarted the mysql on both servers. –  garconcn Oct 31 '11 at 19:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.