如何通过Python 3实现EC2跨实例连接MySQL数据库并执行查询？

Hey Mike, let's break this down step by step since you're solid with Python 3 but new to AWS. I'll walk you through getting that MySQL connection working first, then share a structured learning path to build up your AWS skills.

1. Verify AWS Network & Security Setup (Critical!) #

Most connection failures here come from misconfigured security groups or network settings—don't skip this:

• Security Groups for MySQL EC2 Instance:
  • Head to the AWS Console → EC2 → Instances → Select your MySQL instance → Go to the "Security" tab → Click the linked security group.
  • Add an inbound rule for MySQL (port 3306) that allows traffic from either:
    • The private IP of your Python EC2 instance (tightest security), or
    • The security group associated with your Python EC2 instance (better for scaling later, since new instances in the same group get automatic access).
• Same VPC Check:
  • Ensure both EC2 instances are in the same VPC (or peered VPCs if they're separate). Check the "Networking" tab in each instance's details to confirm matching VPC IDs.
• Use Private IP for Internal Connections:
  • Always use the private IP of the MySQL EC2 instance (found in its details under "Private IPv4 addresses") instead of the public IP—it's faster and more secure for internal AWS traffic.

2. Configure MySQL on the Target EC2 Instance #

MySQL defaults to blocking remote connections, so we need to adjust that:

• Allow Remote Connections in MySQL Config:
  • SSH into your MySQL EC2 instance. Edit the config file (Ubuntu/Debian: /etc/mysql/mysql.conf.d/mysqld.cnf; RHEL/CentOS: /etc/my.cnf):
    • Find the line bind-address = 127.0.0.1 and change it to bind-address = 0.0.0.0 (or the private IP of the MySQL instance for stricter control).
  • Restart MySQL to apply changes: sudo systemctl restart mysql (Ubuntu) or sudo systemctl restart mysqld (RHEL/CentOS).
• Create a MySQL User with Remote Access:
  • Log into MySQL: mysql -u root -p
  • Create a user authorized to connect from your Python EC2's private IP (use % instead of the IP if you want to allow access from any source, but this is less secure):

    CREATE USER 'your_db_user'@'your_python_ec2_private_ip' IDENTIFIED BY 'your_strong_password';
    GRANT ALL PRIVILEGES ON your_target_database.* TO 'your_db_user'@'your_python_ec2_private_ip';
    FLUSH PRIVILEGES;
    

  • Replace the placeholders with your actual values (username, IP, password, database name).

3. Working Python 3 Code with mysql-connector #

Here's a tested script that handles connections, queries, and cleanup. Make sure to replace the placeholders with your details:

import mysql.connector
from mysql.connector import Error

try:
    # Establish connection to MySQL on EC2
    connection = mysql.connector.connect(
        host='mysql_ec2_private_ip',  # Use the private IP we noted earlier
        database='your_target_database',
        user='your_db_user',
        password='your_strong_password'
    )

    if connection.is_connected():
        # Verify connection success
        db_server_info = connection.get_server_info()
        print(f"Connected to MySQL Server v{db_server_info}")
        
        cursor = connection.cursor()
        cursor.execute("SELECT database();")
        connected_db = cursor.fetchone()
        print(f"Connected to database: {connected_db[0]}")

        # Example query - replace with your own
        cursor.execute("SELECT * FROM your_table LIMIT 5;")
        query_results = cursor.fetchall()
        
        print("\nQuery Results:")
        for row in query_results:
            print(row)

except Error as e:
    print(f"Error connecting to MySQL: {e}")
finally:
    # Clean up connection resources
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()
        print("\nMySQL connection closed")

• Quick note: If you get a ModuleNotFoundError, double-check your install command—use pip3 install mysql-connector-python (the correct package name, not just mysql-connector).

4. Troubleshooting Common Issues #

• Timeout/Connection Refused:
  • Check if MySQL is running on the target instance: sudo systemctl status mysql
  • Reverify that the MySQL EC2's security group allows port 3306 from your Python EC2's IP/security group.
  • Confirm you didn't skip changing the bind-address in MySQL config.
• Access Denied:
  • Double-check that your MySQL user has permission to connect from your Python EC2's exact private IP.
  • Ensure your username and password are correctly typed (no typos!).

Since you're already comfortable with Python, here's a structured path to build AWS expertise:

1. Core AWS Fundamentals #

• Start with EC2 deep dives: Master security groups, VPCs, subnets, SSH access, and instance lifecycle. AWS Skill Builder has free, hands-on labs to practice without spending money.
• Learn IAM (Identity and Access Management): Understand roles, policies, and the "least privilege" principle—this is critical for securing all AWS resources.

2. AWS Database Basics #

• Once you have EC2-hosted MySQL working, try AWS RDS (Managed MySQL): It handles backups, scaling, and maintenance automatically. You'll use similar Python code to connect, but with slightly different security group rules.
• Explore parameter groups and encryption options for RDS to learn production-ready database practices.

3. Python + AWS Integration #

• Learn Boto3 (AWS SDK for Python): Automate EC2 instance management, S3 file operations, RDS backups, and more with Python scripts.
• Try AWS Lambda with Python: Build serverless functions that interact with databases, S3, or APIs—great for automating repetitive tasks.

4. Security Best Practices #

• Learn to encrypt data at rest (EBS encryption for EC2, RDS encryption) and in transit (enable SSL for MySQL connections).
• Use AWS Secrets Manager to store database credentials instead of hardcoding them in your Python code—this is non-negotiable for production.

内容的提问来源于stack exchange，提问作者Mike