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.