SQLproNews Home Page About Article Archive News Downloads WebProWorld Forums Jayde iEntry Advertise Contact

Click to Play

The Challenges of Open Gov't
Because people have grown accustomed to the open experience they have with many aspects of their lives, it is only natural that they want an open...

Recent Articles

Using MySQL Workbench To Copy/View/Edit...
This guide is designed to be used for Linux desktops and servers. For best results, use the same Linux version for the desktop as is installed on...

Oracle Pledges To Up Investment In MySQL
An organization with a market cap in the neighborhood of $130 billion will soon be taking a renewed interest in MySQL. The chief corporate architect of Oracle...

Comparing NoSQL And RDBMS For Large Database...
The last few weeks I have witnessed a ton of passionate debate about what is better, NoSQL or RDBMS. It almost sounds like a religious battle between Windows and Mac fan-boys. To me this is like arguing that...

Using YQL To Connect With Large Groups Of APIs
A few days ago I spotted a Twitter conversation between Simon Whatley and Christian Heilmann which brought YQL back into my mind. In case you don't know...

DiscountASP.NET Improves Security Of SQL...
DiscountASP.NET, a Windows shared hosting firm, has announced improved security of their SQL Server hosting platform through the support of SSL encrypted...

Microsoft Answers Calls For SQL Server...
Your wish is - at least in this one instance - Microsoft's command. The company has listened to requests for another service pack for SQL Server 2005...

Better hosting, better service,
and better prices: Check Us Out

MySQL Incremental Backup

By Mike Marr

Best practices encourage SQL administrators to have a system in place to automatically and routinely backup their database. Let's take a look at a line of Joe Sysop's crontab file:

30 15 * * 3,5 mysqldump --user=user --password=pass --host= mydb > /var/backups/mydb-backup
So, automatically at 3:30pm on Wednesdays and Fridays, Joe's MySQL database is dumped to /var/backups/mydb-backup.

One Wednesday morning, Joe's database crashes. Joe calmly restores last Friday's backup and pumps his fist triumphantly when his database is backed up within minutes. Over the next few moments, Joe's inbox is flooded with angry e-mails. At first, Joe doesn't understand why everyone is so mad at him. He conquered the database crash and saved the day, but an e-mail from Susie Sales lets Joe know where he went wrong:


I made a ton of sales this weekend, and keyed them into the system. When I told my manager about it, he was unable to pull any of them up on his computer. I am also no longer able to see them. What did you do to all my sales?


Where did Joe go wrong? The obvious place to point the finger is at the duration between Joe's backups, but even if Joe made backups day to day, this problem would still exist for sales that happen between the last backup and a database crash. Information in databases are often so time sensitive that the immediate second after a backup is made, is a second of information too valuable to lose. Therefore, it is often important for database admins to utilize incremental backups.

Better hosting, better service,
and better prices: Check Us Out

Incremental backups work by storing every data modifying query made to a data, i.e. INSERT, UPDATE, DELETE, etc. Utilizing these backups allow Joe Sysop to take his database from the time of the last full backup to the time of the database crash, minimizing data loss. In terms of overhead, MySQL tests yielded only a 1% decrease in performance when enabling the binary log (the key component in incremental backups).

In order to utilize incremental backups in MySQL, you need to remember a few things:

Logging 101.Binary logging is where the magic of incremental backups take place. Without the binary log, you have no record of all the transactions and modifications to your database, and thus nothing to recover from. Be sure to add --bin-logto mysqld.

Flush your logs.As disgusting and routine as this may sound, this actually is in reference to your MySQL log files. Running the command FLUSH LOGSwhen you do your full backups will stop the current binary log file and start a new one. This allows you to decifer what stored transactions are between backups A and B. A good practice is to store these binary logs with your backups, giving you a clear picture of what happened between backup to backup.

No substitutions please. Incrementalbackups are NOTa substitution for full database backups. They are simply a tool to further minimize data loss, and rely on full database backups to work efficiently.

The actual process of restoring your incremental backup is similar to your restoration of a regular backup. If you have been flushing your logs regularly, and we hope you do, your binary log files should be separated from full backup to backup. Utilizing the command line tool mysqlbinlogwill parse the events logged into executable SQL. You can pipe this output directly into MySQL, but it is probably safer to dump this output into a text file to review. As SQL administrators, we never make SQL mistakes on a production database, but in the event we executed DROP `important_table`; on accident, we wouldn't want to re-issue the command via our incremental backup. Then we can review the code to make sure it was not the source of our data loss, and execute the code safely back into MySQL with all the information restored from our last full backup.

About the Author:
Mike Marr is a Staff Writer for WebProNews.
SQLproNews is brought to you by:

SecurityConfig.com NetworkingFiles.com
NetworkNewz.com WebProASP.com
DatabaseProNews.com SQLProNews.com
ITcertificationNews.com SysAdminNews.com
SQLproNews.com WirelessProNews.com
CProgrammingTrends.com SysAdminNews.com

About SQLproNews
SQLproNews is a collection of up to date tutorials and insightful articles designed to help SQL users of any skill level implement successful SQL systems and practices. SQL Strategies and Tactics for Business

-- SQLProNews is an iEntry, Inc. publication --
iEntry, Inc. 2549 Richmond Rd. Lexington KY, 40509
2010 iEntry, Inc.  All Rights Reserved  Privacy Policy  Legal

advertising info | news headlines | free newsletters | comments/feedback | submit article

SQL Strategies and Tactics for Business SQLproNews News Archives About Us Feedback