1

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 tom@abc.com -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 ?

2

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.

  • Hi Rolando. Thanks for the detailed response. I will try Option 1. Option 2 did not work for me. Hence I went for replicate_wild_do_table. There is only one database on the master i.e. zo_dev_matrix. On Slave, there are several databases but I'm not sure how do you set the current database. Initially, I used replicate-rewrite-db which didnt work. Then just for testing, I created the zo_dev_matrix on slave too. It looks like not having the current database set could be the problem. But I do not know how do you do that as the database is accessed by an app. – Ravi Apr 14 '14 at 19:21
  • And it works just like that. Changed * to %. Restarted the server. Stopped slave, Changed the master_log_pos and master_log_file, started slave. It worked. Then I replaced replicate_wild with replicate_do and it also worked. This was not working before with the value "zo_dev_matrix.opportunity". But now everything seems to be working perfectly. Previously an error was thrown that a certain second table does not exist. I could see the insert query for the table in the relay log. That table still does not exist. I wonder why it is not throwing an error now. – Ravi Apr 15 '14 at 9:02
1

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. :-)

  • There are no other databases on the master Bill. There is only one database. – Ravi Apr 14 '14 at 19:25
  • @Ravi, there's always a mysql database, and usually a test database by default. There are also the system databases information_schema and performance_schema, but these are never replicated anyway. – Bill Karwin Apr 14 '14 at 19:56
  • sorry I meant there is only one database that is used by the application. – Ravi Apr 15 '14 at 8:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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