Managing Mapping Rules

You can create mapping rules to automatically synchronize data from MySQL instances to GeminiDB instances. This section describes how to create, modify, and delete a mapping rule, and provides an example of creating a mapping rule.

Precautions

Currently, only hashes from MySQL can be converted to GeminiDB Redis API.
The Redis key prefix + key separator of a new rule cannot be the subprefix of the Redis key prefix + key separator of an existing rule, and vice versa. For example, if the prefix of a new rule is pre1: and the key separator is a comma (,), and the prefix of the existing rule is pre1 and the separator is a colon (:), the new rule cannot be created.
If the table name of a MySQL instance in the mapping rule is changed, you need to reconfigure the mapping rule.
Currently, the ENUM, SET, and JSON data cannot be synchronized.
Renaming or deleting one or more fields in the key field of a mapping rule renders the rule invalid.

Creating a mapping rule

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the target instance.
In the navigation pane on the left, choose Memory Acceleration. In the Mapping Rule area, click Create Mapping Rule.

Figure 1 Mapping rules
On the displayed page, configure required parameters.
1. Set the rule name.
  Rule Name: Set the name of the mapping rule. The rule name must be unique within a GeminiDB instance and cannot exceed 256 characters or include number signs (#).
  Figure 2 Rule name
2. Configure source instance information.
  - Database Name: Select the database of the instance to be accelerated.
  - Table Name: Select the table in the acceleration instance.
  Figure 3 Configuring source instance information
3. Configure acceleration instance information.
  - Redis Key Prefix: This parameter is optional. The default format is database name:data table name:field name 1:field name 2.... It can contain up to 1024 characters. If you have created a custom prefix, it will take precedence over the default one.
  - Value Storage Type: Data type of the cache. Currently, only hashes are supported.
  - Database No. (0-999): ID of the database that stores cached data in the acceleration instance. The default value is 0.
  - TTL (s) Default value: 30 days : Expiration time of cached data in the acceleration instance. The default value is 30 days (2,592,000 seconds). If you enter -1, the cached data will not expire.
  - Key Delimiter: Separator among the Redis key prefix, key, and key fields. It is a single character in length.
  Figure 4 Configuring acceleration instance information
4. Click Set Key, select the key field of the acceleration instance, and click OK.
  
  If an acceleration instance key consists of multiple source instance fields, the key must be unique (a unique index must be created for these fields in a MySQL instance). You can click Up or Down to adjust the sequence of each field in the key.
  
  Figure 5 Key settings
  
  After the setting is complete, the key is displayed.
  
  Figure 6 Key structure
5. Configure the domain-value of the acceleration instance.
  Select fields required in the source instance and copy them to the fields of the acceleration instance.
  Figure 7 Configuring acceleration instance fields
6. After setting the parameters, click Submit.

Example

Create database db1 in the source MySQL instance and create table students in db1. The SQL statements are as follows:

mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE db1.students(
       sid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT NOT NULL,
       sname VARCHAR(20),
       sclass INT,
       sgender VARCHAR(10),
       sbirthday DATE
       );
Query OK, 0 rows affected (0.00 sec)

mysql> DESC db1.students;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| sid       | int unsigned | NO   | PRI | NULL    | auto_increment |
| sname     | varchar(20)  | YES  |     | NULL    |                |
| sclass    | int          | YES  |     | NULL    |                |
| sgender   | varchar(10)  | YES  |     | NULL    |                |
| sbirthday | date         | YES  |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

After the table is created, on the memory acceleration page, create a mapping rule to convert each row in the students table into a Redis hash. The key of a hash consists of database name:data table name:sid:<sid value>. The fields are sname, sclass, sgender, and sbirthday.
Figure 8 Configuring mapping rules
After a mapping rule is created, check the mapping rule and mapping information.
Figure 9 Mapping information

Insert a new data record to the students table in the MySQL instance.

mysql> INSERT INTO db1.students (sname, sclass, sgender, sbirthday) VALUES ('zhangsan', 1, 'male', '2015-05-20');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM db1.students;
+-----+----------+--------+---------+------------+
| sid | sname    | sclass | sgender | sbirthday  |
+-----+----------+--------+---------+------------+
|   1 | zhangsan |      1 | male    | 2015-05-20 |
+-----+----------+--------+---------+------------+
1 row in set (0.00 sec)

After the mapping rule is created, the data is automatically synchronized to the GeminiDB instance. Run commands in the GeminiDB instance to query the data.

127.0.0.1:6379> KEYS *
1) "db1:students:sid:1"

