Maintenance Plan

[Kipper]

Does MySQL have any similar functionality to the maintenance palns on MSSQL? I want to automatically backup the databases on my Linux server everyday, instead of manually having to invoke the query each time. ------------------------------------------------------------------
It's important to think. It's what separates us from lentils.

 

[KarveR]

Why not just use replication and have a handy hot swap available ? ***************************************
Party on, dudes!

 

[Kipper]

Because I want to be able to back it up onto the network tape drive every night. ------------------------------------------------------------------
It's important to think. It's what separates us from lentils.

 

[DumTech]

Are you just looking for cron? I back up the db at my virtual web host with a simple shell script and a cron job. [Cron's a *nix thing, not a MySQL thing though.]

$ crontab -e
lets you edit the crontab, which looks something like this:

SHELL=/bin/bash
PATH=/bin:/usr/bin:/home/[user]
MAILTO=email@domain.tld
HOME=/home/[user]

# This will run script_name at 01:28 every day
28 1 * * * script_name

man crontab and man cron for more, of course.

Hope maybe that helps..? Matt
matt@paperlove.org
If I can help, I will.

 

[Kipper]

It's a start, thanks. Could you tell me what the script looks like please? My data files are kept in /usr/local/mysql/var/[db].

On another note, do you know how to use cronolog? I've followed the guide, but it's not too clear about the usage in httpd.conf. ------------------------------------------------------------------
It's important to think. It's what separates us from lentils.

 

[DumTech]

I just use mysqldump, which may or may not be what you really want. It basically creates an output file that you could use as source to recreate the database (ie it recreates tables and inserts data with plain ol' SQL statements). Works for me, so I didn't dig deeper into the options for MySQL backups.

I set a $filename with the date in it, then run mysqldump:

#!/bin/bash
date=`date +%d%m%y`;
filename="[name]$date.sql";
mysqldump -u [username] --password=[pwd] --opt [db_name] > [my/home/dir]/$filename;

That'll give me db230402.sql, for instance, today.

As for cronolog, I've no clue... sorry!

Hope this helps otherwise...

Matt
matt@paperlove.org
If I can help, I will.

 

[Kipper]

thx, it does ------------------------------------------------------------------
It's important to think. It's what separates us from lentils.

 

[Kipper]

Oops, getting a problem with crontab.

I've created the script as above. However, when crontab executes it, the result is a file of zero bytes.

Running the script directly at the command-line or using at works.

the crontab entry is
10 0 * * 6 /usr/local/mysql/backups/mysql_cron_bkup

Any suggestions? ------------------------------------------------------------------
It's important to think. It's what separates us from lentils.