MySQL says replication is fine but data is not copied

The setup

The setup is very simple and straightforward. It's a pair of Debian servers with a Gigabit link in between. MySQL is stable Debian Lenny version and OS is Debian Lenny.

Configuration

The dump has been inserted on both nodes and replication has been activated.

The "SHOW MASTER STATUS" commands on the master gives the following information :

+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+
| File             | Position | Binlog_Do_DB                                                                                                                   | Binlog_Ignore_DB |
+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+
| mysql-bin.000025 |       98 | smc, smc_allais, smc_montgenevre, smc_preprod, smc_resto, smc_resto, smc_richermoz, smc_sct, smc_skd, smc_skishop, smc_skiteam |                  | 
+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------+------------------+

The "SHOW SLAVE STATUS" command on the slave gives the following information :

*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 188.165.xxx.xxx
                Master_User: bdd1
                Master_Port: 3306
              Connect_Retry: 10
            Master_Log_File: mysql-bin.000025
        Read_Master_Log_Pos: 98
             Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 235
      Relay_Master_Log_File: mysql-bin.000025
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: smc, smc_allais, smc_montgenevre, smc_preprod, smc_resto, smc_resto, smc_richermoz, smc_sct, smc_skd, smc_skishop, smc_skiteam
        Replicate_Ignore_DB: 
         Replicate_Do_Table: 
     Replicate_Ignore_Table: 
    Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
                 Last_Errno: 0
                 Last_Error: 
               Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 235
            Until_Condition: None
             Until_Log_File: 
              Until_Log_Pos: 0
         Master_SSL_Allowed: Yes
         Master_SSL_CA_File: /etc/mysql/ca-cert.pem
         Master_SSL_CA_Path: 
            Master_SSL_Cert: /etc/mysql/client-cert.pem
          Master_SSL_Cipher: 
             Master_SSL_Key: /etc/mysql/client-key.pem
      Seconds_Behind_Master: 0

The "SHOW PROCESSLIST" command on the slave gives the following information :

+-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db          | Command | Time | State                                                                 | Info             |
+-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+
| 366 | root        | localhost | smc_preprod | Query   |    0 | NULL                                                                  | show processlist | 
| 369 | system user |           | NULL        | Connect | 1097 | Waiting for master to send event                                      | NULL             | 
| 370 | system user |           | NULL        | Connect | 1096 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             | 
+-----+-------------+-----------+-------------+---------+------+-----------------------------------------------------------------------+------------------+

The "SHOW PROCESSLIST" on the master gives the following information :

+-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------+
| Id  | User | Host                 | db          | Command     | Time | State                                                          | Info             |
+-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------+
| 410 | user | 91.121.xx.xx:45479   | smc_allais  | Sleep       |   42 |                                                                | NULL             | 
| 415 | user | 91.121.xx.xx:45481   | smc_preprod | Sleep       | 1463 |                                                                | NULL             | 
| 420 | user | 91.121.xx.xx:46106  | smc_preprod | Sleep       | 1528 |                                                                | NULL             | 
| 432 | user | 91.121.xx.xx:46155 | NULL        | Binlog Dump | 1114 | Has sent all binlog to slave; waiting for binlog to be updated | NULL             | 
| 434 | user | 91.121.xx.xx:60088  | smc_allais  | Sleep       |   79 |                                                                | NULL             | 
| 453 | user | localhost            | smc_preprod | Query       |    0 | NULL                                                           | show processlist | 
+-----+------+----------------------+-------------+-------------+------+----------------------------------------------------------------+------------------

EDIT - Logs

In the slave logs, I get the following information :

Oct 27 11:48:37 bdd2 mysqld[28477]: 101027 11:48:37 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000027' at position 98, relay log './mysqld-relay-bin.000001' position: 4
Oct 27 11:48:37 bdd2 mysqld[28477]: 101027 11:48:37 [Note] Slave I/O thread: connected to master '[email protected]:3306',  replication started in log 'mysql-bin.000027' at position 98

EDIT - Disk space

Disk space on both servers is sufficient. MySQL is in /data for bdd1 and in /home for bdd2.

bdd1:~# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md1               20G  3.4G   16G  19% /
tmpfs                 5.9G     0  5.9G   0% /lib/init/rw
udev                   10M  2.8M  7.3M  28% /dev
tmpfs                 5.9G     0  5.9G   0% /dev/shm
/dev/md2               51G  2.7G   48G   6% /data

bdd2:~# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/md1               10G  2.6G  6.9G  27% /
tmpfs                 5.9G     0  5.9G   0% /lib/init/rw
udev                   10M  2.8M  7.3M  28% /dev
tmpfs                 5.9G     0  5.9G   0% /dev/shm
/dev/md2               64G  8.0G   53G  14% /home

Considering the above information, everything seems to be running perfectly fine yet in reality no data gets copied over. I modify the master database and no changes show up on the slave database.

What am I doing wrong ?


Solution 1:

If you never 'USE' a database, and always fully qualify your database in your queries, your queries won't be written to the binary log when using 'Binlog_Do_DB'.

From http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

An example of what does not work as you might expect: If the server is started with --binlog-do-db=sales and you issue the following statements, the UPDATE statement is not logged:

USE prices; UPDATE sales.january SET amount=amount+1000;

For those reasons, I never use the do_db/ignore_db variables.