No doubt there will be a need for you to split off your analytical queries from the main database for performance reasons.
If you have been following me in the past with Azure SQL DB you would use failover group read endpoints. With MySQL we would need to build a replica (read only) to another server. This uses MySQL’s native feature binlog replication which is great to hear. This form is asynchronous.
How can you set this up? Within the Azure portal under settings, you will see replication.
You will see below that my master server is my main server so I click Add replica
I keep this simple and build the replica in the same region, most likely you will use at least your paired region.
You go to different regions, this is called cross-region replication and it depends where your master server is, please see following link for supported regions.
Click create and let it build the replica.
Some important notes:
- The read replica feature is only available the General Purpose or Memory Optimized pricing tiers
- The replica inherits the admin account, users and firewall IP from the source server.
- No automated failover between source and replica servers. This is currently a manual process if you need to failover.
- Don’t forget about the additional cost of the replicas.
- You can replicate from the source server to up to five replicas.
- When you a stop replica it becomes a read / write endpoint service.
- There will be a server restart on the master if this is the first time setting up a replica.
Once it has completed you will see it within Azure:
This replica is accessible via a valid hostname and user name as per any other MySQL server.
Pingback: Dew Drop – February 16, 2022 (#3623) – Morning Dew by Alvin Ashcraft
Pingback: Replication in Azure DB for MySQL – Curated SQL