Generic MySQL (Edge)

Prerequisites

• The MySQL server (not a local host) is running. To check this, connect to your MySQL database instance and run the following command in the terminal window:

  service mysql status
  

  The status must show as Active: active (running).

• The MySQL version is 5.6, 5.7, 8.0, or 8.4. To check this, connect to your MySQL database instance and run the following command in the terminal window:

  mysql -V
  

• Binary Log (BinLog) replication is enabled.

  Note: Hevo currently supports ingesting data from read replicas only for Percona Server for MySQL version 8.0. The steps to create a read replica depend on your infrastructure or database host. For example, you can use Percona’s standard replication setup method of creating replicas using Percona XtraBackup.

• Hevo’s IP address(es) for your region is added to your MySQL database IP Allowlist.

• The SELECT, RELOAD, SHOW DATABASES, and REPLICATION privileges are granted to the database user.

  Note: We recommend that you create a database user for configuring your MySQL Source in Hevo. However, if you already have one, refer to section Grant privileges to the user.

Perform the following steps to configure your Generic MySQL Source:

Set up MySQL Binary Logs for Replication

In MySQL, the binary log (BinLog) is a collection of log files that records modifications, including deletes, made to data objects in your server instance. Binary logs are typically used for data replication and data recovery.

Hevo uses binary logs to replicate incremental data from your MySQL server. For this, you must enable binary logging on your MySQL server instance. Binary logging can be enabled by modifying the MySQL configuration file or by providing startup options to mysqld.

Note: It is recommended to enable binary logging by modifying the configuration file, as settings applied via startup options persist only until the server restarts.

Perform the following steps to set up BinLog replication:

1. Check if BinLog replication is already enabled

1. Access the MySQL database you want to check for BinLog activity:

   mysql -h hostname -u user -p database
   

2. Open a secure shell:

   ssh user@localhost
   

3. Enter the command:

   select @@log_bin;
   

   If this statement returns a value of 1, BinLog is active. If value returned is 0, this means that BinLog is disabled. To enable it, follow the steps below.

2. Enable BinLog replication

1. Log in to your MySQL server instance.

2. Check your MySQL Server configuration:

   sudo nano /etc/mysql/my.cnf
   

   This may be sudo nano /etc/my.cnf in some cases. In some installations, the file may be found in /etc/.

3. In the configuration file, ensure the following settings are specified. If not, add them now.

   [mysqld]
   
   binlog_format=ROW
   
   binlog_row_image=FULL
   
   # Retention period: 3 days (86400*3=259200), number of seconds in a day=86400
   # For MySQL 8.0 and higher, use binlog_expire_logs_seconds=259200
   expire_logs_days=3      # For MySQL 5.7 and older
   
   # Path to binary logs, typically created in the data directory (def)
   # For Ubuntu set to: /var/log/mysql/mysql-bin.log
   log_bin=mysql-binlog
   
   # Must be a unique positive integer
   server-id=1
   
   # A unique identifier (GTID) for every transaction committed on the server
   gtid_mode=ON
   
   # A safety filter to ensure that only operations safe for GTID-based replication are run
   enforce_gtid_consistency=ON
   
   # Maximum size in bytes of one packet or any generated/intermediate string
   max_allowed_packet=16777216
   
   # (Optional) Add these parameters for longer-lived connections in case of SSH
   net_read_timeout=600            # Seconds to wait for more data from a network connection before terminating the read operation
   net_write_timeout=600       # Seconds to wait for more data from a network connection before terminating the read operation
   wait_timeout=86400          # Seconds to wait for before closing a non-interactive network or file connection
   interactive_timeout=86400   # Seconds to wait for before closing an interactive network or file connection
   

   Note:

   • Enabling Global Transaction Identifiers (GTIDs) is recommended because it simplifies replication by uniquely identifying transactions. This makes it easier to track them and ensures that the primary and replica servers are in sync.
   • The SSH parameters are necessary to maintain a stable data connection for an extended period during data replication.

4. (Optional) Validate the MySQL configuration using the following command:

   mysqld --validate-config
   

   Note: If this command returns no output, your configuration is syntactically correct.

5. Restart the MySQL server using the following command:

   service mysql restart
   

6. After restarting, log in to the MySQL server and do the following:

   1. Recheck the value of BinLog by running the following command:

      SELECT @@log_bin;
      

      The value returned is now 1, indicating that BinLog is active.

      Note: The retention period should ideally be at least 72 hours (3 days). This helps ensure that Hevo does not miss any log files, especially when historical data replication is enabled.

   2. Verify that the binlog_format setting is set to ROW by running the following command:

      SHOW VARIABLES LIKE '%binlog_format%';
      

      The expected output should be:

      +---------------+------+
      | Variable_name | Value|
      +---------------+------+
      | binlog_format | ROW |
      +---------------+------+
      

      Note: Hevo cannot replicate data using binary logs if the value is not set to ROW.

7. Run the following command to check the current value of binlog_row_value_options variable:

      show global variables where variable_name = 'binlog_row_value_options';
   

8. If the value is set to PARTIAL_JSON, unset this variable by:

      set @@global.binlog_row_value_options="";
   

The replication reference guide on MySQL’s documentation portal provides a complete reference of the options available for replication and binary logging.

Allowlist Hevo IP addresses for your region

You need to allowlist the Hevo IP address for your region to enable Hevo to connect to your MySQL database. To do this:

1. Edit the MySQL server configuration:

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. Scroll to [mysqld] and add:

    bind-address = 0.0.0.0
   

   Or

    bind-address = 10.2.7.152
   

   Check the Hevo IP address for your region.

3. Save the file.

Configure SSH Server Settings (Optional)

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the user

The database user for Hevo requires the following privileges:

Connect to your MySQL database as an admin user with an SQL client tool, such as MySQL Workbench, and run the following script. These commands grant only the privileges required by Hevo to ingest data from your MySQL database.

# Grant Privileges to the Database User
GRANT RELOAD, SHOW DATABASES, REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO <database_username>@'%';

# (Optional) Grant Hevo access to all Tables in the Database
GRANT SELECT ON <database_name>.* TO <database_username>;

# Grant Hevo access to the Tables in the Database that you want to replicate
GRANT SELECT, SHOW VIEW ON <database_name>.<table_name> TO <database_username>;

# (Optional) View the grants for the user:
SHOW GRANTS FOR <database_username>@'%';

# Finalize the User’s Permissions
FLUSH PRIVILEGES;

Note:

• Replace the placeholder values in the commands above with your own. For example, <database_username> with hevo.

• The SELECT, RELOAD, and SHOW DATABASES privileges are required only for the historical load.