Backup MySQL Database using mysqldump

If you don't want to lose data, you need to make regular database backups. The mysqldump client is a command-line tool which allows backing up a MySQL database or a collection of databases.

The general syntax is:

mysqldump -u {username} -p {database_name} > {filename}

• --user={username}, -u {username} - the account username which will be used for connecting to the MySQL server.
• --password[=password], -p[password] - the account password which will be used for connecting to the MySQL server. The password value is optional. If not provided, the tool will prompt for one. If provided, there must be no space between --password= or -p option and the password.
• {database_name} - the name of database.
• {filename} - the filename of database backup.

Backup all databases

The --all-databases option allows backing up all databases.

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

Backup only specific databases

The --databases option allows backing up only specific databases by providing the names of them to command-line.

mysqldump -u root -p --databases market company > backup.sql

Backup a single database

The --databases option allows backing up a single database by providing the database name to command-line.

mysqldump -u root -p --databases market > backup.sql

The --databases option can be omitted if wanted to back up a single database.

mysqldump -u root -p market > backup.sql

The difference between these two commands is that, without --databases option, backup doesn't have CREATE DATABASE and USE statements.

Backup only specific tables from a database

Provide database name and table names to command-line without --databases option to back up only specific tables from a database.

mysqldump -u root -p market products manufacturers > backup.sql

Backup a database structure without data

The --no-data option allows backing up a database structure without data. So, the backup file contains only statements to create the tables.

mysqldump -u root -p --no-data market > backup.sql

Backup a database data without structure

The --no-create-info option allows backing up a database data without structure. So backup file contains only tables data.

mysqldump -u root -p --no-create-info market > backup.sql

Backup a database without locking

By default, all tables will be locked until the backup is complete. The --single-transaction option allows backing up database without locking tables.

mysqldump -u root -p --single-transaction market > backup.sql

When this option is used, the transaction isolation mode is set to the REPEATABLE READ and START TRANSACTION statement is sent to the server before starting database backup.

To ensure successful backup, other connections cannot use these statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE.

The --single-transaction option only works with transactional tables, like InnoDB. This option will not work with MyISAM and MEMORY tables. These tables are not transactional, so state can be changed during the backup.

Backup a large database

It is useful to combine the --single-transaction option with the --quick option when need to back up a large database.

mysqldump -u root -p --single-transaction --quick market > backup.sql

When the --quick option is used, data is retrieved by one row at a time, rather than retrieving the entire row set and buffering it in memory.