Howdy, Stranger!

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

Sign In Register

Quick Links


Categories

In this Discussion

› › Backup a MySQL db with logrotate
New on LowEndTalk? Please Register and read our Community Rules.

All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.

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/

  • sleddogsleddog Member

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

  • VirtovoVirtovo Member

    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.

  • bdtechbdtech Member

    Why not automysqlbackup ?

  • petrispetris Member

    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.