易语言网站建设,购买建立网站费怎么做会计凭证,wordpress如何换成经典编辑器,祝贺网站改版本文作者#xff1a;赵黎明#xff0c;爱可生 MySQL DBA 团队成员#xff0c;熟练掌握Oracle、MySQL等数据库系统#xff0c;擅长对数据库性能问题的诊断#xff0c;以及事务与锁机制的分析等。负责解决客户在MySQL及爱可生自主研发的DMP平台日常运维中所遇到的各种问题赵黎明爱可生 MySQL DBA 团队成员熟练掌握Oracle、MySQL等数据库系统擅长对数据库性能问题的诊断以及事务与锁机制的分析等。负责解决客户在MySQL及爱可生自主研发的DMP平台日常运维中所遇到的各种问题并且对开源数据库相关的技术抱有浓厚的兴趣。 背景
在将MySQL迁移到OceanBase Oracle模式的过程中我们经常会面临如何在OB Oracle中实现自增主键的问题。OB社区中已有一些文章为我们提供了相应的解决方案可以作为我们迁移过程中的参考。
本文将从实践角度出发验证并总结一个比较可行的实施方案。
方案一
我们将通过创建创建自定义序列的方式来实现自增主键。
MySQL 端创建测试表
zlm10.186.60.68 [zlm] desc t;
--------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
--------------------------------------------------------
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | YES | | NULL | |
--------------------------------------------------------
2 rows in set (0.00 sec)zlm10.186.60.68 [zlm] select * from t;
----------
| id | name |
----------
| 1 | a |
| 2 | b |
| 3 | c |
----------
3 rows in set (0.00 sec)使用 DBCAT 导出表结构
DBCAT 是 OB 提供的命令行工具主要用于异构数据库迁移场景中非表对象的 DDL 导出和转换如 Oracle 中的序列、函数、存储过程、包、触发器、视图等对象。
cd /opt/oceanbase_package/tools/dbcat-1.9.1-RELEASE/bin
./dbcat convert -H 10.186.60.68 -P 3332 --userzlm --passwordzlm --databasezlm --no-schema --no-quote --from mysql57 --to oboracle32x --table t --file/tmp
Parsed args:
[--no-quote] true
[--no-schema] true
[--table] [t]
[--host] 10.186.60.68
[--port] 3332
[--user] zlm
[--password] ******
[--database] zlm
[--file] /tmp
[--from] mysql57
[--to] oboracle32x
2023-08-16 14:41:58 INFO Init convert config finished.
2023-08-16 14:41:58 INFO {dataSource-1} inited
2023-08-16 14:41:58 INFO Init source druid connection pool finished.
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xColumnConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xIndexConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPrimaryKeyConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xUniqueKeyConverter
2023-08-16 14:41:58 INFO Register c.o.o.d.m.c.m.MySql56ObOracle22xPartitionConverter
2023-08-16 14:41:59 INFO Load meta/mysql/mysql56.xml, meta/mysql/mysql57.xml successed
2023-08-16 14:42:09 INFO Query 0 dependencies elapsed 17.35 ms
2023-08-16 14:42:09 INFO Query table: t attr finished. Remain: 0
2023-08-16 14:42:09 INFO Query 1 tables elapsed 69.71 ms
2023-08-16 14:42:09 WARN Include types is empty. Ignore schema: ZLM
2023-08-16 14:42:09 WARN Skip to compare/convert sequences as SEQUENCE is unsupported
2023-08-16 14:42:09 INFO Starting to convert schema to path: /tmp/dbcat-2023-08-16-144209/ZLM
2023-08-16 14:42:09 INFO Successed to generate report in the path: /tmp/dbcat-2023-08-16-144209/ZLM-conversion.html
2023-08-16 14:42:09 INFO {dataSource-1} closing ...
2023-08-16 14:42:09 INFO {dataSource-1} closed
cd /tmp/dbcat-2023-08-16-144209/ZLM
cat TABLE-schema.sql
CREATE TABLE t (id NUMBER(19,0),name VARCHAR2(30 BYTE),CONSTRAINT PRIMARY PRIMARY KEY (id)
);-- CREATE SEQUENCE xxx START WITH 1 INCREMENT BY 1 ... for tDBCAT 会对目标表的表结构做转换使其符合 Oracle 的语法并在导出的 DDL 语句中写入一行创建序列的伪 SQL 可见此工具也是建议创建序列来处理 MySQL 自增列的。
在 OB 目标端创建序列
对于含有自增列的每个表都需要创建一个序列与之对应。创建序列时建议以 SEQ_表名_字段名 的方式命名。当不指定序列的 CYCLE 和 ORDER 属性时其默认值都是 N 即不循环不排序。当不指定序列的 CACHE 属性时默认缓存 20 个序列。字段 MIN_VALUE 对应创建序列时 MIN_VALUE 属性的值。字段 LAST_NUMBER 对应创建序列时 START WITH 属性的值。
ZLM[ZLM] CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 1;
Query OK, 0 rows affected (0.03 sec)ZLM[ZLM] SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNERZLM;
---------------------------------------------------------------------------
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
---------------------------------------------------------------------------
| SEQ_T_ID | 1 | 1 | N | N | 20 |
---------------------------------------------------------------------------1 row in set (0.01 sec)ZLM[ZLM] drop sequence SEQ_T_ID;
Query OK, 0 rows affected (0.03 sec)ZLM[ZLM] CREATE SEQUENCE SEQ_T_ID MINVALUE 1 MAXVALUE 999999 INCREMENT BY 1 START WITH 10;
Query OK, 0 rows affected (0.03 sec)ZLM[ZLM] SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNERZLM;
---------------------------------------------------------------------------
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
---------------------------------------------------------------------------
| SEQ_T_ID | 1 | 10 | N | N | 20 |
---------------------------------------------------------------------------1 row in set (0.03 sec)在 OB 目标端建表
基于第 1 步获取的 DDL 和第 2 步创建的序列。
ZLM[ZLM] CREATE TABLE ZLM.T (- ID NUMBER(19,0) DEFAULT SEQ_T_ID.NEXTVAL,- NAME VARCHAR2(30 BYTE),- CONSTRAINT PRIMARY PRIMARY KEY (ID));
Query OK, 0 rows affected (0.15 sec)通常表结构及数据都是通过 OMS 来完成迁移的很少会直接用 DBCAT 生成的 DDL 建表语句去目标端手动建表除了一些较特殊的场景如以上这种给字段增加缺省属性为序列值情况。
建表时注意应将表名和字段名都改为大写因为 Oracle 中严格区分数据库对象的大小写。ID 列的 DEFAULT 值指定为第 2 步所创建序列的下一个值即SEQ_T_ID.NEXTVAL 使用 DataX 迁移数据
DataX 是阿里开源的离线数据同步工具支持多种异构数据源可以通过 OB 的 Reader 和 Writer 插件实现 OB 与异构数据库之间的数据迁移。
-- 创建 DataX 配置文件存放在 dataX 的 ./job 目录下
cat t.json
{job: {setting: {speed: {channel: 4 },errorLimit: {record: 0,percentage: 0.1}},content: [{reader: {name: mysqlreader,parameter: {username: zlm,password: zlm,column: [*],connection: [{table: [t],jdbcUrl: [jdbc:mysql://10.186.60.68:3332/zlm?useUnicodetruecharacterEncodingutf8]}]}},writer: {name: oceanbasev10writer,parameter: {obWriteMode: insert,column: [*],preSql: [truncate table T],connection: [{jdbcUrl: ||_dsc_ob10_dsc_||jingbo_ob:ob_oracle||_dsc_ob10_dsc_||jdbc:oceanbase://10.186.65.22:2883/ZLM?useLocalSessionStatetrueallowBatchtrueallowMultiQueriestruerewriteBatchedStatementstrue,table: [T]}],username: ZLM,password:zlm,writerThreadCount:10,batchSize: 1000,memstoreThreshold: 0.9}}}]}
}-- 执行数据迁移
./bin/datax.py job/t.json
DataX (20220610-external), From Alibaba !
Copyright (C) 2010-2017, Alibaba Group. All Rights Reserved.
full db is not specified.
schema sync is not specified.
java -server -Xms4g -Xmx16g -XX:HeapDumpOnOutOfMemoryError -XX:HeapDumpPath/home/admin/datax3/log -DENGINE_VERSION20220610-external -Xms4g -Xmx16g -XX:HeapDumpOnOutOfMemoryError -XX:HeapDumpPath/home/admin/datax3/log -Dloglevelinfo -Dproject.namedi-service -Dfile.encodingUTF-8 -Dlogback.statusListenerClassch.qos.logback.core.status.NopStatusListener -Djava.security.egdfile:///dev/urandom -Ddatax.home/home/admin/datax3 -Dlogback.configurationFile/home/admin/datax3/conf/logback.xml -classpath /home/admin/datax3/lib/*:. -Dlog.file.namein_datax3_job_t_json com.alibaba.datax.core.Engine -mode standalone -jobid -1 -job /home/admin/datax3/job/t.json -fulldb false -schema false
2023-08-16 14:58:41.088 [main] INFO Engine - running job from /home/admin/datax3/job/t.json
2023-08-16 14:58:41.374 [main] INFO VMInfo - VMInfo# operatingSystem class sun.management.OperatingSystemImpl
2023-08-16 14:58:41.382 [main] INFO Engine - the machine info
略...2. record average count and max count task info :
PHASE | AVERAGE RECORDS | AVERAGE BYTES | MAX RECORDS | MAX RECORDS BYTES | MAX TASK ID | MAX TASK INFO
READ_TASK_DATA | 3 | 6B | 3 | 6B | 0-0-0 | t,jdbcUrl:[jdbc:mysql://10.186.60.68:3332/zlm]
2023-08-16 14:58:45.189 [job-0] INFO MetricReportUtil - reportJobMetric is turn off
2023-08-16 14:58:45.189 [job-0] INFO StandAloneJobContainerCommunicator - Total 3 records, 6 bytes | Speed 3B/s, 1 records/s | Error 0 records, 0 bytes | All Task WaitWriterTime 0.000s | All Task WaitReaderTime 0.000s | Percentage 100.00%
2023-08-16 14:58:45.190 [job-0] INFO LogReportUtil - report datax log is turn off
2023-08-16 14:58:45.190 [job-0] INFO JobContainer -
任务启动时刻 : 2023-08-16 14:58:41
任务结束时刻 : 2023-08-16 14:58:45
任务总计耗时 : 3s
任务平均流量 : 3B/s
记录写入速度 : 1rec/s
读出记录总数 : 3
读写失败总数 : 0
2023-08-16 14:58:45.190 [job-0] INFO PerfTrace - reset PerfTrace.验证效果
验证主键列能否实现自增。
SYS[ZLM] select * from t;
----------
| ID | NAME |
----------
| 1 | a |
| 2 | b |
| 3 | c |
----------
3 rows in set (0.01 sec)SYS[ZLM] insert into t(name) values(d);
Query OK, 1 row affected (0.02 sec)SYS[ZLM] select * from t;
----------
| ID | NAME |
----------
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
----------
4 rows in set (0.00 sec)新插入的数据每次都会先获取 ID 列上序列的 NEXTVAL 值于是就实现了主键自增的需求。
使用自定义序列的 NEXTVAL 作为主键列的 DEFAULT 值后不必关心源端表上记录的自增列最大值将表迁移过去后直接插入新数据时不会与原来的数据冲突。
方案二
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列的方案是否好用先来看一个测试吧
-- 删除并重建测试表
ZLM[ZLM] DROP TABLE T;
Query OK, 0 rows affected (0.10 sec)
ZLM[ZLM] CREATE TABLE ZLM.T (- ID NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999,- NAME VARCHAR2(30 BYTE),- CONSTRAINT PRIMARY PRIMARY KEY (ID));
Query OK, 0 rows affected (0.15 sec)-- 查看序列
ZLM[ZLM] SELECT SEQUENCE_NAME,MIN_VALUE,LAST_NUMBER,CYCLE_FLAG,ORDER_FLAG,CACHE_SIZE FROM DBA_SEQUENCES WHERE SEQUENCE_OWNERZLM;
-----------------------------------------------------------------------------
| SEQUENCE_NAME | MIN_VALUE | LAST_NUMBER | CYCLE_FLAG | ORDER_FLAG | CACHE_SIZE |
-----------------------------------------------------------------------------
| SEQ_T_ID | 1 | 21 | N | N | 20 |
| ISEQ$$_50034_16 | 1 | 1 | N | N | 20 |
-----------------------------------------------------------------------------
2 rows in set (0.00 sec)# 此时系统自动创建了名为 ISEQ$$_50034_16 的序列其他默认值与自定义创建的序列一致-- 查看表结构
ZLM[ZLM] desc t;
---------------------------------------------------------
| FIELD | TYPE | NULL | KEY | DEFAULT | EXTRA |
---------------------------------------------------------
| ID | NUMBER(19) | NO | PRI | SEQUENCE.NEXTVAL | NULL |
| NAME | VARCHAR2(30) | YES | NULL| NULL | NULL |
---------------------------------------------------------
2 rows in set (0.02 sec)# 注意ID 列的 DEFAULT 值为 SEQUENCE.NEXTVAL 而不是 ISEQ$$_50034_16.NEXTVAL-- 重新导入数据
./bin/datax.py job/t.json
略 ...-- 插入数据
ZLM[ZLM] insert into t(name) values(d);
ORA-00001: unique constraint 1 for key PRIMARY violatedZLM[ZLM] insert into t(name) values(d);
ORA-00001: unique constraint 2 for key PRIMARY violatedZLM[ZLM] insert into t(name) values(d);
ORA-00001: unique constraint 3 for key PRIMARY violatedZLM[ZLM] insert into t(name) values(d);
Query OK, 1 row affected (0.01 sec)ZLM[ZLM] select ISEQ$$_50034_16.CURRVAL from dual;
---------
| CURRVAL |
---------
| 4 |
---------1 row in set (0.00 sec)表中有 3 条数据当执行插入时START WITH 实际上还是从默认值 1 开始的。
每次执行插入ID 都会获取序列的 NEXTVAL 值直到执行至第 4 次未与表中已有记录冲突才能插入成功。
对于这种场景解决方案有两种不过都比较繁琐
插入记录前先获取当前序列的 NEXTVAL 值需多次执行执行次数 源端表记录数。创建序列时根据源端表上自增列最大值来指定 START WITH 属性。
注意使用 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列无法直接删除会报错。ORA-32794: cannot drop a system-generated sequence 获取表中自增列最大值
方法 1MAX 函数
zlm10.186.60.68 [zlm] SELECT MAX(id)1 as AUTO_INCREMENT FROM t;
----------------
| AUTO_INCREMENT |
----------------
| 4 |
----------------1 row in set (0.00 sec)方法 2系统视图
zlm10.186.60.68 [zlm] select AUTO_INCREMENT from information_schema.tables where table_namet;
----------------
| AUTO_INCREMENT |
----------------
| 4 |
----------------1 row in set (0.00 sec)方法3show create table 命令
zlm10.186.60.68 [zlm] show create table t\G
*************************** 1. row ***************************Table: t
Create Table: CREATE TABLE t (id bigint(20) NOT NULL AUTO_INCREMENT,name varchar(10) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8
1 row in set (0.00 sec)脚本初始化序列的一个示例
-- 删除并重建表
ZLM[ZLM] drop table t;
Query OK, 0 rows affected (0.02 sec)ZLM[ZLM] CREATE TABLE ZLM.T (- ID NUMBER(19,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 999999,- NAME VARCHAR2(30 BYTE),- CONSTRAINT PRIMARY PRIMARY KEY (ID));
Query OK, 0 rows affected (0.04 sec)-- 导入数据
./bin/datax.py job/t.json
略 ...-- 执行脚本并确认返回结果正常
[root10-186-65-73 ~]# cat init_sequence.sh
#!/bin/bash## 获取当前表自增列最大值
i$(obclient -h10.186.60.68 -P3332 -uzlm -pzlm -Nse SELECT MAX(id)1 FROM zlm.t; 2/dev/null | head -1)## 循环执行SQL初始化序列值
for ((j1; j$i; j))
doobclient -h10.186.65.43 -P2883 -uZLMob_oracle#bobo_ob:1675327512 -pzlm -A -c -DZLM -Nse select ISEQ\$\$_50037_16.nextval from dual; 1/dev/null 21
done
[root10-186-65-73 ~]# sh init_sequence.sh
[root10-186-65-73 ~]# echo $?
0-- 执行插入
ZLM[ZLM] insert into t(name) values(d);
Query OK, 1 row affected (0.01 sec)
序列经过初始化处理后当完成数据导入并直接插入新增记录时就不会再产生唯一性冲突的报错了。同样地先用脚本获取到自增列的最大值在创建序列时指定 START WITH 与自增列最大值一致也可以解决以上问题这里不展开了。
总体而言GENERATED BY DEFAULT AS IDENTITY 属性创建方案二的序列不如自定义序列方案一好用。
总结
本文验证并阐述了在 OB Oracle 中实现自增主键的两种方法创建自定义序列和利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列。
方案一
创建自定义序列的时需要为每张有自增列的表创建一个单独的序列序列名建议与表名关联但无需关注 START WITH 的取值。当插入新记录时会自动去获取下一个可用的序列值。
方案二
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列时存在一些限制
因序列由系统自动创建并管理需要查询系统视图才能获取序列名无法与业务表名对应。创建序列时需要根据自增列最大值来指定 START WITH 的取值当有大量表需要处理时较繁琐。
利用 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列名在内部有一个计数器会累计增加即使删除了原来的序列原有的名字也不会被重用。删除表时会自动清理由 GENERATED BY DEFAULT AS IDENTITY 属性生成的序列直接删除该序列会报错但不会影响之前创建的其他自定义序列。
采用 GENERATED BY DEFAULT AS IDENTITY 属性生成序列的方案时还要额外考虑源端待迁移表当前自增列最大值的问题这无疑增加了迁移的复杂度。
综上所述更推荐使用自定义序列实现自增主键的方案。
