Backing Up and Restoring MySQL Database Using mysqldump

Although not always the most efficient, mysqldump is a handy way to migrate your MySQL server — particularly if you don’t have many schemas and the size of data is small.

To create backup, first list all schemas on the server using

SHOW DATABASES;

I normally avoid backing up performance_schema, information_schema and mysql schema. They contain database configuration, user settings etc. This would mean you have to reconfigure all your settings on the new server

Then take the dump of all schema using this command:

mysqldump -u***** -p***** --databases <space_separated_schema_names> > mybackup_20131107_1223.sql

Then compress, transfer and inflate the sql file into the new server’s host. When you’re ready to restore, just do

mysql -u***** -p***** < mybackup_20131107_1223.sql
Advertisements

One thought on “Backing Up and Restoring MySQL Database Using mysqldump

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s