Import

Key Concepts

image.png

One-click import

MySQL Master/Slave/Replica commands only support the synchronization and consumption of incremental logs. If you need to build a slave instance with the same structure and data as the master, you need to perform structure migration and full migration of existing data. To facilitate the creation of a data-consistent mirror instance, PolarDB-X Replica adds a MODE parameter on the basis of the MySQL CHANGE MASTER TO command. When the MODE parameter is set to IMAGE, it means establishing a "one-click import" link using the mirror mode, which achieves the "one-click import" of existing structures and data.

Structure migration

Obtain the database and table structure contained in the source instance, and create them on the target instance. If the database/table already exists, the existing database/table is skipped.

Full migration of existing data

Migration of existing data on the source instance.

Incremental synchronization

Incremental synchronization based on binlog.

Usage Limitations

  • Currently, the source end only supports standard MySQL instances.
  • Currently, structure migration only supports creating default mode databases, that is, DRDS mode databases, and only supports creating target tables completely consistent with the source tables. If there is a need for customized database schemas and table structures, you can create the database and tables yourself and then use the one-click import feature.
  • By default, one-click import filters out the following MySQL system tables: SYS, MYSQL, INFORMATION_SCHEMA. To prevent any impact on the related system tables, it is recommended to set REPLICATE_DO_DB to the required database before starting the link.
  • One-click import does not currently support tables without primary keys. If there are tables without primary keys in the tables included in the Replica, the tables without primary keys will be ignored directly.
  • During the full migration stage, executing DDL in the tables included in the Replica may cause the link to be interrupted.

SQL commands

You can execute the following commands to use the one-click import feature

CHANGE MASTER TO

Add a MODE parameter to the MySQL CHANGE MASTER TO command. If the value of this parameter is IMAGE, it means to establish a one-click import link; otherwise, it means to establish a normal Replica link.

SHOW SLAVE STATUS

Add Replicate_Mode and Running_Stage return parameters to the MySQL SHOW SLAVE STATUS command. Replicate_Mode is used to distinguish whether the link is in one-click import mode or normal Replica mode; Running_Stage is used to display the current running steps of the link.

Other commands

Consistent with MySQL Replica commands, see details in MySQL Corresponding Syntax.

Usage Examples

CASE 1:

  1. Create database tables and insert data on the source MySQL: ```sql CREATE DATABASE rpl_test;

USE rpl_test;

CREATE TABLE accounts_test ( balance int(11) NOT NULL, name varchar(32) NOT NULL, gmt_created datetime NOT NULL, id int(11) NOT NULL, remark varchar(32) NOT NULL, PRIMARY KEY (id), KEY auto_shard_key_name USING BTREE (name) ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

INSERT INTO accounts_test values (1,'1','2022-12-05 11:00:01',1,'12'); INSERT INTO accounts_test values (12,'12','2022-12-05 11:00:01',12,'12'); INSERT INTO accounts_test values (123,'123','2022-12-05 11:00:01',123,'12'); INSERT INTO accounts_test values (1234,'1234','2022-12-05 11:00:01',1234,'12'); INSERT INTO accounts_test values (12345,'12345','2022-12-05 11:00:01',12345,'12'); INSERT INTO accounts_test values (123456,'123456','2022-12-05 11:00:01',123456,'12'); INSERT INTO accounts_test values (1234567,'1234567','2022-12-05 11:00:01',1234567,'12');


2. Obtain the latest binlog position on the source MySQL after executing the above statements:
```sql
SHOW MASTER STATUS;

image.png

  1. Create a Replica one-click import link on the target PolarDB-X:

    CHANGE MASTER TO
 MASTER_HOST='import-opensource-test-7qkj-dn-0',
 MASTER_USER='admin',
 MASTER_PASSWORD='l26rfwsr',
 MASTER_PORT=3306,
 MASTER_LOG_FILE='mysql_bin.000001',
 MASTER_LOG_POS='58050130',
 SOURCE_HOST_TYPE=mysql,Mode='IMAGE';

  2. Set the database tables that need to be synchronized:

    CHANGE REPLICATION FILTER REPLICATE_DO_DB=(rpl_test);

  3. Start the one-click import link:

    START SLAVE;

  4. Check the synchronization status on the target side: ```sql SHOW DATABASES;

SELECT * FROM rpl_test.accounts_test;

![image.png](../images/import_result2.png)

7. Check the link status at this time:
```sql
SHOW SLAVE STATUS\G

