All Products
Search
Document Center

ApsaraDB RDS:Combine ApsaraDB for Redis with ApsaraDB RDS for MySQL

Last Updated:Feb 21, 2024

If you use ApsaraDB RDS for MySQL and you want to accelerate data processing and reduce access latency, Alibaba Cloud provides an efficient solution for persistence of cached data. This solution combines ApsaraDB RDS with ApsaraDB for Redis and ApsaraDB for Memcache to ensure high-speed access and persistent storage. This solution features high throughput and low latency.

Background information

ApsaraDB for Redis and ApsaraDB for Memcache have the following advantages over ApsaraDB RDS:

  • ApsaraDB for Redis and ApsaraDB for Memcache can respond to queries at higher speeds. In most cases, the latency per query is within a few milliseconds.

  • The caches of ApsaraDB for Redis and ApsaraDB for Memcache support higher queries per second (QPS).

Procedure

Combine ApsaraDB RDS for MySQL with ApsaraDB for Redis

Prerequisites

  • An Elastic Compute Service (ECS) instance, an ApsaraDB for Redis instance, and an ApsaraDB RDS for MySQL instance are created.

    • If you want to connect to these instances over an internal network, we recommend that you create the instances in the same virtual private cloud (VPC).

    • If you have created an ECS instance, an ApsaraDB for Redis instance, and an RDS instance but these instances reside in different VPCs, you can enable public endpoints for the ApsaraDB for Redis instance and the RDS instance. This way, you can connect to the instances over the Internet.

    Note
    • In this example, the image version of the ECS instance is Alibaba Cloud Linux 3.2104 LTS 64-bit.

    • In this example, the programming language is Python. Before you start, install Python 3, Pip 3, PyMySQL, and redis-py on the ECS instance.

      Run the following commands to install PyMySQL and redis-py:

      sudo pip3 install pymysql
      sudo pip3 install redis
  • IP address whitelists are configured.

  • Usernames and passwords of database accounts are obtained for the ApsaraDB for Redis instance and the RDS instance. For more information, see Create and manage database accounts and Create an account on an ApsaraDB RDS for MySQL instance.

