MYSQL数据库迁移到POSTGRESQL数据库（人工迁移）

时间：2021-11-19 04:52:14

产品即将上线，可项目经理说要换数据库，要把mysql数据库的内容全部搬到Postgresql中去。

有一个python的工具可以实现迁移（但是存储过程无法迁移、数据类型也无法灵活对应，还要改程序），为了节约时间采用人工迁移的方式，闲话少说，以下是迁移过程，没图，对不起。


  1. 利用Navicat for Mysql工具（其它工具也可以，比如mysqldump)将数据库模式导出（只导表结构不含数据）方法略，生成的文件名为mysql.sql

  2. 利用linux sed工具把导出的脚本中的所有COMMENT及mysql的独有原素替换掉，并生成新文件table_new.sql，见以下脚本
    cat mytab.sql  | sed 's/^.*ENGINE=.*$/);/g' | sed 's/COMMENT.*$/,/g' | sed 's/`//g' > table_new.sql 

  3. 将所有的INDEX语句单独生成一个文件（此步也可以省略，后面有其它方法）
    cat table_new.sql | egrep '\-|^\s*KEY|^\s*UNIQUE' > key.sql

  4. 将所有的外键语句单独生成一个文件 （此步可以省略，后面有其它方法）
    cat table_new.sql | egrep '\-|^\s*CONSTRAINT' > constraint.sql

  5. 去掉KEY和CONSTRAINT的语句，并生成新文件 table_last.sql

    cat table_new.sql | sed  's/^\s*KEY.*$//' | sed 's/^\s*CONSTRAINT.*$//' | sed 's/^\s*UNIQUE.*$//'  | sed '/^$/d'> table_last.sql

  6. 转换换行符

    unix2dos table_last.sql

  7. 把文件COPY到WINDOWS中，用记事本工具（或其它工具）对一些特殊值进行查找、替换操作（此步的意思就是把mysql中的类型对应到postgresql中的类型），比如：

    查找 ID int(12) NOT NULL AUTO_INCREMENT 替换 ID bigserial NOT NULL （自增长类型字段）

    查找 datetime 替换 timestamp

    ……

  8. 重新整理一些特殊表，因为第5步中去掉了所有KEY和CONSTRAINT的语句，所以如果表字段中包括KEY开头或是CONSTRAINT开头的字段时（比如字段 KEYCD，KEYTTL等）要把这些特殊的表的建表语句手工制作一变，并在table_last.sql中替换掉原来的语句。

  9. 在Postgresql中执行table_last.sql语句（方法略），如果有错误进行微调，直到所有表建立成功。

  10. 导入数据（先在mysql中导出，导出后的文件要替换换行符不然报错，然后再导入到postgresql)

    1. 导出语句（在MYSQL中执行以下语句，并复制结果后再次执行，便会在服务器的目录下生成以每个表为单位的数据文件,我用的工具是Navicat for Mysql。注意替换table_schema，这里生成数据文件的目录为：/usr/local/mysql/outfile/，也可换成其它目录）

    2. SELECT CONCAT('select * from ',table_name,
      " into outfile '/usr/local/mysql/outfile/",
      table_name ,
      '.dat' ,"'"
      " fields terminated by '|' ;")
      FROM information_schema.tables
      WHERE table_schema='DEVELOP';

    3. 替换换行符后将生成的文件COPY到postgresql服务器的目录下
      sed -i 's/\r//g' outfile/*

    4. 导入语句（在postgresql中执行，得到查询结果后复制查询结果，并再次执行,注意一定要用有superuser权限的用户执行，这里用的是pgadmin工具，目录是/tmp/data,注意替换table_catalog中的值，我的数据名为DEVELOP）


    5. select 'copy necsl.'||table_name|| ' from ' || chr(39)||'/tmp/data/'||upper(table_name)||'.dat' || chr(39) ||' with DELIMITER ' || chr(39) || '|' || chr(39) ||';' from information_schema.tableswhere table_schema='necsl' andtable_catalog='DEVELOP' ;

  11. 数据导入后可以用以下方法验证是否导入正确


  12. --在mysql及PG中分别建立下表create table table_count(tblname varchar(100) primary key not null,tblrecorder integer);
  13. /*在mysql中执行以下语句，并将结果复制后再次执行，将会生成在表table_count中生成每张表有多少记录*/SELECT CONCAT('insert into table_count select ' ,"'",table_name,"'",' ,count(*) from ',table_name ,";")FROM information_schema.tablesWHERE table_schema='DEVELOP';--在postgresql中执行以下语句，并将结果复制后再次执行，将会生成在表table_count中生成每张表有多少记录,然后与mysql中table_count的表比较即可select 'insert into necsl.table_count select '|| quote_literal(table_name) ||',count(*) from   necsl.'||table_name|| ';'  from information_schema.tableswhere table_schema='necsl' andtable_catalog='DEVELOP' ;
  14. --如果导入有问题，可以在PG中执行以下句语生成清空所有表的语句，将执行结果复制再执行即可清空所有表PG truncateselect 'truncate table  necsl.'||table_name|| ';'  from information_schema.tableswhere table_schema='necsl' andtable_catalog='DEVELOP' ;
  15. /*生成索引信息及外键，在mysql中分别执行以下语句，并将结果复制到POSTGRESQL中执行*//*生成唯一索引*/select    CONCAT (     'CREATE UNIQUE INDEX ' ,                table_name,'_IDX_',index_name,     ' ON ',      table_name,     '(',      GROUP_CONCAT(column_name order by seq_in_index),      ');')from statisticswhere table_schema='DEVELOP'AND INDEX_NAME <>'PRIMARY'AND INDEX_SCHEMA='DEVELOP'AND NON_UNIQUE=0GROUP BY index_name,table_nameORDER BY TABLE_NAME,INDEX_NAME,seq_in_index asc/*生成btree索引*/select    CONCAT (     'CREATE INDEX ' ,                table_name,'_IDX_',index_name,     ' ON ',      table_name,     '(',      GROUP_CONCAT(column_name order by seq_in_index),      ');')from statisticswhere table_schema='DEVELOP'AND INDEX_NAME <>'PRIMARY'AND INDEX_SCHEMA='DEVELOP'AND NON_UNIQUE=1GROUP BY index_name,table_nameORDER BY TABLE_NAME,INDEX_NAME,seq_in_index asc/*生成外键 */selectconcat('alter table ',c.TABLE_NAME,' add constraint ',c.CONSTRAINT_NAME,' foreign key(',c.COLUMN_NAME,') references ',c.REFERENCED_TABLE_NAME,'( ',c.REFERENCED_COLUMN_NAME,');')from TABLE_CONSTRAINTS t,KEY_COLUMN_USAGE cwhere t.CONSTRAINT_SCHEMA='DEVELOP'AND t.CONSTRAINT_TYPE='FOREIGN KEY'AND t.TABLE_SCHEMA='DEVELOP'AND c.REFERENCED_TABLE_SCHEMA='DEVELOP'AND t.CONSTRAINT_NAME =c.CONSTRAINT_NAMEand t.table_name=c.table_name;

