MySQL InnoDB Cluster – HowTo #1 – Monitor your cluster

April 11, 2019

How do I… Monitor the status & the configuration of my cluster?

Short answer

Use:

status()


Long answer…

Assuming you already have a MySQL InnoDB Cluster up and running. If not, please RTFM 🙂
Additionally you can read this tutorial and this article from my colleague lefred.

I’m using MySQL 8.0.15

Let’s connect to my cluster

The “– – cluster” argument enables cluster management by setting the global variable.
This variable is a reference to the MySQL InnoDB Cluster object session. It will give you access (among others) to the status() method that allows you to check and monitor the cluster.


Ask for help

The built-in help is simply awesome!


Cluster status

So let’s discover the status of our cluster

Note:
The instance’s state in the cluster directly influences the information provided in the status report. Therefore ensure the instance you are connected to has a status of ONLINE.

As you can see, by default status() gives you a lot of relevant information.
Thus it could be used to monitor your cluster although the best tool available to monitor your MySQL InnoDB Cluster (but also MySQL Replication, MySQL NDB Cluster and obviously your standalone MySQL servers) is MySQL Enterprise Monitor.

More details with “A Guide to MySQL Enterprise Monitor“.


Extended cluster status

MySQL Group Replication provides several metrics and detailed information about the underlying cluster in MySQL InnoDB clusters.
These metrics which are used for monitoring are based on these Performance Schema tables.

Some of these information are available through MySQL Shell. You can control the amount of information gathered and returned with 2 options: extended & queryMembers.


extended

if enabled, includes information about groupName and memberID for each member; and general statistics about the number of transactions checked, proposed, rejected by members…


queryMembers

if enabled, includes information about recovery and regular transaction I/O, applier worker thread statistic and any lags; applier coordinator statistic…


To summarize

Q: How do I monitor the status & the configuration of my cluster?

A: Use status() or status({extended:true}) or status({queryMembers:true})


References


Thanks for using MySQL!

Follow me on twitter

2

MySQL JSON Document Store

April 2, 2019

Introduction

MySQL is the most popular Open Source database!
An ACID (acronym standing for Atomicity, Consistency, Isolation, and Durability) compliant relational database that allows you, among others, to manage your data with the powerful and proven SQL, to take care of your data integrity with transactions, foreign keys, …
But you already know that 🙂

JavaScript Objet Notation, better known as JSON, is a lightweight and very popular data-interchange format. Use for storing and exchanging data.
A JSON document is a standardized object that can represent structured data. And the structure is implicit in the document.
Anyway, I bet you know that too!

Started with MySQL 5.7.8, you can handle JSON documents in a “relational way”, using SQL queries and also storing them using the MySQL native JSON data type.
We also provides a large set of JSON functions.
I hope you were aware of that!

You should be interested in:

Note:

I would recommend you to have a closer look at JSON_TABLE function, that extract data from a JSON document and returns it as a relational table… It’s just amazing!

However MySQL 8.0 provides another way to handle JSON documents, actually in a “Not only SQL” (NoSQL) approach…
In other words, if you need/want to manage JSON documents (collections) in a non-relational manner, with CRUD (acronym for Create/Read/Update/Delete) operations then you can use MySQL 8.0!
Did you know that?


MySQL Document Store Architecture

Let’s have a quick overview of the architecture.

MySQL Document Store Architecture

  • X Plugin – The X Plugin enables MySQL to use the X Protocol and uses Connectors and the Shell to act as clients to the server.
  • X Protocol – The X Protocol is a new client protocol based on top of the Protobuf library, and works for both, CRUD and SQL operations.
  • X DevAPI – The X DevAPI is a new, modern, async developer API for CRUD and SQL operations on top of X Protocol. It introduces Collections as new Schema objects. Documents are stored in Collections and have their dedicated CRUD operation set.
  • MySQL Shell – The MySQL Shell is an interactive Javascript, Python, or SQL interface supporting development and administration for the MySQL Server. You can use the MySQL Shell to perform data queries and updates as well as various administration operations.
  • MySQL Connectors – Connectors that support the X Protocol and enable you to use X DevAPI in your chosen language (Node.jsPHPPythonJava.NETC++,…).


Write application using X DevAPI