benchmark_tpcc_load

Note: IMAGE indicates the one-click import mode, and REPLICA_INC indicates that the structure and full migration have been completed, entering the incremental migration step. Insert data on the source side at this time, and check the synchronization status on the target side: the effect is the same as a normal Replica link, so it will not be repeated.

CASE 2:

  1. Refer to TPCC Testing to import 10 warehouses of TPCC data on the source side, and start the TPCC test
    1. Note: Due to the usage limitations of one-click import, an auto-increment primary key needs to be added to the bmsql_history table
  2. Establish a one-click import link following the above process ```sql // Source side SHOW MASTER STATUS;

// Target side CHANGE MASTER TO MASTER_HOST='jiyue-test111-tf24-dn-0', MASTER_USER='admin', MASTER_PASSWORD='sp9tn2lq', MASTER_PORT=3306, MASTER_LOG_FILE='mysql_bin.000004', MASTER_LOG_POS='246743757', SOURCE_HOST_TYPE=mysql,Mode='IMAGE';

// Source side CHANGE REPLICATION FILTER REPLICATE_DO_DB=(tpcc);

// Target side START SLAVE;


3. Observe the results

After the TPCC test ends in 2 minutes, observe the data on both sides:
Source side:
```sql
mysql> select count(1) from bmsql_config;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from bmsql_customer;
+----------+
| count(1) |
+----------+
|   300000 |
+----------+
1 row in set (0.03 sec)

mysql> select count(1) from bmsql_district;
+----------+
| count(1) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(1) from bmsql_history;
+----------+
| count(1) |
+----------+
|   484855 |
+----------+
1 row in set (0.03 sec)

mysql> select count(1) from bmsql_item;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from bmsql_new_order;
+----------+
| count(1) |
+----------+
|   118559 |
+----------+
1 row in set (0.01 sec)

mysql> select count(1) from bmsql_oorder;
+----------+
| count(1) |
+----------+
|   491928 |
+----------+
1 row in set (0.02 sec)

mysql> select count(1) from bmsql_order_line;
+----------+
| count(1) |
+----------+
|  4921254 |
+----------+
1 row in set (0.15 sec)

mysql> select count(1) from bmsql_stock;
+----------+
| count(1) |
+----------+
|  1000000 |
+----------+
1 row in set (0.06 sec)

mysql> select count(1) from bmsql_warehouse;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

Target side:

mysql> select count(1) from bmsql_config;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.08 sec)

mysql> select count(1) from bmsql_customer;
+----------+
| count(1) |
+----------+
|   300000 |
+----------+
1 row in set (0.74 sec)

mysql> select count(1) from bmsql_district;
+----------+
| count(1) |
+----------+
|      100 |
+----------+
1 row in set (0.08 sec)

mysql> select count(1) from bmsql_history;
+----------+
| count(1) |
+----------+
|   484855 |
+----------+
1 row in set (0.18 sec)

mysql> select count(1) from bmsql_item;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.08 sec)

mysql> select count(1) from bmsql_new_order;
+----------+
| count(1) |
+----------+
|   118559 |
+----------+
1 row in set (0.09 sec)

mysql> select count(1) from bmsql_oorder;
+----------+
| count(1) |
+----------+
|   491928 |
+----------+
1 row in set (1.08 sec)

mysql> select count(1) from bmsql_order_line;
+----------+
| count(1) |
+----------+
|  4921254 |
+----------+
1 row in set (0.94 sec)

mysql> select count(1) from bmsql_stock;
+----------+
| count(1) |
+----------+
|  1000000 |
+----------+
1 row in set (0.35 sec)

mysql> select count(1) from bmsql_warehouse;
+----------+
| count(1) |
+----------+
|       10 |
+----------+
1 row in set (0.07 sec)

The data on both sides is consistent, and a total of about 7.5 million data have been migrated, which took about 2 minutes to complete.

results matching ""

    No results matching ""