ZCBUS-数据交换/同步服务启动前准备(数据库)

zcbus程序的安装步骤和数据同步的基础配置流程，可参考
链接: ZCBUS安装步骤与数据同步或 ZCBUS-异构(MySQL–Oracle)数据同步配置(发布/策略配置/订阅/数据比对)

在开始使用zcbus的数据同步功能前，需要提前对源端和目标端数据进行同步用户创建、用户授权、部分数据库参数检查修改等操作，今天以MySQL、Oracle、PostgreSQL这三款主流数据为例，对同步用户所需要的权限进行简单说明。

数据源

1.Oracle源

1.确认Oracle数据库开启归档模式。
select name,log_mode from v$database;
2.创建数据库用户
create user zcbus identified by "xxxxxxxx";
3.数据库用户授权
grant create session to zcbus;  #连接数据库
grant resource to zcbus;    #授予开发人员的。拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
grant select any table to zcbus; #查询任何表
grant select any dictionary to zcbus; #查询任何字典
grant select_catalog_role to zcbus; #查询部分字典和包
grant EXECUTE_CATALOG_ROLE to zcbus; #可执行部分字典和包
grant select any transaction to zcbus; #选择任何事务
grant execute on dbms_logmnr_d to zcbus; #可执行dbms_logmnr_d包
grant execute on dbms_logmnr to zcbus; #可执行dbms_logmnr包
grant execute on dbms_flashback to zcbus; #可执行dbms_flashback包
grant flashback any table to zcbus; #FLASHBACK表权限
grant LOGMINING to zcbus;  #12c版本以上执行，LOGMINING权限
CDB/PDB模式下同步用户创建及授权：
create user c##zcbus identified by zcbus  container=all;
grant create session to c##zcbus container=all;
grant resource to c##zcbus container=all;
grant select any table to c##zcbus container=all;
grant select any dictionary to c##zcbus container=all;
grant select_catalog_role to c##zcbus container=all;
grant EXECUTE_CATALOG_ROLE to c##zcbus container=all;
grant select any transaction to c##zcbus container=all;
grant execute on dbms_logmnr_d to c##zcbus container=all;
grant execute on dbms_logmnr to c##zcbus container=all;
grant execute on dbms_flashback to c##zcbus container=all;
grant flashback any table to c##zcbus container=all;
grant LOGMINING to c##zcbus container=all;
注：12C以上版本数据库的CDB模式zcbus用户名要以c##开头
4.开启同步表的表级别全列附加日志
alter table user.tablename add supplemental log data(all,primary key,unique,foreign key) columns;
5.开启库级别附加日志
alter database add supplemental log data;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE INDEX) COLUMNS;
alter database add supplemental log data(all) columns;
注：在确保所有同步表的表级全列附加日志开启情况下，库级可仅开启最小附加日志。

2.MySQL源

1.修改my.cnf或者my.ini参数文件
server-id=xxxxxx   #设置成唯一值，5.7及以后的版本需要添加此参数
log-bin=my-bin    #开启binlog
log-slave-updates=1   #如果数据库为slave端需配置此参数，master端不需要配置
binlog_format=row   
2.创建数据库用户
create user zcbus@'%' identified by 'xxxxxxxx';
3.数据库用户授权
grant select,replication client, replication slave on *.* to 'zcbus'@'%';
注：mysql如果设置了binlog-do_db和binlog-ignore-db，可能会导致增量不分析，需要修改/etc/my.cnf参数文件，binlog-do_db中添加上需要发布的库，改完需要重启MySQL库，zcbus发布进程也需要重启。

3.PG源

1.数据库配置
#逻辑级别日志
wal_level = logical
#设置日志保留个数，设置太少了，日志量太大了日志自动删除后，容易读不到日志
wal_keep_segments = 10
#如果需要远程读日志，需要设置，至少为1，就接受1个读日志线程
max_wal_senders = 10
2.配置说明
用postgres系统账号登录pg库，执行如下(数据库命令行执行)：

postgres=# create user zcbus with password  'xxxxxxxx';
postgres=# grant connect on database db01 to zcbus;
postgres=#create schema zcbus AUTHORIZATION zcbus;

create table zcbus.ddl_event_tbl (  
event text,
tag text, 
objid oid,
object_type text,  
schema_name text,  
object_identity text,
query text,
op_time timestamp default now()
);

