Overview

MYSQL Read Replicas

MYSQL read replicas provide improved performance by distributing read queries, thus reducing the load on the primary database. They also enhance high availability, scalability, and disaster recovery. Additionally, replicas can be placed in different geographical zones to further improve availability and reduce latency.

Creating a Read Replica

To create a read replica for an existing MYSQL database in another zone, you can use the following command:

exo api --zone at-vie-1 create-dbaas-service-mysql rocket <<EOF
{
  "name": "rocket",
  "plan": "startup-8",
  "integrations": [{"type": "read_replica", "source-service": "launch-site"}]
}
EOF

This will create an instance called rocket in Zone AT-VIE-1 for an existing database called launch-site.

NOTE - Do not forget to configure the IP Filter to access the new database.

Verifying the Replica

SHOW SLAVE STATUS is the command in MySQL that provides information about the replication status of a replica (slave) server. It includes details about the slave I/O and SQL threads, the replication position, and any errors.

Run the following SQL query on the replica server:

SHOW SLAVE STATUS\G

This command outputs detailed information such as Slave_IO_State, Master_Host, Master_User, Master_Log_File, and Seconds_Behind_Master.

Promoting the Replica to Master

Step 1: Get details of the replica service to find the integration ID

exo api get-dbaas-service-mysql -z at-vie-1 rocket

Step 2: Check Integration Status

exo api get-dbaas-integration <integration_id>

Step 3: Promote Replica to a master Database (Remove Replication)

exo api delete-dbaas-integration <integration_id>

NOTE - Customers can create a read replica on a startup plan, regardless of the main cluster’s plan. The read replica node is vertically scalable. It can be horizontally scaled by promoting it to the primary, becoming a standalone service detached from the main cluster.