FDW for MySQL

1. Prepare 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 mysql's 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 MySQL version also needs to download the corresponding version 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] (https://downloads.mysql.com/archives/community/). 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 matrixdb

ln -s  /lib64/mysql/libmysqlclient.so.18 /usr/local/matrixdb/lib/libmysqlclient.so

Append libmysqlclient.so location to configuration file

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

3. Create FDW in MatrixDB

Below, log in to MatrixDB and use mysql_fdw to connect to the MySQL data table you just created:

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 MySQL test table.