How to backup up MySQL Binlogs with a file backup job | Ottomatik

How to backup up MySQL Binlogs with a file backup job

The MySQL Binlogs also known as Binary Logs or Transaction logs hold a sequential log of all statements that modify your database.  Inserts, updates, deletes, alter tables, drop tables etc.  If you have binlogs enabled it is a good idea to back them up in addition to your full database backup.

To backup your binlog files make a file backup job with an include path of /var/log/mysql/mysql-bin.[0-9]* -mmin -5 note the advanced find parameters here.  This will backup all mysql-binlog.(digit) files in the /var/log/mysql folder (the default binlog location) that have been modified in the last 5 minutes.  Depending on how busy your database is you will want to modify that 5 minutes to be a time range that 1) you'd have modifying SQL statements run on your DB and 2) coordinates with the schedule of this backup job. 

Next you'll want to schedule how often this job runs.  For ideal operation use a plan that supports the crontab scheduling feature and configure the job's schedule to */4 * * * * which will run the backup every 4 minutes.  Then you could set the retained file count to 15.

The above method should copy the binlogs every 4 minutes, grabbing anything that has changed in the last 5 minutes and keep 15 copies which is an hours worth.  This should work well if you are doing a full backup every hour.  You'd also want to configure "flush logs" on your full backup if your plan supports it.  That will create a new binlog after each full backup.

If you wanted an even more aggressive schedule you could use -mmin -2 and * * * * * and retain 60 files if you wanted a backup to run every minute but that would be your decision as to whether you want a backup connecting to your server over SSH every minute.

Note that the user you have connecting over SSH will need access to the directory holding your binlogs (/var/log/mysql by default).  To check the groups that your backup user belongs to use the command:

groups userName

To add an existing user to an existing group use the command:

sudo usermod -a -G groupName userName