Go Back   vb.org Archive > vBulletin Article Depository > Read An Article > Programming Articles

Reply
 
Thread Tools
Tutorial: How to make a reliable database backup
TECK's Avatar
TECK
Join Date: Nov 2001
Posts: 4,182

 

Canada
Show Printable Version Email this Page Subscription
TECK TECK is offline 06-06-2002, 10:00 PM

[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.
Reply With Quote
  #52  
Old 01-12-2005, 02:13 PM
noppid noppid is offline
 
Join Date: Mar 2003
Location: Florida
Posts: 1,875
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by thamyeuvn
237: Got a packet bigger than 'max_allowed_packet'
How can I fix it?
Your host has to tweak the mysql settings in my.cnf to fix that. Make them do it, do not accept no for an answer.
Reply With Quote
  #53  
Old 01-16-2005, 07:38 PM
thamyeuvn thamyeuvn is offline
 
Join Date: Oct 2002
Posts: 58
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I used Secure SSR and I find this file on my server:
*my.cnf (the name is crazy=
How can I replace "max_allowed_packet" to correct value? and how can I find the file my.cnf on my server .`? My server is using cpanel
Reply With Quote
  #54  
Old 03-21-2005, 09:33 PM
Q-v-n-s-Q Q-v-n-s-Q is offline
 
Join Date: Mar 2005
Posts: 289
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

i setup everything, when i type in my password

and click connect

Shell access is not enabled on your account!
If you need shell access please contact support.
Reply With Quote
  #55  
Old 03-21-2005, 10:10 PM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Then that is just what you need to do. Most hosts will give you access on request without a problem.
Reply With Quote
  #56  
Old 03-22-2005, 04:26 AM
Q-v-n-s-Q Q-v-n-s-Q is offline
 
Join Date: Mar 2005
Posts: 289
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

well my old host http://elite-forumz.net i wana restore database to http://elite-forumx.net but the databse is 8 MB, any other way??
Reply With Quote
  #57  
Old 03-22-2005, 07:20 AM
Trigunflame's Avatar
Trigunflame Trigunflame is offline
 
Join Date: Aug 2002
Posts: 742
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

< advertise >

https://vborg.vbsupport.ru/showthread.php?t=78486

Is your easy answer to a reliable backup

< / advertise >

Reply With Quote
  #58  
Old 06-26-2005, 05:39 PM
deathemperor's Avatar
deathemperor deathemperor is offline
 
Join Date: Jul 2003
Location: HOL
Posts: 1,270
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

This is cool when your server have SSH access, mine now has !

note that if your host isn't localhost by default you must use:

mysqldump --opt -u USERNAME -p -h SERVER DATABASENAME
mysql -u USERNAME -p -h SERVER DATABASENAME

you must then change SERVER, USERNAME and DATABASENAME to your values.

Thanks Teck for this !
Reply With Quote
Reply

Thread Tools

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

Forum Jump


All times are GMT. The time now is 12:05 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04750 seconds
  • Memory Usage 2,268KB
  • Queries Executed 22 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)modsystem_article
  • (1)navbar
  • (4)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (2)pagenav_pagelink
  • (8)post_thanks_box
  • (8)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (8)post_thanks_postbit_info
  • (7)postbit
  • (8)postbit_onlinestatus
  • (8)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete