Tutorial: How to make a reliable database backup
 

[high]UPDATES[/high]
People who ripped my tutorial, without placing a link that point here, as discussed:
[high]Tutorial:[/high] Backup A Reliable Database
[high]Ripper's Comments:[/high] Good Tutorial, Use It!

Please let them know also what you think about it. Thanks.
------------------------------------------------------
This tutorial is [high]vBulletin.org and cPublisher.com copywrited[/high]. All rights reserved.
Please ask permission in this thread to copy the tutorial. If granted, post a link that points to this page.

Database backups are the most important for MySQL users. If something goes wrong, you can easily restore all your tables and other information contained into.
Unfortunately, the vBulletin database backup is reliable only if used on a very small database, due to browser timeout limitations. On a large database the backup can take up to 20 minutes.

[high]TOOLS NEEDED[/high]
SecureCRT - 2.4MB (for more info, visit SecureCRT site)

NOTE: SecureCRT (30 days evaluation) combines the secure logon and data transfer capabilities of Secure Shell (SSH) with the reliability, usability and configurability of a proven Windows terminal emulator.
Another popular alternative would be PuTTY (free), a implementation of SSH and Telnet for Windows platforms, along with an xterm terminal emulator.
I tried both tools and decided that SecureCRT is the best for my needs because all data (send and receive) is encrypted. Also SecureCRT was much more flexible. I will use SecureCRT in all my steps, for this tutorial.

[high]BACKUP PROCEDURE[/high]
I assume that you already installed SecureCRT and configured correctly it's options, including the connection protocol, hostname, port and username. If you are not sure about any values, now is the time to contact your host and enquire this information.

01. Create a [high]/backup[/high] folder in your ROOT, using your FTP client. Later on, you will need to know the path to that folder. If you are not sure how to get the path for your backup folder, create a info.php file with the content:
<?php phpinfo(32); ?>

Upload it into your backup folder and open it in your internet browser. Look for this information:
_SERVER["DOCUMENT_ROOT"] > /path/to/backup/folder

02. Login onto your server, using SecureCRT. You will see this information:
[USERNAME HOSTNAME]$ _

That means you are successfully logged in and ready do give commands, at the prompt.

03. At the prompt, type:
mysqldump --opt -u [high]USERNAME[/high] -p [high]DATABASENAME[/high] > [high]/path/to/backup/folder[/high]/bk060502.sql

NOTE: Change the highlighted values with your own. You will need the MySQL database USERNAME that your web host created for you, NOT the one you access to your FTP server. In most cases, they are different.
If you are not sure, contact your host to obtain this information.
060502: month date year (easier to remember the last date you backup your database). Change it with the corresponding date of your actual backup.

04. You will be asked for your password:
Enter password: _

Enter your MySQL database PASSWORD and hit Enter.
NOTE: If you have a large database, it might take up to 20 minutes until your backup is done. Relax and smoke a cigarette or grab a snack. ;)
When done, you will see in the SecureCRT window, the command prompt.

05. Login onto your FTP server and go to your /backup folder. You will see there your bk060502.sql file. Save it to a safe place (a CD-RW is good for me).

[high]RESTORE PROCEDURE[/high]
Well, here it comes that day when your tables are corrupted and nothing works in your beloved vBulletin. If you would not had done a backup, you would probably hit your head against the wall because you lost all your 3845 members and 285,793 threads. Tables corruption can happen pretty often, even if MySQL is known for it's reliability.

01. Login onto your FTP server and upload your bk060502.sql file to your /backup folder.

02. Login onto your server, using SecureCRT. You will see this information:
[USERNAME HOSTNAME]$ _

That means you are successfully logged in and ready do give commands, at the prompt.

03. At the prompt, type:
mysql -u [high]USERNAME[/high] -p [high]DATABASENAME[/high] < [high]/path/to/backup/folder[/high]/bk060502.sql

NOTE: Change the highlighted values with your own.

04. You will be asked for your password:
Enter password: _

Enter your MySQL database PASSWORD and hit Enter. When done, you will see in the SecureCRT window, the command prompt.

