Move a MySQL database

There are many ways to copy (or move) a MySQL database (aka schema) from one server (aka instance) to another (including the data).

On this occasion I used the Export and Import utilities within “MySQL Workbench” (like doing a backup and restore). The fact that the source and target instances were both hosted on GCP was irrelevant (no brackets required!).

1. Connect to Source and start the Data Export utility …

2.Within the utility, I left the defaults as they were, apart from …

  • TIC the schema I wanted to export (see screenshot)
  • Select “Export to Self-Contained File” as I wanted all the tables
  • and create a meaningful dump-file name

3. Because the Export and Import utilities would be using different logins, I clicked “Advanced Options” within the Export utility, and typed “OFF” over the top of the default “AUTO” setting for set-gtid-purged …

…before clicking the “Return” then “Start Export” back on the main page.

4. To keep it simple, I closed and reopened MySQL Workbench before connecting to the Target instance. Then from the Server menu I chose Data Import …

5. I left all the defaults as they were except …

  • I chose “Import from Self-Contained File”
  • and navigated to the dump-file
  • I clicked “New” and typed the schema name that would receive the import.

6. Finally. On the “Import Progress” page I clicked “Start Import”. Then waited about five minutes before anything seemed to happen

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s