Backing up…

[Cleo]

Drives fail, mistakes are made, are you backing up all of your work?

For most webmasters backing up their web folder is enough, but for sites that use a MySQL database backing up their web folder does not back up their database.

Let's say you have a database named MYDATA and its password is 1234 that you would like to back up.

First FTP into your server and create an empty text file named MYDATA.sql and CHOMD this file 777.

Then log into your server useing telnet or SSH and at the prompt type: mysqldump -uMYDATA -p123456 --opt MYDATA > path_to_MYDATA.sql. You can then download this file to your local drive for safe keeping.

To restore your database this is what you would do. Log into your server useing telnet or SSH and at the prompt type mysql -u MYDATA -p MYDATA < path_to_MYDATA.sql and after entering your password your database will be restored.

It is very easy using crontab and a short script to have your server do daily dumps that you can download for self keeping.

[amber438]

Then log into your server useing telnet or SSH and at the prompt type: mysqldump -uMYDATA -p123456 --opt MYDATA > path_to_MYDATA.sql. You can then download this file to your local drive for safe keeping.

ok..dummie question of the day

path_to_MYDATA.sql is the path to the text file you just made, correct? (never did a cron thing before..)

[Cleo]

Originally posted by amber438
path_to_MYDATA.sql is the path to the sql database you are backing up, correct? (never did a cron thing before..)

No, that is the empty file that you created and then did a dump to or the file that you uploaded from your local backup that you kept in case your server took a shit.

So if your path to your web folder looks something like this, /home/cleo/web and you put your backup in your home folder the it would be /home/cleo/MYDATA.sql

There is no path involved with MySQL, just a call to it on your server.

On my server I have each of my databases do a dump at something like 3am into a file named after the day of the week. I then download these files to my local machine once a week. At most I would loose a few days of data if my server's drive died.

[amber438]

kewlies..thanks

[DangerDave]

..and for those that are telnet/ssh/sql-challenged..

Get PhpMyAdmin - http://www.phpmyadmin.net/

backups are even simpler.

DD

[Cleo]

Originally posted by DangerDave
Get PhpMyAdmin - http://www.phpmyadmin.net/

I always found that much harder to use then pasting a couple of lines of text into a shell, but to each their own.

[DangerDave]

Vers 2.5.2

Via ya browser.. 4 clicks.... and the backup is on your HDD


DD

[iSC_Luke]

Originally posted by DangerDave
..and for those that are telnet/ssh/sql-challenged..

Get PhpMyAdmin - http://www.phpmyadmin.net/

backups are even simpler.

DD

PhpMyAdmin is a great software package, however I don't think there is anyway to set it up to do a cron job and export the stuff on a regular basis.

However if you need to just do a quick backup - it does the job well - you just need to remember to do it every so often.

If you have ssh or telnet access - Cleo's recommendation is more automated

[Cleo]

Using this page as a guide
http://www.cgi-interactive.co.uk/mysql_dump.html
I have daily backups of each of my databases.

It works like a charm and I don't have to remember to do anything.

[iSC_Luke]

TIP: When you export the DB in phpMyAdmin, use this format for the file name: "__DB__ %Y-%m-%d" - it will format the file name like this: "dbname 2003-08-27" - or re-arrange it to fit your purpose

You can also use "__TABLE__" when only exporting 1 table.

[johnbx]

acually i just have the cron job email me the file on a different machine that way i have it sitting in my box with a nice history if i choose to keep.

all you do is the mysqldump like this

mysqldump -u user -ppass db | gzip -9 | uuencode db_backup | mail -s"todays back up" you@mail.com

[Cleo]

Originally posted by johnbx
mysqldump -u user -ppass db | gzip -9 | uuencode db_backup | mail -s"todays back up" you@mail.com

That looks very simple and sweet, may have to give that a try next time I add in a database.

[DangerDave]

All true...

My preamble - "..and for those that are telnet/ssh/sql-challenged.. "

DD

[EclectiXXX]

Just an FYI Cleo's line of code works like a charm!!!

I'm still dinkin round with johnbx's code for CRON... We'll see if I can get it to run tonight...

[AcidMaX]

You could also just add a line to cron and backup your databases nightly, weekly whatever you want. You just have to remember to download them. Or you could get a quick script made in PERL or PHP that will email you the actual file tarred or gzipped on a nightly basis so there is nothing you have to do. Just sit there and check email in the morning :-)

Andy