Migrate MySQL data to ApsaraDB for Redis

Scenario

In one of the classic use cases, ApsaraDB for Redis is used as a caching service between applications and databases to expand the capabilities of traditional relational databases. This also optimizes the ecosystem. ApsaraDB for Redis is used to store hot data. Applications can directly retrieve hot data from ApsaraDB for Redis. In addition, ApsaraDB for Redis can keep sessions alive for active users that 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 ApsaraDB for 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.
• An ApsaraDB for Redis instance is created as the destination.
• 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 ApsaraDB RDS for MySQL and ApsaraDB for Redis instances.
• MySQL and Redis services are running on the ECS instance to extract, convert, and transmit data.

Data before migration

This topic describes how to migrate the test data stored in the company table of the custm_info database. The company table contains test data as shown in the following table.

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 ApsaraDB for 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, and 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 ApsaraDB for 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 ApsaraDB for 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

   Name	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 of 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 of 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 requested by the prompt.	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 URL 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 ApsaraDB for Redis database.
   --pipe	Use the pipeline feature of ApsaraDB for Redis 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 can skip this parameter.	Redispwd233

   

   Note In the result, errors indicates the number of errors that the system returns, and replies indicates the number of responses the system returns. If the value of errors is 0 and the value of replies equals the number of items in the MySQL table, the migration is completed.

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 ApsaraDB for 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.