Thursday, December 14, 2006

Backup-ing and restor(e)-ing My SQL databases

I really hope this post is preserved in time because i feel like a moron having to read up how to restore databases each time and its very easy except maybe if you get in a tight corner. Here we assume we have to backup and restore a database with username@localhost account. The problem with such an account is that it will not have database creation priviliges in many hosts so you will basically kill yourself trying to figure out how to go about this. I assume that your host has disabled the backup facility of Cpanel or you dont have Cpanel (in which case :: GET A LIFE !!! or maybe a good host)

Part 1. Backup Your Database in PHP My Admin

Login to your control panel , click on PHP My Admin , if this option is not there and an option My SQL databases is there , open it instead and then click on PHP My Admin.



Click on Export , you will be able to select one or all of your databases , i leave the other options untouched , do the databases one by one , you will know why later on.



You will be treated to a file with SQL queries , copy the queries and save it in a txt notepad file or anything which doesnt spoil the commands.

Part 2. Restore Your Database in PHP My Admin


Login to your control panel , click on PHP My Admin , if this option is not there and an option My SQL databases is there , open it instead and then click on PHP My Admin.

Rewind !!!! , this wont work , if you see your SQL commands carefully , they have a create database command , however your account , username@localhost has no database creation priviliges , this is where you will be pulling your hair out like i was , you may create as many users as you like but you wont be able to login with them and further blah blah blah , breaking 3-4 cups later when you regain your senses we continue. Some of you may have already realised the workaround.

Why had we created the backup database wise , there was a reason. We first create the database through the my SQL databases section , then we remove the database creation command from the query backup and also the database 'USE' command.
Now going into the PHP My admin , we select the database from the sidebar , click on the SQL button and paste our queries and execute them.

We live happily ever after , although this tutorial might appear non-sensical to you but to a person actually facing the problems i have outlined , rest assured this page is a gold mine of information.

1 comment:

Vinod Kumar Khera said...

Thanx dude , i was looking for this tutorial for a long time..definately saving this post on my comp