Secure connection (SSL) in MySQL - Mini Project: Build & Apply

Secure MySQL Connection Using SSL

📖 Scenario: You are setting up a secure connection to a MySQL database for a small business. The business wants to ensure that all data sent between the application and the database is encrypted to protect sensitive information.

🎯 Goal: Build a MySQL client connection configuration that uses SSL to securely connect to the database server.

📋 What You'll Learn

Create a MySQL client configuration dictionary with connection parameters

Add SSL configuration parameters to enable secure connection

Use the SSL parameters in the connection setup

Complete the connection setup with SSL enabled

💡 Why This Matters

🌍 Real World

Businesses use SSL connections to protect data sent between applications and databases from being intercepted or tampered with.

💼 Career

Database administrators and backend developers must know how to configure secure database connections to comply with security standards.

1

Create MySQL connection parameters

Create a dictionary called mysql_config with these exact entries: 'host': 'localhost', 'user': 'dbuser', 'password': 'dbpass', and 'database': 'business_db'.

MySQL

# Create the mysql_config dictionary with host, user, password, and database
# Your code here

Need a hint?

Use a Python dictionary with keys exactly as 'host', 'user', 'password', and 'database'.

2

Add SSL configuration parameters

Add a key 'ssl' to the mysql_config dictionary with a nested dictionary containing these exact entries: 'ca': '/path/to/ca.pem', 'cert': '/path/to/client-cert.pem', and 'key': '/path/to/client-key.pem'.

MySQL

mysql_config = {
    'host': 'localhost',
    'user': 'dbuser',
    'password': 'dbpass',
    'database': 'business_db'
}
# Add SSL configuration inside mysql_config
# Your code here

Need a hint?

Add the 'ssl' key with a nested dictionary for 'ca', 'cert', and 'key' file paths.

3

Use SSL parameters in MySQL connection

Write a line to create a MySQL connection using mysql.connector.connect() with the **mysql_config dictionary to pass all parameters including SSL.

MySQL

mysql_config = {
    'host': 'localhost',
    'user': 'dbuser',
    'password': 'dbpass',
    'database': 'business_db',
    'ssl': {
        'ca': '/path/to/ca.pem',
        'cert': '/path/to/client-cert.pem',
        'key': '/path/to/client-key.pem'
    }
}
# Create a MySQL connection using mysql.connector.connect(**mysql_config)
# Your code here

Need a hint?

Use the unpacking operator ** to pass the dictionary to connect().

4

Complete connection setup with SSL enabled

Add a line to set connection.autocommit = True to complete the secure connection setup.

MySQL

import mysql.connector

mysql_config = {
    'host': 'localhost',
    'user': 'dbuser',
    'password': 'dbpass',
    'database': 'business_db',
    'ssl': {
        'ca': '/path/to/ca.pem',
        'cert': '/path/to/client-cert.pem',
        'key': '/path/to/client-key.pem'
    }
}

connection = mysql.connector.connect(**mysql_config)
# Enable autocommit on the connection
# Your code here

Need a hint?

Set the autocommit attribute of the connection to True.