Mysql replication from existing slave to new slave

Question

I'm hoping you experts can help advise me, I've got this topology:

Live DB(Master) ----> Backup Server (Master and slave) ----> Test DB

Backup server is a slave of Live DB, and Test DB is a slave of Backup server

The replication from Live to backup server works as expected, the replication from backup to test is where I'm a little confused, if I create a test DB on backup, I can see it replicated to Test DB, but if I do the same on Live DB, it doesn't filter down to Test DB (but does to Backup).

Another thing I notice is that the binlog sizes on Backup are very small, only 150 Bytes, whereas they are close to 100M on Live, (binlog sizes set the same on live and backup)

Maybe this is by design? Maybe it's not possible to chain slaves together?

I goal of Test DB was (once working) to replicated from our Hosted Datacenter to our internal office, then use the internal slave to update our Dev / QA / Training DBs here (reducing bandwidth usage).

Here are what the related settings are on the backup server: (/etc/mysql/my.cnf)

log_bin = /srv/mysql-binlogs/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M
binlog_cache_size = 16M
binlog-format = MIXED

Specs:

• mysql 5.5
• Debian Squeeze

Thanks, Your advice would be much appreciated

Answer 1

I missed one very important option:

log_slave_updates

Now the binlogs are growing!

ref: MySQL Slave Relay Logging but not logging Binary Log

http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_log_slave_updates

Answer 2

so you are creating database on backup server and it's replicating on TestDB,but if you are creating on live server it's not replicating on backup server.

In the above case check for replicate-do-db or replicate-ignore-db variable on backup server under my.cnf file.

• DBA Noobie: Anything that happens on live gets correctly replicated to backup, but doesn't filter down to testdb, but anything I add to backup (only as a test), correctly replicates down to test. those values (replicate-do-db and replicate-ignore-db are commented out on all servers) I want everything replicated.
• DBA Noobie: if it helps, the only difference on the servers are the locations of the binlogs, on live it's in the default /var/lib/mysql directory, on backup it's on /srv/mysql-binlogs. (Test binlogs are in /var/lib/mysql)