Setting up MySQL server

Creating a MySQL user for Debezium

You have to define a MySQL user with appropriate permissions on all databases that the Debezium MySQL connector monitors.

Prerequisites
  • You must have a MySQL server.

  • You must know basic SQL commands.

Procedure
  1. Create the MySQL user:

mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
  1. Grant the required permissions to the user:

mysql> GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user' IDENTIFIED BY 'password';
See permissions explained for notes on each permission.
If using a hosted option such as Amazon RDS or Amazon Aurora that do not allow a global read lock, table-level locks are used to create the consistent snapshot. In this case, you need to also grant LOCK_TABLES permissions to the user that you create. See [overview-of-how-the-mysql-connector-works] for more details.
  1. Finalize the user’s permissions:

mysql> FLUSH PRIVILEGES;

Permissions explained

Permission/item Description

SELECT

enables the connector to select rows from tables in databases

This is only used when performing a snapshot.

RELOAD

enables the connector the use of the FLUSH statement to clear or reload internal caches, flush tables, or acquire locks.

This is only used when performing a snapshot.

SHOW DATABASES

enables the connector to see database names by issuing the SHOW DATABASE statement.

This is only used when performing a snapshot.

REPLICATION SLAVE

enables the connector to connect to and read the MySQL server binlog.

REPLICATION CLIENT

enables the connector the use of following statements:

  • SHOW MASTER STATUS

  • SHOW SLAVE STATUS

  • SHOW BINARY LOGS

This is always required for the connector.

ON

Identifies the database to which the permission apply.

TO 'user'

Specifies the user to which the permissions are granted.

IDENTIFIED BY 'password'

Specifies the password for the user.

Enabling the MySQL binlog for Debezium

You must enable binary logging for MySQL replication. The binary logs record transaction updates for replication tools to propagate changes.

Prerequisites
  • You must have a MySQL server.

  • You should have appropriate MySQL user privileges.

Procedure
  1. Check if the log-bin option is already on or not.

mysql> SELECT variable_value as "BINARY LOGGING STATUS (log-bin) ::"
FROM information_schema.global_variables WHERE variable_name='log_bin';
  1. If OFF, configure your MySQL server configuration file with the following:

See Binlog config properties for notes on each property.
server-id         = 223344 (1)
log_bin           = mysql-bin (2)
binlog_format     = ROW (3)
binlog_row_image  = FULL (4)
expire_logs_days  = 10 (5)
  1. Confirm your changes by checking the binlog status once more.

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

Binlog configuration properties

Number Property Description

1

server-id

The value for the server-id must be unique for each server and replication client within the MySQL cluster. When the MySQL connector is setup, we assign the connector a unique server ID.

2

log_bin

The value of log_bin is the base name of the sequence of binlog files.

3

binlog_format

The binlog-format must be set to ROW or row.

4

binlog_row_image

The binlog_row_image must be set to FULL or full.

5

expire_logs_days

This is the number of days for automatic binlog file removal. The default is 0 which means no automatic removal.

Set the value to match the needs of your environment.

Enabling MySQL Global Transaction Identifiers for Debezium

Global transaction identifiers (GTIDs) uniquely identify transactions that occur on a server within a cluster. Though not required for the Debezium MySQL connector, using GTIDs simplifies replication and allows you to more easily confirm if master and slave servers are consistent.

GTIDs are only available from MySQL 5.6.5 and later. See the MySQL documentation for more details.
Prerequisites
  • You must have a MySQL server.

  • You must know basic SQL commands.

  • You must have access to the MySQL configuration file.

Procedure
  1. Enable gtid_mode:

mysql> gtid_mode=ON
  1. Enable enforce_gtid_consistency:

mysql> enforce_gtid_consistency=ON
  1. Confirm the changes:

mysql> show global variables like '%GTID%';
response
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
| gtid_mode                | ON    |
+--------------------------+-------+

Options explained

Permission/item Description

gtid_mode

Boolean which specifies whether GTID mode of the MySQL server is enabled or not.

  • ON = enabled

  • OFF = disabled

enforce_gtid_consistency

Boolean which instructs the server whether or not to enforce GTID consistency by allowing the execution of statements that can be logged in a transactionally safe manner; required when using GTIDs.

  • ON = enabled

  • OFF = disabled

Setting up session timeouts for Debezium

When an initial consistent snapshot is made for large databases, your established connection could timeout while the tables are being read. You can prevent this behavior by configuring interactive_timeout and wait_timeout in your MySQL configuration file.

Prerequisites
  • You must have a MySQL server.

  • You must know basic SQL commands.

  • You must have access to the MySQL configuration file.

Procedure
  1. Configure interactive_timeout:

mysql> interactive_timeout=<duration-in-seconds>
  1. Configure wait_timeout:

mysql> wait_timeout= <duration-in-seconds>

Options explained

Permission/item Description

interactive_timeout

The number of seconds the server waits for activity on an interactive connection before closing it.

See MySQL’s documentation for more details.

wait_timeout

The number of seconds the server waits for activity on a noninteractive connection before closing it.

See MySQL’s documentation for more details.

Enabling query log events for Debezium

You might want to see the original SQL statement for each binlog event. Enabling the binlog_rows_query_log_events option in the MySQL configuration file allows you to do this.

This option is only available from MySQL 5.6 and later.
Prerequisites
  • You must have a MySQL server.

  • You must know basic SQL commands.

  • You must have access to the MySQL configuration file.

Procedure
  1. Enable binlog_rows_query_log_events:

mysql> binlog_rows_query_log_events=ON

Options explained

Permission/item Description

binlog_rows_query_log_events`

Boolean which enables/disables support for including the original SQL statement in the binlog entry.

  • ON = enabled

  • OFF = disabled