backup script for mysql server

Discussion in 'Server Operation' started by pontifex, Jun 27, 2006.

  1. pontifex

    pontifex New Member

    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 ;-)

  2. platd

    platd New Member

    Check out this script

    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

    /usr/local/bin/mysqldump -u root --password=password --all-databases | gzip > /usr/backups/fullmysql/backup.`date +%y%m
    /usr/local/bin/mutt -s "Mysql Dump all db" -a /usr/backups/fullmysql/backup.`date +%y%m%d`.sql.gz [email protected]
    < /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 :)
  3. sjau

    sjau Local Meanie Moderator

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

    # Make MySQL Backups
    # Remove old files
    rm -f /mysql_backup/*
    #Dump new files
    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;
    this one will create individual backup files for each database. I prefer this over having one huge file....

Share This Page