前明:使用的 chameleon版本 最好与 openGauss版本 一致
1.了解对象迁移限制
有以下几点:
- 由于内核兼容性在持续增强,对象迁移采用先透传再翻译的原则进行,即先直接透传对象创建语句在openGauss端执行,若执行失败,再通过 openGauss-tools-sql-translator进行翻译。
- 迁移的数据库对象体内若存在commit或rollback,将通过sql-translator翻译失败。
- MySQL存储过程中的NO SQL、READS SQL DATA、MODIFIES SQL字段和自定义函数中的CONTAINS SQL、NO SQL、READS SQL DATA、MODIFIES SQL DATA、SECURITY字段无法解析, 将通过sql-translator翻译失败。
- sql-translator借助开源三方件druid进行翻译,更多翻译情况请前往openGauss-tools-sql-translator仓库。
- 对象迁移前需创建和MySQL对象所属definer同名的用户
2.实例说明全流程
(1) 普通用户安装好chameleon后,执行以下三个命令:
chameleon set_configuration_files
cd ~/.pg_chameleon/configuration/
cp config-example.yml default.yml
vi default.yml
(2)编辑default.yml文件(以下内容参数后注释*********************的必改!其他的可选):
# global settings
pid_dir: '~/.pg_chameleon/pid/'
log_dir: '~/.pg_chameleon/logs/'
log_dest: file
log_level: info
log_days_keep: 10
rollbar_key: ''
rollbar_env: ''
dump_json: No
#dump_json 可选项。默认是No,当前开启时,在迁移过程中会在执行chameleon的地方生成json文件记录实时的迁移进度
# type_override allows the user to override the default type conversion
# into a different one.
type_override:
"tinyint(1)":
override_to: boolean
override_tables:
- "*"
# specify the compress properties when creating tables
compress_properties:
compresstype: 0
compress_level: 0
compress_chunk_size: 4096
compress_prealloc_chunks: 0
compress_byte_convert: false
compress_diff_convert: false
# postgres destination connection
pg_conn:
host: "localhost" #openGauss所在ip地址 ****************************
port: "15400" #openGauss端口号 ****************************
user: "zc_pg" #迁移数据的与MySQL同名用户 ****************************
password: "Simple@123" #用户密码 ****************************
database: "zc_db" #要迁移的数据库 ****************************
charset: "utf8"
params:
# maintenance_work_mem: "1G"
# param1: value1
# param2: value2
sources:
mysql:
readers: 4
writers: 4
retry: 3
db_conn:
host: "localhost" #mysql所在ip地址 ****************************
port: "3306" #mysql端口号 ****************************
user: "zc_pg" #迁移数据库的所属用户 ****************************
password: "Simple@123" #用户密码 ****************************
charset: 'utf8'
connect_timeout: 10
schema_mappings:
#delphis_mediterranea: loxodonta_africana
zc_db: sch_zc_db #*****************************************
limit_tables:
#- delphis_mediterranea.foo
skip_tables:
#- delphis_mediterranea.bar
enable_compress: No
compress_tables:
#- delphis_mediterranea.foo
grant_select_to:
#- usr_readonly
- usr_migration
lock_timeout: "120s"
my_server_id: 1
replica_batch_size: 10000
replay_max_rows: 10000
batch_retention: '1 day'
copy_max_memory: "300M"
copy_mode: 'file'
out_dir: /tmp
csv_dir: /tmp
contain_columns: No
column_split: ','
sleep_loop: 1
on_error_replay: continue
on_error_read: continue
auto_maintenance: "disabled"
index_parallel_workers: 2
gtid_enable: false
type: mysql
skip_events:
insert:
- delphis_mediterranea.foo # skips inserts on delphis_mediterranea.foo
delete:
- delphis_mediterranea # skips deletes on schema delphis_mediterranea
update:
keep_existing_schema: No
migrate_default_value: Yes
mysql_restart_config: No
is_create_index: Yes
index_dir: '~/.pg_chameleon/index/'
is_skip_completed_tables: No
with_datacheck: No
slice_size: 100000
csv_files_threshold:
csv_dir_space_threshold:
(3)创建用户及修改database配置
openGauss端:
CREATE USER zc_pg WITH PASSWORD 'Sample@123';
CREATE DATABASE zc_db WITH OWNER zc_pg dbcompatibility='B' ;
#(对象迁移必备!注意是反引号不是单引号!)
set b_compatibility_user_host_auth to on;
create user `zc_pg`@`%` with password 'Sample@123';
grant all privileges to `zc_pg`@`%`;
mysql端
CREATE USER zc_pg;
SET PASSWORD FOR zc_pg=PASSWORD('Simple@123');
GRANT ALL ON *.* TO 'zc_pg';
GRANT RELOAD ON *.* to 'zc_pg';
GRANT REPLICATION CLIENT ON *.* to 'zc_pg';
GRANT REPLICATION SLAVE ON *.* to 'zc_pg';
FLUSH PRIVILEGES;
注意:修改MySQL的配置文件,开启MySQL的复制功能,修改完记得重启mysql。
(一般是/etc/my.cnf或者/etc/my.cnf.d/mariadb-server.cnf)
[mysqld]
binlog_format = ROW
log_bin = mysql-bin
server_id = 1
binlog_row_image = FULL
gtid_mode = ON
(4)初始化迁移过程(步骤将在openGauss侧创建用于复制过程的辅助schema和表)
chameleon create_replica_schema --config default
chameleon add_source --config default --source mysql
(5)复制基础数据
chameleon init_replica --config default --source mysql
注意:若出现lower_case_table_names不一致报错,则参考:https://blog.csdn.net/aachshgd/article/details/138533315?spm=1001.2014.3001.5501
(6)执行迁移
chameleon start_view_replica --config default --source mysql --debug