> ## Documentation Index
> Fetch the complete documentation index at: https://docs.minimus.io/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL TLS Tutorial

> A guide to setting up MySQL and testing that it accepts TLS connections, enforces authentication, and allows secure read/write operations from a test client

The following guide will help you deploy the Minimus MySQL image with self-signed, locally issued certificates to help you get started. Run the code to try it for yourself.

<Info>
  For production purposes, we recommend using publicly trusted certificates issued by a Certificate Authority (CA).
</Info>

## Components

* **MySQL image built by Minimus**: MySQL container configured with `--require_secure_transport=ON` for client authentication.
* Dynamic certificate generation via OpenSSL:
  * **certgen.sh script**: Shell script that generates a custom CA, server, and client certificates using OpenSSL.
  * **minidebug image**: A Minimus dev toolkit that provides a shell, OpenSSL, and other utilities used to generate the certificates.

## What this guide demonstrates

* TLS handshake validation
* Server/client certificate trust
* Basic auth and MySQL operations
* Image compatibility

## Directory structure

```bash theme={null}
.
├── certgen.sh             # Certificate generation script
├── create-certs.yml       # Compose file to run certgen container
└── docker-compose.yml     # Compose file to run MySQLDB
```

## Deploy MySQL with TLS certificates

### Prerequisite: Authenticate to the Minimus Registry

Run the docker login command to authenticate to the Minimus registry:

```shellscript theme={null}
echo "{token}" | docker login reg.mini.dev -u minimus --password-stdin
```

### Step 1: Generate TLS certificates

