Architecture

Database structure is one master multiple slaves with binlog_format MIXED. To replicate the MySQL to TiDB without changing binlog_format, one new MySQL slave(binlog_format) is added to replicate from MySQL Slave(binlog_format=MIXED). The MySQL Slave(binlog_format=ROW) is taken as the source to replicate to TiDB Cloud.

Test Data

172GB test data is generated by tpcc into MySQL Master Data.

admin@workstation(172.82.31.201):~/home$ tiup bench tpcc -H 172.83.3.145 -P 3306 -D tpcc02 --warehouses 2 -U dataload -p 1234Abcd --threads 10 prepare
tiup is checking updates for component bench ...                                          
Starting component `bench`: /home/admin/.tiup/components/bench/v1.12.0/tiup-bench tpcc -H 172.83.3.145 -P 3306 -D tpcc02 --warehouses 2 -U dataload -p 1234Abcd --threads 10 prepare
creating table warehouse                                                                  
creating table district                                                                   
creating table customer                                                                   
creating table history                                                                    
creating table new_order                                                                  
creating table orders                                                                     
creating table order_line                                                                 
creating table stock                                                                      
creating table item                                                                       
load to item
... ...
Finished

Server list

IP	Instance	Spec
172.82.31.201	Workstation	c5.2xlarge
172.83.3.145	MySQL Master Instance	t2.2xlarge
172.83.2.117	MySQL Slave(binlog_format=MIXED)	t2.2xlarge
172.83.4.169	MySQL Slave(binlog_format=ROW)	t2.2xlarge
pivate-tidb.fxinygs92q0.clusters.tidb-cloud.com	TiDB Cloud	TiDB: 8c32GB 2 nodes, TiKV: 8c32GB 3 nodes

Process

The whole process takes 5 hours to complete the migration if we use the above server spec.

MySQL Slave(binlog_format=ROW) setup (28 minutes)
- File copy to MySQL Slave(binlog_format=ROW) (28 minutes)
Data migration from MySQL Slave(binlog_format=ROW) to TiDB Cloud
- Data Export from MySQL Salve(binlog_format) (40 minutes)
- Data Import to TiDB Cloud(4 hours)

Pre-preparation

sync-diff-inspector

admin@workstation(172.82.21.201):~$ wget https://download.pingcap.org/tidb-community-toolkit-v6.5.3-linux-amd64.tar.gz -P /tmp
admin@workstation(172.82.21.201):/tmp$ tar xvf /tmp/tidb-community-toolkit-v6.5.3-linux-amd64.tar.gz
admin@workstation(172.82.21.201):/tmp$ sudo mv /tmp/tidb-community-toolkit-v6.5.3-linux-amd64/sync_diff_inspector /usr/local/bin

Database structure verification

Master DB

Database size

mysql> use information_schema;
mysql> select concat(round(sum(data_length/1024/1024/1024),2),'GB') as data from tables;
+----------+
| data     |
+----------+
| 172.69GB |
+----------+
1 row in set (0.07 sec)

binlog format

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

master status

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000139 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave DB

binlog_format

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

replica status

mysql> show replica status \G  
 *************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 172.83.3.145
                  Source_User: slave
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: binlog.000136
          Read_Source_Log_Pos: 1044
               Relay_Log_File: ip-172-83-2-117-relay-bin.000003
                Relay_Log_Pos: 508
        Relay_Source_Log_File: binlog.000136
           ... ...
                  Source_UUID: c1b1c341-14ca-11ee-9600-0aa77c5edf2d
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
           ... ...

Setup slave node to slave node

Node and DB setup

physical files copy(Recommendation)

Get binlog position and Graceful stop one MySQL slave node

mysql> show master status
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000139 |      157 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Data copy

mysql@slave(172.83.2.117):/var/lib/mysql> time rsync -r -a data 172.83.4.169:/var/lib/mysql/
real    28m0.061s
user    8m25.828s
sys     9m46.862s

Set binlog_format=ROW in the my.conf

[mysqld]
...
server_id       = 10002  # Need to set one unique server id
binlog_format   = ROW    # For DM replication
...

Start MySQL service on slave-to-salve(172.83.4.169)

admin@slave-to-slave(172.83.4.169):/var/lib/mysql> sudo systemctl start mysql

Logical data copy(Not recommended)

MySQL instancer setup

Deploy one empty MySQL instance in the new node and set the GTID_MODE and binlog_format as below:

Get GTID_MODE

mysql> show variables like 'gtid_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode     | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

Get binlog_format

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Data Copy

mysqldump from slave MySQL instance

slave-node$ time sudo mysqldump -u root --all-databases > dump.sql
real    58m46.003s
user    31m21.173s
sys     3m44.236s

