Shulou(Shulou.com)06/03 Report--

A perl script will be used to convert the data format: https://github.com/ahammond/mysql2pgsql

1 Export the data of mysql

# Export structure mysqldump bx_cmdb_37-- master-data=2-- skip-opt-- default-character-set=utf8-- no-data-- compact-- skip-dump-date-- set-gtid-purged=OFF > / tmp/struct.sql# Export data mysqldump bx_cmdb_37-- master-data=2-- skip-opt-- default-character-set=utf8-- no-create-info-- compact-- skip-dump-date-- set-gtid-purged=OFF > / tmp/data.sql

2 use the perl script to convert the 2 sql in the previous step to pg format, with a command similar to the following

Download to / root/ directory chmod + x / root/mysql2pgsql.perlcd / tmp//root/mysql2pgsql.perl struct.sql struct_pg.sql/root/mysql2pgsql.perl data.sql data_pg.sqlsed-i.bak 's # int # bigint # g 'struct_pg.sqlsed-i.bak' s # smallint # int # g 'struct_pg.sqlsed-i.bak' s # datetime (6) # timestamp # g 'struct_pg.sql to do the above sed replacement operations The reason is: here I have a few problems (my following operations are not elegant enough): 1 part of the int unsigned data The perl script has been converted to int signed, resulting in a narrowing of the scope, and the subsequent insertion data may overflow, so here I replace the int in all the table-building statements with bigint2 pay attention to the column of datetime. In PG, we need to manually change it to timestamp type, otherwise error 3 smallint will be changed to int type to prevent insertion overflow into PG.

3 Import the file into the PG library (note that there may be some incompatible errors)

Chown postgres.postgres / tmp/bbb.sqlpsql-- port 5434-d testdb-f / tmp/bbb.sql can be imported into PG's testdb library

4 subsequent incremental data synchronization can be carried out using go-mysql-postgresql (the next blog introduction)

When we export data.sql, we already have master-data with us, so it's easy to know the location of the last copied binlog.

# # reference http://zhansq.cn/article/18-08-09/43.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.