跳到主要内容
版本:v2.7

Mysql - BINLOG配置方法

Mysql实时配置及赋权

1.修改my.cnf​

添加或打开注销

[mysqld]

log_bin=ON #开启binlog

server-id=123 #设置一个与其他mysql不相同的id

binlog-format=row #设置binlog模式为row

2.修改mysql配置文件后需要重启mysql​

3.修改binlog_row_image为FULL​

set global binlog_row_image ='full';

4.查看开启binlog是否生效、查看binlog模式和binlog_row_image是否为FULL。​

使用root用户进入到mysql

show variables like '%log_bin%';(需要设置为ON)

show global variables like '%binlog_format%';(需要设置为ROW模式)

show variables like '%binlog_row_image%';(需要设置为FULL)

5.创建用户和赋权​

创建用户赋予权限

create user 'dp_test'@'%' identified by '12345678';

grant SELECT on mysql.db to 'dp_test'@'%';

grant SELECT on mysql.tables_priv to 'dp_test'@'%';

grant SELECT on mysql.user to 'dp_test'@'%';

grant SELECT,LOCK TABLES on test.* to 'dp_test'@'%';

grant REPLICATION SLAVE,REPLICATION CLIENT on *.* to 'dp_test'@'%' identified by '12345678';(全局设置,需要*.*)

flush privileges;

TiDB目的地权限配置

grant SELECT on mysql.db  to 'dp_test'@'%';

grant SELECT on mysql.tables_priv to 'dp_test'@'%';

grant SELECT on mysql.user to 'dp_test'@'%';

grant Select,Insert,Update,Delete,Create,Drop,Alter,Execute,Index ON test.* TO 'dp_test'@'%';

flush privileges;