Reconstructing Application System Databases with DCS

Scenario

With the development of database applications like the Internet, service demands are increasing rapidly. As the data volume and concurrent access volume are increasing exponentially, conventional relational databases can hardly support upper-layer services. Conventional databases are faced with issues such as complex structure, high maintenance costs, poor access performance, limited functions, and difficulty adapting to changes in data models or modes.

Solution

As a cache layer between the application and database, Redis can solve the above issues and improve data read speed, reduce database load, improve application performance, and ensure data reliability.

Data can be migrated from conventional relational databases such as MySQL to Redis. Since data in Redis is stored in the key-value structure, you need to convert the data structure in conventional databases. The following sections describe how to migrate a table from MySQL to DCS for Redis.

Prerequisites

• You have a MySQL database with a table as the source data.

  For example, create a table named student_info with 4 columns. After migration, the values in the id column of the table will be the hash keys in Redis, the names of the other columns will be the hash fields, and their values will be the field values.

  

• You have a DCS Redis instance as the target database. For details, see Buying a DCS Redis Instance.
  

  If your source is the Huawei Cloud MySQL database, select a DCS Redis instance in the same VPC as the database.

• You have created a Linux ECS in the same VPC as the DCS Redis instance. See Purchasing and Logging In to a Linux ECS.

Procedure

1. Log in to the ECS.
2. Install MySQL and the Redis client on the ECS to extract, transmit, and convert data. For details about Redis client installation, see redis-cli.
3. Analyze the source data structure, create the following script in the ECS, and save the script as migrate.sql.

   SELECT CONCAT(
   "*8\r\n",  #8 refers to the number of fields as follows. It depends on the data structure in the MySQL table.
   '$', LENGTH('HMSET'), '\r\n',  #HMSET is a Redis command in the data writing process.
   'HMSET', '\r\n',
   '$', LENGTH(id), '\r\n',   #id is the first field after HMSET. It will be transferred into Redis as a hash key.
   id, '\r\n',
   '$', LENGTH('name'), '\r\n',   #'name' will be transferred into the hash field as strings, and other arguments such as 'birthday' are applied in the same way.
   'name', '\r\n',
   '$', LENGTH(name), '\r\n',  #name is a variable representing the company name in the MySQL table. It will be transferred to be the value corresponding to the field of the last argument 'name'. Other variables such as birthday are applied in the same way.
   name, '\r\n',
   '$', LENGTH(' birthday'), '\r\n',
   ' birthday', '\r\n',
   '$', LENGTH(birthday), '\r\n',
   birthday, '\r\n',
   '$', LENGTH('city'), '\r\n',
   'city', '\r\n',
   '$', LENGTH(city), '\r\n',
   city, '\r'
   )
   FROM student_info AS s

4. Run the following command on the ECS to migrate data:

   mysql -h <MySQL host> -P <MySQL port> -u <MySQL username> -D <MySQL database name> -p --skip-column-names --raw < migrate.sql | redis-cli -h <Redis host> -p<Redis port> --pipe  -a <Redis password>

   Table 1 Parameters

   Parameter	Description	Example
   -h	Address of the MySQL database.	xxxxxx
   -P	Port of MySQL.	3306
   -u	Username of MySQL.	root
   -D	Database whose table is to be migrated.	mysql
   -p	Password of MySQL. If MySQL does not have a password, leave this parameter blank. For security, you can enter -p only, and enter your password when prompted by the command window after running the command.	xxxxxx
   --skip-column-names	The column names will not be written in query results.	No need to be set.
   --raw	No escape in outputting column values.	No need to be set.
   -h after redis-cli	Address of Redis.	redis-xxxxxxxxxxxx.com
   -p after redis-cli	Port of Redis.	6379
   --pipe	Use Redis pipelining to transmit data.	No need to be set.
   -a	Password of Redis. It does not need to be set if your Redis does not have a password.	xxxxxx

   

   In this screenshot, the Redis instance does not have a password. In the result, errors refers to the number of errors during running, and replies refers to the number of replies received. If errors is 0, and replies is equal to the the number of records in the MySQL table, the table is migrated successfully.

5. One piece of MySQL data corresponds to one hash in Redis. Run the HGETALL command for query and verification. Result:

   [root@ecs-cmtest mysql-8.0]# redis-cli -h redis-xxxxxxxxxxxx.com -p 6379
   redis-xxxxxxxxxxxx.com:6379> HGETALL 1
   1) "name"
   2) "Wilin"
   3) " birthday"
   4) "1995-06-12"
   5) "city"
   6) "Nanjing"
   redis-xxxxxxxxxxxx.com:6379> HGETALL 4
   1) "name"
   2) "Anbei"
   3) " birthday"
   4) "1969-10-19"
   5) "city"
   6) "Dongjing"

   

   You can adjust the migration plan based on actual query needs. For example, you can convert other columns in MySQL to the hash keys, and convert the id column to the field.