Access an ApsaraDB RDS for MySQL database over a VPC for enhanced security - Function Compute

This topic provides guidance on implementing access to an ApsaraDB RDS for MySQL database from Function Compute over a virtual private cloud (VPC). Specifically, you can configure virtual private cloud (VPC)-related settings in a function and a whitelist in the database to access the ApsaraDB RDS for MySQL database and perform related operations. In this topic, Serverless Devs is used to deploy a function to access an ApsaraDB RDS for MySQL database in the Python 3 runtime.

Before you start

Create an ApsaraDB RDS for MySQL instance
Important
- Make sure that the database instance that you create is in the same region as the function that needs to access the database instance.
- We recommend that you create the database instance in a zone that Function Compute supports. For more information, see Zones where Function Compute is available.
  If your database instance is not in a zone that is supported by Function Compute, you can create a vSwitch in your VPC in the same zone as Function Compute and use this vSwitch ID in the VPC configurations of the function. vSwitches in the same VPC can communicate with each other over the private network. Therefore, Function Compute can use the vSwitch to access resources in VPCs that reside in other zones. For more information, see What do I do if the vSwitch is in unsupported zone error is reported?
Create accounts and databases
Create a table named user in the created database.
The logic of the sample code in index.py of this topic is to query all data in the user database table. Therefore, you must create the table in advance.

Procedure

Install Serverless Devs and Docker and configure the AccessKey information.
For more information, see Install Serverless Devs and dependencies and Configure Serverless Devs.
Run the following command to initialize a project.
```
sudo s init 
```
In the CLI, specify Alibaba Cloud as the vendor, specify the quick start mode, and select a built-in Python runtime. Configure the project name and the region where the project is deployed. In this example, the start-fc-mysql-python project is deployed in the China (Hangzhou) region.
Run the following command to go to the project directory.
```
cd start-fc3-python
```

Modify the directory file based on your own business requirements.

Edit the s.yaml file. Example:

edition: 3.0.0
name: hello-world-app
access: "default"

vars: # The global variables.
  region: "cn-hangzhou"

resources:
  hello_world:
    component: fc3 # The component name.
    # actions:       # The custom execution logic. For more information about actions, visit https://docs.serverless-devs.com/serverless-devs/yaml#%E8%A1%8C%E4%B8%BA%E6%8F%8F%E8%BF%B0actions.
    props:
      region: ${vars.region}              # For information about how to use variables, visit https://docs.serverless-devs.com/serverless-devs/yaml#%E5%8F%98%E9%87%8F%E8%B5%8B%E5%80%BC.
      functionName: "start-python-0t1m"
      description: 'hello world by serverless devs'
      runtime: "python3.9"
      code: ./code
      handler: index.handler
      memorySize: 128
      timeout: 30
      internetAccess: true
      vpcConfig:
       vpcId: vpc-bp11y195luy47h8c**** # The ID of the VPC in which the database instance resides.
       securityGroupId: sg-bp1el3hto8hhkdu**** # The security group ID.
       vSwitchIds: 
        - vsw-bp116uemmj7fniub**** # Make sure that the CIDR block of the vSwitch is added to the whitelist of the database instance.
      environmentVariables:      
       PYTHONPATH: /code/python  
       MYSQL_HOST: rm-bp19j9og672d4****.mysql.rds.aliyuncs.com  # The private endpoint of the database instance.
       MYSQL_PORT: "3306"  # The private port of the database instance.
       MYSQL_USER: z*****  # The database created in the database instance.
       MYSQL_PASSWORD: 1****   # The password of the account that is used to access the database instance.
       MYSQL_DBNAME: db****  # The name of the database created in the database instance.

Important

Make sure that the CIDR block of the vSwitch that you configure for the function is added to the whitelist of the database instance. For more information, see Configure an IP address whitelist for the database.

Edit the index.py file. The following code snippet shows the example. The code logic is to query all data in the user table.

# -*- coding: utf-8 -*-
import pymysql  # You need to install the pymysql library first.
import os
import logging


def handler(event, context):
    # Obtain the information used to connect the ApsaraDB RDS for MySQL database. In most cases, such information can be obtained by using environment variables or Key Management Service (KMS).
    db_host = os.environ['MYSQL_HOST']
    db_port = int(os.environ['MYSQL_PORT'])
    db_user = os.environ['MYSQL_USER']
    db_password = os.environ['MYSQL_PASSWORD']
    db_name = os.environ['MYSQL_DBNAME']

    # Establish a database connection.
    connection = pymysql.connect(host=db_host,
                                 port=db_port,
                                 user=db_user,
                                 password=db_password,
                                 db=db_name)

    try:
        with connection.cursor() as cursor:
            # Query all data records in the user table.
            sql = "SELECT * FROM user"
            cursor.execute(sql)
            result = cursor.fetchall()

            for row in result:
                print(f"User: {row}")

    except Exception as e:
        logging.error(f"Error occurred during database operation: {e}")

    finally:
        # Close the database connection.
        connection.close()

    return 'Finished querying the user table'

Run the following command to build a project.
```
sudo s build --use-docker
```
Run the following command to deploy the project.
```
sudo s deploy -y
```

Run the following command to invoke the function.

sudo s invoke -e "{}"

The following code snippet shows the expected output. The returned result indicates that the user table has been successfully accessed.

========= FC invoke Logs begin =========
FC Invoke Start RequestId: 1-65d2b4b4-1502c418-30faff838bec
FC Invoke End RequestId: 1-65d2b4b4-1502c418-30faff838bec

Duration: 35.37 ms, Billed Duration: 36 ms, Memory Size: 128 MB, Max Memory Used: 14.16 MB
========= FC invoke Logs end =========

Invoke instanceId: c-65d2b45f-15f440b6-a01ef0d32d36
Code Checksum: 8737553540873826675
Qualifier: LATEST
RequestId: 1-65d2b4b4-1502c418-30faff838bec

Invoke Result:
Finished querying the user table
> [hello_world] completed (0.25s)

Configure an IP address whitelist for the database

Important

Use an IP address whitelist to authorize functions to access the database. Do not use the security group mode. Otherwise, functions may occasionally fail to connect to the database, which affects the businesses.

Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.
In the left-side navigation pane, click Whitelist and SecGroup.
On the Whitelist Settings tab, you can view the mode of the IP address whitelist.
Note
Existing RDS instances may run in enhanced whitelist mode. All new RDS instances run in standard whitelist mode.
On the Whitelist Settings tab, find the default whitelist group and click Modify.
In the Edit Whitelist dialog box, modify the content in IP Addresses and click OK.
Important
Enter the CIDR block of the vSwitch that you configure for the function in Function Compute.

More information

For more information about accessing an ApsaraDB RDS for MySQL database, see Python access to mysql in Function Compute.
For more information about how to view the information about the configured vSwiches in Function Compute and add CIDR blocks of Function Compute vSwiches to the whitelist of an ApsaraDB RDS for MySQL database, see Configure network settings and Configure an IP address whitelist.
You can troubleshoot a database connection failure by referring to How to troubleshoot database access failures?