Explore the powerful features of Aurora Serverless V2 for MySQL in this informative blog series. Learn about read-only scaling, parameter support, and cost performance. Compare costs between Provisioned Aurora and Aurora Serverless V2. Discover key takeaways for optimizing your MySQL deployment on the cloud. Read now!

1. Read-only Scaling
   1. Failover replicas
   2. …

Discover how to troubleshoot a MySQL replica IO thread stuck in a connecting state. Learn about the replication architecture, security group rules for AWS EC2 instances, and how to address common issues like network restrictions and bind address configuration.

MySQL is a powerful database management and a widely used cloud database service. One of its key features is the ability to create replicas of a master database to improve its availability and scalability. However, at times the IO thread in a MySQL replica may get stuck in a connecting state, which can cause replication issues and affect the overall data consistency …

Default settings can help you get started quickly – but they can also cost you performance and a higher cloud bill at the end of the month. Want to save money on your AWS RDS bill? I’ll show you some MySQL settings to tune to get better performance, and cost savings, with AWS RDS.

Recently I was engaged in a MySQL Performance Audit for a customer to help troubleshoot performance issues that led to downtime during periods of high traffic on their AWS RDS MySQL instances. During heavy loads, they would see messages about their InnoDB settings in the error logs:

[Note] InnoDB: page_cleaner: 1000ms intended loop took 4460ms. The settings might not be optimal. (flushed=140, during the time.)

This message is normally a side effect of a storage subsystem that is not capable of keeping up with the number of writes (e.g., IOPs) required by MySQL. This …

Greetings everyone!!!!!

Let’s begin the phase II of our blog on Aurora serverless-V2.

Part 1 – https://mydbops.wordpress.com/2022/05/22/exploring-auAurora-serverless-v2-for-mysql

Here, I have focused primarily on the migration strategies, hence this blog will be helpful for those who are in a stance to migrate towards serverless V2.

Before entering into the migration strategies, let’s take a look at the feature called “Mixed-Configuration”. and then discuss about the migration strategies

1. Mixed-Configuration: …

Recently, I was working with my colleagues Edwin Wang and Taras Onishchuk and found an interesting edge case involving a situation where a replica running Percona Server for MySQL 5.7, external to AWS Aurora instance version 2.10.2 (5.7-compatible), broke. I recreated the issue in my lab with a simple create database statement, as you will see below.

Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/share/percona-server/charsets/Index.xml' file' on query. Default database: 'lab'. Query: 'create database test'

The interesting thing to note here is the character set ‘#255’. You won’t see this available if you check the list of available collations in Percona Server for MySQL 5.7 for the UTF8MB4 character set.

mysql> SHOW COLLATION WHERE Charset = 'utf8mb4' and id = 255; 
Empty set (0.01 sec)

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.

• https://mydbops.wordpress.com/2016/01/31/faster-logical-backup-with-mydumper/
• …

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

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 …

In the last quarter of 2021, AWS released Aurora version 3. This new version aligns Aurora with the latest MySQL 8 version, porting many of the advantages MySQL 8 has over previous versions.

While this brings a lot of new interesting features for Aurora, what we are going to cover here is to see how DDLs behave when using the ONLINE option. With a quick comparison with what happens in MySQL 8 standard and with Group Replication.

Tests

All tests were run on an Aurora instance r6g.large with a secondary availability zone. The test was composed of:

        Four connections

• • #1 to perform DDL
  • #2 to perform insert data in the table I am altering
  • #3 to perform insert data on a different table 
  • #4 checking the other node operations

In the Aurora instance, a …

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