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.

Using a trigger to parse non-conforming data in MySQL

In another post, I explained how to use the MySQL Query Re-write Plugin to manipulate data that didn’t exactly match SQL standards of MySQL. In this post, I am going to give you another example on how to use a trigger to parse non-conforming data so the data can be written to a MySQL database.

A customer came to me with a problem. They were using third-party software which produced multiple rows of comma-separated data (like a .csv file), but all of the data wasn’t on a single row. Instead, the data consisted of multiple rows of data, separated by commas and with line feeds after each row of data. Here is a shortened example of the data (the original data was 32-lines long):

Header information which may, or may not, contain one or more commas
Item Name,Type,Description,Value
Potato Chips - Sealed Bag,,,
Manifest Number,Number,MANIFEST_NUMBER,8480014
Manufacturer Code,Number,BRAND_CODE,80375993
Information 1,Number,INFO_ONE,15869563
Information 2,Number,INFO_TWO,6569569665
Information 3,Number,INFO_THREE,562
Planned Start Date,String,PROD_START_DATE,9/13/2018
Planned Start Time,String,PROD_START_TIME,081234

For some reason, the output couldn’t be separated, but the software could use an ODBC connection. They needed to be able to insert this data into a MySQL database, but they only needed certain values – they didn’t need a lot of the descriptive information. Of the example above, they only wanted to insert the information in yellow:

“Header information which may, or may not, contain one or more commas.”
Item Name	Type	Description	Value
Potato Chips – Sealed Bag
Manifest Number	Number	MANIFEST_NUMBER	8480014
Manufacturer Code	Number	BRAND_CODE	80375993
Information 1	Number	INFO_ONE	15869563
Information 2	Number	INFO_TWO	6569569665
Information 3	Number	INFO_THREE	562
Planned Start Date	String	PROD_START_DATE	9/13/2018
Planned Start Time	String	PROD_START_TIME	081234

At first, I suggested using a script (Python, Perl, PHP, etc.) to parse the data before inserting into the database. But, the software which produced the data had the ability to insert the data directly into a MySQL database – but all of the data could only be inserted into one field. They didn’t have an option to add an extra step outside of the database connection – and they were new to MySQL, so they needed some help.

I decided to write a trigger for the table, in order to parse the data as it came into the database, but before it was inserted into a row. I also wanted to keep the original data in a column as well.

The first step was to create a database and a table. I mapped the column names to the first value of each row, skipping the header information which wasn’t needed. The column names are in blue, and the data is in yellow:

“Header information which may, or may not, contain one or more commas.”
Item Name	Type	Description	Value
Potato Chips – Sealed Bag
Manifest Number	Number	MANIFEST_NUMBER	8480014
Manufacturer Code	Number	BRAND_CODE	80375993
Information 1	Number	INFO_ONE	15869563
Information 2	Number	INFO_TWO	6569569665
Information 3	Number	INFO_THREE	562
Planned Start Date	String	PROD_START_DATE	9/13/2018
Planned Start Time	String	PROD_START_TIME	081234

I can then match the columns I want to the data values:

“Header information which may, or may not, contain one or more commas.”
Item Name	Potato Chips – Sealed Bag
Manifest Number	8480014
Manufacturer Code	80375993
Information 1	15869563
Information 2	6569569665
Information 3	562
Planned Start Date	9/13/2018
Planned Start Time	081234

Now, I can create the database and the table. I substituted the spaces in the field names with underscores (_). I also added a primary key column (id_MANIFEST_DATA) and a proper DATETIME column – as I want to combine the two columns PROD_START_DATE and PROD_START_TIME into one column to match MySQL’s DATETIME format. This way, they can perform a search on this column later (if necessary).

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

mysql> use MANIFEST_DATA_DB;
Database changed

mysql> CREATE TABLE `MANIFEST_DATA_DB`.`MANIFEST_DATA_TABLE` (
    ->   `id_MANIFEST_DATA` INT NOT NULL AUTO_INCREMENT,
    ->   `MANIFEST_DATA` VARCHAR(4096) NULL,
    ->   `ITEM_NAME` VARCHAR(1024) NULL,
    ->   `MANIFEST_NUMBER` INT NULL,
    ->   `MANUFACTURER_CODE` VARCHAR(1024) NULL,
    ->   `INFO_ONE` CHAR(32) NULL,
    ->   `INFO_TWO` CHAR(32) NULL,
    ->   `INFO_THREE` CHAR(32) NULL,
    ->   `PROD_START_DATE` CHAR(10) NULL,
    ->   `PROD_START_TIME` CHAR(6) NULL,
    ->   `PROD_TIMESTAMP` DATETIME NULL,
    ->   PRIMARY KEY (`id_MANIFEST_DATA`))
    -> AUTO_INCREMENT = 1000000;
Query OK, 0 rows affected (0.00 sec)

The initial “non-conforming data” will be inserted into the MANIFEST_DATA field, so all I have to do is to create a trigger to parse this field before the data is inserted.

Even though the data is on separate lines, parsing this data will be relatively easy, since the data is comma-delimited. I can use the SUBSTRING_INDEX function to create an array to store all of the data. But, since the first line of the data may or may not contain a comma, instead of counting commas from the beginning of the data, I will start at the end. Also, in this example, they don’t need the first line of data, as it is header information.

Let’s take a look at why I want to count backwards. Here are three rows of data – where the first column may or may not contain a comma – or it might contain two or more commas. I really only want to capture the last two columns of data.

"Hello, it's me",12345,ABCDE
"Hello it's me",67890,FGHIJ
"Hello, yes, it's me",112233,A1B2C3

If I parse the data based upon commas and start at the beginning, I will get different results when counting commas from the beginning of the data when the first line of data contains a comma. And I only want the data in green:

1	2	3	4	5
“Hello it’s me”	67890	FGHIJ
“Hello	it’s me”	12345	ABCDE
“Hello	yes	it’s me”	112233	A1B2C3

But if I count backwards, I will get the same result set regardless of how many commas are in the first line of data:

-5	-4	-3	-2	-1
		“Hello it’s me”	67890	FGHIJ
	“Hello	it’s me”	12345	ABCDE
“Hello	yes	it’s me”	112233	A1B2C3

In the actual data I want to sue, I don’t want to store the first row of data anyway – so it can be ignored. If I did need the first line, I would have to search for the quotes and parse that column separately.

Since the initial data will contain a line feed, I will want to replace the line feeds with a comma, so I can have all of the data on a single line and be able to use the SUBSTRING_INDEX function. Here is the original data again, but this time, I have added a column for the line feeds. Also, I am going to count backwards from the last data value. The numbers are in (bold):

/tr>

(-37)“Header information which may, or may not, contain one or more commas.”
(-36) Item Name	(-35) Type	(-34) Description	(-33) Value	-line feed-
(-32) Potato Chips – Sealed Bag	(-31)	(-30)	(-29)	-line feed-
(-28) Manifest Number	(-27) Number	(-26) MANIFEST_NUMBER	(-25) 8480014	-line feed-
(-24) Manufacturer Code	(-23) Number	(-22) BRAND_CODE	(-21) 80375993	-line feed-
(-20) Information 1	(-19) Number	(-18) INFO_ONE	(-17) 15869563	-line feed-
(-16) Information 2	(-15) Number	(-14) INFO_TWO	(-13) 6569569665	-line feed-
(-12) Information 3	(-11) Number	(-10) INFO_THREE	(-9) 562	-line feed-
(-8) Planned Start Date	(-7) String	(-6) PROD_START_DATE	(-5) 9/13/2018	-line feed-
(-4) Planned Start Time	(-3) String	(-2) PROD_START_TIME	(-1) 081234

Of course, you don’t have to map out the fields like I did – you can just count backwards.

The SUBSTRING_INDEX function works similar to grabbing a value out of an array – except the count value is where you want to stop grabbing data. You specify the string you want to parse, the delimiter you want to use, and the count of the value from the string where you want to stop grabbing data.

The format is:

SUBSTRING_INDEX(string,delimiter,count) 

Note: When the count value is a positive number, the count value is the ending position within the string. It doesn’t select the element itself.

Here is an example using the phrase “Hello, I must be going, see you later, bye.” which contains four values separated by commas. I want to capture the third value of “see you later”.

1	2	3	4
Hello	I must be going	See you later	bye

To select “see you later”, I choose the third column as the place where I will stop grabbing data – but, since this is the stopping point, I will get the rest of the phrase up until the third column.

mysql> select SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",3) as PARSE;
+---------------------------------------+
| PARSE                                 |
+---------------------------------------+
| Hello, I must be going, see you later |
+---------------------------------------+
1 row in set (0.00 sec)

I don’t want all of the phrase, I only want “see you later”. With a positive count value of three, I am getting the ending point – it stops at the third delimited value. If I use a negative count value, then I am getting the starting point of the string. If I count backwards, which would give me a negative count value (and I want to do this anyway because the first line of data in the main example may have multiple commas), I can use the value of negative two (-2) and get this:

mysql> select SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",-2) as PARSE;
+----------------------+
| PARSE                |
+----------------------+
|  see you later, bye. |
+----------------------+
1 row in set (0.00 sec)

That gives me a starting point for grabbing “see you later”, but I don’t want any data (the single word “bye”) after this. So, I can wrap an additional SUBSTRING_INDEX around the first one, and then only choose the first delimited set of data from the inner SUBSTRING_INDEX.

It looks like this:

The statement select SUBSTRING_INDEX(“Hello, I must be going, see you later, bye.”,”,”,-2) grabs both of these columns in blue:

1	2	3	4
Hello	I must be going	See you later	bye

But then I can use the second SUBSTRING_INDEX command to only select the first column of the remaining two columns:

