Migrate from on premise MySQL to MySQL Database Service

This post was first published on Oracle MySQL Blog.

If you are running MySQL on premise, it’s maybe the right time to think about migrating your lovely MySQL database somewhere where the MySQL Team prepared a comfortable place for it to stay running and safe.

This awesome place is MySQL Database Service in OCI. For more information about what MDS is and what it provides, please check this blog from my colleague Airton Lastori.

One important word that should come to your mind when we talk about MDS is SECURITY !

Therefore, MDS endpoint can only be a private IP in OCI. This means you won’t be able to expose your MySQL database publicly on the Internet.

Now that we are aware of this, if we want to migrate an existing database to the MDS, we need to take care of that.

What is my case ?

When somebody needs to migrate its actual MySQL database, the first question that needs to be answered is: Can we eventually afford large downtime ?

If the answer is yes, then the migration is easy:

• you stop your application(s)
• you dump MySQL
• you start your MDS instance
• you load your data into MDS

and that’s it !

In case the answer is no, things are of course more interesting and this is the scenario I will cover in this post.

Please note that the application is not covered in this post and of course, it’s also recommended to migrate it to the cloud, in a compute instance of OCI for example.

What’s the plan ?

To migrate successfully a MySQL database from on premise to MDS, these are the actions I recommend:

1. create a VCN with two subnets, the public and the private one
2. create a MDS instance
3. create a VPN
4. create an Object Storage Bucket
5. dump the data to be loaded in MDS
6. load the data in MDS
7. create an in-bound replication channel in MDS

The architecture will look like this:

Virtual Cloud Network

First thing to do when you have your OCI access, it’s to create a VCN from the dashboard. If you have already created some compute instances, these steps are not required anymore:

You can use Start VCN Wizard, but I will cover the VPN later in this article. So let’s just use Create VCN.

We need a name and a CIDR Block, we use 10.0.0.0/16:

This is what it looks like:

Now we click on the name (lefred_vcn in my case) and we need to create two subnets:

We will create the public one on 10.0.0.0/24:

and the Private one on 10.0.1.0/24.

After these two steps, we have something like this:

MySQL Database Service Instance

We can create a MDS instance:

And we just follow the creation wizard that is very simple:

It’s very important to create an admin user (the name can be what you want) and don’t forget the password. We put our service in the private subnet we just created.

The last screen of the wizard is related to the automatic backups:

The MDS instance will be provisioned after a short time and you can see that in its detailed view:

VPN

OCI allows you to very easily create IPSEC VPN’s with all enterprise level hardware that are used in the industry. Unfortunately I don’t have such opportunity at home (and no need for it), so I will use another supported solution that is more appropriate for domestic usage: OpenVPN.

If you are able to deploy the IPSEC solution, I suggest you to use it.

On that new page, you have a link to the Marketplace where you can deploy a compute instance to act as OpenVPN server:

You need to follow the wizard and make sure to use the vcn we created and the public subnet:

The compute instance will be launched by Terraform. When done we will be able to reach the OpenVPN web interface using the public IP that was assigned to this compute instance using the credentials we entered in the wizard:

In case you lost those logs, the ip is available in the Compute->Instances page:

As soon as the OpenVPN instance is deployed, we can go on the web interface and setup OpenVPN:

As we want be able to connect from our MDS instance to our on-premise MySQL server for replication, we will need to setup our VPN to use Routing instead of NAT:

We also specified two ranges as we really want to have a static IP for our on-premise MySQL Instance, otherwise, the IP might change the next time we connect to the VPN.

The next step is the creation of a user we will use to connect to the VPN:

These settings are very important:

Save the settings and click on the banner to restart OpenVPN.

Now, we connect using the user we created to download his profile:

That client.ovpn file needs to be copied to the on-premise MySQL Server.

If OpenVPN is not yet installed on the on-premise MySQL Server, it’s time to install it (yum install openvpn).

Now, we copy the client.ovpn in /etc/openvpn/client/ and we call it client.conf:

# cp client.ovpn /etc/openvpn/client/client.conf

We can start the VPN:

# systemctl start openvpn-client@client
Enter Auth Username: lefred
Enter Auth Password: ******

We can verify that the VPN connection is established:

# ifconfig tun0
tun0: flags=4305 mtu 1500
inet 172.27.232.134 netmask 255.255.255.0 destination 172.27.232.134
inet6 fe80::9940:762c:ad22:5c62 prefixlen 64 scopeid 0x20

unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 txqueuelen 100 (UNSPEC)
RX packets 1218 bytes 102396 (99.9 KiB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 1287 bytes 187818 (183.4 KiB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0

sysemctl status openvpn-client@client can also be called to see the status.

Object Storage

To transfer our data to the cloud, we will use Object Storage.

And we create a bucket:

Dump the data

To dump the data of our on-premise MySQL server, we will use MySQL Shell that has the capability to Load & Dump large datasets in an optimized and compatible way for OCI since 8.0.21.

Please check those links for more details:

• https://docs.cloud.oracle.com/en-us/iaas/mysql-database/doc/importing-and-exporting-databases.html
• https://mysqlserverteam.com/mysql-shell-dump-load-part-1-demo/
• https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/
• https://mysqlserverteam.com/mysql-shell-dump-load-part-3-load-dump/
• https://mysqlserverteam.com/mysql-shell-8-0-21-speeding-up-the-dump-process/

OCI Config

The first step is to create an OCI config file that will look like this:

[DEFAULT]
user=ocid1.user.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
fingerprint=xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx:xx
key_file=/home/lefred/oci_api_key.pem
tenancy=ocid1.tenancy.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
compartment=ocid1.compartment.oc1..xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
region=us-ashburn-1

The user information and key can be found under the Identity section:

Please refer to this manual page to generate a PEM key.

Now that we have an oci config file (called oci.config in my case), we need to verify that our current MySQL server is using GTID:

on-premise mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| OFF         |
+-------------+
1 row in set (0.00 sec)

By default GTID mode is disabled and we need to enable it. To be able to perform this operation without restarting the MySQL instance, this is how to proceed:

on-premise mysql> SET PERSIST server_id=1;
on-premise mysql> SET PERSIST enforce_gtid_consistency=true;
on-premise mysql> SET PERSIST gtid_mode=off_permissive;
on-premise mysql> SET PERSIST gtid_mode=on_permissive;
on-premise mysql> SET PERSIST gtid_mode=on;
on-premise mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+

Routing & Security

We need to add some routing and firewall rules to our VCN to allow the traffic from and to the VPN.

Please note that 10.0.0.11 is the private IP of the OpenVPN Compute Instance.

On the Default Security List for lefred_vcn we also need to allow connections to MySQL ports. We will use them later to connect to the on-premise MySQL Server from our MDS instance:

Now that we dealt with routing and security, it’s time to dump the data to Object Store by connecting MySQL Shell to our on-premise server and use util.dumpInstance():

$ mysqlsh
MySQL JS > \c root@localhost
[...]
MySQL localhost:33060+ ssl JS > util.dumpInstance('onpremise', {ociConfigFile: "oci.config",
 osBucketName: "lefred_bucket", osNamespace: "xxxxxxxxxxxx",threads: 4, 
ocimds: true, compatibility: ["strip_restricted_grants", "strip_definers"]})

You can also find more info on this MDS manual page.

Load the data in MDS

The data is now already in the cloud and we need to load it in our MDS instance.

We first connect to our MDS instance using Shell. We could use a compute instance in the public subnet or the VPN we created. I will use the second option:

MySQL localhost:33060+ ssl JS > \c admin@10.0.1.11
Creating a session to 'admin@10.0.1.11'
Fetching schema names for autocompletion… Press ^C to stop.
Closing old connection…
Your MySQL connection id is 283 (X protocol)
Server version: 8.0.21-u1-cloud MySQL Enterprise - Cloud
No default schema selected; type \use to set one.

It’s time to load the data from Object Storage to MDS:

MySQL 10.0.1.11:33060+ ssl JS > util.loadDump('onpremise', {ociConfigFile: "oci.config",
osBucketName: "lefred_bucket", osNamespace: "xxxxxxxxxxxx",threads: 4})
Loading DDL and Data from OCI ObjectStorage bucket=lefred_bucket, prefix='onpremise' 
using 4 threads.
Target is MySQL 8.0.21-u1-cloud. Dump was produced from MySQL 8.0.21
Checking for pre-existing objects…
Executing common preamble SQL
Executing DDL script for schema employees
Executing DDL script for employees.departments
Executing DDL script for employees.salaries
Executing DDL script for employees.dept_manager
Executing DDL script for employees.dept_emp
Executing DDL script for employees.titles
Executing DDL script for employees.employees
Executing DDL script for employees.current_dept_emp
Executing DDL script for employees.dept_emp_latest_date
[Worker002] employees@dept_emp@@0.tsv.zst: Records: 331603 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] employees@dept_manager@@0.tsv.zst: Records: 24 Deleted: 0 Skipped: 0 Warnings: 0
[Worker003] employees@titles@@0.tsv.zst: Records: 443308 Deleted: 0 Skipped: 0 Warnings: 0
[Worker000] employees@employees@@0.tsv.zst: Records: 300024 Deleted: 0 Skipped: 0 Warnings: 0
[Worker002] employees@departments@@0.tsv.zst: Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
[Worker001] employees@salaries@@0.tsv.zst: Records: 2844047 Deleted: 0 Skipped: 0 Warnings: 0
Executing common postamble SQL
6 chunks (3.92M rows, 141.50 MB) for 6 tables in 1 schemas were loaded in 
5 min 28 sec (avg throughput 431.39 KB/s)
0 warnings were reported during the load.

We still need to set the GTID purged information from when the dump was taken.

In MDS, this operation can be achieved calling a dedicated procedure called sys.set_gtid_purged()

Now let’s find the value we need to add there. The value of GTID executed from the dump is written in the file @.json . This file is located in Object Storage and we need to retrieve it:

When you have the value of gtidExecuted in that file you can set it in MDS:

MySQL 10.0.1.11:33060+ ssl 
SQL > call sys.set_gtid_purged("ae82914d-e096-11ea-8a7a-08002718d305:1")

In-bound Replication

Before stopping our production server running MySQL on premise, we need to resync the data. We also need to be sure we have moved everything we need to the cloud (applications, etc…) and certainly run some tests. This can take some time and during that time we want to keep the data up to date. We will then use replication from on-premise to MDS.

Replication user creation

On the production MySQL (the one still running on the OCI compute instance), we need to create a user dedicated to replication:

mysql> CREATE USER 'repl'@'10.0.1.%' IDENTIFIED BY 'C0mpl1c4t3d!Paddw0rd' REQUIRE SSL; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.1.%';

Creation of the replication channel

We go back on OCI’s dashboard and in our MDS instance’s details page, we click on Channels:

We now create a channel and follow the wizard:

We use the credentials we just created and as hostname we put the IP of our OpenVPN client: 172.27.232.134

After a little while, the channel will be created and in MySQL Shell when connected to your MDS instance, you can see that replication is running.

Wooohooo it works ! \o/

Conclusion

As you can see, transferring the data and creating a replication channel from on-premise to MDS is easy. The most complicated part is the VPN and dealing with the network, but straightforward for a sysadmin. This is a task that you have to do only once and it’s the price to pay to have a more secure environment.