As a Database Engineer, One of the biggest challenges in day-to-day activity is performing DDL on high-traffic and transaction-intensive tables. It will become overhead when handling a large number of servers/shards.

As a standard process, we will first deploy the changes in DEV and QA before deploying them in production.

In the sharded environment, It will become a heavy overhead to maintain the schema changes in DEV, QA, and PROD servers. Since we will have multiple servers in the sharding.

To overcome this deployment supervision, the Skeema tool will help to deploy the changes in QA, DEV, and PROD in a safe and parallel ( for Shards ) as well.

This blog focuses on the basic operation of Skeema, will have a series of blogs on Skeema

1. About the …

This is one of the exciting troubleshooting related to XA transactions let’s dive in. Let me provide a few backgrounds.

For one of our clients, when we try to drop a table, it is waiting for metadata lock. On debugging it is one of the XA transactions is holding the shared write lock and causing metadata lock on all the other local transactions.

Let us view the metadata locks from the Performance Schema.

Amazon Web Services (AWS) very recently(March 02, 2022) announced the GA of its new RDS feature “Readable standby with Multi-AZ deployments” for MySQL. Yes !! you heard it right you can now use the standby instances created with Multi-AZ deployments for failover as well as for Read-scaling starting with version 8.0.26 and later for MySQL in RDS

Launching a MultiAZ Cluster

Now let us see how to launch this readable-Multi AZ cluster?

Region Availability: As this is a new feature now it is currently limited to the regions US-EAST-1 (N.Virginia), US-WEST-1 (Oregon), and EU-WEST-1 (Ireland), this list would be extended progressively

VPC requirement:

Before launching the instance, you should have SUBNET created for 3 AZ(Availability Zone) within the VPC since the cluster instances would be spawn across 3AZ by default

Hereunder the “Engine Option” …

Rollback is an operation, which changes the current state of the transaction to the previous state. Undo logs are generally required if we want to roll back any of the uncommitted transactions and it plays a major role in Isolation.

For any changes made during a transaction, it must be stored priorly, because they are required if we choose to roll back the transaction.

Entries are made in undo logs when data modifications are done. If a transaction modifies data with SQL commands, It will create discrete undo logs for each operation. Once a transaction is committed MySQL is free to purge the undo logs created in that transaction. 

To know more about undo logs, you can check our previous blogs on overview to undo logs.

Usually, the Rollback process will take more time than the original operation. Because …

While exporting the table with MySQL native utility, we don’t have any control on the process, and also there will be no progress update as well on the process completion. So when exporting the larger table will consume high resource utilization and also the disk space usage will also be high.

MySQL shell utility will make the process easier. It will export the table and we can import the data back with a parallel thread and also will provide the current progress status on export/import progress.

util.exportTable() utility was introduced in Shell – 8.0.22 version, will export the data in a controlled manner. We can store the data in either local or Cloud Infrastructure Object Storage bucket as well.

We will see about the compression ratio along with the time taken for native MySQL vs Shell utility

Feature :

• Compression
• Progress status
• Supported output …

We all tried various alternative methods for modifying the table structure, but pt-online-schema-change (pt-osc) is the most convenient and preferred method for performing the alter online. It has more granular control too. But it may lead to data loss if proper precautionary steps are not taken care of.

In this blog, we are going to modify a column to a unique key using pt-osc, below I have shared the table structure.

mysql> show create table test\G
* 1. row *
Table: test
Create Table: CREATE TABLE test (
Personid int(11) NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255) DEFAULT NULL,
Age int(11) DEFAULT NULL,
PRIMARY KEY (Personid)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

I have inserted the data of 1000 rows using …

Aurora has a salient feature “Parallel query“, Which will be more beneficial for analytical workload environments.

Before going to deep dive on this particular feature, let us understand the basis of Aurora.

Aurora Archiecture

Key feature

• The key feature of Aurora is simple data synchronisation among the nodes. The sync latency will be too low when compared to RDS because the synchronisation is happening on storage volumes among the nodes. Also all the server will available in different zone, even when a zone goes down we can able to maintain will other server present in other zone with auto failure.

• Auto healing volume, Each …

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 , …

In my previous Blog i have explained how we can integrate MySQL tools ( Percona Tool kit ) with systemd service, At Mydbops works with multiple clients ,we get various requirements from customers a few may be a complex ones ,one of the requirement was to setup a binlog server and copy the Production binlogs instantly .

To satisfy customer request i have made a simple shell script and integrated in systemd service which does a similar job as of Maxscale Binlog Server ( Pervious blog )

In this blog i will explain how to setup a simple binlog server.

Pre-requites for setting up the Binlog server.

• A standalone remote server with required disk.
• The mysqlclient to be …