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.
Here’s the (in)famous “command-line one-liner” to dumpb all the databases contained in your MySQL or MariaDB using mysqldump:
mysqldump -u db_root_user -p -x -A > /bak/db_backup.sql
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:
mysqldump -user=db_root_user --password --lock-tables mysqldump -u db_root_user -p -x -A > /bak/db_backup.sql > /bak/db_backup.sql
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).
Here’s the mysqldump one-liner to export only one specific database:
mysqldump --user=db_root_user --password --lock-tables --databases db_name > /bak/db_backup.sql
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 we want to backup. To export multiple databases, you can enter multiple names, separating them with a single space.
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:
mysqldump –user=db_root_user –password –lock-tables db_name table_name > /bak/db_backup.sql
As you can see, we’re not using the –databases option 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.
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).