Flink CDC 时初始化MySQL或者PG同步账号

Flink CDC 时初始化MySQL或者PG同步账号
1.MySQL CDC 账号，小写字符是需要动态调整的，修改密码后需要用新密码重新授权

1.1 创建账号
	CREATE USER 'cdc'@'localhost' IDENTIFIED BY 'password';
	
1.2 授权
	GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'cdc' IDENTIFIED BY 'password';
	
1.3 刷新
	FLUSH PRIVILEGES;

2.PG CDC 账号，小写字符是需要动态调整的

2.1 创建账号
	CREATE USER cdc WITH PASSWORD 'password';
	
2.2 授权
	ALTER ROLE cdc REPLICATION;
	
2.3 授权cdc账号能连接的库
	GRANT CONNECT ON DATABASE test TO cdc;
	
2.4 命令在库内部进行操作
	2.4.1 把当前库public下所有表查询权限赋给用户
	GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc;
	GRANT REFERENCES on ALL TABLES IN SCHEMA public to cdc;
	
	2.4.2 如果已经所有表进行发布,那就不用看这个问题, 此处PUBLICATION名字必须为dbz_publication
	CREATE PUBLICATION DBZ_PUBLICATION FOR ALL TABLES;
		
2.5 验证，执行查询SQL查看是否存在发布的表
	SELECT * FROM PG_PUBLICATION_TABLES;

3.PG其他辅助命令

–创建用户账号
CREATE USER readonly WITH PASSWORD ‘123456’;
–授权
ALTER ROLE readonly replication;
–授权
GRANT CONNECT ON DATABASE test1 to readonly;
–系统权限查看
SELECT * FROM pg_roles;
–对象权限查看
select * from information_schema.table_privileges where grantee = ‘readonly’;
–把当前库public下所有表查询权限赋给用户
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO readonly;
---- 设置发布为true
update pg_publication set puballtables=true where pubname is not null;
– 注意，此处PUBLICATION名字必须为dbz_publication，否则后续flink sql报错must be superuser to create FOR ALL TABLES publication
create publication dbz_publication for ALL TABLES;
–删除一个复制
DROP PUBLICATION dbz_publication;
–查看创建发布的结果
select * from pg_publication;
–查询那些表已经发布
select * from pg_publication_tables;
–查询插槽是否创建成功：
SELECT * FROM pg_replication_slots;
–删除复制槽
select pg_drop_replication_slot(‘flink’);
–查看复制标识 https://www.modb.pro/db/52342
select relreplident from pg_class where relname=‘user_source_1’;
– 更改复制标识包含更新和删除之前值（目的是为了确保表 t_user 在实时同步过程中能够正确地捕获并同步更新和删除的数据变化。如果不执行这两条语句，那么 t_user 表的复制标识可能默认为 NOTHING，这可能导致实时同步时丢失更新和删除的数据行信息，从而影响同步的准确性）
ALTER TABLE user_source_1 REPLICA IDENTITY FULL;