复制是众所周知的功能之一，可让您创建数据库的相同副本。 几乎所有关系数据库管理系统（RDBMS）都支持它。 复制具有明显的好处，尤其是高可用性和负载平衡。 但是，如果需要在结构不同的两个数据库（例如MySQL和PostgreSQL）之间复制，该怎么办？ 是否可以将更改从MySQL数据库连续复制到PostgreSQL数据库？ 这个问题的答案是pg_chameleon复制工具 。
对于连续更改复制，pg_chameleon使用MySQL复制库，该库允许您从MySQL数据库获取行的逻辑副本，这些行将转换为jsonb对象。 Postgres中的pl / pgsql函数对jsonb对象进行解码，并在Postgres数据库中再现所做的更改。 要配置这种复制类型，必须将MySQL数据库的binlog_format变量设置为ROW（字符串）。
设置此工具之前需要了解以下几点：
- 需要复制的表必须具有主键。
- 该工具适用于9.5以上的PostgreSQL版本和5.5以上的MySQL系统版本
- 要配置此复制，必须将binlog_format变量设置为ROW。
- Python版本必须高于3.3
开始复制时，pg_chameleon以CSV格式从MySQL检索数据，并细分为一定长度的组，以避免内存过载。 使用COPY命令将此数据刷新到Postgres。 如果复制失败，则执行INSERT命令，这可能会减慢该过程。 如果INSERT命令失败，则该行将丢失。
为了从MySQL复制更改，pg_chameleon模拟MySQL副本（从属）的行为。 这将在Postgres中创建一个方案，执行初始数据加载，连接到MySQL复制协议，并复制Postgres表中的行。 同时，相应的Postgres函数提供字符串解码和更改。 这类似于将传输日志存储在Postgres表中并将其应用于Postgres模式。 不需要使用任何数据描述语言创建Postgres数据库模式。 对于配置复制时指定的表，pg_chameleon工具会自动执行此操作。 如果需要以特定方式转换任何类型，则可以在配置文件中指定此类型。
以下是您可以尝试的练习。 如果它完全满足您的要求，请使用提供的选项。 我们在CentOS Linux版本7.4上执行了这些测试。
环境准备
为MySQL配置Percona服务器
安装MySQL 5.7版并添加适当的复制选项。
在本练习中，我使用YUM存储库安装了适用于MySQL 5.7版的Percona Server。
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum install Percona-Server-server-57 echo "mysql ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers usermod -s /bin/bash mysql sudo su - mysql
对于pg_chameleon，必须在my.cnf文件（带有MySQL服务器参数的文件）中配置以下参数。 您可以将以下选项添加到/etc/my.cnf文件中
binlog_format= ROW binlog_row_image=FULL log-bin = mysql-bin server-id = 1
现在，在my.cnf文件中包含上述参数之后，启动MySQL服务器。
$ service mysql start
从mysqld.log文件中获取root帐户的临时密码，并使用mysqladmin命令重置root密码。
$ grep "temporary" /var/log/mysqld.log $ mysqladmin -u root -p password 'Secret123!'
现在，连接到您自己的MySQL数据库实例并创建一个示例模式/表。 我还创建了一个emp表进行验证。
$ wget http://downloads.mysql.com/docs/sakila-db.tar.gz $ tar -xzf sakila-db.tar.gz $ mysql -uroot -pSecret123! < sakila-db/sakila-schema.sql $ mysql -uroot -pSecret123! < sakila-db/sakila-data.sql $ mysql -uroot -pSecret123! sakila -e "create table emp (id int PRIMARY KEY, first_name varchar(20), last_name varchar(20))"
创建一个用户以使用pg_chameleon工具配置复制，并按照以下步骤为其授予适当的权限。
$ mysql -uroot -p create user 'usr_replica'@'%' identified by 'Secret123!'; GRANT ALL ON sakila.* TO 'usr_replica'@'%'; GRANT RELOAD, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'usr_replica'@'%'; FLUSH PRIVILEGES;
在MySQL服务器（'usr_replica'@'％'）上创建用户时，可能有必要用运行pg_chameleon的服务器的相应IP地址或主机名替换“％”字符。
配置PostgreSQL
安装PostgreSQL并运行数据库的副本。
请按照以下步骤安装PostgreSQL版本10.x。
yum install https://yum.postgresql.org/10/redhat/rhel-7.4-x86_64/pgdg-centos10-10-2.noarch.rpm yum install postgresql10* su - postgres $/usr/pgsql-10/bin/initdb $ /usr/pgsql-10/bin/pg_ctl -D /var/lib/pgsql/10/data start
从以下日志中可以看到，您需要在PostgreSQL中创建一个用户，pg_chameleon可以使用该用户将修改后的数据写入PostgreSQL。 还创建一个目标数据库。
postgres=# CREATE USER usr_replica WITH ENCRYPTED PASSWORD 'secret'; CREATE ROLE postgres=# CREATE DATABASE db_replica WITH OWNER usr_replica; CREATE DATABASE
使用pg_chameleon安装和配置复制的步骤
阶段1。在本练习中，我按照以下步骤安装了Python解释器3.6版和pg_chameleon 2.0.8版。 如果您已经安装了所需版本的Python解释器，则可以跳过安装它的步骤。 如果操作系统默认不包含Python版本3.x，我们可以创建一个虚拟环境。
yum install gcc openssl-devel bzip2-devel wget cd /usr/src wget https://www.python.org/ftp/python/3.6.6/Python-3.6.6.tgz tar xzf Python-3.6.6.tgz cd Python-3.6.6 ./configure --enable-optimizations make altinstall python3.6 -m venv venv source venv/bin/activate pip install pip --upgrade pip install pg_chameleon
第2阶段。此工具需要一个配置文件（用于存储有关源服务器和目标服务器的信息）以及一个用于存储日志的文件夹。 要让pg_chameleon创建配置文件模板和相应的文件夹，请使用以下命令。
$ chameleon set_configuration_files
执行此命令后，将显示以下结果。 它们显示此命令在您运行的地方创建了几个文件夹和文件。
creating directory /var/lib/pgsql/.pg_chameleon creating directory /var/lib/pgsql/.pg_chameleon/configuration/ creating directory /var/lib/pgsql/.pg_chameleon/logs/ creating directory /var/lib/pgsql/.pg_chameleon/pid/ copying configuration example in /var/lib/pgsql/.pg_chameleon/configuration//config-example.yml
将样本配置文件复制到另一个文件，例如default.yml
$ cd .pg_chameleon/configuration/ $ cp config-example.yml default.yml
这是我的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: '' # type_override allows the user to override the default type conversion into a different one. type_override: "tinyint(1)": override_to: boolean override_tables: - "*" #postgres destination connection pg_conn: host: "localhost" port: "5432" user: "usr_replica" password: "secret" database: "db_replica" charset: "utf8" sources: mysql: db_conn: host: "localhost" port: "3306" user: "usr_replica" password: "Secret123!" charset: 'utf8' connect_timeout: 10 schema_mappings: sakila: sch_sakila limit_tables: # - delphis_mediterranea.foo skip_tables: # - delphis_mediterranea.bar grant_select_to: - usr_readonly lock_timeout: "120s" my_server_id: 100 replica_batch_size: 10000 replay_max_rows: 10000 batch_retention: '1 day' copy_max_memory: "300M" copy_mode: 'file' out_dir: /tmp sleep_loop: 1 on_error_replay: continue on_error_read: continue auto_maintenance: "disabled" gtid_enable: No type: mysql skip_events: insert: # - delphis_mediterranea.foo #skips inserts on the table delphis_mediterranea.foo delete: # - delphis_mediterranea #skips deletes on schema delphis_mediterranea update:
步骤3.使用以下命令创建副本（目标数据库）：
$ chameleon create_replica_schema --debug
上面的命令在.pg_chameleon / configuration / default.yml文件中指定的PostgreSQL数据库中创建一个模式和九个表。 这些表是控制从源数据库到目标的复制所必需的。 在下一本杂志中也可以看到相同的内容。
db_replica=# \dn List of schemas Name | Owner
步骤4.使用以下命令将源数据库数据添加到pg_chameleon。 指定配置文件中指定的源数据库的名称。 在此示例中，源数据库的名称为mysql，目标数据库为Postgres数据库，定义为pg_conn。
$ chameleon add_source --config default --source mysql --debug
执行指定的命令后，您将看到原始数据库的数据已添加到t_sources表中。
db_replica=# select * from sch_chameleon.t_sources; -[ RECORD 1 ]-------+---------------------------------------------- i_id_source | 1 t_source | mysql jsb_schema_mappings | {: } enm_status | ready t_binlog_name | i_binlog_position | b_consistent | t b_paused | f b_maintenance | f ts_last_maintenance | enm_source_type | mysql v_log_table | {t_log_replica_mysql_1,t_log_replica_mysql_2} $ chameleon show_status --config default Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql ready Yes N/AN/A
步骤5.使用以下命令初始化副本（目标数据库）。 指定将更改从中复制到PostgreSQL数据库的源数据库。
$ chameleon init_replica --config default --source mysql --debug
初始化包括MySQL服务器（源）上的以下任务。
- 清除表缓存并设置只读锁
- 获取源数据库的坐标
- 复制资料
- 解锁
上面的命令自动创建目标Postgres数据库的架构。
在default.yml文件中，我们提到了以下架构映射（schema_mappings）。
schema_mappings: sakila: sch_sakila
现在，已在目标db_replica数据库中创建了一个新的Scott模式。
db_replica=# \dn List of schemas Name | Owner
步骤6.现在，使用以下命令开始复制。
$ chameleon start_replica --config default --source mysql
步骤7.使用以下命令验证复制状态和错误。
$ chameleon show_status --config default $ chameleon show_errors
复制状态如下所示：
$ chameleon show_status --source mysql Source id Source name Type Status Consistent Read lag Last read Replay lag Last replay ----------- ------------- ------ -------- ------------ ---------- ----------- ------------ ------------- 1 mysql mysql running No N/AN/A == Schema mappings == Origin schema Destination schema --------------- -------------------- sakila sch_sakila == Replica status == --------------------- --- Tables not replicated 0 Tables replicated 17 All tables 17 Last maintenance N/A Next maintenance N/A Replayed rows Replayed DDL Skipped rows
现在您可以看到更改不断从MySQL数据库复制到PostgreSQL数据库。
步骤8.为了进行验证，您可以在我们创建的MySQL数据库表中插入一条记录，以检查Postgres数据库中的复制。
$ mysql -u root -pSecret123! -e "INSERT INTO sakila.emp VALUES (1,'avinash','vallarapu')" mysql: [Warning] Using a password on the command line interface can be insecure. $ psql -d db_replica -c "select * from sch_sakila.emp" id | first_name | last_name ----+------------+----------- 1 | avinash | vallarapu (1 row)
从上面的日志可以看出，插入MySQL表中的记录已复制到Postgres数据库中的表中。
您还可以将多个源复制数据库添加到目标Postgres数据库。
友情链接
从本文档中，您可以了解pg_chameleon中可用的许多附加功能。