17 Nov

Build MySQL HA/Replication Fault-Tolerant Architecture

in MySQL

Build MySQL HA/Replication Fault-Tolerant Architecture. High availability is crucial for applications to ensure uninterrupted service and a seamless user experience. Since most applications run on data from a database.

MySQL, like other relational database management systems, often serves as the backbone for applications by storing, retrieving, and managing critical data. If MySQL data becomes unavailable, it may halt business operations, leading to potential revenue losses, operational inefficiencies, and disrupted services. It’s crucial to ensure you have multiple copies of the database. They come in handy when one database server fails, ensuring the application still runs seamlessly.

This article discusses how to Build MySQL HA/Replication Fault-Tolerant Architecture. Read on!

Also Read

MySQL Sharding: Scaling Techniques for Handling Large Datasets

What is High Availability in MySQL?

High Availability (HA) in MySQL refers to a state where service availability stays at the optimum regardless of either planned or unplanned activities on the database. With high availability, the application read or write to the database hassle-free. Ideally, the database functions optimally even with software hiccups, hardware failures, or network issues.

Basically, high availability aims to reduce downtime and maximize data consistency and accessibility at all times. To achieve this, you have to implement various high availability techniques.

Follow the article how to Build MySQL HA/Replication Fault-Tolerant Architecture to learn ways how to achieve it.

Also Read

How to Setup MySQL Server + phpMyadmin on Linux in Azure/AWS/GCP

How to Achieve High Availability in MySQL

1. InnoDB Cluster

InnoDB Cluster is a built in high availability solution for MySQL that ensures data remains available even in the face of failures. This cluster utilizes the MySQL Group Replication feature to create a fault-tolerant system with automatic failover. It ensures data consistency across server instances by using a distributed recovery mechanism and group communication. In the event of any primary instance failures, one of the secondary instances is automatically promoted to become the new primary, ensuring uninterrupted service.

InnoDB Cluster also provides tools for monitoring the health of the cluster and nodes. With the MySQL Shell ensures data integrity, fault tolerance, and the automatic detection of failures.

2. Caching

Caching in MySQL refers to the use of the MySQL Query Cache, which stores complete result sets for SELECT queries. When the server receives a similar query, the cache returns a response. In this case, the database is queried. Caching increases availability and increases response times.

Not all queries are suitable for caching, and there might be overhead in maintaining the cache, especially in highly dynamic databases. Use more advanced caching systems like ProxySQL or external caches like Redis and Memcached.

3. Backup

Backups are crucial if you want a high availability in your server. There are different types of backups available in MySQL:

Logical backups
Physical backups

Logical backups (like those created with mysqldump) contain SQL statements to recreate the database, whereas physical backups involve copying the actual database files. Store backups either offsite or on cloud storage to ensure that they’re safe from local hardware failures.

4. Replication

Involves setting up one or more replicas (or slaves) to replicate data from a master server. Replication provides data redundancy, improves data availability, and is used to distribute the read load among multiple servers.

There are different types of replication in MySQL, including asynchronous replication, semi-synchronous replication, and group replication. In asynchronous replication, the master writes events to its binary log without waiting for the replicas to acknowledge receipt. In semi-synchronous replication, the master waits for at least one replica to acknowledge receipt before committing a transaction.

Replication ensures data redundancy and high availability but also provides other benefits like load balancing for read-heavy workloads and geographic redundancy by placing replicas in different data centers or regions. When setting up replication monitor replication lag and ensure that replicas are kept up to date with the master.

5. Automatic Failover

Automatic failover is the process by which a standby or replica server takes over the duties of the primary server automatically. If the primary server becomes unavailable, the replica chips without manual intervention. This ensures continued database availability and minimizes downtime in case of unexpected failures.

Automatic failover is possible with tools such MySQL Group Replication, MySQL Router, and third-party solutions like Orchestrator.

Also Read

MySQL Performance Tuning: For Optimal Database Performance

