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:
Extract a MySQL Dump
Once installed, use mysqldump to get a full backup of a database. 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. 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.
