How to Backup and Restore MySQL Database
If you store data in a MySQL database and want to avoid losing it, you should regularly create backups. This article will teach you how to quickly back up and restore MySQL databases via the command line. You’ll also learn how to transfer data to another server.
- How to create a backup via command line (using mysqldump)
- How to back up a MySQL database with compression
- How to restore a MySQL database from a backup
- Backup and restore using Adminer
How to create a backup via command line (using mysqldump)
If you have SSH access to the server, you can quickly create and restore backups. This allows for faster dump creation and restoration than tools like phpMyAdmin or Adminer. A dump is a text version of the database, containing SQL commands to recreate it. Here's the command to create a dump:
$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
[uname] - Database username (often root)
[pass] - Password, written directly after -p if it contains no special characters or spaces, e.g., -proot or -ppassword
[dbname] - Your database name
[backupfile.sql] - Name of the backup file
[--opt] - Additional options for mysqldump, can be omitted
Example: For a database named DrupalDB, with user root and password password, and saving to backup.sql:
$ mysqldump -u root -ppassword DrupalDB > backup.sql
If your password contains spaces or special characters, omit it in the command and enter it when prompted:
$ mysqldump -u root -p DrupalDB > backup.sql
To back up specific tables, list them after the database name, e.g., nodes users:
$ mysqldump -u root -p DrupalDB nodes users > backup.sql
To back up multiple databases:
$ mysqldump -u root -p --databases DrupalDB Drupal7 Drupal8 > backup.sql
To back up all databases:
$ mysqldump -u root -p --all-databases > alldb_backup.sql
Useful mysqldump options:
--add-drop-table – Adds DROP TABLE before each CREATE TABLE statement.
--no-data – Dumps only the schema, not the data (useful for cache tables).
--add-lock – Adds LOCK TABLES and UNLOCK TABLES (may lock tables for a long time on large DBs).
How to back up a MySQL database with compression
Compression can reduce dump size by 10–20 times, making large DB backups manageable. Use gzip for compression:
$ mysqldump -u root -p DrupalDB | gzip -9 > backup.sql.gz
To decompress the file without restoring the DB:
$ gunzip backup.sql.gz
How to restore a MySQL database from a backup
To restore from a dump, you’ll need an empty database. If you used --add-drop-table, existing tables will be dropped automatically. Otherwise, delete them using Adminer or phpMyAdmin. To restore:
$ mysql -u root -p DrupalDB backup.sql
To restore from a compressed backup:
gunzip backup.sql.gz | mysql -u root -p DrupalDB
Backup and restore using Adminer (PhpMyAdmin alternative)
Adminer is a lightweight alternative to PhpMyAdmin, packed into a single file:
Copy the file to your site's root and open it via browser:
http://your_website/adminer-4.2.1.php (you can rename it to adminer.php for convenience)
Log in and select the desired database (e.g., Drupal7).
Click "Export" to dump data — you can exclude unnecessary cache tables:
To import a dump back, go to the "Import" tab:
