2

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 |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
2

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 !!!

  • 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? – VH-NZZ Feb 18 '14 at 8:06
  • All good. I started afresh and now the replication works as expected. – VH-NZZ Feb 18 '14 at 16:39
  • Normally you should avoid filtering until it is mandate for you because in MySQL filtering works on how you execute queries . Use database and nomenclature. – Ankit Kapoor Jun 24 '17 at 19:52
  • I had made a similarly silly mistake; I used replicate-do-db = first,second, instead of two separate lines, one with replicate-do-db = first and one with replicate-do-db = second. The output from SHOW SLAVE appears the same in both cases, so it was only after re-examining the docs in detail that I caught this. The resultant behavior was that nothing replicated, yet oddly, I saw entries for a completely different database in the relay log (though the queries therein were never executed, from what I could tell). – Ben Johnson May 25 '18 at 12:48
0

I'd need more information before I could attempt to give a specific answer.

  • Before you did change master to did you ensure the mysqldump worked as expected and actually populated tables on the slave (select count(*) on the slave; checksum tables between master and slave copies; mysqlchk will just ensure a table is not corrupt, not give you much to compare contents)
  • When you show slave status do you see Exec_Master_Log_Pos advancing or is it remaining stationary
  • Do the relay logs on the slave show queries from the databases you would expect to be coming through (read the binlog using the mysqlbinlog tool)
  • If they are coming through, is the application writting them doing so by setting the default db context with use FatTree, for example? The alternative might be no default context and writing queries of the format insert into FatTree.tbl
  • thanks. I erroneously used * instead of % in the pattern to match table names. – VH-NZZ Feb 18 '14 at 18:29

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.