I'm trying to figure out why even though SHOW SLAVE STATUS isn't reporting any errors, nothing is replicating (see below):

both nodes are running:

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.1.73-log |
+------------+
1 row in set (0.00 sec)

mysql> 

on

# cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 6.5 (Santiago)
# 

master:

mysql> SHOW MASTER STATUS\G;
*************************** 1. row ***************************
            File: mysql-bin.000107
        Position: 228679653
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

slave:

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: MASTER_HOST
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000107
          Read_Master_Log_Pos: 228681259
               Relay_Log_File: mysql:PR_DB-relay-bin.000150
                Relay_Log_Pos: 228398599
        Relay_Master_Log_File: mysql-bin.000107
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: DB1,DB2,DB3,DB4
          Replicate_Ignore_DB: information_schema,mysql
           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: 228681259
              Relay_Log_Space: 228681667
              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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>

master:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    97786 |
+----------+
1 row in set (0.03 sec)

mysql> 

slave:

mysql> SELECT COUNT(*) FROM DB3.users;
+----------+
| COUNT(*) |
+----------+
|    95678 |
+----------+
1 row in set (0.01 sec)

mysql> 

at the initial time of setting up replication, SELECT COUNT(*) returned same values.

  • Replicate-*-DB options, and others like them that prevent replicating the entire server are extremely problematic unless you absolutely understand how they work and their implications, especially when the master's binlog_format is not set to ROW. Have you read and thoroughly understood this and this? – Michael - sqlbot Aug 22 '14 at 0:00
up vote 0 down vote accepted

due to:

MySQL :: MySQL 5.1 Reference Manual :: 16.1.3.3 Replication Slave Options and Variables

Warning

To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.

my.cnf's incorrect syntax:

--replicate-do-db=DB1,DB2,DB3,DB4

was replaced with correct syntax:

--replicate-do-db=DB1
--replicate-do-db=DB2
--replicate-do-db=DB3
--replicate-do-db=DB4

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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