How to migrate your data from the MySQL Database Service (MDS) to MDS High Availability

On March 31st, 2021, MySQL introduced a new MySQL Database Service (MDS) option named MDS High Availability (MDS H/A).

“The High Availability option enables applications to meet higher uptime requirements and zero data loss tolerance. When you select the High Availability option, a MySQL DB System with three instances is provisioned across different availability or fault domains. The data is replicated among the instances using a Paxos-based consensus protocol implemented by the MySQL Group Replication technology. Your application connects to a single endpoint to read and write data to the database. In case of failure, the MySQL Database Service will automatically failover within minutes to a secondary instance without data loss and without requiring to reconfigure the application. See the documentation to learn more about MySQL Database Service High Availability.” From: MySQL Database Service with High Availability

If you already have data in a MDS instance and you want to use the new MDS H/A option, you will need to move your data from your MDS instance to a new MDS H/A instance. This is a fairly easy process, but it will take some time depending upon the size of your data.

First, connect to the MDS instance via an OCI (Oracle Cloud Infrastructure) compute instance. Login to your compute instance:

ssh -i  opc@Public_IP_Address

If you don’t have MySQL Shell installed, here are the instructions. Execute these commands from your compute instance: (answer “y” or “yes” to each prompt)

Note: I am not going to show the entire output from each command.

sudo yum install –y mysql80-community-release-el7-3.noarch.rpm
sudo yum install –y mysql-shell

Connect to the MySQL Shell, using the IP address of your MDS instance. You will need to enter the user name and password for the MDS instance user.

mysqlsh -uadmin -p -h

Change to the JavaScript mode with /js (if you aren’t already in JavaScript mode):

shell-sql>/js

You can dump individual tables, or the entire instance at once. Check the manual for importing data into MDS for more information. The online manual page – Instance Dump Utility, Schema Dump Utility, and Table Dump Utility – will provide you with more details on the various options. For this example, I am going to dump the entire instance at once, into a file named “database.dump“. Note: The suffix of the file doesn’t matter.

shell-js>util.dumpInstance("database.dump", { })

You will see output similar to this (which has been truncated):

Acquiring global read lock
Global read lock acquired
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Checking for compatibility with MySQL Database Service 8.0.23
...
Schemas dumped: 28                                                                                          
Tables dumped: 264                                                                                          
Uncompressed data size: 456.56 MB                                                                            
Compressed data size: 365.24 MB                                                                            
Compression ratio: 5.4                                                                                     
Rows written: 47273                                                                                        
Bytes written: 557.10 KB                                                                                   
Average uncompressed throughput: 3.03 MB/s                                                                 
Average compressed throughput: 557.10 KB/s                                                                 

Quit the MySQL Shell with the “\q” command.

I can check the dump file:

[opc@mds-client ~]$ ls -l
total 760
drwxr-x---. 2 opc opc 365562813 Mar 31 19:07 database.dump

Connect to the new MDS H/A instance.

ssh -i  opc@Public_IP_Address

Start MySQL Shell again:

mysqlsh -uadmin -p -h

You will use the MySQL Shell Dump Loading Utility to load the data. For more information – see the Dump Loading Utility manual page.

You can do a dry run is to check that there will be no issues when the dump files are loaded from a local directory into the connected MySQL instance: (Note: the output is truncated)

util.loadDump("database.dump", {dryRun: true})
Loading DDL and Data from 'database.dump' using 4 threads.
Opening dump...
dryRun enabled, no changes will be made.
....
No data loaded.
0 warnings were reported during the load.

There are many options for loading your data. Here, I am going to just load the entire dump file. If you have problems, you can use the Table Export Utility and export individual tables. You might want to export and import larger tables on their own. I only need to specify my dump file, and the number of threads I want to use. (Note: the output is truncated)

util.loadDump("database.dump", { threads: 8 })
Loading DDL and Data from 'database.dump' using 8 threads.
Opening dump...
Target is MySQL 8.0.23-u2-cloud (MySQL Database Service). Dump was produced from MySQL 8.0.23-u2-cloud
Checking for pre-existing objects...
Executing common preamble SQL
...
0 warnings were reported during the load.

After the data has been loaded, you will want to double-check the databases and tables in the MDS H/A instance, as well as their sizes, by comparing them to the MDS instance.

That’s it. Moving your data from a MDS instance to a MDS H/A instance is fairly easy.

Note: You will need to change the IP address of your application to point to the new MDS H/A instance.

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

MySQL versus MongoDB for storing JSON documents in a doc-store database – which is faster?

MySQL added the ability to store JSON documents in a doc-store format with MySQL version 5.7 (general availability date – October 21, 2015). Although MySQL’s initial release of their doc-store feature did have some limitations, MySQL has made some really nice improvements in their version 8.0 release, and more recently, in their 8.0.19 release.

I came across a paper written by Erik Andersson and Zacharias Berggren titled “A Comparison Between MongoDB and MySQL Document Store Considering Performance“. I think the paper was written for a college project (and it might have been their senior thesis as they were in the bachelor’s program in computer science). In the paper, they compared MongoDB 3.4.4 against MySQL version 5.7.12, which was an early release of MySQL version 5.7. And, it wasn’t a surprise to see that MongoDB’s document store was much faster than MySQL.

But, with all of the new and improved changes to MySQL’s document-store in the latest release (version 8.0.19), I attempted to re-create their tests to see if MySQL had improved since version 5.7.12. And, both products should have improved added since the original test in 2017.

Disclaimer

I am a solutions engineer at MySQL, and I am not a MongoDB user. There may be ways of configuring both databases to be faster, but I did these tests without modifying any of the default variables which could affect performance. The only exception is that for MySQL, I did a separate test with the binary log disabled (I explain this later in the post).

There were two variables that weren’t related to performance I had to change. For MySQL, I did have to set max_join_size=11000000 when running the queries for the largest database. For MongoDB, I had to set DBQuery.shellBatchSize=1000 or MongoDB would only return 20 rows at a time in the terminal window.

The Equipment

I ran the tests on my own server, and then on a Virtual Machine in Oracle’s Cloud Infrastructure (OCI). My server’s hardware was as follows:

• Intel 8th Gen Core i7-8700K Processor (6 Cores / 12 Threads)
• 32GB DDR4 DRAM 2666MHz
• 500GB PC SSD SATA III 6 Gb/s M.2
• Gigabyte Z370 AORUS motherboard
• Mac OS 10.13.6

For OCI, I used the VM.Standard.B1.1 shape, which consisted of the following:

• Single OCPU – 2.2 GHz Intel® Xeon® E5-2699 v4
• 11.8 GB of Memory
• 100 GB Remote Block Volume

Installation

NOTE: I am not going to go into detail how to install MongoDB, NodeJS or MySQL, but I will provide the links.

I began by installing MongoDB via yum. Per the installation instructions, I created a MongoDB repo file (/etc/yum.repos.d/mongodb-org-4.2.repo), edited the repo file and added the following:

[mongodb-org-4.2]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/4.2/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-4.2.asc

Note: gpgcheck is GNU privacy guard, which helps to verify the that you are installing the correct package from MongoDB, and not from a third-party.

I then used this command to install MongoDB via yum.

sudo yum install -y mongodb-org

I also had to install NodeJS. To install, I used the following commands:

curl -sL https://rpm.nodesource.com/setup_10.x | sudo bash -
sudo yum install nodejs

I downloaded the RPM for the latest (version 8.0.19) MySQL community release, and installed it via yum:

sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm -y
sudo yum install mysql-community-server -y

I started both MongoDB and MySQL:

sudo service mongod start
sudo service mysqld start

And I confirmed the status of each:

sudo service mongod status
sudo service mysqld status

Now that I had MongoDB and MySQL installed, I needed some data.

Data

I found some basic JSON data on the data.gov web site – which is a great site for finding generic JSON data. In this case, I needed a large data set, so I found some JSON data which consisted of a list of businesses in the state of Washington (USA). The file I downloaded contained 2.6 million records. The JSON records contained this data:

{
"Ubi": "1234567890",
"Title": "GOVERNOR",
"FirstName": "CHRISTOPHER",
"MiddleName": "WALKEN",
"LastName": "KRAMER",
"Address": "324 SMITHY DR",
"City": "NOWHERE",
"State": "XA",
"Zip": "05252"
}

I know this example isn’t a large document, but it was the only dataset that I could find with millions of records.

Managing the Data

I took the 2.6 million (2,600,000) records, and split the original single file into three files (using the split command). This produced two files containing one million records, and the last file contained 600,000 records. I discarded the last file.

I used one of the files containing one million records, and split it into ten files containing 100,000 records. I then took one of the files with 100,000 records, and split it into ten files with 10,000 records each. I did the same for the 10,000 record file, splitting it into ten 1,000 record files.

I used the same JSON files for both MongoDB and MySQL.

Note: I am not going to share the data I used. Even though the data is public data, it does contain personal information, and as an employee of Oracle, it might violate their privacy policy.

The Databases

I created the same databases to be used on both as well. The naming convention was as follows:

• db_json_test10k = 10 x 1k records imported – 10k records total
• db_json_test100k = 10 x 10k records imported – 100k records total
• db_json_test1000k = 10 x 100k records imported – 1,000k records total
• db_json_test10000k = 10 x 1000k records imported – 10,000k records total

When importing JSON documents, the databases are automatically created in MongoDB. With MySQL, you have to create them manually.

create database db_json_test10k;
create database db_json_test100k;
create database db_json_test1000k;
create database db_json_test10000k;

The documents inside the database can be created automatically by both MongoDB and MySQL.

The Tests

I did not add any configuration variables for either database – except for what I put in the MongoDB repo file – so the default variables were used for each. For MySQL, the binary log is enabled by default, so I ran the tests with the binary log turned on and turned off. For MySQL, the binary log contains all of the transactions which could change data. In other words, all insert, update and delete transactions are written to a (binary) log on disk. Obviously, running the tests without the binary log enabled was faster in MySQL.

Each test consisted of the following:

• Drop all databases (if required)
• Create the MySQL databases
• Import 1k, 10k, 100k and 1,000k records (10 times each with unique data in each import)
• Create indexes – executed once per round per database
• Perform 100 search queries x 10 different times
• Perform 100 update queries x 10 different times
• Perform 100 delete queries x 10 different times
• Repeat four times, for a total of five tests

I recorded the time it took for each part of the test to be executed, and inserted the test results into a spreadsheet. I used the same data files and queries for both MongoDB and MySQL.

The graphs will show you the time for each action and the results are labeled Mongo, MySQL and MySQL-noBL (with the binary log disabled). For the search, update and delete tests, I performed the same 100 unique queries, 10 times against each database. Each series of 10 tests was executed five times, and the time needed to complete each of the individual 10 tests for each database size (10k, 100k, 1000k and 10000k records) was then averaged across all five tests to create the graphs. (Note: for this test, “k” equals 1,000 and not 1,024)

While I was running the tests, the only applications I had open were a text editor, three terminal windows and a spreadsheet program. However, there were some small anomalies in the test results where a test would take a bit longer than the normal run time. I did not correct the data when an anomaly occurred.

Also, not all off the queries returned or modified the same number of rows. For example, one update query might change 10 rows, while another changed 50, so the query result times are not going to be relative to the every other query. But, each query was executed in the same order, so the time it takes to run query #1 should be relative in all five executions of the tests.

Importing Data

For each of the four databases, I imported the same set of records. This involved importing 1k, 10k, 100k and 1,000k records ten times each into their respective databases.

For MySQL, I used the MySQL Shell, which is a command-line client. Here is an example of the import command:

mysqlsh root:password@localhost/db_json_test10k --import /Volumes/HD1/0mongo_v_mysql/1000/1000-0 json_test10k

For MongoDB, I used the mongoimport utility. Here is an example of the import command:

mongoimport --jsonArray --db db_json_test10k --collection json_test10k --file /Volumes/HD1/0mongo_v_mysql/1000/1000-0m

Note: With MySQL, you have to be careful when you import JSON documents – if the syntax is incorrect, the file can import but it will not create a collection (it will create a table instead). The syntax on the import command is important.

Here are the results of the imports. The Y-axis (vertical line) on the graph represents the time in seconds to perform the action.

MongoDB was much faster than MySQL in importing data, even with the MySQL binary log turned off.

Creating Indexes

Whenever you import data into a collection for either MongoDB or MySQL, both instances will automatically create some indexes. After the initial import, I took a look at the indexes:

For MongoDB:

> use db_json_test10k
switched to db db_json_test10k
> db.json_test10k.getIndexes()
[
	{
		"v" : 2,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "db_json_test10k.json_test10k"
	}
]

For MySQL:

mysql> use db_json_test10k;
Database changed
mysql> SHOW INDEXES FROM json_test10k\G
*************************** 1. row ***************************
        Table: json_test10k
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: _id
    Collation: A
  Cardinality: 98627
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
1 rows in set (0.00 sec)

Both MongoDB and MySQL automatically create an index on the column _id (which is a primary key as well).

I needed to create an index on the column UBI, as I was going to use this column for my searches, updates and deletes.

I would only need to create the indexes once for every series of tests, but I still tracked how long it took for both MongoDB and MySQL to create the indexes. Here are the commands to create an index for each one:

MySQL

use db_json_test10k
ALTER TABLE json_test10k ADD COLUMN UBI_2 INT GENERATED ALWAYS AS (doc->"$.Ubi");
ALTER TABLE json_test10k ADD INDEX (UBI_2);

MongoDB

use db_json_test10k
db.json_test10k.createIndex( { Ubi: 1 })

Note: MySQL indexes JSON documents via virtual columns. See this blog post for a detailed explanation of virtual columns.

For the tests, I would be creating an index on all four databases for each instance. Here are the results of the index creation for each database. The Y-axis (vertical line) on the graph represents the time in seconds needed to perform the action.

MySQL was a bit faster than MongoDB in index creation.

Searches

The search test consisted of executing ten scripts, where each script performed 100 search queries. The test was executed against all four databases, each containing their own update queries (there was a total of 40 separate scripts running 100 separate queries each). The test was repeated five times and the average times are shown in the graphs.

Here is an example of the search syntax: (Only the first three queries are shown)

MySQL

use db_json_test10k
select * from json_test10k where UBI_2 = '603013962'; 
select * from json_test10k where UBI_2 = '603598341'; 
select * from json_test10k where UBI_2 = '601574968';
...

MongoDB

use db_json_test10k
DBQuery.shellBatchSize = 1000
db.json_test10k.find({"Ubi" : "603013962"})
db.json_test10k.find({"Ubi" : "603598341"})
db.json_test10k.find({"Ubi" : "601574968"})
...

The search queries were placed in files – SQL files for MySQL, and JavaScript files for MongoDB – and each file was executed as follows: (Only the first three queries are shown)

MySQL

time mysql -uroot -p < /Volumes/HD1/0mongo_v_mysql/3_search/mysql_search_sql_10k/search_mysql_100_10k_0.sql
time mysql -uroot -p < /Volumes/HD1/0mongo_v_mysql/3_search/mysql_search_sql_10k/search_mysql_100_10k_1.sql
time mysql -uroot -p < /Volumes/HD1/0mongo_v_mysql/3_search/mysql_search_sql_10k/search_mysql_100_10k_2.sql
...

MongoDB

time mongo < /Volumes/HD1/0mongo_v_mysql/3_search/mongo_search_10k/search_mongo_100_10k_00.js
time mongo < /Volumes/HD1/0mongo_v_mysql/3_search/mongo_search_10k/search_mongo_100_10k_02.js
time mongo < /Volumes/HD1/0mongo_v_mysql/3_search/mongo_search_10k/search_mongo_100_10k_04.js
...

Here are the results of the searches for each database. The Y-axis (vertical line) on the graph represents the time in seconds needed to perform the action.

The results were much different than in the original tests. MySQL was much faster than MongoDB in searches.

Update

The update test consisted of executing ten scripts, where each script contained 100 unique update queries. The test was executed against all four databases, each containing their own update queries (there was a total of 40 separate scripts). The test was repeated five times and the average times are shown in the graphs.

The update syntax was as follows:

MySQL

use db_json_test10k;
SET max_join_size=11000000;
SET SQL_SAFE_UPDATES=0;
UPDATE json_test1k SET doc = JSON_SET(doc, '$.State', 'KS') 
WHERE UBI_2 = '604052443';

MongoDB

use db_json_test10k
DBQuery.shellBatchSize = 1000
db.json_test1k.updateMany({"Ubi" : "604052443"}, {$set: { "State" : "KS"}});

Here are the results of the updates for each database. The Y-axis (vertical line) on the graph represents the time in seconds needed to perform the action. Note that not all of the queries updated the same number of rows, so the results aren’t going to be the same for each group of queries.

Delete

The delete test consisted of executing ten scripts, where each script contained 100 unique delete queries. The test was executed against all four databases, each containing their own update queries (there was a total of 40 separate scripts). The test was repeated five times and the average times are shown in the graphs. (I did have to add the changes to max_join_size and DBQuery.shellBatchSize as I explained earlier)

The delete syntax was as follows: (Only the first three queries are shown)

MySQL

use db_json_test10k;
SET max_join_size=11000000;
SET SQL_SAFE_UPDATES=0;
DELETE FROM json_test1k WHERE UBI_2 = '603013962';
DELETE FROM json_test1k WHERE UBI_2 = '603598341';
DELETE FROM json_test1k WHERE UBI_2 = '601574968';
...

MongoDB

use db_json_test10k
DBQuery.shellBatchSize = 1000
db.json_test1k.deleteMany({"Ubi" : "603013962"});
db.json_test1k.deleteMany({"Ubi" : "603598341"});
db.json_test1k.deleteMany({"Ubi" : "601574968"});
... 

Again, the results were much different than in the original tests. MySQL was much faster than MongoDB in deletions.

---

Oracle Cloud Infrastructure Test Results

Here are the test results for running the same tests on Oracle Cloud. MongoDB’s imports were still much faster. On the queries, MongoDB performed better, and in some tests, MySQL was competitive only if you had the binary log disabled.

Import

Create Index

Searches

Updates

Deletes

---

The results from running with only one CPU via OCI are surprising.

Overall, I think the latest version of MySQL is certainly a great alternative to MongoDB for storing JSON documents. If you are using MySQL version 5.7 for storing your JSON documents, I would recommend upgrading to the latest 8.0 version.

Finally – remember, I am not a MongoDB expert. If you have any suggestions on how to tune MongoDB to run faster, please leave a comment below.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

How to setup a GUI via VNC for your Oracle Linux Compute Instance in Oracle Cloud Infrastructure (OCI)

In a couple previous posts, I explained how to get an “Always Free” Oracle Cloud compute instance and how to install MySQL on it – as well as how to add a web server.

I started my IT career (way back in 1989) using a (dumb) terminal and a 2400-baud modem to access a server. While I still use a terminal window and the command-line, it is always nice to have access to a GUI. In this post, I will show you how to install and use a GUI on your Oracle Cloud compute instance so you can use a Virtual Network Computing (VNC) application to connect to your “Always Free” (or not-free) Oracle Cloud compute instance.

VNC is a graphical desktop-sharing system that uses the Remote Frame Buffer protocol to remotely control another computer. In other words, it is (almost) like having a monitor connected to your compute instance. Installing everything you need should take about twenty minutes (only because one yum install takes 13-15 minutes).

