The MySQL Replication was my first project as a Database
Administrator (DBA) and I have been working with Replication
technologies for last few years and I am indebted to contribute
my little part for development of this technology. MySQL supports
different replication topologies, having better understanding of
basic concepts will help you in building and managing various and
complex topologies. I am writing here, some of the key points to
taken care when you are building MySQL replication. I consider
this post as a starting point for building a high performance and
consistent MySQL servers. Let me start with below key
points Hardware. MySQL Server Version MySQL Server Configuration
Primary Key Storage Engine I will update this post with relevant
points, whenever I get time. I am trying to provide generic
concepts and it will be applicable to all version of MySQL,
however, some of the concepts are new and applicable to latest
versions …
Since MySQL 5.6, the digest feature of the MySQL Performance Schema has provided a convenient and effective way to obtain statistics of queries based on their normalized form. The feature works so well that it has almost completely (from my experience) replaced the connector extensions and proxy for collecting query statistics for the Query Analyzer (Quan) in MySQL Enterprise Monitor (MEM).
MySQL 8 adds further improvements to the digest feature in the Performance Schema including a sample query with statistics for each digest, percentile information, and a histogram summary. This blog will explore these new features.
…
[Read more]The second alpha build of Percona XtraBackup 8.0.2 is now available in the Percona experimental software repositories.
Note that, due to the new MySQL redo log and data dictionary formats, the Percona XtraBackup 8.0.x versions will only be compatible with MySQL 8.0.x and Percona Server for MySQL 8.0.x. This release supports backing up Percona Server 8.0 Alpha.
For experimental migrations from earlier database server versions, you will need to backup and restore and using XtraBackup 2.4 and then use mysql_upgrade from MySQL 8.0.x
PXB 8.0.2 alpha is available for the following …
[Read more]The release of MySQL 8.0 has brought a lot of bold implementations that touched on things that have been avoided before, such as added support for common table expressions and window functions. Another example is the change in how AUTO_INCREMENT (autoinc) sequences are persisted, and thus replicated.
This new implementation carries the fix for bug #73563 (Replace result in auto_increment value less or equal than max value in row-based), which we’ve only found about recently. The surprising part is that the use case we were analyzing is a somewhat common one; this must be affecting a good number of people out there.
Understanding the bug
The business logic of the use case is such the UNIQUE column found in a table whose id is managed by an AUTO_INCREMENT sequence needs to be updated, and this is done with a …
[Read more]MySQL Adventures: Reduce MySQL Memory Utilization With ProxySQL Multiplexing
In our previous post, we explained about how max_prepared_statement_count can bring production down . This blog is the continuity of that post. If you can read that blog from the below link.
How max_prepared_stmt_count bring down the production MySQL system
We had set the max_prepared_stmt_count to 20000. But after that, we were facing the below error continuously.
Can't create more than max_prepared_stmt_count statements (current value: 20000)
We tried to increase it to 25000, 30000 and finally 50000. But unfortunately, we can’t fix it and …
[Read more]MySQL Adventures: How max_prepared_stmt_count can bring down production
We recently moved an On-Prem environment to GCP for better scalability and availability. The customer’s main database is MySQL. Due to the nature of customer’s business, it’s a highly transactional workload (one of the hot startups in APAC). To deal with the scale and meet availability requirements, we have deployed MySQL behind ProxySQL — which takes care of routing some of the resource intensive SELECTs to chosen replicas. The setup consists of:
- One Master
- Two slaves
- One Archive database server
Post migration to GCP, everything was nice and calm for a couple of weeks, until MySQL decided to start misbehaving and leading to an outage. We were able to quickly resolve and bring the system back online and what follows are lessons from this experience.
The configuration of the …
[Read more]
The traditional way to monitor replication in MySQL is the
SHOW SLAVE STATUS command. However as it will be
shown, it has its limitations and in MySQL 5.7 and 8.0 the MySQL
developers have started to implement the information as
Performance Schema tables. This has several advantages including
better monitoring of the replication delay in MySQL 8.0. This
blog discusses why SHOW SLAVE STATUS should be
replaced with the Performance Schema tables.
The Setup
The replication setup that will be used for the examples in this blog can be seen in the following figure.
…
[Read more]Jetzt sind auch noch die letzten MariaDB und MySQL Schulungstermine für 2019 festgelegt und veröffentlicht.
Mit unseren drei Schulungspartnern in Essen, Köln und Berlin bietet FromDual zur Zeit insgesamt 12 öffentliche Schulungen zum Thema MariaDB und MySQL an.
Es sind dies:
- 7 MariaDB/MySQL für Fortgeschrittene (Januar, April, Mai, Juni, Juli, Oktober und November)
- 1 MariaDB/MySQL für Einsteiger (Jan, diese Schulung findet sicher statt!)
- 4 …
Jetzt sind auch noch die letzten MariaDB und MySQL Schulungstermine für 2019 festgelegt und veröffentlicht.
Mit unseren drei Schulungspartnern in Essen, Köln und Berlin bietet FromDual zur Zeit insgesamt 12 öffentliche Schulungen zum Thema MariaDB und MySQL an.
Es sind dies:
- 7 MariaDB/MySQL für Fortgeschrittene (Januar, April, Mai, Juni, Juli, Oktober und November)
- 1 MariaDB/MySQL für Einsteiger (Jan, diese Schulung findet sicher statt!)
- 4 …
There are multiple way to setup replication with MySQL 8.0 and our replication offer as never been so rich: asynchronous, semi-synchronous, group replication, multi-source, … and much more options !
But if you want to setup a very quick Master-Slave environment from scratch for a quick test (you can always use dbdeployer), here are some commands to make it right the first time
Requirements
You need to have MySQL 8.0 installed and running on both servers
and with the same initial data (a fresh install for example).
Here we use mysql1 and mysql2. We will
also use GTID as it’s much more convenient.
Servers Configuration
Let’s setup mysql1 first:
mysql1> SET PERSIST server_id=1; mysql1> SET …[Read more]