MySQL Replication – How to Fix Slave Not Updated Issue

MySQLmysql-5.5replication

This has been asked many times over but we really cannot get a simple Master-Slave Mysql (5.5.25) replication to work for our two dbs (FatTree and Torus). And no trace of error in the logs.

The procedure we followed was:

  • configure my.cnf on the master and the slave with different server-ids. Nothing particular on the master side, but replicate_wild_do_table on FatTree.* and Torus.* on the slave.
  • flush tables with read lock on the master.
  • mysqldump the two dbs and import them on the slave.
  • run change master to... on the slave.
  • unlock tables on the master.

And nothing is happening on the slave upon updating the master dbs, i.e changes are not propagated to the slave. Master and slave seem to be communicating though. We ran mysqlcheck on both dbs and all tables are OK.

Is there anything we might have forgotten?

Many thanks for your help/insight, that'd be most appreciated!

======================================================================

On the master (server-id=1):

#> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   305049 |              |                  |
+------------------+----------+--------------+------------------+

#> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+

#> show processlist;
+----+-----------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User      | Host                | db   | Command     | Time | State                                                                 | Info             |
+----+-----------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 19 | replica   | 155.193.30.18:49220 | NULL | Binlog Dump | 3625 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 39 | rphillips | localhost           | NULL | Query       |    0 | NULL                                                                  | show processlist |
+----+-----------+---------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+

On the slave (server-id=2):

#> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 155.193.30.117
                  Master_User: replica
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 305049
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 305195
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: FatTree.*,Torus.*
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 305049
              Relay_Log_Space: 305352
              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

#> show processlist;
+-----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time | State                                                                       | Info             |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 309 | system user |           | NULL | Connect | 3821 | Waiting for master to send event                                            | NULL             |
| 310 | system user |           | NULL | Connect |  227 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 312 | root        | localhost | NULL | Query   |    0 | NULL                                                                        | show processlist |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

Best Answer

According to the MySQL Documentation on replicate-wild-do-table

Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the “%” and “_” wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates

Now, look carefully at the Replicate_Wild_Do_Table from your SHOW SLAVE STATUS\G

You have

Replicate_Wild_Do_Table: FatTree.*,Torus.*

According to the MySQL Documentation, it should read

Replicate_Wild_Do_Table: FatTree.%,Torus.%

Asterisks are not used for LIKE pattern matching

Go to your my.cnf and change it to

[mysqld]
replicate-wild-do-table=FatTree.%
replicate-wild-do-table=Torus.%

and restart MySQL.

Give it a Try !!!

UPDATE 2014-02-18 05:57 EST

Your last comment was

Thanks, that was a silly mistake. Now that I changed this, reset the slave and updated the master log position, I get errors like Last_Error: Error 'Table 'torus.logs' doesn't exist' on query. Default database: 'torus'. Query: 'delete from logs where N=11'. Any clue?

If you are running MySQL in Linux, the database names are case sensitive.

Go back to my.cnf, add the following

[mysqld]
replicate-wild-do-table=torus.%

and restart MySQL.

Give it a Try !!!

Related Question