Deploy an Eleventy Blog on Cloudflare pages with Strapi, MySQL, and Dokku on a Digital Ocean Droplet, part 3
Tags: mysql, devops, dokku, ssh
Published: 2022-06-07 11:04:20

!!! warning
    *here be dragons*

### Table of Contents

- [Table of Contents](#table-of-contents)
- [Deploy an Eleventy Blog on Cloudflare pages with Strapi, MySQL, and Dokku on a Digital Ocean Droplet, part 3](#deploy-an-eleventy-blog-on-cloudflare-pages-with-strapi-mysql-and-dokku-on-a-digital-ocean-droplet-part-3)
- [Apps needed to proceed](#apps-needed-to-proceed)
- [Download and Install MySQL services](#download-and-install-mysql-services)
- [Configure Local MySQL services and choose Authentication Method](#configure-local-mysql-services-and-choose-authentication-method)
- [Install and configure Dokku mysql plugin (Remote)](#install-and-configure-dokku-mysql-plugin-remote)
- [(Remote) Playing around with our new MySQL database](#remote-playing-around-with-our-new-mysql-database)
  - [Sidequest? Adding a MySQL User for extra security](#sidequest-adding-a-mysql-user-for-extra-security)
- [Still to come...](#still-to-come)

### Deploy an Eleventy Blog on Cloudflare pages with Strapi, MySQL, and Dokku on a Digital Ocean Droplet, part 3


Hello, welcome back! In the last post I made, I added SSH keys to my Dokku DO droplet, assigned it a floating IP, and added an A record in Cloudflare DNS settings so we can SSH into it any time with `ssh vic@apps.vicstech.xyz`. I then set up a static IP on my host PC, installed Fail2Ban on my remote Digital Ocean Dokku droplet, and whitelisted our host PC's IP on the remote server in both SSH and Fail2Ban's configuration. In addition, I created a new user to work under and disabled root login for extra security, and hardened both Fail2Ban and SSH configurations against attackers. See [my previous post](https://victorfeight.com/2022/03/secure_dokku_droplet/) for more info.

For this post, I'd like to show you how to install the MySQL plugin for Dokku and see if we can test it out using MySQL workbench remotely.

### Apps needed to proceed

- Node.js v16.13.2. You can use [nvm](https://github.com/nvm-sh/nvm)(node version manager) to install older versions of Node.js or manage multiple versions.

![placeholder](https://victorfeight.com/img/second-post-20220503154729.png)

- npm (node package manager), which comes with Node.js. I'm using Git Bash in this tutorial to run npm but you could also use Powershell.
- [MySQL Community Server v8.0.x.](https://dev.mysql.com/downloads/mysql/)

### Download and Install MySQL services

As I've already installed and set up MySQL, I'll go ahead and relaunch the MySQL Installer now, but these instructions would work for a new installation:
![placeholder](https://victorfeight.com/img/second-post-20220503154452.png)

!!! note
    If you're installing MySQL, choose **Developer Default** option to install necessary services for Strapi. Let the installer install any missing requirements.

![placeholder](https://victorfeight.com/img/second-post-20220503155540.png)

If you get a message like the following, go ahead and click next; you can install missing requirements later.
![placeholder](https://victorfeight.com/img/second-post-20220503155813.png)

Let the installer continue and install any missing products.

### Configure Local MySQL services and choose Authentication Method

MySQL, which Strapi uses, has a client-to-server architecture, which interacts over a network. To configure the server, we need to do a MySQL **Product Configuration** for **MySQL Server** and **MySQL Router**.

MySQL Server will grant the Strapi application access to the DBMS. The DBMS then handles all queries and connections.

MySQL Router acts as a middleware, routing between our Strapi application and the MySQL Server.

For the MySQL Server configuration type, choose "Development Computer", ensure your settings look like this:
![placeholder](https://victorfeight.com/img/second-post-20220503160924.png)

!!! attention
    **Important note:** We need to set the authentication method to **Use Legacy Authentication Method** as I've had issues getting dokku's mysql plugin to work with Strapi using the default recommended authentication method.

Ensure your screen is like this:
![placeholder](https://victorfeight.com/img/second-post-20220503160958.png)

Enter a memorable password for our local MySQL root user, then click next. We want to ensure a successful connection as root user.

For Windows Service settings, leave everything default:
![placeholder](https://victorfeight.com/img/second-post-20220503162229.png)

This will add the MySQL Windows Service to your system, and enable it whenever Windows restarts.

Last, click **"Next"** and then **Apply Configuration** by clicking **Execute**.

![placeholder](https://victorfeight.com/img/second-post-20220503162537.png)

Click **Finish**. When prompted for Router configuration, leave it blank and continue.

Finally, enter your credentials in the test scripts for **root user** to execute the scripts.

![placeholder](https://victorfeight.com/img/second-post-20220503162752.png)

Now MySQL Services and workbench should be successfully installed locally. 🌟

### Install and configure Dokku mysql plugin (Remote)

Now we have installed MySQL services and workbench on our _local_ Windows machine. Next, let's install [dokku-mysql](https://github.com/dokku/dokku-mysql) plugin on our _remote_ Dokku instance.

So far, we haven't touched a single Dokku command. Let's change that by installing the official dokku mysql plugin (currently defaults to mysql 8.0.28):

```bash
sudo dokku plugin:install https://github.com/dokku/dokku-mysql.git mysql
```

Output:
![placeholder](https://victorfeight.com/img/second-post-20220503172505.png)

When it's finished installing, we can create a new database named `db` with the following command:

```bash
dokku mysql:create db
```

Output:
![placeholder](https://victorfeight.com/img/second-post-20220503172839.png)

Note the Dsn:
`mysql://mysql:3e7b42ca09896a88@dokku-mysql-db:3306/db`

We should expose port 3307 on our remote mysql instance so that we can test a remote connection with MySQL workbench.

```bash
dokku mysql:expose db 3307
```

Output:
`-----> Service db exposed on port(s) [container->host]: 3306->3307`

Next, note the config directory: `/var/lib/dokku/services/mysql/db/config`

We can cat for the root password in this directory:

```bash
sudo cat /var/lib/dokku/services/mysql/db/ROOTPASSWORD
```

Take note of it!

With the exposed port and root password, we should be able to remotely access the `db` database from MySQL workbench.

Create a new connection in MySQL Workbench home and input the following information:

**Hostname**: apps.vicstech.xyz
**Port**: 3307
**Username**: root

Click **Test Connection** and enter the mysql root password you just noted.

![placeholder](https://victorfeight.com/img/second-post-20220503175627.png)

Success! 🌟 Some helpful tips for your new instance can be found on this [gist](https://gist.github.com/JdRssll/601209434d9b7df4d384dc99bc5f392e).

### (Remote) Playing around with our new MySQL database

We can connect to our `db` as follows but note as we're a regular user we cannot edit our database on the server: `dokku mysql:connect db`

Enter your dokku db instance with the following command:

```bash
dokku mysql:enter db
```

Then, while in the dokku db bash instance, enter the following, and enter the password noted earlier when prompted:

```bash
mysql -u root -p
```

![placeholder](https://victorfeight.com/img/second-post-20220503183557.png)

Now we're logged into our remote MySQL server as root.

Enter the following command at the root sql cli to see which authentication plugin our root user uses:

```sql
SELECT user, plugin FROM mysql.user WHERE user IN ('root');
```

To connect Strapi with MySQL, we have to ensure the authentication plugin for our root user is `mysql_native_password` as our Strapi/dokku-mysql combo currently does not support the auth plugin that comes with MySQL 8.0.x, _chaching_sha2_password_.
![placeholder](https://victorfeight.com/img/second-post-20220503184825.png)

!!! note
    Seeing 2 root accounts here is normal, one describes the rules for `'root'@'%'` and the other for `'root'@'localhost'`. I went ahead and changed both to use **mysql_native_password.**

Enter the following command, replacing `password` with a memorable root password (you can use the one from earlier, to continue testing queries in MySQL Workbench without trouble).

```sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
```

Then:

```sql
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY  'password';
```

Then refresh privileges with this query:

```sql
flush privileges;
```

Resulting output:
![placeholder](https://victorfeight.com/img/second-post-20220503190840.png)

If your authentication plugin is now `mysql_native_password`, you're good to install Strapi.

#### Sidequest? Adding a MySQL User for extra security

!!! note
    MySQL also comes with some security considerations—a default superuser account called **root** is enabled by default, which can dump the whole database, or create and delete any user from the user management list, and this can be a security issue. It is often sensible therefore to create another MySQL user account, with a more restricted set of privileges for daily use.

### Still to come...

Next time, I'll install Strapi CMS onto Dokku and allow Strapi to talk to our MySQL instance. Last, we'll play around in GraphQL playground (built in to Strapi), querying our MySQL instance.

By the end, we'll be able to use Strapi as a small scale, headless CMS blogging engine, configured with MySQL—giving our blog access to security features and user permission management.