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

Started by babjusi, July 23, 2007, 10:15:28 PM

Previous topic - Next topic

babjusi

Recently I have come across some posts where people had problems with restoring their backup. So I decided to make a tutorial on the subject, maybe someone will find it useful. If the moderators think that this tutorial is not necessary or not in the right place, then feel free to delete it or remove it.

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 ''Forum Maintenance'' in the Admin Panel


When backing up this way it is better to do one backup with the ''the table structure'' option checked and another one without. So if later on,you will be restoring to a database in which the tables are already built, you can use the backup copy ''without the table structure'' and the other way around.

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.

Thanks to Tony for bringing this to my attention and reminding me to include it



3. The third method of making a backup is to use the create_backup.php tool created by Unknown, which you will find it attached at this post

Upload the create_backup.php tool to the same directory where your smf files are,point your browser to it and follow the instructions.

i.e. www.yoursite.com/forum/create_backup.php

Note that ''forum'' stands for the directory where your smf files are located. If your smf files are located to same other directory, then replace ''forum'' while pointing your browser with the name of that directory.
Or if your smf files are in the root directory, then point your browser to:
www.yoursite.com/create_backup.php


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:

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

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

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:

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

Whereas for the separate directory it will be:

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


5. Using Java Backup Tool (Windows). I have never used this method, so I can not comment on its efficensy


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:


1.Restore_backup.php tool created by Unknown, which can be found attached this post

Upload the restore_backup.php tool to the same directory where your smf files are,point your browser to it and follow the instructions.

i.e. www.yoursite.com/forum/restore_backup.php

Note that ''forum'' stands for the directory where your smf files are located. If your smf files are located to same other directory, then replace ''forum'' while pointing your browser with the name of that directory.
Or if your smf files are in the root directory, then point your browser to:
www.yoursite.com/restore_backup.php


3. 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:

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:

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

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



5. 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


Smith6612

Nice Tutorial. It'll be useful for a lot of people I'm sure. But one question: Will this tutorial be able to help me on a server that has a crashed forum/has no phpMyAdmin, but is a standard Windows Command Line for MySQL? That's how my server is. On my server, I can just navigate to the MySQL install path (C:\Program Files\MySQL\ ) and then I copy and paste the data folder to my backup location on my network (after shutting down the MySQL and Web Server :) ). That's basically how I back up my databases.

greyknight17

@Smith6612: The code mentioned earlier (see below) should do the job for you. You have to make sure that mysql is in the PATH environment in Windows or cd (change directory) to the MySQL\bin folder (I think that's where it's located). Then run the code...

mysql -h mysqlhostserver -u mysqlusername -p databasename < blog.bak.sql

If you want to backup the database, see the tutorial above again for the command line...


babjusi


greyknight17

That method should work. The code looks right and I have tested this before on my local machine with SQL running. Imported without a hiccup :)

Dragooon


shadow82x

Super detailed babjusi! :) I will show people this topic when importing a database. Superb job.
Colin B
Former Spammer, Customize, & Support Team Member

cursed

suppose i am moving to a new host. do i have to move the files and folders to my new host too? My old forum was in: www.mysite.com/forum [nofollow]

If i want to make the appear the forum in www.mysite.com [nofollow]
do i have to move the files to main directory?

babjusi

Quote from: cursed on August 22, 2007, 04:43:58 AM
suppose i am moving to a new host. do i have to move the files and folders to my new host too? My old forum was in: www.mysite.com/forum

If i want to make the appear the forum in www.mysite.com
do i have to move the files to main directory?

If you want to move your board to a new host, you can do so in 2 ways.

1. You can upload the forum files with your ftp client, to your pc and then re-upload them to your new host in the root directory and then run repair_settings.php to set straight all the paths, urls and the other settings. To do that you must click on the blue recommended values.

2. Or you can install first smf in the root directory of your host, then import the database and run the repair_settings.php for the reasons mentioned above. But in this way you willl have to re-install all the mods from the begin.

For more info of how to move the board you can check out the on-line manual

How do I move my SMF board to a different host?



