How to Migrate MySQL from On-premises into MySQL Database Service (MDS) ?

Guide to Migrate Production MySQL Database running on On-premises to Oracle Cloud MySQL Database Service(MDS) ?

I have one production database which i would like to migrate into Oracle Cloud PaaS model i.e MySQL Database Service , we will walk through on how to migrate customerDB database from On-premises to Oracle Cloud MySQL Database Service(MDS).

Note:- Recommended is to have MySQL DB On-Premises and MDS  both must have same version i.e 8.0 and Utility shell MySQL shell must be 8.0.21 onwards.

make sure app is disconnected and not allowing any traffic during the migration process.

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

How does Migration Work ?

Suppose you wanted to do lift-shift of database called “customerDB” , so “Schema Dump” utility will export customerDB database from on-premises to OCI(oracle cloud infrastructure) object storage.

Then “Load Dump” utility will import directly to MySQL Database Service running in Oracle Cloud as PAAS model.

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.       MDS 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.

How to deploy MDS ?

Below blog will help us to create MDS instance:-

 Additional Details

On-Premises Instance Details	Oracle Cloud Compute Instance Details	MDS Details
Database Name:- CustomerDB IP:- 192.168.1.10 User:- root Port: 3306 DB Size ~ 1.4 GB No of Tables: 800	Public IP Address: 140.238.227.xx SSH ppk file User:- opc	MDS Private IP Address:- 10.0.0.xx MySQL Username:- admin MySQL Port: 3306

 Command to Export the backup from On-premises to OCI Object Storage

Open MySQL Shell and connect to OnP MySQL Instance then execute below commands

MySQL  JS > shell.connect("root@localhost"); Creating a session to 'root@localhost' Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 28 (X protocol) Server version: 8.0.21-commercial MySQL Enterprise Server - Commercial No default schema selected; type \use <schema> to set one. <Session:root@localhost:33060>  MySQL  localhost:33060+ ssl  JS >\py

Check for Compatibility issues and gather the problems in advance before migration by using DRYRUN

MySQL  localhost:33060+ ssl  JS > util.dumpSchemas(["CustomerDB"], "CustomerDBdump", {dryRun: true, ocimds: true})

O/P MySQL  localhost:33060+ ssl  JS > util.dumpSchemas(["CustomerDB"], "CustomerDBdump", {dryRun: true, ocimds: true}) Checking for compatibility with MySQL Database Service 8.0.21 NOTE: Database CustomerDB had unsupported ENCRYPTION option commented out ERROR: Function CustomerDB.fn_emailcheck - definition uses DEFINER clause set to user `abc`@`` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges ERROR: Function fn_stockopengapup - definition uses DEFINER clause set to user `abc`@`` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges ERROR: Trigger CustomerDB.DATA_ID - definition uses DEFINER clause set to user ` abc `@`` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges ERROR: Procedure sp_checkUserComplaince - definition uses DEFINER clause set to user `abc`@`` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges ERROR: View CustomerDB.performanceInfo - definition uses DEFINER clause set to user `abc`@`` which can only be executed by this user or a user with SET_USER_ID or SUPER privileges Compatibility issues with MySQL Database Service 8.0.21 were found. Please use the 'compatibility' option to apply compatibility adaptations to the dumped DDL. Util.dumpSchemas: Compatibility issues were found (RuntimeError)  MySQL  localhost:33060+ ssl  JS >

FIX:- No action from you , just use  “"compatibility": ["strip_definers", "strip_restricted_grants"]}” option Step#01  CODE to Export from On-P to Object Storage

MySQL  localhost:33060+ ssl  JS > util.dumpSchemas(["CustomerDB"],"CustomerDBdump",{threads:30,"osBucketName": "chandanBucket", "osNamespace": "idazzjlcjqzj",  "ocimds": "true","ociConfigFile":"/root/.oci/config", "compatibility": ["strip_definers", "strip_restricted_grants"]})

Output

Step#02  CODE to Import from  Object Storage to MySQL Database Service

For import into a MySQL DB Service (MDS), the MySQL Shell instance where you run the dump loading utility must be installed on an Oracle Cloud Infrastructure Compute instance that has access to the MySQL DB System.

Let’s connect Oracle Cloud Compute Instance ,a nd check whether MDS Instance you are able to connect or not ?

How to connect Compute Instance- open Putty and Public IP into it , see below screen shot.

How to Deploy MDS Instance ? Below is my another blog which will help us to get through.

https://mysqlsolutionsarchitect.blogspot.com/2020/09/how-to-launch-mysql-database-service.html

Let’s Connect MDS instance ,

sudo su root mysqlsh -h10.0.0.13 -uadmin -pXXXXXX

DRY RUN scripts:-

MySQL  localhost:33060+ ssl  JS > util.loadDump("CustomerDBdump",{dryRun: true, osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config"})

Suppose I wanted to push to MDS instance , so for that you needed to connect first with MySQL Shell, then execute LoadDump commands, make sure OCI CLI is installed over here.

Code to Import into MDS

util.loadDump("CustomerDBDump", {threads: 60, osBucketName: "chandanBucket", osNamespace: "idazzjlcjqzj","ociConfigFile":"/root/.oci/config" })

Finally Import Has been done successfully.

Let’s Verify The results:- 

Connect to MDS and check whether customerDB database has been migrated or not?

On-Premises Database:-

List of Errors and fixes during whole migration Exercise.

Error#01:- Traceback (most recent call last):   File "<string>", line 1, in <module> SystemError: RuntimeError: Util.dump_schemas: Cannot open file: /root/.oci/config.   Fix:- 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  JS util.dumpSchemas(["CustomerDB"], "CustomerDBdump", {"osBucketName": "chandanBucket", "osNamespace": "idazzjlcjqzj",  "ocimds": "true","ociConfigFile":"/root/.oci/config", "compatibility": ["strip_definers", "strip_restricted_grants"]}) Util.dumpSchemas: Failed to get object list using prefix 'CustomerDBdump/': Either the bucket named 'chandanBucket' does not exist in the namespace 'idazzjlcjqzj' or you are not authorized to access it (404) (RuntimeError)  MySQL  localhost:33060+ ssl  JS >

 Fix:-

look for connectivity from OnP to Oracle cloud object storage, below links will be helpful https://docs.cloud.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm  #oci os ns get

Error#03 MySQL  localhost:33060+ ssl  Py util.dumpSchemas(["CustomerDB"], "CustomerDBdump", {"osBucketName": "chandanBucket", "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 'chandanBucket' 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(["CustomerDB"], "CustomerDBdump", {"osBucketName": "chandanBucket", "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:-

Error#06

Fix:- Re-login with root user:-

sudo su root mysqlsh -h10.0.0.13 -uadmin -p

Conclusion:-

Migration happened successfully!!!

MySQL Database Service is a fully managed database service that enables organizations to deploy cloud-native applications using the world's most popular open source database. It is 100% developed, managed and supported by the MySQL Team.

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.