How to migrate your data from the MySQL Database Service (MDS) to MDS High Availability
March 31, 2021 Leave a comment
On March 31st, 2021, MySQL introduced a new MySQL Database Service (MDS) option named MDS High Availability (MDS H/A).
“The High Availability option enables applications to meet higher uptime requirements and zero data loss tolerance. When you select the High Availability option, a MySQL DB System with three instances is provisioned across different availability or fault domains. The data is replicated among the instances using a Paxos-based consensus protocol implemented by the MySQL Group Replication technology. Your application connects to a single endpoint to read and write data to the database. In case of failure, the MySQL Database Service will automatically failover within minutes to a secondary instance without data loss and without requiring to reconfigure the application. See the documentation to learn more about MySQL Database Service High Availability.” From: MySQL Database Service with High Availability
If you already have data in a MDS instance and you want to use the new MDS H/A option, you will need to move your data from your MDS instance to a new MDS H/A instance. This is a fairly easy process, but it will take some time depending upon the size of your data.
First, connect to the MDS instance via an OCI (Oracle Cloud Infrastructure) compute instance. Login to your compute instance:
ssh -i opc@Public_IP_Address
If you don’t have MySQL Shell installed, here are the instructions. Execute these commands from your compute instance: (answer “y” or “yes” to each prompt)
Note: I am not going to show the entire output from each command.
sudo yum install –y mysql80-community-release-el7-3.noarch.rpm sudo yum install –y mysql-shell
Connect to the MySQL Shell, using the IP address of your MDS instance. You will need to enter the user name and password for the MDS instance user.
mysqlsh -uadmin -p -h
Change to the JavaScript mode with /js (if you aren’t already in JavaScript mode):
shell-sql>/js
You can dump individual tables, or the entire instance at once. Check the manual for importing data into MDS for more information. The online manual page – Instance Dump Utility, Schema Dump Utility, and Table Dump Utility – will provide you with more details on the various options. For this example, I am going to dump the entire instance at once, into a file named “database.dump“. Note: The suffix of the file doesn’t matter.
shell-js>util.dumpInstance("database.dump", { })
You will see output similar to this (which has been truncated):
Acquiring global read lock Global read lock acquired Gathering information - done All transactions have been started Locking instance for backup Global read lock has been released Checking for compatibility with MySQL Database Service 8.0.23 ... Schemas dumped: 28 Tables dumped: 264 Uncompressed data size: 456.56 MB Compressed data size: 365.24 MB Compression ratio: 5.4 Rows written: 47273 Bytes written: 557.10 KB Average uncompressed throughput: 3.03 MB/s Average compressed throughput: 557.10 KB/s
Quit the MySQL Shell with the “\q” command.
I can check the dump file:
[opc@mds-client ~]$ ls -l total 760 drwxr-x---. 2 opc opc 365562813 Mar 31 19:07 database.dump
Connect to the new MDS H/A instance.
ssh -i opc@Public_IP_Address
Start MySQL Shell again:
mysqlsh -uadmin -p -h
You will use the MySQL Shell Dump Loading Utility to load the data. For more information – see the Dump Loading Utility manual page.
You can do a dry run is to check that there will be no issues when the dump files are loaded from a local directory into the connected MySQL instance: (Note: the output is truncated)
util.loadDump("database.dump", {dryRun: true})
Loading DDL and Data from 'database.dump' using 4 threads.
Opening dump...
dryRun enabled, no changes will be made.
....
No data loaded.
0 warnings were reported during the load.
There are many options for loading your data. Here, I am going to just load the entire dump file. If you have problems, you can use the Table Export Utility and export individual tables. You might want to export and import larger tables on their own. I only need to specify my dump file, and the number of threads I want to use. (Note: the output is truncated)
util.loadDump("database.dump", { threads: 8 })
Loading DDL and Data from 'database.dump' using 8 threads.
Opening dump...
Target is MySQL 8.0.23-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-u2-cloud
Checking for pre-existing objects...
Executing common preamble SQL
...
0 warnings were reported during the load.
After the data has been loaded, you will want to double-check the databases and tables in the MDS H/A instance, as well as their sizes, by comparing them to the MDS instance.
That’s it. Moving your data from a MDS instance to a MDS H/A instance is fairly easy.
Note: You will need to change the IP address of your application to point to the new MDS H/A instance.
 |
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn. |
 |
Tony is the author of Twenty Forty-Four: The League of Patriots Visit http://2044thebook.com for more information. |
 |
Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition Visit https://amzn.to/2oPFLI0 for more information. |
info [at] Scripting
Scripting MySQL 