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.

Scenarios

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.

To use ApsaraDB for Redis as a cache, you must first transmit data from a relational database to ApsaraDB for Redis. You cannot directly transmit tables in a relational database to the ApsaraDB for Redis database that stores data in a key-value structure. Before the migration, you must convert the source data to a special structure. This topic describes how to use the native tool to easily and efficiently migrate tables from MySQL databases to ApsaraDB for Redis. You can use the pipeline feature of ApsaraDB for Redis to transmit data in MySQL tables to hash tables of ApsaraDB for Redis.
Note This topic describes Alibaba Cloud RDS MySQL instance as the migration source and ApsaraDB for Redis instance as the migration destination. In this example, you install the Linux environment that runs the migration command on the ECS instance. These instances run in the same VPC, so they can interconnect with each other.

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.

Prerequisites

  • 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.
Note These prerequisites apply only when you migrate data in Alibaba Cloud. If you want to migrate data in your on-premises environment, make sure that the Linux server that performs migration can connect to the source relational database and the destination ApsaraDB for Redis database.

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.

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.

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', #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
  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 RDS MySQL database.
    Note This is the -h option following mysql.
    		rm-bp1xxxxxxxxxxxx.mysql.rds.aliyuncs.com
    Note 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 redis-cli.
    		r-bp1xxxxxxxxxxxxx.redis.rds.aliyuncs.com
    Note 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.
    		Redispwd233
    Figure 1. Sample code
    Note In the result, errors indicates the number of errors that occur when the system runs the command, 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 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.