Configuring Strapi and MySQL

In this tutorial we will cover the MySQL installation processes. But wait, Is that all? A big no. I will also guide you to configure the installed MySQL to work with our headless CMS, Strapi.

Strapi uses SQLite database client by default, which is good. But now, there comes a time when you need to scale up your application and handle large volumes of data. MySQL, a feature-rich database is an ideal solution to such a scenario. The great part is that MySQL is not limited only to large scale applications. With that in mind, you can use Strapi to create a small scale headless CMS e-commerce and configure it with MySQL. Giving your application access to security features as well as user permission management.

Goals:

• Install MySQL Community Server on your environment.
• Configure MySQL Community server to work with Strapi.
• Use npm (node package manager) or Yarn to create a Strapi application.

Prerequisites:

Make sure you have the following to proceed;

• Node.js LTS version v14.19.0. You can use nvm (node version manager) to install older versions of Node.js. nvm also makes it easy for you to manage many versions of Node.js in the same environment.
• npm (node package manager), which comes with Node.js. There is no need to install npm. You can use Yarn as an alternative to npm. To install Yarn, run the command: npm install -g yarn in your terminal.
• MySQL Community Server v8.0.x. You will see a dropdown menu on the MySQL downloads page. Select your Operating System (OS) alongside a corresponding OS version from that menu. Download the installation package for your specific OS.

Great! Now that you have the above prerequisites let’s head over to the next phase, installing MySQL.

Installing MySQL Server on Windows

Step 1 - Choosing a Setup Type;

In this step, you should choose a setup type from the list provided. But for this tutorial, you will go with the most basic installation. On the “Choosing a Setup Type” tab make sure to choose the first option, “Developer Default”. This means the most basic installation. MySQL installer will install all the vital software you need to have for development.

Select the "Developer Default" option.

Done? Okay, make sure you have chosen “Developer Default” as your setup type.

Afterwards, click on “Next” and head over to the next step. In the upcoming step, you will resolve some missing requirements.

Step 2 - Check Requirements;

Like we said earlier on, in this step you will be resolving missing requirements on your machine. The installer will prompt you with a list of required software that needs resolving. To resolve the requirements, click on each item from the list. The list of requirements may vary. Don’t panic, make sure to check each item on the list and you will be good to go.

Once you check every item from the list, click on “Next”. Now, wait for the installer to resolve the requirements.

It might happen that once you click the “Next” button, you'll get a message like in the image below;

It is always okay to click "Yes". You can install those extensions later on from the Installer.

Afterwards, click on “Next” to proceed to the next step. In the next step, you will install a list of missing MySQL products from your computer.

Step 3 - Download;

As mentioned above, we will install a bunch of MySQL products. But, we have to download them first. No worries, the Installer will download all the necessary products on your computer. All you have to do is to click on “Execute” and let the installer download all the products.

Wait a few minutes and let the installer download all the listed products. Feel free to take a healthy snack whilst your download is in progress.

After all the products have been downloaded, you will once again click the “Execute” button. But this time, the installer will install the downloaded products.

Done? All right then, in the following steps, we will be working on the actual MySQL configuration.

Can’t wait huh?

Great, click on "Next" to proceed. That button will show if the installer finished installing the products.

Step 4 - Product Configuration;

When using MySQL as your database client you have to do some configurations. Unlike SQLite, Strapi’s default server-less database, MySQL has a client to server architecture. This client to server architecture interacts over a network. So, to set up the server part of MySQL’s architecture, you need to do Product Configuration.

How cool is that?

Under “Product Configuration”, there are products listed. These are the products in need of configuration (although some are not mandatory).

According to MySQL, you have to configure three products:

• MySQL Server - Grants the Strapi application access to a database management system. The system then handles all queries and connections.
• MySQL Router - A lightweight middleware. This middleware handles routing between our Strapi application and MySQL Server.
• Samples and Examples - Contains sample databases as examples.

All right then, first, you are going to configure the product, MySQL Server.

Click the “Next” button and proceed to set up MySQL’s Server Configuration Type and Connectivity.

Step 5 - Type and Networking;

In this step, you are going to set up MySQL Server Configuration Type. Since your machine is for personal application development. From the highlighted dropdown menu, choose the option: “Development Computer”. This specific configuration defines and allocates resources to your MySQL server instance suitable for development purposes.

Under Connectivity, leave everything as it is, as shown in the image below;

If you are done, click “Next” and proceed to set up the MySQL server Authentication Method.

Step 6 - Authentication Method;

In this step, you have to choose the authentication method for your MySQL Server. You can let it be set as default, but you'll get an error when you try to create a Strapi app. This is a result of using the default authentication plugin which comes with MySQL 8. For now, it is not yet supported by Strapi.

