What is mysqldump and How Do I Use It?

Screenshot of the contents of mysqldump's output.

As one of the leading freely-available databases, MySQL is a popular choice for all sorts of web applications. Being exposed to the Internet, your app is of course exposed to malicious attacks. If your server is compromised, at best you'll need to re-install your application; at worst, you may lose your data entirely. You'll also eventually be in a situation where you need to migrate a database from one server to another.

What Is mysqldump Used For?

The mysqldump tool has you covered for both of these situations. It's basic function is to take a MySQL database and "dump" it out as a text file. But not just any text file... the file is a set of SQL statements. These statements, when executed, will re-construct your database to the precise state it was in when you executed the dump.

So you can use mysqldump to take exports of a database. These can be for the purpose of backups, or because you're moving the database to a new host. In either case, you'll at some point import the text file back into a MySQL database server. It will execute all the SQL statements in the file, which rebuilds your DB to its original state. This part doesn't actually use the mysqldump command, but it wouldn't be possible without this utility either!

The MySQL docs list other methods of making backups, but these all have their own drawbacks:

  • Hotcopying a DB from MySQL Enterprise is a great way to achieve these backups... if you don't mind the Enterprise price tag.
  • Copying the DB's data directories can be tricky if you're moving across operating systems, as their destinations will be different.
  • Exporting to a delimited text file will give you the content, but you'll have to recreate the structure yourself. And you'd better get it just right...
  • You can often backup databases from GUI programs like MySQL Workbench. But this is a manual process, i.e. not something you can script or include in a batch job.

Installing the mysqldump Tool

If you're interested in this article, chances are you already have MySQL installed. If that's the case, good news—you already have mysqldump! If not, you can simply install MySQL in the standard way for your operating system.

For Windows, check our instructions here for installing MySQL on Windows 7 (the install process is still very much the same). Likewise on macOS, see our directions to install MySQL on macOS 10.7 (again, older but still applicable). Users of Ubuntu-based Linux systems can use the following command to install the MySQL client and utilities:

sudo apt install mysql-client

Extracting a MySQL Dump

Once installed, you can use mysqldump to get a full backup of your database.

mysqldump -h [your DB host's name or IP] -u [the DB user's name] -p [the database name] > db_backup.sql

Let's break this command down a little:

  • "-h": This flag is the database host. It can be a full host name (e.g. myhost.domain.com) or an IP address. You can also leave this blank if you're running it on the same host as the MySQL server.
  • "-u": As mentioned, this is your username.
  • "-p": If you're properly secured your MySQL installation, you'll need a password to connect. This flag with no argument will prompt you for a password when you execute the command. Sometimes it's useful to provide your password directly as the argument to this flag, for example in a backup script. But at the prompt, you shouldn't, because if someone gained access to your computer, they could get this password in your command history.
  • "> db_backup.sql": This last part is telling mysqldump to direct all its output into a file. Normally, the command would output everything directly to the console, meaning you'd see a bunch of SQL statements fly by. But instead, the *>* symbol is a sign to funnel all those things into the named text file instead. And if this file doesn't exist, it'll be created automatically.

    When it's finished, you'll have a .SQL file. This is just a text file containing SQL statements. You can open it up in any text editor to inspect the contents. Looking at an export from a WordPress database, you can see how these files are put together.

    Contents of a mysqldump File

    The file is divided up into sections. In the above image, we can see the first section sets up the table for WordPress comments. The second section then recreates the content (i.e. comment records) that was in those tables. When you go to re-import your MySQL dump again, the command will work through the file, executing the statements and re-building your database just the way it was. So how do you do that? Let's take a look.

    Importing a MySQL Dump File

    Before you're able to do this, you will need a database already created, and have a valid username and password. You should also have all the permissions for the database. (Strictly speaking you don't need the GRANT permission, but it's easier to just grant them all.) You can find details on database permissions in this article.

    To re-import your data, follow these steps:

    1. Log into the MySQL server with the mysql command.
    2. Type use [database name] at the prompt, substituting the appropriate name of the DB.
    3. Enter source [filename], substituting the name of the dump file you took previously.

    When you're finished, you'll see a long list of messages noting that SQL statements are executing. Keep an eye out for errors, but if you have the right permissions, you should be fine as it's a blank database.

    Importing a mysqldump File into a New Database

    Once the process is complete, you'll have a duplicate of your original database. You can verify this by doing another dump, and comparing the two outputs. A decent text editor will be able to do this for you, or a dedicated *diff* tool like the one shown in the below image:

    Comparing the Two Databases' Dump Files

    There are only two differences between these files, as represented by red lines at the very top and bottom of the right scrollbar. The first is the line that contains the database name, and we expect this to be different because we named them differently. The second, all the way at the end, is the timestamp for the dump file. And we also expect this to be different, as we recreated the second database after the first. Otherwise, the files are exactly the same, meaning the databases that generated them are as well!