127.0.0.1:6379> HGETALL db1:students:sid:1
1) "sbirthday"
2) "2015-05-20"
3) "sclass"
4) "1"
5) "sgender"
6) "male"
7) "sname"
8) "zhangsan"

Insert a new data record to the students table in the MySQL instance.

mysql> INSERT INTO db1.students (sname, sclass, sgender, sbirthday) VALUES ('lisi', 10, 'male', '2015-05-22');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM db1.students;
+-----+----------+--------+---------+------------+
| sid | sname    | sclass | sgender | sbirthday  |
+-----+----------+--------+---------+------------+
|   1 | zhangsan |      1 | male    | 2015-05-20 |
|   2 | lisi     |     10 | male    | 2015-05-22 |
+-----+----------+--------+---------+------------+
2 rows in set (0.00 sec)

New data will be synchronized to the GeminiDB instance.

127.0.0.1:6379> KEYS *
1) "db1:students:sid:1"
2) "db1:students:sid:2"

127.0.0.1:6379> HGETALL db1:students:sid:2
1) "sbirthday"
2) "2015-05-22"
3) "sclass"
4) "10"
5) "sgender"
6) "male"
7) "sname"

Update data in the students table in the MySQL instance.

mysql> UPDATE db1.students SET sclass=12, sname='wangwu' WHERE sid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM db1.students;
+-----+--------+--------+---------+------------+
| sid | sname  | sclass | sgender | sbirthday  |
+-----+--------+--------+---------+------------+
|   1 | wangwu |     12 | male    | 2015-05-20 |
|   2 | lisi   |     10 | male    | 2015-05-22 |
+-----+--------+--------+---------+------------+
2 rows in set (0.00 sec)

Data in the GeminiDB instance is updated.

127.0.0.1:6379> KEYS *
1) "db1:students:sid:1"
2) "db1:students:sid:2"

127.0.0.1:6379> HGETALL db1:students:sid:1
1) "sbirthday"
2) "2015-05-20"
3) "sclass"
4) "12"
5) "sgender"
6) "male"
7) "sname"
8) "wangwu"

Delete data from the students table in the MySQL instance.

mysql> DELETE FROM db1.students WHERE sid = 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM db1.students;
+-----+-------+--------+---------+------------+
| sid | sname | sclass | sgender | sbirthday  |
+-----+-------+--------+---------+------------+
|   2 | lisi  |     10 | male    | 2015-05-22 |
+-----+-------+--------+---------+------------+
1 row in set (0.00 sec)

The data is deleted from the GeminiDB instance.
```
127.0.0.1:6379> KEYS *
1) "db1:students:sid:2"
```

Modifying a Mapping Rule

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the target instance.
In the navigation pane on the left, choose Memory Acceleration. In the Mapping Rule area, locate the target rule and click Edit in the Operation column.

Figure 10 The Edit button
After editing the fields, click Submit.

Figure 11 Editing a mapping rule

Deleting a Mapping Rule

Log in to the management console.
Click in the upper left corner and select a region and project.
Click in the upper left corner of the page and choose Databases > Relational Database Service.
On the Instances page, click the target instance.
In the navigation pane on the left, choose Memory Acceleration. In the Mapping Rule area, locate the target rule and click Delete in the Operation column.