If you have ever wondered how to back up a MySQL database running on a remote server, you have probably came across mysqldump. We will try to dig deeper into its functionality in this post.

What is mysqldump?

mysqldump is a client utility used to perform logical backups and it is one of the first things you will come across when backing up MySQL databases. The utility is usually utilized as follows:

$ mysqldump -u root -p database_name > backup_name.sql

The above command would take a backup of a database called “database_name” and back its data into a file called “backup_name.sql”. However, the above command is just a basic example of data being exported using mysqldump – that’s just one of many usage scenarios.

Exporting Data with mysqldump

In general, there are three ways to use mysqldump. You can:

  1. Dump a set of one or more tables;
  2. Dump a set of one or more databases;
  3. Dump the entire MySQL server.

In order to dump a set of one or more tables, invoke mysqldump like so (here root is your user, the database_name is your database name, the table_name is the table that you want to back up and the backup.sql is the name of your backup file):

$ mysqldump -u root -p database_name table_name > backup.sql

In order to dump a set of one or more databases, specify the –databases parameter like so:

$ mysqldump -u root -p --databases db_name > backup.sql

In order to dump an entire MySQL server:

$ mysqldump -u root -p --all-databases > backup.sql

mysqldump Options

mysqldump also supports quite a lot of options. Some of them are:

  1. The --force option, which allows the dump process to continue even if SQL errors are encountered;
  2. The --ignore-table option lets you exclude certain tables from being backed up;
  3. The --insert-ignore option forces the tool to write INSERT IGNORE statements instead of writing INSERT statements – rows with invalid values that cause the error will simply be ignored;
  4. The --no-create-db option suppresses the CREATE DATABASE statements if options that dump databases are specified;
  5. The --no-data option does not dump table contents.

The full list of available options can be seen at the documentation page.

mysqldump also has options for formatting. For example, column values can be separated by a specified string when the –fields-terminated-by=string option is provided. Lines can also be terminated by specifying –lines-terminated-by=string. The full list of formatting options can be seen at the documentation page.

The contents of the File

By default, mysqldump produces a .sql file with the contents of the backup. Some of its contents include:

  • The MySQL server host, database name, and the server version;
  • Some variable assignments wrapped within conditional comments;
  • The contents of the backup (databases, the table structure, etc.);
  • The timestamp of the backup (i.e the dump completion date).

The file requires no external modification – it can be uploaded into the server.

Restoring a Backup

To restore a backup taken with mysqldump, run the following:

$ mysql -u root -p database_name < dump.sql

Here root is your username and the database_name is the database name – you may also need to add a file path if the file is not in the current directory. You can also import the file through another tool like phpMyAdmin by going to the “Import” section.

That’s it! Your backup should now be restored.

Summary

The mysqldump client utility can be used to quickly take logical backups – it is most frequently used when the need to transfer data to another server arises. The utility comes with quite a few options which can be used to alter the backup contents and it produces a set of SQL statements that are executed when data is being restored.

Contributor
Comments to: mysqldump

Your email address will not be published. Required fields are marked *

Attach images - Only PNG, JPG, JPEG and GIF are supported.