1

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.

Tony B
  • 915
  • 1
  • 9
  • 24
  • 1
    I think you're trying to be more clever than `mysqldump`, which is probably not going to work. Your intent to "maximize `mysqlsh`" seems aspirational, not necessarily practical. – tadman Aug 20 '22 at 22:30
  • mysqldump does not work great when moving between different MySql versions, mysqlsh does, thus what I am doing. This is why I said mysqldump is not an option. – Tony B Aug 20 '22 at 22:59

2 Answers2

0

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

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.

knb
  • 9,138
  • 4
  • 58
  • 85