FDW for MySQL

1. Prepare the MySQL environment

MySQL environment:

Host: 127.0.0.1
Port: 3306
User: root
Database: test

Now, create a test table:

mysql> CREATE TABLE test(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.01 sec)

Because the MySQL InnoDB table must have a primary key to be written through mysql_fdw, c1 is specified as the primary key when creating the table.

Then, insert several pieces of test data:

mysql> INSERT INTO test VALUES(0,0),(1,1),(2,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

2. Install mysqlclient and shared libraries

2.1 Install mysqlclient

Note The following installation command is only an example. The RPM package names installed by different yum sources may be different. The corresponding version of MySQL version also needs to be downloaded according to actual needs. Do not copy and execute directly.

Note **Every machine in the cluster needs to be installed and configured.

Execute the installation command under sudo permissions or using the root user.

sudo yum install -y rh-mysql57-mysql

If it is an offline environment, you need to download 3 RPM packages (common, libs, client) in MySQL official website. Just follow the following command to execute the installation:

sudo rpm -ivh mysql-community-client-8.0.11-1.el7.x86_64.rpm --nodeps
sudo rpm -ivh mysql-community-common-8.0.11-1.el7.x86_64.rpm --nodeps
sudo rpm -ivh mysql-community-libs-8.0.11-1.el7.x86_64.rpm --nodeps

2.2 Setting up a shared library

Soft link libmysqlclient.so to the lib directory of YMatrix

ln -s  /lib64/mysql/libmysqlclient.so.18 /opt/ymatrix/matrixdb5/lib/libmysqlclient.so

Append libmysqlclient.so location to configuration file

echo "/lib64/mysql/libmysqlclient.so.18" >> /etc/ld.so.conf

3. Create FDW in YMatrix

First, create the mysql_fdw extension:

mxadmin=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION

Create a MySQL server definition:

mxadmin=# CREATE SERVER server_mysql FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '127.0.0.1', port '3306');
CREATE SERVER

Create a user map:

mxadmin=# CREATE USER MAPPING FOR mxadmin SERVER server_mysql OPTIONS (username 'root',password '123456');
CREATE USER MAPPING

Create an external table:

mxadmin=# CREATE FOREIGN TABLE ext_mysql (c1 int, c2 int) SERVER server_mysql OPTIONS (dbname 'test', table_name 'test');
CREATE FOREIGN TABLE

4. Use mysql_fdw to read and write data tables

After the external table is successfully created, you can directly read and write the external table to implement the operation of the test table in MySQL.

4.1 Query data

Through the following query, you can see that the external table data is the same as the original table:

mxadmin=# SELECT * FROM ext_mysql;
 c1 | c2
----+----
  0 |  0
  1 |  1
  2 |  2
(3 rows)

4.2 Write data

Insert data into external tables:

mxadmin=# INSERT INTO ext_mysql VALUES(3,3);
INSERT 0 1

After inserting the data, connect to MySQL to view the data:

mysql> SELECT * FROM test;
+----+------+
| c1 | c2   |
+----+------+
|  0 |    0 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.00 sec)

It can be seen that the data has been written to the test table of MySQL.