Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Advertise on LowEndTalk.com
Backup a MySQL db with logrotate
New on LowEndTalk? Please read our 'Community Rules' by clicking on it in the right menu!

Backup a MySQL db with logrotate

sleddogsleddog Member
edited March 2014 in Tutorials

File: /etc/logrotate.d/mydb-backup.conf

/home/sleddog/mydb/dump.sql {
    daily
    rotate 14
    compress
    nocreate
    postrotate
        /usr/bin/mysqldump my_db > /home/sleddog/mydb/dump.sql || true
    endscript
}

Now rsync the dir to a remote location for geographic redundancy :)

Thanked by 2tchen Virtovo

Comments

  • tchentchen Member

    For use with rsync, use 'dateext' to make it behave better

    courtesy of http://www.leaseweblabs.com/2013/06/logrotate-and-rsync-mysqldump-backups/

  • Damn, I thought I invented this :) Thanks @tchen.

  • Nice

  • tchentchen Member

    @sleddog said:
    Damn, I thought I invented this :) Thanks then.

    Still counts as an independent invention, just not first :) But thanks for posting it though - I hadn't even thought to use it like that. You've opened pandora's box. Oh the mischief! :P

  • howardsl2howardsl2 Member
    edited March 2014

    Here's the version I use. It includes gzip max compression and automatically reads mysql credentials from file.

    Step 1: Create file /root/.my.cnf with content below.

    [mysqldump]
    user=root
    password=YOUR_MYSQL_ROOT_PASSWORD
    

    Step 2: chmod 600 /root/.my.cnf

    Step 3: mkdir -p /opt/mysqldump

    Step 4: (Important) touch /opt/mysqldump/db.sql.gz

    Step 5: Create file /etc/logrotate.d/mysql-backup.conf. Customize backup frequency and number of archives kept according to your needs.

    /opt/mysqldump/db.sql.gz {
        weekly
        rotate 4
        dateext
        nocompress
        create 0644 root root
        notifempty
        postrotate
           mysqldump --defaults-extra-file=/root/.my.cnf -u root --events --ignore-table=mysql.event --all-databases > /opt/mysqldump/db.sql --single-transaction
           gzip -f -9f /opt/mysqldump/db.sql
        endscript
    }
    

    Step 6: Set up rsync to backup directory /opt/mysqldump.

  • Why not automysqlbackup ?

  • I backup MySQL databases and other stuff by dumping/copying them into another directory and then commit/push those into a remote git repository. I get an offsite copy and have a complete history of what changed without having to muck around with zipped/gzipped files.

Sign In or Register to comment.