Migrate MySQL data to Redis

Background information

One of the classic use cases for Redis is its implementation as a caching layer between applications and databases. It helps extend the service capabilities of traditional relational databases and optimizes the overall business ecosystem. Redis is used to store hot data. Applications can directly retrieve hot data from Redis. In addition, Redis can keep sessions alive for active users who use interactive applications. This reduces the load on the backend relational database and improves user experience.

You can follow the same procedure to migrate data from other relational databases to Redis. During the migration process, you must extract data from the source database, convert the data format, and then transmit the data to the heterogeneous database. This migration method is also suitable for data migration between other heterogeneous databases.

Prerequisites

• An ApsaraDB RDS for MySQL instance is created and stores the tables to be migrated.

• A Tair (Redis OSS-compatible) instance is created as the destination instance.

• An ECS instance that runs the Linux system is created.

• These instances are deployed in the same VPC and region.

• The private IP address of the ECS instance is added to the IP address whitelists of the ApsaraDB RDS for MySQL and Tair (Redis OSS-compatible) instances.

• MySQL and Redis services are running on the ECS instance to extract, convert, and transmit data.

Data before migration

The table contains six columns. After the migration is complete, the values in the id column of the MySQL table are converted to hash keys in Redis. The names of other columns are converted to hash fields, and the values of these columns are converted to the values of the hash fields. You can modify the scripts and commands for the migration based on actual scenarios.

Procedure

1. Analyze the source data structure, create the following migration script on the ECS instance, and then save the script to the mysql_to_redis.sql file.

   SELECT CONCAT(
     "*12\r\n", #The number 12 specifies the number of the following fields. This value depends on the data structure of the MySQL table.
     '$', LENGTH('HMSET'), '\r\n', #The HMSET variable specifies the command that you run to write data to Redis.
     'HMSET', '\r\n',
     '$', LENGTH(id), '\r\n', #The id variable specifies the first field after you run the HMSET command for fields. This field is converted to the hash key in Redis.
     id, '\r\n',
     '$', LENGTH('name'), '\r\n', #The name variable is passed to the hash table as a string field. Other fields such as sdate are processed in the same way.
     'name', '\r\n',
     '$', LENGTH(name), '\r\n', #The name variable specifies the company name in the MySQL table. This variable is converted to the value of the field generated by the 'name' parameter. Other fields such as sdate are processed in the same way.
     name, '\r\n',
     '$', LENGTH('sdate'), '\r\n',
     'sdate', '\r\n',
     '$', LENGTH(sdate), '\r\n',
     sdate, '\r\n',
     '$', LENGTH('email'), '\r\n',
     'email', '\r\n',
     '$', LENGTH(email), '\r\n',
     email, '\r\n',
     '$', LENGTH('domain'), '\r\n',
     'domain', '\r\n',
     '$', LENGTH(domain), '\r\n',
     domain, '\r\n',
     '$', LENGTH('city'), '\r\n',
     'city', '\r\n',
     '$', LENGTH(city), '\r\n',
     city, '\r'
   )
   FROM company AS c

2. Run the following command on the ECS instance to migrate data:

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

   Table 1. Options

   Option	Description	Example
   -h	The endpoint of the ApsaraDB RDS for MySQL database. Note This is the first -h in the command.	rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com Note Use the endpoint to connect the Linux server to the ApsaraDB RDS for MySQL database.
   -P	The service port of the ApsaraDB RDS for MySQL database.	3306
   -u	The username that is used to connect to the ApsaraDB RDS for MySQL database.	testuser
   -D	The database where the MySQL table that you want to migrate is stored.	mydatabase
   -p	The password used to connect to the ApsaraDB RDS for MySQL database. Note If no password is set, you do not need to specify this parameter. For higher security, you can enter only -p, run the command, and then enter the password as prompted.	Mysqlpwd233
   --skip-column-names	The column name is not written into the query result.	No value is required.
   --raw	The output column value is not escaped.	No value is required.
   -h	The endpoint that is used to access the Redis database. Note This is the -h option that follows redis-cli.	r-bp1xxxxxxxxxxxxx.redis.rds.aliyuncs.com Note Use the endpoint to connect the Linux server to the Redis database.
   --pipe	Use Redis pipelining to transmit data.	No value is required.
   -a	The password that is used to access the Redis database. Note If no password is set, you do not need to specify this parameter.	Redispwd233

   Figure 1. Sample code

   Note

   In the result, errors indicates the number of errors that occur during the execution process, and replies indicates the number of responses received. If the value of errors is 0 and the value of replies equals the number of data entries in the MySQL table, the migration is complete.

Data after migration

After the data is migrated, one data entry in the MySQL table corresponds to one data entry in the hash table of Redis. You can run the HGETALL command to query a data entry and view the following result:

You can adjust the migration solution based on the query method required in actual scenarios. For example, you can convert other columns in the MySQL table to the keys in the hash table and convert the id column to a field, or ignore the id column.