First, you will need to create your “Always Free” Oracle Cloud account, and at least one free compute instance. (Of course, this will also work on a paid compute instance.) If you need help creating your free compute instance, you can follow the instructions in the first part of this post (installing MySQL is optional).

Once you have your compute instance ready to go, or if you already have an compute instance running, you can continue with this post.

VNC Viewer

I am using a Mac, so I can use the Screen Sharing application that comes with the operating system (OS). If you don’t have a Mac, you will need to find a VNC application for your OS. I have also used the free (non-commercial-use only) version of VNC Connect from RealVNC, but you will need to buy a copy of you are using it for work. But there are several free ones available, such as TeamViewer, TightVNC and TigerVNC.

If you don’t use a Mac, I won’t be able to show you how to install or setup the VNC viewer you decide to use, but it should be easy to do. Whichever VNC app you choose should provide you with instructions. You should only have to input localhost and the port number of 5901.

Installing what you need on your compute instance

Login to your compute instance. When I created my compute instance, I chose to install Oracle Linux. These instructions should work for any other flavor of Linux, but if not, you can look for the similar packages for your OS and you might have to modify a few things.

You can use yum (a tool for downloading, installing, deleting, querying, and managing software packages) to install the GNOME desktop, which is a free and open-source desktop environment. This installation is 678 megabytes in size, and it will take about 13-15 minutes. You can remove the -y option to your yum command if you want to answer “yes” to the single installation question of “Is this ok?”.

Note: Normally I would post the entire output from a command, but the output is almost 6,000 lines long. I will replace the majority of the screen output with three dots (…).

$ sudo yum -y groups install "Server with GUI" 
Loaded plugins: langpacks, ulninfo
Repository ol7_latest is listed more than once in the configuration
...
Transaction Summary
==================================================
Install  209 Packages (+659 Dependent packages)
Upgrade               (   3 Dependent packages)

Total download size: 678 M
Is this ok [y/d/N]: y
Downloading packages:
...
Complete!

Install the TigerVNC server. (I will suppress most of this output as well)

$ sudo yum -y install tigervnc-server
Loaded plugins: langpacks, ulninfo
...
Resolving Dependencies
--> Running transaction check
---> Package tigervnc-server.x86_64 0:1.8.0-17.0.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================
 Package           Arch      Version             Repository    Size
====================================================================
Installing:
 tigervnc-server   x86_64    1.8.0-17.0.1.el7    ol7_latest   215 k
Transaction Summary
====================================================================
Install  1 Package

Total download size: 215 k
Installed size: 509 k
Downloading packages:
tigervnc-server-1.8.0-17.0.1.el7.x86_64.rpm         | 215 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : tigervnc-server-1.8.0-17.0.1.el7.x86_64       1/1 
  Verifying  : tigervnc-server-1.8.0-17.0.1.el7.x86_64       1/1 

Installed:
  tigervnc-server.x86_64 0:1.8.0-17.0.1.el7
Complete!

Configure the VNC server

You will be required to provide a password that you will need to remember to be able to access this server via VNC. You can also enter a “view-only password” if you want someone to be able to connect to the server, but you don’t want them to be able to control anything (they can only view the screen). I skipped this option.

$ vncserver
You will require a password to access your desktops.
Password:
Verify:
Would you like to enter a view-only password (y/n)? n
A view-only password is not used
xauth:  file /home/opc/.Xauthority does not exist

New 'instance-20191113-1544:1 (opc)' desktop is instance-20191113-1544:1

Creating default startup script /home/opc/.vnc/xstartup
Creating default config /home/opc/.vnc/config
Starting applications specified in /home/opc/.vnc/xstartup
Log file is /home/opc/.vnc/instance-20191113-1544:1.log

SSH Tunnel for VNC

I am going to create a tunnel for VNC through SSH, so I can punch through the VNC port, and also so I will be sending all of the data through an encrypted tunnel.

Note: There is an alternate way to access your compute instance via VNC by creating an Instance Console Connection, but it only provides you with a console connection (non-GUI). If you want to do that, instructions are available via this blog.

In a terminal window, issue the following command, with your public IP address at the end. This will create the SSH tunnel for you to use for VNC.

$ ssh -L 5901:localhost:5901 opc@150.136.199.99

Now you are ready to connect to the instance using VNC. For the Mac, I open the Screen Sharing application, click on the menu option “Connection” then down to “New”. In the “Connect to” box, I enter localhost:5901 and press “Connect”.

And then enter the password you used when you ran the vncserver command earlier.

If you are using another VNC viewer, simply enter localhost:5901, or you might have to enter localhost and the port 5901 in separate fields.

Then, just open the connection with your VNC application, and you should be see the Oracle Linux GUI appear:

You will have to go through a few setup screens the first time you connect.

---

 

Alternatives to GNOME

If you are using the “Always Free” OCI instances, you can also consider using two lightweight alternate GUI’s. You can also install MATE or Xfce.

Multiple connections

If you want to have multiple connections open to separate OCI instances at the same time, you can do this by changing the outgoing port on your SSH tunnel. The syntax for the ssh tunnel command contains two port numbers. The outgoing port (on your machine) is 5902, and the destination/incoming port is 5901. So, to open a second SSH tunnel to connect to a separate OCI instance, you can run this:

$ ssh -L 5902:localhost:5901 opc@150.136.199.98

This syntax follows the ssh man page: -L [bind_address:]port:host:hostport.

Now you know how to use VNC to connect to your Oracle Compute Cloud Instance.

---

 
Many thanks to Pilippe Vanhaesendonck for sharing his technical expertise.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Oracle’s “Always Free” Cloud Instance – Adding a web server to your free MySQL compute instance with zero monthly charges

In a previous post, I explained how you can take advantage of Oracle’s “Always Free” Cloud instance to obtain a free Oracle Cloud compute instance (virtual machine) and install a copy of MySQL – without having to pay a setup fee and without incurring any monthly charges. And, you can have two free compute instances per account.

This free Cloud option from Oracle is great. I can think of a lot of ways to utilize a free Oracle Cloud compute instance – but the first one that came to mind is by using it as a web server. Part of Oracle’s “Always Free” offering falls under their “Additional Services” category – which includes 10 TB of outbound data transfer each month. If you have a small web site without a ton of graphics or one where you don’t have a million hits per month, using this free Oracle Cloud instance could save you a little bit of money versus paying to host your web site with a web hosting company.

To get started, go back and read the post I mentioned, to get your “Always Free” Oracle Cloud account and to install MySQL (if you need a database for your web site). If you don’t need MySQL, just follow the post to set up your cloud account and create your first compute instance.

Once you have created your first compute instance, you only have three small tasks to complete:
1. You will need to install a web server
2. Setup a security rule to allow traffic over port 80
3. Connect your Oracle public IP address to your domain name by editing the domain name service (DNS) entry for your domain name.

The first two tasks should take you less than ten minutes to complete. Updating your DNS entry shouldn’t take but a few minutes – if you know how to do it with your domain name provider.

Installing a web server

Depending upon which operating system (OS) you chose when you created your compute instances, you should be able to install the default web server that comes with that OS. For my compute instance, I chose to install the default OS of Oracle Linux, so I can install the Apache web server via yum. Your OS might be different, but for most Linux versions, the command is “sudo yum install httpd“. If you want yum to automatically answer any install questions with a “yes”, you can add the -y option. (Click here for more yum options)

Note: Remember that with an Oracle compute instance, you will need to run most tasks via sudo (as root).

$ sudo yum install httpd -y
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                        | 2.5 kB  00:00:00     
mysql-tools-community                             | 2.5 kB  00:00:00     
mysql80-community                                 | 2.5 kB  00:00:00     
ol7_UEKR5                                         | 2.8 kB  00:00:00     
ol7_addons                                        | 2.8 kB  00:00:00     
ol7_developer                                     | 2.8 kB  00:00:00     
ol7_developer_EPEL                                | 3.4 kB  00:00:00     
ol7_ksplice                                       | 2.8 kB  00:00:00     
ol7_latest                                        | 3.4 kB  00:00:00     
ol7_oci_included                                  | 2.9 kB  00:00:00     
ol7_optional_latest                               | 2.8 kB  00:00:00     
ol7_software_collections                          | 2.8 kB  00:00:00     
(1/5): ol7_optional_latest/x86_64/updateinfo      | 869 kB  00:00:00     
(2/5): ol7_latest/x86_64/updateinfo               | 2.6 MB  00:00:00     
(3/5): ol7_optional_latest/x86_64/primary_db      | 4.0 MB  00:00:00     
(4/5): ol7_latest/x86_64/primary_db               |  17 MB  00:00:01     
(5/5): ol7_ksplice/primary_db                     | 276 kB  00:00:00     
Resolving Dependencies
--> Running transaction check
---> Package httpd.x86_64 0:2.4.6-90.0.1.el7 will be installed
--> Processing Dependency: httpd-tools = 2.4.6-90.0.1.el7 for package: httpd-2.4.6-90.0.1.el7.x86_64
--> Processing Dependency: /etc/mime.types for package: httpd-2.4.6-90.0.1.el7.x86_64
--> Processing Dependency: libaprutil-1.so.0()(64bit) for package: httpd-2.4.6-90.0.1.el7.x86_64
--> Processing Dependency: libapr-1.so.0()(64bit) for package: httpd-2.4.6-90.0.1.el7.x86_64
--> Running transaction check
---> Package apr.x86_64 0:1.4.8-5.el7 will be installed
---> Package apr-util.x86_64 0:1.5.2-6.0.1.el7 will be installed
---> Package httpd-tools.x86_64 0:2.4.6-90.0.1.el7 will be installed
---> Package mailcap.noarch 0:2.1.41-2.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==============================================================================
 Package                       Arch     Version            Repository    Size
==============================================================================
Installing:
 httpd                         x86_64   2.4.6-90.0.1.el7   ol7_latest    1.2 M
Installing for dependencies:
 apr                           x86_64   1.4.8-5.el7        ol7_latest    103 k
 apr-util                      x86_64   1.5.2-6.0.1.el7    ol7_latest     91 k
 httpd-tools                   x86_64   2.4.6-90.0.1.el7   ol7_latest     90 k
 mailcap                       noarch   2.1.41-2.el7       ol7_latest     30 k

Transaction Summary
==============================================================================
Install  1 Package (+4 Dependent packages)

Total download size: 1.5 M
Installed size: 4.3 M
Downloading packages:
(1/5): apr-util-1.5.2-6.0.1.el7.x86_64.rpm        |  91 kB  00:00:00     
(2/5): apr-1.4.8-5.el7.x86_64.rpm                 | 103 kB  00:00:00     
(3/5): httpd-tools-2.4.6-90.0.1.el7.x86_64.rpm    |  90 kB  00:00:00     
(4/5): mailcap-2.1.41-2.el7.noarch.rpm            |  30 kB  00:00:00     
(5/5): httpd-2.4.6-90.0.1.el7.x86_64.rpm          | 1.2 MB  00:00:00     
-------------------------------------------------------------------------------
Total                                    2.8 MB/s | 1.5 MB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : apr-1.4.8-5.el7.x86_64                1/5 
  Installing : apr-util-1.5.2-6.0.1.el7.x86_64       2/5 
  Installing : httpd-tools-2.4.6-90.0.1.el7.x86_64   3/5 
  Installing : mailcap-2.1.41-2.el7.noarch           4/5 
  Installing : httpd-2.4.6-90.0.1.el7.x86_64         5/5 
  Verifying  : httpd-tools-2.4.6-90.0.1.el7.x86_64   1/5 
  Verifying  : mailcap-2.1.41-2.el7.noarch           2/5 
  Verifying  : apr-util-1.5.2-6.0.1.el7.x86_64       3/5 
  Verifying  : httpd-2.4.6-90.0.1.el7.x86_64         4/5 
  Verifying  : apr-1.4.8-5.el7.x86_64                5/5 

Installed:
  httpd.x86_64 0:2.4.6-90.0.1.el7                                        

Dependency Installed:
  apr.x86_64 0:1.4.8-5.el7
  apr-util.x86_64 0:1.5.2-6.0.1.el7
  httpd-tools.x86_64 0:2.4.6-90.0.1.el7
  mailcap.noarch 0:2.1.41-2.el7        

Complete!

With Apache installed, I can go ahead and start the web server, and I can also configure it to start after the system reboots.

$ sudo apachectl start
$ sudo systemctl enable httpd
Created symlink from /etc/systemd/system/multi-user.target.wants/httpd.service to /usr/lib/systemd/system/httpd.service.

If you want, you can run a quick check on the Apache configuration.

$ sudo apachectl configtest
Syntax OK

Next, you will want to create the firewall rules to allow access to the ports on which the HTTP server listens.

$ sudo firewall-cmd --permanent --zone=public --add-service=http 
success
$ sudo firewall-cmd --reload
success

Create an initial web page

And finally, create a starter web page so you can test the web server later.

sudo bash -c 'echo This is my new web page running on Oracle Cloud Always Free compute instance > /var/www/html/index.html'

Note: The web page files are stored in /var/www/html.

Monitor your usage and potential fees

WARNING: I have not fully tested Oracle’s “Always Free” service yet, and while there shouldn’t be any hidden “gotchas” when it comes to Oracle’s Cloud billing, I am not entirely sure if this use case will fall under their “Always Free” usage. I will be setting up a web page and testing it to make sure I don’t run into any hidden problems as far as being charged for using the cloud network. Therefore, you will want to also monitor your usage.

To monitor your instance’s usage, from the Oracle Cloud menu (top left of your screen), go to Account Management -> Cost Analysis after your web site has been running for a day or two – and you can see if this is truly an “Always Free” option for your web site.

Creating a security rule

You will need to create a stateless security rule to allow ingress traffic on port 80. From the Oracle Cloud menu (top left of your screen), go down to Networking and over to Virtual Cloud Networks.

You will be presented with a list of the Virtual Cloud Networks (VCN) you have already created, and if you are doing this from the beginning, you should only have one VCN listed. Click on the VCN name that begins with VirtualCloudNetwork.

On the left, you will see a menu like this. Click on “Security Lists”:

To the right of the above menu, you will be see a list of the security lists you have already created, and if you are doing this from the beginning, you should only have one security list available. Click on the security list name that begins with Default Security List for VirtualCloudNetwork – where the VirtualCloudNetwork name matches your VirtualCloudNetwork name.

You are going to need to add an Ingress Rule, so click on the “Add Ingress Rules” button:

Fill out the form like this, and then click on “Add Ingress Rules”.

Note: You do not want to click on the “Stateless” box. A stateless rule means that you will also need to create an egress rule for the outbound port 80 traffic. If you leave this unchecked, the rule that is created will be a “stateful” rule, which means that if you allow inbound traffic on port 80, outbound traffic is also automatically allowed.

From Oracle’s documentation:

“Marking a security rule as stateful indicates that you want to use connection tracking for any traffic that matches that rule. This means that when an instance receives traffic matching the stateful ingress rule, the response is tracked and automatically allowed back to the originating host, regardless of any egress rules applicable to the instance. And when an instance sends traffic that matches a stateful egress rule, the incoming response is automatically allowed, regardless of any ingress rules. For more details, see Connection Tracking Details for Stateful Rules.

You should now see a list of Ingress Rules that looks something like this, with your new Ingress Rule at the bottom.

Testing the web server

After you have completed the steps above, you can put your public IP address into a browser window and you should see the web page you created above.

Connecting your domain name to the Oracle Cloud IP address

If you have a domain name for your web site, you will need to go to your domain name hosting company, edit the DNS entry for your domain name and use your Oracle Cloud Public IP address. I can’t really provide you with instructions on how to do this specific to your hosting site. But, if you need an inexpensive domain name and website hosting company, I would recommend www.ionos.com (formerly www.1and1.com). I have used them for over 15 years and they have great products and customer service. And they have special prices for first-year domain names. I just registered a .us domain name for $7.50 for the first year, and $15 for each year after that.

The DNS changes will take anywhere from 24-48 hours to propagate across the Internet. But you can test your web site using the Oracle Cloud public IP address.

Now you have a free Oracle Cloud compute instance with MySQL and a web server running on it. Remember – the “Always Free” tier allows you to create two free compute instances (or virtual machines).

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Installing MySQL on Oracle Cloud’s “Always Free” Compute Instance

At Oracle’s OpenWorld 2019, Oracle announced an Oracle Cloud Free Tier, which includes Oracle Cloud services that will always be free – as long as you use them or keep them active.

Most other hyperscale cloud vendors offer a free 12-month trial before they start charging you, so to see Oracle provide a “truly free” service is really great. And the free services are not just limited to one or two choices – you get a wide variety of free services to choose from. As of this blog post, here is a brief summary of free services (these are subject to change in the future):

Databases – Your choice of Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.
Storage – 2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.
Compute – 2 virtual machines with 1/8 OCPU and 1 GB memory each.
Additional Services – Load Balancer, 1 instance, 10 Mbps bandwidth. Monitoring, 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications, 1 million delivery options per month, 1,000 emails sent per month. Outbound Data Transfer, 10 TB per month.

For this post, I am going to show you how to create an Oracle Cloud Compute Instance (virtual machine) and install the MySQL Community Edition and MySQL Enterprise Edition (license required) of MySQL on it. Installation is a fairly simple process but there are a few things to note. If you follow these steps, you should be able to have a free cloud instance up and running with MySQL installed in less than a half-hour.

Creating your free Oracle Cloud instance

First, you need to go to Oracle’s Cloud Free Tier website, and register. Yes, this requires that you give them your email address and a method of payment, but you won’t be charged as long as you choose the Oracle Cloud free-tier products. I am not going to go through all of the steps to register your account, but it is fairly easy. Here are a few things to note:

When selecting your home region, be sure that the services you want to use are located in this region. This link will show you the available regions for the “Always Free Cloud Services”. (A region is a localized geographic area.)

After you have registered, you will be taken to the login page and then you will need to enter your email address and password. After a successful login, you will be directed to the dashboard page. At the top of the page, you should see a note that states something like this – “You are in a free trial. When your trial is over, your account will be limited to Always Free resources. Upgrade at any time.” If you are part of a free trial period, this period is for a set amount of money and for a limited time. In other words, if your free trial amount is $300 and one month of services, your free trial for normal (not free) services will expire after you have consumed $300 worth of services or after 30-days. So, you aren’t limited in your initial trial to only using the “Always Free Eligible” services. And, the “Always Free” services will continue to work after the trial period – these services are “always free”.

Note: Some of the images and words on the Oracle Cloud web page might change over time.

Note: As you are creating your instances, be sure that you only select options that have the “Always Free Eligible” banner.

Creating your compute instance

You are now ready to create your first Compute instance (Virtual Machine). Click on the “Create a VM instance” box.

Notice the “Always Free Eligible” banner at the top right of the box.

On the “Create Compute Instance” page, the first box allows you to name your instance. If you don’t want to keep the auto-generated instance name, you should change it here. (I am going to keep the default instance name.)

The default operating system or image source is Oracle Linux.

If you don’t want to use Oracle Linux, click on the “Change Image Source” button, and you will see a selection of available operating systems to use. Note that not all images are part of the free version.

Next, click on the “Show Shape, Network and Storage Options” to expand this selection.

The first option is to choose your Availability Domain, but the free option should already be selected. Do not change this option. An Availability Domain is one or more data centers located within a region.

Your Instance Type should be chosen for you, as a Virtual Machine is the only free option.

