Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Backup and Restore a MySQL Database or Table

DZone's Guide to

Backup and Restore a MySQL Database or Table

Here's a quick tutorial to get your MySQL data backed up and restored. Better safe than sorry.

· Database Zone ·
Free Resource

RavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.  

In this tutorial, we will show you how to use backup and restore a MySQL database or tables.

Backup Examples

1.1 Quick reference – mysqldump


#Backup a database
$ mysqldump -u [username] -p [dbname] > filename.sql

#Backup a table
$ mysqldump -u [username] -p [dbname] [table] > filename.sql

#Backup multiple tables
$ mysqldump -u [username] -p [dbname] [table1] [table2] > filename.sql

#Backup a database and gzip it
$ mysqldump -u [username] -p [dbname] | gzip > filename.sql.gz


1.2 Backup a database ‘wrdp1’ to a SQL file ‘db.20160726.sql’.


$ mysqldump -u mkyong -p wrdp1 > db.20160726.sql
Enter password:


1.3 Backup and gzip together.


$ mysqldump -u mkyong -p wrdp1 | gzip > db.20160726.sql.gz
Enter password:


1.4 Back a table ‘wp_postmeta’ to a SQL file ‘wp_postmeta.sql’


$ mysqldump -u mkyong -p wrdp1 wp_postmeta > wp_postmeta.sql
Enter password:

# backup + gzip
$ mysqldump -u mkyong -p wrdp1 wp_postmeta | gzip > wp_postmeta.sql

#multiple tables - wp_postmeta and wp_posts
$ mysqldump -u mkyong -p wrdp1 wp_postmeta wp_posts > tables.sql

Restore Examples

2.1 Quick reference – mysql


#Restore, uses 'mysql' command for backup database and table  
$ mysql -u [username] -p [dbname] < filename.sql

#Restore back a gzip SQL file.
$ gunzip -c filename.sql.gz | mysql -u [username] -p [dbname]


Note: If you are restoring a SQL file, please use command mysql NOT mysqldump

2.2 Restore back a SQL file 'db.20160726.sql' to a database 'wrdp1'.


$ mysql -u mkyong -p wrdp1 < db.20160726.sql
Enter password:

#table, same command
$ mysql -u mkyong -p wrdp1 < wp_postmeta.sql


2.3 Restore back a gzip SQL file 'db.20160726.sql.gz' to a database 'wrdp1'.


$ gunzip -c db.20160726.sql.gz | mysql -u mkyong -p wrdp1
Enter password:


Case Study

Here is my MySQL backup and restore steps, just for self-reference.

  1. SSH into a server 'myserver1'.
  2. Backup and gzip a database 'db1'.
  3. Download the backup file from 'myserver1' via SCP command.
  4. Restore the backup file to a local database 'local_db1'.

# local environment
$ ssh myserver1

# server environment
$ mysqldump -u mkyong -p db1 | gzip > db1.20160726.sql.gz

# server environment
$ exit

# local environment
$ scp mkyong@myserver1:db1.20160726.sql.gz .

# local environment
$ $ gunzip -c db1.20160726.sql.gz | mysql -u mkyong -p local_db1


Done!

Note: Please use this command for more examples: mysqldump --help

Do you pay to use your database? What if your database paid you? Learn more with RavenDB.

Topics:
reference ,backup ,database ,tables ,mysql database ,mysql ,steps

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}