Azure MySQL Flexible Server Architecture and Best Practices

Azure MySQL Flexible Server offers a highly available and scalable database service that's designed to meet the needs of modern applications. It's built on a fully managed platform that automates many of the routine tasks associated with database administration.

One key feature of Azure MySQL Flexible Server is its ability to scale up or down as needed, allowing you to quickly adapt to changing workloads. With this flexibility, you can ensure that your database is always performing optimally.

To get the most out of Azure MySQL Flexible Server, it's essential to understand its architecture and best practices. By doing so, you can ensure a smooth and efficient experience for your users.

Azure MySQL Flexible Server uses a master-slave replication architecture, which provides high availability and data redundancy. This architecture is designed to minimize downtime and ensure that your database is always accessible.

The Flexible Server deployment option offers three compute tiers with different capacities to support your database workloads. These tiers are best suited for specific types of workloads.

The Burstable tier is ideal for low-cost development workloads and low-concurrency workloads that don't need full compute capacity continuously. The General Purpose and Business Critical tiers are better suited for production workloads that require high concurrency, scale, and predictable performance.

You can build your first app on a Burstable tier at a low cost and then adjust the scale to meet the needs of your solution. For details, see Azure Database for MySQL - Flexible Server service tiers.

Here are the key characteristics of the Flexible Server architecture:

When choosing a deployment option for your database workloads, consider the Flexible Server architecture. It offers three compute tiers with varying capacities to support your needs.

The Burstable tier is perfect for low-cost development workloads and low-concurrency workloads that don't require full compute capacity continuously. This tier is ideal for starting small and scaling up as needed.

The General Purpose and Business Critical tiers are better suited for production workloads that require high concurrency, scale, and predictable performance. These tiers provide more resources and are designed for high-demand applications.

You can build your first app on a Burstable tier at a low cost and then adjust the scale to meet the needs of your solution. For more information on service tiers, see the Azure Database for MySQL - Flexible Server service tiers documentation.

Flexible servers are great for ease of deployment, simplified scaling, and low database-management overhead for tasks like backups, high availability, security, and monitoring. They're also perfect for application developments that require a community version of MySQL with better control and customizations.

Flexible servers support high availability within a single availability zone or across multiple availability zones, providing a range of options for your database needs.

Here are some key benefits of using Flexible servers:

Day-to-day management of your Azure MySQL Flexible Server is crucial to ensure smooth operation. Azure's flexible server for MySQL manages the infrastructure and critical tasks, such as backup, patching, and upgrading.

You can perform several day-to-day management tasks on your Azure MySQL Flexible Server. These tasks include managing the infrastructure and critical tasks.

Some tasks you can perform include managing the infrastructure and critical tasks. Azure's flexible server for MySQL manages the infrastructure and critical tasks, such as backup, patching, and upgrading.

MySQL is a popular open-source relational database management system. It's a great tool for storing and managing data, and it's widely used in many industries.

MySQL Flexible Server is a fully managed Azure database service that offers more granular control and flexibility over database management functions and configuration settings. With Flexible Server, you can choose from various availability options, including single server, redundant setup, and zone-redundant setup.

One of the main advantages of a managed service like MySQL Flexible Server is that Microsoft performs automated patching of the underlying hardware, OS, and database engine, making your server secure and up to date.

Currently, MySQL Flexible Server supports MySQL 5.7 and 8.0, but version 5.7 has an end-of-life date of October 21, 2023, so it's recommended to use version 8.0 unless you need it for temporary migration purposes.

Here are the supported MySQL versions:

To get started with MySQL Flexible Server, you'll need to create a managed identity, which will be used for authentication to the KeyVault, which is used to encrypt data at rest.

Azure Regions offer a global reach, making it a great choice for running workloads. With Azure Database for MySQL - Flexible Server available in over 30 regions, you can deploy your application closer to your users.

Some regions have more features than others. For example, Australia East has Same-zone HA, Zone-redundant HA, and Geo-redundant backup, making it a good choice for high-availability and disaster recovery.

Availability is not the same across all regions. For instance, Australia Central is available, but does not have Zone-redundant HA or Geo-redundant backup.

Here is a breakdown of the regions with Same-zone HA:

Azure MySQL flexible server offers two high-availability options: Zone Redundant High Availability and Same Zone High Availability. Zone Redundant High Availability provides complete isolation and infrastructure redundancy across multiple availability zones.

This option is available in a subset of Azure regions that support multiple availability zones and zone-redundant premium file shares. It's ideal for mission-critical applications that cannot afford downtime.

