Backing up your database

[dat]

Can someone give a brief primer or overview on the best practices of backing up your MySQL database? What tools do you use, PHP MyAdmin? Where do you back them up to? Do you FTP them to your hard drive? For larger databases, say 20 megs and above, how do you back them up? Etc.

[grant]

Snagged this from the knowledgebase:

Step 1:
Login to your control panel http://yourdomain.com/cpanel

Step 2:
Click on MySQL databases

Step 3:
Scroll to the bottom and click on phpMyAdmin hyperlink

Step 4:
Click on the dropdown to the left and choose the database you wish to export the backup from.

Step 5:
Click on the Export Tab.

Step 6:
Click the Select ALL link shown in blue. The check Save as file and click GO.

Step 7:
You will be asked to save the .sql file to the hard drive. Click Save.

Step 8:
Choose the location to save to and click Save. Your database has now been exported.



I usually take it a couple steps further though and check the boxes for "add drop table" and "complete inserts".

You can also choose to save it as a zip file, or as the raw sql data.

Hope that helps.

[dat]

Hey thanks. So where do people put their databases once they are zipped? Do you just ftp them to your hard drive or leave them on the server or do you use web storage? What about for larger databases, such as 20 megs or above?

[grant]

Well, everyone has their own personal choice of where to store their data. As for me, I simply download mine (using the steps above), and then keep one copy on my hard drive, and another on a CD. I also keep a copy of one zipped, and one raw. Why I do this I'm not sure. I like to play it safe though and keep things handy.

There's no need to FTP your databses at all. Unless you want to keep a backup on the server, which I wouldn't advise. It's best to have 2 or 3 copies of everything (databases as well as the core files for your site(s)) close at hand so that you can access things at all times.

[timberford]

exporting from phpmyadmin is good but personally i use the cron job feature. it emails me a copy of my entire database every day. i use gmail so there's loads of space for it. you can set the job to run at any time of day and a varying intervals, it doesn't have to be every day.

just go to cron job, choose standard and then choose your time of day and how often. put in an email address to receive it.

i used the following command, obviously replacing the caps with my own details. so i get a massive load mysql commands that i can run to restore my db structure and all my data.

mysqldump -u DBUSERNAME --password=DBPASSWORD DBNAME

you could run a php file instead and make things a lot more tidy. like getting it to email you your db as an attachment.

you could use a command like this...

wget -q http://yourdomain.com/phpmysqlautobackup/run.php

obviously it doesn't matter where the php file is located. it should be pretty easy to find a ready made script somewhere.

[piku]

I was actually trying to debug why Wolf's db_backup script aint working. Other than the $HTTP_*_VARS b/c of PHP5, I don't know what else to check out. Anyone? Try it out and see. It's a great script (on PHP4 at least). Allows tar.gz and FTP-ing to another account.

http://www.bluehostforum.com/showthread.php?t=329

Update
Seems to work but it doesn't send the email from the script successfully like it does in PHP4 host


[10-Mar-2006 01:00:08] PHP Notice: Undefined index: SERVER_NAME in /home/thinkpil/db_backup/class.html.mime.mail.inc.php on line 611
01:00:08: Mail sending failed with the following error(s):
sender not yet given
valid RCPT command must precede DATA

So, you can either turn off sending to an email (and just send results through CRONTAB) or try to look for the booger thats causing this mess. Let me know which you decide, or if anyone has discovered the solution. TIA

[redsox9]

exporting from phpmyadmin is good but personally i use the cron job feature. it emails me a copy of my entire database every day. i use gmail so there's loads of space for it. you can set the job to run at any time of day and a varying intervals, it doesn't have to be every day.

just go to cron job, choose standard and then choose your time of day and how often. put in an email address to receive it.

i used the following command, obviously replacing the caps with my own details. so i get a massive load mysql commands that i can run to restore my db structure and all my data.

mysqldump -u DBUSERNAME --password=DBPASSWORD DBNAME

you could run a php file instead and make things a lot more tidy. like getting it to email you your db as an attachment.

you could use a command like this...

wget -q http://yourdomain.com/phpmysqlautobackup/run.php

