Secure connection (SSL) in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check SSL status of current MySQL connection

What is the output of the following query when run on a MySQL server with SSL enabled and properly configured?

MySQL

SHOW STATUS LIKE 'Ssl_cipher';

A Empty result set (no rows)

B Ssl_cipher value is always 'NULL'

C Syntax error due to incorrect command

D Ssl_cipher value is a non-empty string representing the cipher used

💡 Hint

Check the status variable that shows the SSL cipher used for the connection.

When SSL is enabled and the connection uses SSL, the 'Ssl_cipher' status variable shows the name of the cipher used. If SSL is not used, it is empty.

🧠 Conceptual

intermediate

Purpose of SSL in MySQL connections

Why is it important to use SSL for MySQL client-server connections?

A To encrypt data transmitted between client and server, protecting it from eavesdropping

B To compress data and improve query speed

C To allow multiple clients to connect simultaneously

D To automatically backup the database during connection

💡 Hint

Think about what SSL does in general for network connections.

SSL encrypts the data sent between client and server, preventing attackers from reading sensitive information like passwords or query results.

📝 Syntax

advanced

Correct syntax to enable SSL in MySQL client connection

Which of the following commands correctly connects to a MySQL server using SSL with client certificate and key files?

A mysql --ssl-enable --ssl-cert client-cert.pem --ssl-key client-key.pem -u user -p

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

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

D mysql --enable-ssl --cert=client-cert.pem --key=client-key.pem -u user -p

💡 Hint

Check the official MySQL client options for SSL parameters.

The correct options use '--ssl-ca', '--ssl-cert', and '--ssl-key' with '=' to specify the certificate files. Option B matches the official syntax.

🔧 Debug

advanced

Diagnose SSL connection failure

A user tries to connect to MySQL with SSL but gets the error: 'SSL connection error: SSL_CTX_set_default_verify_paths failed'. What is the most likely cause?

A The CA certificate file path is incorrect or missing

B The server does not support SSL connections

C The client certificate file is missing or unreadable

D The MySQL user does not have permission to connect

💡 Hint

The error mentions 'default verify paths' which relates to CA certificates.

This error usually means the client cannot find or access the CA certificate to verify the server's certificate.

❓ optimization

expert

Optimize SSL usage for performance in MySQL

Which approach best balances security and performance when using SSL for MySQL connections in a high-traffic environment?

A Use self-signed certificates without verification to speed up connection

B Disable SSL to avoid encryption overhead

C Use SSL with session reuse to reduce handshake overhead

D Use SSL only for initial connection, then switch to unencrypted

💡 Hint

Think about how SSL handshakes affect performance and how to reduce their cost.

SSL session reuse allows clients and servers to reuse previous SSL session parameters, reducing handshake time and CPU load while maintaining encryption.