Connecting to the MySQL/Doris Data Source Through JDBC Catalog

Scenario

JDBC Catalog can connect to data sources such as MySQL and Doris through the standard JDBC protocol. This topic describes how to use JDBC Catalog to read MySQL or Doris data.

The operations in this section are applicable to MRS 3.3.1 and later versions.

Prerequisites

• An MRS cluster has been created and the Doris service has been deployed.
• A source cluster, for example, a MySQL server cluster, has been prepared.
• The Doris cluster can communicate with the MySQL server cluster.
• The MySQL client has been installed. For details, see Using the MySQL Client to Connect to Doris.

Querying MySQL/Doris Data Through JDBC Catalog

1. Visit the MySQL official website at https://www.mysql.com/, choose DOWNLOADS > MySQL Community(GPL) DownLoads > Connector/J, and download the driver package of the required version.
   

   The recommended MySQL driver version is 5.1.x or later.

2. Upload the downloaded MySQL driver package to the same directory on all FE and BE nodes, for example, /home/omm, and change the owner group of the driver package to omm:wheel.
   

   If Doris FE and BE are deployed on the same nodes, upload the MySQL driver package to the ${BIGDATA_HOME}/FusionInsight_Doris_*/install/FusionInsight-Doris-*/doris-fe/jdbc_drivers/ directory.

3. Log in to FusionInsight Manager and choose Cluster > Services > Doris > Configurations > All Configurations > FE(Role) > Customization.
4. Add the jdbc_drivers_dir parameter to the custom parameter fe.conf.customized.configs. The value is the directory where the driver package uploaded to in 2, for example, /home/omm.
5. Choose All Configurations > BE(Role) > Customization. Add the jdbc_drivers_dir parameter to the fe.conf.customized.configs parameter and set it to the directory where the driver package uploaded to in 2, for example, /home/omm.
6. Search for the enable_udf_sandbox parameter of BE and set it to false.
7. Click Save. On the Dashboard page, click More > Restart Service in the upper right corner. Enter the password of the user and click OK to restart the Doris service.
8. Log in to the node where MySQL is installed and run the following command to connect to the Doris database:

   If Kerberos authentication is enabled for the cluster (the cluster is in security mode), run the following command to connect to the Doris database:

   export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1

   mysql -uDatabase login user -p -PConnection port for FE queries -hIP address of the Doris FE instance

   Enter the password for logging in to the database.

   

   • To obtain the query connection port of the Doris FE instance, you can log in to FusionInsight Manager, choose Cluster > Services > Doris > Configurations, and query the value of query_port of the Doris service.
   • To obtain the IP address of the Doris FE instance, log in to FusionInsight Manager of the MRS cluster and choose Cluster > Services > Doris > Instances to view the service IP address of any FE instance.
   • You can also use the MySQL connection software or Doris web UI to connect to the database.

9. Create a JDBC catalog in the target Doris cluster.
   • If the source cluster is a MySQL server and Kerberos authentication is disabled for the target Doris cluster, run the following commands:

     CREATE CATALOG doris_jdbc_catalog PROPERTIES (

     "type"="jdbc",

     "user"=" MySQL username ",

     "password"="MySQL user password",

     "jdbc_url" = "jdbc:mysql://IP address of MySQL: MySQL port ?useSSL = false",

     "driver_url" = "mysql-connector-java-xxx.jar",

     "driver_class" = "com.mysql.jdbc.Driver"

     );

   • If both the source and destination are MRS Doris clusters and Kerberos authentication is disabled for the clusters, run the following commands:

     CREATE CATALOG doris_jdbc_catalog PROPERTIES (

     "type"="jdbc",

     "user"=" Doris username ",

     "password"="Doris user password",

     "jdbc_url" = "jdbc:mysql:// IP address of the source Doris FE instance:FE query connection port ?useSSL = false",

     "driver_url" = "mysql-connector-java-xxx.jar",

     "driver_class" = "com.mysql.jdbc.Driver"

     );

   • If both the source and destination are MRS Doris clusters and Kerberos authentication is enabled for the clusters, run the following commands:

     CREATE CATALOG doris_jdbc_catalog PROPERTIES (

     "type"="jdbc",

     "user"=" Doris username ",

     "password"="Doris user password",

     "jdbc_url" = "jdbc:mysql://IP address of the source Doris FE instance:FE query connection port?allowPublicKeyRetrieval=true&useSSL=true&verifyServerCertificate=false",

     "driver_url" = "mysql-connector-java-xxx.jar",

     "driver_class" = "com.mysql.jdbc.Driver"

     );

   

   • If the MySQL driver version is 6.x or later, add the serverTimezone=GMT%2B8 parameter to jdbc_url when creating a JDBC catalog to prevent creation failure caused by time zone inconsistency between the source cluster and the current Doris cluster.
   • If the MySQL driver version is 6.x or later, the value of driver_class is com.mysql.cj.jdbc.Driver. If the MySQL driver version is 5.x, the value of drive_class is com.mysql.jdbc.Driver.
   • To obtain the query connection port of the Doris FE instance, you can log in to Manager, choose Cluster > Services > Doris > Configurations, and query the value of query_port of the Doris service.
   • To obtain the IP address of the Doris FE instance on the source cluster, log in to the Manager of the MRS cluster and choose Cluster > Services > Doris > Instances to view the IP address of any FE instance.

10. Run the following commands to switch to the newly created catalog and view the database:

    switch doris_jdbc_catalog;

    show databases;

    If the MySQL database name and table name are displayed in the command output, Doris can access the MySQL cluster through JDBC Catalog.