Migrating On-premises MySQL Enterprise Database to OCI Compute Instance MySQL Enterprise Database ?
In this post , we will walk through steps needed to migrate particular database(example - Sales database) from local Instance(On-premises) to Oracle Cloud Infrastructure Compute Instance.
We will use two new features introduced with latest release of MySQL
8.0.21.
1.
Dump Schema Utility
a.
This will help us to take the backup from
On-promises database and export to Oracle Cloud Object Storage.
2.
Load Dump Utility
a.
This is help us to Import
the schema from Object
Storage to local compute Instance.
More info:- https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
Suppose you wanted to do lift-shift of database called “sales”
, so “Schema Dump” utility will export sales
database from on-premises to OCI(oracle cloud infrastructure) object storage.
Then “Load Dump” utility
will import directly to MySQL Instance running in OCI compute instance.
Below diagram I have made for clear understanding…
What do we needed handy
1.
MySQL Shell 8.0.21 Version.
2.
On-premises MySQL Up and Running.
3.
Cloud Instances Up and Running.
4.
Install OCI CLI on On-premises Machine.
5.
Install OCI CLI on OCI(Oracle Cloud Compute
Instance)
6.
local_infile variables must be ON for destination machine.
Additional Details
|
On-Premises Instance Details |
Oracle Cloud Compute Instance Details |
|
Database Name:- Sales IP:- 192.168.1.10 User:- root Port: 3306 |
Public IP Address:
|
|
MySQL localhost:33060+
ssl Py > |
|
Checking for compatibility with MySQL Database Service 8.0.21 NOTE: Database sales had unsupported ENCRYPTION option commented out Compatibility issues with MySQL Database Service 8.0.21 were found and
repaired. Please review the changes made before loading them. Acquiring global read lock All transactions have been started Locking instance for backup Global read lock has been released Writing global DDL files Preparing data dump for table `sales`.`employee` Writing DDL for schema `sales` Writing DDL for table `sales`.`employee` Data dump for table `sales`.`employee` will be chunked using column
`empid` Running data dump using 4 threads. NOTE: Progress information uses estimated values and may not be
accurate. Data dump for table `sales`.`employee` will be written to 1 file 1 thds dumping - 100% (2 rows / ~2 rows), 0.00 rows/s, 12.00 B/s
uncompressed, 0.00 B/s compressed Duration: 00:00:03s Schemas dumped: 1 Tables dumped: 1 Uncompressed data size: 39 bytes Compressed data size: 0 bytes Compression ratio: 39.0 Rows written: 2 Bytes written: 0 bytes Average uncompressed throughput: 10.61 B/s Average compressed throughput: 0.00 B/s MySQL localhost:33060+ ssl Py > |
Import Dump file into Compute Instance from OCI Object Storage
|
util.loadDump("worlddump", {threads: 8, osBucketName:
"BootcampBucket", osNamespace:
"idazzjlcjqzj","ociConfigFile":"/root/.oci/config"
}) |
Verify The results
|
|
On-Premises Database:-
Conclusion:-
Migration happened successfully!!!
MySQL Shell 8.0.21 makes MySQL easier to use, by providing
an interactive MySQL client supporting SQL, Document Store, JavaScript &
Python interface with support for writing custom extensions.
And with dumpInstance(), dumpSchemas(), importTable() and
loadDump() MySQL Shell now provides
powerful logical dump and load functionality.
===========Rough Notes=========================================
So doing this Migration what are challenges has come? what are Error has occurred and most important how did you fix it up?
Let's have a look for all Error one by one...
|
Error#01:- Traceback (most recent call
last): File
"<string>", line 1, in <module> SystemError: RuntimeError: Util.dump_schemas:
Cannot open file: /root/.oci/config. Install OCI CLI in your local machine Installing
the CLI (document available on D:\Chandan\Cloud\OCI_Notes) https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm |
|
Error:#02- MySQL localhost:33060+ ssl Py >
util.dump_schemas(["sales"], "salesdump",
{"osBucketName": "BootcampBucket",
"osNamespace": "idazzjlcjqzj", "ocimds": "true",
"compatibility": ["strip_definers",
"strip_restricted_grants"]}) Traceback (most recent call last): File
"<string>", line 1, in <module> SystemError: RuntimeError: Util.dump_schemas: Failed to get object list using prefix 'salesdump/': The required
information to complete authentication was not provided. (401) |
|
Fix:- util.dump_schemas(["sales"], "worlddump",
{"osBucketName": "dumpbucket-2", "osNamespace":
"idazzjlcjqzj", "ocimds": "true",
"compatibility": ["strip_definers",
"strip_restricted_grants"]}) |
|
Error#03 MySQL localhost:33060+
ssl Py > util.dump_schemas(["sales"],
"worlddump", {"osBucketName": "BootcampBucket",
"osNamespace": "idazzjlcjqzj", "ocimds":
"true","ociConfigFile":"/root/.oci/config",
"compatibility": ["strip_definers",
"strip_restricted_grants"]}) Traceback (most recent call last): File
"<string>", line 1, in <module> SystemError: RuntimeError: Util.dump_schemas:
Failed to list multipart uploads: Either the bucket named 'BootcampBucket'
does not exist in the namespace 'idazzjlcjqzj' or you are not authorized to
access it (404) |
|
Fix:- make
sure you have set policies Allow
group chandangroup to read buckets in compartment chandankumar-sandbox Allow
group chandangroup to manage objects in compartment chandankumar-sandbox
where any {request.permission='OBJECT_CREATE', request.permission='OBJECT_INSPECT'} Allow
group chandangroup to read objects in compartment chandankumar-sandbox
|
|
Error#04
MySQL
localhost:33060+ ssl JS > util.dumpSchemas(["sales"],
"worlddump", {"osBucketName": "BootcampBucket",
"osNamespace": "idazzjlcjqzj", "ocimds": "true","ociConfigFile":"/root/.oci/config",
"compatibility": ["strip_definers",
"strip_restricted_grants"]}) Checking for
compatibility with MySQL Database Service 8.0.21 NOTE: Database sales
had unsupported ENCRYPTION option commented out Compatibility issues
with MySQL Database Service 8.0.21 were found and repaired. Please review the
changes made before loading them. Acquiring global read
lock All transactions have
been started Locking instance for
backup Global read lock has
been released Writing global DDL
files Preparing data dump
for table `sales`.`employee` Writing DDL for schema
`sales` Writing DDL for table
`sales`.`employee` WARNING: Could not
select a column to be used as an index for table `sales`.`employee`. Chunking
has been disabled for this table, data will be dumped to a single file. Running data dump
using 4 threads. NOTE: Progress
information uses estimated values and may not be accurate. Data dump for table
`sales`.`employee` will be written to 1 file ERROR: [Worker001]: Failed to rename object 'worlddump/sales@employee.tsv.zst.dumping' to
'worlddump/sales@employee.tsv.zst': Either the bucket named 'BootcampBucket'
does not exist in the namespace 'idazzjlcjqzj' or you are not authorized to
access it (404) Util.dumpSchemas: Fatal error during dump
(RuntimeError) |
|
Fix Make
sure you have given Rename Permission/policies to objects
Allow group chandangroup to manage objects
in compartment chandankumar-sandbox where any
{request.permission='OBJECT_CREATE', request.permission='OBJECT_INSPECT', request.permission='OBJECT_OVERWRITE',request.permission='OBJECT_DELETE'} |
|
Final
Policies:- go to OCI console --> Identity-->Policies--> add Policy
|
Thank you for using MySQL!!!
Please test and let us know your feedback...
Share your feedback on improvement on my blog ,thank you!