SUBSTRING_INDEX(SUBSTRING_INDEX(“Hello, I must be going, see you later, bye.”,”,”,-2), “,”, 1)

1	2
See you later	bye

I can test it:

mysql> select SUBSTRING_INDEX(SUBSTRING_INDEX("Hello, I must be going, see you later, bye.",",",-2), ",", 1) as PARSE;
+----------------+
| PARSE          |
+----------------+
|  see you later |
+----------------+
1 row in set (0.01 sec)

This is essentially how I am going to parse the long line of data (after I convert the line feeds to commas). I will go backwards to the starting point, and then only select the first column beginning at that starting point.

I can then use the table I created to map put the data values I want to extract.

Column Name	Backwards Value
ITEM_NAME	-32
MANIFEST_NUMBER	-25
MANUFACTURER_CODE	-21
INFO_ONE	-17
INFO_TWO	-13
INFO_THREE	-9
PROD_START_DATE	-5
PROD_START_TIME	-1

Example: To extract the PROD_START_TIME, which is at position -1, I will use this in the trigger:

SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

I can now create my trigger to parse all of the data being inserted into the MANIFEST_DATA column. In the trigger, I will use some temporary variables to parse the MANIFEST_DATA, and I will also use the SUBSTRING function to parse the PROD_START_DATE and PROD_START_TIME fields to rearrange them into the correct MySQL DATETIME format. I will use the CONCAT function to combine them into a new DATETIME field. I have some notes in the stored procedure to help you figure out what I am doing.

DELIMITER $$
  
CREATE TRIGGER _convert_MANIFEST_DATA
BEFORE INSERT ON MANIFEST_DATA_DB.MANIFEST_DATA_TABLE
FOR EACH ROW
BEGIN

# Declare temporary variables

# This is the temporary field which contains all of the data
DECLARE _parse_MANIFEST_DATA varchar(4096);

# The temporary date and time fields needed to create the DATETIME field named PROD_TIMESTAMP
DECLARE _parse_TIME_DATE_DATE char(10);
DECLARE _parse_TIME_DATE_TIME char(10);

DECLARE _parse_TIME_DATE_date_final char(10);

DECLARE _parse_TIME_DATE_hour char(2);
DECLARE _parse_TIME_DATE_minutes char(2);
DECLARE _parse_TIME_DATE_sec char(2);

# Take the incoming large dataset which has line feeds and
# Replace the line feeds with a comma
set _parse_MANIFEST_DATA = replace(NEW.MANIFEST_DATA, "\n", ",");

# Parse each new column from the temporary field
SET NEW.ITEM_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -35), ",", 1);
SET NEW.MANIFEST_NUMBER = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -25), ",", 1);
SET NEW.MANUFACTURER_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -21), ",", 1);
SET NEW.INFO_ONE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -17), ",", 1);
SET NEW.INFO_TWO = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -13), ",", 1);
SET NEW.INFO_THREE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -9), ",", 1);
SET NEW.PROD_START_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Get the values from these two fields in order to combine them into a DATETIME field
SET _parse_TIME_DATE_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET _parse_TIME_DATE_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Convert the date from MM/DD/YYYY to YYYY-MM-DD
SET _parse_TIME_DATE_date_final = STR_TO_DATE(_parse_TIME_DATE_DATE, '%c/%d/%Y');

# Parse the time so we can add colons between the hour, minutes and seconds
SET _parse_TIME_DATE_hour = SUBSTRING(_parse_TIME_DATE_TIME, 1, 2);
SET _parse_TIME_DATE_minutes = SUBSTRING(_parse_TIME_DATE_TIME, 3, 2);
SET _parse_TIME_DATE_sec = SUBSTRING(_parse_TIME_DATE_TIME, 5, 2);

# Combine the newly-formatted date with the newly-formatted time so it conforms to the DATETIME format
SET NEW.PROD_TIMESTAMP = CONCAT(_parse_TIME_DATE_date_final, " ", _parse_TIME_DATE_hour, ":", _parse_TIME_DATE_minutes, ":", _parse_TIME_DATE_sec) ;

END$$

DELIMITER ;

Now I can insert the sample data into the database – where all of the data will go into the MANIFEST_DATA column, and the trigger will populate the rest of the columns by parsing the data.

mysql> INSERT INTO MANIFEST_DATA_TABLE (MANIFEST_DATA) VALUES ('Header information which may, or may not, contain one or more commas
    '> Item Name,Type,Description,Value
    '> Potato Chips - Sealed Bag,,,
    '> Manifest Number,Number,MANIFEST_NUMBER,8480014
    '> Manufacturer Code,Number,BRAND_CODE,80375993
    '> Information 1,Number,INFO_ONE,15869563
    '> Information 2,Number,INFO_TWO,6569569665
    '> Information 3,Number,INFO_THREE,562
    '> Planned Start Date,String,PROD_START_DATE,9/13/2018
    '> Planned Start Time,String,PROD_START_TIME,081234');
Query OK, 1 row affected (0.07 sec)

When I look at the contents of the table, I can see the trigger executed successfully. All of the “non-conforming” data is stored in the MANIFEST_DATA field, but the other fields were populated:

mysql> select * from MANIFEST_DATA_TABLE\G
*************************** 1. row ***************************
 id_MANIFEST_DATA: 1000000
    MANIFEST_DATA: Header information which may, or may not, contain one or more commas
Item Name,Type,Description,Value
Potato Chips - Sealed Bag,,,
Manifest Number,Number,MANIFEST_NUMBER,8480014
Manufacturer Code,Number,BRAND_CODE,80375993
Information 1,Number,INFO_ONE,15869563
Information 2,Number,INFO_TWO,6569569665
Information 3,Number,INFO_THREE,562
Planned Start Date,String,PROD_START_DATE,9/13/2018
Planned Start Time,String,PROD_START_TIME,081234
        ITEM_NAME: Type
  MANIFEST_NUMBER: 8480014
MANUFACTURER_CODE: 80375993
         INFO_ONE: 15869563
         INFO_TWO: 6569569665
       INFO_THREE: 562
  PROD_START_DATE: 9/13/2018
  PROD_START_TIME: 081234
   PROD_TIMESTAMP: 2018-09-13 08:12:34
1 row in set (0.00 sec)

Of course, this is only a short example of all of the parsing you can do with triggers in MySQL. And, if you want to look at your trigger, you can query the INFORMATION_SCHEMA.TRIGGERS table:

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='_convert_MANIFEST_DATA'\G
*************************** 1. row ***************************
           TRIGGER_CATALOG: def
            TRIGGER_SCHEMA: MANIFEST_DATA_DB
              TRIGGER_NAME: _convert_MANIFEST_DATA
        EVENT_MANIPULATION: INSERT
      EVENT_OBJECT_CATALOG: def
       EVENT_OBJECT_SCHEMA: MANIFEST_DATA_DB
        EVENT_OBJECT_TABLE: MANIFEST_DATA_TABLE
              ACTION_ORDER: 1
          ACTION_CONDITION: NULL
          ACTION_STATEMENT: BEGIN

# Declare temporary variables

# This is the temporary field which contains all of the data
DECLARE _parse_MANIFEST_DATA varchar(4096);

# The temporary date and time fields needed to create the DATETIME field named PROD_TIMESTAMP
DECLARE _parse_TIME_DATE_DATE char(10);
DECLARE _parse_TIME_DATE_TIME char(10);

DECLARE _parse_TIME_DATE_date_final char(10);

DECLARE _parse_TIME_DATE_hour char(2);
DECLARE _parse_TIME_DATE_minutes char(2);
DECLARE _parse_TIME_DATE_sec char(2);

# Take the incoming large dataset which has line feeds and
# Replace the line feeds with a comma
set _parse_MANIFEST_DATA = replace(NEW.MANIFEST_DATA, "
", ",");

# Parse each new column from the temporary field
SET NEW.ITEM_NAME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -35), ",", 1);
SET NEW.MANIFEST_NUMBER = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -25), ",", 1);
SET NEW.MANUFACTURER_CODE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -21), ",", 1);
SET NEW.INFO_ONE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -17), ",", 1);
SET NEW.INFO_TWO = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -13), ",", 1);
SET NEW.INFO_THREE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -9), ",", 1);
SET NEW.PROD_START_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET NEW.PROD_START_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Get the values from these two fields in order to combine them into a DATETIME field
SET _parse_TIME_DATE_DATE = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -5), ",", 1);
SET _parse_TIME_DATE_TIME = SUBSTRING_INDEX(SUBSTRING_INDEX(_parse_MANIFEST_DATA, ',', -1), ",", 1);

# Convert the date from MM/DD/YYYY to YYYY-MM-DD
SET _parse_TIME_DATE_date_final = STR_TO_DATE(_parse_TIME_DATE_DATE, '%c/%d/%Y');

# Parse the time so we can add colons between the hour, minutes and seconds
SET _parse_TIME_DATE_hour = SUBSTRING(_parse_TIME_DATE_TIME, 1, 2);
SET _parse_TIME_DATE_minutes = SUBSTRING(_parse_TIME_DATE_TIME, 3, 2);
SET _parse_TIME_DATE_sec = SUBSTRING(_parse_TIME_DATE_TIME, 5, 2);

# Combine the newly-formatted date with the newly-formatted time so it conforms to the DATETIME format
SET NEW.PROD_TIMESTAMP = CONCAT(_parse_TIME_DATE_date_final, " ", _parse_TIME_DATE_hour, ":", _parse_TIME_DATE_minutes, ":", _parse_TIME_DATE_sec) ;