obviously it doesn't matter where the php file is located. it should be pretty easy to find a ready made script somewhere.

Thanks for the great tip, timberford! Now that I'm starting to work with these databases, it would be a VERY good idea to have backups just in case.

[bob_ib]

What about the backup option(s) via cPanel? Any good?

Bob

[crazypenguin]

I prefer to use cpanel to backup each individual sql database.

[Maggilove]

I use cPanel too - quick and easy.

[grant]

I suggested the cpanel way because I've had cron jobs fail on me more times than I'd like to share. Plus, it's just easier to do and there's not scripting invoved.

[dkinzer]

I was actually trying to debug why Wolf's db_backup script aint working. [...] Seems to work but it doesn't send the email from the script successfully like it does in PHP4 host
The problem is that the SMTP server at BlueHost requires user authentication and Wolf's script doesn't do that. I modified it to optionally use SMTP authentication and it works great.

I sent a patch file to Wolf for the changes. I haven't heard anything back so I don't know if the changes will be incorporated. I can provide a link to the patch file if anyone's interested.

Don Kinzer
http://www.zbasic.net

[jamesd116]

Thanks for the great tip, timberford! Now that I'm starting to work with these databases, it would be a VERY good idea to have backups just in case.
I used this process to do back ups every hour on my databases now that i have thisinformation coming to my email i look at it and see a bunch of gibberish to a newbie like me if i was to restore this would t be easy or should i find a different way.
Basiclly i guss what im asking is if i deleted everything or just lost all my db info would i just go to my email account copy it aand paste it nto a file and everything would be back to normal
something istelling me itss not that easy
im thiinking once the database is recreated i would have to manually insert information
wold it be better for me to do backups to my hard drive with the zip file
is there a better way for me to back up my sites hopefully in its entirety in one shot without having all the separate files
Maybe going to copy and paste ftp and public_html my computer unlesss someone can tell me an easy way of backing up my sites in a one button click and it gives me what i need to my email periodically
Thanks James
http://www.4spotlight.com
Looking for Local Musicians
Local Music for Local People

[shirleymixon]

MySQL database backup
=====================
http://restkultur.ch/personal/wolf/scripts/db_backup

I use the above to backup to a second server on bluehost
(not the same one - it's cheap at $7/month) and send myself an email that it got done...I call it each morning from a cpanel cron job...you can set the program to do what you want...

[piku]

Has anyone have any problems with Wolf's script above on PHP5?

[Syntholis]

Hey guys...

I'm backing up two different mysql databases via "backup" on the control panel... tried to restore one today, and discovered that they are being chopped at 147k... so they're useless...

If I back them up from phpmyadmin, same thing... the .gz files are corrupt...

Any ideas? A little nervous here...

[chrism]

I did a backup using the control panel earlier today... and restored it... it worked fine...

[Syntholis]

Even the daily backups, and such are doing it...

A FULL backup comes down, but it doesn't have sql backups in it...

ANd it is on multiple machines... so that's not it...

Can you go through the process, step by step for me, maybe I missing something...

My 9-8-06 backup was fine, but they've all been broken since then.

[chrism]

These are the steps:
1) go to control panel
2) go to System Tools
3) go to Backup
4) ... (I'm waiting for the server to respond... will continue when that happens)

[Syntholis]

Just in case this server won't let you post twice in a row... :)

[chrism]

4) Under "Download a MySQL Database Backup," you will see your database(s) listed with a clickable link
5) Click on the database which you wish to backup.
6) Browser may ask you where you want to download it onto your local machine; if so, of course you must direct it to your local directory...

[Syntholis]

Yes sir... that's exactly what I do... and I can click on any of the three databases, and they all download a 147k .gz file... which, when opened, tells me it is corrupt.

[chrism]

Where do you try to open them? What do you mean by "It happens on several machines?"

[chrism]

You must be doing something wrong... I just downloaded a backup up mine... and gunzipped it... and it looks fine...

[Syntholis]

Well, I'd sure like to know what.

Regardless of doing it from there, getting a gz file, or from phpmyadmin, getting either gzipped or none, it's dorked.

