Backup ... backup... Backup... but of course.. you also need to monitor and test those backups often otherwise they could be worthless.  Having your MySQL binlogs enabled can certainly help you in times of an emergency as well.  The MySQL binlogs are often referenced in regards to MySQL replication, for a good reason, they store all of the queries or events that alter data (row-based is a little different but this an example). The binlogs have a minimal impact on server performance when considering the recovery options they provide.


[anothermysqldba]> show variables like 'log_bin%';
+---------------------------------+--------------------------------------------+
| …

The successful deployment of a Database Firewall depends on an effective policy. Oracle AVDF includes preconfigured firewall policies as described in the Firewall Policy page in the Policy tab of the Audit Vault Server console.

These include policies that log all SQL statements, or log only unique SQL statements. In addition, the Database Firewall policy editor enables you to design your own policies quickly and efficiently.

Policy rules can depend on any combination of the SQL statement type, name of the database user, IP address of the database client, operating system user name, client program name, or any exceptions you specify.

First policy and global concept

1. Log in to the Audit Vault Server console as an auditor, and click on the Policy tab:

Or how wsrep_on can bring you to have a cluster with usless data.

This is a WARNING article, and it comes out after I have being working on define internal blueprint on how to perform DDL operation using RSU safely.

The fun, if fun we want to call it, comes as usual by the fact that I am a curious guy and I often do things my way and not always following the official instructions.

Anyhow, lets us go straight to the point and describe what can happen on ANY MySQL/Galera installation.

The environment

The test environment, MySQL/Galera (Percona PXC 5.6.20 version).

The cluster was based on three nodes local no geographic distribution, no other replication in place then Galera.

Haproxy on one application node, simple application writing in this table:

Table: tbtest1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

High season is coming, how do you make sure that MySQL will handle the increased load? Stress tests could help with that, but it’s not a good idea to run them in a production environment. In this case Select_scan, Select_full_join and other MySQL counters could quickly give you an idea of how many queries are not performing well and could cause a performance degradation as the load goes up.

Select_scan from SHOW GLOBAL STATUS indicates how many full table scans were done since last MySQL restart. Scanning the entire table is a resource intensive operation. It also forces MySQL to store unnecessary data in the buffer pool, wasting memory and IO resources.

Full scan of a tiny table would be quite fast so missing indexes could stay invisible until the load rises or the dataset grows up. This could also be the case for developers who work with too small data sets on their dev boxes. To prevent performance issues all newly added …

Often enough I find MySQL benchmark results where the difference between results is 1% or even less and some conclusions are drawn. Now it is not that 1% is not important – especially when you’re developing the product you should care about those 1% improvements or regressions because they tend to add up. However with such a small difference it is very important to understand whenever this is for real or it is just the natural variance for your baseline test.

Take a look at this graph:
Click the image for a larger view)

This is the result for a simple in-memory, read-only “select by primary key” SysBench benchmark on dedicated physical hardware that is otherwise idle, simple 1 socket system. I tried to stabilize it as much as possible, for example …

There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master. The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of thousands of RBR events. The most common way this affects how we upgrade a replication hierarchy, is when we have the master running MySQL 5.5 and the slave running MySQL 5.6 and we have transactions involving DATETIME column(s). Tables with DATETIME columns will have different underlying structure when created on MySQL 5.5 versus when created on MySQL 5.6. Ideally you would avoid creating …

[…] Monitoring and Managing Amazon RDS Databases using MySQL … – May 17, 2012 · Last couple of week I was looking for an easier approch to manage the Databases created on Amazon RDS instances. I had to run through a tedious set of …… […]

There were two nasty MySQL replication bugs in two different 5.6 releases that would make it difficult to upgrade slaves to MySQL 5.6 while still connected to MySQL 5.5 master.

The first of those bugs is MySQL bug 72610 which affects 5.6.19. Essentially this bug is triggered when the table structure on the slave is different from the table structure on the master which leads to unnecessarily large amount of RAM usage while replicating events that affect that table. The amount of RAM used would generally be more noticeable when the replicated transaction consists of thousands of RBR events.

The most common way this affects how we upgrade a replication hierarchy, is when we have the master running MySQL 5.5 and the slave running MySQL 5.6 and we have transactions involving DATETIME column(s). Tables with DATETIME columns will have different …

With the query rewrite framework in the latest MySQL (Optimizer/InnoDB/Replication) labs release, you get the opportunity to author plugins that can rewrite queries. You can choose whether to rewrite the queries before and/or after parsing. Today I am going to walk you through how to write a pre-parse query rewrite plugin.

When would you want to use a pre-parse query rewrite plugin? The greatest benefit compared to post-parse rewrites — which I cover in a separate post — is the efficiency, especially the lack of overhead for those queries that are actually rewritten. Typical cases where you may want to write a pre-parse plugin are:

• When you want to remove certain specific clauses from queries. For example, perhaps you want to remove all ENGINE …

I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

[client]
user=root
password=secret

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

Warning: Using a password on the command line interface can be insecure.

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

[vagrant@localhost ~]$ mysqladmin -u root password
New password:secret
Confirm new password:secret
[vagrant@localhost ~]$ mysql -u root
ERROR 1045 (28000): Access denied for user …