4

I have configured MySQL master and slave replication setup on 5.5.x. But for some reason data is not getting replicated properly. What might be the reason?

At Master:

/etc/my.cnf

[mysqld] log-bin=/var/lib/mysql/mysql-bin.log server-id=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 binlog-do-db=cmdb binlog-do-db=nagios

mysql> show master status;

+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000005 | 168019 | cmdb,nagios | | +------------------+----------+--------------+------------------+

At Slave:

/etc/my.cnf

[mysqld] server-id=2 log-bin=/var/lib/mysql/mysql-bin.log innodb_flush_log_at_trx_commit=1 sync_binlog=1 report-host=10.10.0.22 replicate-do-db=cmdb replicate-do-db=nagios

mysql> show slave status\G;

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.0.21 Master_User: replicator Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 198123 Relay_Log_File: CentOS62-relay-bin.000012 Relay_Log_Pos: 198269 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cmdb,nagios Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 198123 Relay_Log_Space: 198574 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
Improve this question
2
  • Do you have other databases on the master besides cmdb and nagios ? Commented Jun 14, 2012 at 15:20
  • Yes there are few other databases in the master table. Commented Jun 15, 2012 at 6:38

1 Answer 1

Reset to default
2

What concerns me here is the use of both replicate-do-db on the Slave and binlog-do-db on the Master at the same time.

There is no need to use both. I would remove binlog-do-db from the Master. I would also look over any queries with fully qualified table names. Why ?

If you replicate any SQL use fully qualified table names (dbname.tblname) and you use replicate-do-db, MySQL Replication gets confused if you execute USE dbname.

Percona explained this in conjunction with binlog-do-db and replicate-do-db back in May 2009.

Percona also suggested reading the replication filtering rules thoroughly.

Improve this answer
1
  • Thanks for your suggestion. I tried removing the binlog-do-db from the master & replication is happening. But I see a strange behavior. In the same table some of the columns are getting replicated but few of the columns are not getting replicated. What might be the reason?? Commented Jun 15, 2012 at 6:39

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.