Skip to main content
The steps in this guide for configuring a MySQL database are necessary to use the MySQL connector. These steps are based on advice in the MySQL documentation. It’s recommended that you familiarize yourself with this before continuing.

​
Prerequisites

  • When setting up MySQL, you need a MySQL user with the following permissions:
    • SELECT
    • RELOAD
    • SHOW DATABASES
    • CREATE
    • ALTER
    • DROP (optional)
A sample SQL statement to create a user with the necessary permissions: 
CREATE USER 'your_user'@'%' IDENTIFIED BY 'your_password';
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'your_user' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;

​
Configure MySQL binary logs for replication

Binary log (BinLog) replication must be enabled. A binary log is a set of log files that keep track of data and data object changes performed on a MySQL server instance. Binary logs are commonly used for data replication and recovery. supports binary log data ingestion for replication from MySQL servers (BinLog). Binary logging must be enabled on your MySQL server for this to work. This can be done using the MySQL server configuration file or the MySQL server starting options. Follow these steps to set up BinLog replication. First, check whether the log-bin option is already on.
  1. Access the MySQL database that you want to check for BinLog activity: 
    mysql -h hostname -u user -p database
  2. Open a secure shell and enter the command: 
    // for MySql 8.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM performance_schema.global_variables WHERE variable_name='log_bin';
If this statement returns a value of 1, BinLog is active. If the value returned is 0, this means that BinLog is disabled. To enable it, follow the steps below.
  1. Log in to your MySQL server instance.
  2. Check your MySQL Server configuration: 
    sudo nano /etc/mysql/my.cnf
    In some cases, this may be sudo nano /etc/my.cnf.
  3. In the configuration file, ensure the following configurations are specified. If they aren’t specified, add them now: 
     [mysqld]
 binlog_format=ROW
 binlog_row_image=FULL
 expire_logs_days=10  -- The retention period (`expire_log_days`) can also be set in seconds by using the command: `binlog_expire_logs_seconds=259200`
 log_bin=mysql-binlog  -- For ubuntu, use: `/var/log/mysql/mysql-bin.log`
 server-id=1 -- (only in the case of ubuntu)
  4. Restart the MySQL server using the command service mysql restart on your instance.
  5. After a restart, log in to the MySQL server to check the BinLog again: 
    // for MySql 8.x
mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM performance_schema.global_variables WHERE variable_name='log_bin';
    The value returned should now be 1. This indicates that BinLog is active.
    The retention period should ideally be at least 72 hours (3 days). This assists Matillion in ensuring that no log files are missing when historical data loading is enabled.

​
Descriptions of MySQL BinLog configuration properties

PropertyDescription
server-idThe value for the server-id must be unique for each server, and replication client in the MySQL cluster. During the MySQL connector set up, the assigns a unique server ID to the connector.
log_binThe value of log_bin is the base name of the sequence of the BinLog files.
BinLog_formatThe BinLog_format must be set to ROW or row.
BinLog_row_imageThe BinLog_row_image must be set to FULL or full.
expire_logs_daysThe number of days for an automatic BinLog file removal. The default is 0, which means no automatic removal. Set the value to match the needs of your environment.

​
Grant privileges to the user

  1. Check that the database user specified in the streaming pipeline has the following global privileges:
    • Select
    • SUPER or (REPLICATION_CLIENT and REPLICATION_SLAVE)
    The suggested grant privileges above provide access to all schemas and tables. Check with your database administrator for an appropriate mask.
  2. There are two methods for setting up these privileges:
    • Go to your MySQL server. For example: mysql -u root -p, and grant SELECT privileges to the user, using the following command: 
      GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON \*.* TO 'cdcuser'@'%';
      The username must match the user specified in the streaming pipeline, or vice versa.
    • If the MySQL database is deployed via RDS, grant the authenticated user of the agent an additional permission called LOCK TABLES, using the following command: 
      GRANT SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, REPLICATION SLAVE, REPLICATION CLIENT ON \*.* TO 'cdcuser'@'%';
      If you’re deploying the MySQL database using Amazon RDS or Amazon Aurora that doesn’t allow a global read lock, table-level locks are used to create a consistent snapshot.