MySQL Slave not updated

Question

This might be a duplicate of questions on the same problem asked before. But all the suggestions to the previous questions do not seem to work in my case. I have set up Replication using SSH Tunnel. Any changes on Master are being logged in the mysqld-relay-bin file on the slave. But the SQL thread does not seem to be executing these queries as Slave is not being updated.

A replication user has been created with host as 127.0.0.1 and granted the replication slave privilege. For testing purpose, I've run the following command to establish the SSH tunnel.

ssh -f [email protected] -L 7306:127.0.0.1:3306 -N

I can successfully login into the remote server's db using

 mysql -u root -p -h 127.0.0.1 -P 4306

Below are the outputs of SHOW MASTER STATUS, SHOW SLAVE STATUS, SHOW PROCESSLIST respectively:

mysql> show master status
    -> ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000008 |      366 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: replication_user
                  Master_Port: 7306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 366
               Relay_Log_File: mysqld-relay-bin.000019
                Relay_Log_Pos: 382
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: zo_dev_matrix.*
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 366
              Relay_Log_Space: 539
              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)


mysql> show processlist;
+----+-------------+-----------------+---------------+---------+------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db            | Command | Time | State                                                                       | Info             |
+----+-------------+-----------------+---------------+---------+------+-----------------------------------------------------------------------------+------------------+
|  1 | system user |                 | NULL          | Connect |  421 | Waiting for master to send event                                            | NULL             |
|  2 | system user |                 | NULL          | Connect |   61 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
|  8 | root        | localhost:33078 | NULL          | Sleep   |    2 |                                                                             | NULL             |
| 82 | root        | localhost:33081 | NULL          | Sleep   |  352 |                                                                             | NULL             |
| 83 | root        | localhost:33082 | zo_dev_matrix | Sleep   |  348 |                                                                             | NULL             |
| 84 | root        | localhost:33083 | zo_dev_matrix | Sleep   |  346 |                                                                             | NULL             |
| 86 | root        | localhost       | NULL          | Query   |    0 | NULL                                                                        | show processlist |
+----+-------------+-----------------+---------------+---------+------+-----------------------------------------------------------------------------+------------------+
7 rows in set (0.00 sec)

What am I missing ?

Answer 1

The replication filter you have in place

Replicate_Wild_Do_Table: zo_dev_matrix.*

is actually a little misleading. In the MySQL Documentation, the legal characters for wildcards are %, _ and \_. (If you want to interpret a literal underscore). The asterisk character is not listed. The above filter is actually looking for a table called zo_dev_matrix.*. Therefore, you have two options

OPTION #1

Correct the filter in my.cnf to have this

[mysqld]
replicate_wild_do_table = zo_dev_matrix.%

OPTION #2

Change the filter to the database zo_dev_matrix

replicate_do_db = zo_dev_matrix

only if all your queries do no preface the table with the db and you explicit set the current database to zo_dev_matrix.

CAVEAT

With MySQL 5.6 and back, you must restart mysql after changing the filter in my.cnf. Starting with MySQL 5.7, you can create a dynamic replication filter so a mysql restart is not necessary.

Answer 2

It appears that you have a successful connection from the slave to the master, and the SQL thread is running. But I notice this:

  Replicate_Wild_Do_Table: zo_dev_matrix.*

This indicates you have configured a replication filter. It means that even though the relay logs contain all changes, only changes applying to tables in the zo_dev_matrix database will be executed on the slave. If your changes are in other databases, for example the test database, it won't show up on the slave.

---

Update:

If you refer to the documentation for replicate-wild-do-table, the wildcard it uses is %, not *.

Oh, never mind, I see Rolando already answered that. :-)