yii 执行指定迁移文件_MySQL迁移PG工具pymysql2pgsql

阅读使人充实，讨论使人敏捷，写作使人精确。

 迁移工具简介

今天介绍一个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