dasini.net – Diary of a MySQL expert

In the old days, when we wanted to strengthen our skills the only option was to buy a good book (this one litteraly changed my life, but also this one, this one, …). Nowadays one can find a lot of resources on the Internet, however quality is often poor :(.

Fortunately there are still some great people who are brave enough to write new books that will hopefully have an impact on the new generation of women and men (and the current generation as well) who build modern applications with MySQL, the world’s most popular open source database.

 

Let me introduce you 3 MySQL books :

 

MySQL and JSON: A Practical Programming Guide
Discover how to use JavaScript Object Notation (JSON) with MySQL

Author : David Stokes   (MySQL Community Manager)

• ISBN-13 : 978-1260135442
• ISBN-10 : 1260135446

Publisher : Oracle Press

https://www.mhprofessional.com/9781260135442-usa-mysql-and-json-a-practical-programming-guide-group

 

 

---

Introducing the MySQL 8 Document Store
Building schemaless database solutions

Author : Dr. Charles Bell (Development Manager)

• eBook ISBN       : 978-1-4842-2725-1
• Softcover ISBN : 978-1-4842-2724-4
• DOI                       : 10.1007/978-1-4842-2725-1

Publisher : Apress

https://www.apress.com/fr/book/9781484227244

 

 

---

Pro MySQL NDB Cluster
Master the MySQL Cluster Lifecycle

Authors : Jesper Wisborg Krogh(MySQL Senior Principal Technical Support Engineer), Mikiya Okuno(MySQL Technical Analyst)

• eBook ISBN       : 978-1-4842-2982-8
• Softcover ISBN : 978-1-4842-2981-1
• DOI                       : 10.1007/978-1-4842-2982-8

Publisher : Apress

https://www.apress.com/gp/book/9781484229811

 

 

 

I’ll finish this post with a Groucho Marx quotes :

“Outside of a dog, a book is man’s best friend. Inside of a dog it’s too dark to read.”

 

 

Updated 2018/08/06

I also recommend my colleague Jesper‘s blog post: MySQL Books.

 

Updated 2018/10/30

I also recommend my other colleague Dave Stokes‘s blog post: Two More MySQL Books for 2018.

 

 

 

Thanks for using (and reading) MySQL!

 

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 Audit, TDE, Data Masking & De-Identification, Password Management, Password Validation Plugin, User Account Locking, etc…

In this seventh episode of the MySQL Security series, we will see how MySQL Enterprise Firewall can help you to strengthen the protection of your data, in real-time, against cyber security threats including SQL Injection attacks by monitoring, alerting, and blocking unauthorized database activity without any changes to your applications.

Installing the MySQL Enterprise Firewall Plugin

MySQL Enterprise Firewall installation is an easy one-time operation that involves running a script (e.g. linux_install_firewall.sql in this blog post (Linux and similar systems that use .so as the file name suffix); win_install_firewall.sql for Windows systems that use .dll as the file name suffix) located in the share directory of your MySQL installation.

I’m using MySQL 5.7.21 Enterprise Edition :

mysql> 
SELECT version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.7.21-enterprise-commercial-advanced-log |
+-------------------------------------------+

Updated on 22nd of August 2018
Note: MySQL Enterprise Firewall works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

MySQL Enterprise Firewall does not work together with the MySQL Query Cache. Fortunately the query cache is disabled by default.

mysql> 
SHOW VARIABLES LIKE 'query_cache_type';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

Note
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Note
For a great query cache tuning advice from Domas Mituzas : click here 🙂

Now we can installed the Firewall

mysql> 
SOURCE /usr/share/mysql/linux_install_firewall.sql

Database changed
Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.06 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

And check if it has been launched with the system variable mysql_firewall_mode :

mysql> 
SHOW GLOBAL VARIABLES LIKE 'mysql_firewall_mode';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| mysql_firewall_mode | ON    |
+---------------------+-------+

Alternatively, we can also add mysql_firewall_mode under the [mysqld] option group in the MySQL configuration file :

[mysqld]
mysql_firewall_mode=ON

It is also possible to disable or enable the firewall at runtime :

mysql> 
SET GLOBAL mysql_firewall_mode = OFF;

SET GLOBAL mysql_firewall_mode = ON;

Playtime

The MySQL Firewall is installed!

Let’s assume now we have an application that uses the schema sakila in this instance. This application has a dedicated user account (myApp@localhost) and all the privileges on sakila :

mysql> 
CREATE USER 'myApp'@'localhost' IDENTIFIED BY 'AppU$3rPwd';

GRANT ALL ON sakila.* TO 'myApp'@'localhost';

Note

The firewall maintains whitelist rules on a per-account basis.

Regular queries from this hypothetical application are :

• UPDATE rental SET return_date = <date> WHERE rental_id = <id>;
• SELECT get_customer_balance(<id>, <date>);

But first, users are authenticated with :

• SELECT staff_id, first_name, email, last_name, username, password FROM staff WHERE username = ‘<userName>’ AND password=sha1(<userPassword>);

Warning
Query above is not safe nor optimal for production.

Firewall allows 3 modes :

• recording, the firewall adds the normalized statement to the account whitelist rules.
• protecting, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. The firewall also writes the rejected statement to the error log if the mysql_firewall_trace system variable is enabled.
• detecting, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

Recording mode

Ok now we know our queries, let’s go back to the Firewall.

The basic and powerful idea of the MySQL Firewall is to deny SQL statement execution based on matching against a whitelist. In other words the Firewall learns acceptable statement patterns.

In order to create this whitelist, we’ll switch the Firewall in the RECORDING mode using sp_set_firewall_mode stored procedure :

mysql> 
-- register the account with the firewall and place it in recording mode
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'RECORDING');

+-----------------------------------------------+
| read_firewall_whitelist(arg_userhost,FW.rule) |
+-----------------------------------------------+
| Imported users: 0
Imported rules: 0
          |
+-----------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

We can know see what is the status of the Firewall for any user with INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table :

mysql> 
-- Check firewall status
SELECT MODE 
FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS 
WHERE USERHOST = 'myApp@localhost';
+-----------+
| MODE      |
+-----------+
| RECORDING |
+-----------+

During the recording mode, we can run the application. The queries generated by the application will be recorded in the Firewall’s whitelist :

# Login failed (Mike / 0000)
mysql_myApp> 
SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(0000)\G
Empty set (0.00 sec)


# Login succeed (Mike / 12345)
SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(12345)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Other queries…

mysql_myApp>
UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

SELECT get_customer_balance(1, NOW());
+--------------------------------+
| get_customer_balance(1, NOW()) |
+--------------------------------+
|                           0.00 |
+--------------------------------+

And so on…

When the training is done switch the Firewall to protecting mode.

Protecting mode

Use the sp_set_firewall_mode stored procedure to switch the registered user to protecting mode:

mysql> 
-- Switch the Firewall in protecting mode
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'PROTECTING');
Query OK, 3 rows affected (0.00 sec)


-- Check
SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;
+------------------+------------+
| USERHOST         | MODE       |
+------------------+------------+
| myApp@localhost  | PROTECTING |
+------------------+------------+

Firewall stores SQL statements on a normalized digest form. You can check the whitelist with INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table :

mysql>
-- Check whitelist content 
SELECT USERHOST, RULE 
FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST 
WHERE USERHOST = 'myApp@localhost';

+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| USERHOST        | RULE                                                                                                                                                     |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| myApp@localhost | SELECT `get_customer_balance` ( ? , NOW ( ) )                                                                                                            |
| myApp@localhost | UPDATE `rental` SET `return_date` = NOW ( ) WHERE `rental_id` = ?                                                                                        |
| myApp@localhost | SELECT `staff_id` , `first_name` , `email` , `last_name` , `username` , PASSWORD FROM `sakila` . `staff` WHERE `username` = ? AND PASSWORD = `sha1` (?)  |
+-----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Note
For additional training you can switch back recording mode or even update (that is an UPDATE query) this table if necessary using the normalize_statement UDF.

In protecting mode, there are 2 kind of queries for the application point of view :

• Acceptable

mysql_app> 
UPDATE rental SET return_date = NOW() WHERE rental_id = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


UPDATE rental SET return_date = NOW() WHERE rental_id = 42;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1  Changed: 1  Warnings: 0


SELECT get_customer_balance(5, NOW());
+--------------------------------+
| get_customer_balance(5, NOW()) |
+--------------------------------+
|                           0.00 |
+--------------------------------+
1 row in set (0.01 sec)

• Unacceptable

mysql_app>
DROP TABLE rental;
ERROR 1045 (28000): Statement was blocked by Firewall


UPDATE rental SET return_date = NOW();
ERROR 1045 (28000): Statement was blocked by Firewall

SQL injection

One of the big advantage of the MySQL Firewall is that it can help protect against SQL Injection attacks. In this post, I will not go into details of what is an SQL injection. However below a simplistic example to illustrate the overall principle.

User name and password are needed for authentication :

mysql_app> 
-- user = Mike : OK
-- password = 12345 : OK
-- Login OK

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND password=sha1(12345)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Low quality code can generate unsafe queries :

mysql>
-- user = Mike' AND '1'='1'\G --  : Not OK
-- password = <whatever>
-- SQL injection /!\

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND '1'='1'\G --   AND password=sha1(<whatever>)\G
*************************** 1. row ***************************
  staff_id: 1
first_name: Mike
     email: Mike.Hillyer@sakilastaff.com
 last_name: Hillyer
  username: Mike
  password: 8cb2237d0679ca88db6464eac60da96345513964

Fortunately they are blocked by the MySQL Firewall :

mysql_app>
-- user = Mike' AND '1'='1'\G --  : Not OK
-- password = <whatever>
-- Blocked by Firewall because not matching whitelist

SELECT staff_id, first_name, email, last_name, username, password 
FROM sakila.staff 
WHERE username = 'Mike' AND '1'='1'\G --   AND password=sha1(12345)\G

ERROR 1045 (28000): Statement was blocked by Firewall

Rejected queries can be seen in the MySQL error log if the mysql_firewall_trace system variable is enabled.

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


SHOW VARIABLES LIKE 'mysql_firewall_trace';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| mysql_firewall_trace | ON    |
+----------------------+-------+

Unacceptable queries :

mysql_app> 
SELECT * FROM sakila.staff;
ERROR 1045 (28000): Statement was blocked by Firewall

DELETE FROM rental;
ERROR 1045 (28000): Statement was blocked by Firewall

TRUNCATE mysql.user;
ERROR 1045 (28000): Statement was blocked by Firewall

are available in the MySQL error log :

mysql> 
system tail -n3 /var/log/mysqld.log

