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 asRELOAD
,FILE
,SUPER
,BINLOG_ADMIN
, andSET_USER_ID
. It is not possible to create users granting these privileges. This option strips these privileges from dumpedGRANT
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 thegtidExecuted
field in the@.json
dump file. Retrieve the value ofgtidExecuted
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 thegtidExecuted
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!
Last updated