MariaDB

MariaDB is a versatile open-source relational database management system used for high-availability transaction data, analytics, as an embedded server, and is widely supported by various tools and applications. ClickPipes Cloud provides comprehensive support for building data pipelines utilizing MariaDB as both the source and target database.

Supported versions

• Version: 5.x, 10.x
• Architecture: Single-node or primary-replica architecture

Supported Data Types

Category	Data Types
String	CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, BINARY, VARBINARY
Integer	TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
Numeric	DECIMAL, FLOAT, DOUBLE
Date/Time	DATE, TIME, DATETIME, TIMESTAMP, YEAR
Binary Large Objects	TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB
Spatial Data	POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMCOLLECTION
Other	BIT, ENUM, SET, JSON

Preparations

To ensure smooth execution of tasks, you need to enable Binlog in the MariaDB database (for incremental data synchronization) and then create a database account for data replication/development tasks.

1. Log into the MariaDB database and execute the following format of command to create an account for data synchronization/development tasks.

   CREATE USER 'username'@'host' IDENTIFIED BY 'password'
   • username: Enter user name.
   • password: Enter password.
   • host: Enter the host that can be accessed by the account, percent (%) means to allow all host. Example: Create an account named clickpipes: CREATE USER 'clickpipes'@'%' IDENTIFIED BY 'clickpipes@123456';

2. Grant privileges to the newly created account. Below is a simplified example, and it is recommended to set more detailed permissions based on business needs.

Grant Specific Database Permissions

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username' IDENTIFIED BY 'password';
GRANT SELECT ON database_name.* TO 'username' IDENTIFIED BY 'password';

Grant All Database Permissions

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'username' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'username' IDENTIFIED BY 'password';

• username: Enter user name.
• password: Enter password.
• host: Enter the host that can be accessed by the account, percent (%) means to allow all host.

3. To ensure access to incremental data from the MariaDB database, follow the steps below to enable Binlog.

   1. Use the vim command to modify the configuration of MariaDB. For instance:

      In the scenario demonstrated in this article, MariaDB is deployed on the Ubuntu operating system, and the configuration file is located at /etc/mysql/mariadb.cnf. For more information, see MariaDB Configuration File Introduction.

      [mysqld]
      server-id              = 1
      log_bin                = /var/log/mysql/myriadb-bin
      expire_logs_days       = 10
      max_binlog_size        = 100M
      binlog_format = ROW
      binlog_row_image = FULL
      • server_id: Must be unique for each server and replication client in MariaDB, set as an integer greater than 0.
      • log_bin: The base name of the binlog file.
      • expire_logs_days: The number of days to keep binary log files before automatic deletion.
      • max_binlog_size: The maximum size of a single binlog file.
      • binlog_format: Set to row, which records which rows were modified.
      • binlog_row_image: Set to full, recording data for all columns, whether they changed or not.

   2. After modification, execute the following command to restart the MariaDB database. Perform this during off-peak hours to avoid impacting services.

      systemctl restart mariadb

   3. (Optional) Log into the MariaDB database and execute the following command to confirm that the configuration has taken effect, i.e., the output shows format as ROW.

      SHOW VARIABLES LIKE 'binlog_format';

      Example output:

      +---------------+-------+
      | Variable_name | Value |
      +---------------+-------+
      | binlog_format | ROW   |
      +---------------+-------+
      1 row in set (0.00 sec)

Enabling SSL Connection (Optional)

To further enhance the security of the data link, you can choose to enable SSL (Secure Sockets Layer) encryption for the MariaDB database. This ensures encryption at the transport layer for network connections, enhancing communication data security while maintaining data integrity. The specific steps are as follows:

1. Create SSL certificates and private keys, which can be self-signed or obtained from a certificate authority.

   Next, we will demonstrate how to create a self-signed certificate using OpenSSL. Before proceeding, you can log into the MariaDB database and execute SHOW GLOBAL VARIABLES LIKE '%ssl%'; to check if SSL/RSA files have been generated and the SSL status.

   1. Log into the device hosting the MariaDB database and execute the following commands to generate a CA certificate. For more information, see OpenSSL.

      # Create and enter the directory where the certificate files will be stored, adjust as needed
      mkdir -p /etc/mysql/ssl&&cd /etc/mysql/ssl
      # Generate the CA certificate
      openssl genrsa 2048 > ca-key.pem
      openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

   2. Generate server certificates and keys, which will be used for server-side SSL encryption.

      openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
      openssl rsa -in server-key.pem -out server-key.pem
      openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

   3. Generate client certificates and keys for two-way authentication.

      openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
      openssl rsa -in client-key.pem -out client-key.pem
      openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

   4. Verify the correctness of the keys, returning OK if there are no issues.

      openssl verify -CAfile ca-cert.pem server-cert.pem
      openssl verify -CAfile ca-cert.pem client-cert.pem

2. Edit the configuration file of MariaDB, adding the following content in the [mysqld] section. Replace filepath with the path where the CA certificate files are located. Save and exit the editor after making changes.

   In this case, MariaDB is deployed on the Ubuntu operating system, and the configuration file is located at /etc/mysql/mariadb.cnf. For more information, see MariaDB Configuration File Introduction.

   [mysqld]
   # Self-signed CA certificate
   ssl-ca=/etc/mysql/ssl/ca-cert.pem
   # Server certificate file
   ssl-cert=/etc/mysql/ssl/server-cert.pem
   # Server private key file
   ssl-key=/etc/mysql/ssl/server-key.pem
   
   [client]
   # Certificate file required for the client to connect to the server
   ssl-cert=/etc/mysql/ssl/client-cert.pem
   # Private key file required for the client to connect to the server
   ssl-key=/etc/mysql/ssl/client-key.pem

3. Execute the following command to adjust the permissions of the certificate files.

   chown -R mysql:mysql /etc/mysql/ssl/

4. Log into the MariaDB database and choose to execute the following format of commands to adjust the account for data synchronization/development tasks.

   ALTER USER 'username'@'host' REQUIRE x509; -- Force the client to provide a valid certificate
   ALTER USER 'username'@'host' REQUIRE ssl; -- Do not force the client to provide a valid certificate
   FLUSH PRIVILEGES;
   • username: Enter user name.
   • host: Enter the host that can be accessed by the account, percent (%) means to allow all host.

5. Restart the MariaDB database.

   systemctl restart mariadb

Source Config

* **Connection Information Settings**

  * **Connection name**: Fill in a unique name that has business significance.
  * **Connection type**: Supports MariaDB as a source or target database.
  * **Host**: The database connection address.
  * **Port**: The service port of database.
  * **Database**: Database name, a connection corresponding to a database, if there are multiple databases, you need to create multiple connections.
  * **username**: The database username.
  * **Password**: The database password.
  * **Connection parameter string**: Additional connection parameters, default empty.
* **SSL Settings**: Choose whether to enable SSL connections for the data source, which can further enhance data security. After turn on the switch, you will need to upload CA files, client certificates, client key files, etc. The related files can be obtained as outlined in the [Enabling SSL Connection](#ssl) section.