Data copy the slave-to-slave node from MySQL instance

slave-to-slave$ time rsync 172.83.2.117:/var/lib/mysql/backdata/dump.sql ./ 
real    20m59.524s
user    8m16.271s
sys     9m37.505s

Import data to slave-to-slave instance

slave-to-slave$ time sudo mysql -u root < dump.sql
real    394m40.050s
user    22m49.640s
sys     2m17.276s

Replication setup

Create replication user on the slave node

mysql> CREATE USER slave@`%` IDENTIFIED BY '1234Abcd';
Query OK, 0 rows affected (0.05 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@`%`;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

Get binlog position from slave node

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000138 |     1051 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

Start slave replication

mysql> CHANGE MASTER TO                                                                                                                                                              
    -> MASTER_HOST='172.83.2.117',
    -> MASTER_USER='slave',       
    -> MASTER_PASSWORD='1234Abcd',           
    -> MASTER_LOG_FILE='binlog.000138',
    -> MASTER_LOG_POS=1051;                                                               
Query OK, 0 rows affected, 8 warnings (0.06 sec)               
                                           
mysql> start slave ;                
Query OK, 0 rows affected, 1 warning (0.03 sec)
                                           
mysql> show slave status \G    
 *************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 172.83.2.117
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000138
          Read_Master_Log_Pos: 1051
               Relay_Log_File: ip-172-83-4-169-relay-bin.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000138
        ... ...
             Master_Server_Id: 10001
                  Master_UUID: 8b750673-159f-11ee-ae00-129f095a0c83
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
        ... ...

Set server id for slave-slave node

slave-to-slave$ more /etc/mysql/conf.d/my.cnf
... ...
[mysqld]
server_id=10002

Data comparison between master and slave-to-slave(optional)

Make sure there are enough disk for data comparison in the both MySQL database which use tmpdir. This step is not mandatory for production. Recommend to diff in the test environment to make sure the process is correct.

make sure the dir of tmpdir has enough disk

mysql> show variables like 'tmpdir';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| tmpdir        | /tmp  |
+---------------+-------+
1 row in set (0.01 sec)

Analyze all the tables

Before data comparason, the analyze must be done in the slave-to-slave MySQL instance which impact the performance a lot.

Create check user in the master node(optional)

mysql> create user `datachkusr`@`%` identified by '1234Abcd'; 
Query OK, 0 rows affected (0.01 sec)

mysql> grant select on *.* to `datachkusr`@`%`;
Query OK, 0 rows affected (0.01 sec)

mysql> grant SHOW DATABASES on *.* to `datachkusr`@`%`;
Query OK, 0 rows affected (0.01 sec)

mysql> grant RELOAD on *.* to `datachkusr`@`%`;
Query OK, 0 rows affected (0.01 sec)

Create check user in the slave-to-slave node

No need to create the user manually since it is replicated to salve-to-salve from master db.

config file for sync-diff-inspector

workstation$ more config.toml
check-thread-count = 64
export-fix-sql = true
check-struct-only = false
skip-non-existing-table = false

######################### Datasource config #########################
[data-sources]
[data-sources.masterdb]
    host = "172.83.3.145"
    port = 3306
    user = "datachkusr"
    password = "1234Abcd"

[data-sources.slave2slave]
    host = "172.83.4.169"
    port = 3306
    user = "datachkusr"
    password = "1234Abcd"

[task]
    output-dir = "./output"
    source-instances = ["masterdb"]
    target-instance = "slave2slave"
    target-check-tables = ["*.*"]

Data comparison execution

workstation$ sync_diff_inspector --config=config.toml 
A total of 10 tables need to be compared

Comparing the table structure of ``test`.`test03`` ... equivalent
Comparing the table data of ``test`.`test03`` ... equivalent
Comparing the table structure of ``tpcc`.`district`` ... equivalent
Comparing the table data of ``tpcc`.`district`` ... equivalent
Comparing the table structure of ``tpcc`.`item`` ... equivalent
Comparing the table data of ``tpcc`.`item`` ... equivalent
Comparing the table structure of ``tpcc`.`new_order`` ... equivalent
Comparing the table data of ``tpcc`.`new_order`` ... equivalent
Comparing the table structure of ``tpcc`.`orders`` ... equivalent
Comparing the table data of ``tpcc`.`orders`` ... equivalent
Comparing the table structure of ``tpcc`.`history`` ... equivalent
Comparing the table data of ``tpcc`.`history`` ... equivalent
Comparing the table structure of ``tpcc`.`customer`` ... equivalent
Comparing the table data of ``tpcc`.`customer`` ... equivalent
Comparing the table structure of ``tpcc`.`stock`` ... equivalent
Comparing the table data of ``tpcc`.`stock`` ... equivalent
Comparing the table structure of ``tpcc`.`order_line`` ... equivalent
Comparing the table data of ``tpcc`.`order_line`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 10 table have been compared and all are equal.
You can view the comparision details through './output/sync_diff.log'

TiDB Cloud replication(DM on cloud setup)

TiDB Cloud setup

Please go the TiDB Cloud to start one cluster.

VPC Peering

Please refer to VPC Peering setup to setup the vpc peering between TiDB Cloud and VPC of MySQL cluster. After the vpc peering setup, test the connection between TiDB Cloud and MySQL cluster vpc.

admin@ip-172-83-4-169:~$ mysql --connect-timeout 15 -u root -h private-tidb.fxinygs92q0.clusters.tidb-cloud.com -P 4000 -D test -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 943
Server version: 5.7.25-TiDB-v6.5.3 TiDB Server (Apache License 2.0) Enterprise Edition, MySQL 5.7 compatible

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

User preparation on the slave-to-slave node

mysql> create user `tidbdmusr`@`10.250.8.0/21` identified by '1234Abcd';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT,LOCK TABLES,RELOAD,REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO `tidbdmusr`@`10.250.8.0/21`; 
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.01 sec)

Check collation before migration

mysql> show collation;

DM replication setup

Create DM job
make sure the vpc peering has been setup
Choose full data and incremental replication
Check all warns Recommend to conntact us if there is any warnings.
Ignore warnings if it is acceptable
No performance requirment, 2RU is recommended
Check the status until the migration is completed

Data comparison

Table analyze

Data comparison

workstation$ more mysql-vs-tidb.toml
check-thread-count = 64
export-fix-sql = true
check-struct-only = false
skip-non-existing-table = false

######################### Datasource config #########################
[data-sources]
[data-sources.masterdb]
    host = "172.83.3.145"
    port = 3306
    user = "datachkusr"
    password = "1234Abcd"

[data-sources.tidb]
    host = "private-tidb.fxinygs92q0.clusters.tidb-cloud.com"
    port = 4000
    user = "root"
    password = "1234Abcd"

[task]
    output-dir = "./output"
    source-instances = ["masterdb"]
    target-instance = "tidb"
    target-check-tables = ["tpcc.*"]

RUn diff

workstation$ sync_diff_inspector --config=mysql-vs-tidb.toml
A total of 9 tables need to be compared

Comparing the table structure of ``tpcc`.`new_order`` ... equivalent
Comparing the table structure of ``tpcc`.`district`` ... equivalent
Comparing the table structure of ``tpcc`.`customer`` ... equivalent
Comparing the table structure of ``tpcc`.`order_line`` ... equivalent
Comparing the table structure of ``tpcc`.`history`` ... equivalent
Comparing the table structure of ``tpcc`.`warehouse`` ... equivalent
Comparing the table structure of ``tpcc`.`stock`` ... equivalent
Comparing the table structure of ``tpcc`.`item`` ... equivalent
Comparing the table structure of ``tpcc`.`orders`` ... equivalent
Comparing the table data of ``tpcc`.`district`` ... equivalent
Comparing the table data of ``tpcc`.`warehouse`` ... equivalent
Comparing the table data of ``tpcc`.`item`` ... equivalent
Comparing the table data of ``tpcc`.`new_order`` ... equivalent
Comparing the table data of ``tpcc`.`customer`` ... equivalent
Comparing the table data of ``tpcc`.`history`` ... equivalent
Comparing the table data of ``tpcc`.`orders`` ... equivalent
Comparing the table data of ``tpcc`.`stock`` ... equivalent
Comparing the table data of ``tpcc`.`order_line`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 9 table have been compared and all are equal.
You can view the comparision details through './output/sync_diff.log'

Data generation

Master db

mysql> insert into test03 values(5,5);
Query OK, 1 row affected (0.08 sec)

mysql> select * from test03;
+-------+-------+
| col01 | col02 |
+-------+-------+
|     1 |     1 |
|     2 |     2 |
|     3 |     3 |
|     4 |     4 |
|     5 |     5 |
+-------+-------+
5 rows in set (0.00 sec)

TiDB

mysql> select * from test03;
+-------+-------+
| col01 | col02 |
+-------+-------+
|     1 |     1 |
|     2 |     2 |
|     3 |     3 |
|     4 |     4 |
|     5 |     5 |
+-------+-------+
5 rows in set (0.00 sec)

Database creation

master db

mysql> create database tpcc02 collate utf8mb4_bin;
Query OK, 1 row affected (0.02 sec)

tidb

mysql> show databases; 
+--------------------+
| Database           |
+--------------------+
| INFORMATION_SCHEMA |
| PERFORMANCE_SCHEMA |
.. ...
| tpcc               |
| tpcc02             |
+--------------------+
7 rows in set (0.00 sec)

Test data import

workstation$ tiup bench tpcc -H 172.83.3.145 -P 3306 -D tpcc02 --warehouses 2 -U dataload -p 1234Abcd --threads 10 prepare
tiup is checking updates for component bench ...                                          
Starting component `bench`: /home/admin/.tiup/components/bench/v1.12.0/tiup-bench tpcc -H 172.83.3.145 -P 3306 -D tpcc02 --warehouses 2 -U dataload -p 1234Abcd --threads 10 prepare
creating table warehouse                                                                  
creating table district                                                                   
creating table customer                                                                   
creating table history                                                                    
creating table new_order                                                                  
creating table orders                                                                     
creating table order_line                                                                 
creating table stock                                                                      
creating table item                                                                       
load to item
... ...
Finished

Check replicated data

workstation$ more mysql-vs-tidb-02.toml
check-thread-count = 64
export-fix-sql = true
check-struct-only = false
skip-non-existing-table = false

######################### Datasource config #########################
[data-sources]
[data-sources.masterdb]
    host = "172.83.3.145"
    port = 3306
    user = "datachkusr"
    password = "1234Abcd"

[data-sources.tidb]
    host = "private-tidb.fxinygs92q0.clusters.tidb-cloud.com"
    port = 4000
    user = "root"
    password = "1234Abcd"

[task]
    output-dir = "./output"
    source-instances = ["masterdb"]
    target-instance = "tidb"
    target-check-tables = ["tpcc02.*"]

Data comparison

workstation$ sync_diff_inspector --config=mysql-vs-tidb-02.toml
A total of 9 tables need to be compared

Comparing the table structure of ``tpcc02`.`customer`` ... equivalent
Comparing the table structure of ``tpcc02`.`orders`` ... equivalent
Comparing the table structure of ``tpcc02`.`order_line`` ... equivalent
Comparing the table structure of ``tpcc02`.`stock`` ... equivalent
Comparing the table structure of ``tpcc02`.`item`` ... equivalent
Comparing the table structure of ``tpcc02`.`warehouse`` ... equivalent
Comparing the table structure of ``tpcc02`.`new_order`` ... equivalent
Comparing the table structure of ``tpcc02`.`district`` ... equivalent
Comparing the table data of ``tpcc02`.`warehouse`` ... equivalent
Comparing the table data of ``tpcc02`.`district`` ... equivalent
Comparing the table data of ``tpcc02`.`new_order`` ... equivalent
Comparing the table data of ``tpcc02`.`orders`` ... equivalent
Comparing the table data of ``tpcc02`.`item`` ... equivalent
Comparing the table data of ``tpcc02`.`customer`` ... equivalent
Comparing the table structure of ``tpcc02`.`history`` ... equivalent
Comparing the table data of ``tpcc02`.`history`` ... equivalent
Comparing the table data of ``tpcc02`.`stock`` ... equivalent
Comparing the table data of ``tpcc02`.`order_line`` ... equivalent
_____________________________________________________________________________
Progress [============================================================>] 100% 0/0
A total of 9 table have been compared and all are equal.
You can view the comparision details through './output/sync_diff.log'

Files

mysql2tidb.org

Latest commit

History

mysql2tidb.org

File metadata and controls

Architecture

Test Data

Server list

Process

Pre-preparation

sync-diff-inspector

Database structure verification

Master DB

Database size

binlog format

master status

Slave DB

binlog_format

replica status

Setup slave node to slave node

Node and DB setup

physical files copy(Recommendation)

Get binlog position and Graceful stop one MySQL slave node

Data copy

Set binlog_format=ROW in the my.conf

Start MySQL service on slave-to-salve(172.83.4.169)

Logical data copy(Not recommended)

MySQL instancer setup

Get GTID_MODE

Get binlog_format

Data Copy

mysqldump from slave MySQL instance

Data copy the slave-to-slave node from MySQL instance

Import data to slave-to-slave instance

Replication setup

Create replication user on the slave node

Get binlog position from slave node

Start slave replication

Set server id for slave-slave node

Data comparison between master and slave-to-slave(optional)

make sure the dir of tmpdir has enough disk

Analyze all the tables

Create check user in the master node(optional)

Create check user in the slave-to-slave node

config file for sync-diff-inspector

Data comparison execution

TiDB Cloud replication(DM on cloud setup)

TiDB Cloud setup

VPC Peering

User preparation on the slave-to-slave node

Check collation before migration

DM replication setup

Data comparison

Table analyze

Data comparison

RUn diff

Data generation

Database creation

Test data import

Check replicated data

Data comparison