Fixed MySQL source - MDS replication

For the reader: This is a fast implementation (a few clicks here and there) ; the code for these next steps will be provided soon.

As I mentioned in the "Channels", at last section "...more troubleshooting", wht I was looking for was found at Official documentation Import and Exporting Databases (Compatibility Between On-Premise MySQL and MySQL Database Service section) and I quote:

strip_restricted_grants: certain privileges are restricted in the MySQL Database Service. Privileges such as RELOAD, FILE, SUPER, BINLOG_ADMIN, and SET_USER_ID. It is not possible to create users granting these privileges. This option strips these privileges from dumped GRANT statements.

MySQL Shell provides the following utilities:

• dumpInstance(): MySQL instance export utility which exports all compatible schemas to an Object Storage bucket or to local files. By default, this utility exports users, events, routines, and triggers.

[ ... snip ... ]

• loadDump(): an import utility which imports schemas to a DB System. For more information, see MySQL Shell Dump Loading Utility

and ...

This task describes how to export data from a supported MySQL Server source to an Object Storage bucket using the MySQL Shell dumpInstance utility.This task requires the following:

• MySQL Shell 8.0.21, or higher.

• MySQL Server 5.7.9, or higher.

• Access to Object Storage and an existing bucket.

[.... snip ... ]

This task describes how to import data from Object Storage to a MySQL DB System.This task requires the following:

• A Compute instance with access to the target MySQL DB System. This example assumes you are using a Compute running Oracle Linux.

• MySQL Shell 8.0.21, or higher, installed on the Compute instance.

• Access to Object Storage and an existing bucket which contains the exported files.

-- source Exporting Data to Object Storage with MySQL Shell (Import and Exporting Databases)

So, let's start from there - deploy an Object Storage, and perform dumping and loading data between MySQL source and MDS target.

But first...

Adapting the Terraform code

This troubleshooting and fix was be implemented without the creation of the channel.

Therefore, the "create_channel.tf" will no longer be necessary (nor the channel variables from variables.tf):

root@deploymentmachine:/home/bucket_test# tree .
.
├── ansible.cfg
├── compartment.tf
├── create_mysql.tf
├── data.tf
├── dhcp_opt.tf
├── instance.tf
├── int_gateway.tf
├── my.cnf
├── mysql.yml
├── output.tf
├── provider.tf
├── remote.tf
├── replication.yml
├── route.tf
├── security_list.tf
├── subnet.tf
├── variables.tf
└── vcn.tf

0 directories, 18 files

Once we adapt the code to the new scenario, we can build the environment as usual: terraform init, terraform plan and terraform apply.

This will create (as might have guessed it):

• the MySQL source on a compute instance (on which we installed and configured MySQL service with the help of ansible)

• the MySQL DB System

Deploy an Object Storage (OCI UI)

Code will be implemented for this ... for now we will be clicking for a quick check

As you are already aware of, those two instances are deployed in compartment "MySqlOciCompartment".

Let's create the Object Storage under the same compartment:

... sticking with the default...

... click create:

Manage your bucket

If you want to load or export files into/from your bucket, you would need the configuration file for OCI config under /root/.oci/config.

You can find the tutorial, step-by-step, at: Install and Configure OCI CLI

