As one of the leading freely available databases, MySQL is a popular choice for many 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 the application; at worst, you may lose your data. In addition, you may get 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 any text file; the file is a set of SQL statements. These statements, when executed, reconstruct the database to the precise state it was in when the dump was executed.

Use mysqldump to create exports of a database as backups, or when moving the database to a new host. In either case, the text file will be imported back into a MySQL database server. It will execute all the SQL statements in the file, which rebuilds the database to its original state. This part doesn’t use the mysqldump command, but it wouldn’t be possible without this utility either.

The MySQL docs list other methods to make backups, but these have 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 data directories can be tricky when moving across operating systems, as the destinations will be different.Exporting to a delimited text file will give you the content, but you’ll have to recreate the structure.You can often backup databases from GUI programs like MySQL Workbench. But this is a manual process; not something you can script or include in a batch job.

Install the mysqldump Tool

For Windows, check our instructions to install MySQL on Windows 7 (the install process is the same for Windows 10). 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 a database.

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

Here’s a description of the flags used in this command:

  • -h: This flag is the database host. It can be a full hostname (for example, myhost.domain.com) or an IP address. Leave this blank if you run the command on the same host as the MySQL server.-u: Your username.-p: If you properly secured the MySQL installation, you’ll need a password to connect. This flag with no argument prompts you for a password when you execute the command. Sometimes it’s useful to provide the 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 the command history.> db_backup.sql: This part tells mysqldump to direct its output to a file. Normally, the command outputs everything to the console, meaning you’ll see several SQL statements on the screen. The > symbol funnels the output into the named text file. If this file doesn’t exist, it’s created automatically.

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

The file is divided into sections. The first section sets up the table for WordPress comments. The second section recreates the content in those tables (in this example, the comment records). When you re-import the MySQL dump, the command works through the file, executes the statements, and re-builds the database the way it was.

Import a MySQL Dump File

Before you import the dump file, you’ll need a database already created and its valid username and password. You should also have all the permissions for the database. You don’t need the GRANT permission, but it’s easier to grant them all.

To re-import your data, log into the MySQL server with the mysql command. Type use [database name] at the prompt, and substitute the name of the database. Enter source [filename], and substitute the name of the dump file you took previously.

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

When you’re finished, a list of messages appears noting that SQL statements are executing. Keep an eye out for errors, but if you have the right permissions, you should be fine.

When the process is complete, you’ll have a duplicate of the original database. To verify the similarity between the databases, perform another dump then compare the two outputs. Use a text editor or a dedicated diff tool to compare the two files.

There are two differences between these files, as represented by red lines at the top and bottom of the right scrollbar. The first is the line that contains the database name, and this is different because the files were named differently. The second is the timestamp for the dump file. This is different because the second database was recreated after the first. Otherwise, the files are exactly the same, meaning the databases that generated them are as well.

  • How do you fix the mysqldump error: Access denied when using lock tables?
  • Ask your database administrator to grant you the LOCK privilege. If this does not resolve the issue, try running the same mysqldump command adding the –single-transaction flag, such as [$ mysqldump –single-transaction] [-u user] [-p DBNAME ] > backup.sql.
  • Can you use a “where” clause with mysqldump?
  • Use a WHERE clause when creating a backup that only includes the rows fulfilling the given condition. For example, to dump data only from rows with the id column greater than 100, enter “mysqldump my_db_name my_table_name –where=“id > 100” > my_backup.sql”.

Ask your database administrator to grant you the LOCK privilege. If this does not resolve the issue, try running the same mysqldump command adding the –single-transaction flag, such as [$ mysqldump –single-transaction] [-u user] [-p DBNAME ] > backup.sql.

Use a WHERE clause when creating a backup that only includes the rows fulfilling the given condition. For example, to dump data only from rows with the id column greater than 100, enter “mysqldump my_db_name my_table_name –where=“id > 100” > my_backup.sql”.

Get the Latest Tech News Delivered Every Day