Main Tutorials

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

1.1 We can use the built-in command mysqldump can be used to backup or export databases and tables in MySQL or MariaDB.

Terminal

# 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.

Terminal

$ mysqldump -u {username} -p wordpress > db.20160726.sql

1.3 Backup database wordpress and export it to a file name db.20160726.sql.gz.

Terminal

$ 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.

Terminal

$ 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.

Terminal

$ 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.

Terminal

$ 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.

Terminal

$ mysql -u {username} -p wordpress < db.20160726.sql

2.3 Restore or import from a gzipped backup file.

Terminal

$ 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.

Terminal

# 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

4. References

About Author

author image
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments