To copy a MySQL server to another server or to the cloud, there are several ways.
We can distinguish between two different types of copy:
- physical copy
- logical copy
The physical copy is often the fastest. However, it requires some tools to ensure that you have a consistent online backup. For example, you can use MySQL Enterprise Backup (MEB).
Alternatively, it’s possible to use the CLONE plug-in to provision a new instance with existing data from a source server. This is my preferred approach.
Finally, the last physical solution is the use of a file system snapshot, but this requires the right infrastructure and even more care to have a consistent online backup.
However, if the plan is to move to a MySQL database management service such as MySQL HeatWave on OCI, these physical solutions are not suitable. They also require the same version of MySQL.
Similarly, if you plan to copy data from one MySQL Managed Service instance to another, integrated backup may be a solution… if you are staying in the same region.
When it comes to clouds, or different regions or different versions, the recommended method is to use a logical copy (also known as a logical dump).
Some old obsolete tools such as mysqldump, mysqlpump or Workbench may already be familiar to you. These are very limited tools and should be forgotten.
If you want to make a logical copy, the best solution is to use MySQL Shell.
MySQL Shell provides several methods within the util module:
dumpInstance()dumpSchemas()dumpTables()
These methods allow you to dump (and later load with loadDump()) to a file system or to an object store bucket (such as AWS S3, Azure Blob Storage and OCI Object Store).
With MySQL Shell 8.1, there are now some new methods that allow you to copy data from one MySQL instance to another without the need of intermediate storage:
copyInstance()copySchemas()copyTables()
Copy to on-prem
Let’s have a look at an example of copyInstance() from one on-prem instance to another on-prem instance:
As you can see, the dump and the load are made in parallel.
Copy in OCI
Now let’s see how to copy a MySQL HeatWave instance from one region to another, and performing a version downgrade at the same time:
Of course we need to peer the two regions using a remote VCN peering.
Once the interconnection is ready, we can then copy the MySQL HeatWave instance from us-ashburn-1 to eu-frankfurt-1 using MySQL Shell 8.1 installed on the compute instance:
As we are in MySQL HeatWave, we need to enable the strip_restricted_grants compatibility option and exclude the mysql_audit schema. It is also necessary to exclude the ocidm user (see above).
Here is the end of the process:
Conclusion
To copy a MySQL instance to another server, the clone plugin is the quickest and easiest method if you are on-prem and staying with the same version.
Otherwise, MySQL Shell is the preferred alternative. It is the recommended choice in the cloud.
As of MySQL 8.1, the MySQL shell provides the ability to eliminate the need for intermediate storage when using the copy methods.
Enjoying copying MySQL databases !