END
        ACTION_ORIENTATION: ROW
             ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
  ACTION_REFERENCE_OLD_ROW: OLD
  ACTION_REFERENCE_NEW_ROW: NEW
                   CREATED: 2018-09-20 22:13:28.54
                  SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
                   DEFINER: root@localhost
      CHARACTER_SET_CLIENT: utf8mb4
      COLLATION_CONNECTION: utf8mb4_0900_ai_ci
        DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

 

---

	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.

Manipulating queries with non-conforming data via MySQL Query Rewrite Plugin, triggers and stored procedures

The MySQL database is used in thousands of third-party applications, but what can you do when you want to use MySQL with an application, but that application’s queries or data doesn’t match MySQL’s data type or SQL format?

This post will show you three ways to alter a query or mismatched data when you don’t have control of the application’s source code. Of course, there are hundreds of different ways to do what I am about to show you. In this example, I will show you how to use the MySQL Query Rewrite Plugin along with a trigger to alter the non-conforming data. I will also show you an example of manipulating data with a stored procedure.

A customer emailed me with a problem. They wanted to use MySQL for a third-party application, but they didn’t have access to the source code. Their main problem was the application’s TIMESTAMP format didn’t conform to MySQL’s TIMESTAMP format. To be specific, this application produced a TIMESTAMP value that included a trailing time zone, such as “2018-09-05 17:00:00 EDT”. MySQL has two column data types where you can store both the date and time in one column: TIMESTAMP and DATETIME – but MySQL cannot handle TIMESTAMP or DATETIME data with a trailing time zone.

