Mysqldump alternative - MySQL Shell
How to stop fighting with utf8mb4 throughout the import/export process.
Command Palette
Search for a command to run...
Comments
Join the discussionNo comments yet. Be the first to comment.
Search for a command to run...
How to stop fighting with utf8mb4 throughout the import/export process.
No comments yet. Be the first to comment.
I'm writing this post thinking I might not be alone, having installed MySQL Shell alongside MySQL Server without looking too much into it.
I stumbled upon its many uses while struggling to import utf8mb4 content from one MySQL instance to another, using mysqldump. Even though I tried many hacks, like hex-blob, exporting as latin1 while skipping charset checks, nothing seemed to work for some special characters that my utf8mb4 database contained.
I will be using util.dumpTables in this example as it can be used to import within an arbitrary schema, regardless of its name (util.dumpSchemas doesn't seem to allow schemas to be renamed during the importation process).
mysqlsh root@localhost
MySQL JS > util.dumpTables("myDatabase",[], "/data/dump/myDatabase.dump", {all:true,threads: 24})
Note: If you are on Windows, you will want to escape backslashes \\ in your path.
The util.dumpTables accepts 3 or 4 arguments, being:
all allows to provide an empty array as the table list, but to dump all the tables from the specified schema.threads allows the operation to run on multiple threads in parallel (one per virtual CPU core?), making everything much faster.Important: Make sure the tables you are about to import do not exist before importing. If necessary, drop them (I recommend taking a snapshot beforehand).
Before we begin the import, you might need to allow local files to be read by your MySQL Server.
mysqlsh root@localhost
MySQL JS > \sql SET global local_infile=true;
MySQL JS > util.loadDump("/data/dump/myDatabase.dump", {schema:"myDatabase2", threads:24, ignoreversion:true})
MySQL JS > \sql SET global local_infile=false;
Here, the schema is specified as an option, since I want to import the data in a schema that has a different name than the dump's original.
Also, I used ignoreversion to allow me to import data from MySQL 5.7.X to a MySQL 8.X instance.
Voila, the transfer was quick, flawless, and the encoding was preserved throughout the process!