DEV Community

Cover image for Deploy an Eleventy Blog on Cloudflare pages with Strapi, MySQL, and Dokku on a Digital Ocean Droplet, part 3
Victor Feight
Victor Feight

Posted on • Originally published at victorfeight.com

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

!!! warning
here be dragons

Table of Contents

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 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(node version manager) to install older versions of Node.js or manage multiple versions.

placeholder

  • 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.

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

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

placeholder

If you get a message like the following, go ahead and click next; you can install missing requirements later.
placeholder

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

!!! 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

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

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

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

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 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):

sudo dokku plugin:install https://github.com/dokku/dokku-mysql.git mysql
Enter fullscreen mode Exit fullscreen mode

Output:
placeholder

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

dokku mysql:create db
Enter fullscreen mode Exit fullscreen mode

Output:
placeholder

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.

dokku mysql:expose db 3307
Enter fullscreen mode Exit fullscreen mode

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:

sudo cat /var/lib/dokku/services/mysql/db/ROOTPASSWORD
Enter fullscreen mode Exit fullscreen mode

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

Success! 🌟 Some helpful tips for your new instance can be found on this gist.

(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:

dokku mysql:enter db
Enter fullscreen mode Exit fullscreen mode

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

mysql -u root -p
Enter fullscreen mode Exit fullscreen mode

placeholder

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:

SELECT user, plugin FROM mysql.user WHERE user IN ('root');
Enter fullscreen mode Exit fullscreen mode

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

!!! 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).

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Enter fullscreen mode Exit fullscreen mode

Then:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY  'password';
Enter fullscreen mode Exit fullscreen mode

Then refresh privileges with this query:

flush privileges;
Enter fullscreen mode Exit fullscreen mode

Resulting output:
placeholder

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.

Top comments (0)