1

I've hosted my MySQL instance in GCP project and I want to use it's database in AWS Lambda Function. I've tried all the ways to connect to my DB in MySQL instance in GCP but the Lambda Function give me Timeout Error even though I've kept my Timeout period enough to run the function. I've also Zipped the Package with MySQL and pymysql installed and then uploaded to Lambda but the issues still persists.

Here's the code that I've written for connecting to my DB:

import json
import boto3
import mysql.connector
import MySQLdb

def lambda_handler(event, context):
    mydb = MySQLdb.connect(
    host="Public Ip of MySQL Instance",
    user="Username",
    password="Password",
    db="DbName"
    )
    cur = db.cursor()
    cur.execute("SELECT * FROM budget")
    for row in cur.fetchall():
        print(row[0])
    
    db.close()

Here's the Error that I receive:

{
  "errorMessage": "(2003, \"Can't connect to MySQL server on '36.71.43.131' (timed out)\")",
  "errorType": "OperationalError",
  "stackTrace": [
    "  File \"/var/lang/lib/python3.8/imp.py\", line 234, in load_module\n    return load_source(name, filename, file)\n",
    "  File \"/var/lang/lib/python3.8/imp.py\", line 171, in load_source\n    module = _load(spec)\n",
    "  File \"<frozen importlib._bootstrap>\", line 702, in _load\n",
    "  File \"<frozen importlib._bootstrap>\", line 671, in _load_unlocked\n",
    "  File \"<frozen importlib._bootstrap_external>\", line 783, in exec_module\n",
    "  File \"<frozen importlib._bootstrap>\", line 219, in _call_with_frames_removed\n",
    "  File \"/var/task/lambda_function.py\", line 10, in <module>\n    connection = pymysql.connect(host='36.71.43.131',\n",
    "  File \"/var/task/pymysql/connections.py\", line 353, in __init__\n    self.connect()\n",
    "  File \"/var/task/pymysql/connections.py\", line 664, in connect\n    raise exc\n"
  ]
}

Please help me to resolve this. I've tried all different ways to connect to my SQL instance but nothing works.

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
zea
  • 21
  • 1
  • 2
    Is your AWS Lambda associated to your AWS VPC, Subnet(s) and Security Group(s)? – amitd Feb 04 '21 at 15:35
  • You can troubleshoot connectivity as mentioned in [this thread](https://stackoverflow.com/a/65789538/14843902) – amitd Feb 04 '21 at 15:39
  • This means that your MySQL database will be at least somewhat accessible on the public internet. You can do a bit of IP based security along with credentials but this is a terrible architecture. You will spend more money because the Lambda will be slower. Google cloud functions are conceptually similar to AWS Lambda. – stdunbar Feb 04 '21 at 15:44
  • Do you follow [this document](https://cloud.google.com/sql/docs/sqlserver/configure-ip) to enabling public IP and adding an authorized address or address range for public IP contection? Do you try to connect your SQL instance from GCP VM? – William Feb 04 '21 at 17:28
  • @amitd yes I have done the required networking connections appropriately and tested too. It works fine. – zea Feb 04 '21 at 19:01
  • @William When I try to add the IP range of my VPC network from AWS it shows this error: Private networks cannot be whitelisted. Also because the IP address of my SQL Instance in GCP is public I don't think there's a need to add any specific IP address/range as it can be accessible over internet right? – zea Feb 04 '21 at 19:12
  • That only for allowing public IP address or address range to access your SQL instance, so you got that error if you added private IP, even if GCP VM which in same VPC network requests access to SQL instance, you have to add the public IP of VM as an authorized address. – William Feb 04 '21 at 19:41
  • @William Okay got it. But Since I have to access the SQL from aws Lambda. I've configured the lambda to use the internet but since my VPC has a private IP how do I allow it do connect to the SQL instance. I'm stuck here – zea Feb 05 '21 at 03:40
  • @William Thanks a lot! My issue got resolved by the suggestion you gave! I added the eip as the authorized Ip address in MYSQL instance and it resolved my issue. – zea Feb 05 '21 at 05:34
  • Great, please accept my answer, thanks. – William Feb 05 '21 at 15:16

1 Answers1

0

According to the error message, AWS Lambdathe tried to connect the Public IP address of MySQL instance directly.

You have to configure your MySQL instance to have a public IPv4 address, and to accept connections from specific IP addresses or a range of addresses by adding authorized addresses to your instance.

To configure access to your MySQL instance:

  1. From the client machine, use What's my IP to see the IP address of the client machine.
  2. Copy that IP address.
  3. Go to the Cloud SQL Instances page in the Google Cloud Console.
  4. Click the instance to open its Overview page, and record its IP address.
  5. Select the Connections tab.
  6. Under Authorized networks, click Add network and enter the IP address of the machine where the client is installed. Note: The IP addresses must be IPv4. That is, the IP addresses of the instance, and of the client machine that you authorize, both must be IPv4.
  7. Click Done. Then click Save at the bottom of the page to save your changes.
William
  • 151
  • 5