当前位置：首页 > article >正文

OpenGauss MySQL兼容库迁移

article 2025/2/22 22:58:57

OpenGauss 提供了从MySQL到OG的迁移工具，虽然安装在起来及其繁琐，也不怎么好用，不过我现在需要的是，从MySQL到OG的MySQL兼容库，可以理解成从MySQL到MySQL的迁移。

但是很不幸的是，OG的MySQL的兼容模式，与MySQL并不是100%兼容，所以准确的说，是从MySQL迁移到一个不伦不类的MySQL库。但是怎么办，工作还是要做。

我迁移的目标是OpenGauss 5.0.0，好像 6.0.0 的MySQL兼容性增强了一些，但是我试用了一下，还是不能做到mysqldump导出的文件直接导入。最终选择的方案不是用OG提供的迁移工具，因为安装配置起来及其繁琐，而且没有MySQL到OG兼容MySQL库的选项，其实OG的工具是用的开源软件confluent，也难怪没有MySQL到OG兼容MySQL库的选项。我的迁移方案是mysqldump导出DDL和INSERT，然后在OG的MySQL兼容模式中导入。

尝试过直接导入OG 5.0.0的MySQL兼容模式，有不少错误，这些也是迁移工作的重点。

1、mysqldump是按表名顺序导出表定义，但是如果表之间有外键依赖，这样建表时就会报错，在MySQL中可以设置参数 set foreign_key_check=0，这样在创建表以及导入数据时就不会检查依赖，但是OG 5.0.0的MySQL兼容模式不支持这个参数，OG 6.0.0支持这个参数但是只支持导入数据时不检查，不支持建表时不检查。也就是说，还是要自己想办法。

我的方案是，对mysqldump出的脚本进行处理，首先要明确，mysqldump导出的不是一个二进制文件，而是一个SQL脚本，而且可以通过命令行参数控制mysqldump只导出表定义，或者只导出插入数据的INSERT语句，首先只导出表定义，然后提取和删除表定义中的索引、UNIQUE约束的外键约束，表定义中只有列定义和主键定义，这些OG的MySQL兼容模式足以应对，建表时几乎不会出错，如果出错再具体问题具体分析。

然后用提取的索引信息构建CREATE INDEX语句，用提取的UNIQUE约束信息构建ALTER TABLE UNIQUE语句，用提取的外键信息构建ALTER TABLE FOREIGN KEY语句，写到另外的文件中，这些索引和外键的创建，考虑到导入性能，应该在导入数据之后执行。

2、mysqldump单独导出导入数据的INSERT语句，如果加入命令行选项 --skip-extended-insert 则会为每一条记录生成一个INSERT语句，否则一条INSERT语句中包含多行记录。在OG的MySQL兼容库执行INSERT时也遇到了一些问题：

1）字符串类型varchar、text中的转义字符OG的MySQL兼容库无法处理，实际上只能按照PG的方式处理，就是在字符串的引号前加E，即MySQL导出

INSERT INTO T1 VALUES (1, 'Hello \'World\'\n');

应该改为

INSERT INTO T1 VALUES (1, E'Hello \'World\'\n');

这样OG的MySQL兼容库才能处理，用sed实现这个修改。

2）可能会有blob、longblob等类型，在我测试的数据库中，这种类型用来存储日志，通常是大量的文本，默认也是导出为文本，然后放在 _binary ''的单引号内，这种文本内也包含了大量的转义字符，而OG的MySQL兼容模式也不能很好的处理。网上的解决方案是mysqldump时用--hex-blob，将lob类型数据全部转为十六进制表示的字节文本，即：

INSERT INTO T1 VALUES (1, _binary '{\"Hello\" \'World\'}\n');

转为

INSERT INTO T1 VALUES (1, 0x7B22726571756573744964223A31);

然后再用sed改为

INSERT INTO T1 VALUES (1, decode('7B22726571756573744964223A31', 'hex'));

3）在测试中发现，如果是空的 longblob 或 blob 类型，mysqldump 会生成 _binary ''，这个OG的MySQL兼容库也不能处理，于是我转为了 ''，即:

INSERT INTO T1 VALUES (1, _binary '');

改为

INSERT INTO T1 VALUES (1, '');

4）text 等字符串类型中可能包含 '\0' ，由于前面已经将这些字符串前面加上了E，'\0' 也应该被处理，然而OG的MySQL兼容模式不能将'\0'转为unicode字符，所以我的做法是删除字符串中的 '\0'，这算是一种具体问题具体分析。

综合上面四点，对mysqldump导出的INSERT脚本进行处理的sed命令为：

sed -E "s/,'/,E'/g" ambari-insert.sql |sed "s/,0x\([0-9A-F]*\)/,decode('\1', 'hex')/g"|sed "s/_binary ''/''/g"|sed "s/\\\0//g" > 1.sql

