Computer Help Forum

Go Back   vBulletin and Computer Help Forum > Website Support and Management > Website Administration

Website Administration Website management support for FTP access, SSH access, control panels, SEO, advertising programs and other help for web site administrators.

Reply
 
Thread Tools Display Modes
Old 03-25-2006   #1
Greg
Administrator
 
Greg's Avatar
 
Join Date: Feb 2003
Location: Florida
Posts: 5,194
How to Backup Your MySql Database

Backups are very important. This will show you how to do safe backups of your database via SSH command line access or a cron job from your server control panel.

First the backup...

mysqldump -Q -h localhost -u DB_Username -pDB_Password DB_Name > /home/account_name/folder_name/backup_name_date.sql

If you use a remote DB server, change localhost to the IP or domain name. The reason I show the full path to the backup folder is for those without SSH access. That way they can run this as a one time cron job from cpanel and get a proper backup to the correct folder. There is no space between the -p and DB_password, they are the only option connected. There is the possibility of someone seeing your DB password when using a cron job. If you run this from the command line and want to be safe, remove the password leaving just the -p and you should be prompted for it.

Now the restore...

mysql -h localhost -u DB_USER_NAME -pDB_PASSWORD DB_NAME < /home/ACCOUNT_NAME/BACKUP_FILE_NAME.sql

Again we use the full path for those without SSH access to be able to run this as a one time cron job. There is the possibility of someone seeing your DB password when using a cron job. If you run this from the command line and want to be safe, remove the password leaving the -p and you should be prompted for it.

I have used the method with very large databases successfully. Holler if you need more clarification.

To run a cron job, go to your control panel cron job (advanced unix style) creation screen. Note the current time before you start as we will set the backup to run in a few minutes. After it runs, return to the cron job (advanced unix style) screen and delete the cron job so it does not keep running every hour.

In the cron job screen, there is an email field, put your email address there so you can be notified of any errors that may occur.

You will also see a list of fields to fill out for the job itself...

Minute - Hour - Day - Month - Weekday - Command

In the minute field put the minute of the hour that the cron job should run. For instance, if you checked the time before starting and it was 8:30, a good time to run the job may be in five minutes. Enter 35 in the Minute field.

Then in the Hour, Day, Month, and Weekday Fields, put a *. That's a lone asterisks.

In the Command Field, put the mysql command for either the backup or restore, whichever you are running.

Check your backup folder, or DB screen after a restore, when the time you selected has passed and see if the backup files exists or the DB is restored. If it was successfull, return to the Cron screen and delete the cron job. If you don't, the job will run every hour.

If the backup file is not there or the DB not restored, check your email for an error message. That should tell you what went wrong and enable you to adjust. Delete the cron job and start over.

That should ensure your reliable backups. You should download the backup via FTP to your local computer to be extra safe.

Good luck and backup often!
__________________
Computers and Accessories Computer Manuals Riderinfo Connect for Bikers
Ad Revenue - Sell link space on your website and profit.

Last edited by Greg; 12-14-2006 at 01:40 AM.
Greg is offline   Reply With Quote
Old 03-25-2006   #2
AnthonyCea
Senior Member
 
AnthonyCea's Avatar
 
Join Date: Jul 2005
Posts: 189
This seems much more complex than the response you gave in this thread about me doing a back up remotely to a different server!

http://www.cpurigs.com/forums/showth...5701#post15701

What I am trying to learn is how I can back up my forums at separate data center server where I can buy space to store all my back ups on!

You also have to realize that I have never done this before Noppid.

So what specifically do I need to do to accomplish the above before I get really confused and give up?
AnthonyCea is offline   Reply With Quote
Old 03-25-2006   #3
Greg
Administrator
 
Greg's Avatar
 
Join Date: Feb 2003
Location: Florida
Posts: 5,194
Since I've not done it, I could only guess. I don't have a turnkey solution I know about to suggest.
__________________
Computers and Accessories Computer Manuals Riderinfo Connect for Bikers
Ad Revenue - Sell link space on your website and profit.
Greg is offline   Reply With Quote
Old 03-25-2006   #4
AnthonyCea
Senior Member
 
AnthonyCea's Avatar
 
Join Date: Jul 2005
Posts: 189
Well think about it, because a lot of forum owners have the same problem that needs to be solved
AnthonyCea is offline   Reply With Quote
Old 03-26-2006   #5
Zero Tolerance
Senior Member
 
Zero Tolerance's Avatar
 
Join Date: Feb 2003
Location: In Your Machine
Posts: 1,754
Ive been using the above method for a long time....and when I want a backup to my local media....I just use my forum AdminCP and dump it to a CD...then the CD goes into my fire safe.

I dont personally see the need to pay for data assurance. Waste of money in my opinion. But also you need to realize that backing up your database is only part of having a disater recovery system. You obviously will need to backup any directories that contain user upload files....or else you only have half the horse.
__________________
good day,
Zero Tolerance
aka "SpeedDemon" & "theMadHacker"

As I was walking up the stairs
I met a man who wasnt there
He wasnt there again today
I wish...I wish he'd go away!

ATV Forums ~ 250R ~ Banshee ~ Utility Class ~ Sport Quads
Free Internet Radio ~ Independent Music & Artists ~ Become an online radio Dj
Zero Tolerance is offline   Reply With Quote
Old 03-26-2006   #6
Greg
Administrator
 
Greg's Avatar
 
Join Date: Feb 2003
Location: Florida
Posts: 5,194
Problem with PHPadmin Is that not all folks have it and some servers time out on databases of 100 MB or more when downloading often.

Anthony. I have no interest in writing a how to that sends business to other people. If I provided that service, perhaps I'd venture an opinion.
__________________
Computers and Accessories Computer Manuals Riderinfo Connect for Bikers
Ad Revenue - Sell link space on your website and profit.
Greg is offline   Reply With Quote
Old 03-26-2006   #7
AnthonyCea
Senior Member
 
AnthonyCea's Avatar
 
Join Date: Jul 2005
Posts: 189
Well PM me something or start the service Noppid.
AnthonyCea is offline   Reply With Quote
Old 08-02-2006   #8
Big Dan
Senior Member
 
Big Dan's Avatar
 
Join Date: Jul 2006
Location: New York
Posts: 259
Send a message via AIM to Big Dan Send a message via MSN to Big Dan Send a message via Yahoo to Big Dan
Thanks for the how to.
Big Dan is offline   Reply With Quote
Old 08-26-2006   #9
AnthonyCea
Senior Member
 
AnthonyCea's Avatar
 
Join Date: Jul 2005
Posts: 189
Red face

Even with instructions some of us still can't figure out how to do it!
AnthonyCea is offline   Reply With Quote
Old 08-26-2006   #10
Greg
Administrator
 
Greg's Avatar
 
Join Date: Feb 2003
Location: Florida
Posts: 5,194
Quote:
Originally Posted by AnthonyCea View Post
Even with instructions some of us still can't figure out how to do it!
Please be specific and I'll be glad to try and help you sort it out.

Thanks
__________________
Computers and Accessories Computer Manuals Riderinfo Connect for Bikers
Ad Revenue - Sell link space on your website and profit.
Greg is offline   Reply With Quote
Reply

Tags
backup, cron, database, forum, mysql, mysqladmin, vbulletin

Thread Tools
Display Modes

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 -5. The time now is 01:10 AM.



vBulletin 3.8.7 Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright vB and Computer Help Forum

EZ software products copyright Greg Lynch 2006-2013