MySQL-mysql tutorial

Source: Internet
Author: User
Tags 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

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 || | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || ::1       | root       | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || %         | usr_kenyon | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-----------+------------+-------------------------------------------+5 rows in set (0.00 sec)
III, Installation and Use Mysql_fdw

: Http://
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/": 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 '', 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:
2. Suggestedreading

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.