<Steps>
  <Step title="Save script that generates TLS certificates">
    Save the following script to a file named `certgen.sh`. The script is used to generate the TLS certificates and store them in a `certs` folder on the host. It sets UID 1000 as the owner of the certificate files to match the default user of the MySQL process inside the container.

    ```bash certgen.sh expandable theme={null}
    #!/bin/sh
    # Company: Minimus

    set -e
    cd /certs

    echo "[INFO] Generating OpenSSL config..."
    cat > openssl.cnf <<EOF
    [req]
    distinguished_name = req_distinguished_name
    prompt = no

    [req_distinguished_name]
    CN = mysql

    [v3_req]
    keyUsage = keyEncipherment, dataEncipherment
    extendedKeyUsage = serverAuth
    subjectAltName = @alt_names

    [v3_client]
    keyUsage = digitalSignature
    extendedKeyUsage = clientAuth
    subjectAltName = @alt_names

    [v3_ca]
    subjectKeyIdentifier = hash
    authorityKeyIdentifier = keyid:always,issuer
    basicConstraints = critical, CA:true
    keyUsage = critical, keyCertSign, cRLSign

    [alt_names]
    DNS.1 = mysql
    DNS.2 = localhost
    IP.1 = 172.0.0.1
    IP.2 = 192.168.40.0
    IP.3 = 192.168.40.2
    IP.4 = 192.168.40.3

    EOF

    echo "[INFO] Creating CA certificate..."
    openssl genrsa -out ca-key.pem 2048
    openssl req -x509 -new -nodes -key ca-key.pem -sha256 -days 365 \
      -out ca.pem -subj "/CN=MySQL Test CA" \
      -extensions v3_ca -config openssl.cnf

    echo "[INFO] Creating server certificate..."
    openssl genrsa -out server-key.pem 2048
    openssl req -new -key server-key.pem -out server.csr \
      -config openssl.cnf -extensions v3_req
    openssl x509 -req -in server.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial \
      -out server-cert.pem -days 365 -sha256 \
      -extfile openssl.cnf -extensions v3_req

    echo "[INFO] Creating client certificate..."
    openssl genrsa -out client-key.pem 2048
    openssl req -new -key client-key.pem -out client.csr -subj "/CN=root"
    openssl x509 -req -in client.csr -CA ca.pem -CAkey ca-key.pem -CAcreateserial \
      -out client-cert.pem -days 365 -sha256 \
      -extfile openssl.cnf -extensions v3_client

    # Permissions
    chmod 644 *.pem
    chown -R 1000:1000 /certs/*.pem || echo "[WARN] chown failed (non-root?)"

    echo "[SUCCESS] Certificates generated for MySQL."
    ```
  </Step>

  <Step title="Save Docker Compose configuration">
    Save the following YAML file to run with Docker Compose. It uses the [**Minimus minidebug image**](https://images.minimus.io/gallery/images/minidebug/quick-start?__hstc=180987128.11065ee83c8bdcec1851176c12d849d3.1762436227738.1762436227738.1762436227738.1&__hssc=180987128.1.1762436227739&__hsfp=2666866004) to generate the certificates with the `certgen.sh` shell script. Minidebug is a Minimus dev toolkit that provides a shell, OpenSSL, and other utilities. The certificates will be persisted in the `certs` volume on the host.

    ```yaml create-certs.yml theme={null}
    services:
      certgen:
        image: reg.mini.dev/minidebug:latest
        container_name: MySQL_certgen
        volumes:
        - ./certs:/certs
        - ./certgen.sh:/certgen.sh:ro
        entrypoint:
        - /bin/sh
        - /certgen.sh
        network_mode: none
    ```
  </Step>

  <Step title="Generate certificates">
    Run the following to generate the certificates:

    ```shellscript theme={null}
    docker compose -f create-certs.yml up
    ```
  </Step>
</Steps>

Congrats! You have just generated the following self-signed certificates:

* Self-signed CA certificate (`ca.pem`)
* Server certificates (`server-cert.pem`, `server-key.pem`, `server.csr`)
* Client certificates for `testuser` (`client.csr`, `client.pem`, `client-cert.pem`, `client-key.pem`)

Certificate permissions are adjusted to support non-root containers. In the next steps, we will mount these certificates into the MySQL container.

### Step 2: Deploy MySQL server

<Steps>
  <Step title="Save Docker Compose script">
    Save the following Docker Compose script to a file named `docker-compose.yml`. This script sets up the MySQL service with a healthcheck, mounts a volume with the certificates, maps port 3306, and connects the container to a custom network:

    ```yaml docker-compose.yml expandable theme={null}
    services:
      mysql:
        image: reg.mini.dev/mysql:latest
        container_name: mysql-1
        environment:
          MYSQL_ROOT_PASSWORD: rootpass
          MYSQL_DATABASE: testdb
          MYSQL_USER: testuser
          MYSQL_PASSWORD: testpass
        volumes:
          - ./certs:/certs:ro
        ports:
        - 3306:3306
        healthcheck:
          test:
          - CMD
          - mysqladmin
          - ping
          - -ptestpass
          interval: 5s
          retries: 10
        command:
        - --ssl-ca=/certs/ca.pem
        - --ssl-cert=/certs/server-cert.pem
        - --ssl-key=/certs/server-key.pem
        - --require_secure_transport=ON
        - --skip-name-resolve
    ```
  </Step>

  <Step title="Run MySQL">
    Start the MySQL container:

    ```shellscript theme={null}
    docker compose -f docker-compose.yml up -d
    ```
  </Step>
</Steps>

### Step 3: Test your MySQL server

Following are a few commands you can try out:

1. Connect to the database:
   ```bash theme={null}
   mysql -h 127.0.0.1 -P 3306 -u root -p \
     --ssl \
     --ssl-ca=./certs/ca.pem \
     --ssl-cert=./certs/client-cert.pem \
     --ssl-key=./certs/client-key.pem
   ```
   You should get a response from the server asking to input the password. If you used the compose file from this guide as is, the password is `rootpass`.
2. Create a test database (for example `my_new_db`):

   <CodeGroup>
     ```shellscript Create database theme={null}
     CREATE DATABASE my_new_db; 
     ```

     ```bash Expected response theme={null}
     MySQL [(none)]> CREATE DATABASE my_new_db; 
     Query OK, 1 row affected (0.010 sec)
     ```
   </CodeGroup>
3. List all databases:

   <CodeGroup>
     ```bash List all databases theme={null}
     SHOW DATABASES;
     ```

     ```bash Example response theme={null}
     MySQL [(none)]> SHOW DATABASES;
     +--------------------+
     | Database           |
     +--------------------+
     | information_schema |
     | my_new_db          |
     | mysql              |
     | performance_schema |
     | sys                |
     | testdb             |
     +--------------------+
     6 rows in set (0.012 sec)
     ```
   </CodeGroup>
4. Show server version:

   <CodeGroup>
     ```bash Show server version theme={null}
     SELECT version();
     ```

     ```bash Example output theme={null}
     MySQL [(none)]> SELECT version();
     +-----------+
     | version() |
     +-----------+
     | 9.5.0     |
     +-----------+
     1 row in set (0.001 sec)
     ```
   </CodeGroup>
5. Check that TLS is active:

   <CodeGroup>
     ```bash Check TLS theme={null}
     SHOW VARIABLES LIKE 'tls_version';
     SHOW STATUS LIKE 'Ssl_version';
     SHOW STATUS LIKE 'Ssl_cipher';
     SHOW VARIABLES LIKE 'ssl_%';
     ```

     ```bash Example response theme={null}
     MySQL [(none)]> SHOW VARIABLES LIKE 'tls_version';
     +---------------+-----------------+
     | Variable_name | Value           |
     +---------------+-----------------+
     | tls_version   | TLSv1.2,TLSv1.3 |
     +---------------+-----------------+
     1 row in set (0.004 sec)
     MySQL [(none)]> SHOW STATUS LIKE 'Ssl_version';
     +---------------+---------+
     | Variable_name | Value   |
     +---------------+---------+
     | Ssl_version   | TLSv1.3 |
     +---------------+---------+
     1 row in set (0.002 sec)
     MySQL [(none)]> SHOW STATUS LIKE 'Ssl_cipher';
     +---------------+------------------------+
     | Variable_name | Value                  |
     +---------------+------------------------+
     | Ssl_cipher    | TLS_AES_128_GCM_SHA256 |
     +---------------+------------------------+
     1 row in set (0.002 sec)
     MySQL [(none)]> SHOW VARIABLES LIKE 'ssl_%';
     +---------------------------+------------------------+
     | Variable_name             | Value                  |
     +---------------------------+------------------------+
     | ssl_ca                    | /certs/ca.pem          |
     | ssl_capath                |                        |
     | ssl_cert                  | /certs/server-cert.pem |
     | ssl_cipher                |                        |
     | ssl_crl                   |                        |
     | ssl_crlpath               |                        |
     | ssl_fips_mode             | OFF                    |
     | ssl_key                   | /certs/server-key.pem  |
     | ssl_session_cache_mode    | ON                     |
     | ssl_session_cache_timeout | 300                    |
     +---------------------------+------------------------+
     10 rows in set (0.003 sec)
     ```
   </CodeGroup>

   <Info>
     Some server options and system variables were recently deprecated, including `--ssl`, `--skip-ssl`, and `--admin-ssl` server options, and the `have_ssl` and `have_openssl` system variables. [Learn more](https://dev.mysql.com/doc/relnotes/mysql/8.4/en/news-8-4-0.html)
   </Info>