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

Here are the steps I used to to upgrad 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 make a good backup. Do not do move instead of copy files 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.

  1. Open a command prompt.
  2. Go to your xampp folder.
  3. Enter the following command: mysql/bin/mysql --version
  4. 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.
  5. 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.
  6. Shutdown your XAMPP server if it is running.
  7. Rename the xampp/mysql folder to mysql.old.
  8. Unzip the contents of the MariaDB ZIP file into your XAMPP folder.
  9. Rename the MariaDB folder, called something like mariadb-5.5.37-win32, to mysql.
  10. Rename xampp/mysql/data to data.old.
  11. Copy the xampp/mysql.old/data folder (not just the contents) to xampp/mysql/data.
  12. Copy the xampp/mysql.old/backup folder to xampp/mysql/.
  13. Copy the xampp/mysql.old/scripts folder to xampp/mysql/.
  14. Copy mysql_uninstallservice.bat and mysql_installservice.bat from xampp/mysql.old/ into xampp/mysql/.
  15. Copy xampp/mysql.old/bin/my.ini into xampp/mysql/bin.
  16. 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
  17. 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.
  18. 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.
  19. Shutdown and restart MariaDB (MySQL).
  20. 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


Comments

How to upgrade MySQL to MariaDB in XAMPP in 5 minutes on Windows — 28 Comments

  1. thanks fpr your advise.
    all my applications are working with mariadb but xampp control doesnt show mysql(mariadb) running
    If I press “mysql start” again, another mysqld.exe ist startet

    any idea?

    thank you
    klaus

    • Lo sentimos, no tengo un video disponible.
      (translated from English to Spanish using Google Translate)

  2. Pingback: ????? MySQL ?? MariaDB ?? XAMPP | ???? ???

  3. Pingback: MariaDB anstatt MySQL - Delphi-PRAXiS

  4. Thanks! These steps are useful for upgrading MariaDB to a newer version. I have two questions about all these.
    a) If I am correct, XAMPP’s control panel is a 32-bits application. Is it necessary to upgrade MariaDB only with 32-bits versions (mariadb-10.x.xx-win32.zip) or can I use 64-bits zip packages (mariadb-10.x.xx-winx64.zip)?
    b) Is it possible to check the correct version of the current MariaDB (32 or 64 bits) from phpmyadmin’s control panel or from XAMPP’s control panel?

    • Hi John,

      1. You might be able to mix 64-bit version of MySQL/MariaDB with the 32-bit versions of PHP and Apache since they are linked though sockets. Alternatively, check out WampServer. They have 32-bit and 64-bit versions of their server available.
      2. There are several ways to identify the version of MySQL or MariaDB that is running. Here are a few:
        1. Execute the following SQL command from within phpMyAdmin or Adminer: select version();
        2. Execute the following command from the command line: mysql -e “select version();”
        3. Execute the following command from the command line (will tell you if 32 or 64 bit too): mysql –version

      Hope this helps!

      Michael

  5. I follow step by step but I have the error as below when running MySQL in xampp. please help me fix it, thanks advance
    c:\xampp\mysql\bin\mysqld.exe: unknown variable ‘innodb_additional_mem_pool_size=2M’

    • Hi Quan,

      Settings come and go with each new version of MySQL. “Unknown variable” type situations that come up like this can usually easily be resolved by just commenting out or deleting “unknown variable” lines, in this case the innodb_additional_mem_pool_size and innodb_use_sys_malloc lines from xampp/mysql/bin/my.ini.

      Hope this helps.

      Best regards,

      Michael

  6. I have done all steps. But I can’t start mysql. XAMPP shows

    7:04:44 PM [mysql] Error: MySQL shutdown unexpectedly.
    7:04:44 PM [mysql] This may be due to a blocked port, missing dependencies,
    7:04:44 PM [mysql] improper privileges, a crash, or a shutdown by another method.
    7:04:44 PM [mysql] Press the Logs button to view error logs and check
    7:04:44 PM [mysql] the Windows Event Viewer for more clues
    7:04:44 PM [mysql] If you need more help, copy and post this
    7:04:44 PM [mysql] entire log window on the forums

    • Hi Sitt,

      Thanks for taking the time to post your message. During the upgrade process, you should have:

      1. Exported just your databases.
      2. Stopped your Apache and MySQL servers in the XAMPP control panel.
      3. Renamed the existing mysql folder.
      4. Copied the files in place for the new version.
      5. Restarted the MySQL/MariaDB server. At this point, your server should be running. If it isn’t, then you have other problems.
      6. Import your databases back into MySQL/MariaDB.

      The errors you mention typically happen when when one of the two following conditions are met:

      1. You copied the XAMPP folder including the database while MariaDB or MySQL without first shutting down the server. You MUST shutdown your webserver but especially your database server before attempting to move it to another location or computer
      2. You did not export your MySQL database and re-import it after the upgrade. If you just upgrade the MySQL server without touching the database files (found in xampp/mysql/data/…), it’s not going to work. You must export and import only your databases, not all of the databases.

      If you followed the instructions and didn’t run any installer or upgrade application, it should work because the process described above is to actually recreating a new MySQL server and not really upgrade it even if the end result is the same.

      Assuming you did rename the old MySQL folder, you should be able to roll back everything by simply deleting the new mysql folder and renaming the old one back to mysql.

      Hope this helps.

      Best regards,

      Michael

      • How important is step #5? With the version matching?
        Because I’m getting the same error, and far as I can tell, that seems to be the only part where I’m not going 100% by the books

    • I have the same problem, updating to mariaDB version 10.3. All that you have to do is comment the line “innodb_additional_mem_pool_size = 2M” on xampp\mysql\my.ini

  7. Thank u very much…Its work finally..God bless you..
    @@@@Michael Milette@@@@ Your suggestion is also helped me..thanx dude..

  8. Hello, I followed instructions to upgrade MariaDB in XAMPP to the latest version and MySQL doesn’t even start after this. No error log, just hanging on “21:48:27 [mysql] Attempting to start MySQL app…”
    I also tried to use my_medium.ini provided within ZIP as my.ini, tried leaving data folder provided within ZIP (I don’t really need any backup, database may be completely purged, just wanted to upgrade the database), it’s only hanging and the proccess mysqld.exe doesn’t use any % CPU.

  9. I followed your instructions blindly down to the letter. It worked at first shot. There was that innodb crash, but your instructions saved the day again. Can’t thank you enough.

  10. —————————
    mysqld.exe – System Error
    —————————
    The code execution cannot proceed because MSVCR120.dll was not found. Reinstalling the program may fix this problem.
    —————————
    OK
    —————————

Leave a Reply to Sitt Naing Cancel reply

Your email address will not be published. Required fields are marked *