MySQL Incremental Backup

Server Configuration

• Start the server with the --log-bin option

  [mysqld]
  ...
  log_bin=/var/log/mariadb/mysql-binlog
  expire_logs_days=10
  ...
  

Backup

• Full Backup (e.g. Once a week)

# Delete or backup old incremental files
rm -f /var/log/mariadb/mysql-binlog*

# Then, make a full backup (will count from 1 again)
mysqldump --all-databases --single-transaction --flush-logs --master-data=2 | gzip > full_backup.sql.gz

• Incremental Backup (e.g. every one hour from a crontab)

 mysqladmin -uroot flush-logs

Restore

1. First, restore the full backup

    zcat full_backup.sql.gz | mysql -u root
   

2. Then, restore every "fresh" incremental backup file

    mysqlbinlog mysql-binlog.* | mysql