MySQL GUI tools such as phpMyAdmin, SQLyog and etc, often provide features for backup MySQL databases with ease. However, if your database is big, the backup process could be very slow because the backup file needs to be transferred across the network to your client PC. As the result, the backup process increases the locking and available time of the MySQL database server.
MySQL provides a very useful tool for backing up or dumping MySQL databases locally on the server very fast. The backup file is stored in the file system in the server so you just need to download it when needed.
The tool to backup MySQL databases is mysqldump. It is located in the root/bin folder of MySQL installation folder.
The mysqldump is a program provided by MySQL that can be used to dump databases for backup or transfer database to another database server.
The dump file contains a set of SQL statements to create database objects. In addition, the mysqldump can be used to generate CSV, delimited or XML files. In this tutorial, we will focus only on how to backup MySQL database using mysqldump tool.
In this tutorial, we will focus only on how to backup MySQL database using mysqldump tool.
How to backup a MySQL database
To backup a MySQL database, the database first has to exist in the database server and you have access to that server as well. You can use SSH or Telnet to log in to the remote server if you do not have remote desktop to it. The command to backup a MySQL database as follows:
You can use the SSH or Telnet to log in to the remote server if you cannot remote desktop to it. The command to backup a MySQL database as follows:
| 1 | mysqldump -u [username] –p[password] [database_name] > [dump_file.sql] |
The parameter of the command above as follows:
- [username]: valid MySQL username.
- : valid password for the user. Note that there is no space between –p and the password.
- [database_name]: database name you want to backup
- [dump_file.sql]: dump file you want to generate.
By executing the above command, all database structure and data will be exported into a single [dump_file.sql] dump file. For example, in order to back our sample database classicmodels, we use the following command:
| 1 | mysqldump -u mysqltutorial –psecret classicmodels > c:\temp\backup001.sql |
How to backup MySQL database structure only
If you only want to backup database structure only you just need to add an option –no-data to tell mysqldump that only database structure need to export as follows:
| 1 | mysqldump -u [username] –p[password] –no-data [database_name] > [dump_file.sql] |
For example to backup our sample database with structure only, you use the following command:
| 1 | mysqldump -u mysqltutorial –psecret -no-data classicmodels > c:\temp\backup002.sql |
How to backup MySQL database data Only
There is a case that you want to refresh data in staging and development system so the data in those systems are the same as the production system.
In this case, you just need to export data only from the production system and import it to staging and development system. In order to backup data only, you use option –no-create-info of mysqldump as follows:
| 1 | mysqldump -u [username] –p[password] –no-create-info [database_name] > [dump_file.sql] |
For example to backup our sample database with data only, you use the following command:
| 1 | mysqldump –u mysqltutorial –psecret –no-create-info classicmodels > c:\temp\backup002.sql |
How to backup multiple MySQL databases into a single file
If you want to backup multiple databases just separate database name by the command in the [database_name]. If you want to backup all databases in the database server use the option –all-database.
| 1 2 3 |
mysqldump -u [username] –p[password] [dbname1,dbname2,…] > [dump_file.sql]
mysqldump -u [username] –p[password] –all-database > [dump_file.sql] |
In this tutorial, you have learned how to use the mysqldump tool to backup MySQL databases with various options.
Code Blogs 