Tutorial: Enable bin logs for Mysql

This tutorial will help you in configuring binary logs for Mysql

Power is always dangerous. It attracts the worst. And corrupts the best.
— Ragnar Lothbrok, Vikings

Login to Mysql and check binary logging status.

[root@kaf3 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.5.62–38.14 Percona Server (GPL), Release 38.14, Revision 7e0e1cc
Copyright © 2009–2018 Percona LLC and/or its affiliates
Copyright © 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> select variable_value as “BINARY LOGGING STATUS (log-bin) from information_schema.global_variables where variable_name=’log_bin’;
+ — — — — — — — — — — — — — — — — — — -+
| BINARY LOGGING STATUS (log-bin) :: |
+ — — — — — — — — — — — — — — — — — — -+
| OFF |
+ — — — — — — — — — — — — — — — — — — -+
1 row in set (0.00 sec)
mysql> select variable_value as “BINARY LOG FORMAT (binlog_format) from information_schema.global_variables where variable_name=’binlog_format’;
+ — — — — — — — — — — — — — — — — — — — -+
| BINARY LOG FORMAT (binlog_format) :: |
+ — — — — — — — — — — — — — — — — — — — -+
| STATEMENT |
+ — — — — — — — — — — — — — — — — — — — -+
1 row in set (0.00 sec)

Check Whether binary logging used or not?

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging
mysql>

Find the binary log directory with the following SQL command: ​

mysql> show variables like ‘datadir’;
+ — — — — — — — -+ — — — — — — — — -+
| Variable_name | Value |
+ — — — — — — — -+ — — — — — — — — -+
| datadir | /var/lib/mysql/ |
+ — — — — — — — -+ — — — — — — — — -+
1 row in set (0.00 sec)

Edit and add the following lines in /etc/my.cnf file

log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row
socket=mysql.sock

Restart Mysql service. Log in and check Mysql Bin logging enabled or not.

mysql> select variable_value as “BINARY LOGGING STATUS (log_bin) from information_schema.global_variables where variable_name=’log_bin’;
+ — — — — — — — — — — — — — — — — — — -+
| BINARY LOGGING STATUS (log_bin) :: |
+ — — — — — — — — — — — — — — — — — — -+
| ON |
+ — — — — — — — — — — — — — — — — — — -+
1 row in set (0.00 sec)

mysql> show binary logs;
+ — — — — — — + — — — — — -+
| Log_name | File_size |
+ — — — — — — + — — — — — -+
| bin.000001 | 107 |
+ — — — — — — + — — — — — -+
1 row in set (0.00 sec)

mysql> select variable_value as “BINARY LOG FORMAT (binlog_format) from information_schema.global_variables where variable_name=’binlog_format’;
+ — — — — — — — — — — — — — — — — — — — -+
| BINARY LOG FORMAT (binlog_format) :: |
+ — — — — — — — — — — — — — — — — — — — -+
| ROW |
+ — — — — — — — — — — — — — — — — — — — -+
1 row in set (0.00 sec)