Same Zone High Availability, on the other hand, offers infrastructure redundancy with lower network latency because both primary and standby servers are in the same availability zone.

You can monitor the health of your HA server using metrics such as HA IO Status, HA SQL Status, and HA Replication Lag. The HA Status in the server's High Availability pane can also be used to determine the server's HA configuration status.

High availability within and across availability zones is a key feature of Azure Database for MySQL - Flexible Server. It allows configuring high availability with automatic failover.

Zone-redundant high availability offers complete isolation and requires you to configure infrastructure redundancy across multiple availability zones. This is available in a subset of Azure regions that support multiple availability zones.

Zone-redundant HA provides the highest level of availability against any infrastructure failure in an availability zone and where latency across availability zones is acceptable. This makes it suitable for mission-critical applications that cannot afford downtime.

To take advantage of zone-redundant HA, you need to configure infrastructure redundancy across multiple availability zones. This option is available in a subset of Azure regions.

Here are the two high-availability architectural models:

Zone-redundant HA is available in a subset of Azure regions that support multiple availability zones and zone-redundant premium file shares. Same-zone HA is available in all Azure regions where you can create Azure Database for MySQL - Flexible Server instances.

Scheduled maintenance is a crucial aspect of high availability, and Azure Database for MySQL - Flexible Server has got you covered.

The service performs automated patching of the underlying hardware, operating system, and database engine, including security and software updates.

You can configure the patching schedule to be system managed or define your own custom schedule.

By default, a maintenance schedule is system-managed and varies by region.

The system-managed maintenance window might not be suitable for your application requirements.

You can configure a custom schedule by choosing the day of the week and maintenance window.

This allows you to make your patching cycle predictable and choose a maintenance window that has a minimum impact on the business.

The service follows a monthly release schedule for continuous integration and release.

During the maintenance schedule, the patch is applied, and the server might require a restart.

Azure MySQL Flexible Server provides robust security features to safeguard against unauthorized access. Data encryption at rest is supported using customer-managed keys, and Microsoft Defender for Cloud detects anomalous database activity.

To further secure your database, you can restrict public access to a specific IP address range or configure a virtual network and subnet to allow connections only from within the virtual network. Microsoft Entra ID Authentications are also recommended for centralized identity management and token-based authentication.

Azure MySQL Flexible Server also offers robust monitoring features, including metrics for performance and utilization, such as Host CPU percent, CPU credit consumed, and Storage IO percent. You can view these metrics in the Metrics tab or using MySQL flexible server workbooks, which provide customizable charts and graphs to help you visualize your database's performance.

High Availability Monitoring is crucial to ensure that your database is always accessible and running smoothly. Azure Database for MySQL - Flexible Server provides various metrics to monitor the health of the HA server.

You can use the High Availability Status located in the server’s High Availability pane in portal to determine the server’s HA configuration status. The Status can be NotEnabled, ReplicatingData, FailingOver, Healthy, or RemovingStandby.

Here are some key metrics to monitor the health of the HA server:

You can also set an alert to inform you when the replication lag reaches a value that isn't acceptable for your workload. If you see increased replication lag, refer to troubleshooting replication latency to troubleshoot and understand possible causes.

Enterprise Security is a top priority for any business, and Azure Database for MySQL - Flexible Server has got you covered. Data encryption is enforced by default, using the FIPS 140-2 validated cryptographic module for storage encryption of data at rest.

You can also use customer-managed keys stored in an Azure key vault or a managed hardware security module for added security. Data (including backups) and temporary files created while you run queries are all encrypted.

Azure Database for MySQL - Flexible Server uses the AES 256-bit cipher included in Azure storage encryption. This provides an additional layer of protection for your sensitive data.

If you need to connect to your database, you can do so with encrypted connections that use TLS 1.2. All incoming connections that use TLS 1.0 and TLS 1.1 are denied for added security.

Virtual network integration is also available, allowing full private access to the servers. This means that servers in a virtual network can be reached and connected only through private IP addresses.

Here are some key security features of Azure Database for MySQL - Flexible Server:

By implementing these security features, you can rest assured that your data is protected and your business is secure.

Automated patching with a managed maintenance window is a game-changer for Azure Database for MySQL - Flexible Server users. The service performs automated patching of the underlying hardware, operating system, and database engine, including security and software updates.

You can configure the patching schedule to be system managed or define your own custom schedule. A system-managed maintenance window might not be suitable for your application requirements, so it's great that you can choose a custom schedule.

