self-managed MySQL account and binary logging for DTS - Data Transmission Service

Prerequisites

MySQL 5.6 or later
Access to the MySQL configuration file (my.cnf on Linux, my.ini on Windows)
Root or admin privileges on the MySQL server
Ability to restart the MySQL service

Required permissions

DTS requires these permissions for incremental migration, synchronization, and change tracking:

Permission	Purpose
SELECT	Read data from objects to migrate, synchronize, or track
REPLICATION CLIENT	Read the binary log position
REPLICATION SLAVE	Read binary log events
SHOW VIEW	Read view definitions
CREATE on `.`	Create a database named `test` to advance the binary log position

Step 1: Create the database account

Connect to the self-managed MySQL database.

Create a dedicated account for DTS.

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Parameter	Description
`username`	Account name to create
`host`	Allowed login host. Use `%` for any host.
`password`	Password for the account

Grant the required permissions.

Grant permissions on the databases and tables to migrate:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

Grant the CREATE permission:

GRANT CREATE ON *.* TO 'username'@'host' WITH GRANT OPTION;

Parameter	Description
`privileges`	SELECT, INSERT, UPDATE, or ALL
`databasename`	Database name. Use `*` for all databases.
`tablename`	Table name. Use `*` for all tables.
`username`	Account to grant permissions to
`host`	Allowed login host. Use `%` for any host.
`WITH GRANT OPTION`	Optional. Lets the account grant permissions to others.

Example: Create an account dtsmigration with full permissions:

CREATE USER 'dtsmigration'@'%' IDENTIFIED BY 'Dts123456';
GRANT ALL ON *.* TO 'dtsmigration'@'%';

Verify the account permissions.
```
SHOW GRANTS FOR 'dtsmigration'@'%';
```
The output should include GRANT ALL PRIVILEGES ON *.* TO 'dtsmigration'@'%'.

Step 2: Configure binary logging

Important

Binary logging changes require a MySQL restart. Plan this during off-peak hours.

Binary logging parameters

Parameter	Required value	Description
`log_bin`	`mysql_bin`	Enables binary logging and sets the log file prefix.
`binlog_format`	`row`	Records row-level changes. STATEMENT and MIXED formats may cause data inconsistencies.
`server_id`	Integer greater than 1 (example: `2`)	Unique identifier for the server in a replication topology.
`binlog_row_image`	`full`	Logs full before-and-after images of each row. Required for MySQL versions later than 5.6.
`expire_logs_days`	`7` or more	Log retention in days. MySQL versions earlier than 8.0 only. Default: `0` (no expiry). Deprecated in MySQL 8.0.
`binlog_expire_logs_seconds`	`604800` or more	Log retention in seconds. MySQL 8.0 and later only. Default: `2592000` (30 days).
`log_slave_updates`	`ON`	Logs replicated events to the local binary log. Required for dual-primary clusters only.

Edit the configuration file

Open the MySQL configuration file.
- Linux: Edit my.cnf with vim or another editor.
```
vim /path/to/my.cnf
```
  Note
  my.cnf location varies by distribution. Common paths: /etc/my.cnf, /etc/mysql/my.cnf, /etc/mysql/mysql.conf.d/mysqld.cnf.
- Windows: Open my.ini in a text editor.

Add or modify these parameters under the [mysqld] section:

log_bin=mysql_bin
binlog_format=row
server_id=2
binlog_row_image=full

Set the binary log retention period.

MySQL 5.7 and earlier:
```
expire_logs_days=7
```
Note
expire_logs_days is deprecated in MySQL 8.0 and later.

MySQL 8.0 and later:
```
binlog_expire_logs_seconds=604800
```
(Optional) For dual-primary cluster deployments, add:
```
log_slave_updates=ON
```
Restart the MySQL service.
- Linux:
```
/etc/init.d/mysqld restart
```
- Windows: Use Services Manager or run:
```
net stop mysql
net start mysql
```

Verify binary logging

After restarting, connect to MySQL and verify the settings:

SHOW VARIABLES LIKE 'log_bin';
-- Expected: log_bin | ON

SHOW VARIABLES LIKE 'binlog_format';
-- Expected: binlog_format | ROW

SHOW VARIABLES LIKE 'binlog_row_image';
-- Expected: binlog_row_image | FULL

SHOW VARIABLES LIKE 'server_id';
-- Expected: server_id | <integer greater than 1>