How to copy a Schema using MySQL Shell Dump & Load Utility ?

Another common question I receive about MySQL Shell Dump & Load Utility is how to dump a schema and load it with another name. Make a copy in fact.

Dumping the Schema

To do so, we need to use the dumpTables() method:

JS  util.dumpTables("test", [], "/tmp/dump", {all: true})

It is important to notice that the second parameter is an empty array and the option “all” is enabled.

This will dump all tables of the test schena into /tmp/dump.

Loading the data into another Schema

Now, we will load the data we previously dump into another schema.

The first thing to do is to create the destination schema:

JS  \sql create database test2

And finally, we load the data:

JS  util.loadDump("/tmp/dump", {schema: "test2"})

Of course, you can always increase the parallelism using the threads option.

You can also load into another server.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

One comment

  1. How would you copy stored procedures, triggers, views… to the new schema?
    it looks like all our tools are not a complete solution for this old problem…
    mysqldump: it cannot strip definers but it can omit the source schema name
    mysqlpump: it can strip definers but it cannot omit the source schema name
    mysql-shell – dumpTables: well just tables
    mysql-shell – dumpSchema: it cannot omit the source schema name

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.