Tair (Redis® OSS-Compatible):Migrate from MySQL to Tair

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 has six columns. After the migration, the values from the id column of the MySQL table become the keys of the Redis hash. The names of the other columns become the hash fields, and the column values become the values of the corresponding fields. You can adjust the scripts and commands in the migration steps based on your requirements.

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 refers to the -h option after 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 output, errors indicates the number of errors that occurred during execution, and replies indicates the number of replies received. If the value of errors is 0 and the value of replies is the same as the number of records in the MySQL table, the migration is successful.

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 plan based on the query methods required for your scenario. For example, you can convert other columns in the MySQL data to hash keys and convert the id column to a field, or omit the id column entirely.