mysql 5.7 binlog_mysql 5.7 enable binlog

0. precondition

a) install mysql 5.7, for  detail please refer my blog post.

1. login mysql and check the variables to see if the binlog has  been enabled.

mysql -h 127.0.0.1 -uroot -proot

show variables like'%log_bin%';

we can see, the log_bin is disabled.

2. Turn on mysql log_bin

sudo vim /etc/mysql/conf.d/mysql.cnf

add the following config segment at the end of the file

# ----------------------------------------------# Enable the binlogfor replication &CDC

#----------------------------------------------# Enable binary replication log and set the prefix, expiration, and log format.

# The prefix is arbitrary, expiration can beshort forintegration tests but would

# be longer on a production system. Row-level info is required foringest to work.

# Server ID is required, but this will vary on production systems

server-id = 223344log_bin= /var/lib/mysql/mysql-bin

expire_logs_days= 3binlog_format=row

#Mysql Packet Size may need to be re-configured. MySQL may have, by default, a ridiculously low allowable packet size.

#To increase it, you’ll need to have the property max_allowed_packet set to a higher number, say 1024M.

max_allowed_packet=1024M

this configration means:

a) the server id is unique for each server, an is required for log_bin capture, it should be a numeric number equal or greater than 0, in my instance I set it to 223344, this number should be unique in the whole cluster.  seems it's a good idea to set it as the ip

address number of the machine install. I fact I have do this in my real production enviroment.

b) the path of the log_bin, this is required  to define the storage location fo the log_bin.

c) the log_bin retention time, in my case, I set it to 3 days.

d. the bin_log format, we should define it as row.

The whole definition file in my case is:

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAMforthe most important data

# cachein MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size=128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly usefulforreporting servers.

# The server defaults are fasterfortransactions and fast SELECTs.

# Adjust sizes as needed, experiment tofindthe optimal values.

# join_buffer_size=128M

# sort_buffer_size=2M

# read_rnd_buffer_size=2M

skip-host-cache

skip-name-resolve

#datadir=/var/lib/mysql

#socket=/var/lib/mysql/mysql.sock

#secure-file-priv=/var/lib/mysql-files

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0#log-error=/var/log/mysqld.log

#pid-file=/var/run/mysqld/mysqld.pid

#----------------------------------------------# Enable the binlogfor replication &CDC

#----------------------------------------------# Enable binary replication log and set the prefix, expiration, and log format.

# The prefix is arbitrary, expiration can beshort forintegration tests but would

# be longer on a production system. Row-level info is required foringest to work.

# Server ID is required, but this will vary on production systems

server-id = 223344log_bin= /var/lib/mysql/mysql-bin

expire_logs_days= 3binlog_format=row

#Mysql Packet Size may need to be re-configured. MySQL may have, by default, a ridiculously low allowable packet size.

#To increase it, you’ll need to have the property max_allowed_packet set to a higher number, say 1024M.

max_allowed_packet=1024M

#set default charactor set to utf-8character-set-server=utf8

collation-server=utf8_unicode_ci

View Code

3. restart mysql service

systemctl restart mysql

after the mysql restarted, we use the command

show variables like '%log_bin%';

and we should found the log_bin is turned on now:  log_bin                         | ON

then we go to file system, and can fould like this :

the log bin files are just there now!

-rw-r----- 1 mysql mysql 177 Apr 20 15:06 mysql-bin.000001

-rw-r----- 1 mysql mysql 154 Apr 20 15:22 mysql-bin.000002

-rw-r----- 1 mysql mysql 64 Apr 20 15:22 mysql-bin.index

In order to read mysql binlog, we need to grant the mysql user the following permissions:

SELECT

RELOAD

SHOW DATABASES

REPLICATION SLAVE

REPLICATION CLIENT

The first three privileges are required when reading a consistent snapshot of the databases. The last two privileges allow the database to read the server’s binlog that is normally used for MySQL replication.

you can see the permmission for the user by execute the following command in mysql terminal.

show grants for cdc-user ; -- cdc-user is the user name I used to do cdc synchronization.

the output is

--------------------------+

| Grants for cdc-user@% |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT RELOAD, PROCESS, ALTER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc-user'@'%' IDENTIFIED BY PASSWORD '*C8C6BD45F62159406C6E0587C42BDE28FFA5F973' |

| GRANT SELECT, LOCK TABLES, SHOW VIEW ON `inventory`.* TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`help_relation` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`time_zone_leap_second` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`time_zone_name` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`proc` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`general_log` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`slow_log` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`func` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`help_topic` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`time_zone_transition_type` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`event` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`time_zone_transition` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`time_zone` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`help_keyword` TO 'cdc-user'@'%' |

| GRANT SELECT ON `mysql`.`help_category` TO 'cdc-user'@'%' |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+

this indicate the user also need select permission for mysql database.

Notes: every time we restart the mysql server instance, it will  call flush logs and then create a new binlog file.