Automatic MySQL Database Backup to FTP, USB, NAS, Online

Websites

Webmasters rarely work directly with the server where their website is located. Instead, they are usually granted access to a number of folders via FTP or SSH and to a content management system (CMS).

In this scenario, backing up a website suggests that you back up FTP folder and MySQL database:

• To back up remote FTP folders, you need to make use of the FTP Backup plug-in in Handy Backup. It is compatible with all FTP servers and supports FTPS and SFTP protocols. To learn more, please see FTP Backup.
• To back up MySQL database, use the MySQL Backup plug-in.

This scheme should work for most CMS (Wordpress, Drupal, Joomla, etc.). Learn more about how to backup Joomla and migrate Drupal. Also to learn our recommendations on safeguarding websites, please see Website Backup.

MySQL-Driven Applications

There are many business applications that are based on MySQL databases and have no internal backup mechanisms.

In many cases stopping such applications results in a huge loss of productivity – and this is where third party solutions like Handy Backup prove their worth.

The Open File Backup feature enables you to back up any PC program in "live" mode, even if some of its files are opened exclusively. This lets you back up MySQL-driven software (like CRM, ERP, and other) without interrupting their work.

If you develop a product or a service that uses databases, e.g. provide web hosting services, then you should consider trying Handy Backup to improve competitiveness your business. To learn about partnership opportunities, please refer to the Partners section.

Enterprise Servers

In addition to corporate websites and business applications, your servers may manage many other mission-critical domains, from common document archives to customer databases and e-mail servers.

Handy Backup has many great features designed for protecting business data on both "physical" and "logical" levels: these include Disk Image Backup, NAS Server Backup, Exchange Server Backup and many other data source plug-ins.

To learn more, please see the Backup Features page.

Best Storage for Your Backups

If backups can’t be restored in reliable manner, they are worthless. This is why it is so important to select the most cost- and performance-effective storage for your data.

When a disaster strikes, the "time is money" mantra receives its most concrete and meaningful expression: losses incurred during downtime tend to grow geometrically.

• Storing MySQL backups remotely. Using FTP server or a NAS backup storage is a good idea, because the database becomes protected from local disasters. However, this method relies on network connectivity, and downloading backups of large databases may take a lot of time.
• Using optical discs. You can back up MySQL to DVD or other optical discs – many companies consider this approach the most convenient in terms of handling, transportability and price.
  It is much cheaper and easier to have multiple copies of your backups on DVD or Blu-ray discs, and distribute them across several safe places, than to have several hard drives.
  Still it is important to remember that optical discs have limited capacity, and restoration of backups will require a lot of manual work.
• External hard drives. Backing up to USB or FireWire drives is the fastest way to restore backups after data loss events. This method is relatively expensive, but for many companies losses incurred by each minute of database downtime may cost much more.

To learn more about destinations supported by Handy Backup, please see Backup Storage.

Differential and Incremental MySQL Backup

In commercial environments databases grow rapidly and may occupy hundreds of gigabytes of disk space. To reduce costs, the software must allow creating backups in the most efficient way. In Handy Backup, there are a number of advanced options and two main optimization techniques:

• Incremental MySQL backup. Incremental backup allows you to perform backup multiple times and copy only incremental changes with each run. Restoration of a given MySQL snapshot requires you to have a full chain of increments from the first to the last backup.


• Differential backup. Differential backup is similar to incremental backup, with the only difference that it calculates changes from first full backup. To restore a differential MySQL backup you only need two backups: first snapshot and differential changes.

It is important to understand how these two methods work with MySQL Backup plug-in in Handy Backup. Differential changes are calculated on byte level, i.e. it is the main way of optimizing size of backups with Handy Backup.

Incremental backup is performed on file level, and since there’s no way to estimate size and modification time of database tables, the plug-in always thinks that they were changed and copies them completely.

For more information on restoring MySQL databases, please refer to MySQL Restore.

Compression, Encryption and Other Options

The main difference between professional backup software and hand-written scripts is that the software comes with many useful features out of the box. Here are some of the options employed by Handy Backup:

• Built-in compression. Since the MySQL Backup plug-in creates a dump file, size of the backup can be reduced by 50-70% by compressing it. Handy Backup features on-the-fly ZIP compression which doesn’t use any temporary files and works with databases of any size.
  Learn more about ZIP backup compression.
• Encryption engine. Databases contain business data that must be protected from unauthorized access. The same is valid for backups: improper storage may lead to an event of a security breach. The solution is to cypher backups, making them practically useless for third parties.
  Similarly to compression, Handy Backup’s encryption engine works with data streams without saving backups to temporary files and thus providing the highest level of security. Learn more about backup encryption.

Data stream path: from MySQL database to storage media

There are many other functions that can be of great help when backing up MySQL databases. We welcome you to download our software and see them yourself!

Scheduled Backup: Strategies

To back up, restore, or synchronize data with Handy Backup, you need to create the respective task. Each task can be started manually, or scheduled to take place automatically. Below you can find general guidelines for setting up automated MySQL backups.

If You Don’t Have MySQL Replication

1. Estimate the amount of data you can afford to lose.
2. Identify the period for which your database receives this amount of changes. For example, if you’re running a blog, this could be one day: losing data for a day is unpleasant, but not fatal.
3. Set up differential backup and repeat it in accordance with the calculated period.
4. When size of differential backups starts reaching 10-15% of the full backup, repeat full backup.

If You Have MySQL Replication

With MySQL replication, the main danger you should worry about is human mistakes, i.e. the risk of corrupting master database by wrong actions. The recommendations are as follows:

• Set up weekly differential backups.
• Make a backup each time before conducting significant changes to your database.
• From time to time, verify synchronization of your master and slave databases.

For more information on replication, please refer to MySQL Replication.

Do remember, that no backup strategy can guarantee 100% safety, and it is important to control how your data is backed up. In Handy Backup, you can enable e-mail notifications that will send you a letter each time your MySQL database backup task finishes.

This is the easiest way to ensure that your backup routine works as intended, and its big advantage is that you can do it remotely, without having to approach the server.

See also

• User Manual for MySQL Plug-in
• ODBC Backup (an alternate method of backing up MySQL databases automatically)
• MySQL Synchronization