Secure connection (SSL) in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to specify the SSL mode for a secure MySQL connection.

MySQL

mysql --host=example.com --user=user --password=pass --ssl-mode=[1]

Drag options to blanks, or click blank then click option'

ADISABLED

BREQUIRED

CNONE

DOFF

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISABLED or NONE disables SSL, making connection insecure.

Using OFF is not a valid ssl-mode option.

The --ssl-mode=REQUIRED option ensures the connection uses SSL encryption.

2fill in blank

medium

Complete the code to specify the path to the CA certificate for SSL verification.

MySQL

mysql --host=example.com --user=user --password=pass --ssl-ca=[1]

Drag options to blanks, or click blank then click option'

A/etc/mysql/ca.pem

B/var/lib/mysql/client-key.pem

C/home/user/client-cert.pem

D/tmp/mysql.sock

Attempts:

3 left

💡 Hint

Common Mistakes

Using client key or cert files for --ssl-ca option.

Using socket file path instead of certificate.

The --ssl-ca option points to the CA certificate file to verify the server's certificate.

3fill in blank

hard

Fix the error in the command to enable SSL with client certificate and key.

MySQL

mysql --host=example.com --user=user --password=pass --ssl-cert=[1] --ssl-key=/home/user/client-key.pem

Drag options to blanks, or click blank then click option'

A/etc/mysql/ca.pem

B/home/user/client-key.pem

C/home/user/client-cert.pem

D/tmp/mysql.sock

Attempts:

3 left

💡 Hint

Common Mistakes

Using client key file for --ssl-cert option.

Mixing up CA certificate with client cert.

The --ssl-cert option must point to the client certificate file, not the key or CA.

4fill in blank

hard

Fill both blanks to configure a secure MySQL connection with SSL mode and CA certificate.

MySQL

mysql --host=example.com --user=user --password=pass --ssl-mode=[1] --ssl-ca=[2]

Drag options to blanks, or click blank then click option'

AREQUIRED

BDISABLED

C/etc/mysql/ca.pem

D/home/user/client-key.pem

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISABLED disables SSL.

Using client key file for CA certificate option.

Use REQUIRED for ssl-mode to enforce SSL and specify the CA certificate path with --ssl-ca.

5fill in blank

hard

Fill all three blanks to connect securely with SSL mode, client cert, and client key.

MySQL

mysql --host=example.com --user=user --password=pass --ssl-mode=[1] --ssl-cert=[2] --ssl-key=[3]

Drag options to blanks, or click blank then click option'

ADISABLED

B/home/user/client-cert.pem

C/home/user/client-key.pem

DREQUIRED

Attempts:

3 left

💡 Hint

Common Mistakes

Using DISABLED disables SSL.

Swapping client cert and key file paths.

Set ssl-mode to REQUIRED and provide correct paths for client certificate and key files.

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