Backup your databases with mysqldump

Hello, friends. In this post, we will show you how to use the mysqldump command. This command allows you to back up your MySQL / MariaDB databases rapidly.

What is the mysqldump command

The mysqldump command is a tool included in MySQL / MariaDB that allows us to perform and restore backups of entire databases.

As you can imagine, this tool is basic for almost any situation where we use MySQL / MariaDB. Besides this, it has a very high importance in database management at a professional level.

This utility is already included with the installation of a MySQL / MariaDB server, so we will not have to install anything additional.

Remember that the purpose of this post is not to compare mysqldump with other professional tools, but rather to highlight this command as a basic utility that can do the job.

So let’s go for it.

Backup your databases with mysqldump

This command is used to back up databases. And we will give you, in this post, several examples, so you can get to know the potential of the tool.

If you want to back up a single database, then you have to run.

mysqldump -u [user] -p [database-name] > [file]

For example:

mysqldump -u root -p cars > bak.sql

The above command will backup the cars database and store it in a file named bak.sql. It is not necessary that the user root is in charge of the backup, for that, you can also use a user with permissions on the database.

You can also back up many databases at once. To do this, run.

mysqldump -u [user] -p --databases [database1] [database2] [...] > [file].sql

For example:

mysqldump -u root -p --databases cars students > bak.sql

This command backs up the cars and students databases to the bak.sql file.

Another option is to make mysqldump backup all the databases at once thanks to the --all-databases option

mysqldump -u root -p --all-databases > backup.sql

On the other hand, many users require backing up only one or many tables of a database. The syntax would be:

mysqldump -u root -p [database] [table1] [table2] > [file].sql

For example:

mysqldump -u root -p cars model > bak.sql

This will back up in a bak.sql file the content of the model table of the cars database.

If you want to back up only the structure and not the data, add the option --no-data.

mysqldump -u root -p --no-data cars > file.sql

Or just the data without structure with --no-create-info.

mysqldump -u root -p --no-create-info cars > file.sql

Restore databases with mysqldump

To restore a database from a backup, you have to use this syntax.

mysql -u root -p [database] < [file].sql

For example, to restore the cars database

mysql -u root -p cars < bak.sql

This is how easy it is to use this tool.

Conclusion

In this post, you learned how to use the mysqldump command that allows us to back up our database made with MySQL / MariaDB.

I hope you liked it.