Choose "Use Legacy Authentication Method" to avoid this error.

Note; You can also go with the (RECOMMENDED) method. But you have to follow extra steps to change the plugin. The procedure to change the plugin is down below, right after MySQL installation processes. This is so that you won't run into errors while creating a Strapi app.

Once you have selected an Authentication Method of choice, click on “Next” to proceed. In the following step, you will create user Accounts and Roles.

Step 7 - Accounts and Roles;

One of the many reasons why MySQL is the best database client is the ability to manage many users. Each user will have various levels of privileges.

In this step, you will create such user(s) and assign privileges to them.

MySQL provides you with a default user account, a superuser, which goes by the name Root Account or root for short. On the“Accounts and Roles” tab, fill both of the boxes with the password for the root user. Please pick up a specific password for this demo to remember it. Something like admin or root would work out fine.

The Root Account is very special. This is because when it comes to your MySQL database, the root has all the privileges to access and modify it. Root Account can also create or delete users from your user management list. This means not everyone should have the root password.

This means we have to create another account, with a different set of privileges. Go on to the next step to create another account.

Note; Make sure to change the password for the root user if you’re ready to deploy your application.

All right then.

Now create a new user account with admin privileges.

Click "Add User" to add a new user and for the username. Feel free to name it however you like except for the name"root", which is a reserved name. Also, for the password, you can pick the same one as the root user to make sure you won't forget it. Make sure to change it too when ready for production.

To add privileges, choose DB Admin from a dropdown list named "Role:”. DB Admin is a shorter name for Database Administrator. The user you created will have administrative privileges almost as similar to that of the root. But, admin privileges will not equate to that of the root.

Click on “OK” to confirm then “Next” to proceed.

Step 8 - Windows Service;

You can let everything be set as it is by default on this page;

This will add MySQL as a Windows service. Each time your machine starts, the MySQL Server starts as well.

Click “Next” to go to the following step. In that step, you are going to Apply Configuration settings to your MySQL Server.

Step 9 - Apply Configuration;

All right then, now in this step, apply all the configuration set during MySQL server configuration. To do that, simply click on “Execute”. All you have to do now is to wait until all the little circular icons turn to green checkmarks.

Done?

Awesome, upon successful configuration application, you will see a page similar to that below;

All you have to do now is to click on “Finish”.

All right then, once you click "Finish," head towards the next step. The Installer will prompt you to set some configurations for the Router. You can leave it blank and continue.

For the last step, check the connection to your Server. To do that, simply enter the credentials for the root user and execute the scripts on the next page.

Awesome, now head over to Create Strapi App step.

MySQL Installation on MAC;

MySQL installation on macOS is pretty easy and intuitive. To install MySQL Server on your Mac, run the dmg file that you have downloaded from the MySQL downloads page.

Step 1 - Introduction.

Once you run the MySQL Server dmg file you will see a page like that below;

There is nothing to worry about here. This is an Introductory page, don’t hesitate to click “Continue”.

Step 2 - License Agreement.

Under the Licence tab, the MySQL installer will present you with a Software Licence Agreement. It is up to you to print, save or read it. Click on “Continue” to proceed to the next step where you should accept Licence terms.

Step 3 - Accept Software Licence terms.

Now that you have read the software licence in the previous step, it is time to accept those terms. For you to be able to use MySQL you should agree with the terms, no negotiation. It is as simple as clicking the “Agree” button.

Once you agree with the licence terms, your MAC will ask for your installation confirmation. Head over to the next step to confirm.

Step 4 - Confirm Installation.

So now your MAC is asking you if you are sure that you want to install MySQL. Click on “Use Password” if you have one to confirm the installation.

Step 5 - Finishing up

Congrats!

Your MySQL installation was a success! Simple huh?

Yeah, I know but now that you have MySQL on your MAC, how do you start the Server instance anyway? Or set up the Root Account password for that matter.

Well, I got you, head over to the next step and I will show you how.

Step 7 - Locate MySQL.

To start MySQL Server instance you have to know the location of MySQL, to begin with. Open systems preferences, type MySQL on the search box. You will see MySQL from the results. Click on the MySQL icon to open it.

Step 8 - Start MySQL Server Instance.

Once you click on MySQL you will see an interface like below. To start MySQL Server Instance click on “Start MySQL Server” and the red icons on the right will turn green. This is a sign of a Server Active Instance. What if you want MySQL Server to start each time you start your PC? Well, make sure you click on the checkbox “Start MySQL when your computer starts up”.

You can also start MySQL Server using the terminal. To do that open up your terminal and paste the command sudo mysql.server start. A Server instance will start right away. To stop the server use the command sudo mysql.server stop.

