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.