Enabling the binary log on a MySQL Replication Master

A common task when working with MySQL is to enable binary logging which will allow you to add read only slaves (often a good idea even if you aren’t adding the replication slaves now).

According to the official MySQL documentation, there are only 3 steps required to enable binary logging:

1. assign a unique server-id to the server
2. assign a value to log-bin in the my.cnf file
3. restart the MySQL daemon

Taking care of these first two steps is as simple as adding the following lines to the my.cnf under the [mysqld] section:

server_id = 10
log_bin = mysql-bin

It’s also a good idea to setup a limit on the binary log file size and number of days worth of logs to retain to prevent disk space issues. Common values might be:

expire_logs_days = 2
max_binlog_size = 100M

Note that all changes to the my.cnf require restarting the MySQL daemon to take effect, although some changes can be made on-line, like setting the expire_logs_days value. Any changes must also be made to the my.cnf to persist upon restarts of MySQL.

It is my policy that no changes be made to the my.cnf file unless a restart is possible at the same time, otherwise you may end up with invalid changes (typos, etc..) in your my.cnf and MySQL may not come up the next time you need it.