Backup MySQL or MariaDB Server with a single Command-Line using mysqldump

If you like playing with MySQL or MariaDB servers, you most likely know that both of these great open-source DBMS come with mysqldump, a neat command-line utility that can be used to perform backup copies (or "dumps", as its name implies) of their data. There are many things that make mysqldump great, such as:

• It's completely free, so it won't cost you a penny. It's also already bundled with any MySQL or MariaDB installation, therefore you don't even have to download and install it... you already have it.
• It's blazingly fast: with a decent SSD, it will backup many GB of data in a matter of seconds.
• It's super-easy to use. And with "super-easy", I mean that you will be able to perform a full backup with a single command-line - as long as you've got the permissions to do that.
• It doesn't require you to shutdown your database down, even if it's strongly advisable to lock the database before performing the backup to avoid potential data loss (more on that later).

How does it work

The mysqldump tool works in a very straightforward way: it retrieves the data and schema from each database and table and "dumps" everything into a single, gigantic, SQL-formatted text file. Such file will basically contain all the SQL statements necessary to reconstruct the databases and data from scratch: if you open a dump file generated by mysqldump, you will see the various CREATE TABLE statements, followed by a (arguably huge) number of INSERT statements, one for each row of data.

Complete Backup

Here's the (in)famous "command-line one-liner" to dump all the databases contained in your MySQL or MariaDB using mysqldump:

Here's a brief explanation of the options we're using in the above line:

• The -u option is required to specify the user account to access the database server: be sure to replace db_root_user with a user having sufficient permissions rights to your DB instance: you can also use root, but I wouldn't recommend that - especially if you'll want to script+schedule that command using a scheduled task (windows) or a cronjob (linux).
• The -p option, without anything else, will bring up the password prompt, meaning that - as soon as you'll hit ENTER - you'll be prompted for a password, which you'll have to manually type. You can also specify a password there (using -p your_password instead of -p), which can be very useful for scheduled tasks/cronjobs, yet will make the whole one-liner a lot less secure (that's why you shouldn't use root).
• The -x option has the SQL server lock all of the tables before performing the backup: the lock won't be released until the process is finished. This is strongly suggested, because you don't want the data to change while you're performing the backup. It's worth noting that this will cause a downtime, so be wary of using that if the database is connected to production services!
• The -e option will bundle INSERT statements together for each table: this will cause the dump file to be smaller and allow any possible future restores to be executed faster.
• The -A option (notice the uppercase) specifies that all databases are to be exported.
• Last but not least, the > (greater-than) sign will redirect the standard output (STDOUT) to the path and file named after it.

If you're curious about what these switches actually mean, here's the long version of the above one-liner:

In my opinion, this latter syntax - although longer - is easier to follow and to remember. Notice that, if you want to specify a password with such extended syntax, you'll have to put the equal sign (--password=your_password).

Single-Database Backup

Here's the mysqldump one-liner to export only one specific database (or a list of databases):

The only significant difference here is that we switched out the --all-databases (-A) option and replaced it with the --databases (-B) option, followed by the database name(s) we want to backup. To export multiple databases, you can enter multiple names, separating them with a single space.

Dumping Tables

For very large databases, you may want to backup the data based on tables rather than the whole database. This can be done with an one-liner as well:

As you can see, we're not using the --all-databases (-A) and/or--databases (-B) options here: with such syntax, mysqldump will just assume that the first name given is a database and the second name is a table. Again, to backup multiple tables, just list them after the database name separating them by a single space.

Restoring the Backup

After the mysqldump task is done, we'll end up with a db_backup.sql file containing the backup of our database(s).

To recover it, we need to perform the following tasks:

• Copy the db_backup.sql file from origin server to the destination server.
• Open a command prompt (or a terminal window) on the destination server and type the following command:

If the db_backup.sql file contains multiple databases and we only want to recover one of them, we must modify the above command adding the --one-database option in the following way:

It goes without saying that the above mysql command requires MySQL or MariaDB service - with their Command-Line interface (CLI) - to be installed on the destination server.

Conclusion

This more or less concludes our brief analysis of mysqldump, which is clearly among the best options you can use to perform quick backups of single database and also of your whole DBMS Service.  In case you need additional info about it, you can check out the MySQL official documentation. Also, if you're using Windows, you could take a look at SQLyog, a great MySQL/MariaDB GUI which I reviewed few months ago in this article (DISCLAIMER: they didn't pay me or this blog to review it - it's just that I genuinely like it a lot).

Print Friendly & PDF Download