MySQL SHELL – The new era
MySQL Shell is a modern tool that can be used by both developers and database administrators. It is a tool that can be used with any of the following:
- SQL, Python, Javascript
- MySQL Document Store (NoSQL, X DevAPI)
- JSON Documents / SQL tables
In one of our previous articles – Setting up Replication with various methods for MySQL 8 – we reviewed how to create a replica with multiple tools.
Now, it is time to perform the same action but with MySQL Shell.
In general, MySQL Shell is used with MySQL InnoDB Cluster to deploy a MySQL Group Replication, with or without MySQL Router.
Here we will examine how to create an asynchronous replication.
First of all, we may download MySQL Shell via the following url:
https://dev.mysql.com/downloads/shell/
We will be using MySQL Shell version 8.0.22, that is currently the latest and includes extra features.
Let’s start!
On the source database, we connect to mysqlsh:
mysqlsh root@localhost
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.22-commercial
Copyright (c) 2016, 2020, 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.
Creating a session to 'root@localhost'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 11 (X protocol)
Server version: 8.0.22-commercial MySQL Enterprise Server - Commercial
No default schema selected; type \use <schema> to set one.
MySQL localhost:33060+ ssl JS >We may use option \sql or \py to use SQL or Python respectively instead of the JavaScript mode (\js):
MySQL localhost:33060+ ssl JS > \sql
Switching to SQL mode... Commands end with ;
MySQL localhost:33060+ ssl JS > \py
Switching to Python mode...
MySQL localhost:33060+ ssl Py >For creating the dump we will be using the util.dumpInstance().
Please note that the outputUrl (target directory to store the dump files) must be an empty folder, otherwise you will receive an error like :
util.dumpInstance: Cannot proceed with the dump, the specified directory ‘/’ already exists at the target location / and is not empty. (ArgumentError).
If the folder doesn’t exist mysqlsh is smart enough to create the folder for us:
MySQL localhost:33060+ ssl JS > util.dumpInstance("/path_for_dump", {dryRun: true, ocimds: false, threads:4, compatibility: ["strip_definers", "strip_restricted_grants","force_innodb"]})Now, let’s take a minute to review the options we are using:
dryRun: This won’t dump anything, it will print information on what will be dumped.
ocimds: Enable checks for compatibility with MySQL Database Service (MDS).
threads: How many threads to be used for dumping data chunks from the server.
compatibility: Apply MySQL Database Service compatibility modifications when writing dump files. Supported values: “force_innodb”, “strip_definers”, “strip_restricted_grants”, “strip_tablespaces”.
force_innodb – The MySQL Database Service requires use of the InnoDB storage engine. This option will modify the ENGINE= clause of CREATE TABLE statements that use incompatible storage engines and replace them with InnoDB.
strip_definers – strips the “DEFINER=account” clause from views, routines, events and triggers. The MySQL Database Service requires special privileges to create these objects with a definer other than the user loading the schema. By stripping the DEFINER clause, these objects will be created with that default definer. Views and Routines will additionally have their SQL SECURITY clause changed from DEFINER to INVOKER. This ensures that the access permissions of the account querying or calling them are applied, instead of the user that created them. This should be sufficient for most users, but if your database security model requires that views and routines have more privileges than their invoker, you will need to manually modify the schema before loading it.
strip_restricted_grants – Certain privileges are restricted in the MySQL Database Service. Attempting to create users granting these privileges would fail, so this option allows dumped GRANT statements to be stripped of these privileges.
strip_tablespaces – Tablespaces have some restrictions in the MySQL Database Service. If you’d like to have tables created in their default tablespaces, this option will strip the TABLESPACE= option from CREATE TABLE statements.
As we are using a database that contains other storage engines apart from InnoDB engine, we will be using option force_innodb, along with strip_restricted_grants and strip_definers, as we have routines with DEFINER.
So, here we go:
MySQL localhost:33060+ ssl JS > util.dumpInstance("/path_for_dump", {ocimds: false, threads:4, compatibility: ["strip_definers", "strip_restricted_grants","force_innodb"]})
As this moves along, we may open a new connection to our target host, enabling the local_infile parameter so the dump can be imported and at the same time we disable innodb_redo_log as well.
Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite buffering:
mysqlsh back_up@localhost
MySQL localhost:33060+ ssl JS > \sql SET GLOBAL local_infile=1;
MySQL localhost:33060+ ssl JS > \sql ALTER INSTANCE DISABLE INNODB REDO_LOG;
MySQL localhost:33060+ ssl JS > util.loadDump("/data_imp",{threads:4,updateGtidSet:"replace",skipBinlog:true,waitDumpTimeout:1800})Before proceeding, let’s discuss once again the options we are using:
/data_imp: is where the files will be located.
threads: The number of parallel threads to use to upload chunks of data to the target MySQL instance.
updateGtidSet: Apply the gtid_executed GTID set from the source MySQL instance, as recorded in the dump metadata, to the gtid_purged GTID set on the target MySQL instance.
skipBinlog: Skips binary logging on the target MySQL instance for the sessions used by the utility during the course of the import, by issuing a SET sql_log_bin=0 statement.
waitDumpTimeout: Setting this option activates concurrent loading by specifying a timeout (in seconds) for which the utility waits for further data after all uploaded data chunks in the dump location have been processed.
updateGtidSet is available from 8.0.22 version.
If you are using 8.0.21, then you will need to manually set the GTID by locating the gtidExecuted field in the @.json dump file in the dump metadata:
MySQL localhost:33060+ ssl JS > \sql SET @@GLOBAL.gtid_purged= "+gtidExecuted_set";
So, basically we have taken a dump from the source host and at the same time, restore it to the target host.
Once it is completed, and as MySQL Shell is really fast we won’t have to wait for that long, we log in to the target host and execute the following command:
CHANGE MASTER TO MASTER_HOST='primary_IP',MASTER_USER='user_to_connect',MASTER_PASSWORD='******', MASTER_AUTO_POSITION=1;
START SLAVE;And our replica is all set.
Apart from the util.dumpInstance(), we also have the options to use util.dumpSchemas() and util.dumpTables(), that was introduced in MySQL Shell 8.0.22, and offers the option for dumping specific schemas, tables or views.
It is also important to mention that the above-mentioned utilities are fully compatible with Oracle Cloud Infrastructure.
Conclusion
MySQL Shell is a really useful tool that offers a variety of functionalities.
It is super fast and saves us a lot of time when dealing with broken replication or with data transfer.
Try it, you will love it 🙂
References
- Instance Dump Utility, Schema Dump Utility, and Table Dump Utility
- Dump Loading Utility
- loadDump()
