PostgreSQL foreign data wrapper for MySQL "mysql_fdw"

This is my first blog ,In this blog i am trying to give you some information on heterogeneous connection between PostgreSQL to MySQL with the help of "mysql_fdw" .

So,Now we are starting .
Following are the steps how to implement FDW with PostgreSQL to perform operation on mysql 

Step 1 : -

       Download 'mysql_fdw' package

       Link to download = "https://github.com/EnterpriseDB/mysql_fdw"     
              

wget https://github.com/EnterpriseDB/mysql_fdw/archive/master.zip unzip master cd mysql_fdw-master/

Step 2 : -
      
      A)  To build on POSIX-compliant systems you need to ensure the pg_config executable is in your path when you run make. This executable is typically in your PostgreSQL installation's bin directory. 

export PATH=/usr/local/pgsql/bin/:$PATH     //PostgreSQL Binary path .

     B) The mysql_config must also be in the path, it resides in the MySQL bin directory

yum install mysql-devel mysql-common export PATH=/usr/bin/:$PATH

Step 3 : -

        Compile and install the code .

cd mysql_fdw-master/ make USE_PGXS=1 make USE_PGXS=1 install

Step 4 : -

        Create  mysql_fdw extension on Postgresql server

CREATE EXTENSION mysql_fdw;

Till now we have completed installation part .

Below part is for creating connection of PostgreSQL with MySql .
Step 5 : - 

       Now connect to your mysql database to created some dummy user and table .

CREATE USER 'foo'@'%' IDENTIFIED BY 'bar';   --Create user grant all privileges on *.* to 'foo'@'%' with grant option; --Grant permission

      Create one table in mysql databases

Step 6 : -

       Now get back to PostgreSQL Databases ,and do MySQL connectivity configuration as per below steps.

A) Create server object                                                CREATE SERVER mysql_server                         FOREIGN DATA WRAPPER mysql_fdw                         OPTIONS (host '192.168.213.1', port '3306');  B) Create user mapping                         CREATE USER MAPPING FOR postgres                         SERVER mysql_server                         OPTIONS (username 'foo', password 'bar');             C) Create foreign table                         CREATE FOREIGN TABLE warehouse(                         warehouse_id int,                         warehouse_name text,                         warehouse_created datetime)                         SERVER mysql_server                                   OPTIONS (dbname 'test', table_name 'warehouse');

Running select query from PostgreSQL DB against MySql .



Insert one value into mysql's test database checking it from PostgreSQL DB.





ERROR: - 

While accessing DB via PostgreSQL from remote location if you got error like .

ERROR:  failed to connect to MySQL: Can't connect to MySQL server on (113)

iptables -I INPUT 1 -p tcp --dport 3306 -j ACCEPT

 Add below entry into /etc/my.cnf

bind-address = 0.0.0.0

/etc/init.d/mysqld restart