alter table "zcbus"."ddl_event_tbl" REPLICA IDENTITY FULL;

create or replace function zcbus_func_ddl_command() returns event_trigger as $$  
declare  
  v1 text;
  r record;
begin
  select query into v1 from pg_stat_activity where pid=pg_backend_pid();
  -- RAISE NOTICE 'ddl event:%, command:%', tg_event, tg_tag; 
  if TG_EVENT='ddl_command_end' then  
    SELECT * into r FROM pg_event_trigger_ddl_commands();
    if r.classid > 0 then
      insert into zcbus.ddl_event_tbl(event, tag, objid, object_type, schema_name, object_identity, query)  
        values(TG_EVENT, TG_TAG, r.objid, r.object_type, r.schema_name, r.object_identity, v1);
    end if;
  end if;
  if TG_EVENT='sql_drop' then
    if TG_TAG != 'ALTER TABLE' then
      SELECT * into r FROM pg_event_trigger_dropped_objects();
      insert into zcbus.ddl_event_tbl(event, tag, object_type, schema_name, object_identity, query)  
        values(TG_EVENT, TG_TAG, r.object_type, r.schema_name, r.object_identity, v1);
    end if;
  end if;
end;  
$$ language plpgsql strict;

postgres=# CREATE EVENT TRIGGER zcbus_et_ddl_command on ddl_command_end EXECUTE PROCEDURE zcbus_func_ddl_command();  
postgres=# CREATE EVENT TRIGGER zcbus_et_ddl_drop on sql_drop EXECUTE PROCEDURE zcbus_func_ddl_command();
postgres=#grant select,trigger on table zcbus.ddl_event_tbl  to zcbus;
postgres=#grant execute ON FUNCTION zcbus_func_ddl_command to zcbus;
postgres=#alter user zcbus REPLICATION;

**** 切换到db01库下执行
postgres-# \c db01
db01=# grant select on all tables in schema public to zcbus;
db01=# grant execute on function pg_hba_file_rules to zcbus;

**** 添加新表，需要postgres账号开启表的补充日志模式(切换到同步库执行)，并且要再次执行select权限
db01=#ALTER TABLE public.tb02 REPLICA IDENTITY FULL;
db01=#grant select on all tables in schema public to zcbus;
db01=#grant usage on schema public to zcbus;

**** 添加新表，根据schemaname.tablename，确认postgres账号开启表的补充日志模式状态开启成功：
db01=# select relreplident from pg_class c , pg_tables tab where tab.schemaname = 'schemaname' and c.relname='tablename';

查询结果说明：
d = 默认(主键)
n = 无
f = 所有列
i = 索引的indisreplident被设置或者默认
查询结果为f，则此表满足同步条件，上一步postgres账号开启表的补充日志模式成功
**** 回退方式：

postgres-# drop owned by zcbus cascade;
postgres-# drop user zcbus;

开启表REPLICA IDENTITY参数
\c database_name;
ALTER TABLE schema_name.table_name REPLICA IDENTITY FULL;
生成修改表sql语句参考

SELECT 'ALTER TABLE '||nspname||'.'||relname||' REPLICA IDENTITY FULL;' FROM pg_class c,pg_namespace n where relnamespace = n.oid and c.relkind='r' and nspname='public';
注：表所属用户或者超级用户才有开启REPLICA IDENTITY权限，否则报错：must be owner of table

目标端数据库

1.Oracle目标端

1.创建数据库用户
create user zcbus_user identified by zcbus_passwd;
2.数据库用户授权
grant drop any table,create any table,alter any table,create any index,drop any index,select any table,select any dictionary,resource,create session to zcbus_user;
grant UPDATE ANY TABLE,INSERT ANY TABLE ,DELETE ANY TABLE TO ZCBUS_USER;
注：12C的CDB模式zcbus_user要以c##开头

2.MySQL目标端

1.创建数据库用户
create user zcbus@'%' identified by 'zcbus_passwd';

2.数据库用户授权赋权限
grant select,insert,update,delete,create,drop,reload,references,index,alter,show databases,create temporary tables,execute,create view,show view,create routine,alter routine,event,trigger,create tablespace  on 数据库名.* to zcbus@'%';
flush privileges;

3.PG目标端

所需权限：grant select,insert,update,delete,truncate,references,trigger,create,connect,temporary,execute,usage on database 数据库名 to用户名;