Taking a backup of your db and restoring it

[bharathbala2003]

I noticed a lot of sites hosting blogs or forums being shown off in the general section so i think this could be usefull.

Well this should help people backup almost any of the forum or blog software.

Note i ve tried this only on phpBB,SMF,IPB and Wordpress but i assume it will work for others if they use a mySQL db and php.

First things first.

Log on to your site's ftp (either a standalone one like cuteftp or smartftp or turboftp) or accesss *www.net2ftp.com and enter your ftp details to connect from webbrowser.

Now download a copy of the forum of blog folder or any other folder that you want to backup. Download it your computer and make a archive(can be used).

Now log on to your cpanel or which ever panel your host provides, and get to phpmyadmin. If you are not sure how to access php myadmin please contact your host for the details.

Now you will be facing a similar screen to as shown below

*img168.imageshack.us/img168/754/phmyadminyt3.th.jpg

Now navigate to select your database from the list in the left handside, once its loaded select on Export option near the right corner.

There select all tables under the data base and select SQL option and scroll down the page to select SAVE as option, i advice you to save the file in both normal(non Zipped format) as well as gzipped format.

Thats it you have successfully taken a backup now

Restoring

Now there are some really tricky things you need to know and make your backup work.
First try your level best to get same user id for the cpanel so that ur db name will not change. Also try not to change the DB name to something new. Create a User Id and password for the database and id recommend you use same id and password you used before in the old host or from wherever you took the backup.

Now again go to phpMyAdmin and select your new database. Click on export option near the top right corner.

Now select the database you want to upload.(note many hosting providers give a file size limit : some give 10mb some give 50mb so if your size limit is 10 use the gzip).Your file size limit will be shown next to the dialog box where you need to select the source of your backup.

Now scroll down the page and select OK

Wait till the Page loads and says XXXX queries run sccessfully (Where XXXX will be any number of queries depending on your db size). If you get any error while uploading please contact support forums of your host or the software which you are uploading.
The errors could mostly be due to different php or mySql versions.

Now log on to your new site through ftp and reupload the saved files(the forum or the blog or anyother folders).

Note that some hosts will allow unzipping online, if your host provides this then please upload the zip file of the forum or blog or the folders you want to upload. Now go to *www.net2ftp.com and log in and select your zip file and select the option to Unzip.(refer the image below)

*img145.imageshack.us/img145/4453/n2frp6.th.jpg

once its either unzipped or its reloaded to the structure you prefer open the config_global.php or any other global config file. This is usually found in the root folder or your forum software.

Now change the settings there to your new settings, such as db name, db password,website address etc.. now access the site you have upped through browser and make sure everything is smooth.

Thats it you have successfully taken a backup and restored it!

 

[Aberforth]

This will work up to a certain database size (usually 50 - 100 mb on today's servers). For even faster backup/restore you would need SSH mysql commands to restore and make backup dumps of databases as PHP backups could lead to errors.

 

[intel_outside]

ssh access has to be turned on on the server in order to execute those commands, right? please mention a good ssh client.

 

[ahref]

In shared hosting chances are rare to get access to ssh. You either have to need phpmyadmin or some other similar tool, you can also try mysql control center for it.

please mention a good ssh client

putty.

 

[tuxfan]

Good tutorial Just my 2 cents.

Now again go to phpMyAdmin and select your new database. Click on export option near the top right corner.

This appears in restore section of your post. It should be "Click on import" and not export. Please correct the typo.

The file exported and saved will contain all SQL commands to regenerate the MySQL db from scratch, including commands to make a new DB. Some hosts may not allow this and keep throwing errors. So you will have to first create the DB from cPanel (or whatever control panel). Then open the saved/exported DB in a text editor, comment out the instructions to create a DB and then import it. Now it will create all the tables and fill them with the required data.

First try your level best to get same user id for the cpanel so that ur db name will not change.

Hmmm. Ok. But don't get too paranoid about it Let me explain people why this is important. If your username is abc and you create a database called mydata then the database that is created will have the name abc_mydata. So that is why its important to have the same username.

However, if you don't get the same username, you may have to modify your application code a little. All well-written applications like WordPress, phpBB, etc. have a config file where you can specify the DB name. You can open that file in a text editor (may be even notepad) and edit it. Help about that file will be available in the installation instructions of the application