Your Instance Shape should also be chosen for you. The free Instance Shape version is VM.Standard.E2.1.Micro (Virtual Machine) with 1 Core OCPU and 1 GB Memory.

Under Configure Networking, you can change the virtual cloud network name if you want. I left mine as the default auto-generated name. Important – You will want to select “Assign a public IP address” to be able to connect to this instance from outside of the Oracle Cloud. If you choose “Do not assign a public IP address”, you won’t have a way to connect to this instance unless you connect through another instance from within Oracle Cloud.

You are allocated 100 gigabytes of free storage under the free tier. So, under Boot Volume, you can accept the default size of 46.6 GB, or click “Custom boot volume size (in GB) and enter a smaller or larger number. I am going with the default size. Note: You are limited to two free virtual machines per account under the “Always Free” option.

If you are going to connect to instances using Oracle Linux, CentOS or Ubuntu, you will need to create an SSH key pair. This is very simple to do.

Here is one example of how to create the SSH key pair. You might have to search for how to do it for your particular operating system. The file “id_rsa.pub” is what you will need to upload. You will need to place this file in the $HOME/.ssh directory of the computer you will use to access this compute instance. You can also share this file with others who will want to access this instance.

# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/var/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /var/root/.ssh/id_rsa.
Your public key has been saved in /var/root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:od/ZPR2IcW+ml5mcWFa3ojK9c0eeTqsW9eH8T9AQzEI root@Hackintosh.local
The key's randomart image is:
+---[RSA 2048]----+
|           .Eo.  |
|            . o. |
|        .  . o. o|
|       . .  + o=+|
|      . S  . o=O+|
|       . ..o.oX*B|
|        .ooo.o*O=|
|          o..oo*o|
|           .+.+oo|
+----[SHA256]-----+

After you have created the key, you will want to drag-and-drop the SSH key file (it should be named id_rsa.pub) onto the “Drop files here” box. Or you can click “Choose Files” and navigate the window to the location of your SSH key file.

For this demo, I am not going to address any of the advanced options, so I will skip this part:

Finally, you need to click on the “Create” button to create your instance.

You should be directed to the Work Requests page under the Instance Details page, where you will see all of the information about your instance as it is being created.

Towards the bottom, you will see the progress of the instance creation:

It only took a couple of minutes for my instance to be created. The instance page should refresh automatically, but you can manually refresh it. Once the instance has been created, you should see something like this.

Under the Primary VNIC Information, you will see your Public IP Address. You will need this to be able to connect to the instance via SSH.

Now that my instance has been created and is running, I can connect to the Oracle Cloud Compute Instance via a terminal window. I have already placed my SSH key file in the .ssh directory under my $HOME directory.