As a disclaimer, I am not a developer, so sorry no fancy code in this blog post.
However the good news is that I can show you were you’ll be able to found the best MySQL developer resources ever 🙂 that is :

https://insidemysql.com/

And to start, I recommend to focus on the following articles:

And of course the newest articles as well.
Furthermore, another resource that would be useful to you is the

X DevAPI User Guide


Use Document Store with MySQL Shell

If you are a DBA, OPS and obviously a developer, the simplest way to use (or test) MySQL Document Store, is with MySQL Shell.

MySQL Shell is an integrated development & administration shell where all MySQL products will be available through a common scripting interface.
If you don’t know it yet, please download it.
Trust me you are going to love it !

MySQL Shell

MySQL Shell key features are :

  • Scripting for Javascript, Python, and SQL mode
  • Supports MySQL Standard and X Protocols
  • Document and Relational Models
  • CRUD Document and Relational APIs via scripting
  • Traditional Table, JSON, Tab Separated output results formats
  • Both Interactive and Batch operations

Note:

MySQL Shell is also a key component of MySQL InnoDB Cluster. In this context, it allows you to deploy and manager a MySQL Group Replication cluster.

See my MySQL InnoDB Cluster tutorial.

First steps with MySQL Shell

Let’s connect to the MySQL Server with MySQL Shell (mysqlsh)

We must be inside a X session in order to use MySQL as a document store. Luckily there is no extra step, because it’s the default in MySQL 8.0. Note that the default “X” port is 33060.
You can check that you are inside a X session thus using X protocol


If you are connected inside a classic session, you’ll get the following input (note “<ClassicSession instead of <Session”) :

You can know what is you X protocol port by checking mysqlx_port variable.
I’ll switch to the MySQL Shell SQL mode to execute my SQL command:

Then reconnect to the server using the right port (33060 by default) and you should be fine :


CRUD

We are going to create a schema (demo) where we will do our tests

Note:

The MySQL Shell default language is JavaScript. However, all the steps described in this article can also be done in Python.

e.g.

JS> session.createSchema(‘demo’)

Py> session.create_schema(‘demo’)

Create documents

Create a collection (my_coll1) insert the schema demo and insert documents :

Trying to add a non valid JSON document raise an error :

List collections

To get the list of collections belonging to the current schema use getCollections() :

Find documents

Display the content of a collection with find() :

Note:

Each document requires an identifier field called _id. The value of the _id field must be unique among all documents in the same collection.
MySQL server sets an _id value if the document does not contain the _id field.

Please read: Understanding Document IDs.

You can execute many operations on your document. One practical way to get the list of available functions is to press the <TAB> key, to ask for auto-completion, after the dot “.”
For example, type db.my_coll1. then press <TAB>twice, you’ll get the following result:

You can also use the awesome MySQL Shell built-in help (I strongly recommend my colleague Jesper‘s article) and please bookmark is blog.
Last but not least our documentations: X DevAPI User Guide, MySQL Shell JavaSCript API Reference & MySQL Shell Python API reference.

Modify documents

You’ll need the modify() function :

Remove content from documents

You can also modify the structure of a document by remove a key and its content with modify() and unset().

Remove documents

We are missing one last important operation, delete documents with remove()

You can also remove all documents in a collection with one command. To do so, use the remove(“true”) method without specifying any search condition.
Obviously it is usually not a good practice…


Import JSON dcouments

Let’s work with a bigger JSON collection.
MySQL Shell provide a very convenient tool, named importJson(), to easily import JSON documents inside your MySQL Server either in the form of collection or table.

