Register Members List Search Today's Posts Mark Forums Read

Reply
 
Article Options
Tutorial: How to make a reliable database backup
TECK
Join Date: Dec 2001
Posts: 4,182

Canada
by TECK TECK is offline 07 Jun 2002

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

Please let them know also what you think about it. Thanks.
------------------------------------------------------
This tutorial is vBulletin.org and cPublisher.com copywrited. 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.

TOOLS NEEDED
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.

BACKUP PROCEDURE
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 /backup 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 USERNAME -p DATABASENAME > /path/to/backup/folder/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).

RESTORE PROCEDURE
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 USERNAME -p DATABASENAME < /path/to/backup/folder/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.

COMMON ERRORS
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.

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

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

Last edited by TECK; 21 Jul 2004 at 03:17..
Views: 28564
Reply With Quote
Comments
  #2  
Old 07 Jun 2002, 08:46
Chen's Avatar
Chen Chen is offline
 
Join Date: Sep 2001
I highly recommend to create the backup directory above your Web root, not below.
__________________
Chen Avinadav
Formerly vBulletin.org Owner and Founder

chen@avinadav.com
Reply With Quote
  #3  
Old 07 Jun 2002, 16:57
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
thanks firefly for pointing this matter out. very good point indeed.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #4  
Old 01 Jul 2002, 21:11
zachb's Avatar
zachb zachb is offline
 
Join Date: Nov 2001
Real name: Zachary Brown
Thank you very much for all these tutorials, nakkid. I really appreciate you doing all of these. Keep up the good work.
__________________
Zachary Brown
Reply With Quote
  #5  
Old 14 Jul 2002, 22:34
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
UPDATES
People who ripped my tutorial, without placing a link that point here, as discussed:
Tutorial: Backup A Reliable Database
Ripper's Comments: Good Tutorial, Use It!

Please let them know also what you think about it. Thanks.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #6  
Old 24 Jul 2002, 20:53
DestyNova DestyNova is offline
 
Join Date: Jun 2002
lol and that thread is closed now

appreciate your helpful tutorials real lots, it help me get thur these problems. Many thanks
__________________
Civilization did not create war.
War created Civilization.
English is not my first language. Please excuse my sloppy english
Reply With Quote
  #7  
Old 09 Aug 2002, 03:12
Visionray's Avatar
Visionray Visionray is offline
 
Join Date: Feb 2002
Great tutorial. I've been looking for an easy and thorough explanation to this for a LONG time.

Thanks!
Reply With Quote
  #8  
Old 29 Aug 2002, 09:44
IllNation IllNation is offline
 
Join Date: Feb 2002
what is host name ur vb database or ftp.ursite.com?
Reply With Quote
  #9  
Old 29 Aug 2002, 20:49
IllNation IllNation is offline
 
Join Date: Feb 2002
i do not understand how to use secure crt pleass help...
Reply With Quote
  #10  
Old 30 Aug 2002, 05:08
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
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.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #11  
Old 30 Aug 2002, 11:03
IllNation IllNation is offline
 
Join Date: Feb 2002
i dont understand what i put as my hostname...
Reply With Quote
  #12  
Old 30 Aug 2002, 17:44
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
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.
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #13  
Old 31 Aug 2002, 01:55
IllNation IllNation is offline
 
Join Date: Feb 2002
keeps giving me an error about unable to authenticate please help...
Reply With Quote
  #14  
Old 31 Aug 2002, 01:57
TECK's Avatar
TECK TECK is offline
 
Join Date: Dec 2001
Real name: Floren Munteanu
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"...
__________________
Floren Munteanu
Axivo Inc.
Axivo Community - Visit the forums to find out more about us
Why Queued - My personal blog
Reply With Quote
  #15  
Old 31 Aug 2002, 02:07
IllNation IllNation is offline
 
Join Date: Feb 2002
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 :\...
Reply With Quote
Reply

Similar Article
Article Author Type Replies Last Post
Administrative and Maintenance Tools Site Backup (easily backup your forum files) bairy vBulletin 3.6 Add-ons 54 06 Jun 2009 17:07



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Article Options

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 01:38.

Layout Options | Width: Wide Color: