How do I install and configure MySQL on Windows for Matomo

In this FAQ we will provide step-by-step instructions on how to Install MySQL on Windows for use with Matomo.

Install MySQL

It is recommended that you install MySQL on a dedicated server rather than installing MySQL on the same server that that you are running IIS on. The separation of the database server and Web server makes overall installation more secure and manageable and avoids resource contentions between the database and Web server processes.

  1. Download MySQL Community Server. We recommend downloading and using the Windows Installer.
    Note: The MySQL Installer is 32 bit, but the installer will install both 32 bit and 64 bit binaries.

  2. Start Windows Installer, or extract all the files from the archive, and then start Setup.exe.

  3. You can use a Typical Setup or customize the installation to suit your needs.
    Please see below for the detailed setup process including screenshots.

  4. Once the installation wizard is completed, it is recommended that you leave the Configure the MySQL Server now checkbox selected.

Select Server Only

Select Execute

Select Next

Select Standalone MySQL Server / Classic MySQL Replication then Next

Click Next

Unless you have specific requirements do not change the settings on this screen, click Next

Unless you have specific requirements do not change the settings on this screen, click Next

Create a strong and secure MySQL root password

Unless you have specific requirements do not change the settings on this screen, click Next

Click Execute

The installation will complete, on the following screens click Next, then click Finish

Secure MySQL

Then we run the MySQL secure installation tool to improve the default security settings:

Open Explorer and search for mysql_secure_installation.exe. Once you find the executable file, you can run this executable file.

When you run this executable file a window will pop up that will walk you through the process of securing your MySQL installation. We recommend to answer Yes to all the questions.

That’s it!

Create a Mysql database and user

Assuming your MySQL server is running, now we will create a new MySQL database for Matomo and a new MySQL user who can only access this database.

  1. Open the MySQL command prompt by clicking Start -> All Programs -> MySQL -> MySQL Server -> MySQL Command Line Client:

  2. Enter the password for the root account.

Once logged on to MySQL, use the following sequence of commands:

  1. Create a database for Matomo:

    $ mysql> CREATE DATABASE matomo_db_name_here;

  2. Create a user called matomo:

    $ mysql> CREATE USER 'matomo'@'localhost' IDENTIFIED BY 'my-strong-password-here';

  3. Grant this user matomo the permission to access your matomo_db_name_here database

    $ mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON matomo_db_name_here.* TO 'matomo'@'localhost';

In these instructions:

  • replace matomo_db_name_here with the name of your MySQL database dedicated to Matomo Analytics.

  • replace my-strong-password-here by a strong secure password.

  • replace ‘matomo’ by your chosen MySQL username (or simply use ‘matomo’).

Configure MySQL

Locate your MySQL configuration file called C:\ProgramData\MySQL\MySQL Server X.X\my.ini and set the following values:

max_allowed_packet = 256M

wait_timeout = 28800

Restart MySQL to apply these changes.

Best Practices for MySQL

  • Enable TCP/IP Networking — This is the default. Keep the TCP port that MySQL uses to listen at 3306. If the database will be running on a separate system from the Web server, select the Add firewall exception for this port check box.

  • Include ‘Bin’ Directory in Windows PATH — This makes the MySQL utilities available from the command prompt or from Windows PowerShell™.

  • Create an Anonymous Account — The default is to keep this disabled. Adding anonymous user support may create a security risk for the database; additionally, enabling anonymous users causes the GRANT statements used to set up database to be unreliable.

MySQL should now be successfully setup on your Windows server.

Next we’ll take you through setting up PHP for Windows: How do I install and configure PHP on Windows for Matomo?
If you already have PHP setup, you can continue with the next step of setting up IIS for Windows: How do I install and configure IIS on Windows for Matomo?
Once you have everything setup, don’t forget to setup a scheduled task for archiving reports in Matomo.