Set up a self-hosted MySQL database

How to set up a self-hosted MySQL database as a source for RisingWave.

To use the MySQL CDC features, we need to create a MySQL user account with appropriate privileges on all databases for which RisingWave will read from.

Create a user and grant privileges

1. Create a MySQL user with the following query.

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

2. Grant the appropriate privileges to the user.

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'user'@'%';

For MariaDB 10.5 and later, BINLOG MONITOR is the renamed equivalent of REPLICATION CLIENT. You can grant it instead:

GRANT SELECT, REPLICATION SLAVE, BINLOG MONITOR ON *.* TO 'user'@'%';

RisingWave accepts either privilege when validating a MariaDB CDC source.

3. Finalize the privileges.

FLUSH PRIVILEGES;

Enable the binlog

The binlog must be enabled for MySQL replication. The binary logs record transaction updates for replication tools to propagate changes.

1. Check if the log-bin is already on.

SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+

2. If it is OFF, configure your MySQL server configuration file, my.cnf, with the following properties described below. Restart your MySQL server to let the configurations take effect.

server-id         = 223344
log_bin           = mysql-bin
binlog_format     = ROW
binlog_row_image  = FULL
expire_logs_days  = 10

3. Confirm your changes by checking the log-bin again.

SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

See Setting up MySQL for more details.