Types of Replication in MySQL

Synchronous Replication

First type of replication in how to Build MySQL HA/Replication Fault-Tolerant Architecture is a synchronous replication. Here the primary server waits until changes are made to the replica servers. After the changes have been applied, it then commits the transaction. This type of replication provides the highest level of data consistency across all servers. It ensures that all replicas always mirror the primary server. The main benefit of synchronous replication is that it provides high data consistency across all servers.

Semi-synchronous Replication

Semi-synchronous provides a balance between data consistency and performance. In this replication, the primary server only waits for at least one replica to confirm it has received data changes before it commits. This way, there’s a higher level of data consistency than asynchronous replication. Ideally, this method provides a compromise, providing faster response times than full synchronous replication.

Asynchronous Replication

Asynchronous replication is the default replication type for MySQL. In this mode, the primary server commits transactions without waiting for any acknowledgment from the replica servers. Adding more replicas does not impact the primary server’s performance. This method provides the fastest response times alongside being highly scalable . However, it can lead to data inconsistency. If a primary server crashes after committing a transaction, there’s no guarantee that the replicas have received and applied that transaction.

Also Read

Redis vs MySQL – What’s the Difference? (Pros and Cons)

Row-Based vs Statement Based Replication in MySQL

Statement Based Replication (SBR) and Row Based Replication (RBR) are primary methods of data replication in MySQL.

What is Row Based Replication?

Row Based Replication is a method of database replication that focuses on replicating changes made to individual rows in a database. RBR replicates the actual changes at the row level. In this mode, when you perform an update, insert, or delete operation on a database, the change in each row is recorded and logged into the binary log of the master server. This log then serves as the source for replicating the exact row changes to the slave servers.

When you change data in a database, RBR looks at how the rows have changed after you’ve made your update. For example, if you change a customer’s address in a database, RBR notes down what the row looked like before and after you made this change. These before-and-after pictures of each row are then recorded in a special log. This log keeps track of all the changes that happen in the database.

The recorded changes are then sent to another database (slave database) to make sure it has the exact same data as the original database (master database). The slave database looks at the changes in the log and updates its rows to match the master database. This approach to replication comes in handy where the replication of precise data changes is critical. It’s also useful where the operations performed on the database might not be easily or efficiently replicated using SQL statements alone.

Pros of Row Based Replication

RBR can replicate each row change, making it a safer replication form
Since the actual row updates are logged, there’s no need for additional context information
The master requires fewer row locks, allowing for higher concurrency
RBR efficiently handles auto increment columns, timestamps, stored routines, and triggers
Statements that update a few rows are executed quickly

Cons of Row Based Replication

For large scale row updates, RBR generates substantial log data and network traffic
Need a deep understanding of the internal row format
Causes performance issues with large updates
It’s challenging to audit changes as the data is logged in a binary format, not as SQL statements

What is Statement Based Replication?

Statement Based Replication is a method of database replication where the actual SQL statements that cause data modifications are replicated. This is unlike in RBR where individual row changes resulting from these statements. In this mode, whenever a make INSERT, UPDATE, or DELETE operations on the master database, the same statement is logged into the binary log. Subsequently, these statements are sent to the slave databases, where they are executed to mirror the changes made on the master.

SBR aims to replicate the effect of a transaction rather than the exact data change. After executing a replicated SQL statement on the slave, it replicates the effect of the master. This way, it synchronizes the state of the two databases. This approach assumes that the databases are identical at the start of replication. Also, it assumes that the replicated statements have the similar effect on the slave as on the master.

SBR works well with simple SQL statements where the outcome is predictable and does not depend on any external factors. It assumes SQL statements are executed being the same on both the master and the slave databases. This means that the database schema, data, and even the server configurations need to be consistent across the replication.

Pros of SQL Based Replication

Less data transfer between the master and the slave, as logging occurs on SQL statements
The update logs take up less space since they store only the statements
There is no need to deal with the row format, which simplifies replication management.
Auditing database changes is easier due to logging of SQL statements

