Mysql – Binary log backups in MySQL

MySQLmysqlbinlogreplication

I'm running a master-master replication with read-only second master. Now since my binary logs are not getting replicated in my passive master.

Should I need to enable --log-slave-updates? If I enable this, will I be able to take binary log backups from my passive master? If I enable it in passive master, I should not enable the same on active master right?

If I enable –log-slave-updates in my active master also, consider the case of a disaster where in my primary master has a issue, then I will make my passive master the active one and point my application to my new master. Now my new master will be generating binary logs which will be waiting to replicate to my actual master (which is down now). SO what happens when my actual master comes up? Binary of my secondary master gets copied to my primary without any issues?

While enabling –log-slave-updates, should I have attn on any other parameters as well.?

Thanks!

Best Answer

log-slave-updates=true

should be true on both masters. Because if both masters are accepting updates then the other passive master has to be able to update itself.
You can take backups of the bin logs whenever you want.
As to other options, I use the MIXED option for the binglog_format to allow for ROW based or STATEMENT based updates

binlog_format = MIXED

This is because STATEMENT based replication doesn't cover all updates. But STATEMENT based replication is faster. With MIXED MySQL can switch for you when appropriate.

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

Since the binary log on the Master is corrupt, there is nothing else you can do. My condolences. Just skip to the next binlog:

STOP SLAVE;
CHANGE MASTER TO master_log_file='ibm-pr-slglcd-01.000076',master_log_pos=107;
START SLAVE;

Of course, 107 is the start position for MySQL 5.5 binlogs. If the Master is 5.1 (or 5.0.95), use 106. If the Master is prior to 5.1, use 98.

If Master BinLog Corruption is frequent, you may need to consider using a smaller size for the binary logs on the Master (perhaps 128M instead of the default 1G):

[mysqld]
max_binlog_size=128M

This will not stop corruption, but lowering the size it will minimize data loss from 1G to 128M.

MySQL Slave Relay Logging but not logging Binary Log

You forgot one thing

binlog-do-db=our_database  
binlog-ignore-db=mysql  
binlog-ignore-db=test  
replicate-do-db=our_database  
log-slave-updates

The option log-slave-updates takes SQL that was read from relay logs and records them in the Slave Binary Logs when the SQL is completed.

Please be very careful. You should never use binlog-do-db and binlog-ignore-db simultaneously. I wrote about this back on Jun 14, 2012 ( Configured MySQL replication but its not working ) which points to an old Percona article on binlog-do-db being dangerous.

SUGGESTION

Of the things you are using, you only need these lines

replicate-do-db=our_database  
log-slave-updates

You really don't need binlog-do-db=our_database once you have log-slave-updates working properly.

Best Answer

Related Solutions

Mysql – Error reading MySQL 5.5 binary log using thesqlbinlog

MySQL Slave Relay Logging but not logging Binary Log

SUGGESTION

Related Question