MySQL Binlog 权限

• Check binlog status

-- https://github.com/alibaba/canal/wiki/AdminGuide
show variables like 'log_bin';
show variables like 'binlog_format';

• MySQL Binlog权限需要三个权限 SELECT, REPLICATION SLAVE, REPLICATION CLIENT

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal';

• 缺乏SELECT权限时，报错为

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:
Access denied for user 'canal'@'%' to database 'binlog'

 

• 缺乏REPLICATION SLAVE权限时，报错为

java.io.IOException: 
Error When doing Register slave:ErrorPacket [errorNumber=1045, fieldCount=-1, message=Access denied for user 'canal'@'%'

　　

• 缺乏REPLICATION CLIENT权限时，报错为

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s) for this operation

• Binlog为什么需要这些权限：

• Select权限代表允许从表中查看数据

Replication client权限代表允许执行show master status,show slave status,show binary logs命令
Replication slave权限代表允许slave主机通过此用户连接master以便建立主从 复制关系

•  创建用户方法：

mysql>create user  'canal'@'%' IDENTIFIED BY 'canal';

mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
Query OK, 0 rows affected (0.03 sec)

mysql> ALTER USER 'canal'@'%' IDENTIFIED WITH mysql_native_password BY 'canal';
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)