mysql ddl postgresql_Mysql 5.6迁移至PostgreSQL 9.6的实践小结

一、背景

实际生产中，发现mysql查询性能存在抖动，同样的sql，正常执行时间是秒级，但是偶尔会有执行上百秒的情况出现，经过DBA的排查，并没有发现mysql的问题。考虑迁移一部分生成数据到PG中进行测试。(ps~个人觉得这个迁移背景有点牵强，还是应该先定位性能抖动的原因比较好)

二、迁移方案

迁移的大致步骤如下：

从生产环境的mysql备份中拉取一个备份出来

在测试机上通过备份恢复生产库

导出mysql的表定义和数据

通过自己开发的小工具，将mysql表定义语法转换至PG的表定义语法

在PG中创建表

将数据导入PG

三、迁移步骤说明

3.1 拉取备份

这个没什么好说的，scp指定的备份文件到测试机即可

考虑是生产环境，有防火墙和权限等的限制，可以临时创建临时用户tmp，关闭防火墙，待拷贝完成，删除用户，重启防火墙

3.2 恢复生产库

生产上通过xtrabackup做的备份，恢复方法这里就不啰嗦了，不是本次的重点，自行百度~

3.3 导出mysql的表定义和数据

从这步开始就有坑了~

首先，导出表定义(只贴出测试数据)

# 将名为test_db的库中所有的ddl都导出到test_db.sql文件中

# 导出的定义以sql语句的形式写入文件

[mysql@sndsdevdb01 ~]$ mysqldump -h127.0.0.1 -uroot -ppassword -d test_db > /mysql/test_db.sql

[mysql@sndsdevdb01 ~]$ cat /mysql/test_db.sql

...

/* 下面是导出的表定义部分 */

DROP TABLE IF EXISTS `tb1`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb1` (

`c1` int(11) DEFAULT NULL,

`c2` char(5) DEFAULT NULL,

`c3` varchar(10) DEFAULT NULL,

`c4` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

...

导出表定义是为了之后人工检查mysql到PG的ddl语法转换的正确性

实际实施时，利用小工具直接连接mysql服务器即可完成mysql到PG的ddl语法转换

关于小工具的说明，请见附录~

然后，导出数据

考虑到数据格式࿰