SQLines tools can help you assess and convert database schema (DDL), queries and SQL scripts for MySQL to MariaDB migration.
SQLines
SQLConverter -
SQLscripts assessment and conversion tool
Databases:
MySQL 8.x, 6.x and 5.x
MariaDB 10.x and 5.x
Migration Reference
SQL Language Elements
Operators:
MySQL | MariaDB | ||
1 | col->"$.path" | Extract JSON value | Not supported |
2 | col->>"$.path" | Extract JSON value and unquote | Not supported |
Data Types
JSON data:
MySQL | MariaDB | |||
1 | JSON | JSON data | LONGTEXT | JSON is an alias for LONGTEXT, can be used in CREATE TABLE |
Built-in SQL Functions
Regular expression functions:
MySQL | MariaDB | ||
1 | REGEXP_LIKE(str, pattern [,mode]) | Check for common JSON keys in json1 and json2 | Not supported |
JSON functions:
MySQL | MariaDB | ||
1 | JSON_OVERLAPS(json1, json2) | Check for common JSON keys in json1 and json2 | Not supported |
2 | JSON_PRETTY(json) | Format JSON value | Not supported |
3 | JSON_STORAGE_FREE(json) | Get the freed JSON storafe after update | Not supported |
4 | JSON_STORAGE_SIZE(json) | Get the size of binary representation of JSON | Not supported |
5 | JSON_TABLE(columns_definition) | Produce tabular data from JSON data | Not supported |
JSON expressions:
MySQL | MariaDB | ||
1 | CAST('null' AS JSON) | Null JSON document | Not allowed, requires CAST('null' AS LONGTEXT) |
UUID functions:
MySQL | MariaDB | ||
1 | BIN_TO_UUID(uuid_binary) | Convert binary UUID to string | Not supported |
2 | IS_UUID(uuid_string) | Check for valid string-format UUID | Not supported |
3 | UUID_TO_BIN(uuid_string) | Convert string UUID to binary | Not supported |
Spatial functions:
MySQL | MariaDB | ||
1 | DISTANCE(p1, p2) | Get the distance between p1 and p2 | ST_DISTANCE(p1, p2) |
2 | MBRCOVEREDBY(p1, p2) | Check coverage | Not supported |
3 | ST_BUFFER_STRATEGY(strategy, points) | Buffer strategy | Not supported |
4 | ST_DISTANCE_SPHERE(g1, g2 [, radius]) | Get the minimum spherical distance | Not supported |
5 | ST_GEOHASH(long, lat, length) | Returns a geo hash string | Not supported |
6 | ST_ISVALID(param) | Check if param is geometrically valid | Not supported |
7 | ST_LATFROMGEOHASH(geohash) | Return the latitude from a geo hash string | Not supported |
8 | ST_LONGFROMGEOHASH(geohash) | Return the longitude from a geo hash string | Not supported |
9 | ST_POINTFROMGEOHASH(geohash, id) | Return POINT from a geo hash string | Not supported |
10 | ST_SIMPLIFY(g, max_distance) | Simplify a geometry | Not supported |
11 | ST_VALIDATE(g) | Validate a geometry | Not supported |
Replication related functions:
MySQL | MariaDB | ||
1 | GTID_SUBSET(set1, set2) | Checks GTIDs in bots sets | Not supported |
2 | GTID_SUBTRACT(set1, set2) | Get GTIDs from set1 that not exist in set2 | Not supported |
3 | WAIT_FOR_EXECUTED_GTID_SET(set) | Wait until all GTIDs are applied | Not supported |
4 | WAIT_UNTIL_SQL_THREAD_AFTER_GTIDS(set) | Not supported |
Miscellaneous functions:
MySQL | MariaDB | ||
1 | ANY_VALUE(col) | Allow selecting columns that are not in GROUP BY | Not supported |
2 | GROUPING(col) | Grouping with GROUP BY WITH ROLLUP | Not supported |
3 | RANDOM_BYTES(length) | Generate random length bytes | Not supported |
4 | VALIDATE_PASSWORD_STRENGTH(pwd) | Validate plaintext password strength | Not supported |
SELECT Statement
Converting SQL SELECT statement:
MySQL | MariaDB | ||
1 | /*+ MAX_EXECUTION_TIME(n) */ | Max execution time in milliseconds, query hint | Not supported |
SQL Statements
Converting SQL statements:
MySQL | MariaDB | ||
1 | CREATE TABLESPACE name … | Create a tablespace | Not supported |
2 | CREATE USER name … | Create an user | CREATE USER name … see notes below |
CREATE USER statement:
MySQL | MariaDB | ||
1 | IDENTIFIED WITH mysql_native_password | User authentication plugin | Not supported |
IDENTIFIED WITH caching_sha2_password |
System Variables
System variables shown by SHOW GLOBAL VARIABLES statement:
MySQL | MariaDB | |||
1 | max_execution_time | Max execution time for SELECT in milliseconds | max_statement_time | Time in seconds |
How do I import MySQL database into MariaDB?
Importing a MySQL or MariaDB database
To import a MySQL or MariaDB dump, the first thing to do is to create the database where the import will take place. For doing so, if you do not have a database manager, you need to connect to the database server as a “root” user. This will open the Shell of MySQL or MariaDB.
Can I restore MySQL database to MariaDB?
To restore the data to a fresh MySQL/MariaDB database from the command line, follow these steps: Launch a new Windows command shell using the "Shell" button in the XAMPP control panel. Use the mysql client to create a new, empty database to hold your data. In this example, the new database is named myblog.
How do I migrate from MySQL 5.7 to MariaDB?
The following steps are every single step I have performed to install MariaDB 10.1 properly for a MySQL 5.7 migration..
Step 1 — Create the Yum repo. sudo vi /etc/yum.repos.d/MariaDB.repo. ... .
Step 2 — Install the rpm key. ... .
Step 3— Completely remove MySQL data. ... .
Step 4 — Install Maria DB. ... .
Final Step — Start service and restore DBS..
How do I switch from MySQL to MariaDB in Linux?
To upgrade from MariaDB to MySQL you need to perform the follow simple steps:.
stop MariaDB's mysqld process..
install the binary files of 5.7..
start mysqld & run mysqld_upgrade..
run MySQL Shell's upgrade checker utility..
stop mysqld..
upgrade the binaries to MySQL 8.0..