5

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 'bdd1@188.165.221.122: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 ?

Antoine Benkemoun
  • 7,314
  • 3
  • 42
  • 60
  • This has to be the most obvious question ever, but has bitten me once: is there enough free disk space on the slave server? Many moons ago I spent the whole evening pondering a similar problem, just to find later that df -h returned 100% for the MySQL data partition ... – Janne Pikkarainen Oct 27 '10 at 09:59
  • Very good question but sadly that is not the source of the problem... Edited the question with disk space information. – Antoine Benkemoun Oct 27 '10 at 10:08
  • One more obvious question: are the MySQL file/directory permissions OK in the slave? Yes, MySQL _should_ complain loudly if something's wrong, but MySQL sure has fulfilled itself with full of small gotchas. Other than that, I'm running out of ideas. – Janne Pikkarainen Oct 27 '10 at 10:10
  • Oh! One more :-) What storage engine you use for your tables? – Janne Pikkarainen Oct 27 '10 at 10:12
  • Using innoDB. I just reset the permissions and tried to setup replication again. It didn't change anything. MySQL still continues to pretend everything is OK... – Antoine Benkemoun Oct 27 '10 at 12:03
  • Ended up formatting both servers and trying again. Worked right away... On one hand, it's good because it works but on the other hand, I have no idea why there was a problem is the first place. – Antoine Benkemoun Oct 27 '10 at 13:18
  • Ran into this today, replication appeared to be OK but upon running a "CHECK TABLE" against the slave table in question I saw warnings and errors relating to corruption. Will run mysqlcheck and see if it can be repaired. – Cory J Oct 29 '10 at 00:58
  • Great advice, I will try this next time but since it's been formatted, can't try it now :-) If you write an answer, I'll check it. – Antoine Benkemoun Oct 29 '10 at 08:33

2 Answers2

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.

Riedsio
  • 283
  • 4
  • 7
0

Do the servers have different server ids? If they are mistakenly set to be the same then replication will appear to work fine, but it will ignore all statements (it assumes they are from itself and doesn't need to run them).

mysql> show variables like 'server_id';

-Dan

drogart
  • 436
  • 2
  • 2