You can find the JSON file source here.
Note that I had to do an extra step before import the data:
sed ‘s/}}},{“id”/}}} {“id”/g’ got_episodes.json.BAK > got_episodes.json

By the way you can import data from MongoDB to MySQL \o/

No more excuses to finally get rid of MongoDB 😉

Let’s do some queries…

Display 1 document

Looks like data relative to a famous TV show 🙂


All episodes from season 1


First episode of each season


CRUD Prepared Statements

A common pattern with document store datastores is to repeatedly execute the same (or similar) kind of simple queries (e.g. “id” based lookup).
These queries can be accelerated using prepared (CRUD) statements.

For example if your application often use the following query:

So it’s probably a good idea to use prepared statements.
First we need to prepare the query:

Then bind the value to the parameter :

Simply powerful!


Index

Indeed relevant indexes is a common practice to improve performances. MySQL Document Store allows you to index your keys inside the JSON document.

Add a composite Index on keys season AND episode.

The required: true option means that it’s mandatory for all documents to contains at least the keys number and season.
E.g.


Add an index on key summary (30 first characters)


Add a Unique Index on key id
Not the one generated by MySQL called _id and already indexed (primary key)

The unique: true option means that values of key id must be unique for each document inside the collection. i.e. no duplicate values.
E.g.

You can obviously drop an index, using dropIndex().
E.g. db.GoT_episodes.dropIndex(“idxSummary”)


Transactions

MySQL Document Store is full ACID, it relies on the proven InnoDB’s strength & robustness.

Yes, you get it right, We do care about your data!

You need the functions below:

Let’s see an example with a multi collection transactions that will be rollback.

Tadam!!!
We back in the past 🙂


Execute (complex) SQL queries

NoSQL + SQL = MySQL

From the MySQL server point of view, collections are tables as well, like regular tables.
And this is very powerful !!!

Powerful because that allow you, within the same datastore (MySQL), to do CRUD queries and SQL queries on the same dataset.
Powerful because that allow you, to have your OLTP CRUD workload and your analytics SQL workload at the same place.
So no need to transfer/sync/… data from 1 datastore to another anymore!!!

You can do SQL queries using sql() functions:

You can also do SQL queries just as you have done until now, using the rich set of MySQL JSON functions.
OK let’s have a closer look.

Remember this CRUD query?

Its SQL query alter ego is :


Let’s do some SQL queries…

Number of episodes by season


Episode statistics for each season


Statistics on the number of days between episodes

Note:

Hey buddy, aren’t Window Functions very cool?

More here and here.


Drop collections

Use dropCollection() :


Conclusion

Wow!
Probably one of my longest article, but I wanted to be sure to give you a large overview of MySQL Document Store (although not exhaustive) from a point of view of a non developer.


Now it is your turn to give it a try 🙂

NoSQL + SQL = MySQL


In order to go further

Some useful link:


Thanks for using MySQL!

Follow me on twitter

4

MySQL Security – MySQL Enterprise Data Masking and De-Identification

March 19, 2019

When thinking about security within a MySQL installation, you should consider a wide range of possible procedures / best practices and how they affect the security of your MySQL server and related applications. MySQL provides many tools / features / plugins in order to protect your data including some advanced features like Transparent Data Encryption aka TDE,  Audit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, etc…

MySQL Security

In order to mitigate the effects of data breaches, and therefore the associated risks for your organization’s brand and reputation, popular regulations or standards including GDPR, PCI DSS, HIPAA,… recommand (among others things) data masking and de-identification.

According to Wikipedia:

  • Data masking or data obfuscation is the process of hiding original data with modified content (characters or other data.)
  • De-identification is the process used to prevent a person’s identity from being connected with information. For example, data produced during human subject research might be de-identified to preserve research participants’ privacy.

In other words, MySQL Enterprise Data Masking and De-Identification hides sensitive information by replacing real values with substitutes in order to protect sensitive data while they are still look real and consistent.

This the topic of this eight episode of this MySQL  Security series (URLs to all the articles at the end of this page).


MySQL Enterprise Data Masking and De-Identification

The simplest way to present this MySQL feature :
A built-in database solution to help organizations protect sensitive data from unauthorized uses

MySQL Enterprise Masking and De-identificaiton protects sensitive data from unauthorized users.

Note:

MySQL Enterprise Data Masking and De-Identification is an extension included in MySQL Enterprise Edition, a commercial product.

Available in MySQL 8.0, as of 8.0.13 and in MySQL 5.7, as of 5.7.24.

First step, installation.


Installation

MySQL Enterprise Data Masking and De-Identification, is implemented as a plugin library file containing a plugin and user-defined functions (UDFs).
As usual install is easy:

You can check the activation of the data masking plugin:

Note:

If the plugin and UDFs are used on a master replication server, install them on all slave servers as well to avoid replication problems.

Uninstall is simple as well, uninstall the plugin and drop the UDFs:

Now we’re ready to play!

Data Generation

One of the nice “side feature” of MySQL Data Masking and De-Identification is the ability to generate business relevant datasets. Because it is not always possible to test/simulate your application on your real dataset (indeed playing with customer credit card or security social numbers is a very bad practice) this feature is very convenient.

Generating Random Data with Specific Characteristics

Several functions are available. They start with these 4 first characters: gen_ and you’ll find the complete list here.
In this article I’ll use the following functions :

  • gen_range() : returns a random integer selected from a given range.
  • gen_rnd_email() : returns a random email address in the example.com domain.
  • gen_rnd_pan() : returns a random payment card Primary Account Number.
  • gen_rnd_us_phone() : returns a random U.S. phone number in the 555 area code not used for legitimate numbers.


Generating Random Data Using Dictionaries

Sometime you will need data with better quality. So another way to generate a relevant dataset is to use dictionaries.

Again several functions are available. They also start with these 4 first characters: gen_ and you’ll find the complete list here.
I’ll use the following functions :

  • gen_dictionary_load() : Loads a file into the dictionary registry and assigns the dictionary a name to be used with other functions that require a dictionary name argument.
  • gen_dictionary() : Returns a random term from a dictionary.

OK, let’s moving forward!
In order to use data from a dictionary we must first load the data.

A dictionary is a plain text file, with one term per line:

Then we must load the dictionaries

Note:

The secure_file_priv variable must be set properly (usually in your my.cnf or my.ini).

Note:

Dictionaries are not persistent. Any dictionary used by applications must be loaded for each server startup.

Now I have all my bricks to build my business centric test dataset.
For example I can generate a random email address:

Or a random city from my dictionary of the cities of Martinique :

Awesome!

Now let’s use these functions to generate some random but business oriented data.
Below our test table called sensitive_data which contains… sensitive data :

I created a stored procedure (sorry but I’m a DBA) to fill my table with data. However a script in your favorite programming language could be a better choice:

It looks like real data, it smells like real data, it sounds like real data but these are not real data. That’s what we wanted 🙂


Data Masking and De-Identification

Many masking functions are available. They start with these 5 first characters: mask_ and you’ll find the complete list here.
I’ll use the following functions :

mask_inner() masks the interior of its string argument, leaving the ends unmasked. Other arguments specify the sizes of the unmasked ends.

mask_outer() does the reverse, masking the ends of its string argument, leaving the interior unmasked. Other arguments specify the sizes of the masked ends.

mask_pan() masks all but the last four digits of the number;
mask_pan_relaxed() is similar but does not mask the first six digits that indicate the payment card issuer unmasked.

Note:

If you deal with U.S. Social Security Numbers, you could also use mask_ssn() function.

e.g. mysql> SELECT mask_ssn(gen_rnd_ssn());

So how to masked and de-identified customer sensitive data ?


There are different strategies. One is to use views.
Thus you already have a first level of security because you can choose only the columns the business need and/or filter the rows.
Furthermore you have another level of security because you can control who can access these data with relevant privileges, with or without roles.

Let’s see some examples:

Ex. 1
Mask the firstname (firstname) & the lastname (lastname)

Ex. 2
Mask the credit card number (credit_card)

Ex. 3
Replace real values of employee id (emp_id) and birth date (birth_date) with random ones.

Et voilà!
As a conclusion, MySQL Enterprise Masking and De-Identification enables organization to:

  • Meet regulatory requirements and data privacy laws
  • Significantly reduce the risk of a data breach
  • Protect confidential information

To conclude this conclusion, I recommend to read Data Masking in MySQL blog post from the MySQL Server Blog.


MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.

MySQL Enterprise Edition server Trial Download (Note – Select Product Pack: MySQL Database).

MySQL Enterprise Edition

In order to go further

MySQL Security Series

  1. Password Validation Plugin
  2. Password Management
  3. User Account Locking
  4. The Connection-Control Plugins
  5. Enterprise Audit
  6. Enterprise Transparent Data Encryption (TDE)
  7. Enterprise Firewall
  8. Enterprise Data Masking and De-Identification

Reference Manual

MySQL Security

Blog posts


Thanks for using MySQL!

Follow me on twitter

8

MySQL Functional Indexes

March 14, 2019
Sunset in Crete by Olivier DASINI

Since MySQL 5.7 one can put indexes on expressions, aka functional indexes, using generated columns. Basically you first need to use the generated column to define the functional expression, then indexed this column.

Quite useful when dealing with JSON functions, you can find an example here and the documentation there.

Starting with MySQL 8.0.13 we have now an easiest way to create functional indexes (or functional key parts as mentioned in the documentation) \o/

Let’s see how with a quick practical example.

Below salaries table structure:

It contains some data

Let’s focus on the following query:
SELECT * FROM salaries WHERE YEAR(to_date)=1985

We have a full table scan ( type: ALL), meaning no index is used. Perhaps because there is no index on column to_date… 😉
So let’s add an index on to_date !

And run again the query with the hope of a better execution plan

Ouch! Still have a full table scan !
The index can’t be used because of the use of a function (YEAR()) on the indexed column (to_date).
BTW if you’re really surprise, maybe you should read this. 😉

This is the case when you need a functional index!

The syntax is very similar of the creation of a “regular” index. Although you must be aware of the double parentheses: (( <expression> ))
We can now see our new index named idx_year_to_date and the indexed expression year(to_date) :

Let’s test our query again

Here we go!
Now the query is able to use the index. And in this case we have a positive impact on the execution time.

It is also interesting to note that it is possible to use idx_to_date, the first index created (the non functional one) if we can rewrite the original query:

This saves an index, I mean less indexes to maintain for the engine. Also speaking of maintenance cost, the cost to maintain a functional index is higher than the cost of a regular one.

In the other side the execution plan is less good (query cost higher) and obviously you must rewrite the query.

Requirements and restrictions.

A primary key cannot be a functional index:

You can not index non-deterministic functions (RAND(), UNIX_TIMESTAMP(), NOW()…)

SPATIAL and FULLTEXT indexes cannot have functional key parts.

Conclusion

Functional index is an interesting and a relevant feature, it could be very useful to optimize your queries without rewrite them and especially when dealing with JSON documents and other complex types.

Obviously all the details you must know are in the MySQL documentation: Functional Key Parts
If you interested in the high level architecture and the low level design please read the workload.



Thanks for using MySQL!

Follow me on twitter

0

30 mins with MySQL JSON functions

July 23, 2018
Tags: , ,

Lire cet article en français

Note: You may also be interested by 30 mins with JSON in MySQL

Note 2: Handling JSON documents could be also done with MySQL Document Store.

JSON (JavaScript Object Notation) is a popular way for moving data between various systems, including databases.  Starting with 5.7 MySQL supports a native JSON data type (internal binary format for efficiency) and a set of built-in JSON functions that allows you to perform operations on JSON documents.

This blog post is not a complete overview of the entire MySQL JSON functions set (RTFM instead) but rather an arbitrary presentation of some of them.

Note: MySQL 8 enables an alternative way of working with MySQL as a document store. (Not cover in this blog post).

I’m using MySQL 8.0.11, downloadable here.

MySQL native JSON data type

JSON Utility Functions

JSON_PRETTY

Improve readability with JSON_PRETTY

By default, display a JSON document in MySQL looks like something like this :

You can have a prettier display with JSON_PRETTY :

JSON_STORAGE_SIZE

Return the number of bytes used to store the binary representation of a JSON document with JSON_STORAGE_SIZE.

In this collection, the heavier document is 916 bytes, the lighter is 255 and the average size is 537.2814

Note: This is the space used to store the JSON document as it was inserted into the column, prior to any partial updates that may have been performed on it afterwards.

Functions That Search JSON Values

JSON_EXTRACT (->) / JSON_UNQUOTE / ->> operator

JSON_EXTRACT (or ->) returns data from a JSON document.

JSON_UNQUOTE unquotes JSON value and returns the result as a utf8mb4 string.

->> the JSON unquoting extraction operator is a shortcut for JSON_UNQUOTE(JSON_EXTRACT())

Both queries above are similar.

If you want the same result but without quotes use ->> or JSON_UNQUOTE(JSON_EXTRACT()) :

Both queries above are similar.

JSON_CONTAINS

Search whether the value of specified key matches a specified value with JSON_CONTAINS.

JSON_CONTAINS_PATH

Indicate whether a JSON document contains data at a given path or paths with JSON_CONTAINS_PATH.

Let’s insert a dummy document in the collection restaurants

How many documents without grades? :