PDA

View Full Version : Incremental Backups on Replication Slave



UbersmithDan
February 6th, 2007, 07:02 AM
Hi,

We're attempting to run backups from a replication slave, with a daily full backup and an incremental every hour.

We've set up both backup runs and altered the cron entries so the incremental runs every hour (aside from when the full backup runs).

The scheduling works, but it seems that the incremental backups are just backing up the (empty) binary logs of the slave, and not the relay log of events from the master.

Is there a way to make this work, or do we have to perform the incremental backups on the master?

Thanks,

Dan

shailen
February 6th, 2007, 08:40 AM
You should be able to run both incremental and full backups on your replication slave.

Can you run the following command.

mysql-zrm-reporter --show restore-info

This will show you the restore information for all your previous backups.

Please choose the restore INFORMATINO for the LAST incremental backup that was run on your slave.

Next, can you run the following command.

mysql-zrm --action parse-binlogs --backup-set <backup set name> --source-directory <directory path>

What this command will do is BROWSE the binary logs from your the incremental backup you specify (based on the source-directory) and the backup-set you specify.

This will allow you to confirm that the incremental backups are INDEED being performed.

Often times, ZRM will report 0mb backed up since binary logs can be so small.

If you have any additional questions please feel free to post them here. Along, with the output of the commands above.

Thanks!
-Shailen :)

UbersmithDan
February 6th, 2007, 09:05 AM
Shailen,

Thanks for your response. I was able to verify that the backups are being created successfully, however it seems that the problem is more involved than that.

On the slave, there are 2 binary logs, the relay log from the master server and the binary log for the slave. The relay log contains all the queries replicated from the master, and while the binary log for the slave contains only the directives to rotate to the next binary log, generated each time we perform a backup and flush the binary log on the slave.


# mysql-zrm --action parse-binlogs --backup-set ubersmith --source-directory /var/lib/mysql-zrm/ubersmith/20070206120002/
INFO: ZRM for MySQL Community Edition - version 1.1.3
------------------------------------------------------------
Log filename | Log Position | Timestamp | Event Type | Event
------------------------------------------------------------
/var/lib/mysql-zrm/ubersmith/20070206120002/mysql-bin-rep.000112 | 4 | 07-02-06 12:00:02 | Rotate to mysql-bin-rep.000113 pos: 4 |
------------------------------------------------------------

I have looked through the MySQL documentation to see if there is a directive to cause MySQL to write the statements executed from the relay log out to the binary logs, without success.

If anyone can point us in the right direction it would be appreciated.

Dan

shailen
February 6th, 2007, 11:03 AM
Dan,

Thanks for your response.

I understand whats going on.

Let me look into this some more and get back to you :)

In the meantime can you please post the contents of your mysql-zrm.conf file :)

Thanks!

-Shailen

UbersmithDan
February 6th, 2007, 11:22 AM
Shailen,

I think I may have just figured out the solution, there is in fact a mysql option 'log-slave-updates' to have the slave write the replicated statements out to its binary log. It's fairly well hidden in the MySQL documentation, but there are a few articles on the net that do reference it, mostly related to circular replication.

http://dev.mysql.com/doc/refman/4.1/en/replication-options.html

After adding that directive to the my.cnf on the slave server everything appears to be working properly, and the incremental backups have the expected data.

I guess this should be added to the zrm documentation somewhere, thanks for your help.

Dan

shailen
February 6th, 2007, 01:52 PM
Dan,

Thanks for your response.

I'll make sure that this gets added into our documentation for backing up MySQL Slave Servers.

Thanks!

-Shailen :)

azzegai
December 6th, 2007, 02:04 PM
I was not able to find a reference to adding the 'log-slave-updates' option to slaves that are being backed up in the documentation. If this does get added to the documentation, it might be worthwhile also mentioning the following warning in the MySQL manual:

"The reason for running the slave without --log-slave-updates is to prevent slaves from receiving updates twice in case you cause one of the slaves to become the new master. Suppose that Slave 1 has --log-slave-updates enabled. Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master"

Switching Masters During Failover : http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html