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.
In general, there are three ways to use mysqldump. You can:
- Dump a set of one or more tables;
- Dump a set of one or more databases;
- 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 also supports quite a lot of options. Some of them are:
--forceoption, which allows the dump process to continue even if SQL errors are encountered;
--ignore-table option lets you exclude certain tables from being backed up;
--insert-ignoreoption 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;
--no-create-dboption suppresses the CREATE DATABASE statements if options that dump databases are specified;
--no-dataoption 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.
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.