$ pwd
/Users/tonydarnell
$ ls -l .ssh/*pub
-rw-------@ 1 tonydarnell  staff  403 Sep 12 10:58 .ssh/id_rsa.pub

I can connect using SSH and with the user named “opc“. You will probably get a warning about the authenticity of the host, but you can just answer “yes”.

$ ssh opc@150.136.199.99
The authenticity of host '150.136.199.99 (150.136.199.99)' can't be established.
ECDSA key fingerprint is SHA256:9ZE+AFYo7luYoBFZhJZ0YS/W6QdQPPJOP9xItnY17+c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '150.136.199.99' (ECDSA) to the list of known hosts.

I am now connected to my Oracle Cloud Compute Instance. I can check to see which OS version I am running:

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.7 (Maipo)

---

    

Installing the MySQL RPM packages via yum

I can now install MySQL but I have to be careful – as MariaDB is the default version of MySQL installed on Oracle Linux (even though MariaDB isn’t the same as MySQL). If you try to run the regular yum install command (sudo yum install mysql), you will see that it tries to install MariaDB:

Note: Be sure to type “N” at the end of this process when asked “Is this ok” so you don’t install MariaDB.

$ sudo yum install mysql 
Loaded plugins: langpacks, ulninfo
ol7_UEKR5                                            | 2.8 kB  00:00:00     
ol7_addons                                           | 2.8 kB  00:00:00     
ol7_developer                                        | 2.8 kB  00:00:00     
ol7_developer_EPEL                                   | 3.4 kB  00:00:00     
ol7_ksplice                                          | 2.8 kB  00:00:00     
ol7_latest                                           | 3.4 kB  00:00:00     
ol7_oci_included                                     | 2.9 kB  00:00:00     
ol7_optional_latest                                  | 2.8 kB  00:00:00     
ol7_software_collections                             | 2.8 kB  00:00:00     
(1/12): ol7_ksplice/primary_db                       | 257 kB  00:00:00     
(2/12): ol7_addons/x86_64/updateinfo                 |  74 kB  00:00:00     
(3/12): ol7_addons/x86_64/primary_db                 | 137 kB  00:00:00     
(4/12): ol7_latest/x86_64/updateinfo                 | 2.6 MB  00:00:00     
(5/12): ol7_oci_included/x86_64/primary_db           |  71 kB  00:00:00     
(6/12): ol7_optional_latest/x86_64/updateinfo        | 868 kB  00:00:00     
(7/12): ol7_software_collections/x86_64/updateinfo   | 8.7 kB  00:00:00     
(8/12): ol7_developer/x86_64/primary_db              | 441 kB  00:00:00     
(9/12): ol7_software_collections/x86_64/primary_db   | 4.7 MB  00:00:00     
(10/12): ol7_latest/x86_64/primary_db                |  15 MB  00:00:00     
(11/12): ol7_developer_EPEL/x86_64/primary_db        |  11 MB  00:00:01     
(12/12): ol7_optional_latest/x86_64/primary_db       | 4.0 MB  00:00:00     
Resolving Dependencies
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.64-1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
======================================================================
 Package       Arch       Version            Repository        Size
======================================================================
Installing:
 mariadb       x86_64     1:5.5.64-1.el7     ol7_latest        8.7 M

Transaction Summary
======================================================================
Install  1 Package

Total download size: 8.7 M
Installed size: 49 M
Is this ok [y/d/N]: N
Exiting on user command
Your transaction was saved, rerun it with:
 yum load-transaction /tmp/yum_save_tx.2019-11-04.21-11.NHFXMJ.yumtx

To install a real version of MySQL, you will need to download the release package for your platform. (Full instructions may be found at https://dev.mysql.com/doc/mysql-repo-excerpt/8.0/en/linux-installation-yum-repo.html)

Go to the Download MySQL Yum Repository page (https://dev.mysql.com/downloads/repo/yum/) in the MySQL Developer Zone.

Select and download the release package for your platform. You should see something like this on the Yum download page.

Since I am installing on Oracle Linux 7, I will want to download the RPM package for “Red Hat Enterprise Linux 7 / Oracle Linux 7 (Architecture Independent) RPM Package“. Downloading this package requires an Oracle account, so you can register for one if you don’t already have one.

I will now need to copy the package over to the Oracle Cloud instance I created. I can easily do this via SFTP. You will want to set up your FTP client with the user name of opc and you will also need to use the id_rsa.pub file to connect. I am on a Mac, and I use CyberDuck, so this is what my SFTP settings look like:

I created a directory in my $HOME directory and named it mysql-install-files. I placed the RPM package in this directory. You can place the files wherever you want.

[opc@instance-20191113-1544 ~]$ pwd
/home/opc
[opc@instance-20191113-1544 ~]$ ls -l
total 0
drwxrwxr-x. 2 opc opc 56 Nov 14 15:33 mysql-install-files
[opc@instance-20191113-1544 ~]$ cd mysql-install-files/
[opc@instance-20191113-1544 mysql-install-files]$ ls -l
total 28
-rw-rw-r--. 1 opc opc 26024 Nov 14 15:32 mysql80-community-release-el7-3.noarch.rpm

I can now install the downloaded release package. If you chose a different operating system, you will want to replace the platform-and-version-specific-package-name with the name of the downloaded RPM package. And, you will want to refer to the instructions for installing MySQL for your specific OS version.

[opc@instance-20191113-1544 mysql-install-files]$ sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm
Loaded plugins: langpacks, ulninfo
Examining mysql80-community-release-el7-3.noarch.rpm: mysql80-community-release-el7-3.noarch
Marking mysql80-community-release-el7-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql80-community-release.noarch 0:el7-3 will be installed
--> Finished Dependency Resolution
ol7_UEKR5/x86_64                               | 2.8 kB  00:00:00     
ol7_UEKR5/x86_64/updateinfo                    |  21 kB  00:00:00     
ol7_UEKR5/x86_64/primary_db                    | 4.0 MB  00:00:00     
ol7_addons/x86_64                              | 2.8 kB  00:00:00     
ol7_addons/x86_64/updateinfo                   |  74 kB  00:00:00     
ol7_addons/x86_64/primary_db                   | 137 kB  00:00:00     
ol7_developer/x86_64                           | 2.8 kB  00:00:00     
ol7_developer/x86_64/updateinfo                |   71 B  00:00:00     
ol7_developer/x86_64/primary_db                | 444 kB  00:00:00     
ol7_developer_EPEL/x86_64                      | 3.4 kB  00:00:00     
ol7_developer_EPEL/x86_64/group_gz             |  87 kB  00:00:00     
ol7_developer_EPEL/x86_64/updateinfo           | 4.9 kB  00:00:00     
ol7_developer_EPEL/x86_64/primary_db           |  11 MB  00:00:00     
ol7_ksplice                                    | 2.8 kB  00:00:00     
ol7_ksplice/updateinfo                         | 4.8 kB  00:00:00     
ol7_ksplice/primary_db                         | 257 kB  00:00:00     
ol7_latest/x86_64                              | 3.4 kB  00:00:00     
ol7_latest/x86_64/group_gz                     | 148 kB  00:00:00     
ol7_latest/x86_64/updateinfo                   | 2.6 MB  00:00:00     
ol7_latest/x86_64/primary_db                   |  15 MB  00:00:00     
ol7_oci_included/x86_64                        | 2.9 kB  00:00:00     
ol7_oci_included/x86_64/primary_db             |  71 kB  00:00:00     
ol7_optional_latest/x86_64                     | 2.8 kB  00:00:00     
ol7_optional_latest/x86_64/updateinfo          | 868 kB  00:00:00     
ol7_optional_latest/x86_64/primary_db          | 4.0 MB  00:00:00     
ol7_software_collections/x86_64                | 2.8 kB  00:00:00     
ol7_software_collections/x86_64/updateinfo     | 8.7 kB  00:00:00     
ol7_software_collections/x86_64/primary_db     | 4.7 MB  00:00:00     

Dependencies Resolved

=============================================================================================
 Package                     Arch    Version  Repository                                Size
=============================================================================================
Installing:
 mysql80-community-release   noarch  el7-3    /mysql80-community-release-el7-3.noarch   31 k

Transaction Summary
=============================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql80-community-release-el7-3.noarch                    1/1 
  Verifying  : mysql80-community-release-el7-3.noarch                    1/1 

Installed:
  mysql80-community-release.noarch 0:el7-3                                                                                                                      

Complete!

Installing the Community version

Now that I have the MySQL repository in place, I can install the MySQL Community edition server.

$ sudo yum install mysql-community-server
Loaded plugins: langpacks, ulninfo
mysql-connectors-community                               | 2.5 kB  00:00:00     
mysql-tools-community                                    | 2.5 kB  00:00:00     
mysql80-community                                        | 2.5 kB  00:00:00     
(1/3): mysql-connectors-community/x86_64/primary_db      |  49 kB  00:00:00     
(2/3): mysql-tools-community/x86_64/primary_db           |  66 kB  00:00:00     
(3/3): mysql80-community/x86_64/primary_db               |  87 kB  00:00:00     
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-server.x86_64 0:8.0.18-1.el7 will be installed
--> Processing Dependency: mysql-community-common(x86-64) = 8.0.18-1.el7 for package: mysql-community-server-8.0.18-1.el7.x86_64
--> Processing Dependency: mysql-community-client(x86-64) >= 8.0.11 for package: mysql-community-server-8.0.18-1.el7.x86_64
--> Running transaction check
---> Package mysql-community-client.x86_64 0:8.0.18-1.el7 will be installed
--> Processing Dependency: mysql-community-libs(x86-64) >= 8.0.11 for package: mysql-community-client-8.0.18-1.el7.x86_64
---> Package mysql-community-common.x86_64 0:8.0.18-1.el7 will be installed
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
---> Package mysql-community-libs.x86_64 0:8.0.18-1.el7 will be obsoleting
--> Running transaction check
---> Package mysql-community-libs-compat.x86_64 0:8.0.18-1.el7 will be obsoleting
--> Finished Dependency Resolution

Dependencies Resolved

======================================================================================
 Package                      Arch        Version         Repository            Size
======================================================================================
Installing:
 mysql-community-libs         x86_64      8.0.18-1.el7    mysql80-community     3.7 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
 mysql-community-libs-compat  x86_64      8.0.18-1.el7    mysql80-community     1.3 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
 mysql-community-server       x86_64      8.0.18-1.el7    mysql80-community     429 M
Installing for dependencies:
 mysql-community-client       x86_64      8.0.18-1.el7    mysql80-community      38 M
 mysql-community-common       x86_64      8.0.18-1.el7    mysql80-community     597 k

Transaction Summary
======================================================================================
Install  3 Packages (+2 Dependent packages)

Total download size: 473 M
Is this ok [y/d/N]: Y
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/mysql80-community/packages/mysql-community-common-8.0.18-1.el7.x86_64.rpm: 
    Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Public key for mysql-community-common-8.0.18-1.el7.x86_64.rpm is not installed
(1/5): mysql-community-common-8.0.18-1.el7.x86_64.rpm           | 597 kB  00:00:00     
(2/5): mysql-community-libs-8.0.18-1.el7.x86_64.rpm             | 3.7 MB  00:00:00     
(3/5): mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm      | 1.3 MB  00:00:00     
(4/5): mysql-community-client-8.0.18-1.el7.x86_64.rpm           |  38 MB  00:00:17     
(5/5): mysql-community-server-8.0.18-1.el7.x86_64.rpm           | 429 MB  00:01:31     
----------------------------------------------------------------------------------
Total                                                  5.1 MB/s | 473 MB  00:01:32     
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering "
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-3.noarch (@/mysql80-community-release-el7-3.noarch)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.18-1.el7.x86_64        1/6 
  Installing : mysql-community-libs-8.0.18-1.el7.x86_64          2/6 
  Installing : mysql-community-client-8.0.18-1.el7.x86_64        3/6 
  Installing : mysql-community-server-8.0.18-1.el7.x86_64        4/6 
  Installing : mysql-community-libs-compat-8.0.18-1.el7.x86_64   5/6 
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                6/6 
  Verifying  : mysql-community-client-8.0.18-1.el7.x86_64        1/6 
  Verifying  : mysql-community-common-8.0.18-1.el7.x86_64        2/6 
  Verifying  : mysql-community-server-8.0.18-1.el7.x86_64        3/6 
  Verifying  : mysql-community-libs-8.0.18-1.el7.x86_64          4/6 
  Verifying  : mysql-community-libs-compat-8.0.18-1.el7.x86_64   5/6 
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                6/6 

Installed:
  mysql-community-libs.x86_64 0:8.0.18-1.el7        
  mysql-community-libs-compat.x86_64 0:8.0.18-1.el7        
  mysql-community-server.x86_64 0:8.0.18-1.el7       

Dependency Installed:
  mysql-community-client.x86_64 0:8.0.18-1.el7 
  mysql-community-common.x86_64 0:8.0.18-1.el7                                  

Replaced:
  mariadb-libs.x86_64 1:5.5.64-1.el7                                                                                                                            

Complete!

Now that we have MySQL installed, we can start the server.

$ sudo service mysqld start
Redirecting to /bin/systemctl start mysqld.service

And I can check to make sure MySQL is running:

sudo service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2019-11-14 16:26:34 GMT; 41s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7344 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 7426 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─7426 /usr/sbin/mysqld

Nov 14 16:26:13 instance-20191113-1544 systemd[1]: Starting MySQL Server...
Nov 14 16:26:34 instance-20191113-1544 systemd[1]: Started MySQL Server.

During the installation process, the user ‘root’@’localhost’ is automatically created, along with a password which can be found in the MySQL error log file. To find the password, issue this command: Note: The location of the mysqld.log file may be different for your operating system.

sudo grep 'temporary password' /var/log/mysqld.log
2019-11-14T16:26:25.260720Z 5 [Note] [MY-010454] [Server] 
 \ A temporary password is generated for root@localhost: i&wCaLKQf6Tm

I can now login to the MySQL server using this password. I will also need to change the password before I can do anything else within MySQL. I can do this with the ALTER USER command.

$ mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

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

If you don’t specify a password that matches the default password policy, you will get an error message like this:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'HeyThere!';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL is now installed and ready to use.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

Enterprise Edition

MySQL’s Enterprise Edition (which requires a license) may be installed in the same way, except you must download the the Enterprise Edition files via Oracle’s eDelivery web site.

You will want to download at least the following files: (relative to your operating system)

-rw-rw-r--. 1 opc opc  40154652 Nov  4 21:53 mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm
-rw-rw-r--. 1 opc opc    623112 Nov  4 21:50 mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm
-rw-rw-r--. 1 opc opc   3890924 Nov  4 21:49 mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm
-rw-rw-r--. 1 opc opc   1377676 Nov  4 21:48 mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm
-rw-rw-r--. 1 opc opc 477137056 Nov  4 21:34 mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm

To install, use this command:

$ sudo yum install mysql-commercial-{server,client,common,libs}-*

During the installation process, you should see something like this:

$ sudo yum install mysql-commercial*{server,client,common,libs}-*
Loaded plugins: langpacks, ulninfo
Examining mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-server-8.0.18-1.1.el7.x86_64
Marking mysql-commercial-server-8.0.18-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-client-8.0.18-1.1.el7.x86_64
Marking mysql-commercial-client-8.0.18-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-common-8.0.18-1.1.el7.x86_64
Marking mysql-commercial-common-8.0.18-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-libs-8.0.18-1.1.el7.x86_64
Marking mysql-commercial-libs-8.0.18-1.1.el7.x86_64.rpm to be installed
Examining mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm: mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64
Marking mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package mysql-commercial-client.x86_64 0:8.0.18-1.1.el7 will be installed
---> Package mysql-commercial-common.x86_64 0:8.0.18-1.1.el7 will be installed
---> Package mysql-commercial-libs.x86_64 0:8.0.18-1.1.el7 will be installed
---> Package mysql-commercial-libs-compat.x86_64 0:8.0.18-1.1.el7 will be installed
---> Package mysql-commercial-server.x86_64 0:8.0.18-1.1.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==========================================================================================================================
 Package                           Arch     Version           Repository                                            Size
==========================================================================================================================
Installing:
 mysql-commercial-client           x86_64   8.0.18-1.1.el7    /mysql-commercial-client-8.0.18-1.1.el7.x86_64        177 M
 mysql-commercial-common           x86_64   8.0.18-1.1.el7    /mysql-commercial-common-8.0.18-1.1.el7.x86_64        8.5 M
 mysql-commercial-libs             x86_64   8.0.18-1.1.el7    /mysql-commercial-libs-8.0.18-1.1.el7.x86_64           17 M
 mysql-commercial-libs-compat      x86_64   8.0.18-1.1.el7    /mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64   6.4 M
 mysql-commercial-server           x86_64   8.0.18-1.1.el7    /mysql-commercial-server-8.0.18-1.1.el7.x86_64        2.0 G

Transaction Summary
==========================================================================================================================
Install  5 Packages

Total size: 2.2 G
Installed size: 2.2 G
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-commercial-common-8.0.18-1.1.el7.x86_64             1/5 
  Installing : mysql-commercial-libs-8.0.18-1.1.el7.x86_64               2/5 
  Installing : mysql-commercial-client-8.0.18-1.1.el7.x86_64             3/5 
  Installing : mysql-commercial-server-8.0.18-1.1.el7.x86_64             4/5 
  Installing : mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64        5/5 
  Verifying  : mysql-commercial-server-8.0.18-1.1.el7.x86_64             1/5 
  Verifying  : mysql-commercial-libs-8.0.18-1.1.el7.x86_64               2/5 
  Verifying  : mysql-commercial-common-8.0.18-1.1.el7.x86_64             3/5 
  Verifying  : mysql-commercial-libs-compat-8.0.18-1.1.el7.x86_64        4/5 
  Verifying  : mysql-commercial-client-8.0.18-1.1.el7.x86_64             5/5 

Installed:
  mysql-commercial-client.x86_64 0:8.0.18-1.1.el7
  mysql-commercial-common.x86_64 0:8.0.18-1.1.el7     
  mysql-commercial-libs.x86_64 0:8.0.18-1.1.el7     
  mysql-commercial-libs-compat.x86_64 0:8.0.18-1.1.el7    
  mysql-commercial-server.x86_64 0:8.0.18-1.1.el7    

Complete!

You will need to change the password for root as explained above.

Now you have a free Oracle Cloud compute instance with MySQL running on it. Remember – the “Always Free” tier allows you to create two free compute instances (or virtual machines).

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

MySQL Document Store – a quick-guide to storing JSON documents in MySQL using JavaScript and Python (and even SQL!)

MySQL introduced a JSON data type in version 5.7, and expanded the functionality in version 8.0.

Besides being able to store native JSON in MySQL, you can also use MySQL as a document store (doc store) to store JSON documents. And, you can use NoSQL CRUD (create, read, update and delete) commands in either JavaScript or Python.

This post will show you some of the basic commands for using MySQL as a document store.

Requirements

To use MySQL as a document store, you will need to use the following server features:

The X Plugin enables the MySQL Server to communicate with clients using the X Protocol, which is a prerequisite for using MySQL as a document store. The X Plugin is enabled by default in MySQL Server as of MySQL 8.0. For instructions to verify X Plugin installation and to configure and monitor X Plugin, see Section 20.5, “X Plugin”.

The X Protocol supports both CRUD and SQL operations, authentication via SASL, allows streaming (pipelining) of commands and is extensible on the protocol and the message layer. Clients compatible with X Protocol include MySQL Shell and MySQL 8.0 Connectors.

Clients that communicate with a MySQL Server using X Protocol can use X DevAPI to develop applications. X DevAPI offers a modern programming interface with a simple yet powerful design which provides support for established industry standard concepts. This chapter explains how to get started using either the JavaScript or Python implementation of X DevAPI in MySQL Shell as a client. See X DevAPI for in-depth tutorials on using X DevAPI.
(source: https://dev.mysql.com/doc/refman/8.0/en/document-store.html)

And, you will need to have MySQL version 8.0.x (or higher) installed, as well as the MySQL Shell version 8.0.x (or higher). For these examples, I am using version 8.0.17 of both. (You could use version 5.7.x, but I have not tested any of these commands in 5.7.x)

Starting MySQL Shell (mysqlsh)

When starting MySQL Shell (Shell), you have two session options. The default option is mysqlx (‐‐mx), and this allows the session to connect using the X Protocol. The other option when starting Shell is ‐‐mysql, which establishes a “Classic Session” and connects using the standard MySQL protocol. For this post, I am using the default option of mysqlx (‐‐mx). There are other MySQL Shell command-line options for the X Protocol available. And, there are specific X Protocol variables which may need to be set for the mysqlx connection.

Here is a list of all of the MySQL Shell commands and their shortcuts (for MySQL version 8.0).
(source: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-commands.html)

Command	Alias/Shortcut	Description
\help	\h or \?	Print help about MySQL Shell, or search the online help.
\quit	\q or \exit	Exit MySQL Shell.
\		In SQL mode, begin multiple-line mode. Code is cached and executed when an empty line is entered.
\status	\s	Show the current MySQL Shell status.
\js		Switch execution mode to JavaScript.
\py		Switch execution mode to Python.
\sql		Switch execution mode to SQL.
\connect	\c	Connect to a MySQL Server.
\reconnect		Reconnect to the same MySQL Server.
\use	\u	Specify the schema to use.
\source	\.	Execute a script file using the active language.
\warnings	\W	Show any warnings generated by a statement.
\nowarnings	\w	Do not show any warnings generated by a statement.
\history		View and edit command line history.
\rehash		Manually update the autocomplete name cache.
\option		Query and change MySQL Shell configuration options.
\show		Run the specified report using the provided options and arguments.
\watch		Run the specified report using the provided options and arguments, and refresh the results at regular intervals.
\edit	\e	Open a command in the default system editor then present it in MySQL Shell.
\system	\!	Run the specified operating system command and display the results in MySQL Shell.

To start the MySQL Shell (Shell), you simply execute the mysqlsh command from a terminal window. The default mode is JavaScript (as shown by the JS in the prompt).

$ mysqlsh MySQL Shell 8.0.17-commercial Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. 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.  MySQL  JS

By starting Shell without any variables, you will need to connect to a database instance. You do this with the \connect command, or you can use the \c shortcut. The syntax is \c user@ip_address:

MySQL  JS    \c root@127.0.0.1 Creating a session to 'root@127.0.0.1' Fetching schema names for autocompletion. . .  Press ^C to stop. Your MySQL connection id is 16 (X protocol) Server version: 8.0.17-commercial MySQL Enterprise Server – Commercial No default schema selected; type \use to set one.  MySQL  127.0.0.1:33060+ ssl  JS

Or, to start Shell with the connection information, specify the user and host IP address. The syntax is mysqlsh user@ip_address:

$ mysqlsh root@127.0.0.1 MySQL Shell 8.0.17-commercial Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. 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@127.0.0.1' Fetching schema names for autocompletion. . .  Press ^C to stop. Your MySQL connection id is 18 (X protocol) Server version: 8.0.17-commercial MySQL Enterprise Server – Commercial No default schema selected; type \use to set one.  MySQL  127.0.0.1:33060+ ssl  JS

You may find a list of all of the command-line options at https://dev.mysql.com/doc/mysql-shell/8.0/en/mysqlsh.html.

The Shell prompt displays the connection information, whether or not you are using ssl, and your current mode (there are three modes – JavaScript, Python and SQL). In the earlier example, you are in the (default) JavaScript mode. You can also get your session information with the session command:

MySQL  127.0.0.1:33060+ ssl  JS    session <Session:root@127.0.0.1:33060>

All of these commands are case-sensitive, so if you type an incorrect command, you will see the following error:

MySQL  127.0.0.1:33060+ ssl  JS    Session ReferenceError: Session is not defined

Here is how you switch between the three modes: – JavaScript, Python and SQL.

MySQL  127.0.0.1:33060+ ssl  JS    \sql Switching to SQL mode. . .  Commands end with ;  MySQL  127.0.0.1:33060+ ssl  SQL    \py Switching to Python mode. . .   MySQL  127.0.0.1:33060+ ssl  Py    \js Switching to JavaScript mode. . .   MySQL  127.0.0.1:33060+ ssl  JS

There are also several different output formats. You may change the output using the shell.options.set command. The default is table. Here are examples of each one, using the same command:

table output format

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','table')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('select user, host from mysql.user') +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+ | user             | host      | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+ | mysql.infoschema | localhost | | mysql.session    | localhost | | mysql.sys        | localhost | | root             | localhost | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐+ 4 rows in set (0.0005 sec)

JSON output format

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','json')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('select user, host from mysql.user limit 2') {     “user”: “mysql.infoschema”,     “host”: “localhost” } {     “user”: “mysql.session”,     “host”: “localhost” } 2 rows in set (0.0005 sec)

tabbed format

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','tabbed')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('select user, host from mysql.user') user     host mysql.infoschema     localhost mysql.session     localhost mysql.sys     localhost root     localhost 4 rows in set (0.0004 sec)

vertical format

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','vertical')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('select user, host from mysql.user order by user desc') *************************** 1. row *************************** user: mysql.infoschema host: localhost *************************** 2. row *************************** user: mysql.session host: localhost *************************** 3. row *************************** user: mysql.sys host: localhost *************************** 4. row *************************** user: root host: localhost 4 rows in set (0.0005 sec)

MySQL Shell – Create & Drop Schema

Note: With the MySQL Doc Store, the terms to describe the database, table and rows are different. The database is called the schema (even thought the doc store is “schema-less”). The tables are called collections, and the rows of data are called documents.

To create a schema named test1, use the createSchema command:

MySQL  127.0.0.1:33060+ ssl  JS    session.createSchema("test1") <Schema:test1>

To get a list of the current schemas, use the getSchemas command:

MySQL  127.0.0.1:33060+ ssl  JS    session.getSchemas() [ <Schema:information_schema>, <Schema:mysql>, <Schema:performance_schema>, <Schema:sys>, <Schema:test1> ]

Also, you can run SQL commands inside of the Doc Store – something you can't natively do with most other NoSQL databases. Instead of using the getSchemas command, you can issue a SHOW DATABASES SQL command using the runSql NoSQL command.

MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('show databases') +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | Database           | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | information_schema | | mysql              | | performance_schema | | sys                | | test1              | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ 5 rows in set (0.0052 sec)

To drop a schema named test1, you use the dropSchema command:

MySQL  127.0.0.1:33060+ ssl  JS    session.dropSchema("test1")

Just like with MySQL, you have to select the schema (database) you want to use. This shows you how to create a schema and set it as your default schema. With the \use command, you are really setting a variable named db to equal the default schema. So, after you have set your schema with the \use command, when you issue the db command, you can see the default schema.

MySQL  127.0.0.1:33060+ ssl  JS    session.createSchema("workshop")  MySQL  127.0.0.1:33060+ ssl  JS    \use workshop Default schema `workshop` accessible through db.  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db <Schema:workshop>

To change the value of the variable db, you may use the \use command or you may set the value of db using the var (variable) command. The command session.getSchema will return a schema value, but it does not automatically set the db variable value.)

MySQL  127.0.0.1:33060+ ssl  JS    \use workshop Default schema `workshop` accessible through db.  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db <Schema:workshop>  MySQL  127.0.0.1:33060+ ssl  workshop  JS    session.getSchema('mysql'); <Schema:mysql>  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db <Schema:workshop>  MySQL  127.0.0.1:33060+ ssl  workshop  JS    var db = session.getSchema('mysql');  MySQL  127.0.0.1:33060+ ssl  mysql  JS    db <Schema:mysql>

You can also create your own variables. Here is an example of setting the variable sdb to equal the SQL command SHOW DATABASES:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    var sdb = session.runSql('show databases')  MySQL  127.0.0.1:33060+ ssl  workshop  JS     sdb +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | Database           | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | information_schema | | mysql              | | performance_schema | | sys                | | workshop           | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ 6 rows in set (0.0079 sec)

But the variables are only good for your current session. If you quit Shell, log back in, and re-run the variable, you will get an error stating the variable is not defined.

MySQL  127.0.0.1:33060+ ssl  workshop  JS     \q Bye! # mysqlsh root@127.0.0.1 . . .   MySQL  127.0.0.1:33060+ ssl  JS    sdb ReferenceError: sdb is not defined

After you have created your schema (database), and selected it via \use command, then you can create a collection (table) and insert JSON documents into the collection. I will create a collection named test1.

MySQL  127.0.0.1:33060+ ssl  JS    \use workshop  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.createCollection("test1")

You can get a list of collections by using the getCollections command, and you can also execute SQL commands with the session.runSql command. The getCollections command is the same as the SQL command SHOW TABLES.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.getCollections() [ <Collection:test1> ]  MySQL  127.0.0.1:33060+ ssl  workshop  JS    session.runSql('SHOW TABLES') +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | Tables_in_workshop | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ | test1              | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+ 1 row in set (0.0001 sec)

To drop a collection, use the dropCollection command. You can verify the collection was dropped by using the getCollections command again.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.dropCollection("test1")  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.getCollections() []

If you have not selected a default database, you may also specify the schema name prior to the collection name (which is the same type of syntax when you create a table inside of a database in MySQL). You will notice the output is different, as when you specify the schema name before the collection name, that schema name is returned as well.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.createCollection("foods") <Collection:foods>  MySQL  127.0.0.1:33060+ ssl  JS    db.createCollection("workshop.foods2") <Collection:workshop.foods2>

To add a JSON document to a collection, you use the add command. You must specify a collection prior to using the add command. (You can't issue a command like workshop.foods.add.) You can add the document with or without the returns and extra spaces (or tabs). Here the JSON document to add to the collection named workshop:

{     Name_First: "Fox",     Name_Last: "Mulder",     favorite_food: {         Breakfast: "eggs and bacon",         Lunch: "pulled pork sandwich",         Dinner: "steak and baked potato"    }  }

---

Note: Some JSON formats require double quotes around the keys/strings. In this example, Name_First has double quotes in the first example, and the second example doesn’t have double quotes. Both formats will work in Shell.

The -> in the examples below is added by Shell. You don’t need to type this on the command line.

---

 

 MySQL  127.0.0.1:33060+ ssl  project  JS    db.foods.add({
                                          -> "Name_First": "Steve"})
                                          ->
Query OK, 1 item affected (0.0141 sec)
 MySQL  127.0.0.1:33060+ ssl  project  JS    db.foods.add({
                                          -> Name_First: "Steve"})
                                          ->
Query OK, 1 item affected (0.0048 sec)

---

Here are examples of each method of adding a JSON document – one where all of the data in the JSON document is on one line, and another where the JSON document contains data on multiple lines with returns and spaces included.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.add({Name_First: "Fox", Name_Last: "Mulder", favorite_food: {Breakfast: "eggs and bacon", Lunch: "pulled pork sandwich", Dinner: "steak and baked potato"}}) Query OK, 1 item affected (0.0007 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.add( {                                           ->  Name_First: "Fox",                                           ->  Name_Last: "Mulder",                                           ->  favorite_food: {                                           ->      Breakfast: "eggs and bacon",                                           ->      Lunch: "pulled pork sandwich",                                           ->      Dinner: "steak and baked potato"                                           ->  } } )                                           ->  Query OK, 1 item affected (0.0005 sec)

So far, I have created a schema, and a collection, and I have added one document to the collection. Next, I will show you how to perform searches using the find command.

Searching Documents

To find all records in a collection, use the find command without specifying any search criteria:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find() {     "_id": "00005d6fc3dc000000000027e065",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": {         "Lunch": "pulled pork sandwich",         "Dinner": "steak and baked potato",         "Breakfast": "eggs and bacon"     } } 1 document in set (0.0002 sec)

---

Note: The _id key in the output above is automatically added to each document and the value of _id cannot be changed. Also, an index is automatically created on the _id column. You can check the index using the SQL SHOW INDEX command. (To make the output easier to view, I switched the format to vertical and I switched it back to table)
 

MySQL  127.0.0.1:33060+ ssl  workshop  JS     shell.options.set('resultFormat','vertical')  MySQL  127.0.0.1:33060+ ssl  workshop  JS    session.runSql('SHOW INDEX FROM workshop.foods') *************************** 1. row ***************************         Table: foods    Non_unique: 0      Key_name: PRIMARY  Seq_in_index: 1   Column_name: _id     Collation: A   Cardinality: 1      Sub_part: NULL        Packed: NULL          Null:     Index_type: BTREE       Comment:  Index_comment:        Visible: YES    Expression: NULL 1 row in set (0.0003 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS     shell.options.set('resultFormat','table')

---

 
Here is how you perform a search by specifying the search criteria. This command will look for the first name of Fox via the Name_First key.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find('Name_First = "Fox"') {     "_id": "00005d6fc3dc000000000027e065",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": {         "Lunch": "pulled pork sandwich",         "Dinner": "steak and baked potato",         "Breakfast": "eggs and bacon"     } } 1 document in set (0.0004 sec)

And, if your search result doesn't find any matching documents, you will get a return of Empty set and the time it took to run the query:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find('Name_First = "Jason"') Empty set (0.0003 sec)

The first search returned all of the keys in the document because I didn’t specify any search criteria inside the find command – db.foods.find(). This example is how you retrieve just a single key inside a document. And notice the key favorite_food contains a list of sub-keys inside of a key.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find('Name_First = "Fox"').fields('favorite_food') {     “favorite_food”: {             “Lunch”: “pulled pork sandwich”,             “Dinner”: “steak and baked potato”,             “Breakfast”: “eggs and bacon”         } } 1 document in set (0.0004 sec)

To return multiple keys, add the additional keys inside the fields section, separated by a comma.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find('Name_First = "Fox"').fields("favorite_food", "Name_Last") {     “Name_Last”: “Mulder”,     “favorite_food”: {             “Lunch”: “pulled pork sandwich”,             “Dinner”: “steak and baked potato”,             “Breakfast”: “eggs and bacon”         } } 1 document in set (0.0003 sec)

---

Note: The fields are returned in the order they are stored in the document, and not in the order of the fields section. The query field order does not guarantee the same display order.

---

 
To return a sub-key from a list, you add the key prior to the sub-key value in the fields section. This search will return the value for the Breakfast sub-key in the favorite_food key.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find( 'Name_First = "Fox"' ).fields( "favorite_food.Breakfast" ) {     “favorite_food.Breakfast”: “eggs and bacon” } 1 document in set (0.0002 sec)

Modifying Documents

To modify a document, you use the modify command, along with search criteria to find the document(s) you want to change. Here is the original JSON document.

{     “_id”: “00005d6fc3dc000000000027e065”,     “Name_Last”: “Mulder”,     “Name_First”: “Fox”,     “favorite_food”: {         “Lunch”: “pulled pork sandwich”,         “Dinner”: “steak and baked potato”,         “Breakfast”: “eggs and bacon”     } }

I am going to change Fox's favorite_food sub-keys to Lunch being “soup in a bread bowl” and Dinner to “steak and broccoli”.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.modify("Name_First = 'Fox'").set("favorite_food", {Lunch: "Soup in a bread bowl", Dinner: "steak and broccoli"}) Query OK, 0 items affected (0.0052 sec)

I can see the changes by issuing a find command, searching for Name_First equal to “Fox”.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find('Name_First = "Fox"') {     "_id": "00005d6fc3dc000000000027e065",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": {         “Lunch”: "Soup in a bread bowl",         "Dinner": "steak and broccoli"     } } 1 document in set (0.0004 sec)

Notice that the Breakfast sub-key is no longer in the list. This is because I changed the favorite_food list to only include Lunch and Dinner.

To change only one sub-key under favorite_food, such as Dinner, I can do that with the set command:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.modify("Name_First = 'Fox'").set('favorite_food.Dinner', 'Pizza') Query OK, 1 item affected (0.0038 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     "_id": "00005d8122400000000000000002",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": {         "Lunch": "Soup in a bread bowl",         "Dinner": "Pizza",     } }

If I want to remove a single sub-key under favorite_food, I can use the unset command. I will remove the Dinner sub-key:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.modify("Name_First = 'Fox'").unset("favorite_food.Dinner") Query OK, 1 item affected (0.0037 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     "_id": "00005d8122400000000000000002",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": {         "Lunch": "Soup in a bread bowl",     } }

The key favorite_food contained a list, but a key can also contain an array. The main format difference in an array and a list is an array has opening and closing square brackets [ ]. The brackets go on the outside of the values for the array.

Here is the original document with a list for favorite_food:

db.foods.add( {     Name_First: "Fox",     Name_Last: "Mulder",     favorite_food: {         Breakfast: "eggs and bacon",         Lunch: "pulled pork sandwich",         Dinner: "steak and baked potato"  }  } )

I am going to delete the document for Fox, confirm the deletion occurred and insert a new document – but this time, I am going to use an array for favorite_food (and notice the square brackets).

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.remove('Name_First = "Fox"') Query OK, 1 item affected (0.0093 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") Empty set (0.0003 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.add( {                                          ->     Name_First: "Fox",                                          ->     Name_Last: "Mulder",                                          ->     favorite_food: [ {                                           ->        Breakfast: "eggs and bacon",                                          ->        Lunch: "pulled pork sandwich",                                          ->        Dinner: "steak and baked potato"                                          ->   } ]                                           -> }                                           -> )                                          ->  Query OK, 1 item affected (0.0078 sec)  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     "_id": "00005da09064000000000027e068",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": [         {             "Lunch": "pulled pork sandwich",             "Dinner": "steak and baked potato",             "Breakfast": "eggs and bacon"         }     ] } 1 document in set (0.0004 sec)

When dealing with an array, I can modify each element in the array, or I can add another array element. I am going to add Fox's favorite Snack to the array with the arrayAppend command:

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.modify("Name_First = 'Fox'").arrayAppend("favorite_food", {Snack: "Sunflower seeds"}) Query OK, 1 item affected (0.0048 sec) Rows matched: 1 Changed: 1 Warnings: 0  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     “_id”: “00005da09064000000000027e068”,     “Name_Last”: “Mulder”,     “Name_First”: “Fox”,     “favorite_food”: [         {             “Lunch”: “pulled pork sandwich”,             “Dinner”: “steak and baked potato”,             “Breakfast”: “eggs and bacon”         },         {             “Snack”: “Sunflower seeds”         }     ] } 1 document in set (0.0004 sec)

The key favorite_food now contains an array with two separate values in it. The sub-keys in the array position favorite_food[0] contains values for Lunch, Breakfast and Dinner values, while the array position favorite_food[1] only contains the Snack value.

---

Note: If you aren't familiar with arrays, an array is like a list that contains elements. Elements of the array are contained in memory locations relative to the beginning of the array. The first element in the array is actually zero (0) elements away from the beginning of the array. So, the placement of the first element is denoted as array position zero (0) and this is designated by [0]. Most programming languages have been designed this way, so indexing from zero (0) is pretty much inherent to most languages.

---

 
I can now delete an element in an array with the arrayDelete command. I am going to remove the Snack array member, which is favorite_food[1].

MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.modify("Name_First = 'Fox'").arrayDelete("$.favorite_food[1]") Query OK, 1 item affected (0.0035 sec) Rows matched: 1 Changed: 1 Warnings: 0  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     "_id": "00005da09064000000000027e068",     "Name_Last": "Mulder",     "Name_First": "Fox",     "favorite_food": [         {             "Lunch": "pulled pork sandwich",             "Dinner": "steak and baked potato",             "Breakfast": "eggs and bacon"         }     ] } 1 document in set (0.0004 sec)

Modifying a document – adding a key

If I want to add an extra key, I need to add a few more variables and their values. I will need to define my schema and collection. Then I can use the patch command to add a key to an existing document.

I am going to add a middle name to Fox's document.

MySQL  127.0.0.1:33060+ ssl  workshop  JS    var schema = session.getSchema('workshop')  MySQL  127.0.0.1:33060+ ssl  workshop  JS    var collection = schema.getCollection('foods')  MySQL  127.0.0.1:33060+ ssl  workshop  JS    collection.modify("Name_First = 'Fox'").patch({ Name_Middle: 'William' }) Query OK, 1 item affected (0.0020 sec) Rows matched: 1 Changed: 1 Warnings: 0  MySQL  127.0.0.1:33060+ ssl  workshop  JS    db.foods.find("Name_First='Fox'") {     "_id": "00005da09064000000000027e068",     "Name_Last": "Mulder",     "Name_First": "Fox",     "Name_Middle": "William",     "favorite_food": [         {             "Lunch": "pulled pork sandwich",             "Dinner": "steak and baked potato",             "Breakfast": "eggs and bacon"         }     ] } 1 document in set (0.0004 sec)

Importing Large Data Sets

In order to demonstrate indexes, I will need to import a large amount of data. Before I import data, I need to be sure that I have a variable set for the mysqlx client protocol to allow larger client packets. Specifically, I need to set the mysqlx_max_allowed_packet variable to the largest allowable size of 1TB. You can set this variable in the MySQL configuration file (my.cnf or my.ini) and reboot the MySQL instance, or you can set it for your session.

I can check the values of the mysqlx_max_allowed_packet variable from within Shell, and if it isn't set to 1TB, I will modify it for this session. (I can see the value for mysqlx_max_allowed_packet is set to 100MB or 100 megabytes)

MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SHOW VARIABLES like "%packet%"') +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | Variable_name             | Value      | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | max_allowed_packet        | 943718400  | | mysqlx_max_allowed_packet | 104857600  | | slave_max_allowed_packet  | 1073741824 | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ 3 rows in set (0.0021 sec)  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SET @@GLOBAL.mysqlx_max_allowed_packet = 1073741824') Query OK, 0 rows affected (0.0004 sec)  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SHOW VARIABLES like "%packet%"') +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | Variable_name             | Value      | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ | max_allowed_packet        | 943718400  | | mysqlx_max_allowed_packet | 1073741824 | | slave_max_allowed_packet  | 1073741824 | +‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐+‐‐‐‐‐‐‐‐‐‐‐‐+ 3 rows in set (0.0021 sec)

I don't want to write this large import to the binary log (I have binlog enabled), so I can use the SQL command to SET SQL_LOG_BIN = 0 first. I am going to create a new collection named project, and then import a 400+ megabyte JSON file into the new collection.

MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SET SQL_LOG_BIN=0') Query OK, 0 rows affected (0.0464 sec)  MySQL  127.0.0.1:33060+ ssl  JS    session.createSchema("project")  MySQL  127.0.0.1:33060+ ssl  JS    \use project Default schema `project` accessible through db.  MySQL  127.0.0.1:33060+ ssl  JS     util.importJson("./workshop/Doc_Store_Demo_File.json", {schema: "project", collection: "GoverningPersons"}) Importing from file "./workshop/Doc_Store_Demo_File.json" to collection `project`.`GoverningPersons` in MySQL Server at 127.0.0.1:33060 .. 2613346.. 2613346 Processed 415.71 MB in 2613346 documents in 2 min 51.0696 sec (15.28K documents/s) Total successfully imported documents 2613346 (15.28K documents/s)

As you can see from the output above, I imported 2,613,346 documents.
Note: This imported JSON document contains public data regarding businesses in the State of Washington.

Now that I have my 2.6 million documents in the database, I will do a quick search and limit the results to one record by using the limit command. This will show you the keys in the document.

MySQL  127.0.0.1:33060+ ssl  JS    \use project Default schema `project` accessible through db.  MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find().limit(1) {     "Ubi": "601544680",     "Zip": "99205",     "_id": "00005d6fc3dc000000000027e067",     "City": "SPOKANE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "RT 5",     "LastName": "FRISCH",     "FirstName": "BOB",     "MiddleName": "" } 1 document in set (0.0022 sec)

Again – notice an index for the key _id has automatically been added:

MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SHOW INDEX FROM project.GoverningPersons') *************************** 1. row *************************** Table: GoverningPersons Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: _id Collation: A Cardinality: 2481169 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 1 row in set (0.0137 sec)

To demonstrate indexes on a large dataset, I will perform two searches against all documents in the project collection – where LastName is equal to FRISCH and where LastName is equal to VARNELL. Then, I will create the index on LastName, and re-run the two find queries. Note: I am not displaying all of the returned documents to save space.

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find('LastName = "FRISCH"') {     "Ubi": "601544680",     "Zip": "99205",     "_id": "00005da09064000000000027e069",     "City": "SPOKANE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "RT 5",     "LastName": "FRISCH",     "FirstName": "BOB",     "MiddleName": "" } . . . 82 documents in set (1.3559 sec)

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find('LastName = "VARNELL"') {     "Ubi": "602268651",     "Zip": "98166",     "_id": "00005da09064000000000028ffdc",     "City": "SEATTLE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "18150 MARINE VIEW DR SW",     "LastName": "VARNELL",     "FirstName": "JAMES",     "MiddleName": "" } . . . 33 documents in set (1.0854 sec)

The searches took 1.3559 and 1.0854 seconds, respectively. I can now create an index on LastName. When I create an index, I have to specify the data type for that particular key. And for a text key, I have to specify how many characters of that key I want to include in the index. (Note: see TEXT(20) in the command below)

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.createIndex("i_Name_Last", {fields: [{field: "$.LastName", type: "TEXT(20)"}]}) Query OK, 0 rows affected (8.0653 sec)

Now I will re-run the same two queries where LastName equals FRISCH and where LastName equals VARNELL.

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find('LastName = "FRISCH"') {     "Ubi": "601544680",     "Zip": "99205",     "_id": "00005da09064000000000027e069",     "City": "SPOKANE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "RT 5",     "LastName": "FRISCH",     "FirstName": "BOB",     "MiddleName": "" } . . . 82 documents in set (0.0097 sec)

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find('LastName = "VARNELL"') {     "Ubi": "602268651",     "Zip": "98166",     "_id": "00005da09064000000000028ffdc",     "City": "SEATTLE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "18150 MARINE VIEW DR SW",     "LastName": "VARNELL",     "FirstName": "JAMES",     "MiddleName": "" } . . . 33 documents in set (0.0008 sec)

The queries ran much faster with an index – 0.0097 seconds and 0.0008 seconds. Not bad for searching 2.6 million records.

---

Note: The computer I was using for this post is a Hackintosh, running Mac OS 10.13.6, with an Intel i7-8700K 3.7GHz processor with six cores, with 32GB 2666MHz DDR4 RAM, and an SATA III 6 Gb/s, M.2 2280 SSD. Your performance results may vary.

---

 
And I can take a look at the index for LastName:

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','vertical')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SHOW INDEX FROM project.GoverningPersons') *************************** 1. row *************************** Table: GoverningPersons Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: _id Collation: A Cardinality: 2481169 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 2. row *************************** Table: GoverningPersons Non_unique: 1 Key_name: i_Name_Last Seq_in_index: 1 Column_name: $ix_t20_F1A785D3F25567CD94716D955607AADB04BB3C0E Collation: A Cardinality: 300159 Sub_part: 20 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 2 rows in set (0.0059 sec)  MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','table')

I can create an index on multiple columns as well. Here is an index created on the State and Zip fields.

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.createIndex('i_state_zip', {fields: [ {field: '$.State', type: 'TEXT(2)'}, {field: '$.Zip', type: 'TEXT(10)'}]}) Query OK, 0 rows affected (10.4536 sec)

I can take a look at the index as well (the other index results were removed to save space).

MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','vertical')  MySQL  127.0.0.1:33060+ ssl  JS    session.runSql('SHOW INDEX FROM project.GoverningPersons') . . . *************************** 3. row *************************** Table: GoverningPersons Non_unique: 1 Key_name: i_state_zip Seq_in_index: 1 Column_name: $ix_t2_00FFBF570DC47A52910DDA38C0C1FB1361F0426A Collation: A Cardinality: 864 Sub_part: 2 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL *************************** 4. row *************************** Table: GoverningPersons Non_unique: 1 Key_name: i_state_zip Seq_in_index: 2 Column_name: $ix_t10_18619E3AC96C74FECCF6B622D9DB0864C2938AB6 Collation: A Cardinality: 215626 Sub_part: 10 Packed: NULL Null: YES Index_type: BTREE Comment: Index_comment: Visible: YES Expression: NULL 4 rows in set (0.0066 sec)  MySQL  127.0.0.1:33060+ ssl  JS    shell.options.set('resultFormat','table')

I will run a query looking for the first entry FRISH based upon his state (WA) and Zip (99205). The query result time is still pretty good, even though I am also returning his LastName.

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.find("State='WA' AND Zip = '99205' AND LastName = 'FRISCH'") {     "Ubi": "601544680",     "Zip": "99205",     "_id": "00005da09064000000000027e069",     "City": "SPOKANE",     "State": "WA",     "Title": "GOVERNOR",     "Address": "RT 5",     "LastName": "FRISCH",     "FirstName": "BOB",     "MiddleName": "" } 1 document in set (0.0011 sec)

To drop an index, use the dropIndex command:

MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.dropIndex("i_Name_Last")

NoSQL and SQL in the same database

The great advantage to using MySQL to store JSON documents is that the data is stored in the InnoDB storage engine. So, the NoSQL document store database has features and benefits of InnoDB – such as transactions and ACID-compliance. This also means that you can use MySQL features like replication, group replication, transparent data encryption, etc. And if you need to restore a backup and play back the binlog files for a point-in-time recovery, you can do that as well, as all of the NoSQL transactions are written to the MySQL Binary Log. Here is what a NoSQL transaction looks like in the MySQL binary log:

NoSQL:

collection.modify(""Name_First = 'Selena'").patch({ Name_Middle: 'Kitty' })

MySQL Binlog:

# at 3102 #191018 11:17:41 server id 3451 end_log_pos 3384 CRC32 0xd0c12cca Query thread_id=15 exec_time=0 error_code=0 SET TIMESTAMP=1571411861/*!*/; UPDATE `workshop`.`foods` SET doc=JSON_SET(JSON_MERGE_PATCH(doc, JSON_OBJECT('Name_Middle','Kitty')),'$._id',JSON_EXTRACT(`doc`,'$._id')) WHERE (JSON_EXTRACT(doc,'$.Name_First') = 'Selena') /*!*/; # at 3384 #191018 11:17:41 server id 3451 end_log_pos 3415 CRC32 0xe0eaf4ef Xid = 246 COMMIT/*!*/; SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ;

Finally, here is an example of how to do a transaction:

MySQL  127.0.0.1:33060+ ssl  JS    session.startTransaction() Query OK, 0 rows affected (0.0013 sec)  MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.modify("Ubi = '601544680'").set("MiddleName", "PETER") Query OK, 1 item affected (0.0089 sec) Rows matched: 1 Changed: 1 Warnings: 0  MySQL  127.0.0.1:33060+ ssl  JS    session.rollback() Query OK, 0 rows affected (0.0007 sec)  MySQL  127.0.0.1:33060+ ssl  JS    db.GoverningPersons.modify("Ubi = '601544680'").set("MiddleName", "STEVEN") Query OK, 1 item affected (0.0021 sec) Rows matched: 1 Changed: 1 Warnings: 0  MySQL  127.0.0.1:33060+ ssl  JS    session.commit() Query OK, 0 rows affected (0.0002 sec)

JSON functions

Since the MySQL Document Store utilizes the MySQL database server and since the documents are stored in InnoDB, you can also use MySQL SQL JSON functions as well to manipulate the data stored in either a JSON document or in a JSON data type. Here is a list of the JSON functions available, and while JSON functions were introduced in 5.7, not all of these functions will be in 5.7 – but they are all in version 8.0.

JSON_ARRAY	JSON_ARRAY_APPEND	JSON_ARRAY_INSERT	JSON_CONTAINS
JSON_CONTAINS_PATH	JSON_DEPTH	JSON_EXTRACT	JSON_INSERT
JSON_KEYS	JSON_LENGTH	JSON_MERGE_PATCH	JSON_MERGE_PRESERVE
JSON_OBJECT	JSON_OVERLAPS	JSON_PRETTY	JSON_QUOTE
JSON_REMOVE	JSON_REPLACE	JSON_SCHEMA_VALID	JSON_SCHEMA_VALIDATION_REPORT
JSON_SEARCH	JSON_SET	JSON_STORAGE_FREE	JSON_STORAGE_SIZE
JSON_TABLE	JSON_TYPE	JSON_UNQUOTE	JSON_VALID
MEMBER OF

But you already use Mongo? And you have MySQL as well?

If you already use Mongo and MySQL, and you want to switch to MySQL, or if your DBA's already know Mongo, then moving to MySQL or using the MySQL doc store is pretty easy. The commands used in Mongo are very similar to the ones used in the MySQL Document Store. The login command is similar to the MySQL “regular” client command in that you specify the user and password with the -u and -p.

In MySQL Shell, you put the username followed by the @ and the IP address. And you can specify which database or schema you want to use upon login. Mongo does have a few shortcuts with the show command, as in show dbs, show schemas or show collections. But you can do almost the same in MySQL Shell by setting variables to equal certain functions or commands (like I did earlier.)

To create a schemda/database in Mongo, you simply execute the use database name command – and it creates the schema/database if it doesn't exist. The other commands having to do with collections are almost the same.

Where there are differences, they are relatively small. Mongo uses the command insert and MySQL uses add when inserting documents. But then, other commands such as the remove document command are the same.

As for the last item in the table below, native Mongo can't run any SQL commands (without using a third-party software GUI tool) – so again, if you have MySQL DBA's on staff, the learning curve can be lower because they can use SQL commands if they don't remember the NoSQL commands.

Command	Mongo	MySQL Document Store (via Shell)
Login	mongo ‐u ‐p ‐‐database	mysqlsh root@127.0.0.1 ‐‐database
Select schema	use database_name	\use database_name
Show schemas/dbs	show dbs	session.getSchemas()
Create schema	use database_name	session.createSchema()
Show collections	show collections or db.getCollectionNames();	db.getCollections()
Create collection	db.createCollection("collectionName");	db.createCollection("collectionName");
Insert document	db.insert({field1: "value", field2: "value"})	db.insert({field1: "value", field2: "value"})
Remove document	db.remove()	db.remove()
Run SQL command		session.runSql(SQL_command)

So, with MySQL, you have a hybrid solution where you can use both SQL and NoSQL at the same time. And, since you are storing the JSON documents inside MySQL, you an also use the MySQL JSON functions to search, update and delete the same records.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

MySQL Server Deployment with Docker – Basic Installation Instructions for Both the Community and Enterprise Versions

An easy way to setup one or multiple MySQL server deployments on a single server is to use Docker – a computer program that performs operating-system-level virtualization. Docker is simple-to-use and allows you to run multiple containers at once.

