Skip to content
IntoSQL home
Convert a file

Move a MySQL dump to an older or newer MySQL

Drop a dump from mysqldump, mysqlpump, phpMyAdmin or Adminer, from MySQL 5.5 to 9, as .sql or .sql.gz, and download a script that loads on MySQL 5.7 and later. Collations only MySQL 8 and later know, such as utf8mb4_0900_ai_ci, become utf8mb4_unicode_520_ci, and every change is listed in the report.

MySQL to MySQL migration guideMySQL to MySQL data types
Your file
sqlshop_2024.sqlSample file, 5.1 kB
Your database

18 rows converted, 2 fixes, none rejected

Show

Tables in shop_2024.sqlMySQL 8.0.46 dump · written by mysqldump · utf8mb4

  • customers8 rows1
    7 columns
    idint unsigned not null
    namevarchar(100) not null
    emailvarchar(150) not null
    countrychar(2) not null
    is_activetinyint(1) not null
    signed_update not null
    created_attimestamp not null
  • orders10 rows1
    9 columns
    idint unsigned not null
    customer_idint unsigned not null
    statusenum('pending','paid','shipped','cancelled') not null
    totaldecimal(10,2) not null
    itemssmallint unsigned not null
    ordered_atdatetime not null
    shipped_atdatetime
    notevarchar(255)
    updated_attimestamp 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 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,
  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 unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int unsigned NOT NULL,
  `status` enum('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  `total` decimal(10,2) NOT NULL,
  `items` smallint unsigned NOT NULL DEFAULT 1,
  `ordered_at` datetime NOT NULL,
  `shipped_at` datetime DEFAULT NULL,
  `note` varchar(255) 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`) VALUES (1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15','2023-01-15 10:24:00'),(2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03','2023-02-03 08:11:42'),(3,'José Peña','jose@example.es','ES',0,'2023-03-21','2023-03-21 19:05:17'),(4,'李雷','lilei@example.cn','CN',1,'2023-05-09','2023-05-09 02:40:00'),(5,'Noah O\'Brien','noah@example.ie','IE',1,'0000-00-00','2023-06-30 14:00:00'),(6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02','2024-01-02 09:30:00'),(7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29','2024-02-29 23:59:59'),(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12','2024-04-12 12:12:12');
INSERT INTO `orders` (`id`,`customer_id`,`status`,`total`,`items`,`ordered_at`,`shipped_at`,`note`,`updated_at`) VALUES (1,1,'shipped',129.90,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL,'2024-03-02 16:30:00'),(2,2,'paid',54.00,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁','2024-03-02 11:20:00'),(3,1,'cancelled',12.50,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind','2024-03-03 10:00:00'),(4,4,'shipped',980.00,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放','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','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\"','2024-03-11 09:00:00'),(7,8,'paid',76.20,2,'2024-03-11 18:45:00',NULL,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','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','2024-03-15 10:00:00'),(10,7,'paid',1499.00,1,'2024-03-15 21:05:00',NULL,'C:\\Users\\ravi\\invoice.pdf','2024-03-15 21:05:00');

SET UNIQUE_CHECKS = 1;
SET FOREIGN_KEY_CHECKS = 1;

Fixes

  1. customers uses the collation utf8mb4_0900_ai_ci, which MySQL doesn't have, so it uses utf8mb4_unicode_520_ci; text may sort and compare a little differently.
  2. orders uses the collation utf8mb4_0900_ai_ci, which MySQL doesn't have, so it uses utf8mb4_unicode_520_ci; text may sort and compare a little differently.

Your file is converted on this computer and never uploaded.

Other samples:

What changes between the file and the script

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 the fileIn the scriptReport line
utf8mb4_0900_* and utf8mb4_uca1400_* collationsutf8mb4_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.
TIMETIME, durations includedThe same type, with the same range.
TINYINT(1), BOOLTINYINT(1)The same type with the same values.
UNSIGNED and ZEROFILLkeptThe same types.
ENUM and SET, the '' error value includedkeptThe same types; the script loads without strict mode, so the error value loads as it is.
JSONJSONMySQL reads the same JSON text.
NUL bytes in textkeptThe value is copied with the same escape the source used.
AUTO_INCREMENTAUTO_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 dumpan 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_TIMESTAMPkeptThe same clause, with the column's fractions of a second.
generated columnsVIRTUAL or STORED as in the sourceThe same formula, translated term by term.
generated columns with functions IntoSQL doesn't reada plain columnA formula IntoSQL can't read leaves a plain column and a report line.generated_not_translated
INVISIBLE columnsregular columnsMySQL has invisible columns from 8.0.23, MariaDB from 10.3.invisible_column
CHECK constraintskept (MySQL 5.7 parses and ignores them)The same checks, translated term by term.
CHECK constraints with functions IntoSQL doesn't readnot createdA check IntoSQL can't read is left out and reported.check_skipped
CHECK ... NOT ENFORCEDnot 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 GEOMETRYno 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 indexeskeptThe same index; MATCH ... AGAINST works as before.
SPATIAL indexeskeptThe same index.
foreign keyskept, 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, BLOBkeptBytes stay bytes.
PARTITION BYone 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 eventslisted 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 NULLNOT 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 setthe 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 TABLEtypes 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 TABLEGEOMETRYMySQL's geometry format is recognised from the values.
SET NAMES utf8 from utf8mb4 tablesconverted, 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-8read as Latin-1 (Windows-1252)MySQL's latin1 is Windows-1252, the usual encoding of such dumps.charset_guessed
sjis, cp932, gbk, big5 dumpsread as UTF-8, with a warningThese encodings are not decoded yet.unsupported_charset
UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rowsevaluatedSome dump tools write values as these calls; they are read as the values they produce.
other expressions in rowsNULLA converter that doesn't run a database can't evaluate them.expression_value
DEFAULT in VALUESthe column's default valueA literal default is copied into the row.default_keyword_filled
DEFAULT in VALUES without a literal defaultNULLThere is no value to copy.default_keyword_null
REPLACE and INSERT IGNOREplain rowsA dump has one row per key, so the rows are the same.replace_as_insert
types IntoSQL doesn't knowLONGBLOBA blob holds any value's bytes; change the type after loading.unknown_type
values that don't fit their columnNULLA damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads.invalid_value_null
unreadable geometriesNULLBytes that aren't a geometry can't be stored as one.bad_geometry
TIMESTAMPkept, 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 < mysql.mysql.sql

Foreign 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 MySQL 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 utf8 dump loses before the converter sees it.
  • --hex-blob writes binary columns 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, procedures, functions and other objects written in MySQL's own language are listed in the report by name, never dropped silently, so you can check each one against the MySQL version you load into before recreating it.

Questions about moving MySQL between versions

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.

Does it read MariaDB dumps?

Yes. Dumps from mariadb-dump and from MariaDB 10.3 to 12 convert the same way, including MariaDB's INET6, INET4 and UUID columns.

What happens to 0000-00-00 dates?

MySQL keeps them: the script sets a SQL mode that accepts zero and impossible dates while it loads, so they arrive as they are.

Are AUTO_INCREMENT values kept?

Yes. AUTO_INCREMENT stays as it is, and the table's counter continues where the dump left it.

Does TINYINT(1) become boolean?

It stays TINYINT(1), as in the source, so every value keeps its number.

What about stored procedures and triggers?

They are listed in the report by name and not converted, so you can check each one against the MySQL version you load into before recreating it. ON UPDATE CURRENT_TIMESTAMP stays as it is.

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.