How to backup MySQL using binary logging
5 stars based on
Mysql is the world's most popular database for Internet use, powering applications on thousands of websites. It's very likely that you use one or more Mysql databases. Mysql databases need to be backed-up, and backup should incremental backup using binary log be done more than once per day.
A RAID sometimes fails! This article with backup script shows you how to set up full and incremental compressed backups of multiple databases to Rimuhosting's or another backup server using mysqldump and mysql binary log files. Backup will be done every 6 hours configurablewith a full backup once per week. Since backups are incremental and are compressed, the amount of data to be transmitted is relatively small.
It is assumed you have a running mysql server, which should provide you also with the utility programs needed for both backup and recovery. This backup method should work for any mysql database storage engine e.
It incremental backup using binary log however tested only for myisam databases on Debian Etch. It incremental backup using binary log be used to make a full backup of one or more databases in a single operation. The backup strategy is: Configure mysql to do binary logging. You can specify which databases to do binary logging for, or which databases NOT to do binary logging for.
If this statement is used, all databases not specifically named will have binary logging turned off. Multiple statements can be made, one to a line:. If you have doubts, you should read http: Restart your mysql server:. Modify this as desired. The script refers to the Rimuhosting backup server. However, the script can easily be modified to back up to any FTP server. I can install this system on most servers for a fee. If interested, contact me.
Edit the above script to reflect your mysql username probably root and password, as well as your FTP backup server username and password. Replace "database1 database2" with the names of the databases to back up.
The "echoed" statements incremental backup using binary log be emailed to you, the system administrator, by cron. Remove these lines if you do not want the emails. The script handles both incremental and full backups, compresses all files before transmission, and automatically cleans outdated backup files from the backup ftp server.
It performs one full backup per week, on Sunday at You can force the creation of a full backup for testing or whatever purpose by passing the argument "dump". The "flush-logs" mysql command is used to create a new binary log file.
This command might be issued to the mysql server outside of this script such as when the mysql server incremental backup using binary log restartedproducing an extra binary log file. In this case, the script will compress and back up the extra binary log file the next time backup is done. For any backup incremental backup using binary log, It is a good idea to practice the recovery step before assuming that you are protected!
I added encription to compressed file using OpenSSL and used rsync instead of ltfp. Please, how can set this up on window environment. I need to set up both full and incremental. Hi, I am going to create a script that will fetch the newer bing log files and restore on new db on another server. Database name is different here. Can you please help me in this, how can i restore this to new database name? I have multiple databases on my server.
I'm taking full backup for each database separately every week. Now I want to implement incremental backup. So is there a way to do it for individual database different bin log for each DB? If not, how do I recover any one of the databases alone? As far as I understand, if you use the binlog to record changes to multiple datebases, each binlog will contain information about ALL the databases.
When you "replay" the binlog, all backed-up databases will be recovered. It won't be possible to recover only a single database. It's really nice work out and useful article. It's works for me. I just want to do some advance. I want binary log file to be separated for each databases. Currently I have 3 different DBs on same server i.
Is it possible to configure to create separated binary log file for each DB? Sorry, the binary log can't be separated for each database. If you configure several databases to have binlog backup, you will have to recover all databases together when the binlog is "replayed" at recovery time.
I get inspiration for incremental backup from http: This is exactly what I was looking for. All that remains for me is to encrypt files before saving them to the backup server. If I were to do this now, I'd use rsync instead of lftp for file transfer. And if using rsync, ordinary encryption won't work. To find out why, and to discover a working solution with rsync for this case, visit here:. You can set it to incremental backup using binary log a full or incremental backup. If you want it, just send me an e-mail.
Hi, I have to setup a new incremental backup of mysql database. Can u please send me an example file with detailed instruction? I will be highly obliged to you. Sorry, I don't have time to send you detailed instructions. However, you may consider using this simpler alternate backup methodone I am actually using myself now and that I recommend for incremental backup using binary log but the biggest mysql databases.
If you use the command "use sqlgrey;" and then issue "delete from drupalaa. See the following URL for a proper explanation of these options http: That could potentially cause trouble, but not, I think, under most incremental backup using binary log in which these backup instructions would be used.
If the above two conditions are not met, there is definitely a danger here. Thanks for pointing this out! In my case, the sqlgrey database is used by an entirely incremental backup using binary log process which knows nothing about the Drupal databases drupalaa and tosdrupal1. User login Log in using OpenID: Generate a full backup of databases with mysqldump. This incremental backup using binary log backup is compressed and backed-up to off-site storage via FTP.
With binary logging enabled, every mysql action which modifies the database after the full backup is stored in the current binary log file.
Every 6 hours configurablethe current binary log file is closed, and it is FTP'd to the backup server. A new binary log file is created to continue to record changes to the databases. Step 3 is repeated. Once per week, a new full backup is created step 1and all binary log files are purged. The previous full backup and the now-obsolete binary log files are deleted from the backup server.
The restore strategy is: The full backup is restored. Apply the incremental database changes contained in each binary log file, consecutively. The utility programs "mysqladmin" and "mysqlbinlog" are used for this. This brings the databases up-to-date to the moment of the latest incremental backup.
Utility Programs mysqldump and mysqladmin are part of the mysql-client-x. Multiple statements can be made, one to a line: Restart your mysql server: The full backup will be bzip2-compressed, and will look like this: Decompress all the backup files for example: Run a command like this: If the the databases exist, any existing tables will incremental backup using binary log removed!
Next run a command like this: Mysql applies all the sql commands, bringing the databases up-to-date, to the moment of the last incremental backup. This is a great idea, thanks for posting such a useful example.