Cons of SQL Based Replication

There is a potential for data inconsistency between the master and the slave due to non-deterministic behaviors in SQL statements
Replication requires additional information to ensure the same results on the slave as on the master, which is difficult to achieve
It can be difficult to replicate stored routines or triggers accurately

Up next with how to Build MySQL HA/Replication Fault-Tolerant Architecture is the main part of our article. Please stay on.

Also Read

Backup and Restore a MySQL Database (Command Line)

How to Configure MySQL Replication in MySQL

Master-Slave Replication

In this architecture, the primary server, known as the master, handles all writes and updates. The other servers, known as slaves, replicate the master’s data and handle read requests. All changes are propagated from the master to the slaves. This allows for read scalability and provides a basic level of redundancy.

This architecture offloads read operations from the master, providing horizontal scalability. It’s useful for load balancing read-heavy applications and for backup purposes since backups are taken from a slave without affecting the master’s performance.

Master-Master Replication

In this setup, two servers act as masters, allowing writes to both. It requires careful configuration to avoid data conflicts, and applications must be aware of this topology. Typically, they are set up in a way that each master is the slave of the other, leading to bidirectional data replication. This setup provides redundancy since either master can take over if the other fails. It also allows write operations to be load-balanced between the two masters.

Also Read

How To Create a Multi-Node MySQL Cluster on Ubuntu 20.04

How to Set Up Database Replication in MySQL

1. Configure the Master Server

Edit MySQL Configuration:

Open my.cnf file (or mysqld.cnf on some systems):

				
					sudo nano /etc/mysql/my.cnf

2. Locate the [mysqld] section and enter the changes:

				
					server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = your_database_name

3. Restart MySQL:

				
					sudo service mysql restart

4. Log into MySQL shell and grant replication permissions:

				
					mysql -u root -p

Create a replication user and grant appropriate permissions:

				
					GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

5. While still in the MySQL shell, obtain master binary log coordinates:

				
					USE your_database_name;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

Take note of the File and Position values; you’ll need these for the slave configuration.

6. Backup the database:

In a new terminal (without closing the MySQL shell), execute:

				
					mysqldump -u root -p your_database_name &gt; databasebackup.sql

Once done, return to the MySQL shell and unlock the tables:

				
					UNLOCK TABLES;

7. Transfer Backup to Slave Server:

				
					scp databasebackup.sql user@slave_server_ip:/path/to/directory/

Configue the Slave Server

1. Edit MySQL Configuration of the slave server:

Open my.cnf or mysqld.cnf:

				
					sudo nano /etc/mysql/my.cnf

In the [mysqld] section, add the following:

				
					server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = your_database_name

Then, restart MySQL:

				
					sudo service mysql restart

2. Through the MySQL shell, import master database backup:

				
					mysql -u root -p your_database_name &lt; /path/to/directory/databasebackup.sql

On the MySQL shell, set up replication to master:

				
					mysql -u root -p

Execute the following, replacing placeholders with appropriate values:

				
					CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='slave_user',
MASTER_PASSWORD='password', MASTER_LOG_FILE='recorded_log_file',
MASTER_LOG_POS=recorded_log_position;

4. Start Slave Process:

				
					START SLAVE;

5. Check Slave Replication Status:

				
					SHOW SLAVE STATUS\G;

Ensure that both Slave_IO_Running and Slave_SQL_Running have the value Yes.

Test the Replication

After setting up, add data on the master server and check if it replicates to the slave.

The above is a basic implementation of replication in MySQL. In enterprise deployments consider additional security configurations, more complex topologies, and error handling scenarios. Also, remember to always test thoroughly before deploying to production.

Thank you for reading the article how to Build MySQL HA/Replication Fault-Tolerant Architecture. Let’s conclude.

Also Read

MySQL Architecture – Components (How MySQL Works Internally)