2

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.

5
  • Please run this command on both slave and master, and post the output in your question: show databases Oct 31, 2011 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, 2011 at 18:22
  • I see another problem. Don't worry about showing DB names. I supplied an answer. Oct 31, 2011 at 18:27
  • What version of MySQL are you running on Master and Slave ? Oct 31, 2011 at 19:15
  • Both are 5.0.77
    – garconcn
    Oct 31, 2011 at 19:26

1 Answer 1

3

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

4
  • Thanks. This is a problem. I've fixed it and the problem still exists.
    – garconcn
    Oct 31, 2011 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, 2011 at 19:04
  • Answer to this question is yes. Each time you change the replicate-do-db list, please restart mysql. Oct 31, 2011 at 19:18
  • 10 years later Rolando still saving my @$$! <3
    – w00t
    Oct 5, 2021 at 20:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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