Nowadays everything is getting digitalized and migration toward the cloud is at its peak. There is a high chance of data leaks if we don’t tighten the security of the Database servers. It is mandatory to secure the database by restricting access to Database. Although we have restricted the access. We need to monitor the activity of the user to prevent the unwanted usage of data.

Security will be split into three layers

1. Network-level security.
2. OS level security.
3. Database level security.

OS level security will be handled by the AWS team. Since It is managed by the AWS Team. So all the security patching, minor version upgrades of OS, and kernel tuning will be governed by the AWS infra team.

Network-level security and database-level security are owned by the end user.

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 …

Over the decades we have been reading the MySQL error log from the server system file, if there are any issues in MySQL or any unknown restart happened , generally we look at the mysql error log.

By default MySQL error log can be found in the default path /var/log/mysqld.log , or it can be explicitly configured using the variable log_error.

Few drawbacks using MySQL error log as FILE

• Possibility of missing genuine errors while reading lengthy information.
• Filtering of errors for the particular date and timeframes.
• Cannot provide the DB server access to developers because of fear of mishandling DB servers.

To overcome the above issues , from MySQL …

Recently one of our customers ran into an issue, wherein a bad actor(code) from the application had made the wrong update to 16 M records of a critical table in the database, causing the entire production process to go down. The application Team was able to find the bad actor and block it, our Remote DBA was involved in the Data Recovery/Rollback.

Here I would like to discuss possible recovery methods for the above said scenario

Delayed Slave:

A simple and effective way to recover is by using a delayed slave, RDS started supporting this feature from version 5.6.40 and 5.7.22 i.e., you can induce a SQL thread delay-interval for applying the writes to a slave, detailed implementation is covered in our blog here. It’s …

In my day to day tasks as a DB Engineer at Mydbops we have been using multiple MySQL tools for multiple use cases to ensure an optimal performance and availability for servers managed by our Remote DBA Team.

A tool like pt-online-schema can be used for any DDL changes ( Overview to DDL algorithm ), if any tool which needs to scheduled for longer period we tend to use screen or cron.

Some of the problems we face when we demonise the process or use screen for running processes.

• The daemon process gets killed when the server reboot happens. …

This presentation covers about the basics of Group replication in MySQL 8.0. Group replication world on the basis of Multi Paxos ( a variant of it ). The author covers the leader election algorithm , switching the primary node and replication modes in this presentation.

Group Replication in MySQL 8.0 ( A Walk Through ) from Mydbops

Like to have this high availability MySQL solution configured on your environment. Mydbops Consultants can provide the best solution with right cost for performance.

Data security plays a major role in current age. Privacy matters a lot. Data masking is one of the Key Features when comes to security.

• In MySQL community versions if you want to mask your data, You can go with a Maxscale load balancer.
• They introduced a new masking filter on the Maxscale 2.1 version.
• My colleague Prasanth written about column-level data masking. For reference click here Column Level Data Masking.
• In MySQL 8.0 Enterprise they have introduced a new security feature called data masking.
• This feature was introduced in MySQL 8.0.13 Enterprise Edition and it provides data masking and de-identification capabilities.

What is Masking ?

• Transformation of existing data to mask it and remove …

This blog is about one of the issues encountered by our Remote DBA Team in one of the production servers. We have a setup of MySQL 5.7 Single Primary (Writer) GR with cluster size of  3 . Due to OOM, the MySQL process in the primary node got killed, this repeated over the course of the time.

We all know about the OOM (out of memory), theoretically, it is a process that the Linux kernel employs when the system is critically low on memory.

In a dedicated DB server, when the OOM triggers the direct impact will be on mysqld process since it will be the most memory consuming one.

Going forward will look into the detailed analysis made to tackle the issue of OOM.

DB Environment:-

• Service – Group Replication Cluster
• Cluster Nodes – 3
• GR mode – Single Primary …