This blog post will discuss how to use the MySQL super_read_only system variable.

It is well known that replica servers in a master/slave configuration, to avoid breaking replication due to duplicate keys, missing rows or other similar issues, should not receive write queries. It’s a good practice to set

read_only=1

 on slave servers to prevent any (accidental) writes. Servers acting as replicas will NOT be in read-only mode automatically by default.

Sadly, 

read_only

 has an historical issue: users with the SUPER privilege can override the setting and could still run DML queries. Since Percona Server 5.6.21 and MySQL 5.7.8, however, you can use the

super_read_only

 feature to extend the

read_only

One of the main tasks that any DBA has to deal with is certainly data retention. Especially when your developers like to store logging and tracking information inside the database. This is not something that I would recommend (don't try it at home!) but when you're late to the party (read: someone had taken this path before you joined) , then you'll have to live with it.

Data retention in MySQL is usually applied by partitioning the table by RANGE on a selected column, usually containing the row creation timestamp. New partitions are created in advance for the coming days or weeks or months, and a script is used that will run nightly and drop partitions that are older than a set retention.

There are many tools that can be used to automate data retention; I have chosen the excellent pdb-parted, a nice and cozy perl script that you can find in the PalominoDB repository on GitHub (since PalominoDB is no longer in existence, …

A new Group Replication plugin labs release has came out, a little over a week ago. Exciting news! This shows that the team has been working hard to shape up the plugin so that it meets the required feature set, performance and quality standards to be released as GA.…

I have a new blog post on blog.booking.com describing MariaDB 10.1 Optimistic Parallel Replication (with benchmark results):

Evaluating MySQL Parallel Replication Part 4: More Benchmarks in Productionhttp://blog.booking.com/evaluating_mysql_parallel_replication_4-more_benchmarks_in_production.html

If you want to know more about MySQL/MariaDB Parallel Replication and if you are attending 

Optimiser is the brain of the RDBMS. Optimiser decides the right access method , algorithms , join order and right index to be used for better execution of the query. This blog is made to shed some lights on Cost based optimiser in MySQL 5.7. The cost or statistics are stored in the data dictionary .

What is cost-based optimization ?

• The cost model is based on estimates of cost various operations occur during query execution.

• The optimizer has a set of default “cost constants” it will make decision on execution plans.

• In MySQL 5.7, the optimizer has addition a database of cost estimates to use during the execution plan.

• These cost estimates are stored in server_cost & engine_cost tables in MySQL schema. For more details Cost Model

MySQL …

If there is something that I love about information technology is the incredible amount of unpredictable ways that bugs can bite you where and when you less expect it.

This one I'm about to describe is a serious one, yet there has been a very quiet response from Percona bug team at Launchpad, where I first submitted it (because we use Percona server here). If you have read my other posts you know by now that I am a laz... err, busy guy so bear with me if only today I have verified that this is, in fact, an upstream bug and have raised a bug to Oracle too.
It affects 5.6.33, latest version at the time of this article.

Anyways, the bug started to manifest itself in the form of complete stalls of our main cluster, which was happening at different times and apparently in a way that was unrelated to the amount of traffic hitting the master. When stalling, system CPU time was topping 100% of total available …

With MySQL 8.0, we are bringing in an important change in the way user management DDLs are executed.

Background

User management DDLs cover following user management actions:

• Creating user/role (CREATE USER [IF NOT EXISTS] / CREATE ROLE [IF NOT EXISTS])
• Altering user account properties (ALTER USER [IF EXISTS])
• Dropping user/role from database (DROP USER [IF EXISTS] / DROP ROLE [IF EXISTS])
• Granting/Revoking privileges to/from a user/role (GRANT/REVOKE)

Each of these administrative action can handle multiple entities as a part of single command.…

You must have heard about the CVE-2016-6662, the recent zero-day exploit exposed in most of MySQL and its variants. The vulnerability flaw can be exploited by a remote attacker to inject malicious settings into your my.cnf,. you can read about the details here.

At the moment, all supported MySQL vendors by ClusterControl (Oracle, Codership, Percona, MariaDB) have been patched with a bug fix and released in their respective package repository:

With Oracle Open World behind us, we are now getting ready for the next big event, i.e. the European edition of PerconaLive. I am going to be a presenter three times:

• MySQL operations in Docker is a three-hour tutorial, and it will be an expansion of the talk by the same title presented at OOW. Attendees who want to play along can do it, by coming prepared with Docker 1.11 or later and the following images already pulled (images with [+] are mandatory, while [-] are optional):

  …

FRM-less, transactional data dictionary is arguably the most significant feature change announced MySQL 8.0 development release. The server still has two separate dictionaries (MySQL, InnoDB) but the infamous FRM files are finally being replaced with transactional, InnoDB-based storage.

While this is interesting for various reasons, this particular post will focus on the impact this change has on data dictionary performance.
Test configuration Server configuration:

• Hardware: Amazon EC2 m4.4xlarge, 500GB, 15K IOPS
• OS: Debian Jessie
• MySQL versions tested: 5.7.15, 8.0.0-dmr
• Notable customer MySQL configuration values:
  • innodb_buffer_pool_size = 4GB
  • innodb_log_file_size = 256MB
  • innodb_buffer_pool_dump_at_shutdown = 0
  • innodb_buffer_pool_load_at_startup = 0
  • …