147k... corrupt if opened from winrar.

incomplete if phpmyadmin tries to restore it to the local server...

[Syntholis]

and, by "multiple machines", I mean it happens on any of the 10 workstations I have here in the house... so it's not 1 particular machine.

[Syntholis]

Oh well... it looks like I hope nothing ever happens to it. Even the .sql files directly in the full backups won't restore... they seem complete, but until something dies on bluehost, I guess I'll never get to try to restore it... can't risk it.
Other than easyphp, what do you guys use for local servers, without setting up an entire apache server...

[chrism]

Are you running Windows XP by chance?

[Syntholis]

Yes... ? I can try a multitude of Server 2003 & Vista boxes... :)

[chrism]

Invite me to assist you using the Help and Support from the Start Menu... then I can see *exactly* the same thing as you... and we can use MS Instant Messenger... and cut out this lag time...

[Syntholis]

I can't... I'm behind a cisco firewall my employer manages. That would be a strict no no from my boss. I'll just have to look into it from work on Monday.

[chrism]

Good Luck!

[Syntholis]

Thanks for the help.

[chrism]

I just did the same steps...
Downloaded my backup from control panel/Tools/Backup
Uploaded using phpmyadmin...

And it worked...

Sorry, we couldn't resolve what's happening in your case...

Did you wipe out the database in between these two steps? I did... It should not matter since the scripts have "drop table" statements in them...

Good Luck... and good nite.. (er, morning)!

[typksn]

I was able to use Wolf's db_backup script to backup multiple database, but was not able to restore some tables that included a timestamp value (i.e. 2007-05-21 15:22:16).

Here's the insert statement and error I received when I tried to restore:

INSERT INTO `<table_name>` VALUES (1, '1112223333', 'DOE', 'JOHN', '932 N MAPLE AVE', '<city>', '<state>', '<zip>', 2007-05-21 15:22:16) ;

ERROR 1064 (42000) at line 50: 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 '15:22:16)' at line 1

To resolve the problem, I had to add single ticks around timestamp value.

Example: '2007-05-21 15:22:16'

INSERT INTO `<table_name>` VALUES (1, '1112223333', 'DOE', 'JOHN', '932 N MAPLE AVE', '<city>', '<state>', '<zip>', '2007-05-21 15:22:16') ;

[God's Webmaster]

I can't... I'm behind a cisco firewall my employer manages. That would be a strict no no from my boss. I'll just have to look into it from work on Monday.
What about trying it at a friend's house, or the library -- in other words, on some other connection?

[PainKiller]

I use mysqldump (as mentioned near the top of this thread), executed daily...but not directly via cron. I actually use the "logrotate" utility (/usr/bin/logrotate) to manage my retention. The logrotate utility doesn't "know" these are not logs, nor does it "care". I created a logrotate.conf file that tells logrotate to:

Run daily
Retain/rotate logs (which are actually mysql DUMPS!) on a 7-day cycle
Run the dump script immediately after rotating the logs (DUMPS).

It's configured to put the .sql into a backup directory on the server, but could be easily configured to rcp it somewhere else, or to even email it.

A week's dumps look like:
wordpress.fullbkp.sql
wordpress.fullbkp.sql.1
wordpress.fullbkp.sql.2
wordpress.fullbkp.sql.3
wordpress.fullbkp.sql.4
wordpress.fullbkp.sql.5
wordpress.fullbkp.sql.6
wordpress.fullbkp.sql.7

That's actually 8 days counting the current day.

Jack

[txauction]

I am not familiar with this whole mysql bit. However, while I understand I need to backup files, the phpadmin doesn't make sense and I don't have time to read the manual.

What are the pro's & con's of using the cpanel backup vs. phpadmin?
Could someone provide a step-by-step on how to backup up databases? (The original post on this thread is a couple of years old and it apparently has changed since then, as the instructions don't correlate to what is found on the pages referenced in the post.)

[Early Out]

... I don't have time to read the manual.Yet you expect someone else to take the time to do the work for you? :rolleyes:

Let's stop resurrecting stale threads, people.