Backing up and Restoring Databases

Edit on GitHub
Feb 20, 2015
Last updated: Mar 6, 2019
Databases
2 minutes

Export

1HOST=""
2USER=""
3BACKUP_FILENAME="mysqlbackup"

export a single database

1# export a single database
2# mysqldump -u [username] -p [database] > FILENAME.sql
3mysqldump --host="host" --user="USERNAME" --password --port=3306 "db_name" > FILENAME.sql

export multiple databases

1# export multiple databases
2mysqldump -u USERNAME -p -–databases db1 db2 db3 | gzip > FILENAME.sql.gz

export all databases

1# export all databases
2# mysqldump -u [username] -p --all-databases > FILENAME.sql
3mysqldump -u USERNAME -p --all-databases > FILENAME.sql
4
5# compressed export
6# mysqldump -u [username] -p --all-databases > FILENAME.sql
7mysqldump -u USERNAME -p -–all-databases | gzip > FILENAME.sql.gz

Compressed database exports ( | gzip)

1mysqldump -u USERNAME -p -–all-databases | gzip > FILENAME.sql.gz

1# export to a remote server
2mysqldump --host servername dbname > FILENAME.sql

1# import database at remote server
2mysqldump --host 192.168.1.15 -P 3306 -u USERNAME -p DB_NAME > FILENAME.sql

backup without locking the tables

1# backup without locking the tables
2mysqldump -u USERNAME -p --single-transaction --quick --lock-tables=false -h HOST_IP DB_NAME > backup.sql

Copy backup file from one server to another

scp FILENAME USER@HOST:PATH_TO_SAVE_AT

Restore

1# restore a single database
2mysql -u USERNAME -p DB_NAME < FILENAME.sql
3
4# restore all databases
5mysql -u USERNAME -p < FILENAME.sql
6
7# restore a gzip compressed backup
8gzip < [FILENAME.sql.gz] | mysql -u [USERNAME] -p [DB_NAME]

Sample Command for creating a backup and restoring at remote server in one command:

1mysqldump --user=USERNAME --password=password --host=HOST DB_NAME | mysql --host=REMOTE_HOST --user=REMOTE_USERNAME --password=password REMOTE_DB_NAME

Rename

You can use the exported backup file to rename that database. What is the database called and what database to use is defined in the first two lines.

1CREATE DATABASE IF NOT EXISTS `database_wp`;
2USE `database_wp`;

Import

1mysql --host="host" --user="username" --password --port=3306 --database=db_name < "path/to/backup/file.sql"

Notes