How to configure high-availability database master-slave replication monitoring on Linux

How to configure high-availability database master-slave replication monitoring on Linux

Introduction:
In the modern technology environment, the database is a key component, and many applications rely on them. For availability and data protection considerations, database high availability and master-slave replication are very important features. This article will introduce how to configure high-availability database master-slave replication monitoring on Linux, and demonstrate the steps through sample code.

How master-slave replication works:
Master-slave replication is a common database replication method, in which one database server acts as the master server (Master), and other servers act as slave servers (Slave). Write operations received by the master server will be copied to the slave server. This architecture provides the benefits of data redundancy, read-write separation, and failure recovery.

Configure the main server:
First, we need to install the database server. This article takes MySQL as an example.

1. Install MySQL server:

   sudo apt update
   sudo apt install mysql-server

2. Configure the main server:
   Edit the MySQL configuration file:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   In the file Find the following line in and modify it:

   bind-address            = 0.0.0.0
   server-id               = 1
   log_bin                 = /var/log/mysql/mysql-bin.log

3. Restart the MySQL service:

   sudo systemctl restart mysql

Configure the slave server:

1. Install the MySQL server:

   sudo apt update
   sudo apt install mysql-server

2. Configure the slave server:
   Edit the MySQL configuration file:

   sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

   Find the following lines in the file and proceed Modification:

   bind-address            = 0.0.0.0
   server-id               = 2
   log_bin                 = /var/log/mysql/mysql-bin.log
   relay_log               = /var/log/mysql/mysql-relay-bin.log

3. Restart the MySQL service:

   sudo systemctl restart mysql

Set the master-slave relationship:

1. In Create a user for replication on the master server:

   mysql -u root -p
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%' IDENTIFIED BY 'password';
   FLUSH PRIVILEGES;
   EXIT;

2. Configure the master server information on the slave server:

   mysql -u root -p
   CHANGE MASTER TO MASTER_HOST='主服务器的IP地址', MASTER_USER='replication_user', MASTER_PASSWORD='password';
   START SLAVE;
   EXIT;

3. Verify that master-slave replication is working properly :
   Create a database and table on the main server and insert some data. Then, verify whether the corresponding data can be seen on the slave server.

Configuration monitoring:
In order to ensure the high availability of database master-slave replication, we need to monitor its status and detect and handle failures in a timely manner. Below is a simple monitoring script written in Python.

1. Install the required Python packages:

   sudo apt update
   sudo apt install python3-pip
   pip3 install mysql-connector-python
   pip3 install smtplib

2. Create the monitor.py file and copy the following code into the file:

   import smtplib
   import mysql.connector
   from email.mime.text import MIMEText
   
   def send_email(message):
    sender = "your_email@gmail.com"
    receiver = "recipient_email@gmail.com"
    subject = "Database Replication Alert"
    msg = MIMEText(message)
    msg['Subject'] = subject
    msg['From'] = sender
    msg['To'] = receiver
   
    try:
        with smtplib.SMTP_SSL('smtp.gmail.com', 465) as smtp:
            smtp.login(sender, "your_password")
            smtp.sendmail(sender, receiver, msg.as_string())
            print("Email sent successfully!")
    except Exception as e:
        print("Email failed to send: " + str(e))
   
   def monitor_replication():
    try:
        connection = mysql.connector.connect(host="localhost", user="replication_user", password="password", database="test")
        cursor = connection.cursor()
        cursor.execute("SELECT COUNT(*) FROM my_table")
        result = cursor.fetchone()
        cursor.close()
        connection.close()
        print("Replication is working fine, number of records: " + str(result[0]))
    except mysql.connector.Error as error:
        message = "Replication failed: " + str(error)
        print(message)
        send_email(message)
   
   if __name__ == "__main__":
    monitor_replication()

3. Modify the configuration information in monitor.py, including the email addresses of the sender and recipient, and the sender's email password.
4. Run the monitor.py script and add it to the scheduled task to regularly monitor the status of the database master-slave replication.

Conclusion:
Through the above steps, we can configure highly available database master-slave replication monitoring on Linux. Continuous monitoring of the status of the database is critical for failure recovery and availability. Using the sample code, we can detect and handle database master-slave replication problems in time to ensure the smooth operation of the business.

The above is the detailed content of How to configure high-availability database master-slave replication monitoring on Linux. For more information, please follow other related articles on the PHP Chinese website!