A container is a standard unit of software that packages up code and all its dependencies so the application runs quickly and reliably from one computing environment to another. A Docker container image is a lightweight, standalone, executable package of software that includes everything needed to run an application: code, runtime, system tools, system libraries and settings. (Source: https://www.docker.com/resources/what-container)

In other words, think of a container as a virtual machine without the graphical user interface (GUI). There are third-party GUI’s available, but for this post, I am going to use a terminal window.

I am not a Docker expert, so I did have to spend some time figuring out the basics. But with this tutorial, you should be able to install the Docker software and a MySQL server in less than a fifteen minutes.

---

Let’s get started

First, you will need to download and install Docker. I am not going to cover this part, but installation is fairly straightforward. I downloaded and installed the Docker Desktop for my Mac.

I already have a MySQL instance installed on my server, so I will install this new instance using a different port number than the default port of 3306. On the server-side, I will use port 3307 to connect to the default MySQL port of 3306 inside the Docker container. By using a different external port number, I can install multiple MySQL instances on one server, but still use the default port for the MySQL instance. MySQL has their own set of Docker container images on github, and I can install MySQL directly from the command line. I don’t have to download anything separately. You can create your own local repository, but for this example, Docker will pull the latest version from MySQL’s github page.

To install MySQL, I opened a terminal window and ran the following command – changing the first port number (the external port) to 3307. The second port number is the port for the MySQL instance inside the container. You will notice that Docker first checks the local repository, and then once it can’t locate it, it goes out to github. Installation is done via the Docker run command.

$ docker run -p 3307:3306 -d --name mysql -e MYSQL_ROOT_PASSWORD=password mysql/mysql-server
Unable to find image 'mysql/mysql-server:latest' locally
latest: Pulling from mysql/mysql-server
35defbf6c365: Pull complete 
e13cf68584a3: Pull complete 
259d03b6a792: Pull complete 
892ac46af8c0: Pull complete 
Digest: sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Status: Downloaded newer image for mysql/mysql-server:latest
d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c

I can then verify to see if the MySQL container is up and running via the Docker container command:

$ docker container ls
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                            PORTS                               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh mysq…"   2 minutes ago      Up 3 seconds (health: starting)   33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

---

Note:To start or stop the container, simply type use the Docker start/stop command, where mysql is the name of the container – and not the application being run inside the container:

$ docker stop mysql
mysql
$ docker start mysql
mysql

---

Note: If the container isn’t running and you need to start it, you will see an error like this when you try and connect to the container:

$ docker exec -it mysql bash
Error response from daemon: Container d8695b074a014f31c65112fb00ec1e5ad79d4c5ba94eb3be1d0fa424f14f414c is not running

---

I now have a copy of the MySQL container image stored locally on my server. I can look at all of the Docker images installed so far with the Docker images command:

$ docker images -a
REPOSITORY           TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server   latest              39649194a7e7        2 weeks ago         289MB

I can verify if MySQL is running by using the Docker container command:

$ docker container ls -a
CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS                     PORTS               NAMES
0b55334fedcb        mysql/mysql-server   "/entrypoint.sh --ip…"   23 seconds ago      Exited (1) 22 seconds ago                       mysql

Now that I have MySQL installed and I have verified that the container is running, I can connect to the container using the Docker exec command: (The word mysql is the container name, and not the mysql database instance)

$ docker exec -it mysql bash
bash-4.2# 

After connecting, I am now at a regular Linux prompt. The MySQL data directory is stored in /var/lib/mysql, and the configuration file is in /etc/my.cnf.

bash-4.2# cd /var/lib/mysql
bash-4.2# ls -l
total 174160
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 #innodb_temp
-rw-r----- 1 mysql mysql       56 May  9 17:10 auto.cnf
-rw-r----- 1 mysql mysql      178 May  9 17:10 binlog.000001
-rw-r----- 1 mysql mysql      155 May  9 17:10 binlog.000002
-rw-r----- 1 mysql mysql       32 May  9 17:10 binlog.index
-rw------- 1 mysql mysql     1676 May  9 17:10 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 ca.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 client-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 client-key.pem
-rw-r----- 1 mysql mysql     5456 May  9 17:10 ib_buffer_pool
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 May  9 17:10 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibdata1
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 ibtmp1
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 mysql
-rw-r----- 1 mysql mysql 29360128 May  9 17:10 mysql.ibd
srwxrwxrwx 1 mysql mysql        0 May  9 17:10 mysql.sock
-rw------- 1 mysql mysql        2 May  9 17:10 mysql.sock.lock
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 performance_schema
-rw------- 1 mysql mysql     1676 May  9 17:10 private_key.pem
-rw-r--r-- 1 mysql mysql      452 May  9 17:10 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 May  9 17:10 server-cert.pem
-rw------- 1 mysql mysql     1676 May  9 17:10 server-key.pem
drwxr-x--- 2 mysql mysql     4096 May  9 17:10 sys
-rw-r----- 1 mysql mysql 12582912 May  9 17:10 undo_001
-rw-r----- 1 mysql mysql 10485760 May  9 17:10 undo_002
bash-4.2# ls -l /etc/my.cnf
-rw-r--r-- 1 root root 1239 May  9 17:10 /etc/my.cnf

I can log into MySQL the same way as if it was a regular MySQL instance. (When I created the container, I used “password” as the password, but you will want a more secure password)

bash-4.2# mysql -uroot -ppassword
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 18
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 

Remember – since I am not connecting to the instance from outside of Docker, I don’t have to use port 3307. But, I will have to do that if I want to connect via MySQL Workbench.

Before I connect via MySQL Workbench, I will want to create a different user for this connection, and use this user for my Workbench connection:

mysql> CREATE USER 'docker'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'docker'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

I can now create a MySQL Workbench connection. I will open Workbench, and click on the plus symbol to create a new connection.

I need to provide a connection name (Docker Container 3307), the hostname (127.0.0.1) and I need to specify port 3307. If you don’t have another installation of MySQL on your server, you can use the default port of 3306. I will store the password in my keychain by clicking on “Store in Keychain”.

To test and see if you have the correct information, click the “Test Connection” button

I can now use MySQL Workbench to connect to the MySQL Docker container:

That’s it. I now have MySQL installed as a Docker container, and I can access it via a terminal window or via Workbench.

---

MySQL Enterprise Version

The MySQL Community Edition is a great database server, but if you are going to run a database in a production environment, I would recommend you purchasing a MySQL Enterprise Edition license.

The MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime. It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications. (Source)

The steps for installing the Enterprise Edition is almost the same as the Community, but you have to download the Docker image from the Oracle Support portal (My Oracle Support). And, you will need a license to access support. If you are already a customer, login to the support web site, and go to the “Patches and Updates” tab, click on “Patch Search” and then on under “Product or Family (Advanced)”, search for the MySQL version you want and enter the description of “Docker”, then click the “Search” button.

MySQL only has the Linux version of the Enterprise Edition, but I can still install and run it on my Mac. The download file contains a tar file and a README file. For this example, the tar file is named mysql-enterprise-server-8.0.16.tar. I placed this file in my home directory, and from a terminal window, I will need to load the file into the repository using the Docker load command:

$ docker load -i mysql-enterprise-server-8.0.16.tar
d6b2dcf96e3d: Loading layer [==================================================>]  220.6MB/220.6MB
b84b6c2a237e: Loading layer [==================================================>]  8.704kB/8.704kB
141e4cf4cec5: Loading layer [==================================================>]  2.048kB/2.048kB
Loaded image: mysql/enterprise-server:8.0

I can now see the Enterprise Edition image along with the Community Edition image:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

To install the Enterprise Edition, I only need to change a few of the variables from before. I will also want to use port 3308, since 3306 and 3307 are in use. (Remember – the first port number is the “external” server port number and the second is the port number inside the container)

$ docker run -p 3308:3306 -d --name mysqlEE -e MYSQL_ROOT_PASSWORD=password mysql/enterprise-server:8.0

I can now see the Enterprise Edition container:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

I can connect to docker, and open MySQL – using the container name of mysqlEE:

$ docker exec -it mysqlEE bash

bash-4.2# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

I will want to create a new user like before, and then I can create a Workbench connection as well, using port 3308.

That’s it. I now have two instances of MySQL running in two separate containers on my server.

---

Deleting images and containers

Here are the commands to delete any images or containers. To remove one or more specific images, use the Docker images command to see what images are available:

$ docker images -a
REPOSITORY                TAG                 IMAGE ID            CREATED             SIZE
mysql/mysql-server        latest              39649194a7e7        2 weeks ago         289MB
mysql/enterprise-server   8.0                 d4410562024a        2 weeks ago         337MB

And you can delete the image by deleting the IMAGE ID, by using the Docker rmi command:

$ docker rmi 39649194a7e7
Untagged: mysql/mysql-server:latest
Untagged: mysql/mysql-server@sha256:8dd16a45d0e3e789f2006b608abb1bb69f1a8632a338eef89aec8d6fccda7793
Deleted: sha256:39649194a7e780713ee5681d3bc5ff9e1fddaca744113d4a64ed61f67b7de601
Deleted: sha256:46837581982573a52d3af65de8ac243749c3f8bdf16043541e1a3cfcac721f6b
Deleted: sha256:e311a637abb5186c3bafe967fbb4d10c16258b4b878258ed0ceaff9a07969930
Deleted: sha256:348e9a791d8deb3d6f7ea979c768db0086dbd5172fdbe065649aebfebe509c46
Deleted: sha256:c4a7cf6a6169fb6af5316b4917b6f3417d419b5b5c1e5befd74746996088fc57

To remove a container, use the Docker container command to get a list of containers:

$ docker container ls -a
CONTAINER ID        IMAGE                         COMMAND                  CREATED             STATUS                            PORTS                               NAMES
5b4df641d044        mysql/enterprise-server:8.0   "/entrypoint.sh mysq…"   6 seconds ago       Up 4 seconds (health: starting)   33060/tcp, 0.0.0.0:3308->3306/tcp   mysqlEE
0b55334fedcb        mysql/mysql-server            "/entrypoint.sh mysq…"   2 hours ago         Up 2 hours (healthy)              33060/tcp, 0.0.0.0:3307->3306/tcp   mysql

And you can delete the container by deleting the CONTAINER ID via the the Docker container command:

$ docker container rm 5b4df641d044
5b4df641d044

---

For more information on installing MySQL with Docker, see Deploying MySQL on Linux with Docker.

 

---

 

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Using MySQL Shell to create a three-node MySQL InnoDB Cluster

MySQL InnoDB Cluster was introduced in MySQL version 5.7 and consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. In this post, I am going to explain how to setup a three-node cluster using the MySQL Shell.

Note: Visit this page to learn more about MySQL InnoDB Cluster.

Other blogs on Group Replication and InnoDB Cluster:

MySQL 8.0 Group Replication – Three-server installation

Adding a replicated MySQL database instance using a Group Replication server as the source

Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)

MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

To begin, I am going to install three instances of the MySQL database and MySQL Shell (both version 8.0.15) on three separate virtual machines with the IP addresses of 192.168.1.161, 192.168.1.162 and 192.168.1.163. I will do most of the work on 192.168.1.161 via the MySQL Shell.

I am not going to change the configuration file (my.cnf or my.ini) except to add a unique value for server_id for each of the three servers. I will use the last three digits of the IP addresses for the server_id. So, for 192.168.1.161, my configuration file only has this in it:

# MySQL Configuration File
[mysqld]
server_id=161   # each server needs to have a unique server number

Let’s get started

From a terminal window on 192.168.1.161, I will open the MySQL Shell using the command: mysqlsh. MySQL Shell has three modes – SQL, Javascript and Python. For this post, I will be using the SQL and JavaScript modes. Once you are in the MySQL Shell console, here are the commands to switch between the modes:

SQL mode - \sql
JavaScript Mode - \js
Python mode - \py

Each mode is highlighted with a different color. Here is a screenshot to show you what each mode looks like:

When you first open MySQL Shell, you aren’t connected to a database. I will start Shell (via mysqlsh), connect to the local instance, and then switch to SQL mode.

# mysqlsh MySQL Shell 8.0.15-commercial Copyright (c) 2016, 2019, Oracle and/or its affiliates. All rights reserved. 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.  MySQL  JS    \connect root@localhost:3306 Creating a session to 'root@localhost:3306' Please provide the password for 'root@localhost:3306': Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): N Fetching schema names for autocompletion... Press ^C to stop. Your MySQL connection id is 12 Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial No default schema selected; type \use to set one.  MySQL  JS    \sql Switching to SQL mode... Commands end with ;

I am starting with three fresh installations of MySQL. I shouldn’t have any databases or tables other than the default ones. And, I want to double-check to make sure there haven’t been any transactions already executed. I can do this with the SHOW MASTER STATUS\G command:

MySQL  SQL    show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 151 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.0003 sec)

Note: I installed these servers a few times, and sometimes when I ran this command, it would show binlog.000002 instead of binlog.000001. This doesn’t matter – as long as the Executed_GTID_Set is blank.

From an OS command prompt, you can take a look at the binary log file binlog.000001, which is located inside your MySQL data directory. And the binlog.index file contains a list of the active binary logs, which in this case, is only binlog.000001.

MacVM161:data root# ls -l bin* -rw-r—– 1 _mysql _mysql 151 Apr 3 20:48 binlog.000001 -rw-r—– 1 _mysql _mysql 16 Apr 3 20:48 binlog.index MacVM161:data root# cat binlog.index ./binlog.000001

Since this is a new installation, I should only see the four default MySQL databases, and the four default MySQL users:

MySQL  SQL    show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.0013 sec)  MySQL  SQL    select user, host from mysql.user; +------------------+-----------+ | user | host | +------------------+-----------+ | mysql.infoschema | localhost | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 4 rows in set (0.0004 sec)

When you use MySQL Shell to build your InnoDB Cluster, you can use pre-built administration commands to configure and manage the cluster. Before adding an instance to the cluster, I can check to see if the instance is suitable for InnoDB Cluster by running the dba.checkInstanceConfiguration command. To use the commands, I will switch to JavaScript mode:

MySQL  SQL    \js Switching to JavaScript mode…  MySQL  JS    dba.checkInstanceConfiguration('root@localhost:3306') Please provide the password for 'root@localhost:3306': Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Y Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as MacVM163.local Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... Some configuration options need to be fixed: +--------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +--------------------------+---------------+----------------+--------------------------------------------------+ | binlog_checksum | CRC32 | NONE | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | +--------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. Please use the dba.configureInstance() command to repair these issues. { "config_errors": [ { "action": "server_update", "current": "CRC32", "option": "binlog_checksum", "required": "NONE" }, { "action": "restart", "current": "OFF", "option": "enforce_gtid_consistency", "required": "ON" }, { "action": "restart", "current": "OFF", "option": "gtid_mode", "required": "ON" }, ], "status": "error" }

From the above output (under the header ““Some configuration options need to be fixed:”“) – and normally I would need to add these variables and their correct values to the MySQL configuration file and reboot MySQL. But with MySQL Shell, I can change these variables dynamically on the server and reboot the server using the dba.configureInstance() command.

MySQL  JS    dba.configureInstance('root@localhost:3306') Please provide the password for 'root@localhost:3306': Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): Configuring local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as MacVM161.local Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. WARNING: User 'root' can only connect from localhost. If you need to manage this instance while connected from other hosts, new account(s) with the proper source address specification must be created. 1) Create remotely usable account for 'root' with same grants and password 2) Create a new admin account for InnoDB cluster with minimal required grants 3) Ignore and continue 4) Cancel Please select an option [1]: 2 Please provide an account name (e.g: icroot@%) to have it created with the necessary privileges or leave empty and press Enter to cancel. Account Name: cluster_adm Some configuration options need to be fixed: +--------------------------+---------------+----------------+--------------------------------------------------+ | Variable | Current Value | Required Value | Note | +--------------------------+---------------+----------------+--------------------------------------------------+ | binlog_checksum | CRC32 | NONE | Update the server variable | | enforce_gtid_consistency | OFF | ON | Update read-only variable and restart the server | | gtid_mode | OFF | ON | Update read-only variable and restart the server | +--------------------------+---------------+----------------+--------------------------------------------------+ Some variables need to be changed, but cannot be done dynamically on the server. Do you want to perform the required configuration changes? [y/n]: y Do you want to restart the instance after configuring it? [y/n]: y Cluster admin user 'cluster_adm'@'%' created. Configuring instance... The instance 'localhost:3306' was configured for InnoDB cluster usage. Restarting MySQL...

I am going to need to start Shell on the other two servers, connect to the database, and execute the dba.configureInstance command – creating the cluster_adm user as well. All three instances should also be rebooted (if they weren’t rebooted automatically).

After the reboot of the MySQL instance (mysqld), I still have MySQL Shell open, but I will need to reconnect again to the database. Notice – if I have lost my connection and I attempt to do anything – like switch to SQL mode, Shell will re-connect to the database for me:

MySQL  JS    \sql Switching to SQL mode... Commands end with ; Error during auto-completion cache update: ClassicSession.runSql: Lost connection to MySQL server during query The global session got disconnected.. Attempting to reconnect to 'mysql://root@localhost:3306'.. The global session was successfully reconnected.

I can then run dba.checkInstanceConfiguration again on all three servers to see if the instance is ready for InnoDB Cluster. (Remember to switch to JavaScript mode (\js) if you aren’t already in JavaScript mode)

MySQL  SQL    \js Switching to JavaScript mode…  MySQL  JS    dba.checkInstanceConfiguration('root@localhost:3306') Please provide the password for 'root@localhost:3306': Save password for 'root@localhost:3306'? [Y]es/[N]o/Ne[v]er (default No): N Validating local MySQL instance listening at port 3306 for use in an InnoDB cluster... This instance reports its own address as MacVM161.local Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance 'localhost:3306' is valid for InnoDB cluster usage. { "status": "ok" }

The above status line of “status”: “ok”, confirms this instance is ready for InnoDB Cluster.

As I am doing these installs, I like to check the master status as I go along, so I will do that again. First I need to switch to SQL mode.

MySQL  JS    \sql Switching to SQL mode... Commands end with ;  MySQL  SQL    show master status\G *************************** 1. row *************************** File: binlog.000002 Position: 151 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.0002 sec)

The master status hasn’t changed, so nothing was written to the binary log.

One immediate problem from not using the default root user is you aren’t given the option to assign a password for the cluster_adm user. I can check this by querying the mysql.user table:

MySQL  SQL    select user, host, authentication_string from mysql.user where user = 'cluster_adm'; *************************** 1. row *************************** +-------------+------+-----------------------+ | user | host | authentication_string | +-------------+------+-----------------------+ | cluster_adm | % | | +-------------+------+-----------------------+

Since there isn’t a password for cluster_adm (bug 94977), I will go ahead and set it. But – I don’t want to write this to the binary log, as then it would get replicated to the other servers once I start the cluster. I will need to modify this user and set a password on the other two servers as well. I can suppress writing to the binary log with SET SQL_LOG_BIN=0; and then turn it back on with SET SQL_LOG_BIN=1;. (Don’t forget to substitute the value of new_password for your actual password)

SET SQL_LOG_BIN=0;
ALTER USER 'cluster_adm'@'%' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

MySQL  SQL    SET SQL_LOG_BIN=0; Query OK, 0 rows affected (0.0001 sec)  MySQL  SQL    ALTER USER 'cluster_adm'@'%' IDENTIFIED BY 'new_password'; Query OK, 0 rows affected (0.0001 sec)  MySQL  SQL    FLUSH PRIVILEGES; Query OK, 0 rows affected (0.0001 sec)  MySQL  SQL    SET SQL_LOG_BIN=1; Query OK, 0 rows affected (0.0001 sec)

I can check again to see if the cluster_adm user now has a password:

MySQL  SQL    select user, host, authentication_string from mysql.user where user = 'cluster_adm'; +-------------+------+------------------------------------------------------------------------+ | user | host | authentication_string | +-------------+------+------------------------------------------------------------------------+ | cluster_adm | % | $A$005$',q+B<%=JJ|Mz.WH!XXX/iQ4rvG/3DzX/UharambelivesYp1oODqtNZk25 | +-------------+------+------------------------------------------------------------------------+