Keep in mind, that now you make the changes for the compartment where the MDS, MySQL source and Object bucket are located. This configuration file /root/.oci/config must exist on the instance that can access the MDS (in our case, it's the instance "MySqlShellInsance"

root@mysqlshellinstance:~# ls -ltr /root/.oci/
total 20
-rw-r--r-- 1 root root  293 Mar 21 14:59 details.txt
-rw-r--r-- 1 root root 1679 Mar 21 15:01 oci_api_private_key.pem
-rw-r--r-- 1 root root   48 Mar 21 15:01 oci_api_key_fingerprint
-rw-r--r-- 1 root root  451 Mar 21 15:02 oci_api_key_public.pem
-rw------- 1 root root  306 Mar 21 15:08 config
root@mysqlshellinstance:~#

Dumping data from MySQL Source into the Bucket

Change/provide root password on the MySQL Source:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc123!';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye!

Just for phun, create database and a table:

root@mysqlshellinstance:~# 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 23
Server version: 8.0.23-0ubuntu0.20.04.1 (Ubuntu)
[... snip ...]

mysql>

mysql> create database hehe;
Query OK, 1 row affected (0.01 sec)

mysql> create table hehe.hehehe (name varchar(20), firstname varchar(20));
Query OK, 0 rows affected (0.03 sec)

mysql>  insert into hehe.hehehe (name, firstname) values ('Foster', 'Zack');
Query OK, 1 row affected (0.02 sec)

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

mysql> exit;
Bye!

... setup the Javascript mode for MySQL-shell (apparently the snapd installation provides only python and sql modes)

root@mysqlshellinstance:~# # ========== remove mysql-shell =============
root@mysqlshellinstance:~# snap remove mysql-shell

[//snip//]

root@mysqlshellinstance:~# # ========== download package =============
root@mysqlshellinstance:~# wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell_8.0.23-1ubuntu20.04_amd64.deb

[/// snip ///]

2021-03-21 14:39:53 (8.10 MB/s) - ‘mysql-shell_8.0.23-1ubuntu20.04_amd64.deb’ saved [15422676/15422676]

root@mysqlshellinstance:~# # ========== install package =============
root@mysqlshellinstance:~# dpkg -i mysql-shell_8.0.23-1ubuntu20.04_amd64.deb
Selecting previously unselected package mysql-shell:amd64.
[...]
Setting up mysql-shell:amd64 (8.0.23-1ubuntu20.04) ...

root@mysqlshellinstance:~# # ========== perform updates =============
root@mysqlshellinstance:~#
root@mysqlshellinstance:~# apt update
root@mysqlshellinstance:~# 
root@mysqlshellinstance:~# #========== check js mode =============
root@mysqlshellinstance:~#
root@mysqlshellinstance:~# mysqlsh -uusermds -h10.0.1.4 -p
Please provide the password for 'usermds@10.0.1.4': ***********
Save password for 'usermds@10.0.1.4'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.23

[/// snip ///]

 MySQL  10.0.1.4:33060+ ssl  JS >
 MySQL  10.0.1.4:33060+ ssl  JS >

Now, we should be ready to start dumping the data into our bucket, without too many errors:

root@mysqlshellinstance:/home# mysqlsh -uroot -p
Please provide the password for 'root@localhost': *******
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No): N
MySQL Shell 8.0.23
[... snip.... ]
MySQL  localhost:33060+ ssl  JS > util.dumpInstance("test", {osBucketName: "bucket-20210321-1608",osNamespace: "add here namespace", threads: 4, ocimds: true, compatibility: ["strip_restricted_grants"], "consistent":"false"})
Gathering information - done
WARNING: The dumped value of gtid_executed is not guaranteed to be consistent
Checking for compatibility with MySQL Database Service 8.0.23
NOTE: User 'debian-sys-maint'@'localhost' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: User 'root'@'localhost' had restricted privileges (AUDIT_ADMIN, BACKUP_ADMIN, BINLOG_ADMIN, BINLOG_ENCRYPTION_ADMIN, CLONE_ADMIN, CREATE TABLESPACE, ENCRYPTION_KEY_ADMIN, FILE, FLUSH_OPTIMIZER_COSTS, FLUSH_STATUS, FLUSH_TABLES, FLUSH_USER_RESOURCES, GROUP_REPLICATION_ADMIN, INNODB_REDO_LOG_ARCHIVE, INNODB_REDO_LOG_ENABLE, PERSIST_RO_VARIABLES_ADMIN, PROXY, RELOAD, REPLICATION_SLAVE_ADMIN, ROLE_ADMIN, SERVICE_CONNECTION_ADMIN, SESSION_VARIABLES_ADMIN, SET_USER_ID, SHOW_ROUTINE, SHUTDOWN, SUPER, SYSTEM_USER, SYSTEM_VARIABLES_ADMIN, TABLE_ENCRYPTION_ADMIN) removed
NOTE: Database hehe had unsupported ENCRYPTION option commented out
Compatibility issues with MySQL Database Service 8.0.23 were found and repaired. Please review the changes made before loading them.
Writing global DDL files
Writing users DDL
Writing DDL for table `hehe`.`hehehe`
Writing DDL for schema `hehe`
Preparing data dump for table `hehe`.`hehehe`
Data dump for table `hehe`.`hehehe` will be chunked using column `id`
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Data dump for table `hehe`.`hehehe` will be written to 1 file
1 thds dumping - 100% (10 rows / ~10 rows), 9.00 rows/s, 41.00 B/s uncompressed, 0.00 B/s compressed
Duration: 00:00:01s
Schemas dumped: 1
Tables dumped: 1
Uncompressed data size: 42 bytes
Compressed data size: 0 bytes
Compression ratio: 42.0
Rows written: 10
Bytes written: 0 bytes
Average uncompressed throughput: 41.11 B/s
Average compressed throughput: 0.00 B/s
 MySQL  localhost:33060+ ssl  JS >

...and let's check in the bucket for object "test":

Discussion

In order to perform the next step, the loading, you will need to save the file "@.json" on the instance (and folder!) from where you start the mysqlsh to Target db, MDS.

root@mysqlshellinstance:/home# cat > @.json << EOF
> {
>     "dumper": "mysqlsh Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))",
>     "version": "1.0.2",
>     "origin": "dumpInstance",
>     "schemas": [
>         "hehe"
>     ],
>     "basenames": {
>         "hehe": "hehe"
>     },
>     "users": [
>         "'debian-sys-maint'@'localhost'",
>         "'replicauser'@'%'",
>         "'root'@'localhost'",
>     ],
>     "defaultCharacterSet": "utf8mb4",
>     "tzUtc": true,
>     "bytesPerChunk": 64000000,
>     "user": "root",
>     "hostname": "mysqlshellinstance",
>     "server": "mysqlshellinstance",
>     "serverVersion": "8.0.23-0ubuntu0.20.04.1",
>     "gtidExecuted": "a23a4b8b-8a4b-11eb-bfcd-000017024778:1-28",
>     "gtidExecutedInconsistent": true,
>     "consistent": false,
>     "mdsCompatibility": true,
>     "begin": "2021-03-21 16:12:44"
> }
> EOF
root@mysqlshellinstance:/home#

Loading data from bucket into MDS

root@mysqlshellinstance:/home#
root@mysqlshellinstance:/home# mysqlsh -uusermds -h10.0.1.4 -pABCabc123$%
MySQL Shell 8.0.23
[... snip ... ]
 MySQL  10.0.1.4:33060+ ssl  JS >

 MySQL  10.0.1.4:33060+ ssl  JS >  util.loadDump("test", {osBucketName: "bucket-20210321-1608", osNamespace:"addnamespace", threads: 4})
Loading DDL and Data from OCI ObjectStorage bucket=bucket-20210321-1608, prefix='test' using 4 threads.
Opening dump...
Target is MySQL 8.0.23-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-0ubuntu0.20.04.1
Fetching dump data from remote location...
Fetching 1 table metadata files for schema `hehe`...
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL script for schema `hehe`
[Worker002] Executing DDL script for `hehe`.`hehehe`
[Worker000] hehe@hehehe@@0.tsv.zst: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
Executing common postamble SQL

1 chunks (10 rows, 42 bytes) for 1 tables in 1 schemas were loaded in 1 sec (avg throughput 42.00 B/s)
0 warnings were reported during the load.
 MySQL  10.0.1.4:33060+ ssl  JS >
 MySQL  10.0.1.4:33060+ ssl  JS >
 MySQL  10.0.1.4:33060+ ssl  JS > \sql
Switching to SQL mode... Commands end with ;
 MySQL  10.0.1.4:33060+ ssl  SQL > show databases;
+--------------------+
| Database           |
+--------------------+
| hehe               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.0010 sec)
 MySQL  10.0.1.4:33060+ ssl  SQL >

Technically speaking, this looks like a migration... (and no wonder I did not read it in depth from the beginning... )

Back to replication

...the Replication documentation states as following:

The dump loading utility does not automatically apply the gtid_executed GTID set from the source MySQL instance on the target DB System. The GTID set is included in the dump metadata from MySQL Shell's instance dump utility, schema dump utility, or table dump utility, as the gtidExecuted field in the @.json dump file. Retrieve the value of gtidExecuted from the dump in Object Storage.

Connect to the DB System using MySQL Shell and set the value of gtidPurged in the DB System, using the following command:

SQL> call sys.set_gtid_purged("+gtidSet")

where gtidSet is the value in the gtidExecuted field in the @.json dump file.

-- documentation source: Replication

Let's find the gtid from our @.json file:

root@mysqlshellinstance:/home# more @.json | grep gtid
    "gtidExecuted": "a23a4b8b-8a4b-11eb-bfcd-000017024778:1-28",
    "gtidExecutedInconsistent": true,

... and on the target, perform the GTID purging:

root@mysqlshellinstance:/home# mysql -uusermds -h10.0.1.4 -pABCabc123$%
mysql: [Warning] Using a password on the command line interface can be insecure.
[.... snip .... ]

mysql>

mysql> call sys.set_gtid_purged("a23a4b8b-8a4b-11eb-bfcd-000017024778:1-28");
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> show global variables like 'GTID%';
+----------------------------------+-------------------------------------------------------------------------------------+
| Variable_name                    | Value                                                                               |
+----------------------------------+-------------------------------------------------------------------------------------+
| gtid_executed                    | 081a346f-8a4c-11eb-9478-0000170271c8:1-3,a23a4b8b-8a4b-11eb-bfcd-000017024778:1-28  |
| gtid_executed_compression_period | 0                                                                                   |
| gtid_mode                        | ON                                                                                  |
| gtid_owned                       |                                                                                     |
| gtid_purged                      | a23a4b8b-8a4b-11eb-bfcd-000017024778:1-28                                           |
+----------------------------------+-------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

Replication channel

And let's create the replication channel (from UI this time):

And if all good, you should see the Channel as Active:

And let's check the status of the slave on the target MDS. If all good, we should see no errors, along with the executed GTIDs:

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysqlshellinstance.mysqloci.mysqlocivcn.oraclevcn.com
                  Master_User: replicauser
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000021
          Read_Master_Log_Pos: 841
               Relay_Log_File: relay-log-replication_channel.000002
                Relay_Log_Pos: 597
        Relay_Master_Log_File: binlog.000021
        
    [.............. ....  snip ........... ....]
    
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 2
        
    [.............. ....  snip ........... ....]
    
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: a23a4b8b-8a4b-11eb-bfcd-000017024778:29
            Executed_Gtid_Set: 081a346f-8a4c-11eb-9478-0000170271c8:1-3,
a23a4b8b-8a4b-11eb-bfcd-000017024778:1-29
 [        
    [.............. ....  snip ........... ....]
    
            Network_Namespace: mysql
1 row in set, 1 warning (0.01 sec)

Small test

From the MySQL source, just create a database, and see if it is replicated in MDS:

root@mysqlshellinstance:~# mysql -uroot -pabc123!
[....]

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hehe               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> create database henlo;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hehe               |
| henlo              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

... and on the MDS side:

root@mysqlshellinstance:~# mysql -uusermds -h10.0.1.4 -pABCabc123$%

[...]

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hehe               |
| henlo              |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql>

Edit: The code can be found at next chapter, 7.5 Channels (code)

Fini!