Convert MariaDB to MySQL
Drop a dump from mariadb-dump, mysqldump, phpMyAdmin or Adminer, as .sql or .sql.gz, and download a script for MySQL with the same tables, keys, indexes and data. Every value MySQL can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 4 fixes, none rejected
Tables in shop_2024_mariadb.sqlMariaDB 11.8.9-MariaDB-ubu2404 dump · written by mariadb-dump · utf8mb4
customers8 rows2
8 columns id int(10) unsigned not null name varchar(100) not null email varchar(150) not null country char(2) not null is_active tinyint(1) not null signed_up date not null created_at timestamp not null last_ip varchar(39) orders10 rows2
12 columns id int(10) unsigned not null customer_id int(10) unsigned not null status enum('pending','paid','shipped','cancelled') not null total decimal(10,2) not null items smallint(5) unsigned not null ordered_at datetime not null shipped_at datetime note varchar(255) order_ref char(36) not null invoice int(11) not null details json updated_at timestamp not null
-- Written by IntoSQL from a MySQL dump. Load it with: mysql --default-character-set=utf8mb4 <database> < <file>
SET NAMES utf8mb4;
SET TIME_ZONE = '+00:00';
SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO,ALLOW_INVALID_DATES';
CREATE TABLE `customers` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`email` varchar(150) NOT NULL,
`country` char(2) NOT NULL DEFAULT 'US',
`is_active` tinyint(1) NOT NULL DEFAULT 1,
`signed_up` date NOT NULL DEFAULT '0000-00-00',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_ip` varchar(39) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uq_email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci COMMENT='People who bought at least once';
CREATE TABLE `orders` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`customer_id` int(10) unsigned NOT NULL,
`status` enum('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
`total` decimal(10,2) NOT NULL,
`items` smallint(5) unsigned NOT NULL DEFAULT 1,
`ordered_at` datetime NOT NULL,
`shipped_at` datetime DEFAULT NULL,
`note` varchar(255) DEFAULT NULL,
`order_ref` char(36) NOT NULL,
`invoice` int(11) NOT NULL,
`details` json DEFAULT NULL,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_customer` (`customer_id`),
KEY `idx_status_date` (`status`,`ordered_at`),
CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;
INSERT INTO `customers` (`id`,`name`,`email`,`country`,`is_active`,`signed_up`,`created_at`,`last_ip`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15','2023-01-15 10:24:00','::ffff:203.0.113.7'),(2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42','2001:db8::1'),(3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17',NULL),(4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00','::ffff:198.51.100.23'),(5,'Noah O\'Brien','noah@example.ie','IE',1,'0000-00-00','2023-06-30 14:00:00',NULL),(6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00','2001:db8:85a3::8a2e:370:7334'),(7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59','::ffff:192.0.2.200'),(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12','2024-04-12 12:12:12',NULL);
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`order_ref`,`invoice`,`details`,`updated_at`) VALUES (1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f',1000,'{\"gift\": false}','2024-03-02 16:30:00'),(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70',1001,'{\"gift\": true, \"wrap\": \"red\"}','2024-03-02 11:20:00'),(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind','2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081',1002,NULL,'2024-03-03 10:00:00'),(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192',1003,'{\"fragile\": true}','2024-03-07 08:00:00'),(5,5,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice','4f1e806c-7d5b-4c2e-9a99-5e6f708192a3',1004,NULL,'2024-03-08 17:20:00'),(6,6,'shipped',310.40,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: \"glass\"','50f2917d-8e6c-4d3f-8baa-6f708192a3b4',1005,'{\"fragile\": true}','2024-03-11 09:00:00'),(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,NULL,'6103a28e-9f7d-4e40-9cbb-708192a3b4c5',1006,NULL,'2024-03-11 18:46:00'),(8,2,'',19.99,1,'2024-03-12 08:00:00',NULL,'Status lost in an old import','7214b39f-a08e-4f51-8dcc-8192a3b4c5d6',1007,NULL,'2024-03-12 08:00:00'),(9,3,'shipped',64.00,2,'2024-03-14 15:30:00','0000-00-00 00:00:00','Shipping date never recorded','8325c4a0-b19f-4062-9edd-92a3b4c5d6e7',1008,'{\"parcels\": [1, 2]}','2024-03-15 10:00:00'),(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf','9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8',1009,NULL,'2024-03-15 21:05:00');
SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;
Fixes
- customers uses the collation utf8mb4_uca1400_ai_ci, which MySQL doesn't have, so it uses utf8mb4_unicode_520_ci; text may sort and compare a little differently.
- "last_ip" of customers is inet6, which MySQL doesn't have, so it is varchar(39) with the same values.
- orders uses the collation utf8mb4_uca1400_ai_ci, which MySQL doesn't have, so it uses utf8mb4_unicode_520_ci; text may sort and compare a little differently.
- "order_ref" of orders is uuid, which MySQL doesn't have, so it is char(36) with the same values.
Needs a look
- 1 sequence isn't created; MySQL has no sequences. Keep the next value in a table if your application needs it.
- The default of "invoice" in orders can't be written for MySQL (a sequence), so the column has no default. Add the default by hand if new rows need it.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from MariaDB to MySQL
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
utf8mb4_0900_* and utf8mb4_uca1400_* collations
utf8mb4_unicode_520_ci (utf8mb4_bin for binary ones)
MySQL doesn't know the newer collations, the error that stops most MySQL 8 dumps from loading elsewhere. unicode_520 follows the same Unicode rules most closely.
Report line: collation_replaced
zero, partial or impossible dates (0000-00-00, 2024-02-30)
kept
The script loads with ALLOW_INVALID_DATES and without NO_ZERO_DATE, so MySQL keeps the same values the source had.
TIME
TIME, durations included
The same type, with the same range.
TINYINT(1), BOOL
TINYINT(1)
The same type with the same values.
UNSIGNED and ZEROFILL
kept
The same types.
ENUM and SET, the '' error value included
kept
The same types; the script loads without strict mode, so the error value loads as it is.
JSON
JSON
MySQL reads the same JSON text.
NUL bytes in text
kept
The value is copied with the same escape the source used.
AUTO_INCREMENT
AUTO_INCREMENT with the table's counter
The counter continues where the source left it, MyISAM's per-group counters included.
MyISAM AUTO_INCREMENT on the second column of a key, in a table that isn't MyISAM in the dump
an index on the column and one counter for the table
InnoDB counts a column only when it leads an index, and once for the whole table.
Report line: myisam_group_autoincrement
ON UPDATE CURRENT_TIMESTAMP
kept
The same clause, with the column's fractions of a second.
generated columns
VIRTUAL or STORED as in the source
The same formula, translated term by term.
generated columns with functions IntoSQL doesn't read
a plain column
A formula IntoSQL can't read leaves a plain column and a report line.
Report line: generated_not_translated
INVISIBLE columns
regular columns
MySQL has invisible columns from 8.0.23, MariaDB from 10.3.
Report line: invisible_column
CHECK constraints
kept (MySQL 5.7 parses and ignores them)
The same checks, translated term by term.
CHECK constraints with functions IntoSQL doesn't read
not created
A check IntoSQL can't read is left out and reported.
Report line: check_skipped
CHECK ... NOT ENFORCED
not created
MySQL 5.7 and MariaDB can't keep a check without applying it, and the rows may break it.
Report line: check_not_enforced
expression defaults, and defaults on TEXT, BLOB, JSON and GEOMETRY
no default
MySQL takes them from 8.0.13; the script loads on 5.7, so they are left out and reported. MariaDB takes them.
Report line: default_not_translated
functional indexes, KEY ((expr))
not created
MySQL indexes expressions from 8.0.13 and MariaDB not at all; a generated column with an index does the same.
Report line: index_skipped
keys and indexes on a prefix, c(20)
kept
The same index.
FULLTEXT indexes
kept
The same index; MATCH ... AGAINST works as before.
SPATIAL indexes
kept
The same index.
foreign keys
kept, loaded with FOREIGN_KEY_CHECKS=0
The same keys; rows load as they did from the source dump.
GEOMETRY, POINT, POLYGON...
kept, with each value's SRID
The server's own geometry bytes are copied, SRID included.
INET4, INET6, UUID (MariaDB)
varchar(15), varchar(39), char(36)
MySQL has no such types; the text MariaDB writes for them is kept.
Report line: type_replaced
BINARY, VARBINARY, BLOB
kept
Bytes stay bytes.
PARTITION BY
one table
The rows are the same; partition the table again if it needs it.
Report line: partitions_dropped
WITH SYSTEM VERSIONING (MariaDB)
a table with the current rows
A dump holds the current rows only; MySQL has no system versioning.
Report line: system_versioning_dropped
Aria, ColumnStore, CONNECT, Spider, RocksDB (MariaDB)
InnoDB
MySQL has none of these engines. Every other engine is kept.
Report line: engine_dropped
CREATE SEQUENCE (MariaDB)
not created
MySQL has no sequences.
Report line: sequence_not_converted
views, triggers, procedures, functions and events
listed in the report
They are listed for you to recreate; their code often names its definer and server features.
Report line: not_converted_objects
NOT NULL columns with values that become NULL
NOT NULL dropped
A column whose values had to become NULL can't keep NOT NULL, or the load would stop.
Report line: not_null_relaxed
text that isn't valid in its character set
the bad bytes as the replacement character
The script is UTF-8; bytes that aren't would stop the load.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
dumps without CREATE TABLE
types read from the values
A dump made with --no-create-info only has rows, so the types come from the values.
Report line: no_schema
geometries in a dump without CREATE TABLE
GEOMETRY
MySQL's geometry format is recognised from the values.
SET NAMES utf8 from utf8mb4 tables
converted, with a warning
MySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file.
Report line: dump_lost_four_byte_chars
dumps without SET NAMES whose text isn't UTF-8
read as Latin-1 (Windows-1252)
MySQL's latin1 is Windows-1252, the usual encoding of such dumps.
Report line: charset_guessed
sjis, cp932, gbk, big5 dumps
read as UTF-8, with a warning
These encodings are not decoded yet.
Report line: unsupported_charset
UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows
evaluated
Some dump tools write values as these calls; they are read as the values they produce.
other expressions in rows
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
DEFAULT in VALUES
the column's default value
A literal default is copied into the row.
Report line: default_keyword_filled
DEFAULT in VALUES without a literal default
NULL
There is no value to copy.
Report line: default_keyword_null
REPLACE and INSERT IGNORE
plain rows
A dump has one row per key, so the rows are the same.
Report line: replace_as_insert
types IntoSQL doesn't know
LONGBLOB
A blob holds any value's bytes; change the type after loading.
Report line: unknown_type
values that don't fit their column
NULL
A damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads.
Report line: invalid_value_null
unreadable geometries
NULL
Bytes that aren't a geometry can't be stored as one.
Report line: bad_geometry
TIMESTAMP
kept, loaded in the time zone the dump states
The script sets the same time zone the dump did, so every moment is the same.
VECTOR(n) (MariaDB 11.7+)
json with the floats as an array
MySQL 5.7 and 8.0 have no vector type; a JSON array keeps every float on every version.
Report line: type_replaced
| In MariaDB | In MySQL | Report line |
|---|---|---|
| utf8mb4_0900_* and utf8mb4_uca1400_* collations | utf8mb4_unicode_520_ci (utf8mb4_bin for binary ones)MySQL doesn't know the newer collations, the error that stops most MySQL 8 dumps from loading elsewhere. unicode_520 follows the same Unicode rules most closely. | collation_replaced |
| zero, partial or impossible dates (0000-00-00, 2024-02-30) | keptThe script loads with ALLOW_INVALID_DATES and without NO_ZERO_DATE, so MySQL keeps the same values the source had. | |
| TIME | TIME, durations includedThe same type, with the same range. | |
| TINYINT(1), BOOL | TINYINT(1)The same type with the same values. | |
| UNSIGNED and ZEROFILL | keptThe same types. | |
| ENUM and SET, the '' error value included | keptThe same types; the script loads without strict mode, so the error value loads as it is. | |
| JSON | JSONMySQL reads the same JSON text. | |
| NUL bytes in text | keptThe value is copied with the same escape the source used. | |
| AUTO_INCREMENT | AUTO_INCREMENT with the table's counterThe counter continues where the source left it, MyISAM's per-group counters included. | |
| MyISAM AUTO_INCREMENT on the second column of a key, in a table that isn't MyISAM in the dump | an index on the column and one counter for the tableInnoDB counts a column only when it leads an index, and once for the whole table. | myisam_group_autoincrement |
| ON UPDATE CURRENT_TIMESTAMP | keptThe same clause, with the column's fractions of a second. | |
| generated columns | VIRTUAL or STORED as in the sourceThe same formula, translated term by term. | |
| generated columns with functions IntoSQL doesn't read | a plain columnA formula IntoSQL can't read leaves a plain column and a report line. | generated_not_translated |
| INVISIBLE columns | regular columnsMySQL has invisible columns from 8.0.23, MariaDB from 10.3. | invisible_column |
| CHECK constraints | kept (MySQL 5.7 parses and ignores them)The same checks, translated term by term. | |
| CHECK constraints with functions IntoSQL doesn't read | not createdA check IntoSQL can't read is left out and reported. | check_skipped |
| CHECK ... NOT ENFORCED | not createdMySQL 5.7 and MariaDB can't keep a check without applying it, and the rows may break it. | check_not_enforced |
| expression defaults, and defaults on TEXT, BLOB, JSON and GEOMETRY | no defaultMySQL takes them from 8.0.13; the script loads on 5.7, so they are left out and reported. MariaDB takes them. | default_not_translated |
| functional indexes, KEY ((expr)) | not createdMySQL indexes expressions from 8.0.13 and MariaDB not at all; a generated column with an index does the same. | index_skipped |
| keys and indexes on a prefix, c(20) | keptThe same index. | |
| FULLTEXT indexes | keptThe same index; MATCH ... AGAINST works as before. | |
| SPATIAL indexes | keptThe same index. | |
| foreign keys | kept, loaded with FOREIGN_KEY_CHECKS=0The same keys; rows load as they did from the source dump. | |
| GEOMETRY, POINT, POLYGON... | kept, with each value's SRIDThe server's own geometry bytes are copied, SRID included. | |
| INET4, INET6, UUID (MariaDB) | varchar(15), varchar(39), char(36)MySQL has no such types; the text MariaDB writes for them is kept. | type_replaced |
| BINARY, VARBINARY, BLOB | keptBytes stay bytes. | |
| PARTITION BY | one tableThe rows are the same; partition the table again if it needs it. | partitions_dropped |
| WITH SYSTEM VERSIONING (MariaDB) | a table with the current rowsA dump holds the current rows only; MySQL has no system versioning. | system_versioning_dropped |
| Aria, ColumnStore, CONNECT, Spider, RocksDB (MariaDB) | InnoDBMySQL has none of these engines. Every other engine is kept. | engine_dropped |
| CREATE SEQUENCE (MariaDB) | not createdMySQL has no sequences. | sequence_not_converted |
| views, triggers, procedures, functions and events | listed in the reportThey are listed for you to recreate; their code often names its definer and server features. | not_converted_objects |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| text that isn't valid in its character set | the bad bytes as the replacement characterThe script is UTF-8; bytes that aren't would stop the load. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe dump holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
| dumps without CREATE TABLE | types read from the valuesA dump made with --no-create-info only has rows, so the types come from the values. | no_schema |
| geometries in a dump without CREATE TABLE | GEOMETRYMySQL's geometry format is recognised from the values. | |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warningMySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file. | dump_lost_four_byte_chars |
| dumps without SET NAMES whose text isn't UTF-8 | read as Latin-1 (Windows-1252)MySQL's latin1 is Windows-1252, the usual encoding of such dumps. | charset_guessed |
| sjis, cp932, gbk, big5 dumps | read as UTF-8, with a warningThese encodings are not decoded yet. | unsupported_charset |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluatedSome dump tools write values as these calls; they are read as the values they produce. | |
| other expressions in rows | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| DEFAULT in VALUES | the column's default valueA literal default is copied into the row. | default_keyword_filled |
| DEFAULT in VALUES without a literal default | NULLThere is no value to copy. | default_keyword_null |
| REPLACE and INSERT IGNORE | plain rowsA dump has one row per key, so the rows are the same. | replace_as_insert |
| types IntoSQL doesn't know | LONGBLOBA blob holds any value's bytes; change the type after loading. | unknown_type |
| values that don't fit their column | NULLA damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. | invalid_value_null |
| unreadable geometries | NULLBytes that aren't a geometry can't be stored as one. | bad_geometry |
| TIMESTAMP | kept, loaded in the time zone the dump statesThe script sets the same time zone the dump did, so every moment is the same. | |
| VECTOR(n) (MariaDB 11.7+) | json with the floats as an arrayMySQL 5.7 and 8.0 have no vector type; a JSON array keeps every float on every version. | type_replaced |
Load the script into MySQL
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
mysql --default-character-set=utf8mb4 mydb < mariadb.mysql.sqlForeign keys are created as the dump declares them, with FOREIGN_KEY_CHECKS=0 while the rows load, so a row that points to a missing row doesn't stop the load.
For a large dump, run the client on the same machine as the server, and raise max_allowed_packet if a single row is very large.
IntoSQL Pro loads the file straight into MySQL from the desktop app or the command line, without writing a script.
Dump MariaDB for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --default-character-set=utf8mb4 keeps emoji and other 4-byte characters, which a utf8mb3 dump loses before the converter sees it.
- --hex-blob writes binary columns and VECTOR values as hexadecimal, which survives copying between systems.
- --routines --triggers --events add those objects to the dump, so the report can list them.
What isn't converted
Views, triggers, stored procedures, functions and events are written in MariaDB's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for MySQL.
Questions about MariaDB to MySQL
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Which MariaDB versions can the dump come from?
Dumps from mariadb-dump and mysqldump on MariaDB 10.3 to 12, with one INSERT per table or one per row. Every test dump comes from MariaDB 10.11, 11.4 and 11.8 and is compared with its source cell by cell.
Are UUID, INET6, JSON and VECTOR columns kept?
UUID becomes char(36), INET4 and INET6 varchar, JSON json, and VECTOR a json array of its floats, because MySQL 5.7 and 8.0 have no such types.
Are AUTO_INCREMENT values and sequences kept?
AUTO_INCREMENT stays as it is and continues where the dump left it. MySQL has no sequences, so they and the defaults that draw from them are reported.
What about stored procedures and triggers?
They are listed in the report by name and not converted, so you can check each one against MySQL before recreating it.
Which MySQL versions does the script load on?
MySQL 5.7 and later. Every test dump loads on 5.7 and 9, stopping at the first error if anything goes wrong.