What Is MySQL Hot Backup and How to Perform It in Windows?

About MySQL hot backup

MySQL hot backup means to backup a MySQL database while the database is running and applications are reading and writing to it. The advantages of MySQL hot backup are that it does not block normal database operations, and it captures even changes that occur while the backup is happening. You will desire to take hot backups when:

• The data is large enough that the backup takes significant time, and you don’t want to stop database operations during the backup process.

• The data is quite important to your business that you must capture every last change, without taking your application, website, or web service offline.

MySQL hot backup is a kind of physical backup that copies the actual data files. It is different from a logical backup that reproduces table structure and data. Other MySQL physical backup types include cold backup and warm backup.

How to hot backup MySQL databases with MySQL Enterprise Backup

To make MySQL hot backups, you can use the mysqlbackup command which is part of the MySQL Enterprise Backup component. It lets you backup a running MySQL instance, including InnoDB tables. For completeness, it can also backup tables from MyISAM and other storage engines. Follow the instructions below to create a hot backup of MySQL databases:

1. Download and install MySQL Enterprise Backup.

Tip: MySQL Enterprise Backup can be installed separately with either an individual .msi installer or .zip file. When installing with a .msi installer, choose the option Include directory in Windows PATH, so that you can run mysqlbackup from any directory.

2. Designate a location for the backup directory. You may specify two paths – one for backup images and one for temporary output, status, and metadata files. For me, I designate D:\MySQLBackup\Backups and D:\MySQLBackup\Backup-tmp as backup directories.

3. Run Command Prompt as administrator and input the following commands to backup an entire MySQL instance while it is running.

• mysqlbackup -u root -p --backup-image=D:\MySQLBackup\Backups\my.mbi --backup-dir=D:\MySQLBackup\Backup-tmp backup-to-image

• [password]

Notes:
● backup-to-image: create a single-file backup.
● --backup-image: specify the location and filename for the single-file backup.
● --backup-dir: supply the location for an empty folder to store temporary files.

4. When you see “mysqlbackup completed OK!”, type exit and press Enter to close Command Prompt.

▶ Verify a backup

To ensure a successful data recovery when problems occur, you can check the integrity of your backup using the following command:

mysqlbackup --backup-image=D:\MySQLBackup\Backups\my.mbi validate

▶ Restore MySQL instance

If serious problems happen with your MySQL databases, you can follow the steps below to restore a MySQL instance:

1. Shut down the database server in the Services window. You can press Win + R keys and type services.msc in the Run window to get to the window.

2. Delete all files inside the server's data directory and inside the directory you specify with the --backup-dir option.

Tip: If you are going to specify other directories by the --innodb_data_home_dir, --innodb_log_group_home_dir, and --innodb_undo_directory options for restore, you should also delete all files inside those directories, or the restore operation will fail.

3. Execute the following command in an elevated Command Prompt to launch the restoration process.

mysqlbackup --datadir=“C:\ProgramData\MySQL\MySQL Server 8.0\Data” --backup-image=D:\MySQLBackup\Backups\my.mbi --backup-dir=D:\MySQLBackup\Backup-tmp copy-back-and-apply-log

Notes:
● copy-back-and-apply-log: “copy-back” extracts the backup from the image file and copies it to the data directory on the server to be restored. “apply-log” performs an apply log operation to the restored data to bring them up-to-date.
● --datadir: supplies the location of the data directory for restoring the data. If there are spaces in the data directory path, add quotation marks around the path, or you’ll get stuck on “ERROR: Found extraneous commands at the end.”

4. When you see “mysqlbackup completed OK!”, type exit and press Enter to close Command Prompt.

5. Restart MySQL database server.

Tip: Depending on how you are going to start the restored server, you might need to adjust the ownership of the restored data directory. For example, if the server is going to be started by the user mysql, use the chown -R mysql:mysql /path/to/datadir command to change the owner attribute of the data directory and the files under it to the mysql user, and the group attribute to the mysql group.

Reliable software to run MySQL hot/cold backup in Windows

You may find it’s easy to perform MySQL hot backup and restore with MySQL Enterprise Backup. However, you should be really careful about the mysqlbackup commands you use. An extraneous space or missing quotes may lead to backup/restore failure.

To make MySQL database backup and recovery easier and worry-free, you can turn to the best MySQL backup software - AOMEI Cyber Backup. It allows you to create backups either when the database is running or not.

It provides a File Backup option to backup the MySQL data directory.If your data directory is in C:\ProgramData, it is hidden by default and you have to show hidden items before backup. Let’s have a look at the detailed steps:

Step 1. Download the AOMEI Cyber Backup free trial. Install and run it.

Step 2. Click Source Device > Agents. Then download and install agent to the computer that installed MySQL.

Step 3. Once the agent is installed, you will see the computer listed in this interface. Click Backup Task > Backup Task > Create New Task. 

Step 4. Then Create New Task wizard will be open. Choose File Backup as the backup type. Choose the MySQL data directory as the backup content, and configure other settings according to your needs.

Step 5. Click Start Backup to commit and execute the backup task.

Conclusion

There are two solutions offered in this article to help you perform MySQL hot backup in Windows. If you prefer command line operations, you can pick MySQL Enterprise Backup. If you want easier operations with intuitive GUI, then AOMEI Cyber Backup is recommended. Apart from backing up the MySQL, AOMEI Cyber Backup also enable you to backup VMware, MS SQL, Hyper-V, and other critical business data.