Showing entries 1 to 10 of 18
8 Older Entries »
Displaying posts with tag: DBA Insights (reset)
pt-online-schema-change resulted in missing MySQL triggers ?

Recently, while doing a test process to examine resource usage and the time required to alter the table using pt-osc, it leads to a loss of triggers even though the –preserve-triggers option is specified. I made the decision to recreate the identical circumstance so that everyone could see it.

Test environment : 

OS: Amazon Linux 2

MySQL version: 5.7.40

pt-online-schema-change version: 3.1.0

Before digging deeper, we must first obtain the whole picture. Here is an illustration of the table’s structure.

Table structure:

[Read more]
Generate Invisible Primary Key (GIPK) MySQL 8.0

The Primary key is like the hero of a row, which has more beneficial features in the table while performing any task on the table.

The DBA knows the importance of the primary key in the table and how to handle it.

  1. Notable features of having a primary key:
  2. Requirements:
  3. Enabling GIPK:
  4. Handling GIPK:
[Read more]
Synopsis of Mydbops 17th edition of MyWebinar

We’re ecstatic and energised about the prospect of sowing seeds of knowledge in our opensource database community.

Thank you to everyone who joined us for the 17th edition of MyWebinar. We hope that our time together helped to elevate and seam your work to perfection. We’re committed to giving back to the opensource database community in the coming days by presenting more LIVE events.

Maha Lakshmi Ganapathineedi from Mydbops was successful in making this event more interactive, engaging, and, most importantly, productive for the audience. Thank you for your continuous contribution to the opensource community.

Topic: Redo …

[Read more]
Stream MySQL data with mydumper

Mydumper supports streaming of backups right from version 0.11.3 and the latest version Mydumper 0.12.3 it enabled its support for compressed streaming backup. This was the most awaited feature added to Mydumper, making it a more powerful tool for data migration to RDS or Cloud.

If you are hearing mydumper for the first time, then let’s have a quick catch-up on what Mydumper is and what it does exactly.

Mydumper is a multithread logical backup and restores tool for MySQL and its forks. To know more you can refer to our previous blogs/presentations below.

[Read more]
Exploring Aurora Serverless V2 for MySQL

Aurora Serverless V2 is generally available around the corner recently 21-04-22 for MySQL 8 and PostgreSQL, with promising features that overcome the V1 disadvantages. Below are those major features

Features

  • Online auto instance upsize (vertical scaling)
  • Read scaling (Supports up to 15 Read-replica)
  • Supports mixed-configuration cluster ie, the master can be normal Aurora(provisioned) and readers can be in serverlessv2 and vice versa
  • MultiAZ capability (HA)
  • Aurora global databases (DR)
  • Scaling based on memory pressure
  • Vertically Scales while SQL is running
  • Public IP allowed
  • Works with custom port
  • Compatible with Aurora version 3.02.0 ie., >= MySQL 8.0.23 (only supported)
[Read more]
AWS MySQL Security on RDS: Database Level

In the previous blog, we have gone through about network-level security in RDS. In this blog, we will see about the Database level security in RDS.

After network-level restriction to host, we can’t allow a user to connect to the database from anywhere over the internet. We need to restrict user access at the Database level as well. Need to create a user with a restricted host along with a strong password to avoid cracking of password. In RDS we have a special feature named Identity and Access Management (IAM).

We need to monitor the user activity as well in the Database. Because the user may wrongly execute the query in the server which leads to data loss or production outage. The user activity has to be monitored as per the compliances. We can achieve this by enabling the audit log in the RDS.

By …

[Read more]
Introduction to AWS MySQL Security on RDS : Network

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.

Security
[Read more]
Faster Load data outfile in MySQL

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 …
[Read more]
Troubleshooting an unique key addition during pt-online-schema-change

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 …

[Read more]
Introduction to MySQL Parallel query in AWS Aurora

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 …
[Read more]
Showing entries 1 to 10 of 18
8 Older Entries »