Automating Mysql backups
Well this is the scenario: you have a lot of machines around there, all with important data stored into Mysql tables. All the machines are Linux based and you want to backup all MySQL data each night....
We have something for you!
First step
Log in as root into the machine you want to backup. Here you need a local working directory: we call it /home/localdumps
mkdir /home/localdumpsThen log in into the target machine, where you want to stored the backup datas and choose a new directory where to put the backups.
mkdir /home/storedbackupsIn this machine we need a fully functional ftp server and an ftp user to write down the backups.
The script itself
Go back to the source machine, the one you want to backup. Here you can create the script backupmysql
pico /bin/backupmysqlCopy the following lines and edit the first parameters to match your configuration:
#!/bin/bashLOCALDIR="/home/localdumps"
TARGET=""
TARGETPORT=""
TARGETUSER=""
TARGETPASS=""
TARGETDIR=""
TARGETRETRYCONNECT=6
MAILTO="YOUREMAILADDRESSHERE"
MYSQLPASS=""
namefile=$(date +%y.%m.%d)
SOURCE=`hostname`
cd $LOCALDIR
/usr/bin/mysqldump -RaAcel --add-drop-table -h localhost -u root -p$MYSQLPASS > mysqldump.sql
tar -czf mysqldump.tar.gz mysqldump.sql
ftp -i -n -v $TARGET $TARGETPORT 1> mysqldump-ftptest.log 2> mysqldump-ftptest.log <user $TARGETUSER $TARGETPASS
cd $TARGETDIR
bin
put mysqldump.tar.gz $SOURCE.$namefile.tar.gz
quit
!EOF!
mail -s "MysqlDUMP on $SOURCE" $MAILTO < mysqldump-ftptest.log
rm -f mysqldump.tar.gz
chmod 700 mysqldump.sql
Now we transform it into executable
chmod 700 /bin/backupmysqland then we can put it into crontab as usual.