You can efficiently migrate data from ApsaraDB RDS for MySQL or on-premises MySQL databases to ApsaraDB for Redis by using the pipeline feature of ApsaraDB for Redis. You can also migrate data from RDS databases that use other engines to ApsaraDB for Redis by performing the steps described in this topic.
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
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
- 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
- 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 followsredis-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, andreplies
indicates the number of responses the system returns. If the value oferrors
is 0 and the value ofreplies
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.