Connect to MySQL

On this page

Create Service Account

Save Connection

MOCKUP

You can use a saved connection for any task that requires connecting to a database. You can save a new relational database connection:

When creating a new project from a live database connection
When creating or modifying a data migration job
From the Database Connections bar in a project
From the Relational Migrator Connections page

Create Service Account

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 data once, and then stop.
Continuous migration jobs run a snapshot migration and then enter a CDC stage, which continuously replicates data changes.

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

(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.

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

Save Connection

To save a new connection from the Connections page:

Add a connection

In the list of Relational connections, click + Add connection.

Select the database type

In the Database type drop-down, select the database type.

To enter a connection string directly:

Above JDBC URI, enable the Enter URI manually toggle.
Paste your connection string into the JDBC URI.

To create a connection string by entering database information, enter the following:

The general form for a MySQL connection string is:

jdbc:mysql://<host:port>/<database>?<properties>

For example, consider the following connection string:

jdbc:mysql://host1:3306/test

The preceding connection string specifies these connection details:

Property	Value
Host	`host1`
Port	`3306`
Database	`test`

Note

To learn more about MySQL connection strings, see:

Identify the connection

Enter a Connection name and optional Environment tag.

Environment tags color code and organize connections for convenience. Leaving the drop-down empty is the same as selecting the "None" tag.

(Optional) Click Test connection

Relational Migrator attempts to authenticate and connect to the database.

Click Save

The saved connection is available for use in all jobs and projects.

Back

Connect to Databases

Oracle