We know the MySQL Shell is the advanced client tool for communicate to the MySQL server . MySQL Shell has lot of features like InnoDB Cluster control , InnoDB ReplicaSet, MySQL Shell utilities , MySQL server management etc … Today I came to know, MySQL shell helps lot in monitoring as well ( query, threads, resource consumption , locking ) .

In this blog I am going to explain how to use the MySQL Shell for monitor your server .

MySQL Shell provides two hot commands \show and \watch for monitor the MySQL server and report generating purpose .

\show : Execute the report with the provided options

\watch : Execute the report in loop with provided options

\show with thread example :

\show with query example :

You can execute any query within the double quotes .

\show with threads example :

When I tell people that they can use MySQL without SQL they tend to be skeptical.  But with the MySQL Document Store you can do just that with a new NoSQL API and in this case there is no structured query language.pre-FOSDEM MySQL Days (which is sold out and there is a waiting list) is my tutorial on using the Document Store.  Those in my session will be able to see how to use the MySQL Shell (mysqlsh) to connect to a MySQL server and save data without have to do the many things a DBA used to have to do in the past such as normalize data, setup relations, and several other tasks.  Plus the schema-less Document Store means you can alter your data needs without having to do an endless series of ALTER TABLES.

MySQL InnoDB Cluster in 8.0.19 has new version of MySQL InnoDB Cluster MetaData.
MySQL Shell 8.0.19 full function has to be running with V2 metadata.

This is the tutorial for an sample upgrade of MySQL 8.0.18 to MySQL 8.0.19

Reference :
https://mysqlserverteam.com/upgrading-mysql-innodb-cluster-metadata/


The following InnoDB Cluster is running with MySQL 8.0.18.


 Running MySQL Shell 8.0.19 with MySQL InnoDB Cluster 8.0.18 (which the metadata version is @1.0.1) with command dba.getCluster() results in Warning :

WARNING: No cluster change operations can be executed because the installed metadata version 1.0.1 is lower than the version required by Shell which is version 2.0.0. Upgrade the metadata to remove this restriction. See \? …

From the initial release, one of the biggest complaints I had about Group Replication is that it allowed “stale” reads and there was no way to prevent them or to even know that you read “stale” data. That was a huge limitation. Thankfully, Oracle released features to control the consistency levels, and it was exactly a year ago! I don’t know about you, but I personally was confused by naming it group_replication_consistency=’AFTER’ or ‘BEFORE’.

So now I want to try to make sense of it and share my understanding (even if it is one year later).

Setup:

• 3 Node Percona Server for MySQL 8.0.18 running in …

Introduction
Before I talk about installation of MySQL inside docker, it's more important to know
what is Docker?
- Docker is a tool designed to create , deploy ,and run an application any where.
-It allow us to package up application with all requirements such as libraries and other dependencies and ship it all as a PACKAGE.
who uses Docker?
Developer : Docker enables developer to develop application without spending much time on IT infrastructure.
Sysadmin :-Docker enables sysadmin to streamline the software delivery, such as develop and deploy bug fixes and new features without any roadblock. 
Enterprise :-Docker works in the cloud , on premise ,and supports both traditional …

As the second part of the earlier post Give Love to Your SSDs – Reduce innodb_io_capacity_max! we wanted to put together some concepts on how InnoDB flushing works in recent Percona Server for MySQL versions (8.0.x prior to 8.0.19, or 5.7.x). It is important to understand this aspect of InnoDB in order to tune it correctly. This post is a bit long and complex as it goes very deep into some InnoDB internals.

InnoDB internally handles flush operations in the background to remove dirty pages from the buffer pool. A dirty page is a page that is modified in memory but not yet flushed to disk. This is done to lower the write load and the latency of the transactions. Let’s explore the various sources of flushing inside InnoDB.

Idle Flushing

We already discussed the idle flushing in the previous post …

In part 1, we took a brief, high-level look at the various protocols and mechanisms used to keep the Data Dictionary (DD) of MySQL servers connected to a MySQL Cluster in synchronization with each other and with the NDB Dictionary.…

Tweet Share

As a part of Mydbops Consulting we have a below problem statement from one of our client.

“We have a high powered server for reporting which in turn powers our internal dashboard for viewing the logistics status.Even with a high end hardware, we had a heavy CPU usage and which in turn triggers spikes in replication lag and slowness. Below is the hardware configuration.“

OS : Debian 9 (Stretch)

CPU : 40

RAM : 220G (Usable)

Disk : 3T SSD with 80K sustained IOPS.

MySQL : 5.6.43-84.3-log Percona Server (GPL)

Datasize : 2.2TB

Below is the graph on CPU utilisation from Grafana.

Since the work load is purely reporting(OLAP) we could observe a similar type of queries with different ranges. Below is the Execution plan of the query. It is a join query over 6 tables.

Explain Plan:

 …

April 2018 is not just a date for the MySQL world. MySQL 8.0 was released there, and more than 1 year after, it’s probably time to consider migrating to this new version.

MySQL 8.0 has important performance and security improvements, and, as in all migration to a new database version, there are several things to take into account before going into production to avoid hard issues like data loss, excessive downtime, or even a rollback during the migration task.

In this blog, we’ll mention some of the new MySQL 8.0 features, some deprecated stuff, and what you need to keep in mind before migrating.

What’s New in MySQL 8.0?

Let’s now summarize some of the most important features mentioned in the official documentation for this new …

The snapshot in Debezium will do a historical data load from the source database to the Kafka topics. But generally its not a good practice to this if you have a huge data in your tables. Recently I have published many blog posts to perform this snapshot from Read Replica(with/without GTID, AWS Aurora). One guy commented that, in GCP the MySQL managed service is called CloudSQL. There we don’t have much control to stop replication, perform the modifications that we want. So how can we avoid snapshots in CloudSQL and take debezium snapshots from CloudSQL Read Replica? I have spent some time today and figured out a way to do this.

The Approach:

We can’t enable binlogs on read replica. So we have to setup an external read replica for this. If the external replica is a VM, then we can enable the log-slave-updates with GTID. Then we can …