MySQL迁移PG工具py-mysql2pgsql
迁移工具简
今天介绍一个MySQL迁移PostgreSQL的自动化工具py-mysql2pgsql，该工具通过python使用不落地方式，或写出到文件方式（可选），直接将MySQL中的表结构转化到pg，并自动导入数据、自动在pg端创建相关索引。
MySQL迁移PostgreSQL数据类型转化
目前该工具可将以下38种数据类型自动转化为PostgreSQL对应的兼容类型，如需其他类型转化，在配置文件添加即可。
MySQ LPostgreSQL
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 mallint
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 -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-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 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: 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 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
orce_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:
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_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 <<<<<<<<<<
数据对比验证
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: heap
akendb=#
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=#
- 发表于 2020-12-10 11:35
- 阅读 ( 51 )
