In this blog we will show how to access MySQL and MariaDB DBMS tables from MariaDB Server. For that we will use Connect Storage Engine (SE), which supports different table types options. In this case we will use the JDBC table type. To use the JDBC table type we need to specify it in the CREATE TABLE definition for Connect SE and we need the Java connector for the DBMS we are accessing. For demonstration purposes we will use containers, but this will work if the servers are running in VMs or bare-metal. Just make sure the machines can access each other via TCP/IP. The same procedure can be used to access any other DBMS as long as there is a corresponding Java connector.
In order to access the table of another DBMS from MariaDB Server we need:
- Source – the table located in the external DBMS, from where we will “source” our data. (For this example we’ll use both MariaDB and MySQL as “external” DBMSes.
- Target – the local table found in MariaDB Server.
- The Java connector that can communicate with the external DBMS and the dependencies for JDBC.
- Have Connect SE plugin installed on the target system.
- Credentials to access the external DBMS and our source table.
If you don’t want to see the full setup procedure, you can skip the step-by-step guide below and see the connection in action by running the docker-compose script from section 6. You can find the files in this blog post in this repository.
1. Create source tables in MySQL and MariaDB DBMS
We will create two containers, mariadb-source and mysql-source, both containers running the respective database system. We will create one table, with a few rows in both containers. The SQL commands to create the tables will be placed in an .sql file inside the container’s special folder /docker-entrypoint-initdb.d. Any SQL file within that directory will be run when initializing a fresh instance. We will use volume mount points to achieve this.
1.1 MariaDB Server source table
Here is the contents of our data.sql file:
$ cat mariadb_data/data.sql
CREATE TABLE t_maria (t int);
INSERT INTO t_maria VALUES (1),(2),(3);To allow our containers to talk to each other, we will create a custom docker network (user defined bridge). This will allow us to have automatic DNS resolution within the containers, between containers. The default bridge does not support that. (more details in section 5).
$ docker network create jdbc_connect
Next, we’ll start the mariadb-source container:
$ docker container run \
--name mariadb-source \
--rm \
-e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 \
-e MARIADB_DATABASE=db_maria \
-v$PWD/mariadb_data:/docker-entrypoint-initdb.d:z \
-d --network jdbc_connect \
mariadb:latestAfter the container is done initializing, we can check the data.
$ docker exec -t mariadb-source mariadb -e "select * from t_maria" db_maria
+------+
| t |
+------+
| 1 |
| 2 |
| 3 |
+------+1.2 MySQL Server source table
We’ll follow a similar procedure for our MySQL container. Here’s our data file, placed in a different directory for easy mounting.
$ cat mysql_data/data.sql
CREATE TABLE t_mysql (t int);
INSERT INTO t_mysql VALUES (4),(5),(6);Let’s start the container:
$ docker container run \
--name mysql-source \
--rm \
-e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
-e MYSQL_DATABASE=db_mysql \
-v$PWD/mysql_data:/docker-entrypoint-initdb.d:z \
-d --network jdbc_connect \
mysql:latestAnd check the data:
$ docker exec -t mysql-source mysql -e "select * from t_mysql" db_mysql
+------+
| t |
+------+
| 4 |
| 5 |
| 6 |
+------+1.3 MariaDB DBMS target
Now that our sources are in place, let’s create the target MariaDB Docker container. We will not add any preloaded data to this one.
$ docker container run --name mariadb-target --rm -e MARIADB_ALLOW_EMPTY_ROOT_PASSWORD=1 -d --network jdbc_connect mariadb:latestNow we need to configure the target with its required dependencies. We could do this in a Dockerfile, but let’s experiment live with the container.
2. Java connectors of source DBMS and dependencies
2.1 Dependencies for JDBC
Let’s get a shell within the container and start our configuring:
$ docker exec -it mariadb-target bashJDBC has the following requirements: java, jvm and jar. The first two binaries are part of openjdk-11-jre-headless, but since we will be using jar to validate the content of some jar files below, we will use the package openjdk-11-jdk-headless, that has openjdk-11-jre-headless as a dependency.
$ apt update && apt install openjdk-11-jdk-headlessCheck the installation (for java, javac, jar), they should be installed:
$ java --version
openjdk 11.0.18 2023-01-17
OpenJDK Runtime Environment (build 11.0.18+10-post-Ubuntu-0ubuntu122.04)
OpenJDK 64-Bit Server VM (build 11.0.18+10-post-Ubuntu-0ubuntu122.04, mixed mode, sharing)Additionally look for the location of libjvm.so. That should be located in /usr/lib/jvm/java-11-openjdk-amd64/lib/server/libjvm.so. We will need to specify it as part of connect_jvm_path.
2.2 Install Java connectors
To access tables with JDBC we need the Java connector of our source DBMS. In this case, we need MySQL Connector/J and MariaDB Connector/J installed on the target system. We will also need wrappers files. These wrapper files are part of mariadb-test-data package. JavaWrappers.jar has byte code subclasses of the JdbcInterface for MySQL and MariaDB interfaces. (Note to the reader, we are aware that this current packaging scheme is less than optimal, we are working towards improving this and getting JavaWrappers in a proper package).
$ apt install mariadb-test-data # Install the package
$ dpkg -L mariadb-test-data | \
grep JavaWrapper # Let's see where JavaWrappers was placed.
/usr/share/mysql/mysql-test/plugin/connect/connect/std_data/JavaWrappers.jarThere are multiple wrappers in the jar file. One can peek into the jar file to see:
$ jar tf /usr/share/mysql/mysql-test/plugin/connect/connect/std_data/JavaWrappers.jar \
| grep class
wrappers/PostgresqlInterface.class
wrappers/OracleInterface.class
wrappers/ApacheInterface.class
wrappers/Client.class
wrappers/JdbcInterface.class
wrappers/MysqlInterface.class
wrappers/MariadbInterface.classMake sure to remember the path to the JavaWrappers.jar file itself, namely /usr/share/mysql/mysql-test/plugin/connect/connect/std_data/JavaWrappers.jar that will be used later as connect_class_path. You are free to move this file, but make sure to remember the path.
Depending on which database we are connecting to we need an additional Java connector client.
For MariaDB Connector/J, as well as for MySQL in Debian/Ubuntu, we may use libmariadb-java package. For this package, the client is located in /usr/share/java/mariadb-java-client.jar. We need to append this path to the connect_class_path system variable, just like one would do for a regular bash “PATH” variable. Different paths are separated via :.
3. Install the Connect SE plugin on the target system
This step is straight forward and it was already covered in a previous blog. Since here we are logged into the target container let’s install the package for it.
$ apt install mariadb-plugin-connect
$ ls /usr/lib/mysql/plugin/ha_connect*
/usr/lib/mysql/plugin/ha_connect.soThis will install the plugin within the container, but the MariaDB Server doesn’t know that it should actually load it on start-up.
4. Configure the target MariaDB Server for JDBC
Let’s create a configuration file to load Connect SE on MariaDB Server startup and set the configuration variables needed to find jvm and the JavaWrappers. Here are the necessary changes:
[mariadb]
plugin_load_add=ha_connect.so
connect_jvm_path=/usr/lib/jvm/java-11-openjdk-amd64/lib/server/
connect_class_path=/usr/share/mysql/mysql-test/plugin/connect/connect/std_data/JavaWrappers.jar:/usr/share/java/mariadb-java-client.jarYou can add this configuration chunk in /etc/mysql/mariadb.conf.d/50-server.cnf, but you will need to restart the docker container in order for the changes to take place.
$ docker restart mariadb-targetAn alternative would be to create a custom configuration file on the host and mount it within /etc/mysql/mariadb.conf.d/ folder in the container. In section 6, when using docker-compose we do that.
Finally, let’s start checking our configuration. First check if Connect SE is properly installed. It should show up in show plugins.
$ docker exec -it mariadb-target mariadb -uroot -e "show plugins soname like '%connect%';"
+---------+--------+----------------+---------------+---------+
| Name | Status | Type | Library | License |
+---------+--------+----------------+---------------+---------+
| CONNECT | ACTIVE | STORAGE ENGINE | ha_connect.so | GPL |
+---------+--------+----------------+---------------+---------+Second, let’s check the connect_jvm_path and connect_class_path variables for correctness:
$ docker exec -t mariadb-target mariadb -e "select @@connect_jvm_path, @@connect_class_path\G"
*************************** 1. row ***************************
@@connect_jvm_path: /usr/lib/jvm/java-11-openjdk-amd64/lib/server/
@@connect_class_path: /usr/share/mysql/mysql-test/plugin/connect/connect/std_data/JavaWrappers.jar:/usr/share/java/mariadb-java-client.jar5. Access the “external source” tables
We’ll first try to access the table located in our “source” MariaDB Server. To do this, we will create a table in our local MariaDB Server. The table will use the Connect SE with a few special JDBC specific arguments. These arguments specify the table type and the connection url. The table type instructs Connect SE what driver to use to fetch data and the connection string is passed to the driver.
5.1 Fetch data from a table on the localhost
Before we try to connect to our source containers, let’s do a “loopback” connection to test that JDBC works properly within the target container. First initiate a mariadb client connection:
$ docker exec -it mariadb-target mariadb -u rootNow create a test database and a sample table t.
MariaDB [(none)]> create database test; use test;
MariaDB [test]> create table t(t int); insert into t values (1),(2);Next create a Connect SE table to link to it using JDBC:
MariaDB [test]> create table t_jdbc engine=connect table_type=JDBC tabname=t connection='jdbc:mariadb://localhost/test?user=root&password';
MariaDB [test]> select * from t_jdbc;
+------+
| t |
+------+
| 1 |
| 2 |
+------+5.2 Fetch data from tables on remote hosts (mariadb-source and mysql-source)
As we have created all containers on the same network, the container name is resolvable to the private IP address of the container.
Still in our mariadb-target container, create a table with a connection string pointing to mariadb-source. Notice that we made use of the database’s root user. Since we created the root user with an empty password when we created the mariadb-source continer, we pass no password to the jdbc connection string.
MariaDB [test]> create table db_mariadb_target engine=connect table_type=JDBC tabname=t_maria connection='jdbc:mariadb://mariadb-source/db_maria?user=root&password'\G
MariaDB [test]> select * from db_mariadb_target;
+------+
| t |
+------+
| 1 |
| 2 |
| 3 |
+------+Similarly, to connect o our mysql-source container, we just need to adjust the connection string.
MariaDB [test]> create table db_mysql_target engine=connect table_type=JDBC tabname=t_mysql connection='jdbc:mariadb://mysql-source/db_mysql?user=root&password'\G
MariaDB [test]> select * from db_mysql_target;
+------+
| t |
+------+
| 4 |
| 5 |
| 6 |
+------+Great! We got access from remote DBMSes into our target (local) container using the JDBC table type.
5.3 Alter Connect SE table
For all intents and purposes, applications can use this table as if it’s part of the “target” DBMS. However, what if we want to have all the performance benefits of InnoDB? Well, we can just move our Connect SE table to InnoDB. The data won’t be updated when the external table changes, but we’ll have all the data at the moment of conversion to InnoDB.
MariaDB [test]> show tables;
+-------------------+
| Tables_in_test |
+-------------------+
| db_mariadb_target |
| db_mysql_target |
| t |
| t_jdbc |
+-------------------+
4 rows in set (0.000 sec)To import our data, we’ll create a clone table first. Then run ALTER TABLE on it to change the storage engine to InnoDB. This way we’ll preserve the Connect SE table attributes, in case we want to switch back in the future:
MariaDB [test]> create table temp like db_mysql_target;
MariaDB [test]> show create table temp\G
*************************** 1. row ***************************
Table: temp
Create Table: CREATE TABLE `temp` (
`t` int(10) DEFAULT NULL
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='jdbc:mysql://mysql-source/db_mysql?user=root&password' `TABLE_TYPE`='JDBC' `TABNAME`='t_mysql'
1 row in set (0.000 sec)
MariaDB [test]> alter table temp engine=InnoDB;
Query OK, 3 rows affected (0.037 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table temp\G
*************************** 1. row ***************************
Table: temp
Create Table: CREATE TABLE `temp` (
`t` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci CONNECTION='jdbc:mysql://mysql-source/db_mysql?user=root&password' /* `TABLE_TYPE`='JDBC' `TABNAME`='t_mysql' */
1 row in set (0.000 sec)6. Automate with Docker Compose
The nice part of using Docker and containers is that we can automate most of this work. In this repository on GitHub You will find a docker-compose file and the related scripts you can use to achive the same outcome by simply running docker-compose up.
After starting, you can examine the tables directly:
docker exec -t mariadb-target mariadb test -e "select 'mysql' as src,t from db_mysql_target union all select 'mariadb',t from db_mariadb_target"
+---------+------+
| src | t |
+---------+------+
| mysql | 4 |
| mysql | 5 |
| mysql | 6 |
| mariadb | 1 |
| mariadb | 2 |
| mariadb | 3 |
+---------+------+Read more
Feedback Welcome
If you come across any problems in this blog, with the design, or edge cases that don’t work as expected, please let us know. You are welcome to chat about it on Zulip. As always you can use our JIRA bug/feature request in the MDEV project for any bug/feature request you may encounter.