How to Back Up a MySQL Database

文章
mysql database backup

This Article Will Cover:

  • MySQL Basics
  • Why and When MySQL is Important
  • How to Back up a MySQL Database
  • Restoring a MySQL Database
  • About Making Backups of Remote MySQL Databases

Introduction

Databases serve as the heart of countless applications and programs used both by consumers and businesses. With databases, users can store, sort, search and manage data related to anything from car parts to recipes – and everything in between. Because databases act as the information core for many programs, they are usually also the most integral and essential part of those applications.

Released in 1995, MySQL is an open-source relational database management system used by millions of businesses and end users everywhere. The flexible Structured Query Language (SQL) used in MySQL makes the application an ideal data repository and management component for both small desktop applications, such as contact managers and phonebooks, and large online server applications like WordPress. In fact, MySQL is so powerful that many of the world’s largest brands use it to power their websites, including Facebook, Google, Twitter, and YouTube.

Why You Should Back Up MySQL Databases

As you are probably aware, uninstalling and reinstalling many applications is usually straightforward and simple. So if you need to move an application from one computer to another or just want a fresh installation, reinstalling the program itself is relatively easy. Still, if the application connects to a MySQL database, what about all the information you have already entered in the application or system? What happens to it?

Where is the Database?

With many applications, MySQL database files are stored in the same folder or directory as the executable files for the program. Consequently, if you delete or reinstall the application, you may delete the MySQL database files in the process. If that happens, then you will have to recreate all the data from scratch – unless you have a backup.

If you install the MySQL-connected application on a new machine, the installation process will probably create a new database file as well. Therefore, if you want to back up and restore old data from a previous installation, you will also need to migrate the MySQL database to the new computer.

Application Windows are Often Shells

In many cases, the program window is just a shell or interface for working with the database. Therefore, backing up the application itself is usually not as important as backing up the database that powers it. While some applications include tools that enable you to back up or export MySQL databases, many do not. So unless you want to recreate all your valuable data from scratch, you will want to ensure you have a reliable backup option for your MySQL databases.

Backing Up a Local MySQL Database

Now that you know how essential MySQL databases are, let’s examine how to back them up so you always have access to the important data stored in them. Before we get to the actual process of backing up a MySQL database, though, it is important to understand how they work and where they reside and run.

MySQL Server Required

Because some applications or server stacks may set up and configure a database automatically without user intervention, it is easy to assume that MySQL is part of the host program that installs it. However, MySQL runs in its own fully distinct and separate server environment. When backing up a MySQL database, it is not necessary to back up the MySQL server installation. Nevertheless, it is important to have a basic understanding of the server’s role before attempting to back up the actual database.

While another program may create and configure a MySQL database file, the MySQL server application performs the actual hosting and management of the database itself. The connected application essentially just sends commands and requests to the MySQL server for management of the data. Thus, before you can use a MySQL backup database in a new or reinstalled environment, you must ensure that MySQL server is present and running on the new machine or installation.

MySQL

Steps before Backing Up

Before beginning to back up a MySQL server, it is best to shut down or suspend the MySQL server application to ensure an accurate database backup and prevent record corruption. With desktop applications used only on a single computer or small business network, this should be relatively straightforward. Still, you should ensure that you shut down the actual MySQL server application and not just the program that uses the database file.

In most cases, exiting the MySQL-connected program will not close the MySQL server itself. Consequently, you will most likely need to shut down the server application manually. In Windows you can do this in the Task Manager (Ctrl + Alt + Delete), and on a Mac by using the Force Quit Applications window (Command + Option + Escape.) Once you kill or shut down the MySQL server process, you can proceed safely with the MySQL backup.

Mission Critical Applications

While completely halting all MySQL processes is the best way to avoid corruption of backup database files, shutting down the server for long may not be feasible or practical in some environments. For instance, if you use MySQL on a busy website running applications such as Drupal, Joomla, or WordPress, completely shutting down the server application will probably make the site inaccessible or at least cause errors. Nevertheless, to avoid data corruption or record loss, you must halt the SQL server – even if only briefly – before starting the MySQL backup.

It is possible to initiate a SQL database backup while the MySQL server is active and running. However, doing so will almost always result in corruption errors with some records or tables. Requests and operations associated with data in a MySQL database are known as transactions. If you create a backup of a MySQL database, any transactions occurring at the time of the snapshot may be corrupted.

Pre-Backup Commands and Scripts

Since shutting down MySQL server for long periods can cause significant problems in some environments, limiting the amount of downtime as much as possible is extremely important. Fortunately, there are a couple of ways to limit the period that is MySQL is not running during the backup operation – at least when using Acronis backup software.

Acronis provides downloadable scripts that execute before and after creating a snapshot during the backup process. The scripts allow you to lock tables using the MySQL “FLUSH TABLS WITH READ LOCK” command, and then subsequently unlock them once Acronis creates the snapshot. This approach can decrease downtime significantly compared with shutting down the server for the entire duration of the backup process.

Performing the Actual MySQL Backup

Once you perform the pre-backup operations described above, your work is almost all done. After halting the MySQL server, you can perform an easy backup of the database. Once you’re ready, all you need to do is:
  1. Select the source of the backup (the folder or directory containing the MySQL database file)
  2. Select the destination for the backup (local drive or Acronis Cloud)
  3. Click “OK”
  4. Click “Backup Now”
That is all there is to creating a backup up your MySQL database. After the backup finishes, just restart the MySQL server. If you are using the Acronis FLUSH command scripts, the server will restart automatically.

Note: Do you have multiple MySQL databases? You can create a MySQL backup of all databases using the same method. Just ensure that the MySQL server is not running.

Tip: If you want to set up automatic backup of a MySQL database, you will need to create a schedule for shutting down the MySQL server. You can set up schedules in Windows with the Task Scheduler and on a Mac by using the iCal utility

Restore a MySQL Database from a Backup File

Restoring a MySQL database requires only a few simple steps:
  1. Shut down or suspend the MySQL server application
  2. Select the name of the MySQL backup on the left side of the window.
  3. Click “Recover Files”
  4. Select the backup database file to recover and click “Next”
  5. Click “Recover Now”
It will restore the backup MySQL database file to its original folder automatically. If you want to change the destination folder for the recovery, you can do so by clicking “Browse” and selecting the new folder before clicking “Recover Now.”

About Making Backups of Remote MySQL Databases

The above-mentioned methods will work fine for MySQL databases installed on a local machine. If you need to back up a MySQL database located on a remote server, though, the process is a little different – but still super simple.

You can use a web browser and the Acronis Online Dashboard to perform the backup and restore operations. You can access the Acronis Online Dashboard by going to https://cloud.acronis.com and logging in with your Acronis username and password.

When backing up a database from a remote machine, it is important to remember that the MySQL server must still be shut down or suspended before starting the backup.