Automatic Backups

Gee Bee · #1 Jan 8th, 2006, 10:51

Hi,

Is it possible to setup an automatic backup with mysql? I've looked at the options that my hosting provider offer and whilst you can do it manually there's nothing to allow automated ones.

Does anyone know how this could be achieved? As always any help much appriciated!

grahame · #2 Jan 8th, 2006, 11:16

Can you run crontab on your host? I use crontab for a daily backup - my cron table contains:

10 4 * * * mysqldump -hlocalhost -psqlpassword -uwellho wellho > /home/wellho/sqlbackupdirname/`date +%A`.sql

This generates a file named after the day of the week at 10 past 4 every morning. so it runs when the system is quiet.

Link - more about crontab

Gee Bee · #3 Jan 8th, 2006, 12:22

Wow thanks for the quick response, Im new to this forum am am really pleased to have found it!

I do have cron jobs available to me on my host, but how do i find the path for the database?

grahame · #4 Jan 8th, 2006, 12:44

The name of the database will be the name that you were given by your ISP for the purpose, and the name you'll have coded into your application. Databases don't really have "paths".

You will need to set up an output directory and give the full path to it for your dumps - I would suggest that you log in through ssh and use a mkdir to create a new backup directory on your server that's NOT part of you document or cgi directories (this avoids you creating a backup file that has its own URL that someone might stumble upon). The pwd command will tell you the current directory you're in so that you can work out this path.

It's also possible that mysqldump may need a full path name to be provided. If you run which mysqldump you'll be given that full path.

Gee Bee · #5 Jan 8th, 2006, 13:09

Sorry to be a further pain but could you explain a few things and check i have the right idea.

1.mysqldump - this is the command that copies the data
2.hlocalhost - this is the name of the database?
3.psqlpassword - is the password i use.
4. > is move to following path.

but what does "uwellho wellho" mean or do?

grahame · #6 Jan 8th, 2006, 15:48

mysqldump - the command that does the dump
-hlocalhost - dump the data off the computer called "localhost" - that's the equivelant of saying "myself" and you would only change it if your ISP used different computers for the web server and the MySQL server
-psqlpassword - yes, replace "sqlpassword" with password you use to access your MySQL data
-uwellho - the login name you use to access the MySQL daemon
wellho - the name of the database that you're backing up.
> is used to redirect the output ... i.e. say where to save the data. Leave this out and crontab emails you the dump!

Have a look here for full details of the mysqldump command - it's much more thorough than my explanation above

(My own domain name is called "wellho" ... you'll change that to something related to your account - perhaps to geebee if your domain is www.geebee.co.uk, for example)

Gee Bee · #7 Jan 8th, 2006, 16:47

Ok, thanks for your help.
Crontab seems like a really useful tool, can you recomend somewhere on the net to get more info, such as the types of commands you can run?

Thanks again for your help, much apprieciated.

SweetLou · #8 Jan 9th, 2006, 03:54

With cron you can run any command that you could from the shell. So, you could run a command like the one above or you could run a command that runs a script, so that script can do even more.
You might want to change the mysql command a bit. I generally use

Code: Select all

mysqldump --add-drop-table -u {username} -p {database name} > {directory file name}

I like this better because I think it makes it easier to restore.

bridges · #9 Jan 22nd, 2006, 01:42

Once this cronjob is setup. Is it going to over write the backup from the day before?

What is the advantage of the add-drop-table over the other?

SweetLou · #10 Jan 22nd, 2006, 02:48

The add-drop-table will add that line to your dump before each table. That way when you restore, it will drop the table if present and rebuild it. Depending on how you restore the database, it won't restore a table if it already exists, so drop it first to restore it.
Yes, it will overwrite the file, but you can change that if you wish. I have a little script I use that will create a dump with the date and time appended to the end of the file name, so that you will get a new file for whenever the script is ran.

PHP: Select all


<?php
// Enter your MySQL access data
$host= 'localhost';
$user= 'Your_username';
$pass= 'Your_password';
$db= 'Your_database';
// the directory you want to store your database file
$backupdir = 'backupdb'; // Compute day, month, year, hour and min. 
$today = getdate();
$day = $today[mday]; 
if ($day < 10) { 
$day = "0$day";
} 
$month = $today[mon]; 
if ($month < 10) {
$month = "0$month"; 
} 
$year = $today[year];
$hour = $today[hours]; 
$min = $today[minutes]; 
$sec = "00"; 
// Execute mysqldump command. 
// It will produce a file named $db-$year$month$day-$hour$min.sql
// under $DOCUMENT_ROOT/$backupdir 
system(sprintf( 
'mysqldump --opt -h %s -u %s -p%s %s > %s/%s/%s-%s%s%s-%s%s.sql',
$host, 
$user,
$pass, 
$db, 
getenv('DOCUMENT_ROOT'),
$backupdir, 
$db,
$year, 
$month, 
$day,
$hour, 
$min 
)); 
echo '+DONE';
?>

Then what I do is have two cronjobs, one that will backup every night. This file is overwritten each night, and a second one using the script above that runs once a week. So I have a nightly and weekly backups. I like doing it this way incase something happens and I can't catch it that day, that the backup is not ruined also.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Automatic Scheduling	Jackus	PHP Forum	4	Apr 5th, 2008 01:59
Automatic calculations	josephman1988	JavaScript Forum	7	Dec 22nd, 2006 17:44
Automatic Sending	accessman	Databases	0	Sep 25th, 2005 17:25
Automatic Automated mail out	briandmenupalace	Classic ASP	4	Jul 14th, 2005 17:58
automatic redirects	benbacardi	Classic ASP	2	Sep 13th, 2004 09:02

		Webforumz.com > Main Forums > Program Your Website > Databases
Automatic Backups