Procedure

  1. Log on to the ECS instance and write a Python script to simulate a business scenario. If no data is found in the cache of the ApsaraDB for Redis instance, query data from the RDS instance. In this example, the Python script named test.py is created.

    Warning

    This section provides the sample code for demonstration purposes. In the actual business code, do not set the user and password parameters to values in plaintext. We recommend that you configure the parameters by using methods such as external configuration files and environment variables and then reference the parameters in the code.

    import json
    import redis
    import pymysql
    
    # Define the parameters that are used to connect to the RDS instance.
    mysql_host = '<Endpoint of the RDS instance>'
    mysql_user = '<Username>'
    mysql_password = '<Password>'
    mysql_port = 3306
    mysql_charset = 'utf8'
    
    # Define the parameters that are used to connect to the ApsaraDB for Redis instance.
    redis_host = '<Endpoint of the ApsaraDB for Redis instance>'
    redis_port = 6379
    # The password for the ApsaraDB for Redis instance is in the Username:Password format. If no password is not configured, set the redis_password parameter to an empty string.
    redis_password = '<Password for the ApsaraDB for Redis instance>'
    redis_db = 0
    
    
    def create_database_and_tables():
        db = pymysql.connect(host=mysql_host,
                             user=mysql_user,
                             password=mysql_password,
                             port=mysql_port,
                             charset=mysql_charset)
        cursor = db.cursor()
    
        # Create a test database.
        cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")
    
        # Select a database.
        cursor.execute("USE testdb;")
    
        # Create a test table.
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS student (
            s_id INT AUTO_INCREMENT PRIMARY KEY,
            s_name VARCHAR(255) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        """)
    
        # Insert data for testing.
        cursor.execute("""
        INSERT INTO student (s_name) VALUES
        ('Zhangsan'),
        ('Lisi'),
        ('Wangwu')
        ON DUPLICATE KEY UPDATE s_name = VALUES(s_name);
        """)
    
        db.commit()
        cursor.close()
        db.close()
    
    
    def fetch_from_mysql():
        db = pymysql.connect(host=mysql_host,
                             user=mysql_user,
                             password=mysql_password,
                             database="testdb",
                             port=mysql_port,
                             charset=mysql_charset)
        cursor = db.cursor()
        cursor.execute("SELECT * FROM student")
        rows = cursor.fetchall()
        cursor.close()
        db.close()
        return rows
    
    
    def cache_to_redis(redis_client, key, data):
        # Encode the data into JSON strings to store data of complex data types.
        json_data = json.dumps(data)
        # Store data to the ApsaraDB for Redis instance and set the validity period to 600 seconds, which is equivalent to 10 minutes.
        redis_client.setex(key, 600, json_data)
    
    
    def get_from_redis(redis_client, key):
        # Query data from the ApsaraDB for Redis instance.
        json_data = redis_client.get(key)
        if json_data:
            # If data is found, decode the JSON strings.
            data = json.loads(json_data)
            return data
        else:
            return None
    
    
    def main():
        # Create a client for the ApsaraDB for Redis instance.
        redis_client = redis.StrictRedis(
            host=redis_host,
            port=redis_port,
            password=redis_password,
            db=redis_db,
            decode_responses=True  # Automatically decode response data.
        )
    
        # Create a test table for the RDS instance and insert test data into the table.
        create_database_and_tables()
    
        # Define the key for storing student information in the ApsaraDB for Redis instance.
        redis_key = 'students'
    
        # Query data from the ApsaraDB for Redis instance.
        students = get_from_redis(redis_client, redis_key)
    
        if students:
            print("Query data from the ApsaraDB for Redis instance.")
            print(students)
        else:
            print("No data is found in the ApsaraDB for Redis instance. Query data from the RDS instance.")
            # Query data from the RDS instance.
            students = fetch_from_mysql()
            if students:
                print(students)
                # Cache data to the ApsaraDB for Redis instance.
                cache_to_redis(redis_client, redis_key, students)
    
    
    if __name__ == '__main__':
        main()
    
  2. Run test.py.

    python3 test.py
    • If this is the first time you run test.py, data is read from the RDS instance because no data is found in the cache of the ApsaraDB for Redis instance. Sample output:

      No data is found in the ApsaraDB for Redis instance, but data is found in the RDS instance.
      ((1, 'Zhangsan'), (2, 'Lisi'), (3, 'Wangwu'))
    • If this is not the first time you run test.py, data is read from the ApsaraDB for Redis instance because the result of the previous query is cached to the ApsaraDB for Redis instance. Sample output:

      Data queried from the ApsaraDB for Redis instance:
      [[1, 'Zhangsan'], [2, 'Lisi'], [3, 'Wangwu']]

Combine ApsaraDB RDS for MySQL with ApsaraDB for Memcache

Prerequisites

  • An ECS instance, an ApsaraDB for Memcache instance, and an RDS instance are created. Make sure that these instances are created in the same VPC.

    Note
    • You must make sure that these instances are created in the same VPC because ApsaraDB for Memcache does not support connections over the Internet.

    • In this example, the image version of the ECS instance is Alibaba Cloud Linux 3.2104 LTS 64-bit.

    • In this example, the programming language is Python. Before you start, install Python 3, Pip 3, PyMySQL, and python-memcached on the ECS instance.

      Run the following commands to install PyMySQL and python-memcached:

      sudo pip3 install pymysql
      sudo pip3 install python-memcached
  • The CIDR block of the VPC is added to the IP address whitelists of the ApsaraDB for Memcache instance and the RDS instance. For more information, see Configure an IP address whitelist and Configure an IP address whitelist.

  • The username and password of a database account are obtained for the RDS instance. For more information, see Create an account on an ApsaraDB RDS for MySQL instance.

  • The password-free access is enabled for the ApsaraDB for Memcache instance. For more information, see Password-free access.

Procedure

  1. Log on to the ECS instance and write a Python script to simulate a business scenario. If no data is found in the cache of the ApsaraDB for Memcache instance, query data from the RDS instance. In this example, the Python script named test.py is created.

    Warning

    This section provides the sample code for demonstration purposes. In the actual business code, do not set the user and password parameters to values in plaintext. We recommend that you configure the parameters by using methods such as external configuration files and environment variables and then reference the parameters in the code.

    import json
    import pymysql
    import memcache
    
    # Define the parameters that are used to connect to the RDS instance.
    mysql_host = '<Endpoint of the RDS instance>'
    mysql_user = '<Username>'
    mysql_password = '<Password>'
    mysql_port = 3306
    mysql_charset = 'utf8'
    
    # Define the parameters that are used to connect to the ApsaraDB for Memcache instance.
    memcache_host = '<Endpoint of the ApsaraDB for Memcache instance>:<Port>'
    
    
    def create_database_and_tables():
        db = pymysql.connect(host=mysql_host,
                             user=mysql_user,
                             password=mysql_password,
                             port=mysql_port,
                             charset=mysql_charset)
        cursor = db.cursor()
    
        # Create a test database.
        cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")
    
        # Select a database.
        cursor.execute("USE testdb;")
    
        # Create a test table.
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS student (
            s_id INT AUTO_INCREMENT PRIMARY KEY,
            s_name VARCHAR(255) NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        """)
    
        # Insert data for testing.
        cursor.execute("""
        INSERT INTO student (s_name) VALUES
        ('Zhangsan'),
        ('Lisi'),
        ('Wangwu')
        ON DUPLICATE KEY UPDATE s_name = VALUES(s_name);
        """)
    
        db.commit()
        cursor.close()
        db.close()
    
    
    def fetch_from_mysql():
        db = pymysql.connect(host=mysql_host,
                             user=mysql_user,
                             password=mysql_password,
                             database="testdb",
                             port=mysql_port,
                             charset=mysql_charset)
        cursor = db.cursor()
        cursor.execute("SELECT * FROM student")
        rows = cursor.fetchall()
        cursor.close()
        db.close()
        return rows
    
    
    def cache_to_memcache(memcache_client, key, data):
        # Encode the data into JSON strings to store data of complex data types.
        json_data = json.dumps(data)
        Store data to the ApsaraDB for Memcache instance and set the validity period to 600 seconds, which is equivalent to 10 minutes.
        memcache_client.set(key, json_data, time=600)
    
    
    def get_from_memcache(memcache_client, key):
        # Query data from the ApsaraDB for Memcache instance.
        json_data = memcache_client.get(key)
        if json_data:
            # If data is found, decode the JSON strings.
            data = json.loads(json_data)
            return data
        else:
            return None
    
    
    def main():
        # Create a client for the ApsaraDB for Memcache instance.
        memcache_client = memcache.Client([memcache_host], debug=0)
    
        # Create a test table for the RDS instance and insert test data into the table.
        create_database_and_tables()
    
        # Define the key for storing student information in the ApsaraDB for Memcache instance.
        memcache_key = 'students'
    
        # Query data from the ApsaraDB for Memcache instance.
        students = get_from_memcache(memcache_client, memcache_key)
    
        if students:
            print("Query data from the ApsaraDB for Memcache instance.")
            print(students)
        else:
            print("No data is found in the ApsaraDB for Memcache instance. Query data from the RDS instance.")
            # Query data from the RDS instance.
            students = fetch_from_mysql()
            if students:
                print(students)
                # Cache data to the ApsaraDB for Memcache instance.
                cache_to_memcache(memcache_client, memcache_key, students)
    
    
    if __name__ == '__main__':
        main()
    
  2. Run test.py.

    python3 test.py
    • If this is the first time you run test.py, data is read from the RDS instance because no data is found in the cache of the ApsaraDB for Memcache instance. Sample output:

      No data is found in the ApsaraDB for Memcache instance, but data is found in the RDS instance.
      ((1, 'Zhangsan'), (2, 'Lisi'), (3, 'Wangwu'))
    • If this is not the first time you run test.py, data is read from the ApsaraDB for Memcache instance because the result of the previous query is cached to the ApsaraDB for Memcache instance. Sample output:

      Data queried from the ApsaraDB for Memcache instance:
      [[1, 'Zhangsan'], [2, 'Lisi'], [3, 'Wangwu']]

References