What Is mysqldump and How Do I Use It?

Export and import database content with relative ease

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 exposed to malicious attacks. If your server is compromised, at best, you need to reinstall your application; at worst, you may lose your data entirely. In addition, you may 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 the server compromise and migration situations. Its 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, reconstruct your database to the precise state it was in when you executed the dump.

Use mysqldump to create exports of a database as 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 database 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 database from MySQL Enterprise is a great way to achieve these backups — if you don't mind the Enterprise price tag.
  • Copying the database'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.

Install the mysqldump Tool

For Windows, check our instructions here for installing MySQL on Windows 7 (the install process is still very much the same for Windows 10). 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

Extract a MySQL Dump

Once installed, 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": 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 into sections. 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.

    Import a MySQL Dump File

    Before you're ready to import the dump file, you will need a database already created and its 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.)

    Learn more about database permissions before you change security roles within your database.

    To re-import your data, log into the MySQL server with the mysql command. Type use [database name] at the prompt, substituting the appropriate name of the DB. 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.

    Importing a mysqldump File into a New Database

    When the process is complete, you'll have a duplicate of your original database. You can verify their similarity by performing another dump then comparing the two outputs. A decent text editor will be able to do this for you, or a dedicated diff tool:

    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!