导出DDL 和 INSERT 脚本的mysqldump命令：

mysqldump --host 172.32.153.186 -u root --port 3306 -p"Bigdata_123" ambari --no-data > ambari-ddl.sql
mysqldump --host 172.32.153.186 -u root --port 3306 -p"Bigdata_123" ambari --no-create-info --hex-blob --compact > ambari-insert.sql
mysqldump --host 172.32.153.186 -u root --port 3306 -p"Bigdata_123" ambari --no-create-info --hex-blob --compact --skip-extended-insert > ambari-insert.sql

注意 --skip-extended-insert 这种方式利于调试，哪一条记录导入出错了，在文件中很容易定位。

在MySQL兼容库ambari中执行脚本的命令：

gsql -p31001 -Upostgres -WPostgres@123 -c "drop database ambari;create database ambari DBCOMPATIBILITY 'B';"
gsql -p31001 -Upostgres -WPostgres@123 -dambari -f create_table.sql
gsql -p31001 -Upostgres -WPostgres@123 -dambari -f ambari-insert.sql

处理 mysqldump导出DDL脚本的Python代码，其中sqlparse模块可以提取DDL脚本中的一个一个CREATE TABLE 语句，但是再细的分析就无能为力了，还需要re：

import sqlparse
import re

pattern0 = r"CREATE TABLE\s+(`\w+`)\s+\("
pattern1 = r"\s\sCONSTRAINT\s+(`\w+`)\s+FOREIGN KEY\s+\((.*)\)\s+REFERENCES\s+(`\w+`)\s+\((.*)\).*"
pattern2 = r"\s\sUNIQUE KEY\s+(`\w+`)\s+\((.*)\).*"
pattern3 = r"\s\sKEY\s+(`\w+`)\s+\((.*)\).*"

name_set = set()

f_ct=open("/mnt/disk01/nl/hadb-opengauss/create_table.sql","w")
f_fk=open("/mnt/disk01/nl/hadb-opengauss/foreign_key.sql","w")
f_idx=open("/mnt/disk01/nl/hadb-opengauss/index.sql","w")
f_ddl=open("/mnt/disk01/nl/hadb-opengauss/ambari-ddl.sql", "r")

def extract_index(create_table_stmt):
    match0=re.search(pattern0, create_table_stmt)
    match3=re.findall(pattern3, create_table_stmt)
    create_table_name = match0.group(1)
    statements="\n"
    for idx in match3:
        if not idx[0] in name_set:
            statements+="CREATE INDEX {} ON {} ({});\n"\
                         .format(idx[0], create_table_name, idx[1])
            name_set.add(idx[0])
        else:
            new_name = '`' + idx[0].strip('`') + '_' + create_table_name.strip('`') + '`'
            statements+="CREATE INDEX {} ON {} ({});\n"\
                         .format(new_name, create_table_name, idx[1])
    return statements


def extract_unique(create_table_stmt):
    match0=re.search(pattern0, create_table_stmt)
    match2=re.findall(pattern2, create_table_stmt)
    create_table_name = match0.group(1)
    statements="\n"
    for uq in match2:
        if not uq[0] in name_set:
            statements+="ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({});\n"\
                         .format(create_table_name, uq[0], uq[1])
            name_set.add(uq[0])
        else:
            new_name = '`' + uq[0].strip('`') + '_' + create_table_name.strip('`') + '`'
            statements+="ALTER TABLE {} ADD CONSTRAINT {} UNIQUE ({});\n"\
                         .format(create_table_name, new_name, uq[1])
    return statements


def extract_foreignkey(create_table_stmt):
    match0=re.search(pattern0, create_table_stmt)
    match1=re.findall(pattern1, create_table_stmt)
    create_table_name = match0.group(1)
    statements="\n"
    for fk in match1:
        statements+="ALTER TABLE {} ADD CONSTRAINT {} FOREIGN KEY ({}) REFERENCES {} ({});\n"\
                     .format(create_table_name, fk[0], fk[1], fk[2], fk[3])
    return statements

def remove_constraints(create_table_stmt):
    s1=re.sub(pattern1, '', create_table_stmt)
    s2=re.sub(pattern2, '', s1)
    s3=re.sub(pattern3, '', s2)
    res=re.sub(r',\n+\) ENGINE=', '\n) ENGINE=', s3)
    return res

def main():
    for stmt in sqlparse.parsestream(f_ddl):
        if stmt.get_type() == 'CREATE':
            f_idx.write(extract_index(stmt.__str__()))
            f_idx.write(extract_unique(stmt.__str__()))
            f_fk.write(extract_foreignkey(stmt.__str__()))
            f_ct.write(remove_constraints(stmt.__str__()))
        else:
            f_ct.write(stmt.__str__())

main()