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.
NoteIn 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
, andredis-py
on the ECS instance.Run the following commands to install
PyMySQL
andredis-py
:sudo pip3 install pymysql sudo pip3 install redis
IP address whitelists are configured.
If you want to connect to the instances over an internal network, add the CIDR block of the VPC to the IP address whitelists of the ApsaraDB for Redis instance and the RDS instance. For more information, see Configure whitelists and Configure an IP address whitelist.
If you want to connect to the instances over the Internet, enable public endpoints for the ApsaraDB for Redis instance and the RDS instance, and add the public IP address of the ECS instance to the IP address whitelists of the ApsaraDB for Redis instance and the RDS instance. For more information, see Apply for a public endpoint for an ApsaraDB for Redis instance and Apply for or release a public endpoint for an ApsaraDB RDS for MySQL instance.
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
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.WarningThis section provides the sample code for demonstration purposes. In the actual business code, do not set the
user
andpassword
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()
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.
NoteYou 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
, andpython-memcached
on the ECS instance.Run the following commands to install
PyMySQL
andpython-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
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.WarningThis section provides the sample code for demonstration purposes. In the actual business code, do not set the
user
andpassword
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()
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
For more information about ApsaraDB for Redis, see What is ApsaraDB for Redis?
For more information about ApsaraDB for Memcache, see What is ApsaraDB for Memcache?