Secure connection (SSL) in MySQL - Step-by-Step Execution

Concept Flow - Secure connection (SSL)

Client initiates connection

↓

Server requests SSL handshake

↓

Client and Server exchange certificates

↓

SSL handshake completes successfully?

No→Connection denied

Yes↓

Encrypted communication established

↓

Data sent securely

The client starts connecting, then both sides exchange certificates to verify identity. If successful, they create an encrypted channel for secure data transfer.

Execution Sample

MySQL

mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h host -u user -p

This command connects to MySQL server using SSL certificates for a secure encrypted connection.

Execution Table

Step	Action	Details	Result
1	Client starts connection	Client sends connection request to server	Connection request received
2	Server requests SSL handshake	Server asks client to start SSL handshake	Client prepares certificates
3	Client sends certificates	Client sends CA, client cert, and key	Server verifies certificates
4	Server sends certificates	Server sends its certificate to client	Client verifies server certificate
5	SSL handshake completes?	Both verify certificates and keys match	Handshake successful
6	Encrypted channel established	Data will be encrypted	Secure communication begins
7	Data transfer	Queries and results sent encrypted	Data confidentiality ensured
8	Connection closed	Client or server ends connection	Secure session ends

💡 Connection ends when client or server closes the session securely

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
Connection State	Not connected	Certificates exchanged	SSL handshake complete	Encrypted connection active
Certificates Verified	No	Client cert verified	Both certs verified	Verified throughout session

Key Moments - 2 Insights

Why does the connection fail if certificates don't match?

If certificates don't match or verification fails (see step 5 in execution_table), the SSL handshake fails and the connection is denied to prevent insecure communication.

Is data encrypted before the SSL handshake completes?

No, data encryption starts only after the SSL handshake completes successfully (step 6). Before that, data is not secure.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the client send its certificates?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Action' column in execution_table row 3 where client sends certificates.

According to variable_tracker, what is the connection state after step 5?

A Not connected

B SSL handshake complete

C Certificates exchanged

D Encrypted connection active

💡 Hint

Look at 'Connection State' variable after step 5 in variable_tracker.

If the server rejects the client certificate, what happens according to the concept_flow?

A Connection denied

B Encrypted communication established

C Data sent securely

D Client retries handshake automatically

💡 Hint

See the decision branch 'SSL handshake completes successfully? No -> Connection denied' in concept_flow.

Concept Snapshot

Secure connection (SSL) in MySQL:
- Client and server exchange certificates
- SSL handshake verifies identities
- On success, encrypted channel is established
- Data sent over this channel is secure
- Connection ends when session closes

Full Transcript

This visual execution shows how a secure SSL connection is established between a MySQL client and server. First, the client initiates a connection. The server requests an SSL handshake where both sides exchange certificates to verify each other's identity. If verification succeeds, the SSL handshake completes and an encrypted communication channel is established. Data sent after this point is encrypted and secure. The connection ends when either side closes the session. Variables like connection state and certificate verification status change step-by-step during this process. Common confusions include understanding when encryption starts and why connections fail if certificates don't match. The quizzes help reinforce these key points by referencing the execution steps and variable states.

Practice

1. What is the main purpose of using SSL in a MySQL connection?

easy

A. To backup the database automatically

B. To speed up the database queries

C. To encrypt data between the client and server

D. To increase the size of the database

Solution

1. Step 1: Understand SSL role in MySQL

   SSL encrypts data sent between your computer and the MySQL server to keep it private and safe from eavesdropping.

2. Step 2: Compare options with SSL purpose

   Only To encrypt data between the client and server describes encryption, which is the main goal of SSL. Other options describe unrelated features.

3. Final Answer:

   To encrypt data between the client and server -> Option C

4. Quick Check:

   SSL = encrypt data [OK]

Hint: SSL means secure data transfer by encryption [OK]

Common Mistakes:

• Thinking SSL speeds up queries
• Confusing SSL with backup features
• Believing SSL increases database size

2. Which of the following is the correct MySQL client command option to enable SSL when connecting?

easy

A. --disable-encryption

B. --no-ssl

C. --skip-ssl

D. --ssl-mode=REQUIRED

Solution

1. Step 1: Identify SSL enabling option

   The option --ssl-mode=REQUIRED tells MySQL client to use SSL for the connection.

2. Step 2: Check other options

   Options A, B, and D disable or skip SSL, so they are incorrect for enabling SSL.

3. Final Answer:

   --ssl-mode=REQUIRED -> Option D

4. Quick Check:

   Enable SSL = --ssl-mode=REQUIRED [OK]

Hint: Look for option with 'ssl' and 'required' to enable SSL [OK]

Common Mistakes:

• Using options that disable SSL instead
• Confusing SSL enabling with disabling flags
• Missing the correct syntax for SSL mode

3. Given the command: mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u user -p
What will happen if the ca.pem file is missing?

medium

A. The connection will fail because the CA certificate is required for SSL verification.

B. The connection will succeed without SSL encryption.

C. The client will generate a new CA certificate automatically.

D. The server will ignore SSL and allow connection anyway.

Solution

1. Step 1: Understand role of CA certificate

   The CA certificate (ca.pem) verifies the server's identity to the client. Without it, SSL verification fails.

2. Step 2: Consequence of missing CA file

   Without the CA file, the client cannot verify the server, so the connection fails to protect security.

3. Final Answer:

   The connection will fail because the CA certificate is required for SSL verification. -> Option A

4. Quick Check:

   Missing CA cert = connection fail [OK]

Hint: CA cert is mandatory for SSL verification [OK]

Common Mistakes:

• Assuming connection works without CA cert
• Thinking client auto-generates CA cert
• Believing server ignores SSL without CA cert

4. You run this command to connect with SSL:
mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -u user -p
But you get an error: ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed
What is the most likely cause?

medium

A. The MySQL server does not support SSL connections

B. The client key file client-key.pem is missing or unreadable

C. The password provided is incorrect

D. The CA certificate file ca.pem is corrupted

Solution

1. Step 1: Analyze the SSL error message

   The error SSL_CTX_set_default_verify_paths failed often means the client cannot load the key or certificate files properly.

2. Step 2: Check file availability and permissions

   If the client key file is missing or unreadable, SSL setup fails with this error. Other options do not cause this specific error.

3. Final Answer:

   The client key file client-key.pem is missing or unreadable -> Option B

4. Quick Check:

   SSL_CTX error = missing/unreadable key file [OK]

Hint: Check client key file exists and is readable [OK]

Common Mistakes:

• Blaming server SSL support for client file errors
• Assuming password errors cause SSL_CTX failures
• Ignoring file permission issues on key files

5. You want to enforce SSL connections for all MySQL users and reject non-SSL connections. Which server configuration option should you set?

hard

A. require_secure_transport=ON

B. ssl_mode=DISABLED

C. skip_ssl=TRUE

D. allow_non_ssl=YES

Solution

1. Step 1: Identify server option to enforce SSL

   The require_secure_transport=ON option forces MySQL server to accept only secure (SSL/TLS) connections.

2. Step 2: Verify other options

   Options A, C, and D disable or allow non-SSL connections, which is opposite of the requirement.

3. Final Answer:

   require_secure_transport=ON -> Option A

4. Quick Check:

   Enforce SSL = require_secure_transport=ON [OK]

Hint: Use require_secure_transport=ON to force SSL [OK]

Common Mistakes:

• Using options that disable SSL
• Confusing client options with server settings
• Assuming allow_non_ssl=YES enforces SSL