阅读使人充实,讨论使人敏捷,写作使人精确。
迁移工具简介
今天介绍一个MySQL迁移PostgreSQL的自动化工具py-mysql2pgsql,该工具通过python使用不落地方式,或写出到文件方式(可选),直接将MySQL中的表结构转化到pg,并自动导入数据、自动在pg端创建相关索引。
MySQL迁移PostgreSQL数据类型转化目前该工具可将以下38种数据类型自动转化为PostgreSQL对应的兼容类型,如需其他类型转化,在配置文件添加即可。
MySQL | PostgreSQL |
---|---|
char | character |
varchar | character varying |
tinytext | text |
mediumtext | text |
text | text |
longtext | text |
tinyblob | bytea |
mediumblob | bytea |
blob | bytea |
longblob | bytea |
binary | bytea |
varbinary | bytea |
bit | bit varying |
tinyint | smallint |
tinyint unsigned | smallint |
smallint | smallint |
smallint unsigned | integer |
mediumint | integer |
mediumint unsigned | integer |
int | integer |
int unsigned | bigint |
bigint | bigint |
bigint unsigned | numeric |
float | real |
float unsigned | real |
double | double precision |
double unsigned | double precision |
decimal | numeric |
decimal unsigned | numeric |
numeric | numeric |
numeric unsigned | numeric |
date | date |
datetime | timestamp without time zone |
time | time without time zone |
timestamp | timestamp without time zone |
year | smallint |
enum | character varying (with check constraint) |
set | ARRAY[]::text[] |
自动化迁移过程
工具运行后,整个过程可以分为三个阶段:
l 在PostgreSQL端自动创建表结构; l 在PostgreSQL端自动loading加载数据; l 在PostgreSQL端自动创建索引、约束。 工具安装及使用例子 l 下载源码安装包 下载地址https://pypi.org/project/py-mysql2pgsql/#description
l
解压安装
1.需解决的依赖包:
python需按照这几个依赖包:termcolor、mysql-python、psycopg2、argparse
2.安装mysql2pgsql
[root@dbhost ]# pwd/data/aken/tools/py-mysql2pgsql-0.1.5[root@dbhost ]# python setup.py install
3.验证安装,查看help帮助
[root@dbhost /data/aken/tools/py-mysql2pgsql-0.1.5]# py-mysql2pgsql -husage: 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-mysql2pgsql[root@dbhost /data/aken/tools/py-mysql2pgsql-0.1.5]#
迁移存量数据
如下将MySQL中的test.tab_testtext表存量迁移到PostgreSQL中akendb的public下面。
l 编辑迁移配置文件
vi mysql2pgsql.yml 如下:
#source # if a socket is specified we will use that# if tcp is chosen you can use compressionmysql: hostname: 100.66.66.66 port: 15140 socket: username: dbmgr password: 520DBA database: test compress: falsedestination: # if file is given, output goes to file, else postgres. file: postgres: hostname: 100.88.88.88 port: 11005 username: aken password: aken123 database: akendb# 指定迁移的表,默认迁移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 datasupress_data: false# 指定是否只迁移数据,true表示只迁移数据。if supress_ddl is true, only the data will be exported/imported, and not the schemasupress_ddl: false# 表存在是否清空导入,true表示清空再导入。if force_truncate is true, forces a table truncate before table loadingforce_truncate: false# if timezone is true, forces to append/convert to UTC tzinfo mysql datatimezone: false# if index_prefix is given, indexes will be created whith a name prefixed with index_prefixindex_prefix:
l
执行数据迁移
执行导入数据后,自动执行过程分3个阶段:
1.自动在PostgreSQL创建表结构;
2.自动加载数据(约1w rows/sec);
3.自动在PostgreSQL创建索引。
[root@dbhost]# py-mysql2pgsql -v -f mysql2pgsql.yml >>>>>>>>>> STARTING <<<<<<<<<<START CREATING TABLES START - CREATING TABLE tab_testtext FINISH - CREATING TABLE tab_testtextDONE CREATING TABLESSTART WRITING TABLE DATA START - WRITING DATA TO tab_testtext FINISH - WRITING DATA TO tab_testtextDONE WRITING TABLE DATASTART 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_testtextDONE CREATING INDEXES AND CONSTRAINTS>>>>>>>>>> FINISHED <<<<<<<<<<
数据对比验证
l
表结构比对
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表结构
akendb=# \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: heapakendb=#
l
数据抽样比对
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数据
akendb=# select count(*) from tab_testtext; count ------- 4919(1 row)akendb=# select id,name from tab_testtext limit 3; id | name ------+-------------------------------------- 7306 | [N][测试] 7307 | [N][数据迁移] 7308 | [客户端接入][登陆](3 rows)akendb=#
>>>
参考资料
1.https://github.com/philipsoutham/py-mysql2pgsql
往期推荐
1.PostgreSQL等待事件-锁等待分析
2.基于PG亿级毫秒响应实时推荐系统-解决方案探索
------让学习成为一种习惯-Aken