作者：徐田原
1.简介
介绍一款MySQL迁移PostgreSQL的自动化工具py-mysql2pgsql，该工具通过python使用不落地方式，或写出到文件方式（可选），直接将MySQL中的表结构转化到pg，并自动导入数据、自动在pg端创建相关索引。
2.MySQL迁移PostgreSQL数据类型转化
目前该工具可将以下38种数据类型自动转化为PostgreSQL对应的兼容类型，如需其他类型转化，在配置文件添加即可。
MySQLPostgreSQLcharcharactervarcharcharacter varyingtinytexttextmediumtexttexttexttextlongtexttexttinyblobbyteamediumblobbyteablobbytealongblobbyteabinarybyteavarbinarybyteabitbit varyingtinyintsmallinttinyint unsignedsmallintsmallintsmallintsmallint unsignedintegermediumintintegermediumint unsignedintegerintintegerint unsignedbigintbigintbigintbigint unsignednumericfloatrealfloat unsignedrealdoubledouble precisiondouble unsigneddouble precisiondecimalnumericdecimal unsignednumericnumericnumericnumeric unsignednumericdatedatedatetimetimestamp without time zonetimetime without time zonetimestamptimestamp without time zoneyearsmallintenumcharacter varying (with check constraint)setARRAY[]::text[]
3.自动化迁移过程
工具运行后，整个过程可以分为三个阶段：
- 在PostgreSQL端自动创建表结构；
- 在PostgreSQL端自动loading加载数据；
- 在PostgreSQL端自动创建索引、约束。
4.工具安装及使用例子
4.1 下载源码安装包
下载地址：https://pypi.org/project/py-mysql2pgsql/#description
1、解压安装
2、需解决的依赖包：
python需按照这几个依赖包：termcolor、mysql-python、psycopg2、argparse
3、安装mysql2pgsql
[root@123 ]# pwd
/data/aken/tools/py-mysql2pgsql-0.1.5
[root@123 ]# python setup.py install
4、验证安装，查看help帮助
[root@123 /data/aken/tools/py-mysql2pgsql-0.1.5]# py-mysql2pgsql -h
usage: py-mysql2pgsql [-h] [-v] [-f FILE] [-V]
Tool for migrating/converting data from mysql to postgresql.
?
optional arguments:
-h, --help show this help message and exit
-v, --verbose Show progress of data migration.
-f FILE, --file FILE Location of configuration file (default:mysql2pgsql.yml). If none exists at that path, one will be created for you.
-V, --version Print version and exit.
?
https://github.com/philipsoutham/py-mysql2pgsql4.2 迁移存量数据
如下将MySQL中的test.tab_testtext表存量迁移到PostgreSQL中pg的public下面。
1、编辑迁移配置文件
vi mysql2pgsql.yml 如下：
#source
# if a socket is specified we will use that
# if tcp is chosen you can use compression
?
mysql:
hostname: 100.66.66.66
port: 15140
socket:
username: dbmgr
password: 520DBA
database: test
compress: false
?
destination:
# if file is given, output goes to file, else postgres.
file:
postgres:
hostname: 100.88.88.88
port: 11005
username: aken
password: aken123
database: pg
?
# 指定迁移的表，默认迁移database下所有表。if only_tables is given, only the listed tables will be converted. leave empty to convert all tables.
only_tables:
#- table1
#- table2
tab_testtext
?
# 指定排除的表。if exclude_tables is given, exclude the listed tables from the conversion.
#exclude_tables:
#- table3
#- table4
?
# 指定是否只导出表结构，ture表示只迁移dll表结构。if supress_data is true, only the schema definition will be exported/migrated, and not the data
supress_data: false
?
# 指定是否只迁移数据，true表示只迁移数据。if supress_ddl is true, only the data will be exported/imported, and not the schema
supress_ddl: false
?
# 表存在是否清空导入，true表示清空再导入。if force_truncate is true, forces a table truncate before table loading
force_truncate: false
?
# if timezone is true, forces to append/convert to UTC tzinfo mysql data
timezone: false
?
# if index_prefix is given, indexes will be created whith a name prefixed with index_prefix
index_prefix:4.3 执行数据迁移
执行导入数据后，自动执行过程分3个阶段：
- 自动在PostgreSQL创建表结构；
- 自动加载数据（约1w rows/sec)；
- 自动在PostgreSQL创建索引。
[root@123]# py-mysql2pgsql -v -f mysql2pgsql.yml
>>>>>>>>>> STARTING <<<<<<<<<<
?
START CREATING TABLES
START - CREATING TABLE tab_testtext
FINISH - CREATING TABLE tab_testtext
DONE CREATING TABLES
?
START WRITING TABLE DATA
START - WRITING DATA TO tab_testtext
FINISH - WRITING DATA TO tab_testtext
DONE WRITING TABLE DATA
?
START CREATING INDEXES AND CONSTRAINTS
START - ADDING INDEXES TO tab_testtext
FINISH - ADDING INDEXES TO tab_testtext
START - ADDING CONSTRAINTS ON tab_testtext
FINISH - ADDING CONSTRAINTS ON tab_testtext
DONE CREATING INDEXES AND CONSTRAINTS
?
>>>>>>>>>> FINISHED <<<<<<<<<<4.4 数据对比验证
4.4.1 表结构比对
1.MySQL表结构
MySQL [test]> show create table tab_testtext;
+--------------
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------+
| tab_testtext | CREATE TABLE `tab_testtext` (
`id` int(11) NOT NULL DEFAULT '0',
`name` longtext,
`owners` longtext,
`parent_id` int(11) DEFAULT NULL,
`busid` int(11) DEFAULT NULL,
`uid` varchar(255) DEFAULT NULL,
`level` int(11) DEFAULT NULL,
`update_date` datetime NOT NULL,
`create_date` datetime NOT NULL,
`limit_load` int(11) DEFAULT '65',
`children_count` int(11) DEFAULT '0',
`limit_low_load` int(11) DEFAULT '30',
`history_load` varchar(255) DEFAULT NULL,
`status` int(11) DEFAULT '0',
`group_id` int(11) DEFAULT '21576',
`_alarm_types` varchar(64) DEFAULT NULL,
`star_level` int(11) DEFAULT '0',
`remark` text,
`enable` tinyint(11) NOT NULL DEFAULT '1',
UNIQUE KEY `busid` (`busid`),
UNIQUE KEY `uid` (`uid`),
KEY `core_business_6be37982` (`parent_id`),
KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
MySQL [test]>
2.PostgreSQL表结构
pg=# \d+ tab_testtext
Table 'public.tab_testtext'
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
id | integer | | not null | 0 | plain | |
name | text | | | | extended | |
owners | text | | | | extended | |
parent_id | integer | | | | plain | |
busid | integer | | | | plain | |
uid | character varying(255) | | | | extended | |
level | integer | | | | plain | |
update_date | timestamp without time zone | | not null | | plain | |
create_date | timestamp without time zone | | not null | | plain | |
limit_load | integer | | | 65 | plain | |
children_count | integer | | | 0 | plain | |
limit_low_load | integer | | | 30 | plain | |
history_load | character varying(255) | | | | extended | |
status | integer | | | 0 | plain | |
group_id | integer | | | 21576 | plain | |
_alarm_types | character varying(64) | | | | extended | |
star_level | integer | | | 0 | plain | |
remark | text | | | | extended | |
enable | smallint | | not null | 1 | plain | |
Indexes:
'tab_testtext_busid' UNIQUE, btree (busid)
'tab_testtext_uid' UNIQUE, btree (uid)
'tab_testtext_group_id' btree (group_id)
'tab_testtext_parent_id' btree (parent_id)
Access method: heap
?
pg=#4.4.2 数据抽样比对
1.MySQL数据
MySQL [test]> select count(*) from tab_testtext;
+----------+
| count(*) |
+----------+
| 4919 |
+----------+
1 row in set (0.00 sec)
MySQL [test]> select id,name from tab_testtext limit 3;
+------+--------------------------------------+
| id | name |
+------+--------------------------------------+
| 7306 | [N][测试] |
| 7307 | [N][数据迁移] |
| 7308 | [客户端接入][登陆] |
+------+--------------------------------------+
3 rows in set (0.00 sec)
MySQL [test]>
2.PostgreSQL数据
pg=# select count(*) from tab_testtext;
count
-------
4919
(1 row)
?
pg=# select id,name from tab_testtext limit 3;
id | name
------+--------------------------------------
7306 | [N][测试]
7307 | [N][数据迁移]
7308 | [客户端接入][登陆]
(3 rows)
pg=#参考资料
1.https://github.com/philipsoutham/py-mysql2pgsql