[ Part 4 ] Data transfer & GTID purging

Steps to be implemented

• Dumping data from MySQL source into Bucket

• Save the @.json object

• Loading data from Bucket into MySQL DB system

• GTID purging

Following steps are implemented on the mysqlshellinstance host

Make sure you save the namespace of your bucket (in a notepad, etc). You will need it for steps 1.5 and 3.2.

You can find your namespace bucket with following command:

root@mysqlshellinstance:/home# oci os ns get
{
  "data": "YourNamespaceBucket"
}
root@mysqlshellinstance:/home# 

[ 1 ] Dumping data from MySQL source into Object Storage (mdsbucket)

[ 1.1 ] Download sql script test1.sql under a location from where you will start the MySQL Client (in this example, we will implement the next steps under /home folder):

root@mysqlshellinstance:/# cd /home
root@mysqlshellinstance:/home# wget https://raw.githubusercontent.com/isaac-kami/MdsReplication/main/test1.sql   

[ 1.2 ] Access MySQL client that is already configured on mysqlshellinstance from the same location you saved the test1.sql script:

root@mysqlshellinstance:/home# mysql -uroot -pabc123!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)

[ ... snip ... ]

mysql> 

[ 1.3 ] Execute the test1.sql with "source" command for creating the test database:

mysql> source test1.sql
Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

mysql> 

[ 1.4 ] Check if database and the populated tablespace are created:

mysql> select * from testexample.exampletable;
+--------+-----------+
| name   | firstname |
+--------+-----------+
| Foster | Zack      |
+--------+-----------+
1 row in set (0.00 sec)

mysql>
mysql> exit
Bye
root@mysqlshellinstance:/home# 

[ 1.5 ] Access mysql-shell from localhost to dump data into mdsbucket with dumpInstance() utility:

root@mysqlshellinstance:/home#  mysqlsh -uroot -pabc123!
MySQL Shell 8.0.23

Copyright (c) 2016, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 16 (X protocol)
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
No default schema selected; type \use <schema> to set one.
 MySQL  localhost:33060+ ssl  JS > 

...using dumpInstance() utility:

 MySQL  localhost:33060+ ssl  JS > var namespacebucket = "here your namespace"
 MySQL  localhost:33060+ ssl  JS > util.dumpInstance("mdsobject", {osBucketName: "mdsbucket", osNamespace: namespacebucket, threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"]})    

When data dump transfer is done, quit the MySQL-Shell:

MySQL  localhost:33060+ ssl  JS > \q
Bye!
root@mysqlshellinstance:/home# 

[ 2 ] Save @.json file

[ 2.1 ] With OCI CLI, check if object @.json exists in mdsbucket:

oci os object list \
--bucket-name mdsbucket |\
grep "@.json"

Possible output

root@mysqlshellinstance:/home# oci os object list \
> --bucket-name mdsbucket |\
> grep "@.json"
      "name": "mdsobject/@.json",
root@mysqlshellinstance:/home# 

[ 2.2 ] If present, download @.json object with following OCI command:

oci os object get \
 --bucket-name mdsbucket \
 --file @.json \
 --name mdsobject/@.json

Possible output:

root@mysqlshellinstance:/home# oci os object get \
>  --bucket-name mdsbucket \
>  --file @.json \
>  --name mdsobject/@.json
Downloading object  [####################################]  100%
root@mysqlshellinstance:/home# ls -ltr @.json 
-rw-r--r-- 1 root root 817 Apr 18 16:33 @.json
root@mysqlshellinstance:/home# 

[ 3 ] Loading data from Bucket into MySQL DB system

[ 3.1 ] From the same location where you saved the @.json file, you must access the MDS through the mysql-shell. This time, you will be using the utility loadDump().

root@mysqlshellinstance:/home# ls -ltr @*
-rw-r--r-- 1 root root 817 Apr 18 16:33 @.json
root@mysqlshellinstance:/home#
root@mysqlshellinstance:/home# mysqlsh -uusermds -h10.0.1.5 -pABCabc123$%
MySQL Shell 8.0.23

[ ... SNIP ...]

 MySQL  10.0.1.5:33060+ ssl  JS > 

[ 3.2 ] Apply loadDump() utility:

 MySQL  10.0.1.5:33060+ ssl  JS > var nsbucket = "y0urnamespacehere"
 MySQL  10.0.1.5:33060+ ssl  JS > util.loadDump("mdsobject", {osBucketName: "mdsbucket", osNamespace: nsbucket, threads: 4})   

[ 3.3 ] Change to SQL mode:

MySQL  10.0.1.5:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;
MySQL  10.0.1.5:33060+ ssl  SQL > 

... and check if the database has been migrated to MDS:

MySQL  10.0.1.5:33060+ ssl  SQL > select * from testexample.exampletable;
+--------+-----------+
| name   | firstname |
+--------+-----------+
| Foster | Zack      |
+--------+-----------+
1 row in set (0.0005 sec)
MySQL  10.0.1.5:33060+ ssl  SQL > 

If migration has been implemented successfully, it is time to purge the GTID.

[ 4 ] GTID Purging

[ 4.1 ] Directly from SQL mode in mysql-shell, extract the gtidExecuted field from @.json file:

 MySQL  10.0.1.5:33060+ ssl  SQL > \! cat @.json | grep gtidExecuted
    "gtidExecuted": "67651f74-a04b-11eb-aa1f-020017064c9a:1-10",
    "gtidExecutedInconsistent": false,  

[ 4.2 ] Perform GTID purging in SQL mode with following command (obviously, our gtidExecuted value will be different)

call sys.set_gtid_purged("67651f74-a04b-11eb-aa1f-020017064c9a:1-10");
                          

[ 4.3 ] Perform checking

 MySQL  10.0.1.5:33060+ ssl  SQL > show global variables like 'GTID_EX%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                               |
+----------------------------------+-------------------------------------------------------------------------------------+
| gtid_executed                    | 67651f74-a04b-11eb-aa1f-020017064c9a:1-10,
e8a2b443-a04b-11eb-924d-02001702b29c:1-3 |
| gtid_executed_compression_period | 0                                                                                   |
+----------------------------------+-------------------------------------------------------------------------------------+
2 rows in set (0.0021 sec)
 MySQL  10.0.1.5:33060+ ssl  SQL > 

... and quit the MySQL-Shell:

MySQL  10.0.1.5:33060+ ssl  SQL > \q
Bye!
root@mysqlshellinstance:/home#