[high]COMMON ERRORS[/high]
The most frequent error you might get is this:
ERROR 1045: Access denied for user...

There are 3 causes for this:
1. You didn't replace the "username" with the MySQL username assigned by your administrator.
2. You supplied the wrong password (usernames and passwords are case-sensitive).
3. You don't have access. Contact your administrator for more assistance.

NOTE: Since SecureCRT have all data encrypted, it's possible also to safely specify your password into the command line. I recommend not to do it, just to keep a good habit. In normal conditions, if you do so, it's very possible your password can then be viewed by others using your system.

[high]OTHER RESSOURCES[/high]
- Moving Servers (vBulletin Technical Manual)
- mysqldump (Dumping Table Structure and Data)

If this tutorial was useful, please post your comments. Happy backups. ;)

I highly recommend to create the backup directory above your Web root, not below.

thanks firefly for pointing this matter out. very good point indeed.

Thank you very much for all these tutorials, nakkid. I really appreciate you doing all of these. Keep up the good work. :)

[high]UPDATES[/high]
People who ripped my tutorial, without placing a link that point here, as discussed:
[high]Tutorial:[/high] Backup A Reliable Database
[high]Ripper's Comments:[/high] Good Tutorial, Use It!

Please let them know also what you think about it. Thanks.

lol and that thread is closed now

appreciate your helpful tutorials real lots, it help me get thur these problems. Many thanks

Great tutorial. I've been looking for an easy and thorough explanation to this for a LONG time.

Thanks!

what is host name ur vb database or ftp.ursite.com?

i do not understand how to use secure crt pleass help...

try the help file.. it's well explained.
let me know if you found a solution, if not i will post here how to enter your information.

i dont understand what i put as my hostname...

your server that you want to connect. is the same as in your ftp settings.
for example in my case is: teckwizards.com
also, the username is not the name of your database but your ftp server username.
let me know if you have any other problems.

keeps giving me an error about unable to authenticate please help...

how can i help if you dont describe in details exacly what you do?
please post all the steps you do with details, not just:
"keeps giving me an error about unable to authenticate"...

ok i am editing my connect settings u know where it ask u...

name,protocal,hostname,port,username,etc... i put in all that info and then i went to go connect and it gave me that error :\...

you don't understand i think.
write everything here, all the details.

CONNECTION EXAMPLE
Name: teckwizards.com
Protocol: ssh2
Hostname: teckwizards.com
(and all the rest...)

until you dont give exact details, i cannot help you.
MAKE SURE you include the exact error message not something vague...

Name: capital-hiphop.com
Protocol: ssh2
Hostname: capital-hiphop.com
Port: 22
Username: N/A cuz i dont want people knowing
Authentication
Primary: Password
Secondary: <none>

Error Message: Sercure CRT Has disconnected from the server. Reason: Unale to Authenticate using any of the configured authentication modes

ok, first open SecureCRT and do not attempt to connect!!!
go to tab:
Options > Global Settings > SSH2 > Host Keys
and delete your current key.
then delete also the connection you created already:
Alt+C > highlight the connection and hit Del.

now, create a new connection and use ssh1 in the dropdown menu, for protocol.
when you connect for the first time, Accept&Save the host key database entry.
if it doesnt work, MAKE SURE YOU CONTACT YOUR HOST and ask them what is your recommended protocol. any host support SecureCRT, is the most used software to connect.

After I type in the mysqldump etc etc it asks me for my password, but when I go to type it in, nothing comes up and the cursor stays still then after i've finished typing and Pressed enter, it just comes up with:

bash-2.05a$:

What should I do?

reinstall. what OS you run?

windows XP

Reinstall what? SecureCRT?

OK I just noticed something, after I type in the password and hit enter, it doesn't do anything but after a while comes up with the bash thing, after doing that I checked the backup folder and a backup was present! So the 20 minutes you say it takes was misleading, even though I have 50k posts, 5k threads, 1k members in my DB :p

great tut TECK, it's a very fast way of backing up.

One Question: Is this the equivalent of downloading the database everytime from the adminCP? Does this contain the exact same information, all users, posts, threads, template edits, stylesets etc?

