You can efficiently migrate data from RDS 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 based on the steps described in this topic.
You can use ApsaraDB for Redis as a cache between your applications and databases to expand the service capabilities of traditional relational databases. In this way, you can optimize the business ecosystem. This is one of the classic application scenarios of ApsaraDB for Redis. This service stores hot data in the business. You can easily obtain common data in ApsaraDB for Redis from your applications, or use ApsaraDB for Redis to save sessions of active users in interactive applications. This service can greatly reduce the load on the backend relational database and improve the user experience.
Similarly, you can migrate data from other relational databases to ApsaraDB for Redis. In this migration, you need to 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.
- You have created an RDS MySQL instance as the source where table data is available for migration.
- You have created an ApsaraDB for Redis instance as the destination.
- You have created an ECS instance that runs the Linux system.
- These instances run in the same VPC of the same region.
- You have added the internal IP address of the ECS instance to the whitelists of RDS MySQL and ApsaraDB for Redis instances.
- You have installed MySQL and Redis databases on the ECS instance to extract, convert, and transmit data.
Data before migration
This table includes six columns. After the migration, the value of the id column in the MySQL table changes to the key of the hash table in ApsaraDB for Redis. The column names of other columns change to the fields of the hash table, and the values of these columns change to the values of the corresponding fields. You can modify the scripts and commands for the migration according to actual scenarios.
- 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', #HMSET specifies the command that you use when writing data to ApsaraDB for Redis. 'HMSET', '\r\n', '$', LENGTH(id), '\r\n', #id specifies the first field after you run the HMSET command for fields. This field changes to the key of the hash table in ApsaraDB for Redis. id, '\r\n', '$', LENGTH('name'), '\r\n', #'name' 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 changes 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\n' ) 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 Option Description Example -h The endpoint of the RDS MySQL database.Note This is the -h option following
rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.comNote Use the endpoint for connecting the Linux server to the RDS MySQL database. -P The service port of the RDS MySQL database. 3306 -u The username of the RDS MySQL database. testuser -D The database where the MySQL table that you want to migrate is located. mydatabase -p The password for connecting to the RDS MySQL database.Note
- If you do not have any password, you do not need to set this option.
- To improve security, you can enter -p and do not have the password following this option. You can run the command and then enter the password according to the command-line interface (CLI) 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 Specifies the endpoint of ApsaraDB for Redis.Note This is the -h option following
r-bp1xxxxxxxxxxxxx.redis.rds.aliyuncs.comNote Use the endpoint for connecting 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 for connecting to ApsaraDB for Redis.Note If you do not have any password or do not need a password, you do not need to set this option. Redispwd233Note In the result,
errorsindicates the number of errors that occur when the system runs the command, and
repliesindicates the number of responses the system returns. If the value of
errorsis 0 and the value of
repliesequals the number of items in the MySQL table, the migration is completed.
Data after migration
After the migration, one item in the MySQL table corresponds to one item in the hash table of ApsaraDB for Redis. You can run the HGETALL command to query an item 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.