MySQL-mysql tutorial

PostgreSQL mysql_fdw uses many methods to migrate Mysql data to PostgreSQL, such as navicat and jmyetl tools. However, charges are usually incurred, and the efficiency and stability are not necessarily good, the most conservative method is to write some scripts by yourself. of course, there are also some open-source tools, such as mysql_fdw, which need to be used locally to create mysql software and postgresql databases, the test example below is that mysql and pg are installed together.

Mysql_fdw is an open-source tool based on the BSD protocol. it is not yet built into postgresql. The latest version is 1.0.1.

I. environment
CentOS 1, 6.3
PostgreSQL 9.3.4
Host 10.1.11.73

II. Mysql data preparation

mysql> create database db_kenyon;Query OK, 1 row affected (0.01 sec)mysql> create table tbl_kenyon(id int,vname varchar(48));Query OK, 0 rows affected (0.00 sec)mysql> insert into tbl_kenyon values(1,'test');Query OK, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(2,'kenyon');Query OK, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(null,'it/'s null');Query OK, 1 row affected (0.00 sec)mysql> insert into tbl_kenyon values(4,null);Query OK, 1 row affected (0.01 sec)mysql> select * from tbl_kenyon;+------+-----------+| id   | vname     |+------+-----------+|    1 | test      ||    2 | kenyon    || NULL | it's null ||    4 | NULL      |+------+-----------+4 rows in set (0.00 sec)mysql> grant select on db_kenyon.tbl_kenyon to 'usr_kenyon'@'%' identified by '123456';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)mysql> select host,user,password from mysql.user;+-----------+------------+-------------------------------------------+| host      | user       | password                                  |+-----------+------------+-------------------------------------------+| localhost | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || db1       | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || 127.0.0.1 | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || ::1       | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || %         | usr_kenyon | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------------+-------------------------------------------+5 rows in set (0.00 sec)

III, Installation and Use Mysql_fdw

: Http://pgxn.org/dist/mysql_fdw/
Use the postgres user during installation. refer to README for two steps.
Make USE_PGXS = 1
Make USE_PGXS = 1 install
An ERROR may occur after installation. ERROR: cocould not load library "/home/S/lib/mysql_fdw.so": libmysqlclient. so.18: cannot open shared object file: No such file or directory. copy the so.18 file to/home/postgres/lib or make a soft connection.

1. create extension

postgres=# create extension mysql_fdw ;CREATE EXTENSION

2. create a server

postgres=# CREATE SERVER mysql_svr  FOREIGN DATA WRAPPER mysql_fdw  OPTIONS (address '10.1.11.73', port '3306');CREATE SERVER

3. create one or more external tables (foreign table)

postgres=# CREATE FOREIGN TABLE pg_mysql_tbl1 (id integer,    name text)    SERVER mysql_svr    OPTIONS (table 'db_kenyon.tbl_kenyon');CREATE FOREIGN TABLEpostgres=# CREATE FOREIGN TABLE pg_mysql_tbl2 (    id integer,   vname text)   SERVER mysql_svr   OPTIONS (query 'SELECT id, vname FROM db_kenyon.tbl_kenyon WHERE id<>2;');CREATE FOREIGN TABLE

4. create a PostgreSQL fdw query User. the pg_hba configuration is omitted.

postgres=# create user u_select ENCRYPTED PASSWORD '123456';CREATE ROLE

5. create a user mapping (user mapping). the user is the user password of remote mysql.

CREATE USER MAPPING FOR u_select    SERVER mysql_svr     OPTIONS (username 'usr_kenyon', password '123456');

6. to query data, you must log on to u_select. otherwise, user mapping not found will be reported.

postgres=> select * from pg_mysql_tbl1 ; id |   name    ----+-----------  1 | test  2 | kenyon    | it's null  4 | (4 rows)postgres=> select * from pg_mysql_tbl2; id | vname ----+-------  1 | test  4 | (2 rows)

IV. delete extensions

postgres=# drop foreign table pg_mysql_tbl1;DROP FOREIGN TABLEpostgres=# drop foreign table pg_mysql_tbl2;DROP FOREIGN TABLEpostgres=# drop user mapping for u_select server mysql_svr ;DROP USER MAPPINGpostgres=# drop server mysql_svr ;DROP SERVERpostgres=# drop extension mysql_fdw ;DROP EXTENSION

V. Summary
1. this tool is useful for ETL migration from Mysql to postgresql. it is cross-platform and cross-database, and simple and convenient.
2. Currently, this tool is not a built-in version, which may cause risks. for example, if the types of the fields in the mysql and PostgreSQL databases are different, garbled characters may occur.


VI. reference:
1. http://pgxn.org/dist/mysql_fdw/
2. http://francs3.blog.163.com/blog/static/40576727201111211324599? Suggestedreading