Steps to migrate a MySQL DB to MySQL HeatWave Database service in OCI - nsojunior/mysql-general GitHub Wiki
hello!
today we are going to migrate a MySQL Database to OCI!
The source database may be on-premises or at any other cloud service that offers a fork of MySQL as managed services, for instances AWS RDS or Aurora, Google CloudSQL, Azure Database for MySQL and several others.
In order to star we need:
- Source MySQL database provisioned, in this case I am using AWS RDS
- Target MySQL HeatWave service provisioned in OCI
- Compute instance (VM) provisioned with Oracle Linux 8 or any other Linux compatible with mysql-shell, for more info -> Download MySQL Shell
- Create bucket in OCI to store the exported DB, you can that at OCI Console -> Main menu -> Storage -> Buckets
- Install mysql-shell on compute instance
sudo yum install -y mysql-shell
- Set up secure access from compute instance to OCI to allow mysql-shell to connect to the buckets services
- Create .oci/config file with OCI credentials found in OCI Console -> Profile menu -> User settings -> API Keys
- Copy the API Key's private key created in previous step to the compute instance
- Connect to the source MySQL using mysql-shell
mysqlsh <user>@<host>
and provide the password requested.- for the demo:
mysqlsh [email protected]
- for the demo:
- Check if the db is available in RDS running this command in mysql-shell
\sql show databases;
- Run the following command to dump the data to the bucket:
util.dumpSchemas(["<schema>"], "<bucket-folder>", {dryRun:<true|false>, consistent:<true|false>, threads: <threads-count>, ociConfigFile: "<oci-config-file-path>", osBucketName: "<bucket-name>", osNamespace: "<namespace-name>", ocimds: true, compatibility: ["strip_definers", "strip_restricted_grants", "create_invisible_pks", "force_innodb"]})
- Find more details at 11.5 Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
- For the demo:
util.dumpSchemas(["world"], "world-db-folder", {dryRun:true, consistent:false, threads: 8, ociConfigFile: "/home/opc/.oci/config", osBucketName: "bucket-rds2mysql", osNamespace: "idazzjlcjqzj", ocimds: true, compatibility: ["strip_definers", "strip_restricted_grants", "create_invisible_pks", "force_innodb"]})
- Make sure the private subnet has the ports 3306 and 33060 open in its security list, to fnd the private subnet select click on your VCN at OCI Console -> Main Menu -> Networking -> Virtual Cloud Networks
- On mysql-shell, disconnect from RDS and connect to MySQL HeatWave service in OCI issuing the command
\c <user>@<mysql-heatwave-private-ip>
- For the demo:
\c [email protected]
- For the demo:
- Check the DBs available in MySQL HeatWave as well using mysql-shell
\sql show databases;
- Let's load the dump using this command in mysql-shell
util.loadDump("<bucket-folder>", {resetProgress:true, ignoreVersion:true, dryRun:<true|false>, threads: <threads-count>, ociConfigFile: "<oci-config-file-path>", osBucketName: "<bucket-name>", osNamespace: "<namespace-name>"})
- Find more details on MySQL Shell 8.0 :: 11.6 Dump Loading Utility
- For the demo:
util.loadDump("world-db-folder", {resetProgress:true, ignoreVersion:true, dryRun:true, threads: 32, ociConfigFile: "/home/opc/.oci/config", osBucketName: "bucket-rds2mysql", osNamespace: "idazzjlcjqzj"})
- Check database loaded using mysql-shell
\sql show databases
These are the steps to migrate a MySQL DB to OCI and take advantages of all the benefits just Oracle can offer for your MySQL!
Cheers!
Narciso