2018-04-16T08:46:09.353950Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: SELECT * FROM `sakila` . `staff` '
2018-04-16T08:46:09.354198Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: DELETE FROM `rental` '
2018-04-16T08:46:09.354403Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'ACCESS DENIED for 'myApp@localhost'. Reason: No match in whitelist. Statement: TRUNCATE `mysql` . `user`

Detecting mode

MySQL Enterprise Firewall can also be used into a intrusion-detecting mode that writes suspicious statements to the error log but does not deny access.

mysql> 
CALL mysql.sp_set_firewall_mode('myApp@localhost', 'DETECTING');
Query OK, 3 rows affected (0.06 sec)


SELECT * FROM INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;
+-----------------+-----------+
| USERHOST        | MODE      |
+-----------------+-----------+
| myApp@localhost | DETECTING |
+-----------------+-----------+

Now using the application user account, suspicious queries will not be blocked :

mysql_app> 
-- Suspicious query
SHOW TABLES LIKE 'customer%';
+------------------------------+
| Tables_in_sakila (customer%) |
+------------------------------+
| customer                     |
| customer_list                |
+------------------------------+
2 rows in set (0.00 sec)

however a message is written into the MySQL error log :

mysql> 
system tail -n1 /var/log/mysqld.log
2018-04-16T09:01:47.133398Z 1966 [Note] Plugin MYSQL_FIREWALL reported: 'SUSPICIOUS STATEMENT from 'myApp@localhost'. Reason: No match in whitelist. Statement: SHOW TABLES LIKE ? '

Monitor the Firewall

MySQL Enterprise Firewall provides the following status variables :

• Firewall_access_denied : The number of statements rejected by MySQL Enterprise Firewall.
• Firewall_access_granted : The number of statements accepted by MySQL Enterprise Firewall.
• Firewall_access_suspicious : The number of statements logged by MySQL Enterprise Firewall as suspicious for users who are in DETECTING mode.
• Firewall_cached_entries : The number of statements recorded by MySQL Enterprise Firewall, including duplicates.

mysql> 
SHOW GLOBAL STATUS LIKE 'Firewall%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Firewall_access_denied     | 9     |
| Firewall_access_granted    | 8     |
| Firewall_access_suspicious | 1     |
| Firewall_cached_entries    | 3     |
+----------------------------+-------+

Uninstall the Firewall

To remove MySQL Enterprise Firewall, execute the following statements :

mysql> 
USE mysql;

DROP TABLE mysql.firewall_whitelist;
DROP TABLE mysql.firewall_users;
UNINSTALL PLUGIN mysql_firewall;
UNINSTALL PLUGIN mysql_firewall_whitelist;
UNINSTALL PLUGIN mysql_firewall_users;
DROP FUNCTION set_firewall_mode;
DROP FUNCTION normalize_statement;
DROP FUNCTION read_firewall_whitelist;
DROP FUNCTION read_firewall_users;
DROP FUNCTION mysql_firewall_flush_status;
DROP PROCEDURE mysql.sp_set_firewall_mode;
DROP PROCEDURE mysql.sp_reload_firewall_rules;

Note
You may have to kill the application remaining connections (e.g. KILL CONNECTION) or reconnect the application user (e.g. mysql> connect)

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

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 Enterprise Firewall High lights
• Installing the MySQL Enterprise Firewall Plugin
• MySQL Enterprise Firewall

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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  Audit, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, User Account Locking, etc…

In this sixth episode of the MySQL Security series, we will see how data-at-rest encryption helps organizations implement stronger security controls and satisfy regulatory compliance.

You will be able to protect the privacy of your information, prevent data breaches and help meet popular regulatory requirements including GDPR, PCI DSS, HIPAA with MySQL Enterprise Transparent Data Encryption aka TDE.

MySQL Keyring

MySQL Enterprise Transparent Data Encryption relies on a MySQL Keyring plugin for master encryption key management. MySQL Server provides many plugin choices including :

• keyring_file : that stores keyring data in a file local to the server host. Available in all MySQL distributions.
• keyring_encrypted_file : that stores keyring data in an encrypted file local to the server host. Available in MySQL Enterprise Edition.
• keyring_okv : a KMIP 1.1 plugin for use with KMIP-compatible backend keyring storage products such as Oracle Key Vault and Gemalto SafeNet KeySecure Appliance. Available in MySQL Enterprise Edition.
• keyring_aws : that communicates with the Amazon Web Services Key Management Service for key generation and uses a local file for key storage. Available in MySQL Enterprise Edition.

In this article I’ll use the keyring_encrypted_file plugin with MySQL Enterprise Edition 5.7.21.

Updated on 22nd of August 2018
Note: MySQL Enterprise Transparent Data Encryption works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

Warning
The keyring_file and keyring_encrypted_file plugins for encryption key management are not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

These keyring plugins would be use with MySQL Enterprise Audit to store the audit log file encryption password (you can read my previous article) and InnoDB tables when encrypted with  MySQL Enterprise Transparent Data Encryption, actually the topic of this article but you already know that, right? :).

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available and I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

Only one keyring plugin should be enabled at a time. So let’s check that we have none so far :

mysql>
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
Empty set (0.01 sec)

To be usable by the server, the plugin library file must be located in the MySQL plugin directory :

mysql>
SHOW VARIABLES LIKE 'plugin_dir';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+
| plugin_dir    | /var/lib/plugin/ |
+---------------+------------------+


system ls  /var/lib/plugin/ | grep keyring
keyring_encrypted_file.so
keyring_file.so
keyring_okv.so
keyring_udf.so

Update the MySQL configuration file and restart the MySQL server :

[mysqld]
early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/var/lib/mysql-keyring/keyring-encrypted
keyring_encrypted_file_password=V&rySec4eT

• To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
• To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
• The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

mysql> 
SELECT PLUGIN_NAME, PLUGIN_TYPE, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
*************************** 1. row ***************************
       PLUGIN_NAME: keyring_encrypted_file
       PLUGIN_TYPE: KEYRING
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: keyring_encrypted_file.so
PLUGIN_DESCRIPTION: store/fetch authentication data to/from an encrypted file

We can have a look at the Keyring file. Its location could be found with keyring_file_data variable :

mysql>
SHOW VARIABLES LIKE 'keyring_encrypted_file_data'\G
*************************** 1. row ***************************
Variable_name: keyring_encrypted_file_data
        Value: /var/lib/mysql-keyring/keyring-encrypted


system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 0 Apr 5 16:36 /var/lib/mysql-keyring/keyring-encrypted

After a keyring plugin is configured, InnoDB tables may be encrypted.

InnoDB Transparent Encryption

InnoDB supports data encryption for InnoDB tables stored in file-per-table tablespaces.

I’m using Sakila Database, available here.

Below a couple of random queries :

mysql> 
USE sakila;

SELECT rental_id
FROM rental
WHERE inventory_id = 3043
    AND customer_id = 53
    AND return_date IS NULL;
+-----------+
| rental_id |
+-----------+
|     11657 |
+-----------+
1 row in set (0,00 sec)


-- Find Overdue DVDs
SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title
FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
  INNER JOIN address ON customer.address_id = address.address_id
  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
  INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL
  AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
  LIMIT 5;
+------------------+--------------+------------------------+
| customer         | phone        | title                  |
+------------------+--------------+------------------------+
| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |
| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |
| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |
| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |
| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |
+------------------+--------------+------------------------+
1 row in set (0,01 sec)

Data-at-Rest Encryption

Usually it is not necessary to encrypt all the tables. After a deep data analysis let’s assume 2 relevant InnoDB tables must be encrypted : customer and staff.

So let’s encrypted these tables!

We can do it easily with a CREATE or ALTER TABLE statement using ENCRYPTION=”Y” :

mysql> 
ALTER TABLE sakila.customer ENCRYPTION="Y";
Query OK, 599 rows affected (0,19 sec)
Records: 599  Duplicates: 0  Warnings: 0


ALTER TABLE sakila.staff ENCRYPTION="Y";
Query OK, 2 rows affected (0,15 sec)
Records: 2  Duplicates: 0  Warnings: 0

There we go!

Note
Plan appropriately when altering an existing table with the ENCRYPTION option. ALTER TABLE … ENCRYPTION operations rebuild the table using ALGORITHM=COPY. ALGORITHM=INPLACE is not supported.

Identify Tables that Use InnoDB Tablespace Encryption

This information is available in INFORMATION_SCHEMA.TABLES :

mysql>
SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS 
FROM INFORMATION_SCHEMA.TABLES 
WHERE CREATE_OPTIONS = 'ENCRYPTION="Y"';
+--------------+------------+----------------+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS |
+--------------+------------+----------------+
| sakila       | customer   | ENCRYPTION="Y" |
| sakila       | staff      | ENCRYPTION="Y" |
+--------------+------------+----------------+

Query encrypted Tables

Transparent Data Encryption means transparent for the clients ie no change in your applications.

In other words, you don’t have to modify your queries and sames queries with same datasets will give me the same result :

mysql> 
USE sakila;

SELECT rental_id
FROM rental
WHERE inventory_id = 3043
    AND customer_id = 53
    AND return_date IS NULL;
+-----------+
| rental_id |
+-----------+
|     11657 |
+-----------+
1 row in set (0,00 sec)


-- Find Overdue DVDs
SELECT CONCAT(customer.last_name, ', ', customer.first_name) AS customer, address.phone, film.title
FROM rental INNER JOIN customer ON rental.customer_id = customer.customer_id
  INNER JOIN address ON customer.address_id = address.address_id
  INNER JOIN inventory ON rental.inventory_id = inventory.inventory_id
  INNER JOIN film ON inventory.film_id = film.film_id
WHERE rental.return_date IS NULL
  AND rental_date + INTERVAL film.rental_duration DAY < CURRENT_DATE()
  LIMIT 5;
+------------------+--------------+------------------------+
| customer         | phone        | title                  |
+------------------+--------------+------------------------+
| KNIGHT, GAIL     | 904253967161 | HYDE DOCTOR            |
| MAULDIN, GREGORY | 80303246192  | HUNGER ROOF            |
| JENKINS, LOUISE  | 800716535041 | FRISCO FORREST         |
| HOWELL, WILLIE   | 991802825778 | TITANS JERK            |
| DIAZ, EMILY      | 333339908719 | CONNECTION MICROCOSMOS |
+------------------+--------------+------------------------+
1 row in set (0,01 sec)

Master Key Rotation

Legal and regulatory requirements require a periodically rotation of the master encryption key. It’s also more than recommended to rotate the master key whenever you suspect a security breach. Rotating the master encryption key only changes the master encryption key and re-encrypts tablespace keys.

To rotate the master encryption key use ALTER INSTANCE ROTATE INNODB MASTER KEY :

mysql> 
system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 197 Apr  5 16:44 /var/lib/mysql-keyring/keyring-encrypted


