English

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 InnoDB tables in MySQL must have a primary key to allow writes via mysql_fdw, c1 is defined as the primary key.

Insert some 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 installation commands below are examples only. RPM package names may vary across different yum repositories. Download the MySQL version appropriate for your system. Do not copy and execute these commands directly without verification.

Note
Configuration and installation must be performed on every host in the cluster.

Run the installation command with sudo privileges or as the root user:

sudo yum install -y rh-mysql57-mysql

For offline environments, download the following three RPM packages from the MySQL official website (common, libs, client). Then install them using the commands below:

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 Set Up Shared Libraries

Create a symbolic link from libmysqlclient.so to the YMatrix lib directory:

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

Append the libmysqlclient.so path to the 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 server definition for MySQL:

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

Create a user mapping:

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

Create the foreign table:

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

4. Read and Write Data Using mysql_fdw

After successfully creating the foreign table, you can read from and write to the MySQL test table by accessing the foreign table directly.

4.1 Query Data

The following query shows that the data in the foreign table matches the original table:

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

4.2 Insert Data

Insert data into the foreign table:

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

After insertion, connect to MySQL and verify the data:

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

The data has been successfully written to the MySQL test table.

← Previous

FDW for PostgreSQL

FDW for MongoDB

English Русский 简体中文