Back up databases using the mysqldump tool in MySQL

The mysqldump is a tool for making backup of MySQL databases, it can export MySQL database as a sql file.

As a database administrator or maintainer, it is necessary to regularly backup the online MySQL database. It may help you to preserve your data or restore your data in the event of database corruption.

MySQL provides the mysqldump tool for exporting database structures and data from the MySQL database server.

What is mysqldump?

The mysqldump tool is generally installed automatically with the installation of the MySQL database. It can export one or more databases, or one or more tables in the database as a SQL file, including DDL statements and DML statements.

Typically, after installing the MySQL server, you can use the mysqldump tool directly.

If you cannot find the mysqldump tool, configure the bin directory under the MySQL installation directory to the environment variable PATH. Or navigate to the bin directory and use the mysqldump tool.

mysqldump usage

The following command uses mysqldump to make a backup for a single database in MySQL server:

mysqldump --user=<username> --password=<password> --result-file=<path_to_backup_file> --databases <database_name>

here:

  • The <username> is the user account to log in to the MySQL server.
  • The <password> is the password of <username>.
  • The <path_to_backup_file> is the file path of the backup file.
  • The <database_name> is the name of the database you want to back up. If you want to back up multiple databases, separate the names of multiple databases with commas.

The mysqldump tool provides many options to support a variety of exporting requirements. The following are common options for the mysqldump command :

--user
Login Username.
--password
The password for the login username.
--host
The IP address or hostname of the MySQL database server. The default is 127.0.0.1 or localhost.
--port
The port number of the MySQL database server. The default is 3306.
--add-drop-table
Include one DROP TABLE statement for each table DDL.
--add-locks
Include LOCK TABLES and UNLOCK TABLES statements before and after each INSERT statement, respectively. It improves the speed of data recovery from dump files.
--all-databases
Create a dump of all databases on the MySQL server.
--create-options
Include the ENGINE and CHARSET options in the CREATE TABLE statement.
--databases
Customize one or more databases to export.
--disable-keys
Instructs MySQL to disable index updates during data loads for MyISAM tables. MySQL will create indexes after mysqldump has finished loading the data. This option increases recovery speed.
--extended-insert
Combines INSERT statements that insert a single row into one statement that inserts multiple table rows; this option also helps speed up data recovery.
--flush-logs
Flushs server logs before dumping data. This is useful when combined with incremental backups.
--lock-tables
Ensure that the dump is a consistent snapshot by locking all tables in the database during the dump.
--no-data
Generates only the statements needed to recreate the database structure (only CREATE DATABASE, CREATE TABLE…), not the ones that insert data ( INSERT statements).
--opt

The default options used by the mysqldump tool.

The --opt option enables the following options: --add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert, --lock-tables, --quick, and --set-charset.

To disable this option, use --skip-opt. If you want to skip each individual option, use --skip-<option_name>. For example, to skip the disable-keys option, use --skip-disable-keys option.

--quick

Instructs mysqldump not to buffer the table in memory before writing to the file. This option speeds up dumping of large tables.

--result-file
Specifies the path to the output dump file.
--set-charset
Specifies the character set of the database.
--tables
Create a dump of one or more tables.
--where
Dump only rows that satisfy the conditions in the WHERE clause.

mysqldump Examples

Let’s take some examples of using the mysqldump tool to back up a database.

  1. mysqldump Backup all databases using

    mysqldump --user=root --password=password --all_databases --result-file=/bak/all.sql

    Here, the all databases in the MySQL server is backed up to the file /bak/all.sql.

  2. Back up a single database using mysqldumps

    mysqldump --user=root --password=password --databases sakila --result-file=/bak/sakila.sql

    Here, the sakila database backed up to the file /bak/sakila.sql.

  3. Back up multiple databases using mysqldump

    mysqldump --user=root --password=password --databases sakila sqlizdb --result-file=/bak/sakila_sqlizdb.sql

    Here, the sakila and sqlizdb databases are backed up to a single file /bak/sakila_sqlizdb.sql.

  4. Back up the structure of a single database using mysqldump

    mysqldump --user=root --password=password --no-data --databases sakila --result-file=/bak/sakila.sql

    Here, the structure of the sakila database is backed up to a file /bak/sakila.sql.

    Again, if you just need to back up your data, replace --no-data option with --no-create-info option.

Conclusion

The mysqldump is a tool for backing up MySQL databases. It provides many options for different backup needs.