Every application needs a database to store information. It does not matter if it is a web application or a mobile application. And the information a database holds is very important for an individual or a company that owns the database. For example, if you are a blogger, you don’t want to lose the content you wrote on your blog. In most cases, you might be using MySQL Database management system to manage your database. If you are using WordPress, you are using MySQL for database management. MySQL provides a program/utility to backup your data. It is called mysqldump.
In this guide, you will learn mysqldump utility in depth. You will learn almost everything you need to learn in order to backup your data efficiently. With mysqldump, you can even take a backup of your database schema, data in specific tables, whole database or multiple databases. It is a very good utility with lots of features to back up your data.
So, Log in to your server via SSH or open up a terminal on your computer and let’s start with the syntax of the mysqldump command in Linux.
mysqldump Syntax
The program is very easy to use. According to the man page of mysqldump, the syntax of the command is as follows.
mysqldump [options] [db_name [tbl_name ...]]
Normally, in the mysqldump tutorials, you will see > example.sql at the end. While it is not present in the syntax. The reason is, > is an operator in Linux that tells the system to redirect/store the output in the filename given after > operator.
It means that mysqldump cannot create a file with all the data on its own. The utility will only spit out the data as output as per your requirements. However, if you want to store it in a file, you have to use the > operator in Linux.
If you want to try out the utility without redirecting the output to a file, execute the following command.
$ mysqldump -uroot -p DATABASE_NAME TABLE_NAME
Do not forget to replace username, database name and table name in the command. Execute it and the utility will spit out the data in the command line as output. Based on the command given above, the easier version of the syntax is as follows.
mysqldump [OPTIONS] [DATABASE_NAME] [TABLE_NAME TABLE_NAME2 ...]
Now, Let’s learn how to perform some most common tasks using mysqldump backup program.
Common backup tasks using mysqldump
In this section of the guide, we will see how to perform some of the common tasks using this program. We will see an example of each of the task along with the syntax that you can follow to perform the similar backup task.
So, Let’s get started with the most basic and simplest task, backing up a single database.
BACKUP A SINGLE DATABASE
In this case, we just want to take a backup of a single MySQL database. And we will store the dump into an SQL file. The data that mysqldump spits out is in the SQL format. It means that we can store the dump in SQL file and later use it to restore our database.
Here is the syntax to dump a single database into a file.
mysqldump -uUSERNAME -p DATABASE > exportFile.sql
For instance, if you want to export a database called test1 into a file called test1Backup.sql, you can execute the following command.
$ mysqldump -uroot -p test1 > test1Backup.sql
Now, Let’s see how we can export all the databases at once using this awesome tool!
MYSQLDUMP ALL DATABASES
It is even easier to export all the databases. In this case, the syntax is an example too. To export all the databases, execute the following command.
$ mysqldump -uUSERNAME -p --all-databases > exportFile.sql
Now, Let’s see how to export specific databases with mysqldump.
MYSQLDUMP MULTIPLE DATABASES
With this tool, you can even export multiple databases in a single file. As the command will simply output the data and we can use the > operator to redirect the output into a file, we can even store the backup of multiple databases in a single file.
This is useful if you have multiple databases for a single application. Here is the syntax to dump multiple databases in a file using mysqldump.
mysqldump -uUSERNAME -p --databases DATABASE1 DATABASE2 ... > exportFile.sql
And here is an example to export databaseName and exampleDatabase into a file backup123.sql.
$ mysqldump -uroot -p --databases databaseName exampleDatabase > backup123.sql
Similarly, you can even export Database schema without any data.
MYSQLDUMP SCHEMA ONLY
Using an option called --no-data, we can easily export database schema without any data in it. It means that we can easily create a backup of the database schema to create a similar database on another server.
The syntax is the same as exporting single or multiple databases, we just have to add --no-data option in the command. For example, if you want to export a database schema of a database called someDatabase, you can execute the following command.
$ mysqldump -uroot -p --no-data someDatabase > databaseSchema.sql
Similarly, we can add an -h option to remotely take a backup of our databases.
TAKE A BACKUP OF REMOTE DATABASE
To take a backup of your database remotely, you just have to add -h option in the command. You can perform all the tasks given in this guide remotely using the -h option. Let’s see a quick example and move on to the other common backup tasks.
$ mysqldump -uroot -p -h123.123.123.123 --databases databaseName exampleDatabase > backup123.sql
This command will take backups of two databases from the host with IP address 123.123.123.123. Now, Let’s see how we can export specific tables from a single database.
MYSQLDUMP SPECIFIC TABLES
Sometimes, you want to export a few tables from a single database to process data. In that case, the syntax as as follows.
mysqldump -uUSERNAME -p DATABASE TABLE1 TABLE2 TABLE3 ... > exportFile.sql
For instance, if you want to export users and transactions table from the database called myDatabase, you can execute the following command.
$ mysqldump -uroot -p myDatabase users transactions > exportFile.sql
Now, Let’s see how we can export the whole database except a few tables.
MYSQLDUMP IGNORE TABLES
Follow this syntax if you want to export the whole database except a few tables. Here is the syntax to export the database except for a few tables.
mysqldump -uUSERNAME -p DATABASE --ignore-table=DATABASE.TABLE --ignore-table=DATABASE.TABLE2 ... > exportFile.sql
For instance, if you want to export a database exampleDatabase except for the users table, execute the following command.
$ mysqldump -uroot -p exampleDatabase --ignore-table=exampleDatabase.users > exportFile.sql
So, this is how you can export the whole database except some tables.
Conclusion: The main thing you have to understand with the mysqldump command is the options. Majority of the tasks are performed because the options are present. The utility is rich because of the features. You can combine multiple options to perform hundreds of different backup tasks. With the combination of options we learned in this guide, you can perform almost any kind of backup task with full confidence. You will find even more options in the man page of the utility. If you want to open the man page of mysqldump, execute the man mysqldump command in your console.