cursed

Can u gimme a details about how i import the sql in phpmadmin. :D



cursed

I am Having this Problem:

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PK' at line 1

:( Any help?

cursed

And here is the details of the error:

There is a chance that you may have found a bug in the SQL parser. Please examine your query closely, and check that the quotes are correct and not mis-matched. Other possible failure causes may be that you are uploading a file with binary outside of a quoted text area. You can also try your query on the MySQL command line interface. The MySQL server error output below, if there is any, may also help you in diagnosing the problem. If you still have problems or if the parser fails where the command line interface succeeds, please reduce your SQL query input to the single query that causes problems, and submit a bug report with the data chunk in the CUT section below:

----BEGIN CUT----
eNo9VXlUUnkbbibnM82TY4u2qcypxpoEx5XU4yErs3KrzDQXGC1FP1lMFHdoGbOyok3NUGm3QjQj
IEWgqdApIkpzQVJGTUkQERGQ/fOrw5x7/7jnvs/7vs/zvs/93bCDB2MOBgF2egN2+gAiw6KDAD4A
X4CPd6C3feyhhcBie/udh2ODABv3HgsCYI4jclJzMem5IERWGignM8cTC/AB+QYAfH7/3d/r920L
98JjkI9PkLc3AJGODASEFeYANtpHFcUeiAwC+IG8Qd7ewF3paVmpKJgfJjUXng4GItBw+7jYg4CY
WE9AaHhY9CFPwOGwhc7xWShAVEzi3sjIUIA/6Hf7/VGhC+RAASA/YA7Cx37/nv3/x3nGxP6/8MIF
3LY1Pe9opjcgMguVX2gfGRodHgRIRwHz8zKA2+y/Edgfsdhm5aJFi5YsWpbiDOZ18srW/Md2UQP7
11RkTjA8L3iR00LwKBqZdRQDwyBSixaELkieYiEx68FX/fVluDAFyykicMedpYoXhXDSh4S1Sa30
qa5q+PSuyD+Th1KA6pvHMMSokmccs/OJHXFK1rIbGwo2vHbFhD1d4WV77rTN5b9LNQis2WwUOQpq
GmVUIcGiJQoreT0qyDcOiLzgkgADTQhtAsN9cU2EcapqxTReh2MEW8YkJItGhO6iDExWDFa6kvyD
LeEWcT+xcsgd7lIjEU08O+IHZ2ZsHVKmYaSW+ThMH0JW76Xm65/pxqZ048w1khh4x+N3TcP81Ya7
TRkCjtrd3CqkR+3hz49FusZ3/Xo8H50X7DdqaOGQTP6mRLfKo4dDxqysaP+EscgSdViSXoCkybhc
UeU1/U6qKbD0Bb6kWqeuOMQlFuO634GwIh53Olf07jBRjnDBaVXcSSmS1fH1N8NsRY1A3FHHKjJL
0NBleGKI7hVCo1K5QNhl6lGJMIuaDKNvnm4YEGTUwF1kBXB+rkbPBFP7IWUqfhP7M3K4WeDsJqAO
xXrp9DqGF75B3gd3x/IhBabwV2K2cVpNrGkRP8OzlY0QvNZY4QJZdmqkRozXjhFlHMNTpCpebJrR
ZnPM+irLl6oJDSU5gKyfPQYOfnzpJVxsNBno51wa1W/OWR52SO66KMAcmmtygGWXzQSxmLnLBqtg
RLH+lAU+8sJKKNFjcaksoTyg8exrNH8vecj+UxNfl5EP5Swux2XTRWqmdvWPUOm9I6E7VBnapJ46
oDRFhaKmgF3OzqQHPxFxDeo3uaK6qBVGc0W82Oo8y4iCainWaYVTe28SSKYRqpDXD2MVm2ZjxLhC
9ZgKDRfgi1VDovgWUZsRGUw1v4Z7Qc3juhhxR22ZTq1yx5XqJmI8gPfrW4kkwx0PB8EjGbWAms6L
KqByyf0QtnGEP9UrA4sv/j3aIuDoJscJVI+pyamvXSq+kJtRcvyT2QABOYvUOhTdkDNsnHS3zOON
1u2bjBwZTDZv0IAtOneLZkoIoVu2lTCEHcFZg6PDoOg+YOU7s1+2pRQXiO9sop3ZQqGMHw4cNusr
ycs5lBqmlnLik2YQNgyGAgchk9JB2KBbQ18KkPIZkq+ms58feYYuMl5GG0TqLDIRDKEVwkj1/h7K
0/3mjoE6BF0+JaSfdofMpM+3m44qRsPNy9ko0x6yDydv3ErQ5eabJMjKG/6leypakiB5bfLE5PKW
bFJbMifFnwYLBo020ishmvFSs44Kt0xalO6krtsX/v3ktn02x1KFXOyI5TPwE669PwK3Xsaaz6En
e738carmy3QLqcLuk3RQiTeEtifS+v0s7jLzR1Bf71f8lJTwymLdoWH0KJCG9rqZEmwZHmJOLHM/
X6loUWPlEW3Za0w0K6oNgTqyq+X4bti7FQfKsNcCUR7n3zzLYcHdf/ow1Azt0D7oDCm8ZAWvGClV
j8pVYHQDXqcW+Ma0N93MfPMsF802T4ogYg9s/XRSLRpvtK8q1MlJMPPMrdb8lNyvk0TsYFi68mXa
+Zn/lnXX9TYWSAe4evy6e+t4fDTcg/jkW/kTQ0vWL4sIw4Vzm/mXQ32tLVV406SabDH08Ga6lE+s
I1pjmplrj7SkQqQx3Rw3aCP50dJ8//z7bG8wqnlg7JF3YlvSvpWfN2KteL1IzMYZxg0ZSt4bfVqB
USxRJGakP71aR7DLS5xkna1uncFGDfSKGIluJFL5KfI+RtQfL2li2qwlCpQ8V04ml5Obc7KPJVEO
2YMSNdlnJ+LfovDR5b79ASeOf8Rtt3uYV7XPJTJ5C8ukmx8qHj/e2sfgAPu06KB/TL/Pnk/Z0m4K
9E+2c36Sdbt1PzNFjMpfpdRImnM2mHJuaq6d35lOxRU5D0KByaLmnPtFSV5oi+fGw4Vo+yWYOCcR
KZZHUf5UIN0on3tZErKyLxn4+eafRfcHiwOp3+fxFLqqjdJ86SGlvZs2u/hRnujHilXNsSNXYT+x
TIGrmu+TbNeTdjXF797SHMAytW1NWU2ws05XWyvnZcSvWsDROuI66QjJTHdJlGRa6oDET+8rOFOh
g/xmPDqwXaWfkjPlE7NG1IrMuSvDP+pVqn40Rz/CJLEFgT3nzuzIjqGL6uUroEN05EXe05ZihrVB
eWTFBEkiQ2JqEvR56FrPQdf+DC0d6nmzcukH6eQxBp+JzcqmF3UrX28Knz2mQpX95zyKqE/DYGtq
+6qlhO9/AbdB++QDJx4lnZQ9WELMPPf5wweCP7CPOTFnPuIE8W5YGdgzIs2TxmA+XR9JJjESZ9Yt
rrpVVSweZK5JQ6ZbLdDy1KGV7nMl/siZhKTad2SHVSXi6IUjZB8/i2Ulm6V9zUMK9oAh1zmCEs7w
X8zHSRJLrytvTzne9tRqQS/hpaN5j1leRA1Jgc0za0sKRzs9StjP0zvFjBbHgrWbQx1WOW5tfg7n
InunwzCa1P2TSY5VQtdEp5K5vEx5z/HFmY4Xp9eeWvetnxC44bIDFV5PKWAHBhYZZO06ProK5Sub
3nLXNyxph9g42DFkxFbDOt8smHZs3zYKg9NBEVipdiDjZ9XSVxuWFuf9QkzfannuVn47i3b11vXq
WawN16oZd32H9/B0yTEnQs9Awq5sZciDs5VDmTzV36feu/UTl6LPkUilM/qYg+/vdhYK6/yY4bOh
ogt8rPa5OdkPHoF3bYd+qRlRIzqo/M3ntBf+YX1fx835j8Zct9tedZl16Opr4Gijsu5Fy4HxsXXd
lAyC1+Zrd+bHtpyHwc3h+RMHYq9AioskNtdqY2uX1L3c9Y+N3xu7dxfYvhw/tBdWxvUiuP4T/lg4
JnENteNrhjZUPt8GKZq6Ljs0diXBpvfvrTt/phwZ/ZhdDUikoqpVm4+5UTPOZWf3bQieG/CP+Fcl
fzkBRrX5K22DG2tgySnu7v3AgFNprU42lRcBCQgb7r/vrQnFrU4EGOL1mMu60CcDZ4OdiQTsct0L
5dj8pbS3v4zKa1bCrGPGY8YTcl6GVGkPmB7oaIE//CQaZOPNO2fknZeCdxZwVKCrPYI4TpF5jLge
THbnFBt6tF6JHTDA1Ub+ZIcQ1zvUqKm5Je3bba13NCa41QByqI7K+8OhooORLE1pYCs9LxkO0q6K
GeRg0POB5AZ50McSx6u228SlJp8ifczha6k+bPXutZuHfPqxTGgAukBBjD1UWKbsE/3J22tijus3
Rr5ah0o7ip/xyHof8t3Ml1HJ3Q5FPvqR9hXCW7djmi0Z81/jqkpz1RKP7uFReZH88W7iuYIgue3J
HpOBMNdSvFy9bL1ne819RfjUFvxvDZ21fsH6kdl7bTdMhimdUDiR+/HeWNO09raTYnEXtMEqp74X
zhMyZy5JjmGVI2n9de+NfMP4EY9tBmRZrk8Jo5aFpvOVnoVt5XhBADkFEyBCZrhE+RrkZSwixWvQ
UT+Men8Jbjsz7grt4AxGwSS24a8MUPWrv0zb/zYbaCOF+XQRqlq+/Z2Z3fYZ6udBJnNMH0wBd26K
qwYWEXvWBEgvaD84dd9TnCSRcsVfOH78XKgXzo4P02HdaZreqWNUHdexmGyn6OMEUHmVQu7Es7gn
02MxcBV/uoy8hpj+gV7EKpX6zyAmGHFUdXMsBc/tP9BPcthvdcrV013XKw4/nCuzeUtU67hTCE2G
KLE9tVq3KdUZlyQA1zCF420Fs8gfioMi3DhulAzGfE2ebrMf9kthEKvrs2+EonM8mOcQIw5PwSnT
/7g2gQcR6WTKlm0lynGEqG6elqZJL7wLNuhhAfe02iFBBM606tW999TjkhDgEkXXWg4hyIJkIZEs
2aLXYqjtCKyx3/HEB005b/3P86ttBD/4bA6bty7jxxEmVo+CAxchLV6dCR2ZjCfUFjCQFFLPmlXg
G96b24fZSaQQU1L2twTYVID8vGpIqJ9sovU5E8WDjvXAfuBkETQTKBR+Q6zsWP2X9ss4xIWve0++
Ydlvm2czTyVclLvCXL98PwgqMq7Da9xy11XP/VbmvhPnXN3XkNpOCgHV002VQX3RPzX7BE3sLfh6
Q5VyME5vpSlNEOkxHK8UTYoM7yv9fLSv02SZZX/C1XcfWfvpfWxvrNvS1Utr2TH7b2/FSGFn5g7M
rYQ3vT3wLb3oPuEvc5VPGSeCiEMw6vl76q1Vo8Mh0zWkO++JyF51Y+m1vK7OeNv6JVqc5LEhoLy0
uB3vaedaHhV1qfflpyqQjS3GcuO7BBxpnx97g6LLtS3T6Ez7eVObgjw238HlcSusFnDV+KyuDCrj
pJwcvRe5diXlNOFFiDUWy3xX5bb7bsIDRey5Rtn4nY2jdg8f/7Hp5UO9S+J/5/54N/Yo6l/dJwuD
snNL9E5+4RL1Rwhjjdp7dc+pRsWKosm7atUGvCvX/nxO4Nse0MrHjD2vkjY7DSeVK1Zcl7TwsQwc
4/YJv2k6pviX3iqojWEAk2O0Vq1KwN2Xve0y75NFXwr8qv3yGEKvTPr09oFC+/DFg9iTT8CbDi7y
DvLYMR8Km9xZ+nFrj/aR/fa+K2I1WbSdur4hKsb+f3L5U4g=
----END CUT----
----BEGIN RAW----

ERROR: C1 C2 LEN: 2 3 2191
STR: 

CVS: $Id: sqlparser.lib.php,v 2.36 2005/08/08 20:22:11 lem9 Exp $
MySQL: 4.1.11-Debian_4sarge7-log
USR OS, AGENT, VER: Win MOZILLA 5.0
PMA: 2.6.4-pl2
PHP VER,OS: 4.4.4-8+etch1 Linux
LANG: en-utf-8
SQL: PK����]7���}���&gt;����comics_slayer.sql�ms7�5�}~E�K9B� ��xg��X[���ǖg�DL�\�]-�ds�M{����BU��#w#�sE�/����|�lv����ϗ��ُ��ً���?&lt;{6��^�7g3~������~�;������oǭ��ڋ�5;�G��כ��g����Y4g�f+��bs���Us�l�/���������Og��̬���fϿ���ٶMH���LW�"4���5�Z�cV=�&lt;��E����E[��m����ۋ��C��9|�{���Tśz~��.v����r��V��l~�����m���*���߼��y��o^�=��l���?�}����i�\_�(���f�g�wg�r��7��?}�ͬ��mޮ�ϳ�S/���/���gv�?w�G�߾���ߴ��?��?�������m�W����k�������\6���d7;���g������ɉ�����7��\6�D�z�Dv�M���9�/v�N�Ua���6���o�I��
Ԭ��fu^��~k����^�YAB�f�[Ӝ-�]�n�]7��e;������rۜM���W�&gt;����z����I��������_�z��O�~x���og�z���W�۹�m;���g/^���O߼�}���~|��O'-������' ϩ�w�e�Mw�ū�?�������7ߍ��Ͽ��右'���������f{q���?.���n��p������&lt;���_����7������?��8{�ټ;i���.N�-���4k�|~9Ƭ��)���V9�����������_�7^-�?���_���]-��?u�Y�oy�o���i��7?�x_��5'����؜l���ٶ�?�e��c��G��n�H�2�t�&lt;��[?�5|H��[?t��Z\��k��\�]5�_;.⧶�?��|��g�����ǡ�?&lt;8��S���v���-�~��K~��p�\/����   �����A�Z��4����.�������&gt;��c-�o/�];��ݻ�����v�K�k�&gt;�lnYD�qF_�Q}v�9n'�ɴp�g�ݮ^���=x�&gt;�|����7o����3O���hɴro����?�'v��[�o�
�x��_��u]r��v�Ee��b��j}Ҝէw�������og'��&��KE~GŮ͐A3&gt;�����������&lt;���L�a?�Oҿ^��� u5u��17n���1Z�[J�$v&lt;��߾~���f����bw����Zfe����   tZ齈���vM��۷Z�����J�M`µߵ��M.\

DadL

Quote1.Through ''Forum Maintenance'' in the Admin Panel

When backing up this way it is better to do one backup with the ''the table structure'' option checked and another one without. So if later on,you will be restoring to a database in which the tables are already built, you can use the backup copy ''without the table structure'' and the other way around.

Just to make sure... is it like this?


babjusi

@cursed, how are you trying to restore your backup file, zipped? If so unzip it and try to restore it as a sql. format and see if that would help

@DadL, the way shown at the screen shot is with the ''the table structure'' option checked.


Advertisement: