MySQL Backup and Restore from Command Line

Assumptions

  • You have access to a Unix-like terminal
  • You have the password of MySQL's root user, or another user with access to the database

Backup

Backup a single database

Backup a single database to a plain text file containing the SQL commands to recreate the tables and their data:

mysqldump -u [user] -p [database_name] > [filename].sql

Backup to a gzip-compressed file:

mysqldump -u [user] -p [database_name] | gzip > [filename].sql.gz

Backup to a bzip2-compressed file:

mysqldump -u [user] -p [database_name] | bzip2 > [filename].sql.bz2

Most PHPMyAdmin installs can import .gz and .bz2 files directly, so compressed output is usually the better choice.

Use --single-transaction for InnoDB tables

If your database uses InnoDB (the default engine since MySQL 5.5), always add --single-transaction:

mysqldump -u [user] -p --single-transaction [database_name] > [filename].sql

Without this flag, mysqldump locks the tables during the dump, which blocks writes and can produce an inconsistent snapshot if other transactions are in flight. --single-transaction issues a BEGIN statement before dumping, so the snapshot is consistent without any locks. It has no effect on MyISAM tables.

Backup more than one database

mysqldump -u [user] -p --databases [database_1] [database_2] [database_n] > [filename].sql

Backup all databases

mysqldump -u [user] -p --all-databases > [filename].sql

This dumps every database to a single file — useful for migrating an entire MySQL server.

Include stored routines and events

By default mysqldump does not export stored procedures, functions, or scheduled events. Add these flags for a complete backup:

mysqldump -u [user] -p --single-transaction --routines --events [database_name] > [filename].sql

Restore

Create the target database first:

mysql -u [user] -p

At the mysql> prompt:

CREATE DATABASE [database_name];

Optionally, create a dedicated user for it (recommended):

CREATE USER '[new_user]'@'[hostname]' IDENTIFIED BY '[password]';
GRANT ALL PRIVILEGES ON [database_name].* TO '[new_user]'@'[hostname]';
FLUSH PRIVILEGES;
EXIT;

MySQL 5.x note: older syntax GRANT ... IDENTIFIED BY works on MySQL 5.x but is deprecated in MySQL 8 and removed in later versions. Use the two-step CREATE USER + GRANT shown above on MySQL 8+.

Restore from a dump file

mysql -u [new_user] -p [database_name] < [filename].sql

If the file is compressed, decompress it first:

gunzip [filename].sql.gz
bunzip2 [filename].sql.bz2

Or pipe it directly without creating an intermediate file:

gunzip -c [filename].sql.gz | mysql -u [new_user] -p [database_name]

MariaDB

On MariaDB 10.4+ the preferred command is mariadb-dump (a drop-in replacement for mysqldump):

mariadb-dump -u [user] -p --single-transaction [database_name] > [filename].sql

mysqldump still works as an alias, but mariadb-dump is the canonical name going forward.

Last updated on: June 25, 2026