How to Back Up and Recover a MySQL Database

Article
Since 1995, MySQL has been the relational database management system of choice for millions of businesses around the globe. The flexible Structured Query Language (SQL) architecture used in MySQL, along with its low-cost open-source licensing, make it the ideal data repository for applications ranging from accounting and ERP software to Web server applications such as Joomla and WordPress. In fact, according to an iDatalabs report, about 25 percent of all medium-to-large-sized companies use MySQL in one capacity or another. mysql database backup

The Database Is Your Data!

The database is the most critical element of most applications, as it is where the data is actually stored and managed. And, without a working database, most applications that rely on them are essentially useless. Therefore, if you have business or server applications that rely on and connect to MySQL, maintaining good backups of the database files is crucial.

A good MySQL backup can save your team a lot of hard work, frustration, and lost revenue in the event of a disaster or if you need to migrate systems to new hardware. So, in this article, that is exactly what we are going to cover: why you need to backup MySQL with Acronis Backup 12.5 and how to do it.

Saving Data for Reinstalls and Migrations

In almost any business environment, software and hardware changes are inevitable. Software upgrades, adding new systems or moving to new servers are but a few of the reasons why fresh installs, reinstallations, and migrations are a common occurrence for most IT departments. If you are involved with any of these activities, you probably know that installing or reinstalling applications is the easy part; it is the migrating of old data that presents the most challenges.

When reinstalling software, the installation or setup routine usually generates a new database for the program. While some applications do include tools that enable you to export, import, and migrate MySQL database files, there are just as many that do not. Therefore, if you need to migrate data to a new machine or for a fresh install/reinstall, you will probably need to move the old MySQL database file manually. Hence, this is another reason you need to keep good backups of your MySQL databases.

Pre-Backup Procedures for Preventing MySQL Record Corruption

Before backing up your MySQL database, it is important to first take a few steps to ensure that your backup file will not become corrupted during the process. If you just jump into backing up an active, running MySQL database, it is possible that the backup may be incomplete or that database consistency may be affected.
The best way to avoid corrupted records in a MySQL database backup is to ensure that the database is closed completely and not running during the backup operation. This usually requires two separate steps:
  1. Close the application that accesses the MySQL database;
  2. Shut down the MySQL server application.
Once you are sure that MySQL is no longer active and being used, it should then be safe to go ahead and backup the database without fear of record or table corruption.

Backing Up Mission Critical Applications

While shutting down the MySQL database servers and the application that connects to it is the best way to prevent data corruption, there are times when doing so could have serious negative side effects to business operations. For instance, if the MySQL database powers point of sale or other enterprise software or if it is the backend for a corporate website, shutting down the MySQL server could cause significant downtime and loss of revenue. Therefore, when backing up MySQL databases used for mission-critical applications, it is essential to limit database downtime as much as possible.

Acronis Pre/Post Operation Scripts to the Rescue

Because shutting down a MySQL server for long periods during a corporate data backup can lead to significant errors and problems in some situations, you will need to find a way to limit the shutdown time as much as possible. With Acronis backup software, limiting MySQL downtime is relatively simple.

Acronis provides downloadable scripts that are designed to run before and after creating a backup snapshot. The scripts utilize the MySQL “FLUSH TABLES WITH READ LOCK” command to lock tables and records before starting the backup, and then subsequently unlocks the database after Acronis Backup 12.5 creates the snapshot. Using this method reduces downtime significantly versus shutting down the MySQL database and server for the entire time the backup operation needs to run to completion.

How to Backup a MySQL Database on a Linux Server

Acronis Backup 12.5 makes it incredibly easy to create complete backups of your Linux Server systems with a couple of mouse clicks. If your Linux Server runs applications that use MySQL databases, though, the backup requires a couple more steps. Nevertheless, the backup process is still relatively simple. To back up MySQL databases on your Linux Server machine to a local or network hard drive, just the follow the steps below:

Part I – Install the Management Console

Step 1 – Create an Acronis account if you have not already done so. Make sure to activate your account using the link in the confirmation email you receive from Acronis.
Step 2 -  Log in to your Acronis account. Log in to your Acronis accountAcronis Backup Advanced

Step 3 – Download and install the applicable installer for the machine that will be running the management console application.

Acronis Backup 12.5Install Your Backup solutuin

Step 4 – Launch the Acronis Backup setup file. Follow the prompts to download and install the application on the computer from which you plan to run the management console.

Step 5 – Log in to your new Acronis backup server with your Administrator username and password. Add license keys as needed to your Acronis installation.

Log in to your new Acronis backup serverAcronis Backup DasboardAcronis Backup License Key

Part II – Install the Backup Agent on the Linux Server Machine

Step 1 – Download the Linux Backup Agent installation file from within the console management window.

Step 2 – Log in to your Linux server with your preferred terminal or FTP application. Create a new folder on the server named “AcronisBackup” or something similar.

Step 3 – Copy the Linux Backup agent installation file to the Linux server.

Step 4 – Go back to the Linux server and open your terminal application. Move to the folder that contains the Linux Backup Agent installation file. Run the installation file on the Linux Server to install the backup agent and connect to the machine with the Acronis console management application installed.

Tip – If you need additional preparation or installation instructions for your particular distribution of Linux, please refer to this Acronis Knowledge Base article or these step by step instructions for installing Acronis Backup.

