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 to backup 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, 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 to backup all databases.

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

Backup only specific databases

The --databases option allows to backup 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 to backup 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 backup 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 backup 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 to backup a database structure without data. So 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 to backup 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 to backup 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 REPEATABLE READ and START TRANSACTION statement is send 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 work 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 backup 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.

Leave a Comment

Your email address will not be published. Required fields are marked *