When a TIMESTAMP value is being inserted into a row, MySQL converts the TIMESTAMP value from the current time zone set by the MySQL server (see Time Zone Support) to UTC (Coordinated Universal Time) for storage, and converts the data back from UTC to the current time zone (of the server) when retrieved. (This conversion does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s local time. The time zone can be set on a per-connection basis, and as long as the time zone setting remains constant, you will get back the same value you stored. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 5.1.12, “MySQL Server Time Zone Support”.

(From: https://dev.mysql.com/doc/refman/8.0/en/datetime.html)

The customer told me that this application would only be sending data with two different trailing time zones – Central and Eastern. With daylight-savings in use in both of these time zones, this would give us four possible trailing time zone values – CDT, CST, EDT and EST. What we want to do is to intercept the query, and write this TIMESTAMP data to a different column, and then convert the value to UTC time to be stored in the correct column in the database. Because we don’t have access to the source code, I am assuming we have full access to the MySQL database.

---

NOTE: Since we are using time zone information, if you want to duplicate this post, be sure to load the MySQL time zone information. See: https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html

---

 

The MySQL Rewrite Plugin

In MySQL version 5.7, a plugin named the “Query Rewrite Plugin” was introduced. This plugin can examine SQL statements received by the server and modify those statements before the server executes them. In other words, this gives you the ability to intercept “bad” queries and re-format them to be “good” queries for use with MySQL – or to rewrite the queries to do whatever you need. Think of it as a way to change the source code without actually having the source code.

Installing the plugin is fairly easy. In MySQL version 8.0, you install (or uninstall) the plugin via an SQL script provided with your MySQL installation. The script is named install_rewriter.sql and is located in the “share” directory under your MySQL home directory.

# cd /usr/local/mysql/share  (your directory may be different)
# mysql -u root -p < install_rewriter.sql
Enter password: (enter root password here)

The script only takes a few seconds to load (The uninstall script is named uninstall_rewriter.sql). To check and make sure the plugin was installed, run this command from within MySQL:

mysql> SHOW GLOBAL VARIABLES LIKE 'rewriter_enabled';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| rewriter_enabled | ON    |
+------------------+-------+
1 row in set (0.00 sec)

The plugin was installed correctly if the column named “Value” is set to “ON“.

For this example, I am going to create a small table with three columns, and assume that this is an table from a third-party application. The date_time_value column is where the application would normally store the timestamp information.

mysql> create database test;
 Query OK, 1 row affected (0.01 sec)
mysql> use test;
 Database changed
mysql> CREATE TABLE `time_example` (
  `idtime` int(11) NOT NULL AUTO_INCREMENT,
  `action_record` varchar(30) NOT NULL,
  `date_time_value` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`idtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8MB4;
Query OK, 0 rows affected (0.03 sec)

The date_time_value column will obviously not be able to store timestamp data with a trailing time zone, but let’s see what happens when we try and insert a row of data – and let’s pretend that this is the query the application uses.

mysql> insert into test.time_example (action_record, date_time_value) 
 values ('Arrived at work', '2018-09-05 17:00:00 EDT');
Error Code: 1292. Incorrect datetime value: '2018-09-05 17:00:00 EDT' 
 for column 'date_time_value' at row 1

Of course, we get an error because the format for the timestamp is incorrect.

What we want to do is to alter the table and add a column to store this improperly-formatted timestamp data.

mysql> ALTER TABLE `test`.`time_example` 
    -> ADD COLUMN `date_time_storage` VARCHAR(23) NULL AFTER `date_time_value`;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now that we have a column (date_time_storage) to store the “bad” timestamp data, we need to modify the incoming query so that it writes the timestamp data into the new column.

---

Note: In MySQL 8.0+, with the Query Rewrite Plugin, you can modify SELECT, INSERT, REPLACE, UPDATE, and DELETE statements. (Prior to MySQL 8.0.12 you could only modify SELECT statements)

---

 

This is the query the application is sending to the database:

insert into test.time_example (action_record, date_time_value) values (?, ?);

We want to modify the query to use the new date_time_storage column, instead of the date_time_value column. The new query would look like this:

insert into test.time_example (action_record, date_time_storage) values (?, ?);

Now that we have our old (bad) and new (good) queries, we can insert this into the rewrite_rules table of the query_rewrite database.

INSERT INTO query_rewrite.rewrite_rules
    (pattern, replacement, pattern_database) VALUES(
    'insert into test.time_example (action_record, date_time_value) values (?, ?)',
    'insert into test.time_example (action_record, date_time_storage) values (?, ?)',
    'time_example'
    );
1 row(s) affected, 1 warning(s): 1105 Query 'insert into test.time_example 
 (action_record, date_time_value) values ('Left building', '2018-09-05 17:00:00 EDT')' 
 rewritten to 'insert into test.time_example (action_record, date_time_storage) 
 values ('Left building', '2018-09-05 17:00:00 EDT')' by a query rewrite plugin

(More examples may be found on this page: Query Rewrite Plugin Usage)

We need to execute a stored procedure named flush_rewrite_rules to make this query-rewrite change permanent: (See: https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin-usage.html)

mysql> CALL query_rewrite.flush_rewrite_rules();
Query OK, 1 row affected (0.00 sec)

We can confirm the INSERT INTO query_rewrite.rewrite_rules by looking at the rewrite_rules table:

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: insert into test.time_example (action_record, date_time_value) values (?, ?)
  pattern_database: time_example
       replacement: insert into test.time_example (action_record, date_time_storage) values (?, ?)
           enabled: YES
           message: NULL
    pattern_digest: e823e987338aeae6d47f7a729e78f532d3ff3721237c15981bcd11fc2607efda
normalized_pattern: insert into `test`.`time_example` (`action_record`,`date_time_value`) values (?,?)
1 row in set (0.00 sec)

Next, let’s run the same query as before, and see if it puts the timestamp data that is supposed to go into the date_time_value column into the new date_time_storage column:

mysql> insert into test.time_example (action_record, date_time_value) 
 values ('Arrived at work', '2018-09-05 17:00:00 EDT');
Query OK, 1 row affected, 1 warning (0.01 sec)

And now the table contains this data:

mysql> select * from time_example;
+--------+-----------------+-----------------+-------------------------+
| idtime | action_record   | date_time_value | date_time_storage       |
+--------+-----------------+-----------------+-------------------------+
|      1 | Arrived at work | NULL            | 2018-09-05 17:00:00 EDT |
+--------+-----------------+-----------------+-------------------------+
1 rows in set (0.00 sec)

We now have the timestamp with the time zone data stored in the MySQL database, but we need to convert this to a proper format, and put the result into the date_time_value column.

To do this, we can use a trigger.

Normally, you would want your application to produce data in the correct format, but in this example, we don’t have access to the source code. So, we can create a trigger to convert the “incorrectly-formatted” data in date_time_storage to the correct data and store it in date_time_value.

---

NOTE: These examples won’t work if your TIMESTAMP uses microseconds (6-digits) precision (example: ‘1970-01-01 00:00:01.000000’) – but you can modify the code to accommodate microseconds.

---

 

Here is the SQL to create the trigger:

DELIMITER $$
  
CREATE TRIGGER _time_zone_convert_insert2
AFTER INSERT ON time_example
FOR EACH ROW
BEGIN

DECLARE _date_time_no_tz varchar(20);

SET _date_time_no_tz = SUBSTRING(NEW.date_time_storage, 1, 20);

IF NEW.date_time_storage like '%EDT' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%EST' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%CDT' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

IF NEW.date_time_storage like '%CST' THEN
    SET NEW.date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT');
END IF;

END$$

DELIMITER ;

Now that we have a trigger in place, let’s insert another line into the database – BUT, we still want to use the SQL from the application. The query will try and write to the date_time_value column, but the Query Rewrite Plugin will intercept the original query and substitute our new query instead – which will insert the timestamp data into the date_time_storage column, and then the trigger will convert the timestamp and place the correct value into the date_time_value column.

mysql> INSERT INTO time_example (action_record, date_time_value) 
 VALUES ('Lunch Break', '2018-09-05 18:00:00 EDT');
Query OK, 1 row affected (0.00 sec)

The table now contains a true timestamp column with the correct timestamp value in UTC. (The old row didn’t change)

mysql> SELECT * FROM test.time_example;
+--------+------------+---------------------+-------------------------+
| idtime | product_id | date_time_value     | date_time_storage       |
+--------+------------+---------------------+-------------------------+
|      1 | time now1  | NULL                | 2018-09-05 18:00:00 EDT |
|      2 | time now2  | 2018-09-05 22:00:00 | 2018-09-05 18:00:00 EDT |
+--------+------------+---------------------+-------------------------+
2 rows in set (0.00 sec)

But what about stored procedures?

The easiest way to handle the time zone conversion is with a trigger. But, to show you how stored procedures can do the same thing, I have an example of a stored procedure. In this example, I will be passing the values of the idtime and date_time_storage columns.

This example will be similar to the one above – I created a table named time_example, but this time, I am including the extra column:

'CREATE TABLE `time_example` (
  `idtime` int(11) NOT NULL AUTO_INCREMENT,
  `action_record` varchar(30) NOT NULL,
  `date_time_value` timestamp NULL DEFAULT NULL,
  `date_time_storage` varchar(23) DEFAULT NULL,
  PRIMARY KEY (`idtime`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8'

I then inserted a row, where I am storing the time stamp with the time zone information:

mysql> insert into test.time_example (action_record, date_time_storage) 
 values ('Left work', '2018-09-05 17:00:00 EDT’);
Query OK, 1 row affected (0.00 sec)

Here is what the row looks like:

mysql> SELECT * FROM test.time_example;
+--------+------------+-----------------+-------------------------+
| idtime | product_id | date_time_value | date_time_storage       |
+--------+------------+-----------------+-------------------------+
|      1 | Left work  | NULL            | 2018-09-05 17:00:00 EDT |
+--------+------------+-----------------+-------------------------+
1 row in set (0.00 sec)

Again, the date_time_storage column is a temporary storage column. I will call the stored procedure, and provide the idtime and date_time_storage values. The stored procedure which will look at the last three characters in the date_time_storage column, and then convert the time to UTC, which is then stored in the date_time_value column.

call _check_time_zone('1','2018-09-05 17:00:00 EDT');

Now the row looks like this, where the date_time_value column is now stored as UTC:

mysql> SELECT * FROM test.time_example;
+--------+------------+---------------------+-------------------------+
| idtime | product_id | date_time_value     | date_time_storage       |
+--------+------------+---------------------+-------------------------+
|      1 | Left work  | 2018-09-05 21:00:00 | 2018-09-05 17:00:00 EDT |
+--------+------------+---------------------+-------------------------+
1 row in set (0.00 sec)

And here is the code to create the stored procedure:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `_check_time_zone`(IN _id_time INT, IN _date_time_storage VARCHAR(23))
BEGIN

DECLARE _date_time_no_tz varchar(20);

SET _date_time_no_tz = SUBSTRING(_date_time_storage, 1, 20);

IF _date_time_storage like '%EDT' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%EST' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'EST5EDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%CDT' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'CST5CDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%CST' THEN 
UPDATE time_example SET date_time_value = CONVERT_TZ(_date_time_no_tz,'CST5CDT','GMT')
WHERE idtime = _id_time;
END IF;

IF _date_time_storage like '%UTC' THEN 
UPDATE time_example SET date_time_value = _date_time_no_tz
WHERE idtime = _id_time;
END IF;

END $$
DELIMITER ;

 

---

	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 InnoDB Cluster – Creating a sandbox and testing MySQL Shell, Router and Group Replication

MySQL’s InnoDB Cluster was released on Apr 12, 2017, with version 5.7 and is also included in MySQL version 8.0.

MySQL InnoDB cluster provides a complete high availability solution for MySQL. MySQL Shell includes AdminAPI which enables you to easily configure and administer a group of at least three MySQL server instances to function as an InnoDB cluster. Each MySQL server instance runs MySQL Group Replication, which provides the mechanism to replicate data within InnoDB clusters, with built-in failover. AdminAPI removes the need to work directly with Group Replication in InnoDB clusters, but for more information see Chapter 18, Group Replication which explains the details. MySQL Router can automatically configure itself based on the cluster you deploy, connecting client applications transparently to the server instances. In the event of an unexpected failure of a server instance the cluster reconfigures automatically. In the default single-primary mode, an InnoDB cluster has a single read-write server instance – the primary. Multiple secondary server instances are replicas of the primary. If the primary fails, a secondary is automatically promoted to the role of primary. MySQL Router detects this and forwards client applications to the new primary. Advanced users can also configure a cluster to have multiple-primaries. (source: Introducing InnoDB Cluster )

The following diagram shows an overview of how these technologies work together:

I am not going to go into any more details about how the InnoDB Cluster works, as there are plenty of articles over on the MySQL Server Team and MySQL High Availability blogs.

This post will walk you through setting up MySQL InnoDB Cluster in a sandbox environment. I used a Mac running 10.13, but these commands should also work on Linux. The only differences will be the location of the root directory and where MySQL is installed.

To begin, you will need to install the MySQL server (version 8.0), MySQL Router and MySQL Shell. You may download these from the MySQL downloads page. And, I am not going to walk you through the steps of installing these – as on the Mac, the installation is very easy. For any other OS, you will need to refer to the man pages for each package.

Okay – so I have installed all three components of the MySQL InnoDB Cluster – the MySQL server, MySQL Router and MySQL Shell (all three were versions 8.0.11). For ease of going through this tutorial, I would recommend opening five terminal windows – one for the MySQL Shell, one for regular OS access, and three for each of the sandboxes that I will create. The sandboxes will all run on one server, and each sandbox will have a different port number for the MySQL instance. In this example, I am using ports 3310, 3320 and 3330.

To make this tutorial easy to follow, simply enter/use the commands which appear as bold red. I have included the output from these commands separately.

One note: before you start, make sure that all of the directories in the the mysql home directory – /usr/local – are owned by the mysql user. You don’t have to create the mysql user separately, but you will notice the user name will be _mysql.

cd /usr/local
chown -R mysql mysql*

Let’s begin by logging in as root on my Mac and open the MySQL shell:

mysqlsh

~:root # mysqlsh
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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.

I will be creating three sandbox instances to demo how MySQL InnoDB Cluster works. I will deploy the first sandbox instance using port 3310 and will need to enter a root password

dba.deploySandboxInstance(3310);

 MySQL  JS > dba.deploySandboxInstance(3310);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3310

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3310 successfully deployed and started.
Use shell.connect('root@localhost:3310'); to connect to the instance.

Next, deploy the second sandbox instance using port 3320 – and all three instances will need to have the same root password.

dba.deploySandboxInstance(3320);

 MySQL  JS > dba.deploySandboxInstance(3320);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3320

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3320 successfully deployed and started.
Use shell.connect('root@localhost:3320'); to connect to the instance.

Finally, deploy the third sandbox instance using port 3330.

dba.deploySandboxInstance(3330);

 MySQL  JS > dba.deploySandboxInstance(3330);
A new MySQL sandbox instance will be created on this host in 
/var/root/mysql-sandboxes/3330

Warning: Sandbox instances are only suitable for deploying and 
running on your local machine for testing purposes and are not 
accessible from external networks.

Please enter a MySQL root password for the new instance: 
Deploying new MySQL instance...

Instance localhost:3330 successfully deployed and started.
Use shell.connect('root@localhost:3330'); to connect to the instance.

From within the mysql shell, while in Javascript mode, connect to the first host on port 3310.

shell.connect(“root@localhost:3310”);

 MySQL  JS > shell.connect("root@localhost:3310");
Please provide the password for 'root@localhost:3310': 
Creating a session to 'root@localhost:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 20
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I will create the cluster starting with the 3310 sandbox – and the name of the cluster will be simply ‘mycluster’.

cluster = dba.createCluster(‘mycluster’);

MySQL localhost:3310 ssl JS > cluster = dba.createCluster('mycluster');
A new InnoDB cluster will be created on instance 'root@localhost:3310'.

Validating instance at localhost:3310...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
Creating InnoDB cluster 'mycluster' on 'root@localhost:3310'...
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.

Next, add the other two sandboxes to the cluster – the ones on ports 3320 and 3330.

Adding 3320…

cluster.addInstance(“root@localhost:3320”);

 MySQL  localhost:3310 ssl  JS > cluster.addInstance("root@localhost:3320");
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.

Please provide the password for 'root@localhost:3320': 
Adding instance to the cluster ...

Validating instance at localhost:3320...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
The instance 'root@localhost:3320' was successfully added to the cluster.

And 3330…

cluster.addInstance(“root@localhost:3330”);

 MySQL  localhost:3310 ssl  JS > cluster.addInstance("root@localhost:3330")
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.

Please provide the password for 'root@localhost:3330': 
Adding instance to the cluster ...

Validating instance at localhost:3330...
Instance detected as a sandbox.
Please note that sandbox instances are only suitable for deploying test clusters for use within the same host.

This instance reports its own address as MacVM151.local

Instance configuration is suitable.
The instance 'root@localhost:3330' was successfully added to the cluster.

And now I can check the cluster status – and you can see 3310 is read-write, and the other two are read-only.

cluster.status();

 MySQL  localhost:3310 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:3310"
}

I have opened another terminal window, where I can also check the processes for mysqld, and you can see all three sandbox instances are running.

ps -ef|grep mysqld

~:root # ps -ef|grep mysqld
  501  2124   853   0  9:25PM ttys000    0:00.00 grep mysqld
  501  2078     1   0  8:55PM ttys002    0:07.28 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3310/my.cnf
  501  2098     1   0  9:16PM ttys002    0:03.98 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3320/my.cnf
  501  2106     1   0  9:16PM ttys002    0:03.67 /usr/local/mysql/bin/mysqld --defaults-file=/Users/tonydarnell/mysql-sandboxes/3330/my.cnf

From the terminal, go to /var/root and you can see the mysql-sandboxes directory, and the contents of each directory

cd /var/root
pwd
ls -ld mysql-sandboxes
ls -ld mysql-sandboxes/*
ls -ld mysql-sandboxes/3310/*

~:root # cd /var/root

~:root # pwd
/var/root

~:root # ls -ld mysql-sandboxes
drwxr-xr-x  5 _mysql  wheel  170 Apr 24 11:25 mysql-sandboxes

~:root # ls -ld mysql-sandboxes/*
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 12:07 mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3330

~:root # ls -ld mysql-sandboxes/3310/*
-rw-r-----   1 root    wheel     5 Apr 24 12:07 mysql-sandboxes/3310/3310.pid
-rw-------   1 _mysql  wheel   746 Apr 24 12:07 mysql-sandboxes/3310/my.cnf
drwxr-xr-x   2 _mysql  wheel    68 Apr 24 11:24 mysql-sandboxes/3310/mysql-files
drwxr-x---  41 _mysql  wheel  1394 Apr 24 12:07 mysql-sandboxes/3310/sandboxdata
-rwx------   1 _mysql  wheel   126 Apr 24 11:24 mysql-sandboxes/3310/start.sh
-rwx------   1 _mysql  wheel   196 Apr 24 11:24 mysql-sandboxes/3310/stop.sh

I want make sure I change ownership of the sandboxes to the mysql user.

cd /var/root
chown -R mysql mysql-sandboxes/
ls -ld mysql-sandboxes/

~:root # cd /var/root
~:root # chown -R mysql mysql-sandboxes/
~:root # ls -ld mysql-sandboxes/
drwxr-xr-x  5 _mysql  wheel  170 Apr 24 11:25 mysql-sandboxes/

Now, I want to verify that mysql router isn’t running.

ps -ef|grep router

~:root # ps -ef|grep router
    0  2766  2356   0 11:31AM ttys002    0:00.00 grep router

Before I start mysqlrouter I will want to include the router bin directory in the $PATH for root
I will edit root’s .profile file and add: /usr/local/mysql-router/bin to the $PATH.

I can now start the mysqlrouter in bootstrap mode. If you run this as root you will need to specify the user with the –user variable.

mysqlrouter –bootstrap localhost:3310 –directory /usr/local/myrouter –user=mysql

/usr/local:root # mysqlrouter --bootstrap localhost:3310 --directory /usr/local/myrouter --user=mysql
Please enter MySQL password for root: 

Bootstrapping MySQL Router instance at '/usr/local/myrouter'...
MySQL Router  has now been configured for the InnoDB cluster 'mycluster'.

The following connection information can be used to connect to the cluster.

Classic MySQL protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:6446
- Read/Only Connections: localhost:6447
X protocol connections to cluster 'mycluster':
- Read/Write Connections: localhost:64460
- Read/Only Connections: localhost:64470

/usr/local/myrouter/start.sh

Next, I will start the router.

/usr/local:root # /usr/local/myrouter/start.sh

I want to check the processes to make sure that router is running.

ps -ef|grep router

/usr/local:root # ps -ef|grep router
    0  2145     1   0  9:32PM ttys000    0:00.02 sudo ROUTER_PID=/usr/local/myrouter/mysqlrouter.pid /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
   74  2146  2145   0  9:32PM ttys000    0:00.22 /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
    0  2148  2136   0  9:32PM ttys000    0:00.00 grep router

I can now connect to the router and see which of the sandbox instances I connect to from the router.

If you already have a mysql shell window open – use this command: shell.connect(“root@localhost:6446”).

Or, from the command prompt – use this mysqlsh –uri root@localhost:6446

/usr/local/myrouter:root # mysqlsh --uri root@localhost:6446
Creating a session to 'root@localhost:6446'
Enter password: 
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 135
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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  localhost:6446 ssl  JS > 

Switch to sql mode and check to see which port is being used.

\sql

select @@port;

 MySQL  localhost:6446 ssl  JS > \sql
Switching to SQL mode... Commands end with ;
MySQL  localhost:6446 ssl  SQL > select @@port;
+--------+
| @@port |
+--------+
|   3310 |
+--------+
1 row in set (0.0005 sec)

I can see that I am connected to port 3310 – which is the read/write instance of the InnoDB cluster.

If you want to check the status of the cluster – you can’t do that from router – you will have to connect to ports 3310, 3320 or 3330.

If you try to check the status of the cluster while in SQL mode, you get this error:

 MySQL  localhost:6446 ssl  SQL > cluster.status();
ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cluster.status()' at line 1

If you try while in javascript mode, you get this error:

 MySQL  localhost:6446 ssl  JS > cluster.status();
ReferenceError: cluster is not defined

So, I will want to connect back to the cluster itself – but first I need to go to javascript mode.

\js
shell.connect(“root@localhost:3310”);

 MySQL  localhost:6446 ssl  SQL > \js
Switching to JavaScript mode...

 MySQL  JS > shell.connect("root@localhost:3310");
Please provide the password for 'root@localhost:3310': 
Creating a session to 'root@localhost:3310'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 193
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I can check the status of the cluster again from javascript mode.

cluster=dba.getCluster();
cluster.status();

 MySQL  localhost:3310 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3310", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:3310"
}

I am going to create a database, a table and then insert data into the table so I can see how group replication will replicate the changes from the read-write server to the other two servers.

I am opening three terminal windows each open to a separate port – 3310, 3320, and 3330.

mysql -uroot -p -P3310 -h127.0.0.1

/usr/local/myrouter:root #  mysql -uroot -p -P3310 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 219
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

mysql -uroot -p -P3320 -h127.0.0.1

~:root # mysql -uroot -p -P3320 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 109
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

mysql -uroot -p -P3330 -h127.0.0.1

/usr/local:root # mysql -uroot -p -P3330 -h127.0.0.1
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 99
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> 

On each of the three instances – 3310, 3320 and 3330 – I will look at what databases I already have on each instance (they should only contain the default mysql databases).

show databases;

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

On the read-write server, which is on port 3310, I will create a database named test_01.

create database test_01;

mysql> create database test_01;
Query OK, 1 row affected (0.05 sec)

Now, I can check to see if the database was created on 3310, and then check on the other two to see that it has been replicated. I will run this on all three instances.

show databases;

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

I can see the test_01 database, and the new database doesn’t have any tables, so I will run this on all three run this to show zero tables:

use test_01;show tables;

mysql> use test_01;show tables;
Database changed
Empty set (0.01 sec);

I am going to create a table named “employees” on 3310.

CREATE TABLE `employees` (
`id_emp` int(11) NOT NULL AUTO_INCREMENT,
`name_first` varchar(45) DEFAULT NULL,
`name_middle` varchar(45) DEFAULT NULL,
`name_last` varchar(45) DEFAULT NULL,
`phone_home` varchar(45) DEFAULT NULL,
`phone_cell` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id_emp`)
) ENGINE=InnoDB AUTO_INCREMENT=10000;

mysql> CREATE TABLE `employees` (
    ->   `id_emp` int(11) NOT NULL AUTO_INCREMENT,
    ->   `name_first` varchar(45) DEFAULT NULL,
    ->   `name_middle` varchar(45) DEFAULT NULL,
    ->   `name_last` varchar(45) DEFAULT NULL,
    ->   `phone_home` varchar(45) DEFAULT NULL,
    ->   `phone_cell` varchar(45) DEFAULT NULL,
    ->   PRIMARY KEY (`id_emp`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10000;
Query OK, 0 rows affected (0.10 sec)

On all three instances – 3310, 3320 and 3330 – I will run this to show that the employee table creation was propagated to the other two servers via replication.

use test_01;show tables;

mysql> use test_01;show tables;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
+-------------------+
| Tables_in_test_01 |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)

And, on all three instances – 3310, 3320 and 3330 – I will run this to show that the employee table is empty.

select * from employees;

mysql> select * from employees;
Empty set (0.01 sec)

Now I can insert a row into the employees table on 3310.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘John’, ‘H’, ‘Smith’, ‘404-555-1212’, ‘404-555-2020’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('John', 'H', 'Smith', '404-555-1212', '404-555-2020');
Query OK, 1 row affected (0.00 sec)

And, on all three instances – 3310, 3320 and 3330 – I will run this to show that the insert statement was propagated to the other two servers.

select * from employees;

mysql> select * from employees;
+--------+------------+-------------+-----------+--------------+--------------+
| id_emp | name_first | name_middle | name_last | phone_home   | phone_cell   |
+--------+------------+-------------+-----------+--------------+--------------+
|  10006 | John       | H           | Smith     | 404-555-1212 | 404-555-2020 |
+--------+------------+-------------+-----------+--------------+--------------+
1 row in set (0.00 sec)

On another terminal – login as root and let’s take a look at the sandbox files to see what was created.

cd /var/root
pwd
ls -ld mysql-sandboxes/*

~:root # cd /var/root
~:root # pwd
/var/root
~:root # ls -ld mysql-sandboxes/*
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:24 mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 mysql-sandboxes/3330

Now from within shell – connect to the router. You might have to start a new shell – quit the old one if you have it open – otherwise, you might still be connected to 3310.

mysqlsh

~:root # mysqlsh
MySQL Shell 8.0.11

Copyright (c) 2016, 2018, 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.

shell.connect(“root@localhost:6446”);

 MySQL  JS > shell.connect("root@localhost:6446");
Please provide the password for 'root@localhost:6446': 
Creating a session to 'root@localhost:6446'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 146
Server version: 8.0.11 MySQL Community Server - GPL
No default schema selected; type \use  to set one.

I am going to kill the first box sandbox (using port 3310) which was the read-write instance.

dba.killSandboxInstance(3310);

 MySQL  localhost:6446 ssl  JS > dba.killSandboxInstance(3310);
The MySQL sandbox instance on this host in 
/var/root/mysql-sandboxes/3310 will be killed


Killing MySQL instance...

Instance localhost:3310 successfully killed.

# switch to sql mode

\sql

 MySQL  localhost:6446 ssl  JS > \sql
Switching to SQL mode... Commands end with ;

Now, I can check to see which port is now being used by the router.

select @@port;

 MySQL  localhost:6446 ssl  SQL > select @@port;
+--------+
| @@port |
+--------+
|   3320 |
+--------+
1 row in set (0.0004 sec)

I will switch to another terminal window and despite killing the sandbox, the sandbox files for 3310 weren’t removed.

ls -ld /var/root/mysql-sandboxes/*

~:root # ls -ld /var/root/mysql-sandboxes/*
drwxr-xr-x  7 _mysql  wheel  238 Apr 24 11:58 /var/root/mysql-sandboxes/3310
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 /var/root/mysql-sandboxes/3320
drwxr-xr-x  8 _mysql  wheel  272 Apr 24 11:25 /var/root/mysql-sandboxes/3330

I will switch back the mysqlsh window, and switch to javascript mode.

\js

 MySQL  localhost:6446 ssl  SQL > \js
Switching to JavaScript mode...

I can now check the status of the cluster.

cluster=dba.getCluster();

 MySQL  localhost:6446 ssl  JS > cluster=dba.getCluster();

cluster.status();

You can see how the instance for 3310 is now labeled as MISSING and how 3320 is now the read-write instance.

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "(MISSING)"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

Let’s add 3310 back into the cluster – and after you do this, if you quickly do another cluster status, you will see it is in recovery mode.

dba.startSandboxInstance(3310);

 MySQL  localhost:6446 ssl  JS > dba.startSandboxInstance(3310);
The MySQL sandbox instance on this host in 
/var/root/mysql-sandboxes/3310 will be started


Starting MySQL instance...

Instance localhost:3310 successfully started.

# if you do another cluster.status(); very quickly
# you can now see that 3310 is in RECOVERING mode

cluster.status();

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK_NO_TOLERANCE", 
        "statusText": "Cluster is NOT tolerant to any failures. 1 member is not active", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "RECOVERING"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

And then do another cluster.status() – and you can see how 3310 has rejoined the cluster, but it is now a read-only node.

cluster.status();

 MySQL  localhost:6446 ssl  JS > cluster.status();
{
    "clusterName": "mycluster", 
    "defaultReplicaSet": {
        "name": "default", 
        "primary": "localhost:3320", 
        "ssl": "REQUIRED", 
        "status": "OK", 
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.", 
        "topology": {
            "localhost:3310": {
                "address": "localhost:3310", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3320": {
                "address": "localhost:3320", 
                "mode": "R/W", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }, 
            "localhost:3330": {
                "address": "localhost:3330", 
                "mode": "R/O", 
                "readReplicas": {}, 
                "role": "HA", 
                "status": "ONLINE"
            }
        }
    }, 
    "groupInformationSourceMember": "mysql://root@localhost:6446"
}

Now that 3310 is back online, if I try and do an insert from 3310 – I get an error – because it is a read-only node.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘John’, ‘H’, ‘Smith’, ‘404-555-1212’, ‘404-555-2020’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('John', 'H', 'Smith', '404-555-1212', '404-555-2020');
ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

I can do an insert on 3320 as it is the read-write node, and check to see if it was replicated to the other two servers.

INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES (‘Susan’, ‘K’, ‘James’, ‘912-555-8565’, ‘912-555-9986’);

mysql> INSERT INTO `test_01`.`employees` (`name_first`, `name_middle`, `name_last`, `phone_home`, `phone_cell`) VALUES ('Susan', 'K', 'James', '912-555-8565', '912-555-9986');
Query OK, 1 row affected (0.09 sec)

And now I can check the employees table to see both rows of data – on all of the nodes.

use test_01; select * from employees;

mysql> select * from employees;                                                              
+--------+------------+-------------+-----------+--------------+--------------+
| id_emp | name_first | name_middle | name_last | phone_home   | phone_cell   |
+--------+------------+-------------+-----------+--------------+--------------+
|  10003 | John       | H           | Smith     | 404-555-1212 | 404-555-2020 |
|  10004 | Susan      | K           | James     | 912-555-8565 | 912-555-9986 |
+--------+------------+-------------+-----------+--------------+--------------+
2 rows in set (0.00 sec)

The InnoDB Cluster (sandbox version) is now up and running!

---

If you want to start over, kill all three mysqld processes, where pid1 and pi2 are the process ID’s from the ps -ef statement.

ps -ef| grep mysqld
kill -9 pid1 pid2

Double-check to make sure you killed the mysqld processes:

ps -ef| grep mysqld

~:root # ps -ef| grep mysqld
    0  2342     1   0 10:05PM ttys000    2:34.77 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3320/my.cnf --user=root
    0  2347     1   0 10:05PM ttys000    2:29.65 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3330/my.cnf --user=root
    0  2706     1   0  9:58AM ttys000    0:41.80 /usr/local/mysql/bin/mysqld --defaults-file=/var/root/mysql-sandboxes/3310/my.cnf --user=root
    0  2721  2356   0 11:17AM ttys002    0:00.00 grep mysqld
~:root # kill -9 2342 2347 2706
~:root # ps -ef| grep mysqld
    0  2723  2356   0 11:17AM ttys002    0:00.00 grep mysqld

Remove the sandbox files.

cd /var/root
ls -ld mysql-sandboxes/
rm -r mysql-sandboxes/
ls -ld mysql-sandboxes/

~:root # cd /var/root
~:root # ls -ld mysql-sandboxes/
drwxr-xr-x  5 _mysql  wheel  170 Apr 23 22:05 mysql-sandboxes/
~:root # rm -r mysql-sandboxes/
~:root # ls -ld mysql-sandboxes/
ls: mysql-sandboxes/: No such file or directory

ps -ef|grep router
kill -9 2645 2646 (whatever the PIDs are for router)
ps -ef|grep router

~:root # ps -ef|grep router
    0  2645     1   0  9:27AM ttys000    0:00.01 sudo ROUTER_PID=/usr/local/myrouter/mysqlrouter.pid /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
   74  2646  2645   0  9:27AM ttys000    0:39.63 /usr/local/bin/mysqlrouter -c /usr/local/myrouter/mysqlrouter.conf --user=mysql
    0  2764  2356   0 11:31AM ttys002    0:00.00 grep router
~:root # kill -9 2646

Remove the /usr/local/myrouter directory.

cd /usr/local/
ls -ld myrouter
rm -r /usr/local/myrouter

/usr/local:root # cd /usr/local/
/usr/local:root # ls -ld myrouter
drwx------  10 _mysql  _mysql  340 Apr 24 11:40 myrouter
/usr/local:root # rm -r /usr/local/myrouter
/usr/local:root # ls -ld myrouter
ls: myrouter: No such file or directory

You can now start the whole process over again.

	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.

MySQL Enterprise Edition Database Firewall – Control and Monitor SQL Statement Executions

As of MySQL 5.6.24, MySQL Enterprise Edition includes MySQL Enterprise Firewall, an application-level firewall (it runs within the mysql database process) that enables database administrators to permit or deny SQL statement execution based on matching against whitelists of accepted statement patterns. This helps harden MySQL Server against attacks such as SQL injection or attempts to exploit applications by using them outside of their legitimate query workload characteristics.

Each MySQL account registered with the firewall has its own whitelist of statement patterns (a tokenized representation of a SQL statement), enabling protection to be tailored per account. For a given account, the firewall can operate in recording or protecting mode, for training in the accepted statement patterns or protection against unacceptable statements. The diagram illustrates how the firewall processes incoming statements in each mode.

MySQL Enterprise Firewall Operation

(from https://dev.mysql.com/doc/refman/5.6/en/firewall.html)

If you do not have a MySQL Enterprise Edition license, you may download a trial version of the software via Oracle eDelivery. The MySQL Firewall is included in the MySQL Product Pack, specifically for MySQL Database 5.6.24 or higher.

MySQL Enterprise Firewall has these components:

• A server-side plugin named MYSQL_FIREWALL that examines SQL statements before they execute and, based on its in-memory cache, renders a decision whether to execute or reject each statement.
• Server-side plugins named MYSQL_FIREWALL_USERS and MYSQL_FIREWALL_WHITELIST implement INFORMATION_SCHEMA tables that provide views into the firewall data cache.
• System tables named firewall_users and firewall_whitelist in the mysql database provide persistent storage of firewall data.
• A stored procedure named sp_set_firewall_mode() registers MySQL accounts with the firewall, establishes their operational mode, and manages transfer of firewall data between the cache and the underlying system tables.
• A set of user-defined functions provides an SQL-level API for synchronizing the cache with the underlying system tables.
• System variables enable firewall configuration and status variables provide runtime operational information.

(from https://dev.mysql.com/doc/refman/5.6/en/firewall-components.html)

Installing the Firewall

Installing the firewall is fairly easy. After you install MySQL version 5.6.24 or greater, you simply execute an SQL script that is located in the $MYSQL_HOME/share directory. There are two versions of the script, one for Linux and one for Windows (the firewall isn’t supported on the Mac yet).

The scripts are named win_install_firewall.sql for Windows and linux_install_firewall.sql for linux. You may execute this script from the command line or via MySQL Workbench. For the command line, be sure you are in the directory where the script is located.

shell> mysql -u root -p mysql < win_install_firewall.sql
Enter password: (enter root password here)

The script create the firewall tables, functions, stored procedures and installs the necessary plugins. The script contains the following:

# Copyright (c) 2015 Oracle and/or its affiliates. All rights reserved.
# Install firewall tables
USE mysql;
CREATE TABLE IF NOT EXISTS mysql.firewall_whitelist( USERHOST VARCHAR(80) NOT NULL, RULE text NOT NULL) engine= MyISAM;
CREATE TABLE IF NOT EXISTS mysql.firewall_users( USERHOST VARCHAR(80) PRIMARY KEY, MODE ENUM ('OFF', 'RECORDING', 'PROTECTING', 'RESET') DEFAULT 'OFF') engine= MyISAM;

INSTALL PLUGIN mysql_firewall SONAME 'firewall.dll';
INSTALL PLUGIN mysql_firewall_whitelist SONAME 'firewall.dll';
INSTALL PLUGIN mysql_firewall_users SONAME 'firewall.dll';

CREATE FUNCTION set_firewall_mode RETURNS STRING SONAME 'firewall.dll';
CREATE FUNCTION normalize_statement RETURNS STRING SONAME 'firewall.dll';
CREATE AGGREGATE FUNCTION read_firewall_whitelist RETURNS STRING SONAME 'firewall.dll';
CREATE AGGREGATE FUNCTION read_firewall_users RETURNS STRING SONAME 'firewall.dll';
delimiter //
CREATE PROCEDURE sp_set_firewall_mode (IN arg_userhost VARCHAR(80), IN arg_mode varchar(12))
BEGIN
IF arg_mode = "RECORDING" THEN
  SELECT read_firewall_whitelist(arg_userhost,FW.rule) FROM mysql.firewall_whitelist FW WHERE FW.userhost=arg_userhost;
END IF;
SELECT set_firewall_mode(arg_userhost, arg_mode);
if arg_mode = "RESET" THEN
  SET arg_mode = "OFF";
END IF;
INSERT IGNORE INTO mysql.firewall_users VALUES (arg_userhost, arg_mode);
UPDATE mysql.firewall_users SET mode=arg_mode WHERE userhost = arg_userhost;

IF arg_mode = "PROTECTING" OR arg_mode = "OFF" THEN
  DELETE FROM mysql.firewall_whitelist WHERE USERHOST = arg_userhost;
  INSERT INTO mysql.firewall_whitelist SELECT USERHOST,RULE FROM INFORMATION_SCHEMA.mysql_firewall_whitelist WHERE USERHOST=arg_userhost;
END IF;
END //
delimiter ;

After you run the script, the firewall should be enabled. You may verify it by running this statement:

mysql> SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| mysql_firewall_max_query_size |  4096 |
| mysql_firewall_mode           |    ON |
| mysql_firewall_trace          |   OFF |
+-------------------------------+-------+

Testing the Firewall

To test the firewall, you may use a current mysql user, but we are going to create a test user for this example – webuser@localhost. (The user probably doesn’t need all privileges, but for this example we will grant everything to this user)

CREATE USER 'webuser'@'localhost' IDENTIFIED BY 'Yobuddy!';
'GRANT ALL PRIVILEGES ON *.* TO 'webuser'@'localhost' WITH GRANT OPTION'

OPTIONAL: For our test, we will be using the sakila schema provided by MySQL. You may download the sakila database schema (requires MySQL 5.0 or later) at http://dev.mysql.com/doc/index-other.html. If you don’t want to use the sakila database, you may use your own existing database or create a new database.

After downloading the sakila schema, you will have two files, named sakila-schema.sql and sakila-data.sql. Execute the sakila-schema.sql first, and then sakila-data.sql to populate the database with data. If you are using the command line, simply do the following: (substitute UserName for a mysql user name)

# mysql -uUserName -p < sakila-schema.sql
# mysql -uUserName -p < sakila-data.sql

After creating the sakila schema and importing the data, we now set the firewall to record those queries which we want to allow:

mysql> CALL `mysql`.`sp_set_firewall_mode`("webuser@localhost","RECORDING")
+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0  Imported rules: 0          |
+-----------------------------------------------+
1 row in set (0.14 sec)

+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.22 sec)
Query OK, 5 rows affected (0.28 sec)

We can check to see the firewall mode via this statement, to be sure we are in the recording mode:

mysql> SELECT * FROM MYSQL.FIREWALL_USERS;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| webuser@localhost |  RECORDING |
+-------------------+------------+
1 row in set (0.02 sec)

Now that we have recording turned on, let’s run a few queries:

mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> select * from actor limit 2;
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+
2 rows in set (0.13 sec)

mysql> select first_name, last_name from actor where first_name like 'T%';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| TIM        | HACKMAN   |
| TOM        | MCKELLEN  |
| TOM        | MIRANDA   |
| THORA      | TEMPLE    |
+------------+-----------+
4 rows in set (0.00 sec)

We turn off the recording by turning on the protection mode:

mysql> CALL `mysql`.`sp_set_firewall_mode`("webuser@localhost","PROTECTING");
+-------------------------------------------+
| set_firewall_mode(arg_userhost, arg_mode) |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+
1 row in set (0.00 sec)

We can check to see the firewall mode via this statement:

mysql> SELECT * FROM MYSQL.FIREWALL_USERS;
+-------------------+------------+
| USERHOST          | MODE       |
+-------------------+------------+
| webuser@localhost | PROTECTING |
+-------------------+------------+
1 row in set (0.02 sec)

And we can look at our whitelist of statements:

mysql>  SELECT * FROM MYSQL.FIREWALL_WHITELIST;
+-------------------+-------------------------------------------------------------------+
| USERHOST          | RULE                                                              |
+-------------------+-------------------------------------------------------------------+
| webuser@localhost | SELECT * FROM actor LIMIT ?                                       |
| webuser@localhost | SELECT SCHEMA ( )                                                 |
| webuser@localhost | SELECT first_name , last_name FROM actor WHERE first_name LIKE ?  |
| webuser@localhost | SHOW TABLES                                                       |
+-------------------+-------------------------------------------------------------------+
4 rows in set (0.00 sec)

The firewall is now protecting against non-whitelisted queries. We can execute a couple of the queries we previously ran, which should be allowed by the firewall.

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.01 sec)

Now we run two new queries, which should be blocked by the firewall.

mysql> select * from rental;
ERROR 1045 (42000): Firewall prevents statement

mysql> select * from staff;
ERROR 1045 (42000): Firewall prevents statement

The server will write an error message to the log for each statement that is rejected. Example:

2015-03-21T22:59:05.371772Z 14 [Note] Plugin MYSQL_FIREWALL reported:
'ACCESS DENIED for webuser@localhost. Reason: No match in whitelist.
Statement: select * from rental '

You can use these log messages in your efforts to identify the source of attacks.

To see how much firewall activity you have, you may look look at the status variables:

mysql> SHOW GLOBAL STATUS LIKE 'Firewall%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Firewall_access_denied  | 42    |
| Firewall_access_granted | 55    |
| Firewall_cached_entries | 78    |
+-------------------------+-------+

The variables indicate the number of statements rejected, accepted, and added to the cache, respectively.

The MySQL Enterprise Firewall Reference is found at https://dev.mysql.com/doc/refman/5.6/en/firewall-reference.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.

MySQL Enterprise Transparent Data Encryption (TDE) – provides at-rest encryption for physical InnoDB tablespace data files

With MySQL version 5.7.11 and up, Oracle continues to improve MySQL’s security features by adding MySQL Enterprise Transparent Data Encryption (TDE) for InnoDB tables stored in innodb_file_per_table tablespaces. This feature provides at-rest encryption for physical tablespace data files.

MySQL Enterprise TDE uses a two-tier encryption key architecture, consisting of a master encryption key and tablespace keys. When an InnoDB table is encrypted, a tablespace key is encrypted with the master key and the encrypted value of the tablespace key is stored in the tablespace header. When encrypting tablespace data, InnoDB transparently uses the master encryption key to decrypt the tablespace key and then uses it to encrypt (on write) and decrypt (on read).

The master encryption key is stored in memory on the MySQL keyring. Using a key vault, this key ring is both persisted and protected. The master key nor any of the tablespace keys are ever written to disk in plain text. When the the user first enables TDE, and when the server starts up or when the master key is rotated – the master key is requested and updated from the key vault. Databases and their tablespaces can get large, and had we chosen a single-tiered method, then key rotation would require re-encrypting entire tablespaces. This process is slow, costly and risky. With a two-tiered scheme, the decrypted version of a tablespace key never changes, but the master encryption key may be changed as required. This action is referred to as master-key rotation and it re-encrypts all of the tablespace keys in one atomic operation very rapidly (milliseconds).

There are various key vaults in the market and there’s also a common industry protocol supported by most vaults. The protocol is KMIP and it comes from OASIS. Losing keys (whether by accident, mismanagement, or getting hacked) means you lose your data. To ensure your keys are available, secure, auditable, etc., you will want to use a key-vault management software application such as Oracle Key Vault. Oracle Key Vault enables you to quickly deploy encryption and other security solutions by centrally managing encryption keys not only for MySQL, but across many things, including (but not limited to) Oracle Wallets, Java Keystores, and credential files.

Using the label for TDE requires:
1 – Applications aren’t aware of encryption – it is provided seamlessly and doesn’t require any changes
2 – The data is encrypted at-rest – so the operating system users or other access to the files on the media or the file system can’t read the data (without a key)
3 – The keys are secured, available and protected – you can’t leave the key in the door (or under the mat)
4 – The keys are never written to the filesystem (in plain text)

MySQL does include tablespace encryption with the community version but that is not TDE as it doesn’t meet requirements #3 and #4 – the keys aren’t necessarily secured.

MySQL Enterprise TDE supports the Advanced Encryption Standard (AES) block-based encryption algorithm. It uses Electronic Codebook (ECB) block encryption mode for tablespace key encryption and Cipher Block Chaining (CBC) block encryption mode for data encryption. (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In this post, I will show you how easy it is to use this new feature to encrypt your InnoDB tables (requires a licensed version of the MySQL Enterprise Edition MySQL 5.7.11 or higher).

Setting up MySQL TDE

Get Oracle Key Vault Credentials

First you will need to set up an account in Oracle Key Vault and create an endpoint (the key storage that keyring_okv plugin will talk to). Once you are done with that, you will have to create a configuration folder for keyring_okv. This folder will contain credentials to log into Oracle Key Vault. This, of course, needs to be stored securely. Go to this link to find information on how to accomplish this:
https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/keyring-okv-plugin.html
As the result you should have a configured endpoint in Oracle Key Vault and the configuration folder which should look like this:

# ls -l
total 8
-rw-rw-r-- 1 rob rob 427 lip 7 18:49 okvclient.ora
drwxrwxr-x 2 rob rob 4096 lip 7 18:49 ssl
# ls -l ssl
10:47 $ ls -l ssl
total 16
-rw-rw-r-- 1 rob rob 1200 lip 7 18:49 CA.pem
-rw-rw-r-- 1 rob rob 1209 lip 7 18:49 cert.pem
-rw-rw-r-- 1 rob rob 1027 lip 7 18:49 cert_req.pem
-rw-rw-r-- 1 rob rob 1675 lip 7 18:49 key.pem

From now on we will call this folder okv_enrollment.

Note: If you want to trial Oracle Key Vault it can be downloaded from the Oracle Software Delivery Cloud at https://edelivery.oracle.com/.

Install MySQL Server

Next you will need to install MySQL 5.7.11, and follow the post-installation steps, which include initializing the data directory.

Load keyring_okv plugin

To use the tablespace encryption feature, you will also need to load the keyring plugin (keyring_okv) by adding the following to your configuration file (under the [mysqld] section, let us assume that your path to the Oracle Key Vault folder is /home/user/okv_enrollment):

[mysqld]
early-plugin-load=keyring_okv.so
keyring_okv_conf_dir=/home/user/okv_enrollment

Before you attempt to create an encrypted table, you can check and make sure the plugin is active via this command:

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS
-> WHERE plugin_name='keyring_okv';
    
+--------------+---------------+
| PLUGIN_NAME  | PLUGIN_STATUS |
+--------------+---------------+
| keyring_okv  | ACTIVE        |
+--------------+---------------+

Creating encrypted tables

To create a table with encryption, you only need to add ENCRYPTION = ‘Y’ to the CREATE TABLE statement. This example will create a new database and an encrypted table:

CREATE DATABASE scripting;
USE scripting;
CREATE TABLE `test_encryption` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';

Technical side note (can be skipped :)
As this is the first encrypted table we are creating - InnoDB will first ask 
keyring_okv to generate master key in Oracle Key Vault. From now on this key will 
be used to encrypt tablespace keys. Next InnoDB will ask Oracle Key Vault to 
generate random key for encrypting table test_encryption. keyring_okv will use 
Oracle Key Vault to ensure that this key will get generated with high entropy. 
This random key will then get encrypted using the master key and stored alongside
the encrypted test_encryption table. The next time encrypted table gets created - 
only the tablespace key will get generated and the master key will get reused 
(to encrypt new tablespace key).

This encryption feature only works with InnoDB tables. If you try this with a MyISAM table, you will get an error:

CREATE TABLE `test_encryption2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `server_name` varchar(15) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 1031 (HY000): Table storage engine for 'test_encryption2' doesn't have this option

You may view a list of all of the encrypted tables via this command:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
-> WHERE CREATE_OPTIONS like 'ENCRYPTION="Y"';
+--------------+-----------------+----------------+
| TABLE_SCHEMA | TABLE_NAME      | CREATE_OPTIONS |
+--------------+-----------------+----------------+
| scripting    | test_encryption | ENCRYPTION="Y" |
+--------------+-----------------+----------------+
1 row in set (0.20 sec)

Encrypting tables with different endpoints

It is possible to use different endpoints with one instance of MySQL. You can start server with (say) endpoint1 and encrypt table test_encryption with it. Then you can restart the server with some other endpoint and use it to encrypt table (for instance) test_more_encryption. However test_encryption will not be accessible. You will see the following error:

mysql> select * from test_encryption; ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

Also please note that, although keyring_okv_conf_dir is settable in runtime, it should be changed with care. Please see Appendix 1, at the bottom of this article, for more information.
Overall using multiple endpoints with one server is not encourage and should be done with extra care.

Encrypting existing tables

To enable encryption for an existing InnoDB table, specify the ENCRYPTION option in an ALTER TABLE statement.

mysql> ALTER TABLE t1 ENCRYPTION='Y';

To disable encryption for an InnoDB table, set ENCRYPTION=’N’ using ALTER TABLE.

mysql> ALTER TABLE t1 ENCRYPTION='N';

One warning – “Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITM=INPLACE is not supported.” (from https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html)

In other words, it may take some time to encrypt (or decrypt) an already-existing InnoDB table.

Rotating InnoDB master key

For security reasons InnoDB master key should be rotated periodically. This operation creates a new master key in Oracle Key Vault, re-encrypts all the tablespace keys with the new master key and deactivates the old master key. In case when rotation gets interrupted by the server crash/restart it will be continued on the next server startup. To start innoDB key rotation use this command:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

For more information go this link (paragraph InnoDB Tablespace Encryption and Master Key Rotation):
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html

Moving encrypted tables between servers

You can also move a copy of an encrypted table to another server, following these steps. There are some limitations you will need to review.

“When exporting a tablespace that is encrypted using the InnoDB tablespace encryption feature, InnoDB generates a .cfp file in addition to a .cfg metadata file. The .cfp file must be copied to the destination server together with the .cfg file and tablespace file before performing the ALTER TABLE … IMPORT TABLESPACE operation on the destination server. The .cfp file contains a transfer key and an encrypted tablespace key. On import, InnoDB uses the transfer key to decrypt the tablespace key. For related information, see Section 14.4.10, InnoDB Tablespace Encryption.” (from: https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html#innodb-transportable-tablespace-limitations

You now know how to use encryption for your InnoDB tables.

Troubleshooting

• In case keyring_okv can not be accessed after restarting mysqld, you will see an error like this in the error log:

[ERROR] Plugin keyring_okv reported: 'Could not connect to the OKV server'
[ERROR] Plugin keyring_okv reported: 'keyring_okv initialization failure. Please check that the keyring_okv_conf_dir points to a readable directory and that the directory contains Oracle Key Vault configuration file and ssl materials. Please also check that Oracle Key Vault is up and running.

As error indicates it seems that Oracle Key Vault server is either down or there is something wrong with the credential directory. Make sure MySQL Server has privileges to access the configuration directory and that keyring_okv_conf_dir variable is set correctly (you can change it in runtime). Check also that structure of the configuration file is correct (see point Get Oracle Key Vault Credentials).

• In case you forget to set keyring_okv_conf_dir variable during server startup or you install the plugin in runtime you will see the following error in the log file:

[Warning] Plugin keyring_okv reported: 'For keyring_okv to be initialized, please point keyring_okv_conf_dir variable to a directory with Oracle Key Vault configuration file and ssl materials

As you can see you have to set keyring_okv_conf_dir variable.

---

Appendix 1. Things to consider when changing keyring_okv_conf_dir in runtime

When server is loaded with keyring_okv – InnoDB fetches Master Key from Oracle Key Vault and uses it to decrypt each tablespace key – then it caches the decrypted tablespace keys. Thus the following code works:

CREATE TABLE t1(c1 INT, c2 char(20)) ENCRYPTION="Y" ENGINE = InnoDB; 
INSERT INTO t1 VALUES(0, "aaaaa"); 
SELECT * FROM t1; 
# Changing endpoint 
SET @@global.keyring_okv_conf_dir= /home/user/okv_enrollment2';
# Table should accessible,keys in memory. 
SELECT * FROM t1;

Although, we have changed the endpoint, table t1 is accessible because its decrypted tablespace key was cached. This seems nice, but you have to be aware that changing endpoint in runtime does not implicitly mean blocking access to tables encrypted with the previously loaded endpoints. Also, we can get into troubles when endpoint we are changing to does not contain master key created by the server instance we are using. Then the following is not possible:

mysql> create table `test_encryption_4` (`id` int(10) unsigned) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 ENCRYPTION = 'Y';
ERROR 3185 (HY000): Can't find master key from keyring, please check keyring plugin is loaded.

This is because InnoDB tries to fetch Master Key from the endpoint and it fails. This is different from starting the server with keyring_okv – then, when InnoDB cannot find Master Key in the keyring it asks keyring to generate a new one.

---

Thanks to Robert Golebiowski for help with this blog.

---

Here are some various links regarding MySQL Enterprise TDE:

https://www.mysql.com/products/enterprise/tde.html
https://dev.mysql.com/doc/refman/5.7/en/faqs-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-okv-plugin.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-tablespace-encryption.html
https://dev.mysql.com/doc/refman/5.7/en/keyring-installation.html
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_keyring_okv_conf_dir
http://www.mysql.com/news-and-events/web-seminars/mysql-security-transparent-data-encryption/

	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.