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

Quick Navigation:

• About MySQL hot backup

• How to create hot backup of MySQL databases with MySQL Enterprise Backup?

• Reliable software to run MySQL hot/cold backup in Windows

• Conclusion

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 Backupper Technician Plus. It allows you to create backups either when the database is running or not.

There are four backup types available: System/Disk/Partition/File Backup. To backup MySQL databases, you need to use File Backup to backup the 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 AOMEI Backupper free trial. Install and run it.

Step 2. Click Backup from the left panel and select File Backup.

Step 3. Name the task and choose Add Folder to add the data directory of MySQL.

Step 4. Choose a destination path to save the backup image. It supports various backup storage devices, such as external hard drive, USB drive, and NAS device.

Step 5. Click Schedule and Scheme to do more settings if you need. Click Start Backup to begin MySQL hot backup.

▶ How to restore MySQL databases?

Go to Home and find the MySQL backup task. Expand the drop-down menu and select Restore. Then, follow the wizard to complete the process.

✍ Tips:

• If you want to restore a specific database instead of the whole MySQL instance, you can navigate to the corresponding database folder and check it.

• If you want to restore an existing table to its previous version, you need to check Replace existing files before clicking Start Restore.

• If you are prompted the files are being used and cannot be overwritten, you may close MySQL database server and try again.

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 Backupper is recommended. Apart from the backup and restore features, AOMEI Backupper owns the clone function with which you can clone a physical server. Don’t hesitate to give it a try!