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.
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.
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)
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#
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.
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)