The service follows a monthly release schedule for continuous integration and release. This ensures that your server stays up-to-date with the latest security patches and software updates.

With a custom schedule, you can make your patching cycle predictable and choose a maintenance window that has a minimum impact on the business. This is especially useful if you have specific time windows when your application is less busy.

GTID is a unique identifier created with each committed transaction on a source server and is OFF by default in Azure Database for MySQL Flexible Server. It's supported on versions 5.7 and 8.0.

To enable GTID, you need to update the gtid_mode server parameter, which indicates if GTIDs are used to identify transactions. This parameter has four possible values: OFF, OFF_PERMISSIVE, ON_PERMISSIVE, and ON.

Here's a brief explanation of each value:

You can't change the gtid_mode value in a single step; you need to do it one step at a time in ascending order. For example, if it's currently set to OFF_PERMISSIVE, you can change it to ON_PERMISSIVE but not to ON directly.

It's also important to note that you can't update the gtid_mode value for a master/replica server to keep replication consistent. To enable GTID replication, you need to set the enforce_gtid_consistency server parameter to ON before enabling GTID replication. This parameter enforces GTID consistency by allowing execution of only those statements that can be logged in a transactionally safe manner.

Security and monitoring systems can be complex and resource-intensive, requiring significant investment in hardware, software, and personnel.

A single point of failure can be catastrophic, as seen in the example of the compromised firewall that allowed a malicious actor to breach the system.

Investing in robust backup systems and disaster recovery plans is crucial to minimize downtime and data loss.

Monitoring systems can generate a high volume of alerts, which can be overwhelming for security teams to manage.

False positives can lead to alert fatigue, causing teams to become complacent and miss real threats.

Regularly reviewing and updating security policies and procedures is essential to stay ahead of emerging threats.

The example of the phishing attack that targeted employees' personal email accounts highlights the importance of employee education and awareness training.

Security teams must balance the need for security with the need for system availability and performance.

Automatic backups are a vital feature of Azure MySQL Flexible Server. They automatically create server backups and store them in user-configured locally redundant or geo-redundant storage.

The default backup retention period is 7 days, but you can configure a retention period of 1 to 35 days. This means you can choose how long you want to keep your backups.

All backups are encrypted through AES 256-bit encryption for added security. This ensures that your data remains protected even in the event of a disaster.

Here are the specifics of automated backups:

You can change the default backup retention period using the Change button near the Backup section. This allows you to adjust the retention period to suit your needs.

The portal also shows the backup type, retention period, and earliest restore point for your Azure MySQL instance. This provides valuable information for managing your backups.

Azure MySQL Flexible Server offers a range of performance and scaling features that make it an ideal choice for businesses of all sizes.

You can build your first app on a small database for a few dollars a month and then seamlessly adjust the scale to meet the needs of your solution.

Dynamic scalability enables your database to respond to rapidly changing resource requirements transparently, and you only pay for the resources you consume.

To scale out your read workload, you can use read replicas, which allow you to replicate data from an Azure Database for MySQL - Flexible Server instance to a read-only server.

Replicas are updated asynchronously via the MySQL engine's native binary log (binlog) file position-based replication technology.

You can use a load-balancer proxy solution like ProxySQL to seamlessly scale out your application workload to read replicas without any application refactoring cost.

To monitor the maximum IOPS requirement, you can use the metrics, and then provisioned IOPS or switch to Auto scale IOPS to configure MySQL for unpredictable spikes in the database traffic.

You can also choose from Auto scale IOPS or Pre-provisioned IOPS during the MySQL flexible server deployment.

Here are some common use cases for read replica:

These use cases highlight the importance of read replicas in improving the performance and scale of read-intensive workloads.

Automatic failover in HA servers is a crucial feature that ensures your MySQL database remains available even in the event of a primary server failure. The monitor pings to the nodes Management network Endpoint, and if this check fails two times continuously, it triggers an automatic failover operation.

This health check is designed to detect issues such as node unavailability, OS problems, and networking issues between management components and nodes. The monitor also runs a simple query on the instance, and if the queries fail to run, automatic failover will be triggered.

Here are some scenarios that are addressed by the health check:

However, it's essential to note that the health check does not monitor scenarios such as:

To ensure the health check works correctly, make sure that the NSG rules for the VNet do not block the communication to the instance customer networking endpoint on port 3306. For public access, ensure that the firewall rules are set and network traffic is allowed on port 3306. Additionally, DNS resolution from the client application side also needs to be taken care of.