How to backup and restore (import and export) MySQL database or table
This tutorial will show you how to back up and restore (import or export) MySQL / MariaDB database or tables.
Table of contents:
- 1. MySQL – Backup or export databases and tables
- 2. MySQL – Restore or import databases and tables
- 3. Backup and Restore MySQL
- 4. References
1. MySQL – Backup or export databases and tables
1.1 We can use the built-in command mysqldump can be used to backup or export databases and tables in MySQL or MariaDB.
# Backup a database
$ mysqldump -u {username} -p {database} > filename.sql
#Backup a table
$ mysqldump -u {username} -p {database} {table1} > filename.sql
#Backup multiple tables
$ mysqldump -u {username} -p {database} {table1} {table2} > filename.sql
#Backup a database and gzip it
$ mysqldump -u {username} -p {database} | gzip > filename.sql.gz
Note
-u {username}
– Connect to database via user{username}
.-p
– Connect to database via password authentication.{database}
– Name of the database.{table1}
,{table2}
– Table name in the {database}.
1.2 Backup database wordpress
and export it to a file name db.20160726.sql
.
$ mysqldump -u {username} -p wordpress > db.20160726.sql
1.3 Backup database wordpress
and export it to a file name db.20160726.sql.gz
.
$ mysqldump -u {username} -p wordpress | gzip > db.20160726.sql.gz
1.4 Back up a table wp_postmeta
in the wordpress
database and export it to a file name wp_postmeta.sql
.
$ mysqldump -u {username} -p wordpress wp_postmeta > wp_postmeta.sql
# gzip example
$ mysqldump -u {username} -p wordpress wp_postmeta | gzip > wp_postmeta.sql
1.5 Back up multiple tables, wp_postmeta
and wp_posts
, in the database wordpress
and export it to a file name tables.sql
.
$ mysqldump -u {username} -p wordpress wp_postmeta wp_posts > tables.sql
Note
From MariaDB 10.4.6, we can use mariadb-dump. The mariadb-dump
is a symlink to mysqldump
.
2. MySQL – Restore or import databases and tables
2.1 We can use the command mysql
to restore or import data from a backup file.
$ mysql -u {username} -p {database} < filename.sql
# Restore or import from a gzipped file.
$ gunzip -c filename.sql.gz | mysql -u {username} -p {database}
2.2 The below example restore or import data from a file db.20160726.sql
to the database wordpress
.
$ mysql -u {username} -p wordpress < db.20160726.sql
2.3 Restore or import from a gzipped backup file.
$ gunzip -c db.20160726.sql.gz | mysql -u {username} -p wordpress
3. Backup and Restore MySQL
The below steps show how to back up and restore a MySQL database.
# SSH login into a `{server-ip}` as user `mkyong`.
$ ssh mkyong@{server-ip}
# Assume MySQL has user `mkyong` and log in using `unix_socket` authentication.
# Dump the database `wordpress`, gzip it and export it to a file `db1.20160726.sql.gz`
$ mysqldump wordpress | gzip > db1.20160726.sql.gz
# Another example, login to MySQL via user `admin` using password authentication
$ mysqldump -u admin -p wordpress | gzip > db1.20160726.sql.gz
# exit `{server-ip}`
$ exit
# scp copy the backup file from `{server-ip}` to local
$ scp mkyong@{server-ip}:db1.20160726.sql.gz .
# Restore or import it into the local MySQL database "local_wordpress"
$ gunzip -c db1.20160726.sql.gz | mysql -u mkyong local_wordpress