[Tutorial] ''How to backup&restore your forum in several different ways''

[babjusi]

Post HTML Suite Plugin's DEMO

*** If you are looking for an affordable hosting with great support quality, click here! ***

Backing up your forum

A lot of web service providers say they do backup of all the files, but my opinion is not to take their word for granted. We have put in a lot of efforts and time in creating our forums,so if we lost them, then all our hard work would have been in vain. Backing up our forums at least once a week, makes sure that we never loose too much of our work in case of a server crash, and it will make us sleep better at night. It is easy and fast, so there is no reason for not doing it.

The way I see it we can make a backup in several different ways.

1.Through Tools & Maintenance-Database Backups-New Backup in the Admin Panel. I won''t go in details here as we all know what to do there

2.Through phpmyadmin at the Control Panel of your host

Open phpMyAdmin.
1. Click Export in the Menu to get to where you can backup you MySql database.
2. Make sure that you have selected to export your entire database, and not just one table. There should be as many tables in the export list as showing under the database name.
3. Select"SQL"-> for output format, Check "Structure" and "Add AUTO_INCREMENT" value. Check "Enclose table and field name with backquotes". Check "DATA", check use "hexadecimal for binary field". Export type set to "INSERT".
4. Check "Save as file", do not change the file name, use compression if you want. Then click "GO" to download the backup file.

Note If you have large databases it may be not possible to backup using phpMyAdmin, as phpMyAdmin has some file size limits. So, in this case you will to use the command line tools that comes with Mysql. I came across this method recently and I am currently testing it. So, use it at your own risk

1. Change your directory to the directory you want to dump things to:

user@linux:~> cd files/blog

2. Use mysqldump (man mysqldump is available):

user@linux:~/files/blog> mysqldump --add-drop-table -h mysqlhostserver
-u mysqlusername -p databasename (tablename tablename tablename) | bzip2
-c > blog.bak.sql.bz2

Enter password: (enter your mysql password)
user@linux~/files/blog>

Example:
mysqldump --add-drop-table -h db01.example.net -u dbocodex -p dbwp | bzip2 -c > blog.bak.sql.bz2

Enter password: my-password
user@linux~/files/blog>

The bzip2 -c after the pipe | means the backup is compressed on the fly.

Note: Compressing db's on the fly is only appropriate for small forums. -For large forums its best to compress after the dump due to it loading everything in memory when done on the fly.




3. Through SSH/Telnet

This will only work if you have SSH or Telnet access to your site. You will have to ask your hosting company about that. If you do, the next thing you will need is a SSH/Telnet Client. I would recommend PuTTy.

Open your SSH/Telnet client and log into your website. This will bring you to the FTP root folder. To create a backup in the current directory, type in the following:

Code:[Highlight]

mysqldump --opt -Q -u dbusername -p databasename > backupname.sql

If you want to create the backup in a separate directory, then instead type in:

Code:[Highlight]

mysqldump --opt -Q -u dbusername -p databasename > /path/to/backupname.sql

The program will ask you for your database password. Enter it and the backup process of your database will start.

Some hosting companies, stores the db in a remote server, in that case you will need to add the server name to the command line. You can see that at the file that holds the db info. It varies for forums.
The command line for the current directory will be:

Code:[Highlight]

mysqldump --opt -Q -h servername -u dbusername -p databasename > backupname.sql

Whereas for the separate directory it will be:

Code:[Highlight]

mysqldump --opt -Q -h servername -u dbusername -p databasename > /path/to/backupname.sql

4.Through mysqldumper

You can download it and read how to use it at its official website:

http://www.mysqldumper.de/en/

Mysqldumper can be used to restore the database as well


Restoring the database

1. Through phpmyadmin at the Control Panel of your host

Open phpMyAdmin.
1. Click Import in the Menu.
2. Choose Location of the text file.
3.Browse to your backup of the database that you have stored in your computer
4. Choose Character and Format of imported file
5. Click Go


Restore without phpMyAdmin

The restore process consists of unarchiving your archived database dump, and importing it into your Mysql database.

Assuming your backup is a .bz2 file, creating using instructions similar to those given for Backing up your database using Mysql commands, the following steps will guide you through restoring your database :

1. Unzip your .bz2 file:

user@linux:~/files/blog> bzip2 -d blog.bak.sql.bz2

Note: If your database backup was a .tar.gz called blog.bak.sql.tar.gz file, then,

tar zxvf blog.bak.sql.tar.gz

is the command that should be used instead of the above.

2. Put the backed-up sql back into mysql:

user@linux:~/files/blog> mysql -h mysqlhostserver -u mysqlusername
-p databasename < blog.bak.sql

Enter password: (enter your mysql password)
user@linux~/files/blog:>

Now, as we mentioned above, phpMyAdmin has some file size limits so if you have large databases it may not be possible to backup using phpMyAdmin. In that case we can restore the backup in 2 other different ways, which are as follow:

2. Through SSH/Telnet

If you have access to SSh, then you can use putty or some other SSH/Telnet Client that takes your fancy.

First upload your backup copy to your server and then open your telnet client and log in to your site type in directly the path to where your backup is located in your server. So the command line will be something like this:

Code:[Highlight]

mysql -u dbusername -p databasename < /path/to/backupname.sql

and enter the db password.

Don''t forget that if you are on a remote MySQL server, then don''t forget to add the server name to the command line. Like this:

Code:[Highlight]

mysql -h servername -u dbusername -p databasename < /path/to/backupname.sql

3. The other way is through BigDump: Staggered MySQL Dump Importer



4. Through mysqldumper

You can download it and read how to use it at its official website:

http://www.mysqldumper.de/en/

Mysqldumper can be used to backup the database as well




I hope that this tutorial may come in handy to someone.

Best regards
Babjusi

[littleg]

Just a recap.... so others know whats been happening

I have mysqldumper installed and set up. Amazing tool!
Everything went well seeing the db and starting the restore.

After restoring many tables, it stopped on this error


What do i do? its was going very well till this happened.

The db did not finish restoring, so whats the next step? I tried again, but got the same error.

[babjusi]

(12-11-2009 06:37 PM)littleg Wrote:  Just a recap.... so others know whats been happening

I have mysqldumper installed and set up. Amazing tool!
Everything went well seeing the db and starting the restore.

After restoring many tables, it stopped on this error


What do i do? its was going very well till this happened.

The db did not finish restoring, so whats the next step? I tried again, but got the same error.

At what table did it stop? And what Mysql version did you have at your old host and what version at your new one?

I will have to log off now but keep me updated and I will get back to you again tomorrow.

[littleg]

All is good now .. you were a big help.

Thanks again, solved!

[babjusi]

You are welcome, glad to have been of help.