Backing Up Your Database With Mysqldump

For most modern websites and web applications, much of the data they use in operation is stored in a database. MySQL or its fork MariaDB is the database most used by the lion’s share of websites. These database programs store the data in special files on the server’s filesystem. Now it may seem logical to copy these files when backing up the server in order to restore your database, but this isn’t the best way. Due to the way in which the database stores data in memory and writes out to disk periodically, you could end up with an inconsistent database at best or a corrupted and useless one at worst.

Mysqldump

For this reason we have mysqldump. This tool takes the contents of the database and saves it as a file. This file will consist of all the SQL statements (database instructions) needed to recreate the database as it was at the time the command was run. This will often result in a file that is considerably larger than the database was previously, but with the advantage that it can be imported into almost any other version of MySQL/MariaDB.
Fortunately, this tool is normally installed as part of the MySQL/MariaDB install on your server, so there’s no need to perform a separate installation in order to use it. Let’s jump straight into looking at a few command examples of how to use it.
“`
mysqldump -u root -p mydatabase
“`
In this example, mysqldump will be used to dump the contents of the database named *mydatabase*. The -u and -p flags work much the same here as they do on the usual MySQL/MariaDB command line client. As such you can switch out the username root for the username you want to use with access to the database. Something you’ll notice right away is that mysqldump just puts its output on the screen. This means that if required you can pipe the output through other commands, but for most of our examples we’ll just redirect the output to a file similar to below:
“`
mysqldump -u root -p mydatabase > mydatabase.sql
“`
So, the above command mirrors the previous one, but writes the output to the file mydatabase.sql in the current working directory. In normal circumstances you’ll likely need to dump more than a single database. To do this, you can use the *–databases* flag and follow it with a list of database names separated by spaces:
“`
mysqldump -u root -p –databases mydatabase mydatabase_2 mydatabase_4 > mydatabases.sql
“`
As you can probably expect, the result of this command will be that the *mydatabases.sql* file will contain the data to rebuild the databases *mydatabase*, *mydatabase_2* and *mydatabase_4*. Fortunately there’s a shorthand if you want to make a dump of all your databases in one go:
“`
mysqldump -u root -p –all-databases > allmydatabases.sql
“`
This then creates the file *allmydatabases.sql* that will contain all the data needed to fully recreate your database.

Importing with MySQL

So once you have your database backed up the next step would be to import it on the computer you want to use it on. This can easily be done with the *mysql* command as that can take input from redirecting the contents of a file in as follows:
“`
mysql -u root -p < mydatabses.sql
“`
This will create and populate the databases with all the data you backed up earlier. There’s one catch though: when you backup a single database it doesn’t include the commands to create the database when reading the file in, so you’d need to manually create the database first in the mysql client before importing the dump file. As the create database command is in the dump file when using the *–databases* flag, you can also use that flag when dumping a single database to get that command included.
So there we have it – a simple guide to backing up and, just as importantly, restoring your databases from MySQL or MariaDB. If you’ve configured any form of automated backups to copy files from your server to another, it’s worth setting a script to perform the mysqldump a little bit prior to this so that you can be sure your database backup is current with your files.