NOTICE: The cluster_adm user must have the same password on all servers in the cluster!

InnoDB Cluster uses Global Transaction Identifiers (GTIDs). “A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (the master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication topology.” Source

Each server in the three-node cluster will have its own GTIDs, which is composed of a Universal Unique Identifier (UUID), a colon (:) and an incremental number. To see the UUID for each server, this value is stored in the MySQL data directory in the auto.cnf file:

 # cat auto.cnf
[auto]
server-uuid=ae1a6186-5672-11e9-99b4-80e6004d84ae

Therefore, each of these three servers will have their own UUID being used in the GTIDs. The cluster itself will have a separate UUID being used in its own GTID, and this UUID is generated when the cluster is created. I have the following UUID’s for the three servers:

IP Address	Server UUID
192.168.1.161	ae1a6186-5672-11e9-99b4-80e6004d84ae
192.168.1.162	cd287ef0-5672-11e9-be9a-b79ce5a797fd
192.168.1.163	d85f6086-5672-11e9-ad64-c08d80ddd285
InnoDB Cluster	– to be determined –

Now I am ready to create the cluster. From the first server (192.168.1.161), I will switch to JavaScript mode, and then I will want to re-connect as the cluster_adm user.

MySQL  JS    \js Switching to JavaScript mode...

MySQL  JS    \connect cluster_adm@192.168.1.161:3306 Creating a session to 'cluster_adm@192.168.1.161:3306' Please provide the password for 'cluster_adm@192.168.1.161:3306': Save password for 'cluster_adm@192.168.1.161:3306'? [Y]es/[N]o/Ne[v]er (default No): N Fetching schema names for autocompletion... Press ^C to stop. Closing old connection... Your MySQL connection id is 19 Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial No default schema selected; type \use to set one.

I can create the cluster using the dba.createCluster command:

MySQL  JS    dba.createCluster('myCluster'); A new InnoDB cluster will be created on instance 'cluster_adm@192.168.1.161:3306'. Validating instance at 192.168.1.161:3306... This instance reports its own address as MacVM161.local Instance configuration is suitable. Creating InnoDB cluster 'myCluster' on 'cluster_adm@192.168.1.161:3306'... Adding Seed Instance... Cluster successfully created. Use Cluster.addInstance() to add MySQL instances. At least 3 instances are needed for the cluster to be able to withstand up to one server failure.

The InnoDB Cluster was successfully created. Now when I do a SHOW MASTER STATUS\G, I will see some values under the Executed_Gtid_Set section: (I will need to switch back to the SQL mode)

MySQL  JS    \sql Switching to SQL mode... Commands end with ;

MySQL  SQL    show master status\G *************************** 1. row *************************** File: binlog.000001 Position: 12406 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12, c446e75c-5674-11e9-bf50-753fdb914192:1-2 1 row in set (0.0003 sec)

Under the Executed_Gtid_Set section, I have two sets of GTID’s. One is for the 192.168.1.161 server (ae1a6186-5672-11e9-99b4-80e6004d84ae), and the other is for the cluster (c446e75c-5674-11e9-bf50-753fdb914192).

Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12,
c446e75c-5674-11e9-bf50-753fdb914192:1-2

The Executed_Gtid_Set shows which transactions have been applied to this server (192.168.1.161). There have been 12 transactions for the server (ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12) and two transactions for the cluster (c446e75c-5674-11e9-bf50-753fdb914192:1-2). The GTID’s for the cluster should be the same on each server, as we add them to the server. Also, once you add servers to the cluster, the read-only servers will be changed to SUPER_READ_ONLY to prevent write-transactions from being applied to the read-only servers in a single-primary mode cluster. If you want to view the transactions which were executed, you can use the mysqlbinlog utility.

I can reference my earlier table of the UUID’s for all of the servers, and I can now add the UUID for the cluster (in red:

IP Address	Server UUID
192.168.1.161	ae1a6186-5672-11e9-99b4-80e6004d84ae
192.168.1.162	cd287ef0-5672-11e9-be9a-b79ce5a797fd
192.168.1.163	d85f6086-5672-11e9-ad64-c08d80ddd285
InnoDB Cluster	c446e75c-5674-11e9-bf50-753fdb914192

I can check the status of the cluster. (after switching back to JavaScript mode)

MySQL  JS    \js Switching to JavaScript mode...

MySQL  JS    var cluster = dba.getCluster()  MySQL  JS    cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "192.168.1.161:3306", "ssl": "REQUIRED", "status": "OK_NO_TOLERANCE", "statusText": "Cluster is NOT tolerant to any failures.", "topology": { "192.168.1.161:3306": { "address": "192.168.1.161:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "MacVM161.local:3306" }

Under the topology section above, we can see server 192.168.1.161 is in the cluster and has the status of ONLINE.

I am now ready to add 192.168.1.162 and 192.168.1.163 to the cluster. But first I need to make sure each server is ready to be added to the cluster using the dba.checkInstanceConfiguration command. I can run these commands from any one of the servers, but I am going to run this from the first server – 192.168.1.161. Prior to this, I went ahead and made the same changes to the MySQL configuration file (my.cnf or my.ini) as I did on 192.168.1.161.

MySQL  JS    dba.checkInstanceConfiguration('cluster_adm@192.168.1.162:3306') Please provide the password for 'cluster_adm@192.168.1.162:3306': Save password for 'cluster_adm@192.168.1.162:3306'? [Y]es/[N]o/Ne[v]er (default No): Validating MySQL instance at 192.168.1.162:3306 for use in an InnoDB cluster... This instance reports its own address as MacVM162.local Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance '192.168.1.162:3306' is valid for InnoDB cluster usage. { "status": "ok" }

MySQL  JS    dba.checkInstanceConfiguration('cluster_adm@192.168.1.163:3306') Please provide the password for 'cluster_adm@192.168.1.163:3306': Save password for 'cluster_adm@192.168.1.163:3306'? [Y]es/[N]o/Ne[v]er (default No): Validating MySQL instance at 192.168.1.163:3306 for use in an InnoDB cluster... This instance reports its own address as MacVM162.local Clients and other cluster members will communicate with it through this address by default. If this is not correct, the report_host MySQL system variable should be changed. Checking whether existing tables comply with Group Replication requirements... No incompatible tables detected Checking instance configuration... Instance configuration is compatible with InnoDB cluster The instance '192.168.1.163:3306' is valid for InnoDB cluster usage. { "status": "ok" }

Both servers are ready to go, as indicated by the “status”: “ok”.

Now I can add the other two servers. (I have already set the variable “cluster” earlier – and you only need to set “var cluster = dba.getCluster()” once per session. If you get an error, you might have to set it again)

MySQL  JS    cluster.addInstance('cluster_adm@192.168.1.162:3306') A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Adding instance to the cluster ... Please provide the password for 'cluster_adm@192.168.1.162:3306': Save password for 'cluster_adm@192.168.1.162:3306'? [Y]es/[N]o/Ne[v]er (default No): Validating instance at 192.168.1.162:3306... This instance reports its own address as MacVM162.local Instance configuration is suitable. The instance 'cluster_adm@192.168.1.162:3306' was successfully added to the cluster.

MySQL  JS    cluster.addInstance('cluster_adm@192.168.1.163:3306') A new instance will be added to the InnoDB cluster. Depending on the amount of data on the cluster this might take from a few seconds to several hours. Adding instance to the cluster ... Please provide the password for 'cluster_adm@192.168.1.163:3306': Save password for 'cluster_adm@192.168.1.163:3306'? [Y]es/[N]o/Ne[v]er (default No): Validating instance at 192.168.1.163:3306... This instance reports its own address as MacVM162.local Instance configuration is suitable. The instance 'cluster_adm@192.168.1.163:3306' was successfully added to the cluster.

The SHOW MASTER STATUS\G should now be the same on all three servers.

MySQL  SQL    SHOW MASTER STATUS\G *************************** 1. row *************************** File: binlog.000005 Position: 17098 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: ae1a6186-5672-11e9-99b4-80e6004d84ae:1-12, c446e75c-5674-11e9-bf50-753fdb914192:1-14 1 row in set (0.0002 sec)

I can also check the status of the cluster, to see if all three nodes are ONLINE.

MySQL  JS    cluster.status() { "clusterName": "myCluster", "defaultReplicaSet": { "name": "default", "primary": "192.168.1.161:3306", "ssl": "REQUIRED", "status": "OK", "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", "topology": { "192.168.1.161:3306": { "address": "192.168.1.161:3306", "mode": "R/W", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "192.168.1.162:3306": { "address": "192.168.1.162:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" }, "192.168.1.163:3306": { "address": "192.168.1.163:3306", "mode": "R/O", "readReplicas": {}, "role": "HA", "status": "ONLINE" } }, "topologyMode": "Single-Primary" }, "groupInformationSourceMember": "MacVM161.local:3306" }

All three nodes have the status of ONLINE, so the cluster is up and ready to use.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

Replicating data between two MySQL Group Replication sets using “regular” asynchronous replication with Global Transaction Identifiers (GTID’s)

MySQL introduced Group Replication (GR) in version 5.7, and GR is part of the InnoDB Cluster high-availability solution. InnoDB Cluster consists of Group Replication, MySQL Shell and MySQL Router.

I am not going to explain InnoDB Cluster or Group Replication in this post. So, if you aren’t familiar with either one, I have some previous posts in which I have explained how to work with both. See:

MySQL 8.0 Group Replication – Three-server installation

MySQL 8.0 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

Adding a replicated MySQL database instance using a Group Replication server as the source

---

A customer wanted to know how to replicate data between two separate three-node Group Replication groups. They have two data centers in different states, and they want to use each GR group for a different application. And, they wanted to replicate the data between the two (for various reasons).

For Group Replication (GR), you must have a minimum of three nodes in the group, and you can have a maximum of nine nodes. The GR group can be either in single-primary or multiple-primary mode. A five-node Group Replication Group looks like this:

For this (fictional) example, I will be using two three-node Group Replication groups. One group is in Atlanta, Georgia, and the other is in Tampa, Florida. Besides the standard group replication requirements, there are a couple of items you need to take into consideration if you decide to try this. First, this will probably not work very well if you have a very write-heavy application. Group Replication does support multi-threading, but the replicated databases may not always be up-to-date with the source database. Also, please note that you need will need a very good network connection between the two groups. And, if one GR group completely fails, there is a good chance some of the data might not have had time to replicate over to the other group. Just remember – your replication performancee may vary. For this post, I am using six virtual machines on a single server.

The topology so far…

To replicate the data between two groups, you only need to use use “regular” MySQL asynchronous replication with Global Transaction Identifiers (GTIDs)

Note: To learn more about replicating with GTIDs, I wrote two posts explaining the “how to” – see part one and part two). GTID’s are required when using Group Replication.

For the Atlanta group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.151, 192.168.1.152 and 192.168.1.153. For the Tampa group, I have three instances of MySQL (version 8.0.15) with IP addresses 192.168.1.161, 192.168.1.162 and 192.168.1.163. The groups look like this:

With our two three-node groups, I will use the primary-write node in the first group to be the source (master) database for the primary-write node (replica/slave) in the second group – and vice-versa.

In the Atlanta group, I will use the primary-write node with the IP address of 192.168.1.151 (MEMBER_HOST MacVM151.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.161).

In the Tampa group, I will use the primary-write node with the IP address of 192.168.1.161 (MEMBER_HOST MacVM161.local) to be the source (master) database for the primary-write in the Tampa group (which will be the slave with IP address 192.168.1.151).

The other nodes in both groups are set to SUPER READ-ONLY, so they can’t be used as a replica (slave) database source, as the replica (slave) needs to be able to perform writes. The topology will looks something like this – with the arrows showing the way the data is replicated (or “flows”):

If that is too confusing, here is a simplified layout: (ATL = Atlanta, TPA = Tampa)

The members of each group

In the Atlanta Group, I have the following members in the Group Replication group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

In this group, I want the first node in the list (MEMBER_HOST MacVM151.local) to be the primary, but for some reason, the second node (MacVM152.local) is the primary. To change the PRIMARY to be the first server in the list, I can issue this command:

SELECT group_replication_set_as_primary(‘member_uuid’); (where the member_uuid is equal to the MEMBER_ID of the first node (‘c727957e-4cb6-11e9-abd5-f80a484a9c32’) from the above output.)

mysql> SELECT group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('c727957e-4cb6-11e9-abd5-f80a484a9c32') |
+--------------------------------------------------------------------------+
| Primary server switched to: c727957e-4cb6-11e9-abd5-f80a484a9c32         |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)

I can verify this change by checking the members again (notice the MEMBER_ROLE column):

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

The Tampa Group is ready to go – as 192.168.1.161 is the PRIMARY server:

Tampa Group:

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | fdc89b12-50d7-11e9-bfa8-012cdcc95c70 | MacVM161.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 1ef93b16-50d8-11e9-b7da-7e47ebc51826 | MacVM162.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 46012962-50d8-11e9-8dc0-de7edddaaccd | MacVM163.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

(Note: Yes, I could have used the second node with IP of 192.168.1.152, but I wanted to use the first node in each group)

Checking the state of each group

I am starting with a clean install of MySQL on all six instances. You can still do this if you have existing data in your groups, but you will need to get the other group to have a beginning set of data. When you begin replication, it won’t automatically export/backup the data and restore/import it to the other group. You will have to backup and restore the data from each group, so the new replicated server will have a starting point at a particular GTID. (I explain how to do this in this post – search for “What if my Group Replication (GR) group already has data?”).

I can see from the GET MASTER STATUS\G on both groups under the Executed_Gtid_Set that I have executed seven and three transactions on the two GR groups, respectively (these transactions were from creating and modifying the group replication views):

Atlanta Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2217
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

Tampa Group

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 1515
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-3
1 row in set (0.00 sec)

But, if you want to look at the transactions (which are stored in the binary log mysql-bin.000001 on each server) for each group to verify that you didn’t have any transactions which changed data, you can use the mysqlbinlog tool:

# mysqlbinlog mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
....
SET TIMESTAMP=1553731529/*!*/;
COMMIT
....

I checked both binary logs, and all of the transactions had to do with changing the state of the group replication view. All of these transactions were SET TIMESTAMP transactions (“SET TIMESTAMP=1553731529/*!*/;”). I am now ready to start replication.

Let’s get started

As shown in the above topology images, I am going to setup the Atlanta node (MacVM151.local – IP address 192.168.1.151) to be a source (master) for the Tampa node (MacVM161.local – IP address 192.168.1.161). Since I already have a replication user created for Group Replication, I can use the same user for this “regular” asynchronous replication. If you want more details on asynchronous replication using GTID’s – see this post.

Since I already have a replication channel open with the Group Replication, I will need to specify a new channel. I will name the channel “atl_tpa_replication” (for “Atlanta to Tampa replication”), meaning the Atlanta server will be the source (master) and the Tampa server will be the replica (slave). In replication, it is the job of the replica (slave) to contact the source (master) and retrieve all of the transactions (data) that has not been applied to its database. I will open a MySQL prompt on the Tampa instance with the IP address of 192.168.1.161, and issue this CHANGE MASTER TO statement:

On Tampa

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.151',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'atl_tpa_replication';

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.151',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'atl_tpa_replication';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

I can now start the slave for the “atl_tpa_replication” replication channel, and take a look at the “slave status”:

mysql> start slave for channel 'atl_tpa_replication';
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status for channel 'ATL_TPA_REPLICATION'\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.151
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 893
               Relay_Log_File: MacVM161-relay-bin-atl_tpa_replication.000004
                Relay_Log_Pos: 1099
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 893
              Relay_Log_Space: 1919
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
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: 151
                  Master_UUID: c727957e-4cb6-11e9-abd5-f80a484a9c32
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: atl_tpa_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

On the Atlanta group, there were seven transactions which had been applied (GTIDs of 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) – and I can tell this from when I did a “SHOW MASTER STATUS\G” from the Atlanta primary-write server (192.168.1.151):

On Atlanta

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

I can see which GTID’s I have retrieved (Retrieved_Gtid_Set) and executed (Executed_Gtid_Set) from the SHOW SLAVE STATUS\G command, and these seven GTID’s (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from the server are included:

mysql> show slave status for channel 'atl_tpa_replication'\G
...
           Retrieved_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Since I executed the SHOW SLAVE STATUS\G from the Tampa server, I can also see the existing Tampa GTIDs also fall under the Executed_Gtid_Set section – 160f4761-c55c-422f-8684-d086f6a1db0e:1-3.

Now I can turn on replication to go from Tampa to Atlanta, where the Tampa primary-write server is the source (master) for the Atlanta primary-write server replica (slave). After I run the CHANGE MASTER TO command, I will turn on the replica (slave) with the START SLAVE command. I will then check on the status with SHOW SLAVE STATUS\G.

NOTE: I have changed the CHANNEL name to be tpa_atl_replication (Tampa to Atlanta replication)

On Atlanta

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'tpa_atl_replication';

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>  show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 2568
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
1 row in set (0.00 sec)

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'tpa_atl_replication';
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3581
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 1413
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3581
              Relay_Log_Space: 1631
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
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: 161
                  Master_UUID: fdc89b12-50d7-11e9-bfa8-012cdcc95c70
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: tpa_atl_replication
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

From the above, I can now see the three GTIDs from the Tampa server (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) have been replicated over to the Atlanta server, and the seven GTIDs (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) from Atlanta also appear in the Executed_Gtid_Set:

           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7

Finally, since I am running Group Replication, these transactions which were replicated between the primary-write servers will also be replicated to the other two servers in each group. I execute the SHOW SLAVE STATUS\G on a secondary server (IP of 192.168.1.152) and see all of the GTID’s which have been replicated between the primary servers:

On secondary server (in Atlanta) with IP of 192.168.1.152:

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7
...

Replicate new transactions

Since both of these servers were new installs of MySQL, each instance only has these four databases:

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

I will now create a database on each server, with the GR group location name as the name of the database. These databases will then replicate over to the other groups. And, you will see the executed GTIDs on the source (master) increase from seven (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-7) to eight (8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8).

On Atlanta:

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

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 2097
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-3,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

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

When I go to any of the group replication instances in Tampa, I can see the Atlanta database has already been replicated over to the group, and each node has executed this transaction. From node three – 192.168.1.163:

On Tampa

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

And the same happens when I create a database named Tampa – it will get replicated over to the Atlanta Group Replication group:

On Tampa:

mysql> create database Tampa;
Query OK, 1 row affected (0.00 sec)

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

On the Tampa Group Replication group, the GTIDs increased from three (160f4761-c55c-422f-8684-d086f6a1db0e:1-3) to four (160f4761-c55c-422f-8684-d086f6a1db0e:1-4). The other GTIDs – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8 – are from the Atlanta Group Replication group. I can confirm this with a SHOW MASTER\G statement:

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 3957
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
1 row in set (0.00 sec)

Also, I can check to see if the “Tampa” database (which was GTID 160f4761-c55c-422f-8684-d086f6a1db0e:4) has been replicated over to the Atlanta group by running a SHOW SLAVE STATUS\G on any of the Atlanta nodes. I will use the second node (IP address 192.168.1.152):

On Atlanta

mysql> show slave status\G
...
           Retrieved_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:4
            Executed_Gtid_Set: 160f4761-c55c-422f-8684-d086f6a1db0e:1-4,
8e2f4761-c55c-422f-8684-d086f6a1db0e:1-8
...
1 row in set (0.00 sec)

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

I now have two different MySQL Group Replication groups replicating data between each other.

WARNING: This should only be done if you can ensure that neither group will be modifying the other group’s data. Since replication is not instant, you could make a change on one group at the same time another person is changing the same row – and this scenario would break the replication between the two groups. Or, you could try to modify data that was modified on another group, and you will be modifying stale data.

Can I change the primary-write server in this scenario?

Since I have replication channels on both of the primary-write nodes, what happens if I want to change the primary node to another server? For example, 192.168.1.151 is the PRIMARY for the Atlanta Group Replication, but I want 192.168.1.152 to be the PRIMARY.

On Atlanta, here are the members of the group replication, with 192.168.1.151 (MacVM151.local) as the PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

If I try and change the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1) to be the PRIMARY server, I will get an error:

From the current PRIMARY node – which is the first node in the group – 192.168.1.151, I will try and make the second node the PRIMARY:

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
ERROR 13223 (HY000): The function 'group_replication_set_as_primary' failed. There is a slave channel running in the group's current primary member.

In order for me to change the PRIMARY to the second node, I will need to stop the asynchronous replication on the first node (192.168.1.151) by issuing the STOP SLAVE command. This stops the replication between 192.168.1.151 and 192.168.1.161.

I can then change the PRIMARY to be the second node (MEMBER_ID 247898e0-4cb7-11e9-97a9-12f28adcadd1 and IP address of 192.168.1.152). Once the second node is the new PRIMARY, then I can start replication on the second node by running the CHANGE MASTER TO command on 192.168.1.152.

On 192.168.1.151 (Atlanta Group): (Note: You can change the PRIMARY from any node in the Group Replication group)

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1');
+--------------------------------------------------------------------------+
| group_replication_set_as_primary('247898e0-4cb7-11e9-97a9-12f28adcadd1') |
+--------------------------------------------------------------------------+
| Primary server switched to: 247898e0-4cb7-11e9-97a9-12f28adcadd1         |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)

I can see the second node is the new PRIMARY: (see the MEMBER_ROLE column)

mysql> SELECT * FROM performance_schema.replication_group_members order by member_host;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | c727957e-4cb6-11e9-abd5-f80a484a9c32 | MacVM151.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
| group_replication_applier | 247898e0-4cb7-11e9-97a9-12f28adcadd1 | MacVM152.local |        3306 | ONLINE       | PRIMARY     | 8.0.15         |
| group_replication_applier | 49e388f6-4cb7-11e9-8ccf-a355c1c8a6e8 | MacVM153.local |        3306 | ONLINE       | SECONDARY   | 8.0.15         |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

Now I can start replication on 192.168.1.152, with 192.168.1.161 as the source (master) by using the CHANGE MASTER TO command.

CHANGE MASTER TO 
MASTER_HOST = '192.168.1.161',
MASTER_PORT = 3306,
MASTER_USER = 'rpl_user',
MASTER_PASSWORD = 'R3plic4tion!',
MASTER_AUTO_POSITION = 1
FOR CHANNEL 'TPA_ATL_REPLICATION';

mysql> CHANGE MASTER TO 
    -> MASTER_HOST = '192.168.1.161',
    -> MASTER_PORT = 3306,
    -> MASTER_USER = 'rpl_user',
    -> MASTER_PASSWORD = 'R3plic4tion!',
    -> MASTER_AUTO_POSITION = 1
    -> FOR CHANNEL 'TPA_ATL_REPLICATION';
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

And, the SHOW SLAVE STATUS confirms the SQL and IO threads are running:

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.161
                  Master_User: rpl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3957
               Relay_Log_File: ic1-relay_log-tpa_atl_replication.000002
                Relay_Log_Pos: 400
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
...

Note: With Group Replication, when the PRIMARY goes down, the group will automatically elect a new PRIMARY server, and the group will keep on processing transactions. If you are doing “regular” asynchronous replication from the PRIMARY and it goes down, there isn’t a way to automatically change the source (master), and replication will be broken. You will need to change the replica (slave) to another server in the Group Replication group. And, when the new PRIMARY comes back online, you will need to issue a STOP SLAVE command if the replication is still active on that server. You don’t want two replicas (slaves) attached at the same time.

 

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.

MySQL 8.0 Group Replication – Three-server installation

MySQL InnoDB Cluster was introduced in MySQL version 5.7. MySQL InnoDB Cluster consists of three parts – Group Replication, MySQL Shell and MySQL Router. MySQL InnoDB Cluster provides a complete high availability solution for MySQL. I am not going to go into the details of InnoDB Cluster and how it works, as there are enough manual pages and blogs to cover these topics.

MySQL InnoDB Cluster manual
Blog sites: mysqlhighavailability.com and mysqlserverteam.com.

Instead, I will be showing you how to install Group Replication on three new installations of mysql 8.0 manually, without using the MySQL Shell.

These instructions should enable you to setup Group Replication in less than an hour. I am doing this on a Mac running 10.13, but most of these commands can easily be translated over to Linux or Windows. I will try to supply the correct commands for all three operating systems.

I will be installing Group Replication on three new installations of MySQL (without any data) with the IP addresses (host names) of 192.168.1.151 (ic-1), 192.168.1.152 (ic-2) and 192.168.1.153 (ic-3). It is important that you don’t run any other commands on the server, and that you start with a fresh install of MySQL. If you already have a server with data, you will need to export the data and import it into the other servers before you go any further here. Starting Group Replication with a server with data requires a different set of commands, and this blog might not work in that situation.

Group Replication may be setup as either a single-primary (one server to handle the writes and two servers for reads), or multi-primary (read/write to any of the servers). This post covers setting up a single-primary configuration. The server with the IP address of 192.168.1.151 will be our single-primary (read/write server) and the other two servers will be read-only.

Let’s begin.

Edit your /etc/hosts file, and add the IP addresses and host names for the three servers.

192.168.1.151 ic-1
192.168.1.152 ic-2
192.168.1.153 ic-3

Flush the directory service cache by running this as root:

Mac - dscacheutil -flushcache
Linux - /etc/rc.d/init.d/nscd restart
Windows - ipconfig /flushdns

You will need to add some variables to your MySQL options file, which is located in these directories: (see Using Option Files)

Mac and Linux - /etc/my.cnf
Windows - C:\ProgramData\MySQL\MySQL Server X (where X is the version number of MySQL)

NOTE: On Windows, the my.ini file may be hidden.

The following variables need to be in the options file under the [mysqld] section: (you may remove the # comment lines if you want, but read each line before deleting it)

You will need to change the server_id value for each server so each has a unique ID. I simply used 1, 2 and 3 for the three servers.

# All members in the group requires a unique server_id greater than zero
server_id=1

# this is the default port for MySQL - you can change it if you want, but it should be the same on all servers
port=3306

# you may specify a name for the binary log, or leave it blank to use the default name
# however, binary logging is required
log_bin=mysql-bin  

# these settings are required
log_slave_updates=ON
binlog_checksum=NONE
enforce_gtid_consistency=ON
gtid_mode=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
transaction_write_set_extraction=XXHASH64

# Group Replication specific options

# this is the name of the plugin
# load the plugin - in Windows, the plugin will be named group_replication.dll

plugin_load_add ="group_replication.so"

# this is required
group_replication = FORCE_PLUS_PERMANENT

# you will turn this on and back off during Group Replication setup
group_replication_bootstrap_group = OFF

# the group_replication_group_name is the UUID for the entire group
# Each server has their own UUID in the file auto.cnf located in the MySQL data directory
# You can generate your own group_replication_group_name on Linux with `uuidgen -t`, 
# on a Mac use "uuidgen" 
# for Windows - The Windows SDK comes with a tool called uuidgen
# all members use this value as group_replication_group_name

# PLEASE NOTE: this group_replication_group_name must be unique for each Group Replication (GR) group
# so each server within a group will have the same value
# Example: if you have two GR groups with three nodes in each group, then three nodes in one group
# will have the same group_replication_group_name, and the other three nodes in the different
# group will have a different group_replication_group_name
group_replication_group_name = 8E2F4761-C55C-422F-8684-D086F6A1DB0E

In the config file make sure group_replication_start_on_boot is set to OFF or add a # (comment) to the line with the value of ON as shown below. You will want to uncomment this line after you setup and start Group Replication, so if the server is rebooted, Group Replication will begin automatically.

# uncomment this line AFTER you have started Group Replication
# so that Group Replication will start after a reboot

#group_replication_start_on_boot = ON

You will need to change this line to match each of the servers, and while you can change the group_replication_local_address (or port number – the recommended default value is 33061), it doesn’t matter what port you use as long as that port isn’t being used by another application.

# change this to be the local address for each server
# the port number can be anything except ports already in use
# and do not use 3306

group_replication_local_address = '192.168.1.151:33061'

This line must contain all of the servers that will be in your group. The group_replication_group_seeds is a list of group members used to establish the connection from the new member to the group.

# add all of the members of the group here, along with the same port numbers

group_replication_group_seeds = '192.168.1.151:33061,192.168.1.152:33061,192.168.1.153:33061'

That is all you need for the configuration file. After you have made the changes, reboot the MySQL instance.

---

NOTE: If you installed MySQL as the root user, be sure that the OS user “mysql” owns all of the mysql directories. You will need to change the directory name (in this example it is /usr/local) to be the directory where you installed the MySQL Server.

# be sure that the mysql user own the mysql directory
# if you install MySQL via root on a Mac or Linux, there is a good chance that root owns the directory

$ cd /usr/local
$ chown -R mysql mysql*

---

Now we are ready to install Group Replication. Be sure to restart the mysqld processes to make the /etc/my.cnf changes permanent.

Let’s start with Server #1, which will be our read-write primary server.

On Server #1 (IP 192.168.1.151)

Since we included the variable group_replication_group_seeds in the options file, each instance has already been added to the group.

The table performance_schema.replication_group_members shows network and status information for replication group members. The network addresses shown are the addresses used to connect clients to the group, and should not be confused with the member’s internal group communication address specified by group_replication_local_address. (source: https://dev.mysql.com/doc/refman/8.0/en/replication-group-members-table.html)

You can run this command from a mysql prompt on each of the servers to see if they have joined the group successfully:

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

Or, you can run the command with the \G at the end instead of the semi-colon (;)

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: OFFLINE
   MEMBER_ROLE: 
MEMBER_VERSION: 
1 row in set (0.00 sec)

The MEMBER_ID 60889f20-48ed-11e8-b6e2-0998e2a48fe0 is the UUID for this particular MySQL instance. The UUID is located in the auto.cnf file in the MySQL data directory.

# cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=60889f20-48ed-11e8-b6e2-0998e2a48fe0

You can take a look at the MASTER STATUS of the primary server, and it should be relatively blank.

# command to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

The active binary log for the server is “mysql-bin.000001“. You can take a look at the events in the log and see nothing has happened on the server.

# command to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001'\G

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001'\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 151
End_log_pos: 124
       Info: Server ver: 8.0.11, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 124
 Event_type: Previous_gtids
  Server_id: 151
End_log_pos: 151
       Info: 
2 rows in set (0.00 sec)

---

Now go to Server #2 (IP 192.168.1.152)

Confirm that Server #2 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

OPTIONAL: If you want, you can run the same informational commands for Server #2 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

---

Now go to Server #3 (IP 192.168.1.153)

Confirm that Server #3 is part of the group.

# command to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST    | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local |        3306 | OFFLINE      |             |                |
+---------------------------+--------------------------------------+----------------+-------------+--------------+-------------+----------------+
1 row in set (0.01 sec)

OPTIONAL: If you want, you can run the same informational commands on Server #3 as you did on Server #1. (SHOW MASTER STATUS, SHOW BINLOG EVENTS, cat auto.cnf)

---

Make sure that the “group_replication” plugin is active on all three servers. You value of PLUGIN_STATUS should be ACTIVE. Run this command on all three servers.

# command to run from MySQL prompt

select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G

mysql> select * from information_schema.plugins where PLUGIN_NAME = 'group_replication'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.1
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.2
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.9
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.1.0)
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: FORCE_PLUS_PERMANENT
1 row in set (0.00 sec)

You should see the same output on the other two servers (IP 192.168.1.152 and 192.168.1.152) as on Server #1.

---

Next you will want to create the replication users. Since we will be turning on replication, we don’t want to write this to the binary logs. Execute this command on all of the servers.

# run this on all three servers
# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

NOTE: If you are using SHA-256 for your passwords, you need to specify sha256_password in your CREATE USER statement (see below). See: https://dev.mysql.com/doc/refman/8.0/en/sha256-pluggable-authentication.html

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED WITH sha256_password BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

Once the users have been created, we can start execute our CHANGE MASTER statement and start Group Replication. Execute these two commands on all of the servers.

# run this on all three servers

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
START GROUP_REPLICATION;

We have to create the replication users, but we don’t want to write this to the binary log, as it would get replicated to the other servers and cause an error.

On Server #1 (IP 192.168.1.151)

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.04 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

Since we are only running single-primary mode, we don’t need to do a CHANGE MASTER on Server #1. In replication, the slave is responsible for connecting to the master to get the write statements. Since the secondary nodes won’t be accepting any writes, then the primary won’t need to connect to them. But, since the primary could fail, and be brought back into the group as a read-only slave, we need to go ahead and run the following CHANGE MASTER statement.

The CHANGE MASTER statement will produce two warnings – we can look at the warnings and ignore them.

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now we will need to turn group_replication_bootstrap_group to ON, and then we can start Group Replication (but do this only for on Server #1). We will want to turn group_replication_bootstrap_group to OFF after we have started Group Replication for the first time.

# commands to run from MySQL prompt

SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

mysql> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

Check the status of the MASTER server:

# commands to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 458
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8e2f4761-c55c-422f-8684-d086f6a1db0e:1
1 row in set (0.00 sec)

There has only been one GTID executed on the MASTER – 8e2f4761-c55c-422f-8684-d086f6a1db0e:1. Check the binlog to see what has been written to it.

# commands to run from MySQL prompt

SHOW BINLOG EVENTS in 'mysql-bin.000001';

mysql> SHOW BINLOG EVENTS in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |       151 |         124 | Server ver: 8.0.11, Binlog ver: 4                                 |
| mysql-bin.000001 | 124 | Previous_gtids |       151 |         151 |                                                                   |
| mysql-bin.000001 | 151 | Gtid           |       151 |         229 | SET @@SESSION.GTID_NEXT= '8e2f4761-c55c-422f-8684-d086f6a1db0e:1' |
| mysql-bin.000001 | 229 | Query          |       151 |         291 | BEGIN                                                             |
| mysql-bin.000001 | 291 | View_change    |       151 |         390 | view_id=15247058086370221:1                                       |
| mysql-bin.000001 | 390 | Query          |       151 |         458 | COMMIT                                                            |
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
6 rows in set (0.00 sec)

We still only have one member of the Group Replication, and we can see which server is a member:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
1 row in set (0.00 sec)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
1 row in set (0.00 sec)

You can see one member of the group (Server #1) is online. Group Replication has been started on Server #1.

Now we can add Server #2. We will do the same steps as we did for Server #1, except we don’t need to bootstrap Group Replication, as it has already been started. Oon Server #2 (IP 192.168.1.152):

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

# commands to run from MySQL prompt

SHOW MASTER STATUS\G

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 151
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

Run the CHANGE MASTER statement:

# commands to run from MySQL prompt

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                              |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure.                                                                                                                                                                                                               |
| Note  | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

The CHANGE MASTER statement produced two warnings – we can look at the warnings and ignore them.
# commands to run from MySQL prompt

SHOW WARNINGS\G

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
*************************** 2. row ***************************
  Level: Note
   Code: 1760
Message: Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2 rows in set (0.00 sec)

Start Group Replication on Server #2:

# commands to run from MySQL prompt

START GROUP_REPLICATION;

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.10 sec)

---

NOTE: If you put in the wrong password in your CHANGE MASTER statement, the server will be in the group, but will be in a MEMBER_STATE of RECOVERING and it will never join the group.

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | RECOVERING   | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

---

Check to see that Server #2 has joined the group. You can execute this command on Server #1 or #2. (This was executed from Server #1)

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

Or you can run it with the \G at the end.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Running the same commands from Server #2 gets the same results, as you can query the status of the Group from any member of the group.

This was executed from Server #2

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
2 rows in set (0.00 sec)

And the option with \G at the end:
# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
2 rows in set (0.00 sec)

Group replication now has Server #1 and Server #2. Now we can add Server #3 (IP 192.168.1.153)

We run the same commands as above. Note:I only show the commands to run – I do not show the screen output here.

# commands to run from MySQL prompt

SET SQL_LOG_BIN=0;
CREATE USER rpl_user@'%' IDENTIFIED BY 'R3plic4tion!';
GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
FLUSH PRIVILEGES;
SET SQL_LOG_BIN=1;

CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='R3plic4tion!' FOR CHANNEL 'group_replication_recovery';

START GROUP_REPLICATION;

If you didn’t see any errors, Group Replication is ready to go. As before, you can check the status of the group from any server.

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members\G

mysql> SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: 60889f20-48ed-11e8-b6e2-0998e2a48fe0
   MEMBER_HOST: MacVM151-2.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: PRIMARY
MEMBER_VERSION: 8.0.11
*************************** 2. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: bedc9968-48ee-11e8-9735-0a5899f91373
   MEMBER_HOST: MacVM153.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
*************************** 3. row ***************************
  CHANNEL_NAME: group_replication_applier
     MEMBER_ID: f4112942-48ed-11e8-86c4-f57773daeb0a
   MEMBER_HOST: MacVM152.local
   MEMBER_PORT: 3306
  MEMBER_STATE: ONLINE
   MEMBER_ROLE: SECONDARY
MEMBER_VERSION: 8.0.11
3 rows in set (0.00 sec)

Or you can run:

# commands to run from MySQL prompt

SELECT * FROM performance_schema.replication_group_members;

mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST      | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
| group_replication_applier | 60889f20-48ed-11e8-b6e2-0998e2a48fe0 | MacVM151-2.local |        3306 | ONLINE       | PRIMARY     | 8.0.11         |
| group_replication_applier | bedc9968-48ee-11e8-9735-0a5899f91373 | MacVM153.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
| group_replication_applier | f4112942-48ed-11e8-86c4-f57773daeb0a | MacVM152.local   |        3306 | ONLINE       | SECONDARY   | 8.0.11         |
+---------------------------+--------------------------------------+------------------+-------------+--------------+-------------+----------------+
3 rows in set (0.00 sec)

NOTICE: Be sure to change group_replication_start_on_boot to ON in your my.ini or my.cnf configuration file for all of the servers as shown below.

(in the my.cnf or my.ini file)
group_replication_start_on_boot = ON

If the MEMBER_STATE of three servers is ONLINE, then you are finished and Group Replication has been installed!

---

Creating an InnoDB Cluster

If you have an existing deployment of Group Replication and you want to use it to create a cluster, pass the adoptFromGR option to the dba.createCluster() function. The created InnoDB cluster matches whether the replication group is running as single-primary or multi-primary. See: https://dev.mysql.com/doc/refman/8.0/en/mysql-innodb-cluster-from-group-replication.html

---

	Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
	Tony is the author of Twenty Forty-Four: The League of Patriots  Visit http://2044thebook.com for more information.
	Tony is the editor/illustrator for NASA Graphics Standards Manual Remastered Edition  Visit https://amzn.to/2oPFLI0 for more information.