Backup a MySQL db with logrotate

Comments

• tchen Member

  March 2014

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

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

  March 2014

  Damn, I thought I invented this Thanks @tchen.
• Virtovo Member

  March 2014

  Nice
• tchen Member

  March 2014

  @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
• howardsl2 Member

  March 2014 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.
• bdtech Member

  March 2014

  Why not automysqlbackup ?
• petris Member

  March 2014

  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.