Innodb Cluster or ClusterSet topologies already have secondary instances that can act as a failover for primary or also offload read requests. However, with MySQL 8.4, we now have the feasibility of adding a separate async replica to the cluster for serving various special/ad-hoc queries or some reporting purposes. This will also help offload read traffic away […]

Learn how Multi-Valued Indexing in MySQL 8.0 can enhance query performance by efficiently indexing and querying JSON arrays. Discover the benefits, implementation steps, and considerations for optimizing your MySQL database.

1. Multi-Valued Indexing in MySQL 8.0
2. Understanding Multi-Valued Indexes and their benefits
3. Creating Multi-Valued Indexes in MySQL …

A friend’s application started failing with MySQL causing error about timestamp columns and it needs urgent fixing from the database side. A timestamp column was not accepting the null values…

The post MySQL 8 – timestamp cannot be null and explicit_defaults_for_timestamp first appeared on Change Is Inevitable.

In the MySQL world, I am surrounded by some of the best database consultant, it brings them immense joy about having a “Friday”, so much so that they cannot believe…

The post Providing Friday Proofs to MySQL Consultants | Scripts Functions Routines first appeared on Change Is Inevitable.

From MySQL 5.7, we had a Multi-threaded Slave (MTS) Applier mechanism called LOGICAL_CLOCK to overcome the problems of parallel replication within a database.

To further improve the parallelisation mechanism, from MySQL 8 (5.7.22) we have write-set replication, so before going further , lets look at the difference between Logical clock (LC) and Writeset.

LOGICAL_CLOCK

Transactions that are part of the same binary log group commit on a master are applied in parallel on a slave. The dependencies between transactions are tracked based on their timestamps to provide additional parallelisation where possible.

WRITESET

Write-set is a mechanism to track independent transactions that can be executed in parallel in the slave. Parallelising on write sets has potentially much more parallelism than logical_clock ,since it does not depend …

We have many ways to install MySQL on linux machines such as source, binary and so on. But most of the Engineers always prefer default package managers (yum for RPM-Based distributions and apt for DPKG-Based distributions) for its ease of use and it can resolve all dependencies on its own. And of course, it is not possible to use package managers in environments where the internet is not allowed, but this is a different case. 

At some point, we need to install exactly specific version of MySQL for the following cases

• To create Production Replicas 
• To simulate an Production Issue on similar kind of environment
• To configure Disaster Recovery(DR)/UAT Setup
• Compatibility with opensource tools ( Eg , …

We will have a look at a new feature in MySQL 8.0 called binlog encryption. This feature is available from the MySQL version 8.0.14 or above.

Our previous blogs discussed about table space encryption in MySQL and Percona servers. In Mydbops, we are giving high importance about achieving security compliances.

The binary log records changes made to the databases so that it can be used to replicate the same to the slaves and also for the point in time recovery (PITR). So, it means that if someone has access to the binary logs, they can reproduce our entire database in many forms. As a DBA, we need to make sure that the binary log files are protected from users who are having access to the file system and also, log files need …

In my previous blog, I have explained how the MySQL clone plugin works internally. In this blog, I am going to do a comparison of  Backup and Recovery speed of MySQL clone plugin with other available mysql open source backup tools.

Below tools are used for speed comparison of Backup and Recovery,

1. Clone-Plugin
2. Xtrabackup
3. mysqldump
4. mydumper with myloader
5. mysqlpump

Test …

… or what I should keep in mind in case of disaster

To retrieve and maintain in SQL format the definition of all tables in a database, is a best practice that we all should adopt. To have that under version control is also another best practice to keep in mind.

While doing that may seem redundant, it can become a life saver in several situations. From the need to review what has historically changed in a table, to knowing who changed what and why… to when you need to recover your data and have your beloved MySQL instance not start…

But let’s be honest, only a few do the right thing, and even fewer keep that information up to date. Given that’s the case, what can we do when we have the need to discover/recover the table structure?

From the beginning, MySQL has used some external files to describe its internal structure.

For instance, if I have a schema named windmills and a table …

With the next version of MySQL that is MySQL 8, there is a very nice feature of creating "roles" which can be assigned certain privileges and then these roles can be assigned to users, thus helping us in maintaining the principle of least privilege.



It makes our life as developers and DBAs easier as we do not have to remember what are the specific privileges assigned to different users. These "roles" can simply be assigned or de-assigned from users as necessary.

The setup process is pretty easy and intuitive.

1. Create Role:

CREATE ROLE 'read', 'write', 'read_write';

Which essentially …