Migrating from MariaDB to MySQL

Owned by Anthony George
Last updated: Aug 14, 2024 by Zoe Baldwin
2 min read

Introduction

MariaDB is no longer a supported database. This article details how to migrate from MariaDB to MySQL. These instructions assume you are migrating from a version of PhixFlow to version 11 or higher.

Ensure you have taken and tested a complete backup of your PhixFlow system before attempting to migrate your database schema. If you have any questions please get in touch with our support team (see Support Home)

Summary

  1. Perform a Data Dump

  2. Load the Data Dump

  3. MySQL User Assignment

  4. Upgrading PhixFlow

Step 1 - Perform a Data Dump

  1. Open PowerShell as an administrator and run the following command, replacing 'root' with your root user, '127.0.0.1' with the MariaDB server IP address, and '3306' with the MariaDB server port:

mysqlsh root@127.0.0.1:3306

  1. Enter the password for the root user when prompted.

  2. Choose or create a directory for storing the dump.

  3. Run the following command using the chosen directory to create the dump:

util.dumpInstance("C:/Temp/dump_db83", {users: false})

  1. This command generates a data dump of all databases on the server.

  2. Navigate to the directory and remove any database files for databases that are not intended to be migrated, ensuring only the required databases remain. It should look something like this

image-20240229-164003.png

Step 2 - Load the Data Dump

  1. Update the MySQL configuration file, typically located at 'C:\ProgramData\MySQL\MySQL Server 8.0', to allow the local file to be loaded. Add the following line under '[mysqld]'

    local_infile=1

  2. Restart the server.

  3. Open PowerShell as an administrator.

  4. Run the following command, replacing 'root' with the root user, '127.0.0.1' with the MySQL server IP address, and '3307' with the MySQL server port:

  5. Enter the password for the root user.

  6. Locate the directory of the data dump.

  7. Run the following command using the chosen directory to load the dump:

Step 3 - MySQL User Assignment

For the new MySQL database ensure the necessary user assignments are configured, which will be used by the PhixFlow webapps to access the database.

Step 4 - Upgrade Phixflow

  1. MySQL should be used with PhixFlow version 11 onwards, therefore you will need to upgrade your instance of PhixFlow accordingly in order to access it.

  2. To do this follow the Upgrading PhixFlow instructions ensuring you follow all special installation instructions.