mysqlsh to dump and load full schema

Question

I want to use mysqlsh to do the following:

1. Dump the FULL schema of a given database (not just tables, but functions, triggers, everything related to this database schema, same as mysqldump -R DATABASE > DATABASE.sql)
2. Load this full schema into a brand new database I just created (similar to mysql --database=NEWDATABASE < DATABASE.sql)

When I run mysqlsh --execute 'util.dumpTables("DATABASE", [], "SQL/DATABASE", {all:true});', it of course just dumps the tables, and this can easily be imported into a brand new database with this command mysqlsh --database=NEWDATABASE --execute 'util.loadDump("SQL/DATABASE", {schema: 'NEWDATABASE', ignoreVersion:true,resetProgress:true});. The problem is it is missing the functions and stored procedures.

So then I tried mysqlsh --execute 'util.dumpSchemas(["DATABASE"], "DATABASE");', and then load it into a new DB with mysqlsh --database=NEWDATABASE --execute 'util.loadDump("DATABASE", {dryRun: true, ignoreVersion:true});', but I instantly notice that it is trying to load into the original database, not my new database. So how do I load it into a NEW database, one with a totally different name?

In case you are wondering, I am trying to learn how to maximize mysqlsh for my use case. So the old mysqldump is not an option in this case.

Answer 1

I think you will just have to edit the .sql file(s) with a text editor before you try to load it.

This tool is really for dumping schemas and importing them to a different MySQL instance, but leaving the schema names unchanged.

Answer 2

I think a simple string substitution at the beginning of the dumpfile might suffice.

Just replace
"use olddatabase;"

with
"use newdatabase;"

and you are done. Unless your SQL code, e.g. stored procedures and view definitions contain some fancy inter-database commands, such as (I'm making this up).... select u.name, u.id from olddatabase.users u inner join somedatabase.tokens t on u.id = t.id where... Then you would need to perform proper string substitutions here as well.