Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
    
Do you have other databases on the master besides cmdb and nagios ? –  RolandoMySQLDBA Jun 14 '12 at 15:20
    
Yes there are few other databases in the master table. –  pprakash Jun 15 '12 at 6:38
add comment

1 Answer 1

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.

share|improve this answer
    
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?? –  pprakash Jun 15 '12 at 6:39
add comment

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.