17.生成以上信息后，在PG中建立以下函数，用于重置所有的sequence（如果没有自增长类型可忽略此步），建立放在public模式下

CREATE OR REPLACE FUNCTION pro_resetallseq()  RETURNS void AS$BODY$ DECLARE           tmp VARCHAR(512);           maxval bigint;           stmt  record; BEGIN       FOR stmt IN   select sequence_name,REPLACE(sequence_name,'_id_seq','') as tnm from information_schema.sequences where sequence_catalog='DEVELOP' and sequence_schema='necsl'  LOOP                    tmp := 'SELECT MAX(ID) FROM '|| quote_ident(stmt.tnm) || ';';  EXECUTE tmp into maxval;                  if not (maxval  is NULL or maxval = 0) then                                         EXECUTE 'SELECT SETVAL('|| quote_literal(stmt.sequence_name) || ',' || maxval || ');';                  end if;                                           END LOOP;       RAISE NOTICE 'finished .....';       END;$BODY$  LANGUAGE plpgsql

18.建立成功后可以用以下语句重置所有的sequence

select pro_resetallseq();

19.关于mysql中的函数（存储过程）因为语法差别较大，只能在PG中重写。我没有找到太好的方法。


因为时间紧，所以没有写工具，过程也比较粗糙，仅供参考，如果有好的建议欢迎留言，谢谢阅读。

本文出自 “镜子” 博客，请务必保留此出处http://383133430.blog.51cto.com/454215/1686169

标签：

相关文章