In order to utilize Ottomatik's MySQL Binlog Backup jobs there are some requirements for how your database handles it's binlog files. All these changes can be set in the [mysqld] section of your mysql config file.

Make a backup copy of any MySQL config files you plan to modify before you make any changes.

The MySQL config file can be found in different locations depending on your Operating System and MySQL variant and version. Some common locations are:

Ottomatik requires the following configuration settings related to binlogs, all of which can be set in the mysql config file:

#1) Mysql must bind to a network address accepting remote connections

This can be achieved by setting the bind-address parameter to 0.0.0.0 or the public or private IP of your server depending on how network connections reach your server. 0.0.0.0 will tell mysql to listen on any IP address.

#2) A server ID must be set

This can be achieved by setting the server-id parameter. It should simply be set to a number. If you only have a single server (as in no other master / slave configurations) you could simply set this to 1.

#3) Binary logging must be enabled

This can be achieved by setting the log_bin parameter. The value will be interpreted as a path and log filename. A typical setting for this parameter is /var/log/mysql/mysql-bin.log which will store the binlog files in the /var/log/mysql directory with filenames like mysql-bin.XXXXXX.

Check that the /var/log/mysql directory exists. Some CentOs / Red Hat distros use /var/log/mariadb instead.

#4) Binlog max size cannot exceed 100M

This can be achieved by setting the max_binlog_size parameter. The max supported by Ottomatik is 100M so unless you have other needs it is advised to use the 100M setting.

#5) Binlog format must be set to STATEMENT

This can be achieved by setting the binlog_format parameter to STATEMENT. You may find that there is not a default value for this parameter commented out in your config file. That is because STATEMENT is the default value, but we advise setting the parameter explicitly for good measure.

#6) Expire the binlog files after 1 day

This setting tells MySQL how long to keep binlog files on the disk before they get rotated and deleted. Like any other log file we want to make sure they get rotated. If files are not rotated they will eventually fill the disk on your server and cause a service outage.

This can be achieved by setting the expire_logs_days parameter to 1.

Here is a final example of the configuration parameters and values as they could be set in your mysql config file:

After making the necessary configuration changes to your MySQL config file you must restart the mysql server in order for the changes to take effect.

A typical restart command might look like:

You can ensure the configuration is working as expected by verifying a non-empty / non-error response to the following queries: