How to do MySQL dump

How to Save Your MySQL Database

MySQL is a database service for storing any type of data. It offers different types of backup options, which can be chosen with data size, hardware specs, or storage capacity in mind.

MySQL provides:

• Logical backups, executed through a .sql file and create/insert statements. Later, you can restore a database from this file. This backup type is considered slower than others, and suitable for more medium-sized databases.
• Physical backups are executed by copying files in the original format. The drawback of this type of backup is that the database can only be restored on a MySQL server.
• Consistent backups, executed through automated regular backup of files.

One can also create a full backup (a copy of the whole database), incremental backup (a repetitive copy of changes made to the database), and differential backup (a copy of the changes made from the latest backup).

The part of the relation database package is mysqldump, which is a command-line utility for MySQL database backups. It can be used to recreate data in a single file containing SQL statements and then can be used to recreate data in its original format. With this command, one can dump tables, several databases, or all of the server’s data for further migration to another server.

Overall, one can easily use a command. However, many users face complications when restoring the database from a backup file using SQL commands. The database recreation process may take a while to complete if you have a large database since mysqldump runs all SQL statements for inserting the data.

Keep in mind, that mysqldump cannot dump certain tables, including temporary tables, system tables, performance schema tables (from the Performance Schema database), and information schema tables (from the Information Schema database). However, the command offers customization options: you can specify to exclude certain types of tables or data from the dump with --ignore-table command or use --no-data option to keep the table structure while getting rid of the data.

What Tasks Does Mysqldump Facilitate?

Mysqldump utility facilitates:

• Database backups;
• Database migration;
• Database versioning (you can create different versions of your database, which you can return to later);
• Environment replication (this command can help replicate the production data to create a stable environment for development or testing);

Structure of Exported Data

Exported data will consist of:

• Compressed files in gz (gzip) or .bz2 (bzip2) formats;
• SQL Statements in the form of SQL script used to recreate the structure and data of the database;
• XML-formatted data for easier integration of data with other systems;
• Tab-delimited text files used for data import and export;
• Comma-separated values or CSV files used for data exchange;
• Custom user-defined formats are used for customizing the output format;

Privileges for Mysqldump Usage

To start using mysqldump command, a minimum of full read access privileges is required. To use the extended functionality, the following privileges are needed:

• SHOW VIEW (for accessing view definitions);
• SELECT (for reading data);
• LOCK TABLES (for table locking);
• RELOAD (for using --flush-privileges option);

Examples of MySQL Dump

As we already mentioned, with mysqldump command you can back up specific tables, a single database, multiple databases, and all databases from the MySQL server. Let’s look at each example.

Backing up specific MySQL tables

To back up specific tables from a MySQL database, run the following command:

mysqldump -u my_user -p database_name table1 table2 > tables.sql

Here:

• my_user — is for your MySQL user name;
• -p — is for the password of MySQL user;
• database_name — is the name of the database containing the necessary tables;
• table1 table2 — is for the names of tables you want to back up;

If you want to back up more tables, you can use the same principle and add the names of the databases you want to additionally back up.

Upon completion, the file “tables.sql” created by mysqldump will contain the SQL commands needed to recreate the tables and the data within them.

Backing up a single database

If you want to back up a single database, you’ll need to use the command below:

mysqldump -u my_user -p database_name > backup.sql

Here:

• my_user — is for your MySQL user name;
• -p — is for the password of MySQL user;
• database_name — is for the name of the database you want to back up;

After you run the command, “backup.sql” file will have the data necessary to restore the database in its original form.

Backing up multiple databases

To back up multiple databases, you need to add --databases flag to the mysqldump command, like so:

mysqldump -u my_user -p --databases db1 db2 db3 > backup.sql

Here:

• my_user — is for your MySQL user name;
• -p — is for the password of MySQL user;
• db1 db2 db3 — is for the names of databases you want to back up;

Backing up all databases from the MySQL server

If you want to back up all databases from a MySQL server, you need to add --all-databases flag when using the mysqldump command:

mysqldump -u my_user -p --all-databases > all_databases.sql

The final all_databases.sql file will contain the necessary SQL commands to recreate the backed-up databases.

How to restore a database on a remote server

To replicate backed-up databases on a new server, we have to first make sure that the mentioned server has a blank database or its outline.

For this, you can check if a server already has a database or create one if it doesn’t:

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

This command is going to create a database on a new server.

Then, you can easily restore tables, or databases from the backups you’ve created. Using a single database as an example, you can use the following command to restore a database from the backup files:

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

Summary

Mysqldump is a versatile utility that can help back up databases in a way that is accessible even to beginners. This one command can pack up the entire database into a single file, which you can then recreate the database from. Mysqldump is flexible enough for a user to be able to choose what part of the database they want to be backed up: certain tables, the whole database, several databases, or all databases on the server. Overall, this tool can come in handy for those, who regularly deal with databases.

FAQ

What is the effect of the --single-transaction flag in mysqldump?

In short, --single-transaction streamlines a database backup through just one transaction. It means that mysqldump can be done with a single transaction, provided that the database remains consistent throughout the backup process.

What methods can be employed to dump large tables effectively?

You could combine --single-transaction and -quick for dumping large databases quicker.

This approach is most suitable for InnoDB tables since it uses less RAM and ensures consistency of the dumping process.

How can tables be ignored during the mysqldump process?

You can ignore certain tables with –-ignore-table option. It can look something like this:

mysqldump -u root -pmypassword my_db –-ignore-table=my_db.table_to_ignore > my_db.sql

So the tables that you tell to be ignored are notified with  –-ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

You can also ignore all tables in a database or the whole database for that matter, you’ll need to repeat the notifying part for it to include all tables:

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

What approach should be taken to dump binary BLOB data with mysqldump?

If a database has binary data, you might face some issues. If you need to dump MySQL database that has binary data, use --hex-blob flag. This option sorts binary strings and puts them into distinguishable format. Here’s how you want to dump binary data:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

Is it possible to utilize the "where" clause with mysqldump?

You can use the “where” clause with mysqldump to make it easier to sort out the data you want to backup from the database. The “where” clause initiates the string of conditions and takes the data compliant with the criteria:

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql