How to configure a highly available database cluster on Linux

How to configure a highly available database cluster on Linux

In modern Internet applications, databases play a very important role. In order to ensure data reliability and high availability, many companies configure highly available database clusters. This article will introduce how to configure a highly available database cluster on Linux to ensure that when the database fails, it can quickly switch to a standby database to ensure the continuous operation of the application.

We will use MySQL as a sample database to demonstrate how to configure a highly available MySQL database cluster on Linux.

1. Installing MySQL

First, you need to install MySQL on Linux. You can use the following command:

sudo apt-get install mysql-server

2. Configure master-slave replication

In a high-availability database cluster, master-slave replication is usually used to achieve data synchronization. In this mode, one database server serves as the master server, responsible for writing and updating data, and other database servers serve as slave servers, responsible for reading data and synchronizing the data of the master server in real time.

First, you need to configure it on the main server. Edit the MySQL configuration file my.cnf, find and modify the following configuration:

server-id=1
log_bin=mysql-bin
binlog_format=row

Then, restart the MySQL service.

Next, configure on the slave server. Similarly, you need to edit the MySQL configuration file my.cnf, find and modify the following configuration:

server-id=2
relay-log=mysql-relay-bin
log_slave_updates=1
read_only=1

Then, restart the MySQL service.

3. Set master-slave synchronization

Now that the master-slave server has been successfully configured, you need to set up master-slave synchronization. On the master server, use the following command to create a user for synchronization:

CREATE USER 'replication'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

Then, execute the following command to obtain the binary log file and location of the master server:

SHOW MASTER STATUS;

The result is similar to ：

+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.0001| 107       | test         |                  |
+---------------+----------+--------------+------------------+

Next, execute the following command on the slave server to start master-slave synchronization:

CHANGE MASTER TO MASTER_HOST='master_server_ip', MASTER_USER='replication', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.0001', MASTER_LOG_POS=107;
START SLAVE;

Execute the following command on the slave server to view the master-slave synchronization status:

SHOW SLAVE STATUSG

If both Slave_IO_Running and Slave_SQL_Running are displayed as Yes, it means that the master-slave synchronization has been successfully set up.

4. Configuring master-slave switchover

When the primary server fails, you need to manually switch to the standby database. To facilitate switching, Keepalived and HAProxy can be used to implement automatic switching.

First, you need to install Keepalived and HAProxy. You can use the following command:

sudo apt-get install keepalived haproxy

Then, edit the Keepalived configuration file /etc/keepalived/keepalived.conf and modify the following configuration:

vrrp_script chk_mysql {
    script "killall -0 mysqld"
    interval 2
    weight -2
}

vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass your_password
    }
    track_script {
        chk_mysql
    }
    virtual_ipaddress {
        192.168.1.100/24
    }
}

Save and close the file.

Next, edit the HAProxy configuration file /etc/haproxy/haproxy.cfg and add the following configuration:

listen mysql-cluster
    bind 192.168.1.100:3306
    mode tcp
    option mysql-check user haproxy_check
    balance roundrobin
    server mysql1 192.168.1.101:3306 check
    server mysql2 192.168.1.102:3306 check backup

Save and close the file.

Finally, restart the Keepalived and HAProxy services:

sudo service keepalived restart
sudo service haproxy restart

Now, when the primary server fails, Keepalived will switch the virtual IP address to the standby database and forward traffic to HAProxy.

Through the above steps, we successfully configured a highly available database cluster on Linux. When the database fails, the system automatically switches to the standby database, ensuring data reliability and high availability. I hope this article can help you configure a highly available database cluster.

The above is the detailed content of How to configure a highly available database cluster on Linux. For more information, please follow other related articles on the PHP Chinese website!