Description:
I was trying to copy an instance from a home server to OCI and use MySQL DBS.
My idea was to copy the instance to DBS and then configure DBS to replicate from my home instance. My home instance runs 8.0.35 and so does the destination instance.
I bumped into a few issues so thought it would be good to reference them here to improve the user experience when trying to copy data.
How to repeat:
Look to copy an instance from one place to another using the MySQL shell which provides options to do that.
Suggested fix:
Suggested improvements:
copyInstance() takes: target, options as parameters
- it seems that by default the shell will connect to an instance and use that as the source. This should be mentioned more explicitly.
- it would be useful to also take the source so the copy is explicitly defined, so perhaps add a third option as the source, or provide the source as one of the options parameters.
Provide a "setup replication configuration" option so after copying the instance replication can be setup from the source to the destination or from the destination to the source (the direction may depend on what the user wants to do so both directions may make sense).
- include the replication direction as part of the definition
- provide replication user/password credentials
New versions of 8.0+ have issues at least in OCI/DBS related to wildcard_grants. If such users exist, it would be good to indicate which users they are, or at least how to identify them so the user can determine how to resolve this.
If a copyInstance() fails part way through, as happened to me, trying to run it again will fail as some tables already exist. It would be useful to have a copyInstance option to "overwrite_existing_tables", that is DROP them and continue with the creation of a new table as before. This option probably should not be enabled by default but makes sense when creating a new setup on an existing instance with some data or if the previous copy attempt failed.
Related: if the versions are "compatible" then copyInstance could be implemented through MySQL native cloning between the 2 instances. It might be useful to let MySQL do the copying itself as native cloning runs really quickly and simply let the shell "orchestrate" the cloning process between instances.
