Skip to main content

MySQL Configuration

Welcome to your comprehensive guide on configuring MySQL as a source database for Boltic Pipes! This document will walk you through each step to ensure a seamless integration of MySQL with Boltic’s data pipeline framework.

Boltic Pipes is designed to synchronize and transfer data efficiently. To get MySQL set up as a source, you'll need to configure various settings to ensure smooth operation and data flow. This guide will cover essential configurations and best practices to make your setup robust and efficient.

Required Privileges​

To integrate your MySQL database with Boltic Pipes, ensure the following privileges are granted:

  • SELECT: Allows reading data from tables.
  • RELOAD: Enables reloading of the grant tables and flushing of logs.
  • SHOW DATABASES: Permits viewing all databases.
  • REPLICATION SLAVE: Required for replication slave connections.
  • REPLICATION CLIENT: Needed to query replication status.
  • LOCK TABLES: Allows locking tables for the current session.

Database Configuration​

Configure your MySQL instance with the settings below to ensure proper operation:

  • Enable Binary Logging: Set log_bin to ON to enable binary logging.
  • Set Binary Log Format: Ensure binlog_format is set to ROW for accurate data replication.
  • Set Binlog Retention Period: Configure the retention period to at least 24 hours.

Configure the binlog retention period with the following SQL command:

-- Set the binary log retention period to 24 hours
-- This command adjusts the retention period for binary logs.
-- Binary logs are used for replication and data recovery.
-- The retention period specifies how long these logs are kept
-- before they are automatically purged. Setting it to 24 hours
-- ensures that logs are retained for at least a day.
CALL mysql.rds_set_configuration('binlog retention hours', 24);

User Creation And Grants​

If a new user needs to be created, follow these steps. Avoid using master credentials:

-- Create a new MySQL user with the specified username and password
CREATE USER 'boltic_cdc_user' IDENTIFIED BY 'password';

-- Grant necessary privileges to the new user
-- PRIVILEGES:
-- REPLICATION SLAVE: Required for replication slave connections
-- SELECT: Allows reading data from tables
-- SHOW DATABASES: Allows viewing all databases
-- RELOAD: Enables reloading of the grant tables and flushing of logs
-- REPLICATION CLIENT: Needed to query replication status
-- LOCK TABLES: Allows locking tables for the current session
-- EXECUTE: Allows execution of stored procedures
GRANT REPLICATION SLAVE, SELECT, SHOW DATABASES, RELOAD, REPLICATION CLIENT, LOCK TABLES, EXECUTE
ON *.* TO 'boltic_cdc_user'@'%';

-- Apply the changes to ensure the new privileges are active
FLUSH PRIVILEGES;

🤓 Have Questions?​

We're here to help! Feel free to reach out via email at 📧 [email protected].