PDA

View Full Version : Incremental Backups



pr0t
April 9th, 2007, 11:01 AM
I can't find much documentation or extensive information about MySQL incremental backups so maybe I can get a few answers here.

1. Why do incremental backups if you already have binary logs?

2. On a replication slave what are the different situations binary logs can be deleted? If replication gets out of sync for all of my databases and I have to setup replication again will this delete my binary logs on the master or slave?

3. Say I want to do incremental backups every four hours so at 12 AM I do a full backup and four hours after that I force the binary logs to rotate and back it up.. thats how MySQL incremental backups work right? But what happens if so many queries were executed before I do my first incremental backup that the binary log rolls over on its ow, maybe even twice... how would I know and how could I prepare for something like that?

If anyone can point me to some links that have extensive information about MySQL incremental backups that would be greatly appreciated thank you.

kkg
April 9th, 2007, 09:16 PM
Hi,

While doing incremental backups, ZRM copies the binary logs to the backup destination and keeps track of the binary log files. This ensures that even if the binary logs get purged or deleted you can restore from the backup.

The way incremental backups are done in ZRM is as follows

a) The first step when a full backup is done in ZRM is to flush the logs so that the logs are rotated.
b) For an full backup, all that ZRM does (in relation to binary logs) is to register the name of the new binary log file that was created after the flush is done (for e.g. assume it was my-bin.000007)
c) Next time when an incremental backup is done, again the first thing that ZRM does is to flush the logs so that logs are rotated.
d) This new log file name is now noted. (say it was my-bin.000015)
e) Then it copies all of the binary logs that was created since the last backup. So in our e.g. it copies all of the binary log files from my-bin.000007 to my-bin.000014.

So now we have one full backup and one incremental backup with all of the data till the time the incremental was fired.

If a new incremental backup is fired, ZRM will now copy all of the binary logs from my-bin.000015 to the current binary log file that was present just before the log file that was created by the new log flush command fired by ZRM.

Hope this gives you a good idea of how ZRM does incremental backup. Please don't hesitate to ask more questions if I have not been clear or if you have any other questions.

--kkg

pr0t
April 10th, 2007, 12:14 PM
Ahh your answers definitely helped me understand a bit more I am not sure if I want to use ZRM, I think I would like to write my own Perl script to handle doing my incremental database backups cause I would want to do something along these lines;

1. Flush the binary logs so that the current one rolls over.
2. Get the name of the new binary log.
3. Any number less then the current binary log that we got parse out only the important data we need so something like mysqlbinlog -s binarylog and also parse out the time stamps and save it to a file with a timestamp and delete the old binary logs which we just got our data from.

That is how i was thinking of doing it, I am just not sure if that is the best way to go about it. I also have the issue of dealing with all the old binary logs that exists currently. If I implement this new backup solution I'd have to figure out what to do with all the existing binary logs.

pr0t
April 10th, 2007, 03:35 PM
How does ZRM get the name of the new binary log after the roll over? Maybe by using the index?

kkg
April 10th, 2007, 08:35 PM
Ahh your answers definitely helped me understand a bit more I am not sure if I want to use ZRM, I think I would like to write my own Perl script to handle doing my incremental database backups cause I would want to do something along these lines;

1. Flush the binary logs so that the current one rolls over.
2. Get the name of the new binary log.
3. Any number less then the current binary log that we got parse out only the important data we need so something like mysqlbinlog -s binarylog and also parse out the time stamps and save it to a file with a timestamp and delete the old binary logs which we just got our data from.

That is how i was thinking of doing it, I am just not sure if that is the best way to go about it. I also have the issue of dealing with all the old binary logs that exists currently. If I implement this new backup solution I'd have to figure out what to do with all the existing binary logs.

In ZRM, you can create a post backup plugin for most of your additional work that you need to do including purging of old binary log files.
You can get the name of the new binary log from the index file itself.
You can use the parse-binlogs action to parse the data out. This will parse all of the binary logs and all you need to do is to just write a parse-binlog plugin that does the saving into a file with the appropriate timestamp.

Hope this helps.

--kkg

--kkg

kkg
April 10th, 2007, 08:36 PM
How does ZRM get the name of the new binary log after the roll over? Maybe by using the index?


Yes, this information is stored in the index.

--kkg

pr0t
April 11th, 2007, 08:51 PM
Most of my question were based around the idea of developing my own Perl script to do the incremental backups, parsing, and purging of the binary logs, but with the input you gave me I will save a lot of time. For instance I had no idea that ZRM could parse the binary log for me. I also didn't know I could write plug-ins for ZRM. Instead of developing an independent solution I think I'll look more into ZRM, thank you very much for your input it will save me a lot of time and work.

kkg
April 11th, 2007, 09:34 PM
Glad I could be of help.

--kkg

pr0t
April 16th, 2007, 11:02 AM
I started playing with ZRM and it seems like a great tool, but I have a few things I can't figure out. The first thing is that I would like individual backup files for each database on the server, by default ZRM just throws them all in the same backup. I also don't quiet get the plugins is there any other manuals out there besides the wiki that might be able to help me understand how I can perform these task?

kkg
April 16th, 2007, 08:40 PM
I started playing with ZRM and it seems like a great tool, but I have a few things I can't figure out. The first thing is that I would like individual backup files for each database on the server, by default ZRM just throws them all in the same backup. I also don't quiet get the plugins is there any other manuals out there besides the wiki that might be able to help me understand how I can perform these task?


The best way to put each database in separate files is to use separate backup-sets.
BTW is there any specific reason for needing separate files for each database? Please do note that even though ZRM put all of the backups into one single file sql file, it has the capability to restore individual databases from this single file.

As regards the plugins, do take a look at the the following location on the wiki

http://mysqlbackup.zmanda.com/index.php/Plugins

If you need additional information, do let me know what you need and I will try and help answer them. This will also help us update the documentation.

--kkg

pr0t
April 19th, 2007, 02:38 PM
The reason I want individual backups is because I work for a company in which the developers frequently need backed up databases loaded onto to the developer server. So I will have a small php application where the developers can submit the database they want and which development server they want it to be loaded onto. This will be easy to do, but even easier if I dont want to decompress ALL of the databases on the server and parse out the one they requested. That's if the backups are backed up in individual files.

Also about using different backup sets, can this be done dynamically for all of the MySQL databases on the server or each time a new database is created will I have to manually add a new backup-set, because that could be a pain.

ZRM sounds like its a great tool, but for my situation I think it might be best just for me to develop my own script to do MySQL incremental backups.

kkg
April 19th, 2007, 03:44 PM
The reason I want individual backups is because I work for a company in which the developers frequently need backed up databases loaded onto to the developer server. So I will have a small php application where the developers can submit the database they want and which development server they want it to be loaded onto. This will be easy to do, but even easier if I dont want to decompress ALL of the databases on the server and parse out the one they requested. That's if the backups are backed up in individual files.

Also about using different backup sets, can this be done dynamically for all of the MySQL databases on the server or each time a new database is created will I have to manually add a new backup-set, because that could be a pain.

ZRM sounds like its a great tool, but for my situation I think it might be best just for me to develop my own script to do MySQL incremental backups.

You will need to manually add a new backup-set when a new database is created.

The one thing you might want to think of is to just write a script that will detect that a new database has been created and create a new backup-set for that database.

--kkg