Ok, so now we’re got our InnoDB Cluster a-clustering, MySQL Router a-routing, now we need some disaster to be a-disaster-recovering…

A foreword first.

If you’re looking to use Enterprise Backup to recover a single node and restore that node back into an existing InnoDB Cluster, LeFred takes you through that one nicely here.

Preparing for backup

On our single primary server, the one that allows write, which was ic2/10.0.0.12 in my case:

mysql -uroot -poracle << EOF 
SET sql_log_bin = OFF; 
 create user 'backup'@'%' identified by 'oracle';
 grant all on *.* to 'backup'@'%';
SET sql_log_bin = ON; 
EOF

Let’s create something to backup (if you haven’t already done so of course):

mysqlsh --uri …

Want to setup InnoDB Cluster and be prepared for a Disaster Recovery scenario? Get ready:

Here’s a way to set up InnoDB Cluster using the 3 environments, on Oracle Linux 7.2, 5.7.19 MySQL Commercial Server, MySQL Shell 8.0.3 DMR, MySQL Router. As this is the first blog post for a complete disaster recovery scenario of InnoDB Cluster, we’ll also be installing MySQL Enterprise Backup.

If you’re new to InnoDB Cluster then I’d highly recommend looking at the following to understand how it works and what Group Replication, Shell & Router are.:

• https://dev.mysql.com/doc/refman/5.7/en/mysql-innodb-cluster-production-deployment.html
• …

Sometimes when you are using a MySQL InnoDB Cluster, you might encounter some performance issue because one node becomes dramatically slow.

Why ?

First of all, why ? A node can apply the transactions slower than the other nodes for many different reasons. The most frequents are for example, slower disks (remember, it’s advised to have nodes with the same specifications), but if you are using a RAID controller with a BBU, during the learning cycle, the write performance can decrease by 10 or even more. Another example could be an increase of IO operations that will flood the full IO capacity of the system. Making a local backup or sharing the server resources with some other components could lead in such behavior.

Flow Control

To avoid to have a node lagging to much behind and try to sustain the same throughput all over the cluster, Group Replication uses a flow control mechanism ( …

Introduction Last week, one of my blog readers asked me of a way to reuse the @Id mapping so that it won’t have to be declared on each an every entity. Because this is a good opportunity to introduce @MappedSuperclass, I decided to answer the question with a blog post. Domain Model Assuming we are … Continue reading How to inherit properties from a base class entity using @MappedSuperclass with JPA and Hibernate →

Last time we looked at SKIP LOCKED where rows locked by another process were skipped. NOWAIT informs the server to return immediately IF the desired rows can not be locked.

How To Use NOWAIT Start a connection to your MySQL 8 server, start a transaction, and make a query to lock up part of the data. mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE;
On a second connection, start a transaction and issue a SELECT query with NOWAIT. mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE countryCode = 'USA' FOR UPDATE NOWAIT;
The second connection will get a message saying 'statement aborted because lock(s) could not be acquired immediately and NOWAIT is SET
So if you can come back later to lock the records or just can not …

Having a load balancer or reverse proxy in front of your MySQL or MariaDB server does add a little bit of complexity to your database setup, which might lead to some, things behaving differently. Theoretically, a load balancer which sits in front of MySQL servers (for example an HAProxy in front of a Galera Cluster) should just act like a connection manager and distribute the connections to the backend servers according to some balancing algorithm. MySQL, on the other hand, has its own way of managing client connections. Ideally, we would need to configure these two components together so as to avoid unexpected behaviours, and narrow down the troubleshooting surface when debugging issues.

If you have such setup, it is important to understand these components as they can impact the overall performance of your database service. In this blog post, we will dive into MySQL's max_connections and HAProxy maxconn options …

SKIP LOCKED is a new feature in MySQL 8 that many will find valuable.  If allows you to not wait about for locked records and work on what is available -- the unlocked records.
How To Use SKIP LOCKED The MySQL world database has 274 records in the city table where the countryCode field equals 'USA' there are 274 records. From past interactions, we somehow know there are some records with the ID field greater than 4000.
On MySQL shell number 1, start a transaction and lock some records mysql>START TRANSACTION; mysql>SELECT * FROM city WHERE ID > 4000 and countryCode = 'USA'; There will be 66 records.
On MySQL shell number number 2, start a transaction and lets try to lock the records starting at IS 3990 and up. mysql>START TRANSACTION; mysql>SELECT FROM city WHERE id > 3990 and countryCode='USA'
FOR UPDATE SKIPPED LOCKED; There will be 10 records.  The records …

The MySQL Enterprise Audit plug-in is part of the MySQL Enterprise Edition (available through a paid license). Basically, Enterprise Audit tracks everything that is happening on your MySQL server, and can be used to protect/detect the misuse of information, and to meet popular compliance regulations including HIPAA, Sarbanes-Oxley, and the PCI Data Security Standard.

MySQL Enterprise Audit uses the open MySQL Audit API to enable standard, policy-based monitoring and logging of connection and query activity executed on specific MySQL servers. Designed to meet the …

Here's something that has puzzled me for several weeks.
Right after migrating  MySQL from 5.6 to 5.7, we started experiencing random xtrabackup failures on some, but not all, of our slaves.
The failures were only happening when taking an incremental backup, and it would always fail on the same table on each slave, with errors similar to the following:

171106 13:00:33 [01] Streaming ./gls/C_GLS_IDS_AUX.ibd
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for the remaining bytes.
InnoDB: 262144 bytes should have been read. Only 114688 bytes read. Retrying for …

Introduction Last week, Burkhard Graves asked me to answer the following StackOverflow question: And, since he wasn’t convinced about my answer: I decided to turn it into a dedicated article and explain how UPSERT and MERGE work in the top 4 most common relational database systems: Oracle, SQL Server, PostgreSQL, and MySQL. Domain Model For … Continue reading How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL →

The post How do UPSERT and MERGE work in Oracle, SQL Server, PostgreSQL and MySQL appeared first on Vlad Mihalcea's Blog.