Making a Mysql database backup with Mysqldump

Mysql and MariaDB

A database is an elementary apart in the management of information within a company and is a vital component in information systems. Therefore, it is necessary to carry out operations that guarantee its optimal functioning. However, many times problems can occur that affect these data, so it is important to make constant backups of the database. If you use MySQL you will be able to do it without problems with mysqldump.

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfers to another SQL server.

Basic use of Mysqldump

The tool is a simple tool in its use but powerful for the possibilities and flexibility it offers you. Making a backup with mysqldump is really easy. I will show you:

  • Back up a single database:

This is the most basic form of use that mysqldump has. Thanks to this, I will backup a single database. Open a terminal and Run:

:~# mysqldump -u root -p database > output.sql
1.-Back up a single database
1.-Back up a single database with mysqldump

Please replace the name of the database with yours and output.sql with the name you want.

  • Make a backup of all databases:

But in some cases, I need to perform a Backup of all databases. To do this, run:

:~# mysqldump -u root -p --all-databases > output.sql
2.- Making a backup of all databases
2.- Making a full backup with mysqldump

Don’t forget to replace output with the name you want for your backup.

  • Back up multiple databases

And at other times, I need to perform backups of certain databases.

:~# mysqldump -u root -p --databases database1 database2 > output.sql
3.- Makiing a backup with mysqldump of some databases
3.- Making a backup with mysqldump of some databases

Again, I remind you to replace the data.

Common mysqldump options

There are many options to modify the execution of mysqldump, however, you can access them through the terminal

:~# mysqldump --help
4.- Showing mysqldump help
4.- Showing mysqldump help

Here, I’ll show them to you with a brief description:

–compatible=nameChange the dump to be compatible with a given mode. By default tables are dumped in a format optimized for MySQL

mysqldump options
Option Description
-A, –all-databases Dump all the databases
-Y, –all-tablespaces Dump all the tablespaces
-y, –no-tablespaces Do not dump any tablespace information
–add-drop-database Add a DROP DATABASE before each create.
–add-drop-table Add a DROP TABLE before each create.
-c, –complete-insert Use complete insert statements
-C, –compress Use compression in server/client protocol
-B, –databases Dump several databases
-E, –events Dump events
-F, –flush-logs Flush logs file in server before starting dump
–flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql database
-f, –force Continue even if we get an SQL error
-h, –host=name Connect to host
–ignore-table-name Do not dump the specified table
-p, –password[=name] Password to use when connecting to server
-P, –port=# Port number to use for connection
–ssl Enable SSL for connection
-u, –user=name User for login if not current user
-v, –verbose Print info about the various stages.


Of course, there are other options, but their use is less frequent.

As I said, mysqldump is a wonder and also allows you to compress the backup using gzip.

:~#  mysqldump -u root -p --all-databases | gzip > backup.sql.gz
5.- A backup with mysqldump using gzip
5.- A backup with mysqldump using gzip

Or, connect to a host:

:~# mysqldump -h IP -u root -p --routines --all-databases  > output.sql
6.- Connecting to a host to making a backup
6.- Connecting to a host to making a backup

Restore a backup

If the backup contains only one database and does not contain ‘drop database’ and ‘create database’ statements, then the database where the restoration should be performed can be indicated:

:~# mysql -u root -p  database < backup.sql
7.- Restoring a backup
7.- Restoring a backup

Or, if the backup is total, you can restore it as follows:

:~# mysql -u root -p < backup.sql
8.- Restoring a full backup
8.- Restoring a full backup

As you can see making a backup with mysqldump is really simple but at the same time is very important so I recommend you to further examine the options for you to get the most out of this tool.

We want to know your opinion, have you used mysqldump? What is your favorite option?

Please spread this article through your social networks


Please enter your comment!
Please enter your name here