版本: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;