Configure Migration Prerequisites for MySQL

On this page

Steps

Learn More

To run migration jobs from a MySQL source database, the database may require some configuration changes. If Relational Migrator determines the database needs configuration changes, it automatically generates a SQL script with the required changes. It is recommended to have a Database Administrator (DBA) review the commands in this script and perform their execution on the database server. The MySQL Server configurations depend on the type of migration job:

Snapshot migration jobs migrate all the data and then stops.
Continuous migration job run a snapshot and then enter a CDC stage to continuously replicate data changes.

For details on supported versions of MySQL, see Supported Databases and Versions.

Steps

(Optional) Set up user permissions

The following code creates a new MySQL service account for Relational Migrator to connect to the MySQL instance. Alternatively, you can use an existing MySQL service account to connect to Relational Migrator with the appropriate permissions.

Create a service account:
```
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
```
Grant the required permissions to the service account:
```
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO 'user'@'%';
```
Apply the user privilege changes:
```
FLUSH PRIVILEGES;
```

Running continuous jobs on Relational Migrator requires the binary log to be enabled on your MySQL instance. The binary log (Binlog) records all operations in the order they are committed to the database.

(Optional) Set up user permissions

Create a service account:
```
CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';
```
Grant the required permissions to the service account:
```
GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT
ON *.*
TO 'user'@'%';
```
Apply the user privilege changes:
```
FLUSH PRIVILEGES;
```

(Optional) Manually verify Binlog is enabled

Relational Migrator automatically checks this setting for you. To manually check the if the Binlog option is enabled, use the queries below for your version of MySQL:

Note

Binlog is automatically enabled by default on MySQL 8.x versions.

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

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

Locate and update the MySQL configuration file

Run the following SQL query to get the server_id value for your MySQL instance:
SELECT variable_value FROM performance_schema.global_variables WHERE variable_name='server_id';
SELECT variable_value FROM information_schema.global_variables WHERE variable_name='server_id';
Locate the config file for your MySQL instance by running the following mysqld command in your terminal:
mysql --help | findstr cnf
mysql --help | grep cnf
Under the [mysqld] section of your MySQL configuration file add the following lines. Replace the XXXXX value with the server_id from the previous query:
server-id = XXXXX log_bin = mysql-bin binlog_format = ROW binlog_row_image = FULL binlog_expire_logs_seconds = 864000
server-id = XXXXX log_bin = mysql-bin binlog_format = ROW binlog_row_image = FULL expire_log_days = 10
Note
If you're running MySQL on AWS RDS and automated backups are not enabled, Binlog will be disabled, even if the values are set in the configuration file.

Learn More

Relational Migrator relies on the open-source Debezium connector to capture row-level changes. For more details, see Debezium MySQL.

Back

MongoDB

Oracle