From MySQL to MariaDB Server - An Effective Migration

This presentation was delivered during the MariaDB Partnership Program in Milano, Italy.

In this presentation, we explore how to plan a migration from MySQL to MariaDB.

Transcript

1. From MySQL to MariaDB Server An Effective Migration Frédéric Descamps Community Advocate MariaDB Foundation MariaDB Day Milano - April 2026
2. Who am I ? https://about.me/lefred Copyright @ 2026 MariaDB Foundation. 2
3. • @lefred • @lefredbe.bsky.social • @lefred@fosstodon.org • MariaDB Community Advocate since 2026 • using MySQL since version 3.20 • devops believer • living in • https://lefred.be Frédéric Descamps Copyright @ 2026 MariaDB Foundation. 3
4. MariaDB Foundation https://mariadb.org Copyright @ 2026 MariaDB Foundation. 4
5. Copyright @ 2026 MariaDB Foundation. 5
6. Copyright @ 2026 MariaDB Foundation. 6
7. Copyright @ 2026 MariaDB Foundation. 7
8. Why Companies Support the MariaDB Foundation Companies support the Foundation to participate in how MariaDB-based systems are defined and adopted. Supporting the Foundation complements investment in MariaDB by: • strengthening the ecosystem in which MariaDB is used • improving how technologies are positioned within real architectures • increasing visibility at the point of infrastructure decision Support also ensures: • long-term openness and continuity of MariaDB Server • independent governance and freedom from lock-in Copyright @ 2026 MariaDB Foundation. 8
9. Our Sponsors Copyright @ 2026 MariaDB Foundation. 9
10. Visit the MariaDB Sever Ecosystem Hub Copyright @ 2026 MariaDB Foundation. 10
11. Why should I migrate? reasons, drivers, and mindset shifts Copyright @ 2026 MariaDB Foundation. 11
12. Why teams migrate from MySQL to MariaDB Server Business drivers • Lower licensing and vendor lock-in pressure • More flexibility for self-managed, hybrid, or cloud deployments • Open-source governance and community-driven ecosystem • Opportunity to modernize operations during the move Technical drivers • Strong support for SQL standards and protocol compatibility for many MySQL-based apps • Mature replication, backup, and HA options • MariaDB-specific features for teams that want to evolve beyond a strict MySQL footprint • No roadmap The important mindset shift Older releases of MariaDB were often treated as a near drop-in replacement. For modern estates, especially since MySQL 8.x, plan as an application-compatible migration, not a blind binary swap. Migration projects succeed when teams explicitly test SQL behavior, authentication, connectors, replication mode, and performance baselines before cutover. Copyright @ 2026 MariaDB Foundation. 12
13. What Oracle's restructuring means for your MySQL support, innovation, and long-term viability Oracle's focus is only OCI and HeatWave, not MySQL • OCI & HeatWave Innovations • Innovation releases without real or limited innovation Almost empty LTS • MySQL 8.4 (first major release in 6 years) • 3 major new features: ◦ automatic histogram updates ◦ tagged GTIDs ◦ dedicated FLUSH_PRIVILEGES Decline of Community Engagement • decrease of bug fixes • decrease of contribution processing • state of connectors Copyright @ 2026 MariaDB Foundation. 13
14. The height of MySQL... and its decline 1 Development (2009-2018) • MySQL as a Global Unit • "Make MySQL a better MySQL" • Four major releases, 5.5, 5.6, 5.7 and 8.0 2 Diverted & Disassembled (2019-2024) • Engineering diverted to OCI MySQL DBaaS • Engineering further diverted to HeatWave • Loss of key leadership, end of Global Business Unit • One LTS release (8.4) and some innovation releases very limited 3 A Foretold End (2025-2026) • Huge layoffs, including MySQL leadership, MySQL engineering teams • No more sales or marketing support for MySQL • Some development units are without engineers • One last attempt before another round of mass layoffs Copyright @ 2026 MariaDB Foundation. 14
15. The height of MySQL... and its decline Copyright @ 2026 MariaDB Foundation. 15
16. ~ 3 years of Innovation Releases Release Release Date Major Feature(s) MySQL 8.1 2023-07-18 Capturing EXPLAIN FORMAT=JSON output MySQL 8.2 2023-10-25 MySQL 8.3 2024-01-16 MySQL 9.0 2024-07-23 - VECTOR datatype (no distance) - JavaScript Stored Procedure MySQL 9.1 2024-10-15 OpenTelemetry MySQL 9.2 2025-01-21 Group Replication enhancements MySQL 9.3 2025-04-15 MySQL 9.4 2025-07-22 - JSON duality views - Updateable JSON duality views MySQL 9.5 2025-10-21 MySQL 9.6 2026-01-20 container_aware flag Enterprise Edition only feature Copyright @ 2026 MariaDB Foundation. 16
17. One last attempt! Desperate attempt to save MySQL by trying to revert many bad decisions but creating more confusion and uncertainty in the process. Oracle, tried to re-engage with the Community, but the message is redundant, unclear and not credible, especially after the layoffs and the lack of real innovation in the last 3 years. To save time, many Enterprise Edition dedicated features will be open-sourced within the next LTS release, but that will not save MySQL from its decline, as the core issues are not only about features but about the lack of a clear roadmap, the lack of innovation, and the lack of support for the Community. The willing of the remaining team is there... but they will lack the resources and a roadmap! Copyright @ 2026 MariaDB Foundation. 17
18. Copyright @ 2026 MariaDB Foundation. 18
19. Copyright @ 2026 MariaDB Foundation. 18
20. How should I migrate? Assessment, planning, and execution best practices Copyright @ 2026 MariaDB Foundation. 19
21. Migration is not a binary swap With MySQL 8.x and 9.x, the gap between MySQL and MariaDB Server has widened, especially in terms of SQL behavior, authentication, and replication modes. Key differences to consider • SQL dialect differences (functions, JSON handling, optimizer behavior) • Authentication and user management changes • Replication incompatibilities (GTIDs, replication modes) InnoDB is no longer fully compatible • MariaDB uses a forked version of InnoDB evolving separately • MySQL introduces changes that break compatibility (e.g., redo log format, undo logs) • and MariaDB did other changes also breaking that compatibility Copyright @ 2026 MariaDB Foundation. 20
22. Migration Checklist 1 Inventory identify MySQL versions, features used such as storage engines, size, routines, events, users, plugins, topology and application dependencies 2 Assess Compatibility check authentication plugins, SQL modes, reserved keywords, JSON functions, GTIDs 3 Baseline establish performance baselines, identify critical queries, and measure current system metrics set the expectations 4 Test Migration perform test migrations, validate data integrity, and test application compatibility Iterate and refine 5 Plan Cutover develop a detailed cutover plan, including rollback procedures, and communication plan Copyright @ 2026 MariaDB Foundation. 21
23. Migration Checklist 1 Inventory identify MySQL versions, features used such as storage engines, size, routines, events, users, plugins, topology and application dependencies 2 Assess Compatibility check authentication plugins, SQL modes, reserved keywords, JSON functions, GTIDs 3 Baseline establish performance baselines, identify critical queries, and measure current system metrics set the expectations 4 Test Migration perform test migrations, validate data integrity, and test application compatibility Iterate and refine 5 Plan Cutover develop a detailed cutover plan, including rollback procedures, and communication plan Do not start with data movement. Start with evidence: exact information, incompatibilities, and performance baselines. This will guide your migration strategy and help set realistic expectations, and a tested rollback path. Copyright @ 2026 MariaDB Foundation. 21
24. Migration Checklist 1 Inventory identify MySQL versions, features used such as storage engines, size, routines, events, users, plugins, topology and application dependencies 2 Assess Compatibility check authentication plugins, SQL modes, reserved keywords, JSON functions, GTIDs 3 Baseline establish performance baselines, identify critical queries, and measure current system metrics set the expectations 4 Test Migration perform test migrations, validate data integrity, and test application compatibility Iterate and refine 5 Plan Cutover develop a detailed cutover plan, including rollback procedures, and communication plan Do not start with data movement. Start with evidence: exact information, incompatibilities, and performance baselines. This will guide your migration strategy and help set realistic expectations, and a tested rollback path. No matter what pain arises, it would be much worse during a migration to PostgreSQL Copyright @ 2026 MariaDB Foundation. 21
25. Inventory Identify MySQL versions, features, size, routines, events, users, plugins, ... Copyright @ 2026 MariaDB Foundation. 22
26. Get the inventory right Inventory should include: • MySQL versions in use across environments • Storage engines used (InnoDB, MyISAM, etc.) • Database size and growth patterns • Routines, events, and triggers in use • User accounts and authentication plugins • Replication topology and GTID usage • Application dependencies and compatibility requirements Copyright @ 2026 MariaDB Foundation. 23
27. MySQL Version The migration will be illustrated with the database of lefred.be mysql> show global variables like 'version%'; Copyright @ 2026 MariaDB Foundation. 24
28. MySQL Version The migration will be illustrated with the database of lefred.be mysql> show global variables like 'version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | version | 8.4.8 | | version_comment | MySQL Community Server - GPL | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.3.1 | +-------------------------+------------------------------+ Copyright @ 2026 MariaDB Foundation. 24
29. Used Engines MySQL > SELECT count(*) as '# TABLES', sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) INDEXES, sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE', engine `ENGINE` FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') GROUP BY engine; Copyright @ 2026 MariaDB Foundation. 25
30. Used Engines MySQL > SELECT count(*) as '# TABLES', sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) INDEXES, sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE', engine `ENGINE` FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') GROUP BY engine; +----------+------------+------------+------------+--------+ | # TABLES | DATA | INDEXES | TOTAL SIZE | ENGINE | +----------+------------+------------+------------+--------+ | 321 | 557.09 MiB | 177.14 MiB | 734.23 MiB | InnoDB | | 4 | 816 bytes | 8.00 KiB | 8.80 KiB | MyISAM | +----------+------------+------------+------------+--------+ Copyright @ 2026 MariaDB Foundation. 25
31. Dataset Size We first check on the filesystem by looking the size of the data directory: du du -sh -sh /var/lib/mysql /var/lib/mysql [ [root@mucha ~ root@mucha ~] ]# du -sh database/ # du -sh database/ 148G database/ 148G database/ Copyright @ 2026 MariaDB Foundation. 26
32. Dataset Size (2) Get the size of the dataset by running the following query: MySQL > SELECT sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) INDEXES, sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE' FROM information_schema.TABLES ORDER BY data_length + index_length; Copyright @ 2026 MariaDB Foundation. 27
33. Dataset Size (2) Get the size of the dataset by running the following query: MySQL > SELECT sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) INDEXES, sys.format_bytes(sum(data_length + index_length)) 'TOTAL SIZE' FROM information_schema.TABLES ORDER BY data_length + index_length; +------------+------------+------------+ | DATA | INDEXES | TOTAL SIZE | +------------+------------+------------+ | 560.89 MiB | 177.49 MiB | 738.38 MiB | +------------+------------+------------+ Copyright @ 2026 MariaDB Foundation. 27
34. Schemas Size MySQL > SELECT TABLE_SCHEMA, sum(table_rows) `ROWS`, sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) IDX, sys.format_bytes(sum(data_length) + sum(index_length)) 'TOTAL SIZE', round(sum(index_length) / sum(data_length),2) IDXFRAC FROM information_schema.TABLES GROUP By table_schema ORDER BY sum(DATA_length) DESC; Copyright @ 2026 MariaDB Foundation. 28
35. Schemas Size MySQL > SELECT TABLE_SCHEMA, sum(table_rows) `ROWS`, sys.format_bytes(sum(data_length)) DATA, sys.format_bytes(sum(index_length)) IDX, sys.format_bytes(sum(data_length) + sum(index_length)) 'TOTAL SIZE', round(sum(index_length) / sum(data_length),2) IDXFRAC FROM information_schema.TABLES GROUP By table_schema ORDER BY sum(DATA_length) DESC; +--------------------+---------+------------+------------+------------+---------+ | TABLE_SCHEMA | ROWS | DATA | IDX | TOTAL SIZE | IDXFRAC | +--------------------+---------+------------+------------+------------+---------+ | wp_lefred | 79390 | 145.78 MiB | 11.14 MiB | 156.92 MiB | 0.08 | ... | performance_schema | 3151387 | 0 bytes | 0 bytes | 0 bytes | NULL | | information_schema | 0 | 0 bytes | 0 bytes | 0 bytes | NULL | +--------------------+---------+------------+------------+------------+---------+ Copyright @ 2026 MariaDB Foundation. 28
36. Tables Size MySQL > SELECT CONCAT(table_schema, '.', table_name) as 'TABLE', ENGINE, CONCAT(ROUND(table_rows / 1000000, 2), 'M') `ROWS`, sys.format_bytes(data_length) DATA, sys.format_bytes(index_length) IDX, sys.format_bytes(data_length + index_length) 'TOTAL SIZE', round(index_length / data_length,2) IDXFRAC FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; Copyright @ 2026 MariaDB Foundation. 29
37. Tables Size MySQL > SELECT CONCAT(table_schema, '.', table_name) as 'TABLE', ENGINE, CONCAT(ROUND(table_rows / 1000000, 2), 'M') `ROWS`, sys.format_bytes(data_length) DATA, sys.format_bytes(index_length) IDX, sys.format_bytes(data_length + index_length) 'TOTAL SIZE', round(index_length / data_length,2) IDXFRAC FROM information_schema.TABLES ORDER BY data_length + index_length DESC LIMIT 10; +----------------------------+--------+-------+------------+------------+------------+---------+ | TABLE | ENGINE | ROWS | DATA | IDX | TOTAL SIZE | IDXFRAC | +----------------------------+--------+-------+------------+------------+------------+---------+ | rss.ttrss_entries | InnoDB | 0.07M | 205.61 MiB | 17.30 MiB | 222.91 MiB | 0.08 | | wp_lefred.wp_posts | InnoDB | 0.01M | 89.42 MiB | 1.17 MiB | 90.59 MiB | 0.01 | | wp_lefred.wp_options | InnoDB | 0.00M | 22.47 MiB | 112.00 KiB | 22.58 MiB | 0.00 | | wp_lefred.wp_commentmeta | InnoDB | 0.04M | 14.48 MiB | 3.28 MiB | 17.77 MiB | 0.23 | | rss.ttrss_user_entries | InnoDB | 0.08M | 7.52 MiB | 8.44 MiB | 15.95 MiB | 1.12 | ... +----------------------------+--------+-------+------------+------------+------------+---------+ Copyright @ 2026 MariaDB Foundation. 29
38. Routines, events, triggers Routines MySQL > SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY ROUTINE_SCHEMA, ROUTINE_TYPE, ROUTINE_NAME; Copyright @ 2026 MariaDB Foundation. 30
39. Routines, events, triggers Routines MySQL > SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY ROUTINE_SCHEMA, ROUTINE_TYPE, ROUTINE_NAME; Empty set (0.04 sec) Copyright @ 2026 MariaDB Foundation. 30
40. Routines, events, triggers (2) Triggers MySQL > SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME; Copyright @ 2026 MariaDB Foundation. 31
41. Routines, events, triggers (2) Triggers MySQL > SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_OBJECT_TABLE, ACTION_TIMING, EVENT_MANIPULATION FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY TRIGGER_SCHEMA, EVENT_OBJECT_TABLE, TRIGGER_NAME; Empty set (0.01 sec) Copyright @ 2026 MariaDB Foundation. 31
42. Routines, events, triggers (3) Events MySQL > SELECT EVENT_SCHEMA, EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY EVENT_SCHEMA, EVENT_NAME; Copyright @ 2026 MariaDB Foundation. 32
43. Routines, events, triggers (3) Events MySQL > SELECT EVENT_SCHEMA, EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY EVENT_SCHEMA, EVENT_NAME; Empty set (0.00 sec) Copyright @ 2026 MariaDB Foundation. 32
44. Routines, events, triggers (4) SELECT 'PROCEDURE/FUNCTION' AS object_kind, ROUTINE_SCHEMA AS db_name, ROUTINE_NAME AS object_name, ROUTINE_TYPE AS extra FROM INFORMATION_SCHEMA.ROUTINES UNION ALL SELECT 'TRIGGER', TRIGGER_SCHEMA, TRIGGER_NAME, CONCAT(ACTION_TIMING, ' ', EVENT_MANIPULATION, ' ON ', EVENT_OBJECT_TABLE) FROM INFORMATION_SCHEMA.TRIGGERS UNION ALL SELECT 'EVENT', EVENT_SCHEMA, EVENT_NAME, STATUS FROM INFORMATION_SCHEMA.EVENTS ORDER BY db_name, object_kind, object_name; summary all at once Copyright @ 2026 MariaDB Foundation. 33
45. Plugins MySQL > select plugin_name, plugin_version ver, plugin_type from information_schema.plugins where plugin_status = "ACTIVE" and plugin_type != 'INFORMATION SCHEMA'; Copyright @ 2026 MariaDB Foundation. 34
46. Plugins MySQL > select plugin_name, plugin_version ver, plugin_type from information_schema.plugins where plugin_status = "ACTIVE" and plugin_type != 'INFORMATION SCHEMA'; +-----------------------------+-----+----------------+ | plugin_name | ver | plugin_type | +-----------------------------+-----+----------------+ | binlog | 1.0 | STORAGE ENGINE | | sha256_password | 1.1 | AUTHENTICATION | | caching_sha2_password | 1.0 | AUTHENTICATION | | sha2_cache_cleaner | 1.0 | AUDIT | | daemon_keyring_proxy_plugin | 1.0 | DAEMON | | CSV | 1.0 | STORAGE ENGINE | | MEMORY | 1.0 | STORAGE ENGINE | | InnoDB | 8.4 | STORAGE ENGINE | | MyISAM | 1.0 | STORAGE ENGINE | | MRG_MYISAM | 1.0 | STORAGE ENGINE | | PERFORMANCE_SCHEMA | 0.1 | STORAGE ENGINE | | TempTable | 1.0 | STORAGE ENGINE | | ARCHIVE | 3.0 | STORAGE ENGINE | | BLACKHOLE | 1.0 | STORAGE ENGINE | | ngram | 0.1 | FTPARSER | | mysqlx_cache_cleaner | 1.0 | AUDIT | | mysqlx | 1.0 | DAEMON | | mysql_native_password | 1.1 | AUTHENTICATION | +-----------------------------+-----+----------------+ Copyright @ 2026 MariaDB Foundation. 34
47. Components MySQL > select * from mysql.component; Copyright @ 2026 MariaDB Foundation. 35
48. Components MySQL > select * from mysql.component; +--------------+--------------------+------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+------------------------------------+ | 1 | 1 | file://component_validate_password | +--------------+--------------------+------------------------------------+ 1 row in set (0.09 sec) Copyright @ 2026 MariaDB Foundation. 35
49. Users and authentication plugins Which authentication plugins are in use and by how many users? MySQL > select plugin, count(*) users from mysql.user group by plugin; Copyright @ 2026 MariaDB Foundation. 36
50. Users and authentication plugins Which authentication plugins are in use and by how many users? MySQL > select plugin, count(*) users from mysql.user group by plugin; +-----------------------+-------+ | plugin | users | +-----------------------+-------+ | mysql_native_password | 14 | | caching_sha2_password | 9 | +-----------------------+-------+ 2 rows in set (0.00 sec) Copyright @ 2026 MariaDB Foundation. 36
51. Users and authentication plugins (2) Unused accounts SELECT DISTINCT m_u.user, m_u.host FROM mysql.user m_u LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER' LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER' LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host) LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host) WHERE ps_a.user IS NULL AND is_v.definer IS NULL AND is_r.definer IS NULL AND is_e.definer IS NULL AND is_t.definer IS NULL ORDER BY m_u.user, m_u.host; Copyright @ 2026 MariaDB Foundation. 37
52. Users and authentication plugins (2) Unused accounts SELECT DISTINCT m_u.user, m_u.host FROM mysql.user m_u LEFT JOIN performance_schema.accounts ps_a ON m_u.user = ps_a.user AND ps_a.host = m_u.host LEFT JOIN information_schema.views is_v ON is_v.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_v.security_type = 'DEFINER' LEFT JOIN information_schema.routines is_r ON is_r.DEFINER = CONCAT(m_u.User, '@', m_u.Host) AND is_r.security_type = 'DEFINER' LEFT JOIN information_schema.events is_e ON is_e.definer = CONCAT(m_u.user, '@', m_u.host) LEFT JOIN information_schema.triggers is_t ON is_t.definer = CONCAT(m_u.user, '@', m_u.host) WHERE ps_a.user IS NULL AND is_v.definer IS NULL AND is_r.definer IS NULL AND is_e.definer IS NULL AND is_t.definer IS NULL ORDER BY m_u.user, m_u.host; Copyright @ 2026 MariaDB Foundation. +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | wp_lefred | 127.0.0.1 | +------------------+-----------+ 37
53. Roles If you are using ROLES, active-role semantics, default-role semantics, special-role/ global-role features, and system tables are different enough that some role scripts are not automatically portable between MySQL and MariaDB. Listing the roles: MySQL > SELECT distinct DISTINCT User 'Role Name', if(from_user is NULL,0, 1) Active FROM mysql.user LEFT JOIN role_edges ON from_user=user WHERE account_locked='Y' AND password_expired='Y' AND authentication_string=''; +-----------+--------+ | Role Name | Active | +-----------+--------+ | wp_reader | 0 | +-----------+--------+ 1 row in set (0.0023 sec) Copyright @ 2026 MariaDB Foundation. 38
54. Replication GTID modes MySQL > show global variables like 'gtid_mode'; Copyright @ 2026 MariaDB Foundation. 39
55. Replication GTID modes MySQL > show global variables like 'gtid_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | OFF | +---------------+-------+ Copyright @ 2026 MariaDB Foundation. 39
56. Replication GTID modes MySQL > show global variables like 'gtid_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | OFF | +---------------+-------+ MySQL > show global variables like 'gtid_mode'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | gtid_mode | ON | +---------------+-------+ MySQL > show global variables like 'enforce_gtid_consistency'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | enforce_gtid_consistency | ON | +--------------------------+-------+ Copyright @ 2026 MariaDB Foundation. 39
57. Replication Topology You can use pt-replica-find to quickly get an inventory of your replication topology: # ./pt-replica-find -u clusteradmin -h mysql1 --password "Passw0rd!" mysql1 Version 8.4.8 Server ID 410232619 Uptime 45:29 (started 2026-04-20T14:28:26) Replication Is not a replica, has 2 replicas connected, is not read_only Filters Binary logging ROW Slave status Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 8.4.8 +- mysql3 Version 8.4.3 Server ID 3078509641 Uptime 34:39 (started 2026-04-20T14:39:16) Replication Is a replica, has 0 replicas connected, is read_only Filters Binary logging ROW Slave status 0 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 8.4.3 +- mysql2 Version 8.4.0 Server ID 3957045113 Uptime 42:08 (started 2026-04-20T14:31:47) Replication Is a replica, has 0 replicas connected, is read_only Filters Binary logging ROW Slave status 0 seconds behind, running, no errors Slave mode STRICT Auto-increment increment 1, offset 1 InnoDB version 8.4.0 Copyright @ 2026 MariaDB Foundation. 40
58. Assess Compatibility check authentication plugins, SQL modes, reserved keywords, JSON functions, GTIDs Copyright @ 2026 MariaDB Foundation. 41
59. SQL Mode The SQL_MODE is a string containing settings that can change how the database interprets SQL queries, handles data validation, and even enforces certain standards. MySQL > select @@sql_mode\G Copyright @ 2026 MariaDB Foundation. 42
60. SQL Mode The SQL_MODE is a string containing settings that can change how the database interprets SQL queries, handles data validation, and even enforces certain standards. MySQL > select @@sql_mode\G *************************** 1. row *************************** @@sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 1 row in set (0.0012 sec) Copyright @ 2026 MariaDB Foundation. 42
61. SQL Modes SQL mode MySQL 8.4 MariaDB 13.0 Difference TIME_TRUNCATE_FRACTIONAL Yes No MySQL-only DB2 No Yes MariaDB compatibility mode EMPTY_STRING_IS_NULL No Yes MariaDB-only IGNORE_BAD_TABLE_OPTIONS No Yes MariaDB-only MAXDB No Yes MariaDB compatibility mode MSSQL No Yes MariaDB compatibility mode MYSQL323 No Yes MariaDB legacy compatibility mode MYSQL40 No Yes MariaDB legacy compatibility mode NO_AUTO_CREATE_USER No Yes MariaDB-only NO_FIELD_OPTIONS No Yes MariaDB-only NO_KEY_OPTIONS No Yes MariaDB-only NO_TABLE_OPTIONS No Yes MariaDB-only Copyright @ 2026 MariaDB Foundation. 43
62. SQL modes (2) SQL mode MySQL 8.4 MariaDB 13.0 Difference ORACLE No Yes MariaDB compatibility mode POSTGRESQL No Yes MariaDB compatibility mode SIMULTANEOUS_ASSIGNMENT No Yes MariaDB-only TIME_ROUND_FRACTIONAL No Yes MariaDB-only ANSI Yes Yes Exists in both, but composition differs TRADITIONAL Yes Yes Exists in both, but composition differs Copyright @ 2026 MariaDB Foundation. 44
63. SQL Modes - default Aspect MySQL 8.4 MariaDB 13.0 Documented default ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION Stricter GROUP BY by default Yes (`ONLY_FULL_GROUP_BY`) No Zero-date checks by default Yes (`NO_ZERO_IN_DATE`, `NO_ZERO_DATE`) No Strict mode enabled by default Yes (`STRICT_TRANS_TABLES`) Yes (`STRICT_TRANS_TABLES`) Division by zero mode enabled by default Yes Yes Engine substitution disabled by default Yes (`NO_ENGINE_SUBSTITUTION`) Yes (`NO_ENGINE_SUBSTITUTION`) Includes `NO_AUTO_CREATE_USER` by default No (Not a mode in MySQL, it's the only behavior) Yes Copyright @ 2026 MariaDB Foundation. 45
64. SQL Modes - STRICT_TRANS_TABLES The STRICT_TRANS_TABLES mode is a critical aspect of MySQL's and MariaDB's behavior that affects how the database handles invalid or out-of-range data during data modification operations (INSERT, UPDATE, etc.). When this mode is enabled, the database will reject any operation that attempts to insert or update data that violates data type constraints, such as inserting a string into an integer column or inserting a value that exceeds the defined range for a column. Instead of silently truncating or adjusting the data, the operation will fail with an error, ensuring data integrity and preventing unintended consequences. Copyright @ 2026 MariaDB Foundation. 46
65. SQL Modes - STRICT_TRANS_TABLES (2) If you have data that has gone through multiple versions and updates, the definition of temporal fields can often be problematic. Copyright @ 2026 MariaDB Foundation. 47
66. SQL Modes - STRICT_TRANS_TABLES (2) If you have data that has gone through multiple versions and updates, the definition of temporal fields can often be problematic. mysql> SELECT table_schema, table_name, column_name, column_type, column_default FROM information_schema.columns WHERE column_default IN ('0000-00-00', '0000-00-00 00:00:00') AND table_schema NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY table_name, ordinal_position; Copyright @ 2026 MariaDB Foundation. 47
67. SQL Modes - STRICT_TRANS_TABLES (2) If you have data that has gone through multiple versions and updates, the definition of temporal fields can often be problematic. mysql> SELECT table_schema, table_name, column_name, column_type, column_default FROM information_schema.columns WHERE column_default IN ('0000-00-00', '0000-00-00 00:00:00') AND table_schema NOT IN ('sys','mysql', 'information_schema', 'performance_schema', 'mysql_innodb_cluster_metadata') ORDER BY table_name, ordinal_position; We do have 47 columns defined with such default! Copyright @ 2026 MariaDB Foundation. 47
68. Authentication plugins Authentication Plugin MySQL 8.4 MariaDB 13.0 Notes caching_sha2_password Yes (default) Yes (but needs to be enabled manually) MySQL default since 8.0 mysql_native_password Disabled by default (deprecated) Yes (default) Removed in MySQL 9.0 mysql_old_password No Yes pre MySQL 4.1 sha256_password Yes No MySQL-only, older alternative to caching_sha2_password auth_socket Yes Yes Unix socket auth; MariaDB commonly uses unix_socket unix_socket No Yes MariaDB equivalent naming for socket-based auth ed25519 No Yes MariaDB-only modern password authentication plugin pam No Yes MariaDB PAM authentication Copyright @ 2026 MariaDB Foundation. 48
69. Authentication plugins (2) Authentication Plugin MySQL 8.4 MariaDB 13.0 Notes gssapi No Yes MariaDB Kerberos/GSSAPI authentication ldap_simple No Yes MariaDB LDAP simple authentication ldap_sasl No Yes MariaDB LDAP SASL authentication dialog Yes Yes Interactive authentication plugin mysql_clear_password Yes Yes Sends password in cleartext; use with TLS authentication_kerberos Yes No MySQL Kerberos plugin authentication_ldap_sasl Yes No MySQL LDAP SASL plugin authentication_ldap_simple Yes No MySQL LDAP simple plugin authentication_windows Yes No MySQL Windows native authentication auth_pam_compat No Yes MariaDB PAM compatibility plugin Copyright @ 2026 MariaDB Foundation. 49
70. X Protocol MariaDB doesn't support the X Protocol. If your applications rely on MySQL's X Protocol for document store access or other features, you will need to modify them to work with standard SQL in MariaDB. You can verify if X Protocol is in use by checking the related satus variables Copyright @ 2026 MariaDB Foundation. 50
71. X Protocol (2) MySQL > SELECT MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_sessions_accepted' THEN VARIABLE_VALUE END) AS sessions_accepted, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_sessions_closed' THEN VARIABLE_VALUE END) AS sessions_closed, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_connections_accepted' THEN VARIABLE_VALUE END) AS conns_accepted, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_connections_closed' THEN VARIABLE_VALUE END) AS conns_closed FROM performance_schema.global_status WHERE VARIABLE_NAME IN ( 'Mysqlx_sessions_accepted', 'Mysqlx_sessions_closed', 'Mysqlx_connections_accepted', 'Mysqlx_connections_closed' ); Copyright @ 2026 MariaDB Foundation. 51
72. X Protocol (2) MySQL > SELECT MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_sessions_accepted' THEN VARIABLE_VALUE END) AS sessions_accepted, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_sessions_closed' THEN VARIABLE_VALUE END) AS sessions_closed, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_connections_accepted' THEN VARIABLE_VALUE END) AS conns_accepted, MAX(CASE WHEN VARIABLE_NAME = 'Mysqlx_connections_closed' THEN VARIABLE_VALUE END) AS conns_closed FROM performance_schema.global_status WHERE VARIABLE_NAME IN ( 'Mysqlx_sessions_accepted', 'Mysqlx_sessions_closed', 'Mysqlx_connections_accepted', 'Mysqlx_connections_closed' ); +-------------------+-----------------+----------------+--------------+ | sessions_accepted | sessions_closed | conns_accepted | conns_closed | +-------------------+-----------------+----------------+--------------+ | 1769 | 0 | 1783 | 1783 | +-------------------+-----------------+----------------+--------------+ 1 row in set (0.01 sec) Copyright @ 2026 MariaDB Foundation. 51
73. NoSQL Document Store - collections It's possible to check for the presence of MySQL's NoSQL document store collections: MySQL > SELECT c.TABLE_SCHEMA, c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_TYPE = 'BASE TABLE' AND c.COLUMN_NAME = 'doc' AND c.DATA_TYPE = 'json' AND c.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME; Copyright @ 2026 MariaDB Foundation. 52
74. NoSQL Document Store - collections It's possible to check for the presence of MySQL's NoSQL document store collections: MySQL > SELECT c.TABLE_SCHEMA, c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c JOIN INFORMATION_SCHEMA.TABLES AS t ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME WHERE t.TABLE_TYPE = 'BASE TABLE' AND c.COLUMN_NAME = 'doc' AND c.DATA_TYPE = 'json' AND c.TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ORDER BY c.TABLE_SCHEMA, c.TABLE_NAME; Copyright @ 2026 MariaDB Foundation. +--------------+------------+ | TABLE_SCHEMA | TABLE_NAME | +--------------+------------+ | library | books | | vinyl | records | +--------------+------------+ 2 rows in set (0.04 sec) 52
75. JSON Datatype Area MySQL 8.4 MariaDB 13.0 Why it matters JSON data type Native JSON type JSON is an alias for LONGTEXT COLLATE utf8mb4_bin Storage format, validation, replication, and migration behavior differ. Internal storage Stored in MySQL's native binary JSON format Stored as text MySQL optimizes JSON storage/ access differently; MariaDB keeps JSON as textual data. Validation on insert Invalid JSON in a JSON column raises an error Validation is enforced through the JSON alias / JSON_VALID check behavior Data-cleanliness rules look similar at SQL level, but implementation differs. Replication / migration Native JSON columns are normal MariaDB recommends converting MySQL JSON to TEXT/LONGTEXT, using statement-based replication, or using the mysql_json plugin during migration Direct cross-engine migration of JSON columns can break or need conversion. JSON_TABLE() Supported Supported Both can project JSON into relational rows. Copyright @ 2026 MariaDB Foundation. 53
76. JSON Datatype (2) Area MySQL 8.4 MariaDB 13.0 Why it matters Schema validation functions Supports JSON schema validation functions such as JSON_SCHEMA_VALID() Supported Schema-based JSON validation is similar. Normalization helpers No direct equivalent documented like MariaDB's JSON_NORMALIZE() Supports JSON_NORMALIZE() MariaDB has built-in canonicalization for equality/ unique-comparison workflows. Pretty / loose formatting helpers Supports JSON_PRETTY() only Supports JSON_LOOSE() and JSON_DETAILED() / JSON_PRETTY() MariaDB has more formatting- oriented helper functions. JSON_VALUE() Supported as part of MySQL's JSON function set Supported Scalar extraction exists in both, but surrounding JSON ecosystem differs. JSON_EXTRACT() Supported Supported Core path extraction is broadly compatible. Copyright @ 2026 MariaDB Foundation. 54
77. JSON Datatype (3) Area MySQL 8.4 MariaDB 13.0 Why it matters JSON_SET(), JSON_REPLACE(), JSON_SEARCH() Supported Supported Many day-to-day JSON manipulation functions overlap. Aggregate JSON functions JSON_ARRAYAGG(), JSON_OBJECTAGG() supported JSON_ARRAYAGG(), JSON_OBJECTAGG() supported Aggregation is available on both sides, though execution details may differ. Equality / canonical comparison No dedicated documented function like JSON_NORMALIZE() JSON_NORMALIZE() sorts keys and removes spaces MariaDB gives you a built-in path to compare semantically equivalent JSON documents. Compatibility direction Own native JSON implementation SQL-level compatibility with MySQL functions, but different storage model Queries may port more easily than storage or operational behavior. -> & ->> Supported Not supported yet These operators are used for JSON path extraction. Copyright @ 2026 MariaDB Foundation. 55
78. JSON Datatype (4) Finding all JSON columns in MySQL: MySQL > SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE DATA_TYPE = 'json' AND TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'mysql_innodb_cluster_metadata' ) ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION; Copyright @ 2026 MariaDB Foundation. 56
79. JSON Datatype (4) Finding all JSON columns in MySQL: MySQL > SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE DATA_TYPE = 'json' AND TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys', 'mysql_innodb_cluster_metadata' ) ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION; +--------------+-------------------+--------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | +--------------+-------------------+--------------+ | library | books | doc | | nextcloud | oc_files_versions | metadata | | nextcloud | oc_share | attributes | | vinyl | records | doc | | vinyl | records | _json_schema | +--------------+-------------------+--------------+ Copyright @ 2026 MariaDB Foundation. 56
80. Data Type Mapping MySQL MariaDB Compatibility TINYINT TINYINT 1:1 Compatible SMALLINT SMALLINT 1:1 Compatible MEDIUMINT MEDIUMINT 1:1 Compatible INT INT 1:1 Compatible BIGINT BIGINT 1:1 Compatible DECIMAL DECIMAL 1:1 Compatible NUMERIC NUMERIC 1:1 Compatible FLOAT FLOAT 1:1 Compatible DOUBLE DOUBLE 1:1 Compatible BIT BIT 1:1 Compatible BOOLEAN BOOLEAN (alias for TINYINT(1)) 1:1 Compatible (BOOLEAN is an alias in both) Copyright @ 2026 MariaDB Foundation. 57
81. Data Type Mapping (2) MySQL MariaDB Compatibility CHAR CHAR 1:1 Compatible VARCHAR VARCHAR 1:1 Compatible BINARY BINARY 1:1 Compatible VARBINARY VARBINARY 1:1 Compatible TINYBLOB TINYBLOB 1:1 Compatible BLOB BLOB 1:1 Compatible MEDIUMBLOB MEDIUMBLOB 1:1 Compatible LONGBLOB LONGBLOB 1:1 Compatible TINYTEXT TINYTEXT 1:1 Compatible TEXT TEXT 1:1 Compatible MEDIUMTEXT MEDIUMTEXT 1:1 Compatible LONGTEXT LONGTEXT 1:1 Compatible Copyright @ 2026 MariaDB Foundation. 58
82. Data Type Mapping (3) MySQL MariaDB Compatibility DATE DATE 1:1 Compatible DATETIME DATETIME 1:1 Compatible TIMESTAMP TIMESTAMP 1:1 Compatible TIME TIME 1:1 Compatible YEAR YEAR 1:1 Compatible ENUM ENUM 1:1 Compatible SET SET 1:1 Compatible GEOMETRY GEOMETRY 1:1 Compatible POINT POINT 1:1 Compatible LINESTRING LINESTRING 1:1 Compatible POLYGON POLYGON 1:1 Compatible Copyright @ 2026 MariaDB Foundation. 59
83. Reserved Keywords MySQL and MariaDB have different sets of reserved keywords. If your application uses any of these as identifiers (e.g., table names, column names) without quoting them, you may encounter syntax errors after migration. • MySQL counts 734 keywords • 259 keywords are reserved • MariaDB counts 699 keywords • it's not clear how many are reserved • it seems they are also 259 Copyright @ 2026 MariaDB Foundation. 60
84. Reserved Keywords (2) MySQL 8.4 MySQL > select count(*) from information_schema.keywords ; +----------+ | count(*) | +----------+ | 734 | +----------+ 1 row in set (0.00 sec) MySQL > select count(*) from information_schema.keywords where reserved; +----------+ | count(*) | +----------+ | 259 | +----------+ 1 row in set (0.01 sec) Copyright @ 2026 MariaDB Foundation. 61
85. Reserved Keywords (3) MariaDB 12.3.1 MariaDB > select count(*) from information_schema.keywords ; +----------+ | count(*) | +----------+ | 699 | +----------+ 1 row in set (0.048 sec) MariaDB doesn't include a reserved column in the keywords table. check https://mariadb.com/docs/server/reference/sql-structure/sql-language-structure/reserved-words Copyright @ 2026 MariaDB Foundation. 62
86. Keywords - only in MySQL - ACCESSIBLE - ACTIVE - ASSIGN_GTIDS_TO_ANONYMOUS_TRANSACTIONS - ATTRIBUTE - AUTHENTICATION - AUTOEXTEND_SIZE - BERNOULLI - BUCKETS - BULK - CHALLENGE_RESPONSE - CLONE - COLUMN_FORMAT - COMPONENT - COMPRESSION - CUME_DIST - DATAFILE - DEFAULT_AUTH - DEFINITION - DENSE_RANK - DESCRIPTION - ENCRYPTION - ENFORCED - ENGINE_ATTRIBUTE - FACTOR - FAILED_LOGIN_ATTEMPTS - FILE_BLOCK_SIZE - FILTER - FINISH - FIRST_VALUE - GENERATE - GEOMCOLLECTION - GEOMETRY - GEOMETRYCOLLECTION - GET_SOURCE_PUBLIC_KEY - GROUPING - GROUP_REPLICATION - GROUPS - GTID_ONLY - GTIDS - HISTOGRAM - INACTIVE - INITIAL - INITIAL_SIZE - INITIATE - INSTANCE - IO_AFTER_GTIDS - IO_BEFORE_GTIDS - JSON_VALUE - KEYRING - LAG - LATERAL - LEAD - LINESTRING - LOG - LOGFILE - MANUAL - MAX_SIZE - MEMBER - MULTILINESTRING - MULTIPOINT - MULTIPOLYGON - NDB - NDBCLUSTER - NETWORK_NAMESPACE - NEW - NO_WAIT - NTH_VALUE - NTILE - NULLS - OFF - OJ - OLD - OPTIMIZER_COSTS - OPTIONAL - ORGANIZATION - PARALLEL - PARSE_TREE - PASSWORD_LOCK_TIME - PERCENT_RANK - PERSIST - PERSIST_ONLY - PLUGIN_DIR - POINT - POLYGON - PRIVILEGE_CHECKS_USER - QUALIFY - RANDOM - RANK - REDO_BUFFER_SIZE - REFERENCE - REGISTRATION - REPLICATE_DO_DB - REPLICATE_DO_TABLE - REPLICATE_IGNORE_DB - REPLICATE_IGNORE_TABLE - REPLICATE_REWRITE_DB - REPLICATE_WILD_DO_TABLE - REPLICATE_WILD_IGNORE_TABLE - REQUIRE_ROW_FORMAT - REQUIRE_TABLE_PRIMARY_KEY_CHECK - RESOURCE - RESPECT - RESTORE - RETAIN - ROTATE - S3 - SECONDARY - SECONDARY_ENGINE - SECONDARY_ENGINE_ATTRIBUTE - SECONDARY_LOAD - SECONDARY_UNLOAD - SOURCE_AUTO_POSITION - SOURCE_BIND - SOURCE_COMPRESSION_ALGORITHMS - SOURCE_CONNECTION_AUTO_FAILOVER - SOURCE_CONNECT_RETRY - SOURCE_DELAY - SOURCE_HEARTBEAT_PERIOD - SOURCE_HOST - SOURCE_LOG_FILE - SOURCE_LOG_POS - SOURCE_PASSWORD - SOURCE_PORT - SOURCE_PUBLIC_KEY_PATH - SOURCE_RETRY_COUNT - SOURCE_SSL - SOURCE_SSL_CA - SOURCE_SSL_CAPATH - SOURCE_SSL_CERT - SOURCE_SSL_CIPHER - SOURCE_SSL_CRL - SOURCE_SSL_CRLPATH - SOURCE_SSL_KEY - SOURCE_SSL_VERIFY_SERVER_CERT - SOURCE_TLS_CIPHERSUITES - SOURCE_TLS_VERSION - SOURCE_USER - SOURCE_ZSTD_COMPRESSION_LEVEL - SQL_AFTER_MTS_GAPS - SRID - STACKED - STREAM - TABLESAMPLE - THREAD_PRIORITY - TLS - TYPES - UNDO_BUFFER_SIZE - UNDOFILE - UNREGISTER - URL - VCPU - XID -ZONE Copyright @ 2026 MariaDB Foundation. 63
87. Keywords - only in MariaDB - != - && - << - <= - <=> - <> - => - >= - >> - || - ATOMIC - AUTHORIZATION - AUTHORS - BODY - CHECKPOINT - CLOB - COLUMN_ADD - COLUMN_CHECK - COLUMN_CREATE - COLUMN_DELETE - COLUMN_GET - CONTRIBUTORS - CONVERSION - CURRENT_PATH - CURRENT_POS - CURRENT_ROLE - CYCLE - DELETE_DOMAIN_ID - DES_KEY_FILE - DO_DOMAIN_IDS - ELSIF - EXAMINED - EXCEPTION - FEDERATED - GOTO - HARD - ID - IGNORED - IGNORE_DOMAIN_IDS - IMMEDIATE - INCREMENT - ISOPEN - LASTVAL - MASTER_CONNECT_RETRY - MASTER_DELAY - MASTER_DEMOTE_TO_REPLICA - MASTER_DEMOTE_TO_SLAVE - MASTER_GTID_POS - MASTER_HEARTBEAT_PERIOD - MASTER_HOST - MASTER_LOG_FILE - MASTER_LOG_POS - MASTER_PASSWORD - MASTER_PORT - MASTER_RETRY_COUNT - MASTER_SERVER_ID - MASTER_SSL - MASTER_SSL_CA - MASTER_SSL_CAPATH - MASTER_SSL_CERT - MASTER_SSL_CIPHER - MASTER_SSL_CRL - MASTER_SSL_CRLPATH - MASTER_SSL_KEY - MASTER_SSL_VERIFY_SERVER_CERT - MASTER_USE_GTID - MASTER_USER - MAX_STATEMENT_TIME - MINUS - MINVALUE - MONITOR - MYSQL - NEXTVAL - NOCACHE - NOCOPY - NOCYCLE - NOMAXVALUE - NOMINVALUE - NOTFOUND - OBJECT - OLD_PASSWORD - ONLINE - OVERLAPS - PACKAGE - PAGE_CHECKSUM - PARSE_VCOL_EXPR - PERIOD - PERSISTENT - PORTION - PREVIOUS - RAISE - RAW - RECORD - REF_SYSTEM_ID - REPLAY - REPLICA_POS - ROWCOUNT - ROWNUM - ROWTYPE - SCALAR - SEQUENCE - SETVAL - SLAVE_POS - SLAVES - SOFT - SQL_CACHE - STAGE - STATEMENT - SYSDATE - SYSTEM_TIME - THREADS - TO_DATE - TRANSACTIONAL - VARCHAR2 - VECTOR - VERSIONING - VIA - WITHIN Copyright @ 2026 MariaDB Foundation. 64
88. Reserved Keywords - MySQL vs MariaDB Both have 31 reserved keywords that the other doesn't! MySQL MariaDB ACCESSIBLE CUME_DIST DATABASE DENSE_RANK EMPTY FIRST_VALUE FUNCTION GENERATED GET GROUPING GROUPS IO_AFTER_GTIDS IO_BEFORE_GTIDS JSON_TABLE LAG LAST_VALUE LATERAL LEAD NTH_VALUE NTILE OF OPTIMIZER_COSTS OPTION PERCENT_RANK RANK ROW SCHEMA STORED SYSTEM VIRTUAL WINDOW != && << <= <=> <> => >= >> || CONVERSION CURRENT_PATH CURRENT_ROLE DELETE_DOMAIN_ID DO_DOMAIN_IDS IGNORE_DOMAIN_IDS MASTER_DEMOTE_TO_REPLICA MASTER_DEMOTE_TO_SLAVE OFFSET PAGE_CHECKSUM PARSE_VCOL_EXPR PORTION REF_SYSTEM_ID RETURNING SQL_AFTER_GTIDS SQL_BEFORE_GTIDS STATS_AUTO_RECALC STATS_PERSISTENT STATS_SAMPLE_PAGES TO_DATE VECTOR Copyright @ 2026 MariaDB Foundation. 65
89. Reserved Keywords - MySQL vs MariaDB MySQL MariaDB MySQL > create table accessible (id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'accessible (id int)' at line 1 MariaDB > create table accessible (id int); Query OK, 0 rows affected (0.037 sec) MySQL > create table conversion (id int); Query OK, 0 rows affected (0.03 sec) MariaDB > create table conversion (id int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'conversion (id int)' at line 1 Copyright @ 2026 MariaDB Foundation. 66
90. Configuration MySQL and MariaDB have different default configuration settings, and some configuration variables that exist in one may not exist in the other. To facilitate migration, some removed settings in MariaDB have been aliased to no-op variables that can be set to the same values as in MySQL without causing errors, but they won't have any effect. Copyright @ 2026 MariaDB Foundation. 67
91. Configuration MySQL and MariaDB have different default configuration settings, and some configuration variables that exist in one may not exist in the other. To facilitate migration, some removed settings in MariaDB have been aliased to no-op variables that can be set to the same values as in MySQL without causing errors, but they won't have any effect. For example innodb_thread_concurrency is a no-op variable and will create a warning in the error log if set, but it won't cause an error and won't affect performance: Apr 28 11:27:09 dell mariadbd[3811998]: 2026-04-28 11:27:09 0 [Warning] 'innodb-thread-concurrency' was removed. It does nothing now and exists only for compatibility with old my.cnf files. Copyright @ 2026 MariaDB Foundation. 67
92. Configuration (2) That's the reason why it's important to review your MySQL configuration file before migration and remove or comment out any settings that are no longer relevant in MariaDB. It's also very important to review all messages and warnings in the MariaDB error log during the migration, as they may indicate configuration settings that need to be adjusted or removed. Copyright @ 2026 MariaDB Foundation. 68
93. Configuration (2) That's the reason why it's important to review your MySQL configuration file before migration and remove or comment out any settings that are no longer relevant in MariaDB. It's also very important to review all messages and warnings in the MariaDB error log during the migration, as they may indicate configuration settings that need to be adjusted or removed. I have created a MDEV to track the no-op variables when they are set: https://jira.mariadb.org/browse/MDEV-39252 Copyright @ 2026 MariaDB Foundation. 68
94. Configuration - MDEV-39252 Example: MariaDB [information_schema]> SELECT * FROM INFORMATION_SCHEMA.REMOVED_STARTUP_OPTIONS; +---------------------------+--------------+--------------+-------------------------------+----------+ | OPTION_NAME | OPTION_VALUE | SOURCE | CONFIG_FILE | HANDLING | +---------------------------+--------------+--------------+-------------------------------+----------+ | innodb-log-files-in-group | 2 | CONFIG | /dev/shm/var_auto_3zrj/my.cnf | IGNORED | | innodb-thread-concurrency | 8 | COMMAND_LINE | NULL | IGNORED | +---------------------------+--------------+--------------+-------------------------------+----------+ 2 rows in set (0.001 sec) Copyright @ 2026 MariaDB Foundation. 69
95. Configuration - MDEV-39252 Example: MariaDB [information_schema]> SELECT * FROM INFORMATION_SCHEMA.REMOVED_STARTUP_OPTIONS; +---------------------------+--------------+--------------+-------------------------------+----------+ | OPTION_NAME | OPTION_VALUE | SOURCE | CONFIG_FILE | HANDLING | +---------------------------+--------------+--------------+-------------------------------+----------+ | innodb-log-files-in-group | 2 | CONFIG | /dev/shm/var_auto_3zrj/my.cnf | IGNORED | | innodb-thread-concurrency | 8 | COMMAND_LINE | NULL | IGNORED | +---------------------------+--------------+--------------+-------------------------------+----------+ 2 rows in set (0.001 sec) Upvote if you think it's an interesting feature! Copyright @ 2026 MariaDB Foundation. 69
96. It contains: • Compatibility Evaluation • Migration Path Selection • Specialized Migration Scenarios Migrating to MariaDB from MySQL The documentation for migrating from MySQL to MariaDB is available at https:// mariadb.com/docs/server/server-management/install-and-upgrade-mariadb/ migrating-to-mariadb/moving-from-mysql Copyright @ 2026 MariaDB Foundation. 70
97. Baseline establish performance baselines, identify critical queries, and measure current system metrics Copyright @ 2026 MariaDB Foundation. 71
98. Baseline - Performance Metrics This section is purely theoritical, but you would want to gather performance metrics on your MySQL system before migration. This includes: • Query performance (e.g., using EXPLAIN ANALYZE on critical queries) • Resource utilization (CPU, memory, disk I/O) • Replication lag (if applicable) • Application response times • Error rates and logs • User connection patterns and load profiles Copyright @ 2026 MariaDB Foundation. 72
99. Baseline - Plan the Target Before migration, you should have a clear plan for your target for the new MariaDB environment. • What is the response time expectation? (same, 10% margin, etc.) • What is the acceptable replication lag (if applicable)? • What are the critical queries that must perform well? • What are the resource utilization targets? • What are the error rate thresholds? Copyright @ 2026 MariaDB Foundation. 73
100. Baseline - Plan the Target Before migration, you should have a clear plan for your target for the new MariaDB environment. • What is the response time expectation? (same, 10% margin, etc.) • What is the acceptable replication lag (if applicable)? • What are the critical queries that must perform well? • What are the resource utilization targets? • What are the error rate thresholds? That's the only way to validate the migration Copyright @ 2026 MariaDB Foundation. 73
101. Migrate the Data dump, restore and eventually replicate data Copyright @ 2026 MariaDB Foundation. 74
102. Data Migration - Logical Logical dump and restore is the only valid way to migrate from MySQL to MariaDB, due to the differences in storage formats and features. You can use mysqldump or mydumper to export the data from MySQL, and then import it into MariaDB. Copyright @ 2026 MariaDB Foundation. 75
103. Data Migration - Logical Logical dump and restore is the only valid way to migrate from MySQL to MariaDB, due to the differences in storage formats and features. You can use mysqldump or mydumper to export the data from MySQL, and then import it into MariaDB. If you use mydumper, I recommend to also use compression. $ mydumper --compact --user=admin --password=yourpass --exec-per-thread="/usr/bin/zstd -c" --exec-per-thread-extension=".zst" -v 3 Copyright @ 2026 MariaDB Foundation. 75
104. Data Migration - Logical Logical dump and restore is the only valid way to migrate from MySQL to MariaDB, due to the differences in storage formats and features. You can use mysqldump or mydumper to export the data from MySQL, and then import it into MariaDB. If you use mydumper, I recommend to also use compression. $ mydumper --compact --user=admin --password=yourpass --exec-per-thread="/usr/bin/zstd -c" --exec-per-thread-extension=".zst" -v 3 ** (mydumper:11262): CRITICAL **: 08:43:13.099: Non transactional table found: `mysqlandfriends`.`wp_tz_plusgallery_item` on a consistent backup attempt. Restart backup using --trx-tables=0 to indicate that you have non transactional tables. Copyright @ 2026 MariaDB Foundation. 75
105. Data Migration - Logical (2) Do you remember, we had 4 MyISAM tables! Now it's up to you to decide how to handle them. You can either convert them to InnoDB before migration, or you can migrate them as MyISAM but your backup won't be consistent, and you will need to use --trx-tables=0 with mydumper to ignore the transactional consistency check. Copyright @ 2026 MariaDB Foundation. 76
106. Data Migration - Logical (2) Do you remember, we had 4 MyISAM tables! Now it's up to you to decide how to handle them. You can either convert them to InnoDB before migration, or you can migrate them as MyISAM but your backup won't be consistent, and you will need to use --trx-tables=0 with mydumper to ignore the transactional consistency check. If possible, I always recommend converting MyISAM tables to InnoDB!! Copyright @ 2026 MariaDB Foundation. 76
107. Data Migration - Logical (3) MySQL > SELECT table_schema, TABLE_NAME, table_rows, CONCAT(ROUND((index_length+data_length)/1024/1024),'MB') AS size FROM information_schema.TABLES WHERE engine='myisam' AND table_schema NOT IN ('information_schema', 'mysql'); +-----------------+---------------------------+------------+------+ | TABLE_SCHEMA | TABLE_NAME | TABLE_ROWS | size | +-----------------+---------------------------+------------+------+ | mysqlandfriends | wp_tz_plusgallery_options | 4 | 0MB | | mysqlandfriends | wp_tz_plusgallery_type | 4 | 0MB | | mysqlandfriends | wp_tz_plusgallery_item | 4 | 0MB | | wp_lefred | wp_twitter_integration | 0 | 0MB | +-----------------+---------------------------+------------+------+ 4 rows in set (0.01 sec) Copyright @ 2026 MariaDB Foundation. 77
108. Data Migration - Logical (4) MySQL > alter table mysqlandfriends.wp_tz_plusgallery_options engine=innodb; Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0 MySQL > alter table mysqlandfriends.wp_tz_plusgallery_type engine=innodb; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 MySQL > alter table mysqlandfriends.wp_tz_plusgallery_item engine=innodb; Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 MySQL > alter table wp_lefred.wp_twitter_integration engine=innodb; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 Copyright @ 2026 MariaDB Foundation. 78
109. Data Migration - Dump $ mydumper --compact --exec-per-thread="/usr/bin/zstd --user=fred --password=xxx -c" --exec-per-thread-extension=".zst" -v 3 ** Message: 08:59:03.060: MyDumper backup version: 0.21.4-1 ** Message: 08:59:03.060: Using 4 dumper threads ** Message: 08:59:03.064: Connection via default library settings using password: User: fred ** Message: 08:59:03.064: searching group group mydumper_mysql ** Message: 08:59:03.065: Connected to MySQL 8.4.8 ** Message: 08:59:03.066: Started dump at: 2026-04-21 08:59:03 ... ** Message: 08:59:11.514: Releasing DDL lock ** Message: 08:59:11.514: UNLOCK INSTANCE: OK ** Message: 08:59:11.514: Queue count: 0 0 0 0 0 ** Message: 08:59:11.514: Main connection closed ** Message: 08:59:11.517: Finished dump at: 2026-04-21 08:59:11 Copyright @ 2026 MariaDB Foundation. 79
110. Data Migration - Dump $ mydumper --compact --exec-per-thread="/usr/bin/zstd --user=fred --password=xxx -c" --exec-per-thread-extension=".zst" -v 3 ** Message: 08:59:03.060: MyDumper backup version: 0.21.4-1 ** Message: 08:59:03.060: Using 4 dumper threads ** Message: 08:59:03.064: Connection via default library settings using password: User: fred ** Message: 08:59:03.064: searching group group mydumper_mysql ** Message: 08:59:03.065: Connected to MySQL 8.4.8 ** Message: 08:59:03.066: Started dump at: 2026-04-21 08:59:03 ... ** Message: 08:59:11.514: Releasing DDL lock ** Message: 08:59:11.514: UNLOCK INSTANCE: OK ** Message: 08:59:11.514: Queue count: 0 0 0 0 0 ** Message: 08:59:11.514: Main connection closed ** Message: 08:59:11.517: Finished dump at: 2026-04-21 08:59:11 Copyright @ 2026 MariaDB Foundation. [root@mysql1]# du -sh export-20260421-085903 58M export-20260421-085903 🗆 79
111. Data Migration - Restore Before restoring, we need to take in consideration the findings we discovered during the inventory phase. In our case, we will ignore the sys schema table, and the two collections we found. So we create a file named exclude.txt with the following content: sys library.books vinyl.records Copyright @ 2026 MariaDB Foundation. 80
112. Data Migration - Restore Before restoring, we need to take in consideration the findings we discovered during the inventory phase. In our case, we will ignore the sys schema table, and the two collections we found. So we create a file named exclude.txt with the following content: sys library.books vinyl.records If you use InnoDB Cluster or ReplicaSet ignore the metadata schema: mysql_innodb_cluster_metadata Copyright @ 2026 MariaDB Foundation. 80
113. Data Migration - Restore (2) # myloader --directory export-20260421-085903 --omit-from-file=/vagrant/exclude.txt --drop-table Copyright @ 2026 MariaDB Foundation. 81
114. Data Migration - Restore (2) # myloader --directory export-20260421-085903 --omit-from-file=/vagrant/exclude.txt --drop-table ** (myloader:8926): WARNING **: 09:39:50.689: Thread 8 using connection 2243 - ERROR 1067: Error occurs between lines: 3 and 0: Invalid default value for 'comment_date' CREATE TABLE `wp_comments` ( `comment_ID` bigint unsigned NOT NULL AUTO_INCREMENT, `comment_post_ID` bigint unsigned NOT NULL DEFAULT '0', `comment_author` tinytext NOT NULL, `comment_author_email` varchar(100) NOT NULL DEFAULT '', `comment_author_url` varchar(200) NOT NULL DEFAULT '', `comment_author_IP` varchar(100) NOT NULL DEFAULT '', `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `comment_content` text NOT NULL, `comment_karma` int NOT NULL DEFAULT '0', `comment_approved` varchar(20) NOT NULL DEFAULT '1', `comment_agent` varchar(255) NOT NULL DEFAULT '', `comment_type` varchar(20) NOT NULL DEFAULT 'comment', `comment_parent` bigint unsigned NOT NULL DEFAULT '0', `user_id` bigint unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`comment_ID`) ) ENGINE=InnoDB AUTO_INCREMENT=649 DEFAULT CHARSET=utf8mb3 Copyright @ 2026 MariaDB Foundation. 81
115. Data Migration - Restore (3) The error is due to the fact that older version of MySQL allowed 0000-00-00 00:00:00 as a default value for datetime columns, while new versions, and MariaDB does not (see SQL_MODE=STRICT_TRANS_TABLES). Copyright @ 2026 MariaDB Foundation. 82
116. Data Migration - Restore (3) The error is due to the fact that older version of MySQL allowed 0000-00-00 00:00:00 as a default value for datetime columns, while new versions, and MariaDB does not (see SQL_MODE=STRICT_TRANS_TABLES). We have again several options to handle this: • Modify the dump file to remove the default value (not recommended) • Modify the table definition in MySQL and redo the dump • Modify the dump's metadata to change the SQL_MODE (faster) Copyright @ 2026 MariaDB Foundation. 82
117. Data Migration - Restore (4) Comment the SQL_MODE line in the metadata file inside the export directory: $ grep SQL_MODE export-20260421-085903/metadata #SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE, NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' /*!40101 And restore again. Copyright @ 2026 MariaDB Foundation. 83
118. Data Migration - Restore (5) [root@mariadb]# myloader --directory export-20260421-085903 --omit-from-file=/vagrant/exclude.txt --drop-table -v 3 ** Message: 10:07:21.999: MyDumper restore version: 0.21.4-1 ** Message: 10:07:21.999: Using 4 loader threads ** Message: 10:07:22.001: Using /tmp/.NQL5N3 as FIFO directory and /tmp/.41J4N3 as LOAD DATA temporary directory, please remove them if restoration fails ** Message: 10:07:22.002: Omit list file contains 4 tables to skip ** Message: 10:07:22.030: Connection via default library settings ** Message: 10:07:22.039: Initializing initialize_worker_schema ** Message: 10:07:22.043: myloader_session_variables found on metadata ** Message: 10:07:22.043: Change master will be executed for channel: default channel ** Message: 10:07:22.046: Intermediate queue: Sending END job ** Message: 10:07:22.051: Intermediate thread: SHUTDOWN ** Message: 10:07:22.051: metadata pushed ... ** Message: 10:08:03.432: Restore completed Copyright @ 2026 MariaDB Foundation. 84
119. Replication If you want to perform a migration with the minimal downtime, you can set up replication from MySQL to MariaDB. This allows you to see how your new MariaDB system performs under load, and validate your queries. Of course, if you ignored some schemas or tables during the initial dump and restore, you will need to handle that in the replication setup as well. Copyright @ 2026 MariaDB Foundation. 85
120. Replication - source preparation We need to prepare the MySQL server to be a source for replication. We need to create a replication user, and grant it the necessary privileges: MySQL > CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass'; MySQL > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; We also need to confirm that the binary log is enabled (default) and that the server has a unique server_id: MySQL > select @@log_bin; +-----------+ | @@log_bin | +-----------+ | 1 | +-----------+ Copyright @ 2026 MariaDB Foundation. 86
121. Replication - source preparation (2) MySQL > set persist server_id=410232619; If log_bin is off, you need to enable it and restart the MySQL server, by commenting the following line as binary logs are enabled by default in MySQL: [mysqld] #disable_log_bin And don't forget to be sure to have enough time to restore and prepare the replica, MySQL should not purge the binary logs before we start replication. select @@binlog_expire_logs_seconds/60/60/24; +---------------------------------------+ | @@binlog_expire_logs_seconds/60/60/24 | +---------------------------------------+ | 30.000000000000 | +---------------------------------------+ Copyright @ 2026 MariaDB Foundation. 87
122. Replication - replica preparation On the MariaDB side, we need to prepare the server to be a replica of MySQL. [mariadb] server_id=2 slave_parallel_mode=conservative We also need to enable caching_sha2_password plugin: MariaDB > INSTALL SONAME 'auth_mysql_sha2'; Query OK, 0 rows affected (0.024 sec) Copyright @ 2026 MariaDB Foundation. 88
123. Replication - setup replication MariaDB > change master to master_host='192.168.56.2', master_port=3306, master_user='repl', master_password='replpass', master_log_file='binlog.000007', master_log_pos=2242, master_ssl=0; Copyright @ 2026 MariaDB Foundation. 89
124. Replication - setup replication MariaDB > change master to master_host='192.168.56.2', master_port=3306, master_user='repl', master_password='replpass', master_log_file='binlog.000007', master_log_pos=2242, master_ssl=0; You can get the master_log_file and master_log_pos values from the dump's metadata file: $ grep SOURCE metadata # SOURCE_LOG_FILE = "binlog.000007" # SOURCE_LOG_POS = 2242 Copyright @ 2026 MariaDB Foundation. 89
125. Replication - start replication Before starting replication, if we skipped some schemas and tables, it's time to tell MariaDB to ignore them: MariaDB > SET GLOBAL replicate_ignore_table='library.books,vinyl.records'; MariaDB > SET GLOBAL replicate_ignore_db='sys,mysql_innodb_cluster_metadata'; And we can start replication: MariaDB > start replica; Query OK, 0 rows affected (0.028 sec) Copyright @ 2026 MariaDB Foundation. 90
126. Replication - validate replication We can check the replication status using: MariaDB > show replica status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.2 Master_Log_File: binlog.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Ignore_DB: sys,mysql_innodb_cluster_metadata Replicate_Ignore_Table: vinyl.records,library.books Last_Errno: 0 Exec_Master_Log_Pos: 2784 Seconds_Behind_Master: 0 Master_Server_Id: 410232619 Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Copyright @ 2026 MariaDB Foundation. 91
127. Limitation Please pay attention that even if the auth_mysql_sha2 plugin is available in MariaDB, it only supports authentication, and not replication. This means you shouldn't create new users until the migration is complete. JSON columns must be converted to LONGTEXT prior to migration if replication and modification or insertion are required, since MariaDB does not support ROW based replication for the JSON data type: Last_SQL_Error: Table structure for binlog event is not compatible with the table definition on this slave: In RBR mode, Slave received unknown field type field 245 for column Name: fred.test_json.options Copyright @ 2026 MariaDB Foundation. 92
128. Data and Analytics Platform Migration MariaDB plc recommends the use of Sqlines (https://sqlines.com/), an open- source tool that helps you convert database schema (DDL), queries and SQL scripts from MySQL to MariaDB. Example: $ ls lib/ libmysqlclient.so $ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:./lib $ ./sqldata -sd=mysql,migration/fred@192.168.56.2 \ -td=mariadb,migration/fred@192.168.56.4 -t=*.* \ -texcl =sys.*,mysql.*,performance_schema.*,information_schema.* -cmd=validate Copyright @ 2026 MariaDB Foundation. 93
129. Data and Analytics Platform Migration (2) Connecting to databases (31 ms) MySQL 8.4.9 MySQL Community Server - GPL x86_64 Linux 1.3.2 (Ok, 31 ms) MySQL 12.3.1-MariaDB-log MariaDB Server x86_64 Linux system 21a047800091 OpenSSL 3.5.1 1 Jul 2025 (Ok, 17 ms) Reading the database schema (349 tables, 0 objects, 97 ms) Validating table row count (4 concurrent sessions): cards.proposed - Started (1 of 349, session 1) cards.proposed - Ok (4 ms, session 1) Source: 37 rows (4 ms) Target: 37 rows (1 ms) ... 112. wp_lefred.wp_commentmeta (Source rows: 36342, Target rows: 36342) 113. rss.ttrss_entries (Source rows: 83339, Target rows: 83339) Summary: Total number of tables: 349 With the same row count: 349 With the different row count: 0 Total number of rows: 884498 in source, 884498 in target Row count difference: 0 Copyright @ 2026 MariaDB Foundation. 94
130. Test Migration perform test migrations, validate data integrity, and test application compatibility Copyright @ 2026 MariaDB Foundation. 95
131. Test Migration - Data Integrity The best way to test that everything is working as expected is to let run the replica for a while, and then compare the data on both sides. Also verify that the replication doesn't break, and that the replica is not lagging behind. You can also perform a data integrity check using tools like pt-table-checksum from Percona Toolkit, which can compare the data between the source and replica and report any discrepancies. Copyright @ 2026 MariaDB Foundation. 96
132. Test Migration - Data Integrity (2) We need to use a user that uses the mysql_native_password plugin, as pt- table-checksum does not support caching_sha2_password yet. MySQL > CREATE USER 'checksum'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd'; MySQL > GRANT SELECT ON *.* TO 'checksum'@'%'; Copyright @ 2026 MariaDB Foundation. 97
133. Test Migration - Data Integrity (2) We need to use a user that uses the mysql_native_password plugin, as pt- table-checksum does not support caching_sha2_password yet. MySQL > CREATE USER 'checksum'@'%' IDENTIFIED WITH mysql_native_password BY 'passwd'; MySQL > GRANT SELECT ON *.* TO 'checksum'@'%'; # ./pt-table-checksum --user checksum --password passwd --host=192.168.56.2 --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,sys,mysql_innodb_cluster_metadata -q 2>/dev/null Checking if all tables can be checksummed ... Starting checksum ... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 04-21T14:48:43 1 0 0 0 0 0 0.002 library.books 04-21T14:49:00 1 0 0 0 0 0 0.001 vinyl.records Copyright @ 2026 MariaDB Foundation. 97
134. Test Migration - Data Integrity (3) Let's force a data mismatch to see how the tool reports it: MariaDB > select db, tbl, this_crc, this_cnt, source_crc, source_cnt from percona.checksums where this_crc <> source_crc; Empty set (0.000 sec) Copyright @ 2026 MariaDB Foundation. 98
135. Test Migration - Data Integrity (3) Let's force a data mismatch to see how the tool reports it: MariaDB > select db, tbl, this_crc, this_cnt, source_crc, source_cnt from percona.checksums where this_crc <> source_crc; Empty set (0.000 sec) MariaDB > select id, title from ttrss_entries limit 2; +------+------------------------------------------------------------+ | id | title | +------+------------------------------------------------------------+ | 6588 | mariadb 10.6 laravel migration | | 6848 | Encoding not responding [closed] | +------+------------------------------------------------------------+ Copyright @ 2026 MariaDB Foundation. 98
136. Test Migration - Data Integrity (3) Let's force a data mismatch to see how the tool reports it: MariaDB > select db, tbl, this_crc, this_cnt, source_crc, source_cnt from percona.checksums where this_crc <> source_crc; Empty set (0.000 sec) MariaDB > select id, title from ttrss_entries limit 2; +------+------------------------------------------------------------+ | id | title | +------+------------------------------------------------------------+ | 6588 | mariadb 10.6 laravel migration | | 6848 | Encoding not responding [closed] | +------+------------------------------------------------------------+ MariaDB > update ttrss_entries set title = "Encoding never responding [closed]" where id =6848; Copyright @ 2026 MariaDB Foundation. 98
137. Test Migration - Data Integrity (4) # ./pt-table-checksum --user checksum --password passwd --host=192.168.56.2 --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,sys,mysql_innodb_cluster_metadata -q 2>/dev/null Checking if all tables can be checksummed ... Starting checksum ... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 04-21T14:53:21 1 0 0 0 0 0 0.002 library.books 04-21T14:53:34 0 1 83339 0 1 0 4.236 rss.ttrss_entries 04-21T14:53:37 1 0 0 0 0 0 0.001 vinyl.record Copyright @ 2026 MariaDB Foundation. 99
138. Test Migration - Data Integrity (4) # ./pt-table-checksum --user checksum --password passwd --host=192.168.56.2 --no-check-binlog-format --nocheck-replication-filters --ignore-databases=mysql,sys,mysql_innodb_cluster_metadata -q 2>/dev/null Checking if all tables can be checksummed ... Starting checksum ... TS ERRORS DIFFS ROWS DIFF_ROWS CHUNKS SKIPPED TIME TABLE 04-21T14:53:21 1 0 0 0 0 0 0.002 library.books 04-21T14:53:34 0 1 83339 0 1 0 4.236 rss.ttrss_entries 04-21T14:53:37 1 0 0 0 0 0 0.001 vinyl.record MariaDB > select db, tbl, this_crc, this_cnt, source_crc, source_cnt from percona.checksums where this_crc <> source_crc; +-----+---------------+----------+----------+------------+------------+ | db | tbl | this_crc | this_cnt | source_crc | source_cnt | +-----+---------------+----------+----------+------------+------------+ | rss | ttrss_entries | f10e9fdb | 83339 | c706508f | 83339 | +-----+---------------+----------+----------+------------+------------+ Copyright @ 2026 MariaDB Foundation. 99
139. Test Migration - Application Compatibility After validating data integrity, it's important to test the application against the new MariaDB database to ensure that everything is working as expected. This includes testing all critical queries, stored procedures, and application features that interact with the database. You also need to monitor the response times and error rates to ensure that the application performs well with MariaDB. And finally, you need to monitor the resource utilization on the new system. Copyright @ 2026 MariaDB Foundation. 100
140. Plan Cutover develop a detailed cutover plan, including rollback procedures, and communication plan Copyright @ 2026 MariaDB Foundation. 101
141. Cutover Plan A cutover plan is a detailed plan that outlines the steps to be taken during the final migration from MySQL to MariaDB. The cutover plan should include: 1 A timeline of the cutover process 2 A list of tasks to be performed during the cutover 3 A list of responsible parties for each task 4 A communication plan to inform stakeholders of the cutover process and any expected downtime 5 A rollback plan in case of any issues during the cutover MySQL cannot be configured as replica of MariaDB using ROW based replication! Copyright @ 2026 MariaDB Foundation. 102
142. Cutover Plan - Rollback Procedures When we are ready to cutover, we need to have a rollback plan in case anything goes wrong. At that time, before promoting the MariaDB replica to be the new primary, we need to make some temporary changes in the configuration that should stay active until we validate the migration in production: [mariadb] log_bin=binlog server_id=2 slave_parallel_mode=conservative encrypt-binlog=0 log-bin-compress=0 character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci character_set_collations=utf8mb4=utf8mb4_unicode_ci gtid-strict-mode=OFF binlog_annotate_row_events=OFF binlog_format=STATEMENT Copyright @ 2026 MariaDB Foundation. 103
143. MySQL as Replica of MariaDB Now MySQL can be configured as a replica of our new promoted MariaDB. We need to create a replication user on the MariaDB side, and grant it the necessary privileges: MariaDB > CREATE USER 'repl'@'%' IDENTIFIED BY 'replpass'; MariaDB > GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; Copyright @ 2026 MariaDB Foundation. 104
144. MySQL as Replica of MariaDB (2) We need to check the binary log file and position on the MariaDB side, and then configure MySQL to be a replica of MariaDB: MariaDB > show master status\G *************************** 1. row *************************** File: binlog.000003 Position: 1733 Binlog_Do_DB: Binlog_Ignore_DB: Gtid_Binlog_Pos: 0-2-19 Copyright @ 2026 MariaDB Foundation. 105
145. MySQL as Replica of MariaDB (3) And finally, we configure and start replication on the MySQL side: MySQL > CHANGE REPLICATION SOURCE TO SOURCE_HOST = '192.168.56.5', SOURCE_PORT = 3306, SOURCE_USER = 'repl', SOURCE_PASSWORD = 'replpass', SOURCE_LOG_FILE = 'binlog.000003', SOURCE_LOG_POS = 1733, SOURCE_SSL = 0, SOURCE_AUTO_POSITION=0; MySQL > START REPLICA; Copyright @ 2026 MariaDB Foundation. 106
146. Summary Production Application Server Copyright @ 2026 MariaDB Foundation. 107
147. Summary Production Application Server logical dump Copyright @ 2026 MariaDB Foundation. 108
148. Summary Production Application Server logical restore Copyright @ 2026 MariaDB Foundation. 109
149. Summary Production Application Server replication replication filters Copyright @ 2026 MariaDB Foundation. 110
150. Summary Production Application Server replication replication filters pt-table-checksum Copyright @ 2026 MariaDB Foundation. 111
151. Summary Production Application Server binlog_format=STATEMENT replication Copyright @ 2026 MariaDB Foundation. 112
152. Copyright @ 2026 MariaDB Foundation. 113
153. join this great big family! Copyright @ 2026 MariaDB Foundation. 114