How to use MySQL 5.7 instead of MariaDB in Xampp for Windows

Unfortunately, there's a lot of people like me that loves to work with tools that are ready to use locally. Xampp is without a doubt one of those tools that help you out configuring an environment where you can work with Apache, MySQL and PHP in matter of minutes without so much hassle. This is someway counterproductive if you don't have any idea of what's happening on the background (that happened to me for at least a year).

Progressively, you will end up understanding how some components of the tool work. For example, i worked with MySQL a long time until i finally understood that the database manager in some servers wasn't exactly what i always though it was. MariaDB is Open Source whereas MySQL uses some proprietary code in its Enterprise Edition. Everyone in the open source will suggest you to work with MariaDB, as it's the real open source RDBMS database.

In some cases, you will want however to work locally at least with MySQL itself. In this tutorial, we will explain you how to use it

Before starting

I would recommend to do this only with a clean installation of XAMPP, otherwise you may run the risk of data corruption, however it's feasible to do recover from the data that you had using MariaDB. So, don't forget to:

Dump a .SQL backup of every database that you had on the database engine.

In this example, we will use xampp-windows-x64-7.4.8-0-VC15, so the PHPMyAdmin panel shows that we have MariaDB 10.4.13:

Having said that, let's get started with the tutorial!

1. Download MySQL Community Server

As first step, you will need to open the MySQL Community Server Downloads Page here and search for the latest version of MySQL 5.7, download the 32-Bit version (to proceed with the download you will probably need to create an oracle account):

The zip file size is around ~300MB, once you have it, proceed with the next step.

2. Create new mysql directory with the new data

In this example, our working directory of xampp will be c:/xampp, but yours will be probably different. Stop any running instance of Apache and MySQL in the xampp control panel and rename the old xampp/mysql folder in the xampp directory to xampp/mariadb.

Then create a new empty directory namely mysql and extract the content of the zip download in the first step inside the new folder:

Then, create a new directory inside this mysql folder namely data (c:/xampp/mysql/data).

3. Create new mysql configuration file and initialize

Now, proceed to create the configuration file in the bin directory of mysql (xampp/mysql/bin/my.ini) with the following content, note that you need to change the basedir and datadir according to the installation directory in your computer:

# c:/xampp/mysql/bin/my.ini
[mysqld]
# set basedir to your installation path
basedir=c:/xampp/mysql
# set datadir to the location of your data directory
datadir=c:/xampp/mysql/data

After creating the configuration file, you will need to initialize the mysql directory data. Open a new command prompt and switch to the bin directory of mysql:

cd c:\xampp\mysql\bin

Then initialize MySQL with the following command (it shouldn't generate any output):

mysqld --initialize-insecure

This should start an insecure instance of MySQL (empty root password) which is perfect for local development, exactly as it is with Xampp.

4. Accessing MySQL 5.7 from the CLI

Now, open the xampp control panel and start both services apache and MySQL:

Once the services are running, open a new command prompt and switch to the bin directory of mysql again:

cd c:\xampp\mysql\bin

Then run mysql from the cli with the following command:

mysql -u root -p

As we initialized an insecure instance of MySQL, you shouldn't need any password. You should now have access to the MySQL cli:

You can as well verify it in PHPMyAdmin, however you will find some errors in PHPMyAdmin that you will need to fix with the latest step.

5. Fix PHPMyAdmin

You will find the create the create_tables.sql file in the following directory C:\xampp\phpMyAdmin\sql:

All that you need to do is open PHPMyAdmin in the explorer and import the mentioned file, this should fix any related exception and you will have a fully functional MySQL 5.7 instance running locally on your Xampp instance: