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

    Cheers
    PM
     
  2. platd

    platd New Member

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

    Code:
    # 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....
     

Share This Page