Configure MySQL in Mac Terminal;

MySQL is installed on your Mac, great. But that’s not enough you need to establish a root password. Make sure the MySQL Server instance is not running.

Open your terminal and paste the command;

sudo /usr/local/mysql/support-files/mysql.server start --skip-grant-tables

And hit enter. You will get a prompt to enter your PC password. The command above enables you to run MySQL Server with privilege bypass.

After your Server starts, paste the command;

/usr/local/mysql/bin/mysql

And press enter key again. The command will log you into MySQL CLI. In that CLI paste in the following commands respectively;

FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'Your_Root_Password';

Replace Your_Root_Password with an actual password. Done, now your Root Account is secure. You will use that password each time you login into your MySQL CLI as root.

Wonderful, proceed to Create MySQL Database Step.

Create MySQL Database;

To continue, let's first create the database that we will use in our Strapi app. Open up and login into your MySQL CLI, which you got from installing MySQL Server.

To open and login into MySQL CLI on;

• Windows; Search your PC for “MySQL Command Line Client”. Open the Command Line Interface from the search results, you’ll be prompted to enter a root password. Type in the root password you set during MySQL Server Configuration.

• Mac; Open up your terminal and type in the command mysql -u root -p. You will get a prompt to enter a password. That password is for the superuser, root. Enter the root password and hit enter. Now, you're signed into the MySQL CLI as root.

Okay then, in your MySQL CLI, paste in the following command to create a database, demodb;

CREATE DATABASE demodb;

We're almost done. To connect Strapi with MySQL, you have to make sure that the authentication plugin for our root user is mysql_native_password. Currently, Strapi doesn't support the auth plugin that comes with MySQL 8.0.x chaching_sha2_password.

Run this command into your MySQL CLI to see which authentication plugin our root user uses.

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

If your authentication plugin is mysql_native_password, jump to Installing Strapi App.

Otherwise, you might get something like this;

To change the authentication plugin for our root user, make sure to run the following commands into your MySQL CLI. They will change the auth plugin and reset your password to admin;

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin';

FLUSH PRIVILEGES;

If you don't want to do this for every user you have in your database, make sure to update the configuration file of the MySQL server. It is usually located at this path if you're using Windows C:\ProgramData\MySQL\MySQL Server 8.0, and it can be opened up with any text editor of your choice.

Let's see what auth plugin our root user uses now. Rerun the following command:

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

If the authentication plugin is now mysql_native_password, you're ready to continue installing Strapi.

Awesome!

Installing Strapi App;

Great, now that we have MySQL set up for our project, let's get started with Strapi. In your terminal, navigate to an empty folder you wish to store your project. Run the following command in your terminal:

Using Yarn; yarn create strapi-app <name_of_your_app>

Using NPM; npx create-strapi-app@latest <name_of_your_app>

After running this, it will ask you to choose the type of installation you would like to continue with. For this tutorial, we'll go with custom. Make sure everything is set up as in the image below and wait for your installation to get done;

After the installation is complete, navigate into your app's directory. Run the following command to start Strapi in development mode;

Using Yarn; yarn develop

Using NPM; npm run develop

If everything was set up correctly, you should get a prompt taking you to Strapi's login page.

Now let’s create our user and head towards our admin panel.

If you managed to complete all the steps in this tutorial correctly, you should be seeing this page. This is where all the magic and cool stuff happens;

Congratulations, you’ve managed to set up Strapi using a MySQL database.

Conclusion:

In this article, I have laid out to you how to install and configure MySQL for Strapi v4. You can now leverage this know-how to take your Strapi application to the next level. Take full advantage of MySQL’s high security, multiple user management and as well as high performance.

If you have any questions or suggestions, let me know in the discussion section.

Host on Strapi Cloud

The Strapi experience has always been about flexibility, customization, and open-source innovation. But we understand that managing infrastructure and scaling your application can sometimes be a daunting task, diverting your focus from what you do best: developing web experiences.

That's why we're excited to introduce Strapi Cloud, so you can leverage the same robust Strapi capabilities you love, but without the hassle of managing servers, worrying about uptime, or scaling infrastructure as your project grows. It will allow you to future-proof your apps with a CMS that meets the content management needs of all your stakeholders, no matter your use case, services or devices.

Strapi remains committed to open-source values, and self-hosting will always be a viable option. We believe in offering choices that align with your project's unique needs. Strapi Cloud is an additional resource for those who want to focus solely on development, leaving the infrastructure to us.

Aw are here to support you every step of the way, no matter which hosting option you choose. You can reach out to us and the community on our Discord if you have any questions!