Menu

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.

Updated on

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=backup_user --password --result-file=/backups/app_db.sql --databases app_db

Replace the example account, database, and output path with values from your system. The client prompts for the password before creating the dump.

  • --password prompts you to enter the password for <username>. Do not add the password value to the command line; MySQL documents that method as insecure. See password security guidance.
  • /backups/app_db.sql is the path for the dump file; ensure the directory exists and the current operating-system user can write to it.
  • app_db is the database to back up. To back up multiple named databases, separate their names with spaces after --databases.

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
Prompts for the login password when used without a value. Avoid forms such as --password=secret, which expose the password on the command line.
--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 --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 --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 --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 --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.