Monday, September 14, 2020

How to Migrate On-premises MySQL Database to MySQL Database ServiceMDS ?

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: 

SSH ppk file

User:- opc

MDS Private IP

Address:- 10.0.0.xx

 

 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