ALTER INSTANCE ROTATE INNODB MASTER KEY;
Query OK, 0 rows affected (0,01 sec)


system ls -lh /var/lib/mysql-keyring/keyring-encrypted
... 325 Apr  5 17:18 /var/lib/mysql-keyring/keyring-encrypted

Et voilà!

Now you know the MySQL Enterprise Transparent Encryption basics \o/

I would like to address a last item, regarding a common customer question about the disk footprint of an encrypted table.

So let’s play a little bit more with TDE.

We have a table, encryptedTable, not yet encrypted with 5 millions rows

Table size is 722 MB

mysql> 
SHOW TABLE STATUS LIKE 'encryptedTable'\G
*************************** 1. row ***************************
           Name: encryptedTable
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 4986035
 Avg_row_length: 61
    Data_length: 304922624
Max_data_length: 0
   Index_length: 449921024
      Data_free: 2097152
 Auto_increment: NULL
    Create_time: 2018-04-10 12:02:35
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 


-- Rows = 5 million
SELECT count(*) FROM encryptedTable;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+


-- Table size = 722MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 290.7969MB
  INDEXES: 429.0781MB
DATA_FREE: 2.0000MB
    TOTAL: 721.8750MB

Table is now encrypted

ALTER TABLE encryptedTable ENCRYPTION="Y";
Query OK, 5000000 rows affected (6 min 42,58 sec)
Records: 5000000  Duplicates: 0  Warnings: 0

Table size is now : 870 MB

-- Table size = 870MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 249.7813MB
  INDEXES: 614.6563MB
DATA_FREE: 6.0000MB
    TOTAL: 870.4375MB

Is there a way to reclaim space (and optionally improve I/O efficiency)?  Yes of course, OPTIMIZE TABLE :

OPTIMIZE TABLE test.encrypTable\G
*************************** 1. row ***************************
   Table: test.encrypTable
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2. row ***************************
   Table: test.encrypTable
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (1 min 32,52 sec)

Table size is again 722 MB

-- Table size = 722MB
SELECT   
    CONCAT(sum(ROUND(data_length / ( 1024 * 1024 ), 4)), 'MB') DATA,   
    CONCAT(sum(ROUND(index_length / ( 1024 * 1024 ),4)), 'MB') INDEXES, 
    CONCAT(sum(ROUND(data_free / ( 1024 * 1024 ), 4)), 'MB') DATA_FREE, 
    CONCAT(sum(ROUND(( data_length + index_length+data_free) / ( 1024 * 1024 ), 4)), 'MB') 'TOTAL' 
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'test'
    AND TABLE_NAME = ('encryptedTable')\G
*************************** 1. row ***************************
     DATA: 290.7969MB
  INDEXES: 429.0781MB
DATA_FREE: 2.0000MB
    TOTAL: 721.8750MB

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

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

• The MySQL Keyring
• Encrypting InnoDB Tables
• Presentation of MySQL Enterprise Transparent Data Encryption (TDE)
• MySQL : InnoDB Transparent Tablespace Encryption

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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, Data Masking & De-Identification, Firewall, Password Management, Password Validation Plugin, etc…

In order to spot database misuse and/or to prove compliance to popular regulations including GDPR, PCI DSS, HIPAA, … database administrators can be required to record and audit database activities. In this fifth episode of the MySQL  Security series, we will see what MySQL Enterprise Audit provides to help organizations implement stronger security controls and satisfy regulatory compliance.

MySQL Enterprise Audit

MySQL Enterprise Edition includes MySQL Enterprise Audit which uses the open MySQL Audit API to enable standard, policy-based monitoring, logging, and blocking of connection and query activity executed on specific MySQL servers.

Audit plugin enables MySQL Server to produce a XML (default) or JSON log file (named audit.log) containing an audit record of server activity. The log contents include when clients connect and disconnect, and what actions they perform while connected, such as which databases and tables they access.

Installation

I’m using MySQL 5.7.21 Enterprise Edition :

SELECT VERSION();
+-------------------------------------------+
| VERSION()                                 |
+-------------------------------------------+
| 5.7.21-enterprise-commercial-advanced-log |
+-------------------------------------------+

Updated on 22nd of August 2018
Note: MySQL Enterprise Audit works with MySQL 8.0 as well. In other words examples below could be done with MySQL 8.0.12+

To install MySQL Enterprise Audit, look in the share directory of your MySQL installation and choose the script that is appropriate for your platform :

– audit_log_filter_win_install.sql : Choose this script for Windows systems that use .dll as the file name suffix.

– audit_log_filter_linux_install.sql : Choose this script for Linux and similar systems that use .so as the file name suffix.

Then run the script :

e.g.

$ mysql -u root -p < audit_log_filter_linux_install.sql

You can verify the plugin installation examining the INFORMATION_SCHEMA.PLUGINS table or use the SHOW PLUGINS statement

mysql> 
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'audit%';
+-------------+---------------+
| PLUGIN_NAME | PLUGIN_STATUS |
+-------------+---------------+
| audit_log   | ACTIVE        |
+-------------+---------------+

mysql> 
SHOW PLUGINS\G
... [snip] ...
*************************** 45. row ***************************
   Name: audit_log
 Status: ACTIVE
   Type: AUDIT
Library: audit_log.so
License: PROPRIETARY

The Audit plugin installed a bunch of User-Defined Function (UDF) :

mysql> 
-- Display all the UDFs installed by the Audit plugin
SELECT * FROM  mysql.func;
+-----------------------------------+-----+--------------+----------+
| name                              | ret | dl           | type     |
+-----------------------------------+-----+--------------+----------+
| audit_log_filter_set_filter       |   0 | audit_log.so | function |
| audit_log_filter_remove_filter    |   0 | audit_log.so | function |
| audit_log_filter_set_user         |   0 | audit_log.so | function |
| audit_log_filter_remove_user      |   0 | audit_log.so | function |
| audit_log_filter_flush            |   0 | audit_log.so | function |
| audit_log_read_bookmark           |   0 | audit_log.so | function |
| audit_log_read                    |   0 | audit_log.so | function |
| audit_log_encryption_password_set |   2 | audit_log.so | function |
| audit_log_encryption_password_get |   0 | audit_log.so | function |
+-----------------------------------+-----+--------------+----------+

These functions are described here.

The Audit plugin enables MySQL Server to produce a log file based in the datadir and named audit.log. By default, its contents is written in XML format, without compression nor encryption :

mysql> 
SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

$ cat /var/lib/mysql/audit.log
<?xml version="1.0" encoding="UTF-8"?>
<AUDIT>
 <AUDIT_RECORD>
  <TIMESTAMP>2018-03-28T13:42:01 UTC</TIMESTAMP>
  <RECORD_ID>1_2018-03-28T13:42:01</RECORD_ID>
  <NAME>Audit</NAME>
  <SERVER_ID>0</SERVER_ID>
  <VERSION>1</VERSION>
  <STARTUP_OPTIONS>mysqld</STARTUP_OPTIONS>
  <OS_VERSION>x86_64-linux-glibc2.12</OS_VERSION>
  <MYSQL_VERSION>5.7.21-enterprise-commercial-advanced</MYSQL_VERSION>
 </AUDIT_RECORD>

Configuration

After MySQL Enterprise Audit is installed, you can use the audit_log option for subsequent server startups to control the Audit plugin activation.

e.g. Prevent the plugin from being removed at runtime, select JSON as logging format and force log file rotation when it reaches 1MB :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

Then restart the MySQL server.

