Wednesday, August 25, 2010

BACKup and REStore of MySQL Databases

Here is simple process which I follow to finish the task/work much easier way -

If you have to re-build your database from scratch, you can easily restore the mysqldump file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch.

Here's how you would restore your custback.sql file to the Customers database.
mysql -u XXXX -p XXXX Customers < custback.sql

Easy isn't it ? Here's the general format you would follow:
mysql -u [username] -p [password] [database_to_restore] < [backupfile]

Now how about those zipped files? You can restore your zipped backup files by first uncompressing its contents and then sending it to mysql.
gunzip < custback.sql.sql.gz | mysql -u sadmin -p pass21 Customers

You can also combine two or more backup files to restore at the same time, using the cat command. Here's how you can do that.
cat backup1.sql backup.sql | mysql -u sadmin -p pass21

Moving Data Directly Between Databases
How would you like to replicate your present database to a new location? When you are shifting web hosts or database servers, you can directly copy data to the new database without having to create a database backup on your machine and restoring the same on the new server. mysql allows you to connect to a remote database server to run sql commands. Using this feature, we can pipe the output from mysqldump and ask mysql to connect to the remote database server to populate the new database. Let's say we want to recreate the Customers database on a new database server located at, we can run the following set of commands to replicate the present database at the new server.

mysqldump -u sadmin -p pass21 Customers | mysql --host=hostname -C Customers


No comments: