Manage MySQL catalog

After you configure a MySQL catalog, you can access the tables of a MySQL instance in the console of fully managed Flink. This topic describes how to configure, view, use, and delete a MySQL catalog in the console of fully managed Flink.

Background Information

When you use a MySQL catalog, take note of the following points:

• You can directly access a table of a MySQL instance without the need to execute DDL statements to register the MySQL table. This improves the efficiency and accuracy of data development.
• Tables of MySQL catalogs can be used as MySQL Change Data Capture (CDC) source tables, MySQL result tables, and MySQL dimension tables in Flink SQL jobs.
• Catalogs of self-managed MySQL databases are supported.
• Sharding-based logical tables can be directly accessed.
• You can use the CREATE DATABASE AS and CREATE TABLE AS statements to synchronize the full data of a database, the merged data of sharded tables in a sharded database, and changes in table schemas based on MySQL data sources.

Limits

• You cannot modify the DDL statements that are related to catalogs.
• You can only query data in databases. You cannot create databases.
• If a table of a MySQL catalog is used as a MySQL CDC source table, you can read data from the source table only in streaming mode. If a table of a MySQL catalog is used as a dimension table or a result table, streaming processing is supported.
• Only MySQL 5.7 and MySQL 8.0.X are supported.
• When reading data from a table in the MySQL catalog, you can use the dynamic table option to disable SSL for that specific query. Here's an example of a query that does this:

1 SELECT * FROM `my-aws-mysql`.`vvc`.user_1
2 /*+ OPTIONS('jdbc.properties.useSSL' = 'false') */

• By including the OPTIONS clause with 'jdbc.properties.useSSL' = 'false', you ensure that the query does not use SSL encryption when connecting to the MySQL database.

• When writing data to a MySQL table in the MySQL catalog, you can override the connection URL by using the dynamic table option to disable TLS. Here's an example of how to do this using Flink SQL:

1INSERT INTO `my-aws-mysql`.`vvc`.user_2
2/*+ OPTIONS('url' = 'jdbc:mysql://your_hostname:3306/your_database_name?useSSL=false') */

• Replace your_hostname with the actual hostname of your MySQL server, and your_database_name with the name of the database you want to connect to. This query will write data from source_table into the user_2 table in the MySQL catalog with TLS disabled.

Create a MySQL Catalog in the UI

1. In the left navigation, click the Namespace selector and select the namespace that you want to open.
2. Click Catalogs.
3. On the Catalog list page, click Create Catalog.
4. In the Create Catalog dialog box, select MySQL and click Next.
5. Configure the parameters.

1. Click Confirm. You can see the created catalog on the catalogs list page in the Catalogs tab, as well as on the SQL Editor page (the right side Catalogs menu).

Create a MySQL Catalog by Executing an SQL Statement

1. In the left navigation, click the Namespace selector and select the namespace that you want to open.
2. Click SQL Editor.
3. In the upper-left corner of the SQL Editor page, click New.
4. On the SQL Scripts tab of the New Draft dialog box, click Blank Stream Draft and click Next.
5. Configure the parameters of the draft. For more information, see the parameters table above, in the UI (#create-a-mysql-catalog-in-the-ui) section.
6. Click Create. In the script editor, enter the following statement to create a MySQL catalog:

1    CREATE CATALOG <catalogname> WITH(
2    'type' = 'mysql',
3    'hostname' = '<hostname>',
4    'port' = '<port>',
5    'username' = '<username>',
6    'password' = '<password>',
7    'default-database' = '<dbname>',
8    'catalog.table.metadata-columns' = '<metadata>'
9    );

You can select the SQL text and click the hovered Run button to execute the selected SQL, then the catalog will be created.

1. You can see the created catalog on the catalogs list page in the Catalogs tab, as well as on the SQL Editor page (the right side Catalogs menu).

View the Metadata of a MySQL Catalog

After you configure a MySQL catalog, you can perform the following steps to view the metadata of the MySQL catalog.

1. In the left navigation, click the Namespace selector and select the namespace that you want to open.
2. Click Catalogs.
3. On the Catalog list page, select the MySQL catalog whose metadata you want to view.
4. View information about databases, and tables in the MySQL catalog.

Use a MySQL Catalog

Read Data from a MySQL CDC Source Table

1    INSERT INTO ${other_sink_table}
2    SELECT ...
3    FROM `${mysql_catalog}`.`${db_name}`.`${table_name}` /*+ OPTIONS('server-id'='6000-6018') */;

Read Data from the Sharding-based MySQL Logic Tables

MySQL catalogs allow you to configure the databases and tables in a sharded database as a logical table in a query statement by using regular expressions and executing the query statement to read data from the logical table. For example, if a sharded MySQL database has tables, such as user01, user02, and user99, in database shards from db01 to db10 and the schemas of all the tables are compatible with each other, you can access all the tables in the database shards by using the following regular expression:

1    SELECT ... FROM `db.*`.`user.*` /*+ OPTIONS('server-id'='6000-6018') */;

The query result contains two additional system fields _db_name (STRING) and _table_name (STRING). The two fields and the primary key of the original tables are used as the new joint primary key of the logical table to ensure that the joint primary key is unique. If the primary keys of the tables from user01 to user99 are id, the joint primary key of the logical table named user is (_db_name, _table_name, id). For more information about how to read data from sharded tables in a sharded database by using regular expressions, see CREATE TABLE AS statement.

Execute the CREATE TABLE AS and CREATE DATABASE AS Statements to Synchronize MySQL Data Changes and Schema Changes in Real-time

1    USE CATALOG `${target_catalog}`;
2
3    -- Single-table synchronization: Synchronize schema changes and data changes of tables in real time.
4    CREATE TABLE IF NOT EXISTS `${target_table_name}`
5    WITH (...)
6    AS TABLE `${mysql_catalog}`.`${db_name}`.`${table_name}`
7    /*+ OPTIONS('server-id'='6000-6018') */;
8
9    -- Database synchronization: Synchronize schema changes and data changes of the database in real time.
10    CREATE DATABASE `${target_db_name}` WITH (...)
11    AS DATABASE `${mysql_catalog}`.`${db_name}` INCLUDING ALL TABLES
12    /*+ OPTIONS('server-id'='6000-6018') */;

For more examples, see CREATE TABLE AS statement or CREATE DATABASE AS statement.

Read Data from a MySQL Dimension Table

1    INSERT INTO ${other_sink_table}
2    SELECT ...
3    FROM ${other_source_table} AS e
4    JOIN `${mysql_catalog}`.`${db_name}`.`${table_name}` FOR SYSTEM_TIME AS OF e.proctime AS w
5    ON e.id = w.id;

Write the Result Data to a MySQL Table

1    INSERT INTO `${mysql_catalog}`.`${db_name}`.`${table_name}`
2    SELECT ...
3    FROM ${other_source_table}

Delete a MySQL Catalog

You can delete a MySQL catalog in the UI or by executing an SQL statement. Ververica recommends that you delete a MySQL catalog in the UI.

Delete a MySQL Catalog in the UI

1. In the left navigation, click the Namespace selector and select the namespace that you want to open.
2. Click Catalogs.
3. On the Catalog list page, choose the required catalog and click on Delete in the Actions column.
4. In the message that appears, click Delete.
5. View the catalog that you delete in the Catalogs pane on the left side of the Catalog List page. After the catalog is deleted, the catalog does not exist on this page.

Delete a MySQL Catalog by Executing an SQL Statement

1. In the left navigation, click the Namespace selector and select the namespace that you want to open.
2. Click SQL Editor.
3. In the upper-left corner of the SQL Editor page, click New.
4. On the SQL Scripts tab of the New Draft dialog box, click Blank Stream Draft and click Next.
5. Configure the parameters of the draft. For more information, see Create a Draft. Then, click Create.
6. In the script editor, enter the following statement: catalog_name is the name of the MySQL catalog that you want to delete in the console of fully managed Flink.

1    DROP CATALOG ${catalog_name}

1. On the left side of the SQL Editor page, click the Catalogs tab to check whether the catalog is deleted.