$ cat /var/lib/mysql/audit.log
[  
   {  
      "timestamp":"2018-03-28 14:54:27",
      "id":0,
      "class":"audit",
      "event":"startup",
      "connection_id":0,
      "startup_data":{  
         "server_id":0,
         "os_version":"x86_64-linux-glibc2.12",
         "mysql_version":"5.7.21-enterprise-commercial-advanced",
         "args":[  
            "mysqld"
         ]
      }
   }

We have now a JSON format audit log file.

As you can see, by default, contents of audit log files produced by the audit log plugin are not encrypted and may contain sensitive information, such as the text of SQL statements. For security reasons, audit log files should be written to a directory accessible only to the MySQL server and to users with a legitimate reason to view the log.

For additional security, you can enable audit log file encryption.

Encryption

To encrypt your audit log file, the MySQL keyring must be enabled because audit logging uses it for password storage. Note that any keyring plugin can be used e.g. : file-based, encrypted file, OKV KMIP compatible, AWS, …

To control whether audit log file encryption is enabled, set the audit_log_encryption system variable at server startup. Permitted values are NONE (no encryption; the default) and AES (AES-256-CBC cipher encryption).

To set or get the encryption password, use these user-defined functions (UDFs):

• To set the encryption password, invoke audit_log_encryption_password_set(), which stores the password in the keyring, renames the current log file, and begins a new log file encrypted with the new password.
• To get the current encryption password, invoke audit_log_encryption_password_get(), which retrieves the password from the keyring.

More details here.

Let’s practice!

Keyring Plugin Installation : keyring_encrypted_file

Like I stated before many keyring plugins are available. I’ll use the keyring plugin : keyring_encrypted_file, that stores keyring data in an encrypted file local to the server host.

Warning
The keyring_encrypted_file plugin for encryption key management is not intended as a regulatory compliance solution. Security standards such as PCI, FIPS, and others require use of key management systems to secure, manage, and protect encryption keys in key vaults or hardware security modules (HSMs).

Update the MySQL configuration file and restart the MySQL server :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

audit_log_encryption=AES

early-plugin-load=keyring_encrypted_file.so
keyring_encrypted_file_data=/var/lib/mysql/keyring/keyring-encrypted
keyring_encrypted_file_password=myS3cureP4ssw%rd

• To be usable during the server startup process, keyring_encrypted_file must be loaded using the early-plugin-load option.
• To specify the password for encrypting the keyring data file, set the keyring_encrypted_file_password system variable.
• The keyring_encrypted_file_data system variable optionally configures the location of the file used by the keyring_encrypted_file plugin for data storage.
• Set the audit_log_encryption system variable to AES.

Warning
Because the my.cnf file stores a password when written as shown, it should have a restrictive mode and be accessible only to the account used to run the MySQL server.

mysql> 
SELECT PLUGIN_NAME, PLUGIN_STATUS, PLUGIN_LIBRARY, PLUGIN_DESCRIPTION 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME = 'keyring_encrypted_file'\G
*************************** 1. row ***************************
       PLUGIN_NAME: keyring_encrypted_file
     PLUGIN_STATUS: ACTIVE
    PLUGIN_LIBRARY: keyring_encrypted_file.so
PLUGIN_DESCRIPTION: store/fetch authentication data to/from an encrypted file

Now the default name of Audit log file is audit.log.enc instead of audit.log.
And obviously this is not the only change. Guess what? this new file is… encrypted (AES-256-CBC cipher encryption)

$ strings  '/var/lib/mysql/audit.log.enc' 
Salted__
9,]`<Lrc}P
d.Zw
!U\?
v_l%
Oq}{/
d_qa
S7C0
...

If you don’t want to use encryption anymore you’ll need to update audit_log_encryption option in your MySQL configuration file.
e.g.

[mysqld]
audit_log_encryption=NONE

Then restart the MySQL server

Compression

You can lowering the disk footprint of your audit logs with compression.

To control whether audit log file compression is enabled, set the audit_log_compression system variable at server startup.

Permitted values are NONE (no compression; the default) and GZIP (GNU Zip compression).

More details here.

Let’s practice!

SHOW VARIABLES LIKE 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | NONE  |
+-----------------------+-------+

Update the MySQL server configuration file :

[mysqld]
audit_log=FORCE_PLUS_PERMANENT
audit_log_format=JSON
audit_log_rotate_on_size=1048576

audit_log_compression=GZIP

Then restart the server :

SHOW VARIABLES LIKE 'audit_log_compression';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| audit_log_compression | GZIP  |
+-----------------------+-------+

Now the default name of Audit log file is audit.log.gz instead of audit.log.
This new file is compressed with GNU Zip.

$ ls -l audit.*
... 832 Apr  3 14:38 audit.20180403T123833.log.enc
... 803 Apr  3 14:49 audit.20180403T124958.log
...  20 Apr  3 14:50 audit.log.gz

Note
If both compression and encryption are enabled, compression occurs before encryption. To recover the original file manually, first decrypt it, then uncompress it.

If you don’t want to use compression anymore you’ll need to update audit_log_compression option in your MySQL configuration file.
e.g.

[mysqld]
audit_log_compression=NONE

Then restart the MySQL server

Audit Log Filtering

Another amazing feature is the audit log filtering functions. It enables filtering control in JSON format by providing an interface to create, modify, and remove filter definitions and assign filters to user accounts.

When a connection arrives, the audit log plugin determines which filter to use for the new session by searching for the user account name in the current filter assignments:

• If a filter is assigned to the user, the audit log uses that filter.
• Otherwise, if no user-specific filter assignment exists, but there is a filter assigned to the default account (%), the audit log uses the default filter.
• Otherwise, the audit log selects no audit events from the session for processing.

By default, no accounts have a filter assigned, so no processing of auditable events occurs for any account.

The following list briefly summarizes the UDFs that implement the SQL interface for audit filtering control:

• audit_log_filter_set_filter() : Define a filter
• audit_log_filter_remove_filter() : Remove a filter
• audit_log_filter_set_user() : Start filtering a user account
• audit_log_filter_remove_user() : Stop filtering a user account
• audit_log_filter_flush() : Flush manual changes to the filter tables to affect ongoing filtering

Detailed description here.

Examples

Users and Filters can be monitored respectively with mysql.audit_log_user and mysql.audit_log_filter tables

e.g.

mysql> 
SELECT * FROM mysql.audit_log_user\G
*************************** 1. row ***************************
      USER: %
      HOST: 
FILTERNAME: log_all


SELECT * FROM mysql.audit_log_filter\G
*************************** 1. row ***************************
  NAME: log_all
FILTER: {"filter": {"log": true}}

Logging All Events

You can log all auditable events for all users by create a simple filter to enable logging and assign it to the default account:

mysql>
SELECT audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }');
+-------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_all', '{ "filter": { "log": true } }') |
+-------------------------------------------------------------------------+
| OK                                                                      |
+-------------------------------------------------------------------------+


SELECT audit_log_filter_set_user('%', 'log_all');
+-------------------------------------------+
| audit_log_filter_set_user('%', 'log_all') |
+-------------------------------------------+
| OK                                        |
+-------------------------------------------+

The filter assigned to % is used for connections from any account that has no explicitly assigned filter (which initially is true for all accounts).

To determine whether a filter has been assigned to the current session, check the session value of the read-only audit_log_filter_id system variable. If the value is 0, no filter is assigned. A nonzero value indicates the internally maintained ID of the assigned filter:

mysql> 
-- Check if a filter has been assigned to the current session
SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     0 |
+-----------------------+

-- Re-connection
connect
Connection id:    7
Current database: *** NONE ***

SELECT @@audit_log_filter_id;
+-----------------------+
| @@audit_log_filter_id |
+-----------------------+
|                     1 |
+-----------------------+


-- Execute a query of your choice e.g.
SHOW SCHEMAS;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| drupal             |
... [snip] ...


-- Reads the most recently written audit log event from the audit log
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:34:11","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"show_databases","query":"SHOW SCHEMAS","status":0}}, null ]

Note
Starting with MySQL 5.7.22, you can use JSON_PRETTY function that prints out a JSON value in a format that is easy to read.

Updated on 22nd of August 2018
For convenience, a JSON_PRETTY example with MySQL 8.0.12:

mysql>
SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 8.0.12-commercial |
+-------------------+


SELECT JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4))\G
*************************** 1. row ***************************
JSON_PRETTY(CONVERT(audit_log_read(audit_log_read_bookmark()) using utf8mb4)): [
  {
    "id": 0,
    "class": "general",
    "event": "status",
    "login": {
      "ip": "",
      "os": "",
      "user": "msandbox",
      "proxy": ""
    },
    "account": {
      "host": "localhost",
      "user": "msandbox"
    },
    "timestamp": "2018-08-22 13:19:54",
    "general_data": {
      "query": "SELECT CONVERT(audit_log_read(audit_log_read_bookmark()), JSON)",
      "status": 0,
      "command": "Query",
      "sql_command": "select"
    },
    "connection_id": 12
  },
  null
]

Remove a filter on a user account

I don’t want to audit all users anymore. Instead I’ll use the log_all filter to audit the user account olivier@%.

-- Stop auditing all users 
SELECT audit_log_filter_remove_user('%');
+-----------------------------------+
| audit_log_filter_remove_user('%') |
+-----------------------------------+
| OK                                |
+-----------------------------------+


SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:48:08","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT audit_log_filter_remove_user('%')","status":0}}, null ]


-- Audit still enabled for the current session
SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+


SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:42","id":0,"class":"general","event":"status","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 1","status":0}}, null ]


-- Reset the session
connect
Connection id:    10
Current database: *** NONE ***


SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+

-- No more entries in the audit log
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 15:50:49","id":0,"class":"connection","event":"disconnect","connection_id":7,"account":{"user":"root","host":"localhost"},"login":{"user":"root","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket"}}, null ]

Note
Filtering of current sessions for the user remains unaffected. 

Assign a filter explicitly to a particular user account

The filter is already defined (log_all), let’s assign it to the olivier@% user account :

mysql_root> 
SELECT audit_log_filter_set_user('olivier@%', 'log_all');
+---------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'log_all') |
+---------------------------------------------------+
| OK                                                |
+---------------------------------------------------+

In another session connection with user olivier :

$ mysql -uolivier -p

All commands from user olivier are audited :

mysql_root>
-- Root session
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:01:09","id":0,"class":"connection","event":"connect","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"connection_data":{"connection_type":"socket","status":0,"db":""}}, {"timestamp":"2018-04-03 16:01:09","id":1,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select @@version_comment limit 1","status":0}}, {"timestamp":"2018-04-03 16:01:09","id":2,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"select USER()","status":0}}, null ]


-- Olivier session
mysql_olivier>
SELECT 'Olivier';
+---------+
| Olivier |
+---------+
| Olivier |
+---------+


-- Back to root session
mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 16:17:17","id":0,"class":"general","event":"status","connection_id":15,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"general_data":{"command":"Query","sql_command":"select","query":"SELECT 'Olivier'","status":0}}, null ]

Logging Specific Event Classes

Create a new filter : log_table_access_events

It allows to audit all table accesses. This filter is assigned to user olivier. So it replace any previous filter.

mysql_root>
SELECT audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }');
+-----------------------------------------------------------------------------------------------------------------+
| audit_log_filter_set_filter('log_table_access_events', '{ "filter": { "class": { "name": "table_access" } } }') |
+-----------------------------------------------------------------------------------------------------------------+
| OK                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------+

SELECT audit_log_filter_set_user('olivier@%', 'log_table_access_events');
+-------------------------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'log_table_access_events') |
+-------------------------------------------------------------------+
| OK                                                                |
+-------------------------------------------------------------------+

SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G
*************************** 1. row ***************************
USER: olivier
HOST: %
FILTERNAME: log_table_access_events


SELECT * FROM mysql.audit_log_filter WHERE NAME='log_table_access_events'\G
*************************** 1. row ***************************
NAME: log_table_access_events
FILTER: {"filter": {"class": {"name": "table_access"}}}

Connection with user olivier

$ mysql -u olivier -p

Run a query on a table (table access)

-- Olivier's session
mysql_olivier>
SELECT * FROM actor LIMIT 1\G
*************************** 1. row ***************************
actor_id: 1
first_name: PENELOPE
last_name: GUINESS
last_update: 2006-02-15 04:34:33

This command is logged in Audit log file :

mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]

Other kinds of queries will not be logged :

-- Olivier's session
mysql_olivier>
CREATE SCHEMA s1;
Query OK, 1 row affected (0,01 sec)

The Audit log file did not changed

mysql_root>
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:03:03","id":0,"class":"table_access","event":"read","connection_id":25,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"actor","query":"SELECT * FROM actor LIMIT 1","sql_command":"select"}}, null ]

It’s also possible to select a specific event on these tables like INSERT, DELETE, UPDATE…

You’ll find all the details here.

Blocking Execution of Specific Events

Event items can include an abort item that indicates whether to prevent qualifying events from executing.

This is convenient  to block execution of specific SQL statements.

Let’s block INSERTs, UPDATEs and DELETEs on sakila.payment table :

mysql_root>
-- This filter blocks INSERT, UPDATE, and DELETE statements only for sakila.payment table
SET @filter = '{"filter": {"class": {"name": "table_access","event": {"name": [ "insert", "update", "delete" ],"abort": {"and": [{ "field": { "name": "table_database.str", "value": "sakila" } }, { "field": { "name": "table_name.str", "value": "payment" } }]}}}}}';
Query OK, 0 rows affected (0,00 sec)


SELECT audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter);
+------------------------------------------------------------------------------+
| audit_log_filter_set_filter('block_writes_on_table_payment_events', @filter) |
+------------------------------------------------------------------------------+
| OK                                                                           |
+------------------------------------------------------------------------------+

SELECT audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events');
+--------------------------------------------------------------------------------+
| audit_log_filter_set_user('olivier@%', 'block_writes_on_table_payment_events') |
+--------------------------------------------------------------------------------+
| OK                                                                             |
+--------------------------------------------------------------------------------+

SELECT * FROM mysql.audit_log_user WHERE USER='olivier'\G
*************************** 1. row ***************************
USER: olivier
HOST: %
FILTERNAME: block_writes_on_table_payment_events


SELECT * FROM mysql.audit_log_filter WHERE NAME='block_writes_on_table_payment_events'\G
*************************** 1. row ***************************
NAME: block_writes_on_table_payment_events
FILTER: {"filter": {"class": {"name": "table_access", "event": {"name": ["insert", "update", "delete"], "abort": {"and": [{"field": {"name": "table_database.str", "value": "sakila"}}, {"field": {"name": "table_name.str", "value": "payment"}}]}}}}}

When using user olivier :

# Connection with user olivier
$ mysql -u olivier -p

Any forbidden queries will be blocked…

mysql_olivier>
SELECT COUNT(*) FROM sakila.payment;
+----------+
| COUNT(*) |
+----------+
|    16049 |
+----------+


-- MySQL Audit plugin prevents writes on sakila.payment
DELETE FROM sakila.payment;
ERROR 1045 (28000): Statement was aborted by an audit log filter


SELECT COUNT(*) FROM sakila.payment;
+----------+
| COUNT(*) |
+----------+
|    16049 |
+----------+

and logged by the MySQL Audit plugin.

mysql_root> 
SELECT audit_log_read(audit_log_read_bookmark())\G
*************************** 1. row ***************************
audit_log_read(audit_log_read_bookmark()): [ {"timestamp":"2018-04-03 17:29:22","id":0,"class":"table_access","event":"delete","connection_id":28,"account":{"user":"olivier","host":"localhost"},"login":{"user":"olivier","os":"","ip":"","proxy":""},"table_access_data":{"db":"sakila","table":"payment","query":"DELETE FROM sakila.payment","sql_command":"delete"}}, null ]

$ tail /var/lib/mysql/audit.log
{ "timestamp": "2018-04-03 17:28:00", "id": 0, "class": "table_access", "event": "read", "connection_id": 25, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "SELECT COUNT(*) FROM sakila.payment", "sql_command": "select" } },
{ "timestamp": "2018-04-03 17:29:22", "id": 0, "class": "table_access", "event": "delete", "connection_id": 28, "account": { "user": "olivier", "host": "localhost" }, "login": { "user": "olivier", "os": "", "ip": "", "proxy": "" }, "table_access_data": { "db": "sakila", "table": "payment", "query": "DELETE FROM sakila.payment", "sql_command": "delete" } }

And now Olivier is in big troubles!!! 🙂

MySQL Enterprise Audit plugin is very powerful!

Please read the documentation for more information.

Uninstallation

To remove MySQL Enterprise Audit, execute the following statements :

DROP TABLE IF EXISTS mysql.audit_log_filter;
DROP TABLE IF EXISTS mysql.audit_log_user;
UNINSTALL PLUGIN audit_log;
DROP FUNCTION audit_log_filter_set_filter;
DROP FUNCTION audit_log_filter_remove_filter;
DROP FUNCTION audit_log_filter_set_user;
DROP FUNCTION audit_log_filter_remove_user;
DROP FUNCTION audit_log_filter_flush;
DROP FUNCTION audit_log_encryption_password_get;
DROP FUNCTION audit_log_encryption_password_set;
DROP FUNCTION audit_log_read;
DROP FUNCTION audit_log_read_bookmark;

Voilà!

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

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 5.7 Installing the MySQL Enterprise Audit Plugin
• MySQL 5.7 Enterprise Audit
• Presentation of MySQL 5.7 Enterprise Audit

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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, User Account Locking, etc…

An ordinary threat databases could face is an attempt to discover the password by systematically trying every possible combination (letters, numbers, symbols). This is known as a brute force attack.
In this fourth episode of the MySQL Security series, we will see how the MySQL DBA can leverage the Connection-Control Plugins to slow down brute force attacks.

The Connection-Control Plugins

The MySQL Server includes a plugin library that enables administrators to introduce an increasing delay in server response to clients after a certain number of consecutive failed connection attempts. This capability provides a deterrent that slows down brute force attacks that attempt to access MySQL user accounts.

Installation

In MySQL 5.7, the Connection-Control plugin is not installed by default :

mysql> 
SELECT version();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+

SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'connection%';
Empty set (0.00 sec)


SHOW VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+

The plugin library contains two plugins :

• CONNECTION_CONTROL checks incoming connections and adds a delay to server responses as necessary.
• CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS implements an INFORMATION_SCHEMA table that exposes more detailed monitoring information for failed connection attempts.

As usual, you can easily register the plugins at runtime with INSTALL PLUGIN statement :

mysql> 
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
Query OK, 0 rows affected (0.02 sec)


INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
Query OK, 0 rows affected (0.00 sec)


SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'connection%';
+------------------------------------------+---------------+
| PLUGIN_NAME                              | PLUGIN_STATUS |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+

Alternatively you can modify the configuration file (my.cnf / my.ini) and then restart the server

[mysqld]
plugin-load-add=connection_control.so

If the plugins have been previously registered with INSTALL PLUGIN or are loaded with plugin-load-add, you can use the connection-control and connection-control-failed-login-attempts options at server startup to control plugin activation.

e.g. to load the plugins at startup and prevent them from being removed at runtime, use these options :

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT

Configuration

To enable you to configure its operation, the CONNECTION_CONTROL plugin exposes 3 system variables :

• connection_control_failed_connections_threshold : The number of consecutive failed connection attempts permitted to clients before the server adds a delay for subsequent connection attempts.
• connection_control_min_connection_delay : The amount of delay to add for each consecutive connection failure above the threshold.
• connection_control_max_connection_delay : The maximum delay to add.

Note : To entirely disable checking for failed connection attempts, set connection_control_failed_connections_threshold to zero.

Default values are :

mysql> 
SHOW VARIABLES LIKE 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+

You can modify these variables at runtime with SET GLOBAL :

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

 
SET GLOBAL connection_control_min_connection_delay = 1000;
Query OK, 0 rows affected (0.00 sec)

 
SHOW VARIABLES LIKE 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 2          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+

Indeed they can be made persistent with the configuration file :

[mysqld]
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
connection_control_failed_connections_threshold=2
connection_control_min_connection_delay=1000

Let’s test the Connection-Control plugin behavior with a threshold = 2 and a delay = 1 second :

$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m0.070s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m0.067s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m1.069s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m2.061s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m3.072s


$ time mysql -uroot -pWrongPwd
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

real	0m4.065s

Please focus on the command execution time i.e. real NmN.NNNs

Starting at the 3rd attempts the delay between each connection increase (approximately +1s (= 1000 ms) between each new failed connection attempts).

Monitoring

To monitor failed connections, use these information sources:

• The Connection_control_delay_generated status variable indicates the number of times the server added a delay to its response to a failed connection attempt. This does not count attempts that occur before reaching the threshold defined by the connection_control_failed_connections_threshold system variable.
• The INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS table provides information about the current number of consecutive failed connection attempts per client user/host combination. This counts all failed attempts, regardless of whether they were delayed.

mysql> 
SHOW STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 4     |
+------------------------------------+-------+


SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
+--------------------+-----------------+
| USERHOST           | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'root'@'localhost' |               6 |
+--------------------+-----------------+

You can easily monitor different user accounts :

$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m0.065s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m0.088s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m1.063s


$ time mysql -uWrongUser -pWrongPwd
ERROR 1045 (28000): Access denied for user 'WrongUser'@'localhost' (using password: YES)

real	0m2.076s

Connection-Control failure monitoring :

mysql> 
SHOW STATUS LIKE 'connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 6     |
+------------------------------------+-------+


SELECT * FROM INFORMATION_SCHEMA.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS ;
+-------------------------+-----------------+
| USERHOST                | FAILED_ATTEMPTS |
+-------------------------+-----------------+
| 'WrongUser'@'localhost' |               4 |
| 'root'@'localhost'      |               6 |
+-------------------------+-----------------+

Uninstalling Plugins

To remove the plugins, use the UNINSTALL PLUGIN statement :

• UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
• UNINSTALL PLUGIN CONNECTION_CONTROL;

mysql> 
UNINSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
Query OK, 0 rows affected (0.01 sec)


UNINSTALL PLUGIN CONNECTION_CONTROL;
Query OK, 0 rows affected (0.01 sec)

Note : Update the configuration file (my.cnf / my.ini) if necessary

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 5.7 Connection-Control Plugins
• MySQL 5.7 Connection-Control Plugin Installation
• MySQL 5.7 Connection-Control System and Status Variables

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

If you are using MySQL 8.0, I would recommend you to read : MySQL Security – Failed-Login Tracking and Temporary Account Locking

---

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, The Connection-Control Plugins, etc…

For security reasons some context require you to setup a user account locking policy. Thus an unauthorized user is not able (anymore) to login to the MySQL server. In this 3rd article of the MySQL Security series, we will see how to [un]lock a user account.

User Account Locking

MySQL supports locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

• When used with CREATE USER, these clauses specify the initial locking state for a new account. In the absence of either clause, the account is created in an unlocked state.
• When used with ALTER USER, these clauses specify the new locking state for an existing account. In the absence of either clause, the account locking state remains unchanged.

So let’s create a user batchman { not this one 🙂 } with all privileges on sakila database:

mysql> 
select VERSION();
+-----------+
| version() |
+-----------+
| 5.7.21    |
+-----------+

CREATE USER batchman@localhost IDENTIFIED BY 'p4s5W0%d';

GRANT ALL ON sakila.* TO batchman@localhost;

Account locking state is recorded in the account_locked column of the mysql.user table:

mysql> 
SELECT user, host, account_locked FROM mysql.user WHERE user = 'batchman'\G
*************************** 1. row ***************************
          user: batchman
          host: localhost
account_locked: N

The output from SHOW CREATE USER indicates whether an account is locked or unlocked:

mysql> 
SHOW CREATE USER batchman@localhost\G
*************************** 1. row ***************************
CREATE USER for batchman@localhost: CREATE USER 'batchman'@'localhost' IDENTIFIED WITH 'mysql_native_password' 
AS '*15E0AF3C6647A20428477A460202CD7C89D78DDF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

Indeed accounts are unlock by default.

The new user account is allowed to connect to the server and can handle sakila’s objects:

$ mysql -u batchman -p

mysql_batchman> 
SELECT USER(); SHOW SCHEMAS;
+--------------------+
| USER()             |
+--------------------+
| batchman@localhost |
+--------------------+


+--------------------+
| Database           |
+--------------------+
| information_schema |
| sakila             |
+--------------------+

Lock account

What if your security policy requires you to lock this account?

ACCOUNT LOCK clause is the answer :

mysql> 
ALTER USER batchman@localhost ACCOUNT LOCK;


SELECT user, host, account_locked FROM mysql.user WHERE user = 'batchman';
+----------+-----------+----------------+
| user     | host      | account_locked |
+----------+-----------+----------------+
| batchman | localhost | Y              |
+----------+-----------+----------------+


mysql> 
SHOW CREATE USER batchman@localhost\G
*************************** 1. row ***************************
CREATE USER for batchman@localhost: CREATE USER 'batchman'@'localhost' 
IDENTIFIED WITH 'mysql_native_password' AS '*15E0AF3C6647A20428477A460202CD7C89D78DDF' 
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK

batchman@localhost is now locked!

Any connection attempt with this user will failed :

-- User try to connect
$ mysql -u batchman -p 

ERROR 3118 (HY000): Access denied for user 'batchman'@'localhost'. Account is locked.

Note.

If the user is still connected you’ll need to kill the connection.

MySQL returns an ER_ACCOUNT_HAS_BEEN_LOCKED error.

The server increments the Locked_connects status variable that indicates the number of attempts to connect to a locked account and writes a message to the error log :

-- Back to the administrator session
mysql> 
SHOW GLOBAL STATUS LIKE 'Locked_connects';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Locked_connects | 1     |
+-----------------+-------+

-- In the error log
mysql> system tail -n1 /var/log/mysqld.log
2018-02-01T15:30:35.649393Z 65 [Note] Access denied for user 'batchman'@'localhost'. Account is locked.

In order to go further

• MySQL Security – Failed-Login Tracking and Temporary Account Locking

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 5.7 User Account Locking
• CREATE USER Syntax

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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 Validation Plugin, User Account Locking, The Connection-Control Plugins, etc…

Some regulations required that the password is renewed in a timely and appropriate manner (e.g. every 90 days). In this article, 2nd of the MySQL  Security series, we will see how to establish a policy for password expiration with MySQL 5.7 Password Management.

Password Management

Basic password policy practices teaches us :

• Each user must have a password
• A user’s password should be changed periodically

MySQL provides password-expiration capability, which enables database administrators to require that users reset their password.

Let’s have a closer look!

$ mysql -u root -p

mysql {root}> 
SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.7.21-log |
+------------+

Basically there are different clauses a DBA can use with CREATE USER or ALTER USER to establish a per account password expiration policy.

Let’s play with some of them.

PASSWORD EXPIRE

Force user to change its password at the first connection.

-- Mark the password expired so that the user must choose a new one at the first connection to the server
mysql {root}> 
CREATE USER 'aUser'@'localhost' IDENTIFIED BY 'AutoGeneratedPwd' PASSWORD EXPIRE;

SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: Y
password_last_changed: 2018-02-08 14:22:24

Note that password_expired column is Y.

This new MySQL user will be able to connect to the server but he must reset its password.

$ mysql -u aUser -p
...

mysql {aUser}> 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

ALTER USER 'aUser'@'localhost' IDENTIFIED BY 'new_passw0rd';
Query OK, 0 rows affected (0.00 sec)


SELECT USER();
+-----------------+
| USER()          |
+-----------------+
| aUser@localhost |
+-----------------+

Column password_expired is now N.

mysql {root}> 
SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aUser'\G
*************************** 1. row ***************************
                 user: aUser
                 host: localhost
    password_lifetime: NULL
     password_expired: N
password_last_changed: 2018-02-08 14:23:50

PASSWORD EXPIRE INTERVAL N DAY

Force user to change its password every N days.

mysql {root}> 
-- Require that a new password be chosen every 90 days:
CREATE USER 'aNewUser'@'localhost' IDENTIFIED BY 'n3w_password'  PASSWORD EXPIRE INTERVAL 90 DAY;

SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'aNewUser'\G
*************************** 1. row ***************************
                 user: aNewUser
                 host: localhost
    password_lifetime: 90
     password_expired: N
password_last_changed: 2018-02-08 14:24:53

Note that password_lifetime column is 90.

After 90 days any statement will generate error 1820 :

$ mysql -u aNewUser -p

mysql {aNewUser}> SELECT USER();
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

Password could be reset with ALTER USER command :

mysql {root}>
ALTER USER 'aNewUser'@'localhost' IDENTIFIED BY '4noth3r_password' PASSWORD EXPIRE INTERVAL 90 DAY;

PASSWORD EXPIRE NEVER

It’s also possible to disable password expiration for an account

mysql {root}>
-- Disables password expiration for the account so that its password never expires.
CREATE USER 'pingDB'@'localhost' IDENTIFIED BY 'new_p4ssword' PASSWORD EXPIRE NEVER;

SELECT user, host, password_lifetime, password_expired, password_last_changed FROM mysql.user WHERE user = 'pingDB'\G
*************************** 1. row ***************************
                 user: pingDB
                 host: localhost
    password_lifetime: 0
     password_expired: N
password_last_changed: 2018-02-08 14:29:43

Note that password_lifetime column is 0.

PASSWORD EXPIRE DEFAULT

This clause sets the account so that the global password expiration policy applies, as specified by the default_password_lifetime system variable.

In MySQL 5.7 it applies to accounts that use MySQL built-in authentication methods (accounts that use an authentication plugin of mysql_native_password or sha256_password).

The default default_password_lifetime value is 0, which disables automatic password expiration. If the value of default_password_lifetime is a positive integer N, it indicates the permitted password lifetime; passwords must be changed every N days.

default_password_lifetime can be changed at runtime with SET GLOBAL command. However it must be set in the MySQL configuration file for persistence.

e.g.

$ cat my.cnf 

[mysqld]
# global policy that passwords have a lifetime of approximately 3 months
default_password_lifetime=90
...

e.g. Create a user account with the default global password policy (90 days)

mysql>
-- default_password_lifetime set to 90 days
SET GLOBAL default_password_lifetime = 90;

SHOW VARIABLES LIKE 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+

-- Sets the account so that the global expiration policy applies, as specified by the default_password_lifetime system variable.
CREATE USER 'dba'@'localhost' IDENTIFIED BY 'new_pas5word' PASSWORD EXPIRE DEFAULT;

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 5.7 Password Management
• MySQL 5.7 Password Expiration and Sandbox Mode

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

 

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

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, User Account Locking, The Connection-Control Plugins, etc…

In this article, 1st of a MySQL Security series, we will see how to enforce Strong Passwords with Password Validation Plugin when using MySQL 5.7.

Authentication with ID and password is a very simple and common (because it’s simple) way to secure the access to a resource, however the password can be the weak point of this system. In order to increase the security level, you can required that your user passwords meet certain minimal security requirements, using the MySQL Password validation plugin!

Password Validation Plugin

The Password validation plugin serves to test passwords and improve security. It exposes a set of system variables that enable you to define password policy.

For ALTER USER, CREATE USER, GRANT, and SET PASSWORD statements the plugin checks the password against the current password policy and rejects it if it is weak.

Examples are made with MySQL CE 5.7.21 on Linux:

mysql> SHOW VARIABLES LIKE 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.7.21                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

Installation

Plugins are located in the… plugin directory. To know where is your MySQL plugin directory you can use SHOW VARIABLES :

mysql> 
SHOW VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+

system ls -l /usr/lib64/mysql/plugin/ | grep validate
-rwxr-xr-x 1 root root   29336 Dec 28 04:07 validate_password.so

Use the regular INSTALL PLUGIN statement:

-- Install validate_password plugin
mysql> 
INSTALL PLUGIN validate_password SONAME 'validate_password.so';

-- Check validate_password status
SELECT PLUGIN_NAME, PLUGIN_STATUS 
FROM INFORMATION_SCHEMA.PLUGINS 
WHERE PLUGIN_NAME LIKE 'validate%';
+-------------------+---------------+
| PLUGIN_NAME       | PLUGIN_STATUS |
+-------------------+---------------+
| validate_password | ACTIVE        |
+-------------------+---------------+

INSTALL PLUGIN loads the plugin, and also registers it in the mysql.plugins system table to cause the plugin to be loaded for each subsequent normal server startup.

Alternatively you can modify the MySQL configuration file (e.g. my.cnf or my.ini) and reboot the instance.

e.g.

# sample from my.cnf
[mysqld]
plugin-load-add=validate_password.so

When installed some system and status variables are available:

mysql> SHOW VARIABLES LIKE 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+


SHOW STATUS LIKE 'validate%';
+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| validate_password_dictionary_file_last_parsed | 2018-02-06 14:58:19 |
| validate_password_dictionary_file_words_count | 0                   |
+-----------------------------------------------+---------------------+

They are described here.

Playtime

Let’s play a little a bit with the Password Validation Plugin.

Set Password Validation Plugin to the LOW level

When validate_password_policy is set to LOW (or 0) it checks only the length i.e. validate_password_length >= 8 (by default)

mysql> 
SET GLOBAL validate_password_policy = 0;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| validate_password_policy | LOW   |
+--------------------------+-------+

Warning

Passwords in the following examples are not secure. Do NOT use trivial passwords!

User creation that is not satisfy the policy will failed

mysql> 
-- NOK because password length < 8 
CREATE USER u_low1 IDENTIFIED by 'p';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


-- OK because password length >= 8
CREATE USER u_low2 IDENTIFIED by 'p2345678';
Query OK, 0 rows affected (0.01 sec)

CREATE USER u_low3 IDENTIFIED by 'pppppppp';
Query OK, 0 rows affected (0.00 sec)



mysql> 
-- new users created
SELECT user FROM mysql.user WHERE user LIKE 'u%';
+--------+
| user   |
+--------+
| u_low2 |
| u_low3 |
+--------+

Set Password Validation Plugin to the MEDIUM level

When validate_password_policy is set to MEDIUM (or 1) it checks

• the length i.e. validate_password_length >= 8 (by default)
• numeric
• lowercase/uppercase
• special characters

mysql> 
SET GLOBAL validate_password_policy = 1;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | MEDIUM |
+--------------------------+--------+

mysql>
-- NOK because password length < 8, no special character, nor numeric, nor uppercase
CREATE USER u_medium1 IDENTIFIED by 'p';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirement

-- NOK because no special character, nor uppercase
CREATE USER u_medium2 IDENTIFIED by 'p2345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

-- NOK because no uppercase
CREATE USER u_medium3 IDENTIFIED by 'p_345678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

-- NOK because no uppercase
CREATE USER u_medium4 IDENTIFIED by 'p_p45678';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements


-- OK because password length >= 8, numeric, lowercase/uppercase, special character
CREATE USER u_medium5 IDENTIFIED by 'p_P45678';
Query OK, 0 rows affected (0.00 sec)



mysql>
-- new users created
SELECT user FROM mysql.user WHERE user LIKE 'u%';
+-----------+
| user      |
+-----------+
| u_low2    |
| u_low3    |
| u_medium5 |
+-----------+

Set Password Validation Plugin to the STRONG level

When validate_password_policy is set to STRONG (or 2) it checks

• the length i.e. validate_password_length >= 8 (by default)
• numeric
• lowercase/uppercase
• special characters
• dictionary file

mysql> 
SET GLOBAL validate_password_policy = 2;


SHOW VARIABLES LIKE 'validate_password_policy';
+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| validate_password_policy | STRONG |
+--------------------------+--------+

The main difference with the medium policy is the possibility to use a dictionary file to for checking password against. Set validate_password_dictionary_file variable. By default, this variable has an empty value and dictionary checks are not performed.

-- No dictionary file by default
mysql> 
SHOW VARIABLES LIKE 'validate_password_dictionary_file';
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| validate_password_dictionary_file |       |
+-----------------------------------+-------+

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 5.7 Password Validation Plugin
• MySQL 5.7 Password Validation Plugin Installation
• MySQL 5.7 Password Validation Plugin Options and Variables

MySQL Security

• Security in MySQL
• MySQL 5.7 Secure Deployment Guide
• MySQL 5.7 Reference Manual – Security

 

Thanks for using MySQL!

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Lire cet article en français

There are 3 pillars for a database architecture:

• Monitoring
• Backup / Restore process
• High Availability

This blog post is about database High Availability; more precisely about one of the best combo of the moment :

 

• MySQL 5.7 Group Replication : the only native HA solution for MySQL, it’s a Single/Multi-master update everywhere replication plugin for MySQL with built-in automatic distributed recovery, conflict detection and group membership.
• ProxySQL 1.4 : probably the best proxy for MySQL.

 

Note : MySQL replication is indeed more than a serious alternative for HA. By the way ProxySQL natively handle it.

Note : ProxySQL have a plethora of features, however the purpose of this article is its use in a MySQL Group Replication context..

Note : MySQL NDB Cluster is also a HA solution adapted for the distributed computing environment. This is out of the scope of this article.

 

ProxySQL is compatible with MySQL Group Replication since version 1.3 see (in French) : Configurer ProxySQL pour MySQL Group Replication. However, version 1.4 have a native MySQL Group Replication support. It is therefore easier to use these two popular technologies together.

This is what we will see now.

 

In this article I will make the following assumptions :

• MySQL 5.7.20 is downloaded and installed, on 3 hosts.
• ProxySQL 1.4.4 is downloaded and installed  on 1 host (ProxySQL HA is not covered in this article).
• A 3 nodes MySQL Group Replication cluster is up and running.

 

MySQL Group Replication

Specifications

• Server version : 5.7.20
• Plugin version : 1.0
• Node 1 : mysql_node1, 172.22.0.10 : 3306
• Node 2 : mysql_node2, 172.22.0.20 : 3306
• Node 3 : mysql_node3, 172.22.0.30 : 3306

These characteristics therefore represent a 3-nodes MySQL Group Replication cluster, up and running:

node1>
-- MySQL Server version number
SELECT left(version(),6);
+-------------------+
| left(version(),6) |
+-------------------+
| 5.7.20            |
+-------------------+

-- MySQL Group Replication plugin details
SELECT * FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'group%'\G
*************************** 1. row ***************************
           PLUGIN_NAME: group_replication
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: GROUP REPLICATION
   PLUGIN_TYPE_VERSION: 1.1
        PLUGIN_LIBRARY: group_replication.so
PLUGIN_LIBRARY_VERSION: 1.7
         PLUGIN_AUTHOR: ORACLE
    PLUGIN_DESCRIPTION: Group Replication (1.0.0)
        PLUGIN_LICENSE: PROPRIETARY
           LOAD_OPTION: FORCE_PLUS_PERMANENT

-- MySQL Group Replication member status
SELECT * FROM performance_schema.replication_group_members\G
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c2039a6-f152-11e7-90da-0242ac16001e
 MEMBER_HOST: mysql_node3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c2039a8-f152-11e7-9132-0242ac160014
 MEMBER_HOST: mysql_node2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a
 MEMBER_HOST: mysql_node1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

MySQL Enterprise Monitor gives us a graphical view of the cluster and its state: (click to enlarge) :

 

The cluster is in single primary mode, ie only one node is available for reading & writing at a time (while the other 2 nodes are read-only).

node1>
-- Is single primary mode activated?
SHOW VARIABLES LIKE 'group_replication_single_primary_mode';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | ON    |
+---------------------------------------+-------+

-- Who is the primary node?
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE
FROM performance_schema.replication_group_members
INNER JOIN performance_schema.global_status ON (MEMBER_ID = VARIABLE_VALUE)
WHERE VARIABLE_NAME='group_replication_primary_member'\G
*************************** 1. row ***************************
   MEMBER_ID: 3c36e366-f152-11e7-91a5-0242ac16000a
 MEMBER_HOST: mysql_node1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE

MySQL Enterprise Monitor shows us (click to enlarge):

 

I am going to extend the MySQL 5.7 sys schema with the following script : addition_to_sys.sql

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

 

So I load the functions and views into the cluster primary node (mysql_node1) :

# Loading extra functions and views to sys schema on the cluster (using a primary node)

$ mysql -u root -p -h mysql_node1 < ./addition_to_sys.sql

 

This script will allow ProxySQL to monitor the state of the cluster nodes.

e.g.

node1> 
-- Status of the primary node
SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

node2> 
-- Status of a secondary node
SELECT * FROM sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | YES       |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

 

On the cluster side, the last configuration step consists of creating the supervision users that will be used by ProxySQL (yes, there is a relationship with the previous step) :).

Here again I use the primary of the group :

node1>
-- Create ProxySQL monitoring user inside the cluster
CREATE USER proxysqlmonitor@'%' IDENTIFIED BY 'Very-S3cr3t';

GRANT SELECT ON sys.* TO proxysqlmonitor@'%';

 

Let’s configure ProxySQL now !

 

ProxySQL

Specifications

• Proxy version : 1.4.4
• Admin Interface : 172.22.0.2:6032
• Cluster connection : 172.22.0.2:3306

 

$ proxysql --version
ProxySQL version 1.4.4-139-ga51b040, codename Truls

$ service proxysql status
ProxySQL is running (58).

 

ProxySQL configuration can be done online, which is obviously a very good thing.

Let’s start by logging into the admin interface on the default port 6032 with the default admin user and password… : admin (!) – Defaults that can and must be changed in real life.

$ mysql -u admin -p -P 6032 --protocol=tcp main

 

Servers configuration

First step, add the cluster nodes to the proxy :

-- Add 3 nodes of the cluster into the mysql_servers table
proxy> 
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'mysql_node1', 3306), (2, 'mysql_node2', 3306), (2, 'mysql_node3', 3306);

proxy> 
select * from mysql_servers;
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | mysql_node1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql_node2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | mysql_node3 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

 

Hostgroups configuration

I have done a brief presentation of ProxySQL 1.3 objects last time : Configurer ProxySQL pour MySQL Group Replication

Version 1.4 has some differences, the most notable in our context is the new table : mysql_group_replication_hostgroups :

proxy> 
SHOW CREATE TABLE main.mysql_group_replication_hostgroups\G
*************************** 1. row ***************************ajouter les nœuds du cluster
       table: mysql_group_replication_hostgroups
Create Table: CREATE TABLE mysql_group_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    backup_writer_hostgroup INT CHECK (backup_writer_hostgroup>=0 AND backup_writer_hostgroup<>writer_hostgroup) NOT NULL,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND backup_writer_hostgroup<>reader_hostgroup AND reader_hostgroup>0),
    offline_hostgroup INT NOT NULL CHECK (offline_hostgroup<>writer_hostgroup AND offline_hostgroup<>reader_hostgroup AND backup_writer_hostgroup<>offline_hostgroup AND offline_hostgroup>=0),
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    max_writers INT NOT NULL CHECK (max_writers >= 0) DEFAULT 1,
    writer_is_also_reader INT CHECK (writer_is_also_reader IN (0,1)) NOT NULL DEFAULT 0,
    max_transactions_behind INT CHECK (max_transactions_behind>=0) NOT NULL DEFAULT 0,
    comment VARCHAR,
    UNIQUE (reader_hostgroup),
    UNIQUE (offline_hostgroup),
    UNIQUE (backup_writer_hostgroup))

 

The best description I have found is available in my brilliant colleague’s blog post : MySQL Group Replication: native support in ProxySQL

I quote

”

There are many new columns, let’s have a look at their meaning:

Column Name	Description
writer_hostgroup	the id of the hostgroup that will contain all the members that are writer
backup_writer_hostgroup	if the group is running in multi-primary mode, there are multi writers (read_only=0) but if the amount of these writer is larger than the max_writers, the extra nodes are located in that backup writer group
reader_hostgroup	the id of the hostgroup that will contain all the members in read_only
offline_hostgroup	the id of the hostgroup that will contain the host not being online or not being part of the Group
active	when enabled, ProxySQL monitors the Group and move the server according in the appropriate hostgroups
max_writers	limit the amount of nodes in the writer hostgroup in case of group in multi-primary mode
writer_is_also_reader	boolean value, 0 or 1, when enabled, a node in the writer hostgroup will also belongs the the reader hostgroup
max_transactions_behind	if the value is greater than 0, it defines how much a node can be lagging in applying the transactions from the Group, see this post for more info

”

by  @Lefred 🙂

 

Our configuration is as follows :

• writer_hostgroup = 2
• backup_writer_hostgroup = 4
• reader_hostgroup = 3
• offline_hostgroup = 1
• active = 1
• max_writers = 1
• writer_is_also_reader = 1
• max_transactions_behind = 0

Which gives us :

proxy> 
INSERT INTO mysql_group_replication_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) VALUES (2,4,3,1,1,1,1,0);

-- Save & load new configuration
SAVE MYSQL SERVERS TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;

proxy> 
select * from mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
       writer_hostgroup: 2
backup_writer_hostgroup: 4
       reader_hostgroup: 3
      offline_hostgroup: 1
                 active: 1
            max_writers: 1
  writer_is_also_reader: 1
max_transactions_behind: 0
                comment: NULL

 

Supervision configuration

A little further up we created a supervision user in the cluster (remember?).

It is this user that will use ProxySQL to be aware of the state of the various nodes of the cluster :

-- Set user name & password for monitoring module
SET mysql-monitor_username='proxysqlmonitor';
SET mysql-monitor_password='Very-S3cr3t';

-- Save & load new configuration
SAVE MYSQL VARIABLES TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;

proxy> 
select hostgroup_id, hostname, status  from runtime_mysql_servers;
+--------------+-------------+--------+
| hostgroup_id | hostname    | status |
+--------------+-------------+--------+
| 2            | mysql_node1 | ONLINE |
| 2            | mysql_node3 | ONLINE |
| 2            | mysql_node2 | ONLINE |
+--------------+-------------+--------+

 

Application user creation

The application connects to the MySQL server with a user and password (in the best case: D).

This user must obviously exist in the different MySQL instances that form the cluster, with the correct MySQL privileges.

This user must also be created in ProxySQL :

proxy> 
INSERT INTO mysql_users (username, password, active, default_hostgroup, max_connections) VALUES ('app_user', 'app_pwd, 1, 2, 200);

-- Save & load new configuration
SAVE MYSQL USERS TO DISK;
LOAD MYSQL USERS TO RUNTIME;

proxy> 
select * from mysql_users;
*************************** 1. row ***************************
              username: app_user
              password: app_pwd
                active: 1
               use_ssl: 0
     default_hostgroup: 2
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 200

 

Change the listening port

By default the application will connect to the cluster through ProxySQL using port 6032 (in our case: 172.22.0.2:6032)

It is therefore possible (not required) to modify the ProxySQL port to listen on 3306 :

proxy> 
SET mysql-interfaces='0.0.0.0:3306;/tmp/proxysql.sock';

SAVE MYSQL VARIABLES TO DISK;

proxy> 
SHOW VARIABLES LIKE 'mysql-interfaces'\G
*************************** 1. row ***************************
Variable_name: mysql-interfaces
        Value: 0.0.0.0:3306;/tmp/proxysql.sock

 

Note : For a mysterious reason, this last configuration change is not online. In other words I must restart ProxySQL for this change to be taken into account.

$ service proxysql restart
Shutting down ProxySQL: DONE!
Starting ProxySQL: DONE!

 

The ProxySQL configuration for MySQL Group Replication is now complete \o/

I’m taking this opportunity to introduce a new table in version 1.4 : mysql_server_group_replication_log.

Useful for monitoring :

proxy> 
select * from mysql_server_group_replication_log order by time_start_us desc limit 3\G
*************************** 1. row ***************************
           hostname: mysql_node3
               port: 3306
      time_start_us: 1515079109822616
    success_time_us: 1782
   viable_candidate: YES
          read_only: YES
transactions_behind: 0
              error: NULL
*************************** 2. row ***************************
           hostname: mysql_node2
               port: 3306
      time_start_us: 1515079109822292
    success_time_us: 1845
   viable_candidate: YES
          read_only: YES
transactions_behind: 0
              error: NULL
*************************** 3. row ***************************
           hostname: mysql_node1
               port: 3306
      time_start_us: 1515079109821971
    success_time_us: 1582
   viable_candidate: YES
          read_only: NO
transactions_behind: 0
              error: NULL

 

Playtime

As a reminder, workflow is as follows :

• The app connects to ProxySQL (i.e. it only sees and knows the proxy)
• ProxySQL retrieves transactions and redirects them to the primary node of the MySQL Group Replication cluster.
• In case of primary node crash / shutdown,
  • MySQL Group Replication elects a new primary
  • ProxySQL identifies the new primary and sends transactions to this new primary

 

The application must therefore be configured to connect to ProxySQL. For example, if my app is Drupal  then my settings.php file will look like the following code snippet :

...
$databases['default']['default'] = array (
  'database' => 'drupal',
  'username' => 'app_user',
  'password' => 'app_pwd',
  'prefix' => 'drupal_',
  'host' => '172.22.0.2',
  'port' => '3306',
  'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
  'driver' => 'mysql',
);
...

• User : app_user
• Password : app_pwd
• Port : 3306 (ProxySQL)
• Host : 172.22.0.2 (ProxySQL)

QED!

 

Let’s simulate all this in the command line!

My app is the mysql command-line tool. To simplify my commands, I first create a configuration file for the mysql client that contains the following information (btw it’s not recommended to have passwords in clear in a text file)  :

$ cat /tmp/temp.cnf
[mysql]
user=app_user
password=app_pwd
protocol=tcp

 

My servers are configured with the variable report_host filled (see http://dasini.net/blog/2016/11/08/deployer-un-cluster-mysql-group-replication/).

$ for x in {1..5}; do mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306  -BNe"SELECT @@report_host;" ; done;
mysql_node1
mysql_node1
mysql_node1
mysql_node1
mysql_node1

The cluster primary node is still mysql_node1 (no database failover yet since the beginning of this article, but we get there).

 

Now let’s test the failover with a slightly more complex example.

First, let’s create the test.poc table in InnoDB format :

proxy>
CREATE SCHEMA test;

CREATE TABLE test.poc (
id tinyint unsigned NOT NULL AUTO_INCREMENT,
host varchar(11),
time timestamp,
PRIMARY KEY (id)
) ENGINE=InnoDB;

 

The test application will run the following 2 queries :

• INSERT INTO test.poc (host) VALUES (@@report_host);
• SELECT * FROM test.poc;

 

With these 2 queries, the little scripts below and a timely kill -9, we will be able to follow the routing process (ProxySQL) and the database failover (MySQL Group Replication).

$ while true; do
>   mysql --defaults-file=/tmp/temp.cnf -h 172.22.0.2 -P 3306 -BNe"INSERT INTO test.poc (host) VALUES (@@report_host); SELECT * FROM test.poc;";
>   echo;
>   sleep 4;
> done

2	mysql_node1	2018-01-04 16:42:31 <=> Inserted row #1

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35 <=> Inserted row #2

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35
11	mysql_node3	2018-01-04 16:42:43 <=> Inserted row #3 => Failover! New primary is mysql_node3

2	mysql_node1	2018-01-04 16:42:31
9	mysql_node1	2018-01-04 16:42:35
11	mysql_node3	2018-01-04 16:42:43
18	mysql_node3	2018-01-04 16:42:47 <=> Inserted row #4

Transactions 1 & 2 (ids 2 et 9) are run on mysql_node 1.

From the third transaction (ids 11 et 18), they are run on the new primary mysql_node 3, because mysql_node 1 crashed.

 

Let’s finish this tutorial with pictures.

 

MySQL Enterprise Monitor

As stated in the introduction, a database architecture should rely on the 3 pillars : Monitoring / Backup process / High Availability.

Below a brief introduction of MySQL Enterprise Monitor (MEM) which is the MySQL monitoring tool, available with the commercial editions of MySQL (MySQL Enterprise Edition). MEM enables monitoring of MySQL instances and their hosts, notification of potential issues and problems, and advice on how to correct issues. You can also monitor and troubleshoot all types of MySQL Replication including MySQL Group Replication.

If you want to try our  Enterprise tools, please click here.

Below are some screenshots of the various states of the cluster supervised by MySQL Enterprise Monitor (click to enlarge):

 

 

The final word(s)

You already know, MySQL Replication and MySQL semi-synchronous Replication.

MySQL Group Replication is an additional tool that brings in especially the concept of built-in “Automatic Database Failover” that was missing in MySQL.

These architectures are used most of the time with a proxy. ProxySQL is without a doubt one of the best companion for MySQL today.

 

Note : MySQL provides MySQL InnoDB Cluster, which combines MySQL technologies ( MySQL Group Replication, MySQL Router and MySQL Shell) to enable you to create highly available clusters of MySQL server instances.
Tutorial : Tutoriel – Déployer MySQL 5.7 InnoDB Cluster (French)

 

 

References

dasini.net

• Déployer un cluster MySQL Group Replication
• Adopte un… cluster MySQL Group Replication
• Configurer ProxySQL 1.3 pour MySQL Group Replication
• Tutoriel – Déployer MySQL innoDB Cluster

MySQL Group Replication

• Group Replication Features backported to MySQL 5.7
• Group Replication: Prioritise member for the Primary Member Election
• Group Replication support SAVEPOINT
• Group Replication – Extending Group Replication performance_schema tables
• MySQL High Availability blog
• Doc
• lefred’s blog

ProxySQL

• MySQL Group Replication: native support in ProxySQL
• http://www.proxysql.com/blog/how-to-run-multiple-proxysql-instances
• Doc

 

Thanks for using MySQL!

 

Lire cet article en français

When starting a new project, it is generally recommended to go on the most recent version of MySQL, to take advantage of the latest features but also (mainly?) to be sure to be up to date with the security patches.

This blog post centralizes the various URLs where to download the world’s most popular open source database.

MySQL Community Edition

If you’re looking for the latest binaries, client/server packages or source code, in 64/32 bits for Linux, Windows, Mac, Solaris, FreeBSD :

• All platforms : https://dev.mysql.com/downloads/mysql/
• MySQL on Windows (suite of tools) : https://dev.mysql.com/downloads/windows/

Linux Repository

On your Linux distros, the MySQL is often very old, (or worst you could download a totally different database).

The solution ? Install the MySQL APT/Yum/SUSE repository, which allows a simple and convenient way to install and update the latest GA, as well as the other products :

• Yum Repository : https://dev.mysql.com/downloads/repo/yum/
• APT Repository : https://dev.mysql.com/downloads/repo/apt/
• SUSE Repository : https://dev.mysql.com/downloads/repo/suse/

BTW please have a look on Ivan Ma‘s blog post : Installation of MySQL.

Others

Official MySQL container images, created, maintained and supported by MySQL :

• Docker : https://hub.docker.com/r/mysql/mysql-server/
• MySQL in Oracle Container Registry

GitHub repository, created, maintained and supported by MySQL :

• GitHub : https://github.com/mysql/mysql-server

---

MySQL NDB Cluster

MySQL NDB Cluster, is a high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. Available in the above repositories, as well as in these 2 dedicated URLs :

• NDB Cluster : https://dev.mysql.com/downloads/cluster/
• Docker (NDB cluster) : https://hub.docker.com/r/mysql/mysql-cluster/
• MySQL in Oracle Container Registry

---

MySQL Enterprise Edition

Oracle customers can sign in to their My Oracle Support account:

• MOS : http://support.oracle.com

Oracle Software Delivery Cloud (e-delivery) allows you to evaluate the MySQL Enterprise products :

• Edelivery : http://edelivery.oracle.com

Official MySQL Enterprise container images:

• MySQL in Oracle Container Registry

MySQL Enterprise Edition is also available in Oracle Cloud :

• MySQL Cloud Service : https://cloud.oracle.com/mysql

---

Old versions

Sometimes we do not have a choice, a particular version is mandatory. You will find it in the archives :

• MySQL Server : https://downloads.mysql.com/archives/community/
• MySQL NDB Cluster : https://downloads.mysql.com/archives/cluster/
• MySQL product archives : https://downloads.mysql.com/archives/

Thanks for using MySQL!

• Announcing MySQL Server 5.7.19, 5.6.37, and 5.5.57