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.

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 you start, you must convert the source data to a specific structure. This topic describes how to use the open source tool to migrate tables from MySQL databases to ApsaraDB for Redis in an easy and efficient way. You can use the pipeline feature of ApsaraDB for Redis to transmit data in MySQL tables to hash tables of ApsaraDB for Redis.
Note In this example, data is migrated from the source ApsaraDB RDS for MySQL instance to the destination ApsaraDB for Redis instance. A Linux environment that is deployed on an Elastic Compute Service (ECS) instance is used to run the command to migrate data. These instances are deployed in the same virtual private cloud (VPC), therefore they can communicate with each other.

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.
Note These prerequisites apply only when you migrate data on 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.

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
    Figure 1. Sample code
    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.