Contents x
    MySQL Log-Based Configuration for Version 1
    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Contents

    MySQL Log-Based Configuration for Version 1

    • 3 Minutes to read
    • Print
    • Dark
      Light
    • PDF
    Article Summary

    Version 1 will be deprecated in the near future.

    This is a step-by-step guide on how to configure MySQL Log-Based.

    Prerequisites

    • MySQL 5.6+ or MySQL 8.0+.
    • MySQL server instance running.
    • Permission to 'GRANT' and 'CREATE USER' in MySQL. 
    • A connection to the Master / Primary Database is necessary; Read Replicas are not supported.


    Configuring CDC on a MySQL On-Prem Server

    You'll need access to the server's configuration files if the MySQL database is hosted on an on-premise server.

    Check to see if the server's 'binlog' is enabled.

    Run the following SQL statement to see if the 'binlog' is enabled in the MySQL server:

    SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::" 
FROM information_schema.global_variables WHERE variable_name='log_bin';

    If the outcome is 'OFF,' make the following changes to your MySQL configuration file (my.cnf):

    •  Set binlog_format to ROW
    • Set binlog_row_image to FULL
    • Set expire_logs_days to 10
    • Set log_bin to mysql-bin
    • Make sure your server has enough storage to keep your database 'binlog' for ten days.


    Configuring CDC on Amazon RDS

    If you're using Amazon RDS, configuring these properties can be done through the RDS Console.

    How to Create a Parameter Group:

    1. Log in to the Amazon RDS console.
    2. Go to Parameter Groups and then Create Parameter Group.
    3. Create a parameter group to use with the database:
      • Under Parameter group family, select the relevant MySQL version.
      • Click Create after selecting a suitable Group Name and Description.
    4. Set the relevant parameters:
      • Choose a new group from the drop-down menu.
      • Click on Edit Parameters.
    5. Set the following relevant parameters in the parameter group:
      • Set binlog_format to ROW.
      • Set binlog_row_image to Full.


    Set the Duration of Your 'binlog' Retention Period

    Rivery suggests a 10-day 'binlog' retention period.
    Due to out-of-sync scenarios, this is required to limit the amount of resync (migration) required from the database.

    According to the Amazon RDS documentation, the default strategy is to remove the log as quickly as possible, so you'll need to increase the retention period from the database's default setting.

    To check the current state of the bin log retention period, do the following:

    1. Connect to your MySQL using Workbench.
    2. Run the following command:

    mysql> call mysql.rds_show_configuration;
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                          |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
| binlog retention hours | NULL  | binlog retention hours specifies the duration in hours before binary logs are automatically deleted. |
+------------------------+-------+------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

Query OK, 0 rows affected (0.06 sec)

    If the 'binlog retention hours' parameter is NULL or 0, the database will erase the log as quickly as possible.

    Run the following command to increase this configuration value:

    call mysql.rds_set_configuration('binlog retention hours', 168);

    Caution:

    Increased binary log retention hours demands more disk space in your source Amazon RDS MySQL database since the number of log files saved at one time grows.

    After you've established the retention period, keep an eye on the DB instance's storage usage to make sure the binary logs you want to keep aren't taking up too much space.


    Create User for Replication

    In order to get the 'binlog', create a replication user for Rivery or change an existing user with the following grants and permissions.

    To create a user for Rivery, run the following command:

    For MySQL versions prior to 8.0:

    CREATE USER 'rivery'@'%' IDENTIFIED BY 'password';


    For MySQL versions 8.0 and later:

    CREATE USER 'rivery'@'localhost' IDENTIFIED WITH  mysql_native_password  BY '< password >';
    Warning:
    When creating a new MySQL user for Rivery, the password must be no longer than 32 characters.


    Permissions for Version 1 (V1) Log-Based

    Grant the user with the right permissions for Version 1 and set the necessary privileges. 

    Note:

    The same privileges can be applied to an existing user.


    Set the <database> to the appropriate database(s) on your server, or set it to * to all databases:

    GRANT SELECT, SHOW DATABASES, REPLICATION SLAVE, RELOAD, REPLICATION CLIENT, LOCK TABLES, EXECUTE ON *.* TO 'rivery'@'%';

    Run the following command to grant the user permissions:

    FLUSH PRIVILEGES;

    Make sure you're connected to Rivery with the user you just configured.


    Custom Timezone for V1

    Rivery uses a default connection with timezone UTC if your DB is in CEST timezone our connector expect a custom timezone
    Example:


    You can compare the timezone needed to the selected DB:
    https://en.wikipedia.org/wiki/List_of_tz_database_time_zones


    Was this article helpful?
    What's Next