Enabling MySQL's binary logging on macOS

MySQL 5.7.16, installed on macOS Sierra using Homebrew.

With Homebrew installation, it's convenient to access mysql commandline. Whereas using its DMG installer, the mysql commandline can only be accessed using its full path.

mysql --user root --password -e 'SHOW VARIABLES' | grep log_bin

By default, log_bin is disabled

log_bin OFF
log_bin_basename 
log_bin_index 
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON

Create directory where to put the binary logging files and its index:

sudo mkdir /var/log/kel-mysql-log-bin/
sudo touch /var/log/kel-mysql-log-bin/binlogging.index
sudo chown -R `whoami` /var/log/kel-mysql-log-bin/

Check the binary logging directory:

ls -la /var/log/kel-mysql-log-bin/

Output:

drwxr-xr-x   3 jack  wheel   102 Dec  1 19:55 .
drwxr-xr-x  60 root  wheel  2040 Dec  1 19:55 ..
-rw-r--r--   1 jack  wheel     0 Dec  1 19:55 binlogging.index

Create .my.cnf in user's home path:

vim ~/.my.cnf

.my.cnf content:

[mysqld]
log-bin=/var/log/kel-mysql-log-bin/binlogging
server-id=1

Restart MySQL:

mysql.server restart

Check if the binary logging is enabled:

mysql --user root --password -e 'SHOW VARIABLES' | grep log_bin

Output:

log_bin ON
log_bin_basename /var/log/kel-mysql-log-bin/binlogging
log_bin_index /var/log/kel-mysql-log-bin/binlogging.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON

Check the binary logging directory:

ls -la /var/log/kel-mysql-log-bin/

Output:

drwxr-xr-x   4 jack  wheel   136 Dec  1 19:57 .
drwxr-xr-x  60 root  wheel  2040 Dec  1 19:55 ..
-rw-r-----   1 jack  wheel   154 Dec  1 19:57 binlogging.000001
-rw-r-----   1 jack  wheel    44 Dec  1 19:57 binlogging.index

log_bin variable is not something that is being set directly, it's a read-only variable. To turn the log_bin on, the binary logging directory and index must be specified.


Happy Coding!