Step 5 – Go back to the system running the Acronis console management application. Open a new tab or window in your Web browser application, and then navigate to the Acronis Knowledge Base article 58136: Acronis Backup: Backing Up MySQL. Click the link titled “Download and unpack scripts.”

How to backup a MySQL database

Step 6 – Use an FTP application to move the scripts file to the directory on the Linux server where the SQL database file is located. After moving the scripts, unzip the files in the selected folder on the Linux server machine.

Part III – Backing Up the MySQL Server

Step 1 – Go back to the system running the Acronis console management application.

Step 2 - Select “All Machines” in the sidebar menu. Click the “Enable Backup” button underneath the computer name for your Linux server machine.

Enable BackupDashboardPreparing for backup of mysql database

Step 3 – Click the drop-down list button to the right of “What to Back Up,” and then select “Files/folders.”

Backup plan

Step 4 – Click the “Specify” link to the right of “Items to Back Up.” In the next pane, click “Select files and folders.” Navigate to the folder that contains the MySQL database file you want to back up. Click the checkbox next to the folder name with the MySQL database file. Click the “Done” button.

MySql DB backupFiles and folders

Step 5 – Click the “OK button.

OK button

Step 6 – Click “Specify” to the right of “Where to Back Up.” In the “Where to back up” pane, click “Add Location.” Click “Local Folder” or “Network Folder.” Navigate to the local or network drive and folder where you want to save the MySQL backup file, and then click “Add.” In the “Where to back up” pane, click the path to the local or network backup folder you selected.

Specify where to backupAdd locationSpecifying folder for mysql backupMySQL backupsCreate backup

Step 7 – Configure schedule settings for the MySQL backup, and then click “Done.”

BackupScheduling your backupStart Backup

Step 8 – Determine how long you want to keep copies of the backup database. Configure the cleanup settings as needed, and then click the “Done” button.

encrypted backup of mysqlCleanupMySQL backup - done!

Step 9 – Click the “Encryption” toggle switch. Enter a strong password for the encryption. Re-enter the password to confirm it. Select an encryption algorithm, and then click “OK.”

Backup ScheduleEncrypt your backupAES 256password protected backupBackup options

Step 10 – Click the “Gear” icon in the upper-right corner of the window. In the “Backup options” window, scroll down to and click “Pre-post data capture commands.”

Advanced backup optionsAlertsCommandsAcronis Backup - options

Step 17 – Click the “Yes” toggle button underneath “Execute a command before the data capture.” In the “Command or batch file path on the machine with an agent” field, enter the path to the “pre-freeze-script.sh” file. (Note that this is one of the files you downloaded earlier. In the “Working” directory” field, enter the path to the folder that contains the MySQL database file.

toggle buttonsexecuting commandsbackup working directory

Step 18 – Click the “Yes” toggle button underneath “Execute a command after the data capture” In the “Command or batch file path on the machine with an agent” field, enter the path to the “post-thaw-script.sh” file. (Note that this also is one of the files you downloaded earlier. In the “Working” directory” field, enter the path to the folder that contains the MySQL database file. Click the “Done” button.

enter the path to the folder that contains the MySQL database filebackup agent

Step 19 – Click the “Create” button. After the window refreshes, click the “Run Now” button. Wait for the backup to run and refresh the console window.

Run NowThe process of backing upMySQL backup - success!

Tip – You can also choose to backup your MySQL databases to the secure Acronis Cloud. To do so, just select your Acronis Cloud account in the “Where to Back Up” window.

Important Note – The above instructions are for use with MySQL databases where all files are contained on a single volume. If creating snapshots of databases spread across multiple volumes, the above process may not work correctly

Recovery Options

Hopefully, you will not need to use your MySQL backups very often. Nevertheless, chances are good that at some point, restoration of a MySQL backup will become inevitable. Fortunately, Acronis Backup 12.5 makes it super simple to recover your MySQL backups with just a few mouse clicks. To recover your MySQL databases quickly and easily, just follow these simple directions:

Step 1 – Shut down the MySQL server on the Linux server system.

Step 2 – Launch Acronis Backup 12.5 console management application in a Web browser. Log in with your username and password if prompted to do so. Click “Devices” in the sidebar menu.

mysql db recoverybackup and recovery statusprotected devices

Step 3 – On the “All machines” page, click the “Recover” button underneath the machine on which you created the MySQL backup plan.

vm backup

Step 4 – Click the “Recover Files/Folders” button.

Recover Files/Folders

Step 5 – Enter the encryption password for the MySQL backup file, and then click “OK.”

Enter the encryption password for the MySQL backup fileok

Step 6 – Navigate to the folder where you want to restore the MySQL database backup file. Click the checkbox next to the “Type” label near the top of the window, and then click “Recover” in the upper-right corner of the page.

Navigate to the folder where you want to restore the MySQL database backup filemysql backup filesdownload backupchecking files

Step 7 – Select “Original Location” in the Recover Files pane, and then click “Start Recovery.” Alternatively, select “Custom Location” if you want to move the database installation to a new folder or machine.

Start recovery

Step 8 – Select a file replacement option. You can choose from “Overwrite existing files,” “Overwrite an existing file if it is older,” or “Do not overwrite existing files.” Once you make your selection, click the “Proceed” button. Wait for Acronis Backup 12.5 to run the recovery process and update the monitor window.

overwrite existing filesMySQL database recoveryBackup activity

Step 9 – Restart MySQL on the Linux server.

you have now successfully restored the MySQL database file to the selected folder

That’s it; you have now successfully restored the MySQL database file to the selected folder.