How to upgrade MySQL to MariaDB in XAMPP in 5 minutes on Windows

Here are the steps I used to to upgrade MySQL to MariaDB in XAMPP on Windows in about 5 minutes. After completing this process, MariaDB will look and work just like MySQL. You may even notice a performance increase in your website. No need to panic fellow developer, increased performance is perfectly normal and just one of the great benefits of MariaDB over MySQL.

⚠

IMPORTANT: Before you begin, always be sure to createa good backup. Do not move files instead of copying them in the instructions below, as the original files may be required in order to back out and restore the original MySQL environment, if it doesn't work out for you.

Open a command prompt.
Go to your xampp folder.
Enter the following command: mysql/bin/mysql --version
Take note of the version of MySQL you have installed. This is the version of MariaDB that you will need. You can now exit the command prompt as the rest of the instructions can be done through Windows Explorer.
Download the ZIP version of MariaDB making sure that the version matches as closely as possible to the version of MySQL you currently have installed.
Shutdown your XAMPP server if it is running.
Rename the xampp/mysql folder to mysql.old.
Unzip the contents of the MariaDB ZIP file into your XAMPP folder.
Rename the MariaDB folder, called something like mariadb-5.5.37-win32, to mysql.
Rename xampp/mysql/data to data.old.
Copy the xampp/mysql.old/data folder (not just the contents) to xampp/mysql/data.
Copy the xampp/mysql.old/backup folder to xampp/mysql/.
Copy the xampp/mysql.old/scripts folder to xampp/mysql/.
Copy mysql_uninstallservice.bat and mysql_installservice.bat from xampp/mysql.old/ into xampp/mysql/.
Copy xampp/mysql.old/bin/my.ini into xampp/mysql/bin.
Edit xampp/mysql/bin/my.ini using a text editor like Notepad. Find skip-federated and add a # in front (to the left) of it to comment out the line if it exists. Save and exit the editor. If you skip this step, MariaDB may not start and you may find an error message similar to the following in the MySQL error log: [ERROR] xampp\mysql\bin\mysqld.exe: unknown option '--skip-federated' [ERROR] Aborting
Start-up XAMPP. If you can't get MariaDB to start, you may need to use the --skip-grant-tables option. I didn't need to do this but am including this tip in case you need it.

Run xampp/mysql/bin/mysql_upgrade.exe. Once you do this, the database may no longer be internally compatible with the MySQL version of your server. If you skip this step, you may notice the following error message in the MySQL error log:

[ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2′,'MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323′,'MYSQL40′,'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NO_AUTO_CREATE_USER','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH'), found type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2′,'MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323′,'MYSQL40′,'ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_A
[ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

If you see log files like, ib_logfile0 and ib_logfile1, rename or move them to some other folder.
Shutdown and restart MariaDB (MySQL).
Test your website to make sure everything still works. Once you start making changes to it, you will no longer be able to rollback to the MySQL server.

MariaDB should now be ready to use. Because it is a drop in replacement for MySQL, you will be able to start and stop it from within the XAMPP Control Panel just like you always have in the past.

If anything goes wrong, and you followed these instructions correctly, you can easily restore the original MySQL by simply shutdown XAMPP and renaming xampp/mysql to xampp/mariadb and xampp/mysql.old to xampp/mysql to put everything back as it was before.

If you prefer to troubleshoot your issue, you may find more information on the problem by viewing the MySQL error log, either by clicking the Logs button for MySQL in the XAMPP Control Panel, or by opening the following file in a text editor: xampp\mysql\data\mysql_error.log

Reference: Mike's Draft Article Bin

How to upgrade MySQL to MariaDB in XAMPP in 5 minutes on Windows

Comments

More from this blog

GitHub Phantom Notifications (and How I Fixed It)

Redis: A Short Guide

My .gitignore for Node

Linux Users

Command Palette

Comments

More from this blog