MySQL Recovery: Restoring Data, Setting up Replication, Migrating to Other DBMS

Comments: 0

MySQL Recovery

It is important to always remember that if backups can’t be restored when you need them, they are useless. The logic is very straightforward: the more downtime your business faces, the more revenue, time and productivity you lose. Since MySQL databases are used for websites, CRM systems and other mission-critical applications, not being able to recover backups can result in millions in lost revenues.

On this page you can learn about restoring backups made with the MySQL Backup plug-in in Handy Backup. Here are several common scenarios in which you can rely on our software:

  • MySQL recovery after data loss
  • Restoration of a backup to set up replication servers
  • Migration from MySQL to other DBMS
  • Experiments with your MySQL-driven applications

To learn about backing up MySQL databases with Handy Backup, please see MySQL Backup Tool.

Version 7.16.0, built on 31 July, 2018
164 MB

Contents [hide]

MySQL Restore: Use Cases

While the primary concept of backing up suggests that you create copies of important data for disaster recovery, there are many other ways how you can use your backups. Below you can see four different scenarios involving MySQL restore.


Recovering After Data Loss

If you’ve ever experienced the frustration of losing important information, then you already know the main purpose of backups. With regular backups and proper handling of storage media, you can restore your system to any point in the past. The idea is simple and valid for both home users and businesses. The only difference between them is that the latter have more data and need to be able to recover faster.

Of course, in some situations you can restore MySQL data from other sources than backups. For example, if your MySQL-based website crashes, you can find its articles in Google cache. But that’s more like an exception, because in most cases accurate “backupless” recovery is impossible, or costs too much.

We welcome you to download our software and start backing up your MySQL databases today!


Setting up Replication Servers

A more interesting case is using MySQL backups to set up replication servers. Replication is a built-in feature of MySQL that repeats all events happening to Master database on Slave databases, in real time or asynchronously. There are two things that you need to take into account when setting it up:

  • Replication reproduces events starting with the moment it is enabled, but not earlier. This means that before you run it, you need to synchronize Master and Slave databases. This can be done by creating a Master backup and restoring it to the Slave server.
  • Master and Slave databases are not meant to be absolutely identical. Contrariwise, it is a common practice to modify Slave database and make it read-only, to increase performance of your website or application.
Setting up MySQL replication

Restoring MySQL backup to set up master-to-slave replication configuration

Handy Backup exports your MySQL databases into a dump file, i.e. the backup is made in very understandable format, and can be easily modified. Before restoring it to a Slave server, you can open it with any text editor, find the CREATE TABLE statements and change storage engine to MyISAM (which is a general recommendation for master-to-slave replication).

To learn more about setting replication configuration, please refer to MySQL Replication.


Recommended Solution

Handy Backup Small Server

Version 7.16.0, built on 31 July, 2018. 164 MB
Backup Software from Novosoft LLC. 249 USD per license.

Small Business Backup Solution

To perform MySQL backup and recovery, you need the Handy Backup Small Server edition. You can try for 30 days for free!


Migrating to Other Databases

Depending on your business needs, you may want to migrate from MySQL to other database management software, like Oracle or MS SQL Server. This is another scenario where backups made by Handy Backup can be of great use.

Handy Backup creates MySQL dump files which are basically SQL statements that can create database tables from zero, and you can use these backups to fill up other SQL-compliant databases. Since implementation of each database is different, it is sometimes necessary to modify queries from MySQL dump to make them compliant with the other database’s syntax. Usually it is enough to check the following things:

  • General syntax rules. There are syntax differences that you need check during migration. For example, the MySQL’s LIMIT syntax is not supported by many DBMS, so you will need to rewrite the respective queries manually.
  • Stored routines. Migration of stored procedures and functions is often the hardest part of the whole procedure. Rewriting them into T SQL, PL/SQL or other language of your choice doesn’t take very long, but requires certain skills and expertise.
  • Data types. Different databases have different names for types of data that can be used in tables. For example, if you’re migrating from MySQL to Oracle, then you need to change INT to NUMBER, TIME to DATE, DECIMAL to FLOAT, etc. The mapping is individual for each migration, and should be made with care.

While the above may seem complicated, for most simple applications backups made with Handy Backup can be restored to other databases without many manual adjustments.


Experiments

Experiments are a very important part of any development process. Will your MySQL application work faster if you change storage engine from InnoDB to MyISAM? Will it require less database queries, if you change table structure? Handy Backup will help you to find it out: you use any text editor to edit the dump file, restore it to a test database, and test everything you need. It’s as easy as it sounds!

Version 7.16.0, built on 31 July, 2018
164 MB

Restoring Multi-Part Backups and Backups with Options

The beauty of the technology behind Handy Backup is that it exploits data streams only and doesn’t rely on temporary files. This greatly saves time and resources required for restoration. The idea can be illustrated with an example of daily incremental backups with compression and encryption:

  • Let’s say backup A is an encrypted archive of a full backup, made on day 0.
  • Backup B is an encrypted archive containing all changes that happened between days 0 and 1.
  • Backup C is an encrypted archive containing all changes that happened between days 1 and 2.

What if you need to restore backup C? Your actions should be as follows:

  1. Decrypt and decompress backup C.
  2. Decrypt and decompress backup B.
  3. Decrypt and decompress backup A.
  4. Calculate the list of files that you need to build by combining full backup and increments.
  5. Perform the restoration.

Handy Backup performs all these operations “on the fly”, transforming several encrypted and compressed backups into normal files. Even if your MySQL database occupies gigabytes of disk space, recovery will be done in a very efficient manner, with relatively small consumption of system resources.

To learn more about backing up MySQL in incremental or differential mode and with various options, please see MySQL Database Backup.

Licensing Information

To perform backup and recovery of MySQL databases, you need the MySQL Backup plug-in of Handy Backup. This plug-in is available in business editions called Small Server and Server Network.

Handy Backup Small Server
Handy Backup Server Network

Try MySQL Backup in Small Server and Server Network editions - 30 days trial!

The Small Server edition enables you to take advantage of all features, including MySQL Backup, MS SQL Server Backup, Oracle Backup, and other. Functionality of the Server Network edition is similar, with the only difference that it can back up multiple computers at once. Designed with client-server architecture in mind, it allows setting up backups on up to 5 networked machines.

You can find pricing on various editions in the Order section.

Video Example

In the following video, you can see an example of backing up and restoring MySQL 5.5.16 with Handy Backup 7.3.0.

Note: To follow instructions of the tutorial, you need to installed our software. Download it right now - it’s fast and free!

See Also

Version 7.16.0, built on 31 July, 2018
164 MB

With Handy Backup, recovering your MySQL data is easy as a pie!

Loading comments, please wait...

0 comments

    Contact Us

    support@handybackup.net

    sales@handybackup.net

    Handy Backup Small Server

    $249 per license

    Upgrade to version 7

    Volume Discounts
    1 license$249 per unit
    2-9 licensescontact us$249 per unit
    10-49 licensescontact us$249 per unit

    See all volume order prices

    Related to MySQL Recovery

    MS SQL Server Backup - learn about the MSSQL Backup plug-in

    Oracle Backup - read about Oracle database backup and recovery

    Jira Restore Backup - provide Jira restore from backup using MySQL database engine

    ODBC Backup - use the general Database Backup plug-in to export databases via ODBC connectivity

    Hot Backup - learn about hot (“logical”) backup and recovery that doesn’t require you to stop applications

    How to Restore MySQL

    To restore MySQL backup made with our program, follow these steps:

    1. Create a new restore task.
    2. Browse for the desired index file (backup.hbi). Index files are created for each backup and include information about data that must be restored.
    3. Go through restoration options, name the task and click Finish to run it.

    You can also restore MySQL backup by right-clicking the backup task and clicking Restore.

    Who uses our backup software?