it depends on your database size. if you have a small database, ya is fast. but on a large site it can last even 30min, so is not misleading.
I specified this clearly in the tutorial:
You will not see the prompt until the backup is done.
for your question, yes. it does contain all the time all data stored in your database. and the bigger is your database to higher will be the lap time until you complete it.

I'm on broadband, should it be fast? It only takes me about 40 seconds to backup but if I download the DB from the AdminCP it's about 70MB however when I dump it it's only 38MB :confused:

i'm talking about databases that are 1gig. 70mb is really small.

How can I import someone elses VB into mine without losing eithers posts?

there is no way to do this, until now. if i understood correctly, vB team works on a project like that...

So I'd likely have to import the db table itself into a sub table, then transfer what I could by hand?

I'm more at home with the database than I am php code, I believe I could get some done.

Trying to get this to work.

When I log on to CRT, I don't get [USERNAME HOSTNAME]$ _

I get
bash-2.04*$

Anyways, when I type in the mysql bla bla, it says

"no such file or directory exists.

This is the path Im typing
home/virtual/site376/fst/var/www/html/backup/blablabla.sql

This is the exact info I was given from the info.php file.
I know the directory is there, I can see it on my server, but CRT isn't recognizing it.

Any ideas?

Should I turn off the board first? I've heard it both ways and I'm not sure if this is required or not. Thanks.

JakeC

No need to turn the board off. :)

Thanks, I appreciate it.

JakeC

Is it normal for the backup made this way to be about half the size of the one that was downloaded via the vB admin backup? And should I be getting "Database Error" emails to my admin account every 2 seconds while backing it up? Other than that, everything seemed to have gone well.

Thanks.

No and No.

teck help me out i got this error

mysqldump --opt -u***** -p clan?racknine?net > /home/myback.sql

mysqldump: Got error: 1130: Host 'localhost.localdomain' is not allowed to connect to this MySQL server when trying to connect

Great Tutorial Teck! Ive been looking for a way to do this for a while.

i have very small board for that reason i share server with someone but dont have have shell access backing up is not a problem i use SQLyog or MySQL-Front clients backing up my database is ok but when i restore it takes ages and i mean ages. The way it backsup is it runs SQL Query on every thing like thousands for word and search they take the most time i was wondering is there a way i can just upload the whole backup file so it dosnt run Queries instead i just delete the old one and replace it with new one.
i have broadband connection so it would take long to uoload the files.
Many Thanks

My backup strategy - this runs as a daily cron job.

My web root and the databases are on /usr not /var - and /usr is on a 40g IDE drive. The rest of the system is on a 10k rpm SCSI drive. No reason for the web content to be faster than my internet connection :)

I get one copy of the backup on each spindle and then manually copy one of the backups to a different machine once a week or so. I guess I could automate that if I wanted to.

#!/bin/sh

service httpd stop

mysqldump -uusername -ppassword -c vb_bassforum > /usr/www/archive/vb_bassforum.sql.dump
mysqldump -uusername -ppassword -c pointbeing > /usr/www/archive/pointbeing.sql.dump
mysqldump -uusername -ppassword -c hcbf > /usr/www/archive/hcbf.sql.dump

service httpd start

cp /var/spool/mail/* /usr/www/archive/mail

#change dump permissions so I can restore databases from remote with phpMyAdmin if I need to.

chown wizard /usr/www/archive/*.dump
chgrp apache /usr/www/archive/*.dump
chmod 660 /usr/www/archive/*.dump

tar cf /archive/backup.tar /usr/www/*

cp /archive/* /usr/archive

A little hint for those who's database does not run on their webserver, i.e. where your host has a dedicated mysql server.

You must use the '-h' option to specify a mysql server

Example:

mysqldump --opt -h mysite.co.uk -u name -p dbname > /home/www.mysite.co.uk/public_html/backup/vbulletin.sql

I spent weeks trying to get my head round this, eventualy our sysadmin helped out with the above info.

use: /var/www/html/backup/blablabla.sql


actually....get it out of 'web accessible' area...
/var/backup/blablabla.sql