backup script for mysql server

[pontifex]

Hi everyone,
i would likte to backup my complete mysql server on a regulär base to another server. is there a way to do that using rsync to save traffic?

every link is appreciated ;-)

Cheers
PM

[platd]

Check out this script
http://kent.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5

I use it my self and it works great you can have the databases emailed to you and it backs upto a folder with date etc which you could then rsync or what ever to another machine as well

I use this one too for full backup which make restoring easy

#!/bin/sh
/usr/local/bin/mysqldump -u root --password=password --all-databases | gzip > /usr/backups/fullmysql/backup.`date +%y%m
%d`.sql.gz
/usr/local/bin/mutt -s "Mysql Dump all db" -a /usr/backups/fullmysql/backup.`date +%y%m%d`.sql.gz emailme@domain.org.uk
< /root/text_foremail.txt

text_foremail.txt just contains some text that appears in the body of the email to remind me to do stuff.
I am not very good at scripting but it works ok :)

[sjau]

If you use rsync or rdiff or whaterver to put files on a remote server I suggest this here:


# Make MySQL Backups
#!/bin/bash
# Remove old files
rm -f /mysql_backup/*

#Dump new files
USER=root
PASSWORD=************
HOST=localhost

for i in $(echo 'SHOW DATABASES;' | mysql -u$USER -p$PASSWORD -h$HOST|grep -v '^Database$'); do
mysqldump \
-u$USER -p$PASSWORD -h$HOST \
-Q -c -C --add-drop-table --add-locks --quick --lock-tables \
$i > /mysql_backup/$i.sql;
done;

this one will create individual backup files for each database. I prefer this over having one huge file....