# Migrating from MySQL to MariaDB Galera Cluster (Node-by-Node In-Place) {% hint style="warning" %} **Version Requirement** This guide provides instructions for migrating a **MySQL 8.0 Galera Cluster** to a **MariaDB Galera Cluster 11.4**. Ensure that your systems meet or exceed these version requirements before proceeding. Please refer to the [Prerequisites](#prerequisites) for detailed version information. The **End-of-Life (EOL)** date for continued maintenance and regular binary releases of MySQL Galera Cluster will be **September 30, 2026**. {% endhint %} This guide outlines the procedure for migrating a live MySQL Galera Cluster to a MariaDB Galera Cluster. It follows the process for migrating a live MySQL Galera Cluster to a MariaDB Galera Cluster by replacing the binaries on each node sequentially. This "In-Place" method maintains cluster availability during the migration, although the cluster capacity will be reduced while individual nodes are being processed. ## Prerequisites * **Source:** MySQL Galera Cluster 8.0.43-26.4.24 or later. * **Target:** MariaDB Community Server 11.4 (LTS) or later. * **Library:** MariaDB Galera library 26.4.24 or latest stable edition. * **Tools:** * `mysqldump` (installed with the server). * `mariadb-backup` (installed with MariaDB). * **Access:** Root access to the operating system and the database. {% hint style="danger" %} **Cluster Backup** This migration involves uninstalling software and wiping data directories on live nodes. **A full cluster backup (using Xtrabackup or Volume Snapshot) is mandatory before proceeding.** {% endhint %} ## Incompatibility Check & Data Preparation {% hint style="warning" %} **Perform these steps on the running MySQL Cluster BEFORE shutting down any nodes.** {% endhint %} ### Incompatibilities and Feature Differences MariaDB maintains high levels of compatibility with MySQL, but you still need to [check for incompatibilities and feature differences](/docs/release-notes/community-server/about/compatibility-and-differences/incompatibilities-and-feature-differences-between-mariadb-rolling-and-mysql.md) to select the most suitable MariaDB version compatible with your current MySQL server. While MariaDB and MySQL share a common history, they have diverged significantly. You must "clean" the schema and configurations to ensure data compatibility before the migration begins. #### 1. Authentication Protocol MySQL 8.0 defaults to `caching_sha256_password`. MariaDB does not support the `caching_sha256_password` authentication protocol in all released versions (which is cumbersome and not secure for in-house attacks, since clear-text passwords are available inside the server). **Support Status:** * Implemented via [MDEV-9804](https://www.google.com/search?q=https://jira.mariadb.org/browse/MDEV-9804) for version 12.1.1. * Available in the CS release based on [MDEV-37600](https://jira.mariadb.org/browse/MDEV-37600). * Already supported in 11.8 Enterprise Server via [MENT-2359](https://jira.mariadb.org/browse/MENT-2359). * Available in 11.4 Enterprise Server with the December 2025 release as a rebase of [MDEV-37600](https://jira.mariadb.org/browse/MDEV-37600). {% hint style="info" %} **For MySQL 8.4 Users** Ensure `mysql_native_password=ON` is set in your configuration, or you will receive the error: `Plugin 'mysql_native_password' is not loaded`. {% endhint %} You can check which MySQL users are using SHA-256 or caching\_sha256\_password by executing: ```sql SELECT user, plugin FROM mysql.user WHERE plugin LIKE "%sha%"; ``` You can change the user to use a protocol compatible with both MySQL and MariaDB with: {% code overflow="wrap" %} ```sql ALTER USER user_name IDENTIFIED WITH mysql_native_password BY 'new_password'; ``` {% endcode %} #### 2. JSON Column Conversion MySQL stores JSON as a native binary type. MariaDB stores JSON as an alias for `LONGTEXT` with constraint checks. Galera replication may break if you attempt to replicate binary JSON objects from MySQL to MariaDB. Check if you have tables that use the MySQL JSON type: ```sql SELECT table_schema, table_name FROM information_schema.COLUMNS WHERE data_type="JSON"; ``` You can convert the JSON column to text with: ```sql ALTER TABLE table_name MODIFY json_column LONGTEXT; ``` #### 3. Encryption and Compression Encryption and compression table options are very different in MySQL compared to MariaDB. Logical dump will naturally de-encrypt/de-compress table contents but syntax used for creating tables is not supported by MariaDB. For example: {% code overflow="wrap" %} ```sql CREATE TABLE `t1` ( `c1` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION='lz4' ``` {% endcode %} leads to: `ERROR 1911 (HY000) at line 1174: Unknown option 'COMPRESSION'` Therefore, encrypted/compressed tables need to be de-encrypted/de-compressed before starting the conversion, and then encrypted/compressed again afterwards using MariaDB syntax. **Detect encrypted tables:** {% code overflow="wrap" %} ```sql SELECT table_schema, table_name, create_options FROM information_schema.TABLES WHERE create_options LIKE "%ENCRYPT%"; ``` {% endcode %} **Detect compressed tables:** {% code overflow="wrap" %} ```sql SELECT table_schema, table_name, create_options FROM information_schema.TABLES WHERE create_options LIKE "%COMP%"; ``` {% endcode %} **Action:** Run `ALTER TABLE` to remove the conflicting **`ENCRYPTION`** and **`COMPRESSION`** attributes. They can be re-enabled using MariaDB syntax after the full migration is complete. #### 4. General Tablespaces MariaDB does not support MySQL general tablespaces. In MySQL, these are used to store tables in specific external files rather than the default data directory. During migration, this `TABLESPACE` argument is ignored. **Example Scenario:** In MySQL, a user creates a custom tablespace file at a specific path and assigns a table to it: ```sql -- MySQL: Creating a tablespace in a custom file location mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/home/jan/galeradb/node1/ts1.ibd' Engine=InnoDB; -- MySQL: Creating a table assigned to that tablespace mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1; ``` If you examine the table definition in MySQL, you will see the tablespace assignment: ```sql mysql> show create table t1; | t1 | CREATE TABLE `t1` ( `c1` int NOT NULL, PRIMARY KEY (`c1`) ) /*!50100 TABLESPACE `ts1` */ ENGINE=InnoDB ... | ``` **Migration Behavior:** When this table is migrated to MariaDB (via `mysqldump`), the `TABLESPACE 'ts1'` attribute is ignored. * **Result:** The table `t1` will be created as a standard file-per-table in the default MariaDB data directory (e.g., `/var/lib/mysql/dbname/t1.ibd`). * **Loss of Path:** It will **not** use the custom path `/home/jan/galeradb/node1/ts1.ibd`.sq {% hint style="warning" %} During cluster migration make sure that **no new tablespaces are created** and **no new tables created inside a general tablespaces**. {% endhint %} #### 5. XA Transactions Ensure no XA transactions are in a `PREPARED` state, as these cannot be migrated cleanly. ```sql XA RECOVER; -- Should return an empty set ``` ## Global Cluster Preparation {% hint style="info" %} **Perform these steps on the running MySQL Cluster.** {% endhint %} ### 1. Create the SST User (Crucial) The `mysql.user` system table structure differs between vendors and **will not migrate automatically**. You must create the SST user now so it exists in the data stream before the switch. ```sql -- Create user compatible with both engines CREATE USER 'sst_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'strong_password'; GRANT ALL PRIVILEGES ON *.* TO 'sst_user'@'localhost'; FLUSH PRIVILEGES; ``` ### 2. Configure InnoDB Settings Ensure these settings are active on the MySQL cluster. * **`innodb_file_per_table = 1`** * This is the default setting for MySQL and MariaDB. If this is not set, you must use `mysqldump` for the entire migration, as specific recommendations regarding tablespaces will not work. * **`innodb_flush_log_at_trx_commit = 1`** * InnoDB writes the log buffer to the log file and flushes it to disk after every transaction commit. This is the safest option, ensuring no data loss in a crash, though it comes at the cost of slower performance due to frequent disk I/O. ### 3. Galera Provider Options * **`gcache.size`**: Should be large enough to avoid SST (State Snapshot Transfer) when a node is temporarily out of the cluster. A larger cache increases the chance of a faster IST (Incremental State Transfer). * [See: Customizing gcache.size](https://galeracluster.com/documentation/html_docs_remove-wsrep-new-cluster/kb/customizing-gcache-size.html) ## Migrating the First Node ("The Bridge") This is the most complex step. This node will bridge the gap between the MySQL cluster and the new MariaDB environment. {% stepper %} {% step %} **Isolate and Shutdown** 1. **Remove from Load Balancer:** Ensure no application traffic is hitting this node. 2. **Clean Shutdown Prep:** 
SET GLOBAL innodb_fast_shutdown = 0;
3. **Stop the Service:** ```bash mysqladmin -u root -p shutdown ``` {% endstep %} {% step %} **Swap Binaries & Wipe Data** 1. **Uninstall MySQL:** Remove all MySQL server and client packages using your OS package manager (e.g., `apt remove`, `dnf remove`). 2. **Clean Data Directory:** One way is to move all files under `datadir` to a new directory. ```bash mv /var/lib/mysql /var/lib/mysql_backup_timestamp mkdir /var/lib/mysql chown mysql:mysql /var/lib/mysql ``` 3. **Install MariaDB:** Install `mariadb-server` and `mariadb-backup`. {% endstep %} {% step %} **Configure MariaDB (`my.cnf`)** Update the configuration file to work with both MariaDB and MySQL. **Verify Configuration:** Before starting the service, verify that your configuration file does not contain unsupported options (such as `regexp-time-limit`). ```bash mariadbd --help --verbose > /tmp/log 2>&1 ``` Check `/tmp/log` for any errors or warnings about unknown variables and adjust your configuration files if needed. **Required Settings:** You **must** add these specific settings for the first node: {% code overflow="wrap" %} ```ini [galera] # ... standard galera settings ... # 1. IGNORE PROTOCOL MISMATCH # Required to avoid application protocol mismatch messages during migration. wsrep_provider_options="gcs.check_appl_proto=0;gcache.size=2G" # 2. USE LOGICAL SST (First Node Only) # wsrep_sst_method should be set to mysqldump for the first MariaDB node # to join the MySQL cluster. wsrep_sst_method=mysqldump # 3. SST AUTHENTICATION # Should contain user and password and this user should exist on both # MySQL and MariaDB databases with sufficient access rights. wsrep_sst_auth=sst_user:strong_password ``` {% endcode %} {% endstep %} {% step %} **Start and Join** Start the MariaDB service: ```bash systemctl start mariadb ``` When initiated, the node connects to the MySQL cluster and automatically triggers a `mysqldump` on a designated MySQL donor node. It then imports the SQL dump stream, effectively reconstructing the database in MariaDB's native format. {% endstep %} {% step %} **Post-Join Upgrade** Once the node is `Synced`, run `mariadb-upgrade` to fix system tables. {% hint style="warning" %} Manually re-create the `sst_user` (and other system users) if `mariadb-upgrade` reports issues, as the `mysql.user` table structure is not fully compatible. {% endhint %} {% endstep %} {% endstepper %} ## Migrating Subsequent Nodes Once the first node (Node A) is successfully running MariaDB, you can migrate Node B, Node C, etc. {% stepper %} {% step %} **Shutdown and Replace** 1. **Set Fast Shutdown:** ```sql SET GLOBAL innodb_fast_shutdown = 0; ``` *Required because undo/redo log formats may change between versions.* 2. **Shutdown:** `mysqladmin -u root -p shutdown` 3. **Wipe & Install:** Uninstall MySQL, Wipe Datadir, Install MariaDB. {% endstep %} {% step %} **Configure MariaDB** The configuration differs slightly for subsequent nodes. We switch back to **Physical Backups** for speed. {% code overflow="wrap" %} ```sql [galera] # ... standard galera settings ... # 1. Point to the MariaDB Donor # After the first MariaDB node has successfully joined, this variable # should be set on all other nodes to the value of wsrep_node_name # on the first MariaDB node. wsrep_sst_donor=Name_of_First_MariaDB_Node # 2. Use Binary SST # For other joining nodes, mariabackup value should be used. wsrep_sst_method=mariabackup wsrep_sst_auth=sst_user:strong_password wsrep_provider_options="gcs.check_appl_proto=0" ``` {% endcode %} {% endstep %} {% step %} **Start and Join** Start the service. The node will perform a binary snapshot transfer (`mariabackup`) from the first MariaDB node. Repeat the above 3 steps for all remaining nodes. {% endstep %} {% endstepper %} ## Finalization Once all nodes are running MariaDB: 1. **Cleanup Config:** Remove `gcs.check_appl_proto=0` and `wsrep_sst_donor` from `my.cnf` on all nodes. Reset `wsrep_sst_method` to `mariabackup` on the first node. 2. **Security:** Run `mariadb-secure-installation` to secure the root account and remove anonymous users. 3. **Features:** Re-enable encryption or compression using MariaDB-supported syntax if required. ## Known Issues & Limitations #### 1. `mysql.user` Migration The `mysql.user` table structure is not fully compatible and cannot be migrated automatically. * **Impact:** The administrator must create necessary users for the MariaDB database manually. * **Workaround:** In testing, using `--skip-table=mysql.user` during dump/restore and manually creating the necessary SST user was required. * **Reference:** [MDEV-33486](https://jira.mariadb.org/browse/MDEV-33486) #### 2. `regexp-time-limit` Parameter MariaDB currently does not support the `regexp-time-limit` parameter. * **Impact:** If this variable exists in your configuration or scripts, the server may fail to start or throw errors. * **Reference:** [MySQL Sysvar: regexp\_time\_limit](https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_regexp_time_limit), [MDEV-37403](https://jira.mariadb.org/browse/MDEV-37403) #### 3. `wsrep_start_position` Mismatch MariaDB does not understand the `wsrep_start_position` format provided by MySQL due to GTID formatting differences. * **Example:** MySQL node will use a set `wsrep_start_position` like: `”60e63a7-734c-11f0-bd90-97669e82ccf4:2284/2281/11/0747b7c5-734c-11f0-a69b-00e04ca2f8fe”` * **Reference:** [MGL-57](https://jira.mariadb.org/browse/MGL-57) #### 4. Replication Direction Replication from MariaDB → MySQL is **not supported**. * **Impact:** During migration, there can be **no load** directed to the MariaDB nodes until the entire cluster is migrated. #### 5. Testing Scope Currently, migration from 8.0.x has been verified to work on a simple `sysbench` database workload. #### Required Code Changes (References) For developers or those compiling from source, the following changes were relevant to this migration path: * [codership-mariadb-server Pull Request #519](https://github.com/mariadb-corporation/codership-mariadb-server/pull/519) * [codership-mysql Pull Request #2062](https://github.com/mariadb-corporation/codership-mysql/pull/2062) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/migrating-to-mariadb/moving-from-mysql/migration-from-mysql-to-mariadb-cluster-node-by-node-in-place.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.