A MySQL full backup enables you to restore all databases and tables when problems occur. Learn how to create a full backup for MySQL databases easily from this page.
Quick Navigation:
MySQL is an open-source relational database management system (RDBMS). It has Community edition and Enterprise edition. The Community edition can be used for free while the Enterprise edition offers a comprehensive set of advanced features.
MySQL has stand-alone clients that allow users to interact directly with a MySQL database using SQL, but more often it is used with other programs to implement applications that need relational database capability.
If you have created a number of MySQL databases and tables, it is necessary for you to backup MySQL to protect your data from media failure, user errors, permanent loss of a server or other unexpected situations.
Most backup strategies start with a complete backup of the MySQL server, from which you can restore all databases and tables. After a full backup, you can perform incremental backups for the next several backup tasks. You then make a full backup periodically to begin the cycle again. Then, how to do MySQL full database backup? Please read on.
MySQL offers a built-in backup tool – mysqldump. The mysqldump utility can dump a database including the SQL statements required to rebuild the database. Following is the syntax of the mysqldump utility:
mysqldump -u [user name] -p [password] [options] [database_name] [tablename] > [dumpfilename.sql]
Learn how to use mysqldump to backup MySQL as follows:
1. Run Command Prompt as administrator. If you don’t run it as administrator, you may get stuck on “mysqldump access denied”.
2. Run mysql full backup commands as shown below to create a full backup for a database named “test”.
mysqldump -u root -p test > D:\MySQLBackup\test.sql (you may replace D:\MySQLBackup with another location to save the dump file)
[password]
3. Type exit and press Enter to close Command Prompt.
Tip: To do a MySQL full dump of all databases, run the command mysqldump -u root -p --all-databases > all_databases.sql in step 2.
If you encounter ‘mysqldump’ is not recognized as an internal or external command, operable program or batch file, you can try the solution offered below:
1. Right-click the This PC icon on the Windows desktop and select Properties.
2. In the pop-up window, select Advanced system settings from the left panel.
3. In the System Properties window, click on Environment Variables.
4. Under System variables, select Path and click on Edit.
5. In the next window, click New, add the path name of the MySQL bin directory and click OK. For me, it’s C:\Program Files\MySQL\MySQL Server 8.0\bin.
6. Click OK on the Environment Variables window to save changes.
If your original database is corrupted or lost, you can follow the steps below to restore it:
1. Run MySQL Command Line Client from the Start menu. Enter the valid password of the MySQL user.
2. Execute the following commands to drop the original database and recreate an empty one.
drop database test;
create database test;
exit
3. Run Command Prompt as administrator and type the following commands to restore the “test” database.
mysql -u root -p test
[password]
4. Type exit and press Enter to close Command Prompt.
Tip: When you restore the database, instead of using mysqldump, you must use mysql; otherwise, the mysqldump will not generate the schema and the data.
Although the steps to backup and restore MySQL databases with mysqldump aren’t complicated, it requires you to be acquainted with SQL syntax. A miss of semicolon, comma, or space will lead to the failure of MySQL backup and restore.
If you prefer GUI operations, you can turn to the best MySQL backup software - AOMEI Backupper Technician Plus. It offers all-around backup solutions as follows:
✦ You are able to do Windows OS backup, disk/partition backup or file backup as per your need. ✦ You can create a full backup for all data or run incremental/differential backup for only changed data. ✦ You are allowed to create MySQL automatic backup to save manpower and enable backup scheme to delete old backups automatically. ✦ It supports all Windows Server & PC operating systems, including Windows Server 2019/2016/2012/2008 and Windows 10/8/7.
To create MySQL full database backup, you need to backup the corresponding database folder which is located in the data directory. The default data directory location is C:\Program Files\MySQL\MySQL Server 8.0\data, or C:\ProgramData\MySQL\MySQL Server 8.0\Data. If your data directory is in C:\ProgramData, it is hidden by default and you have to show hidden items before backup. Now follow the instructions below to do MySQL backup:
Step 1. Download AOMEI Backupper free trial. Install and launch 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 MySQL database folder.
Tip: To backup all the MySQL databases, you can add the whole data directory to be backed up.
Step 4. Specify a destination path to save the backup image. It supports various backup storage devices.
Step 5. Do more configurations with Schedule and Scheme based on your need. Click Start Backup to begin MySQL full backup.
★ How to run MySQL incremental/differential backup?
After the first MySQL full backup, you can run MySQL incremental backup or differential backup for the next time. There are two methods available.
Method 1: Go to Home and find the MySQL backup task. Expand the drop-down menu and select Backup > Incremental Backup/Differential Backup.
Method 2: If you have created an automatic backup, the program will run incremental backup by default. You can change to differential backup under the Advanced tab of the Schedule Settings window.
★ 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.
Now you know how to run MySQL full backup on Windows 10/8/7 & Server. The mysqldump utility offers you command line operations, which requires you to be tech-savvy. If you want a simpler way, you can turn to AOMEI Backupper Technician Plus. With the concise interface, you can finish the backup and restore operations with a few clicks. Just go for it!