Archived Forum

http://sourceforge.net/projects/automysqlbackup/

Think that's the one I'm using for our servers. It's in Debian repos.

Thanks.

I will give it a try

Ian

Hi.

You might as well use automysqlbackup, which I didn't know about, but for what it's worth, here's what i did on my server :

(Comments were in french, I'm adding a translation here)

--------------------------------------------------------------------------------------------------------------------------------

#!/bin/sh

#########################################################################
## Ce script sert a effectuer une sauvegarde des bases de donnees mySQL #
## mySQL backup script #
#########################################################################

db=mysql
rep_base=/backup_dir_wherever_you_want/$db

# 1 - Creer un repertoire de sauvegarde en fonction de la date
# 1 - Create backup dir according to date

repertoire=`date +%y%m%d`
mkdir $rep_base/$repertoire
cd $rep_base/$repertoire

# 2 - Faire les sauvegardes
# 2 - Do the backup

for base in `mysql -u root --password=pwd --execute="SHOW Databases" --skip-column-names | cat`
do
echo "Sauvegarde de $base ..."
# Sauvegarde de la base
#echo "mysqldump --single-transaction --host=localhost --user=root --password=pwd $base | gzip > $base.sql.gz"
mysqldump --single-transaction --host=localhost --user=root --password=pwd $base | gzip > $base.sql.gz
done

# 3 - Effacer les vieilles sauvegardes
# 3 - Delete old backups

# Sur la base d'une sauvegarde quotidienne, et pour conserver un mois d'historique,
# on efface ce qui a plus de 30 jours
# We want one month of daily backups,
# let's erase what is older than 30 days

echo "Suppression des sauvegardes precedentes ..."
#find $rep_base -mtime +30 -print
find $rep_base -mtime +30 -delete
 

--------------------------------------------------------------------------------------------------------------------------------

(You may want to comment the delete line and uncomment the print line at the end, in a first time, to check what is going to be deleted.)

I created a similar script for postgresql and made both executable (command : chmod +x filename)

To call them, I added a file in /etc/cron.daily/

--------------------------------------------------------------------------------------------------------------------------------

#!/bin/sh

#####################################
## Sauvegarde des bases de donnees ##
#####################################

# postgresql
su - postgres -c "path_to_pgsql_backup_file"

mysql
path_to_pgsql_backup_file

--------------------------------------------------------------------------------------------------------------------------------

This appears to work but does not claim to be the most elegant and efficient solution.

Hi Jérôme

Thanks. I am fairly new to Linux but I think I understand how your script works so I will give it a try.

Regards

Ian

You're welcome.

<geek mode>Beware, this script might containt cat abuse...</geek mode>

Just like for the delete command, if the mysqldump command fails, you can uncomment the 'echo' line to see what happens.

Hi Jérôme

I have started writing the script in sections to test.

First part:-

#!/bin/sh

#########################################################################
## Ce script sert a effectuer une sauvegarde des bases de donnees mySQL #
## mySQL backup script #
#########################################################################

db=mysql
rep_base=/media/KINGSTON/$db

# 1 - Creer un repertoire de sauvegarde en fonction de la date
# 1 - Create backup dir according to date

repertoire=`date +%y%m%d`
mkdir $rep_base/$repertoire
cd $rep_base/$repertoire

Running it I get this error:-

pi@raspberrypi /usr/local/bin $ test.sh
mkdir: cannot create directory `/media/KINGSTON/mysql/130211': No such file or directory
/usr/local/bin/test.sh: 16: cd: can't cd to /media/KINGSTON/mysql/130211
pi@raspberrypi /usr/local/bin $

I can't see where my error is, any ideas?

/media/KINGSTON is a usb drive that exists and is mounted. I can get to it with a command line cd.

pi@raspberrypi /usr/local/bin $ cd /media/KINGSTON/

pi@raspberrypi /media/KINGSTON $

Regards

Ian

Try

     mkdir /media/Kingston/mysql

first.

By default, mkdir won't create all path but only child directory if parent exists. You can pass it '-p' to let it create necessary directories (see man mkdir).

Jérôme

Again many thanks. The script is now working and all looks good. Tomorrow will show if I have added it to cron correctly.

Regards

Ian

Glad it helps.

Yet, as I said, if I were you, I'd pay a look at the tool Oscar suggested, which is even packaged for debian.

Well, actually no. I would have paid a look, but now that you got something that seems to work, I'd probably keep it as it is.

Bye.

Note to self : add something in the wiki about http://sourceforge.net/projects/automysqlbackup/