There are 6 ways provided in this post to backup MySQL database, you will know the MySQL dump database commands and other ways to create MySQL backup in Linux and Windows.
MySQL is an open source relational database management system. A relational database organizes data into one or more data tables in which data types may be related to each other. In addition to relational databases and SQL, MySQL work with operating systems to implement relational databases in a computer's storage system, manage users, allow network access, facilitate testing of database integrity and create backups.
MySQL database is important for enterprise or business institution, so here we will show you 6 different ways to make MySQL dump databases.
And there is no doubt that mysqldump is the first choice for technicians to backup MySQL database. Because mysqldump is a database backup software, which dumps one or more MySQL databases to backup or migrate to another server. And it will generate CSV files, text or XML files.
Mysqldump syntax
There are three different parameters included in the mysqldump command to backup MySQL database:
mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases
Tip: the above [options] please refer to the mysqldump options to get more details.
The first command backs up single MySQL database; the second command dumps the set of the specified databases; and the last mysqldump command is able to backup all tables in all MySQL databases.
And there are two ways to make MySQL dump databases using mysqldump: Command Prompt and MySQL Shell.
Input cmd in Windows search box, right click command prompt and run as administrator.
Then run the following 2 commands to backup MySQL database:
set path=C:\Program Files\MySQL\MySQL Server 5.6\bin
Replace C:\Program Files\MySQL\MySQL Server 5.6\bin with the file location of mysql.exe.
The above command will add the MySQL location and make the MySQL related commands available, or you will get the error: mysql is not recognized as an internal or external command, operable program or batch file.
mysqldump -h 127.0.0.1 -u root -p dbname > filename.sql
Replace the parameters with your own, and here is my mysqldump example:
mysqldump -h 127.0.0.1 -u root -p mysql > mysql.sql
After enter the password, it will create a dump file in C:\Users\username
To restore a MySQL database, just input the command:
mysql -u username -p dbname
For example:
mysql -u root -p mysql
To backup multiple MySQL databases, input the command:
mysqldump -h 127.0.0.1 -u root -p --databases dbname_a dbname_b > filename.sql
For example, backup two MySQL database named mysql and mydatabase:
mysqldump -h 127.0.0.1 -u root -p --databases mysql mydatabase > mysql-mydatabase-dump.sql
To backup all MySQL databases, please input the command:
mysqldump -h 127.0.0.1 -u root -p --all-databases > filename.sql
For example, input the following command to perform MySQL backup:
mysqldump -h 127.0.0.1 -u root -p --all-databases > all_mysql_database.sql
Moreover, you could use mysqldump command in MySQL Shell to create MySQL backup, here are the steps:
Open MySQL Shell, and execute the following commands one by one:
\sql (switch to the SQL mode)
\connect root@localhost (input the password for the root@localhost, or choose if save password)
mysqldump -u root -p --all-databases > all_database.sql
However, mysqldump has some limitations:
Mysqldump will not backup the INFORMATION_SCHEMA database by default. To backup INFORMATION_SCHEMA, please use use the –skip-lock-tables option and name it on the cmd.
Mysqldump does not dump the MySQL Cluster ndbinfo information database.
It’s not recommended to backup database with large tables because it’s a logical backup tool and loads data in memory, and the memory is limited.
1. Open MySQL Workbench, and click Server > Data Export. Or go Data Export under Management in Navigator.
2. At the Tables to Export section, choose the schema (MySQL databases) and the objects to export, select Dump Structure and Data. Tick Export to Self-Contained File in Export Options section, and the MySQL database dump file will be stored in C:\Users\username\Documents\dumps\Dump20201221.sql by default, or you could specify other location to store MySQL database dump file.
3. Press Start Export to begin backup MySQL databases.
To restore the MySQL databases:
1. Please use the Data Import next to the Data Export at the Server dropdown menu. Or choose Data Import/Restore under Management in Navigator.
2. Customize the MySQL Restore settings, and then click Import Progress tab, and press Start Import to restore MySQL Database.
Generally speaking, the database files stored in C:\Program Files\MySQL\MySQL Server 5.6\data\dbname, and these database files consist of files with the suffixes .MYD, .MYI, .frm, .ibd, .CSV, and .CSM.
If you want to copy entire database folder, please stop the server, lock and flush the relevant information. And then copy the entire database folder to other location to create a dump folder.
However, if your database has the InnoDB tables, you can’t just copy the entire folder to dump MySQL database, because the InnoDB may modify your data in memory and not flush to disk.
There are many cloud server control panels, like ISPConfig, Ajenti, CentOS-WebPanel, Virtualmin, Webmin, Everleap, and cPanel etc.
Take cPanel for example to create MySQL backup:
1. Please log on to your cPanel control panel, and select Backup under FILES section.
2. Choose a database name under Download a MySQL Database Backup section.
3. Select Save As and configure the settings, and click OK.
PHPMyAdmin is a free and open source adminstrator tool for MySQL and MariaDB. And you could import and export MySQL and MariaDB use these file format: .sql, text, pdf, CSV, JSON, Microsoft Word, PHP array, etc.
Here is how to create MySQL backup for all databases:
1. Click Export at the toolbar in PHPMyAdmin.
2. Tick Quick - display only the minimal options or Custom - display all possible options in Export Method.
3. Choose SQL format, and click Go.
It will create a ***.sql dump(backup) file.
Or you could select a MySQL database before clicking Export, and follow the same steps listed above to create MySQL backup for the specified database. And you could click Import to restore MySQL database.
If you are running Windows Servers, and AOMEI Backupper Technician Plus is a great choice for MySQL dump databases. Please download AOMEI Backupper Technician Plus 30-day free trial and have a try.
It could copy your MySQL database automatically on basis of daily, weekly and monthly. Besides, you can create a smart backup with Real-Time Sync feature, which allows you to sync any changed data from the source directory to the target directory in real time.
Here's a 30-day free trial for you:
Here is the steps to create MySQL backup in Windows Server 2003/2008/2012/2016/2019/2022:
1. Click Sync > Basic Sync on the main window.
2. Press Add Folder button to choose MySQL database folders. Choose a destination for the MySQL backup.
3. Hit Start Sync >> to create backup for MySQL databases easily.
Tip: You could create an automatic backup for MySQL databases via 5 modes, Daily, Weekly, Monthly, Event triggers, and USB plug in.
MySQL databases often store important data of entireprise, that's way it's an essential task to backup them. This article includes 6 effective ways, please choose the suitable one to dump MySQL database right now.