cron this!

Anon

I have a crontab which creates a gzip of my mysql directory and places it in a folder and its named mysq.tar.gz

Here is the routine..

0 1 tar -cf /path/to/backup/folder/mysql.tar
/path/to/mysql/ ;

cd /path/to/backup/folder/;

gzip mysql.tar

However is it possible to get the the name of gzip file to reflect the time it was gzipped such as

mysql1129.tar.gz instaed of mysql.tar.gz

Any advice on this matter as always is very much appreciated.

Kind regards

Paul

[deleted]

First, making a copy of datafiles is a bad idea unless you specifically lock the tables you are copying first.
A much better solution is to use MySQLdump to make a copy of the data and the table structure.

To get it to have a special name, why not just use PHP in CGI form? or a PERL script?

jrmckee

Probably a good idea to create a shell script and use mysqldump. Here is a simple bourne shell script:

#!/bin/sh

Get date from server

TODAY=date +%m-%d-%y.%H

Dump database using mysqldump

/usr/local/mysql/bin/mysqldump --user=root --password=yourpwd dbname > /data_bak/dbname.sql

Create TAR file name

SAVEFILE="$TODAY.tar"

Buid TAR file

tar cf /data_bak/$SAVEFILE /data_bak/dbname.sql

GZIP TAR file

gzip /data_bak/$SAVEFILE

END of script

Make this script executable and run it from cron. Remember to replace <dbname> with your database name.

Jon

Anon

There's also a small Perl script in you binary dir called 'mysqlhotcopy'.

It claims:

mysqlhotcopy - fast on-line hot-backup utility for local MySQL databases

It's supplied in the MySQL package itself.

Anon

or save a step

SAVEFILE="$TODAY.tar.gz"
tar czf /data_bak/$SAVEFILE /data_bak/dbname.sql

[deleted]

why use tar on a single file?

you can condense all that to a oneliner.

/usr/local/mysql/bin/mysqldump --user=root --password=yourpwd dbname --opt | gzip > /data_bak/date +%m-%d-%y.%H.sql.gz

A bit trivial to condense it, I'm just showing off here :-0

One important note though: then --opt is very usefull. It makes sure that when restoring the backup, all old data is erased before the data is restored. (we are talking MySQL here, checkpoints are not available until version 4.0 and even then it's not quite up to scratch) Also, it locks the tables during the restore, so that data remains consistent, and the restore is quite a lot faster.