Reconstructing Application System Databases with DCS

Overview

Application 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 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 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.

  

• The server of the MySQL database can communicate with the DCS instance.
  • When the MySQL database and Redis instance are in the same VPC:

    By default, networks in a VPC can communicate with each other.

  • When the MySQL database and Redis instance are in different VPCs in the same region:

    If the VPC of the MySQL database and DCS Redis instance are not in the same VPC, they can be connected using a VPC peering connection. For details, see Does DCS Support Cross-VPC Access?.

  • When the VPCs of the MySQL database and the DCS Redis instance are not in the same region:

    If the MySQL database and the Redis instance are not in the same region, connect the network using Direct Connect. For details, see What Is Direct Connect.

  • For public access

    For details about how to access a DCS Redis 4.0/5.0/6.0 instance on a MySQL database server over a public network, see Using Nginx for Public Access to DCS or Using ELB for Public Access to DCS.

• JDK1.8 (or later) and IntelliJ IDEA have been installed on the MySQL database server. Download the Jedis client.

  The development tools and clients mentioned in this document are for example only.

Procedure

1. Log in to the MySQL database server.
2. Install the Redis client on the server 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 on the server, 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 server 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.