Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 |
+-----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
share|improve this question

2 Answers 2

up vote 1 down vote accepted

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

share|improve this answer
    
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? –  okiharaherbst Feb 18 at 8:06
    
All good. I started afresh and now the replication works as expected. –  okiharaherbst Feb 18 at 16:39

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
share|improve this answer
    
thanks. I erroneously used * instead of % in the pattern to match table names. –  okiharaherbst Feb 18 at 18:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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