Keepalived build highly available MySQL-HA

There are many solutions for MySQL-HA, such as heartbeat, drbd, mmm, and shared storage, but they have their own advantages and disadvantages. The heartbeat and drbd configurations are complex. You need to write your own scripts to achieve automatic MySQL switching. For those who do not know the scripting language, this is undoubtedly a split-brain problem. For mmm, few people use the production environment, and the mmm management end needs to run a server separately. To achieve high availability, You need to implement HA on the mmm management end, this will undoubtedly increase hardware spending. For shared storage, I personally think that MySQL Data is safer to be stored locally. After all, there is a single point of failure in storage devices. Using MySQL dual master + keepalived is a very good solution. In the MySQL-HA environment, MySQL is mutually master-slave, which ensures data consistency between the two MySQL databases, then, use keepalived to implement virtual IP addresses, and use the service monitoring function provided by keepalived to automatically switch MySQL during failure.

Next, I will share with you the architecture in a production environment to be launched. Let's take a look at how MySQL-HA is implemented in this architecture. The environment topology is as follows:

 

 
 

  
  
MySQL-VIP: 192.168.1.200
  
  
MySQL-master1: 192.168.1.201
  
  
MySQL-master2: 192.168.1.202
  
  

  
  
OS Version: CentOS 5.4
  
  
MySQL version: 5.0.89
  
  
Keepalived version: 1.1.20
 
 

1. MySQL master-master Configuration

1. Modify the MySQL configuration file

To enable the binlog function for both MySQL instances, enable the function by adding log-bin = MySQL-bin in the [MySQLd] section of the MySQL configuration file.

The server-ID of the two MySQL instances cannot be the same. By default, the serverID of the two MySQL instances is 1 and you need to change one of them to 2.

2. Set 192.168.1.201 to the master server of 192.168.1.202.

Create an authorized user on 192.168.1.201

 

 
 

  
  
MySQL> grant replication slave on *.* to replication@% identified by replication;    
  
  
Query OK, 0 rows affected (0.00 sec)    
  
  
   
  
  
MySQL> show master status;    
  
  
+------------------+----------+--------------+------------------+    
  
  
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |    
  
  
+------------------+----------+--------------+------------------+    
  
  
| MySQL-bin.000003 |      374 |              |                  |     
  
  
+------------------+----------+--------------+------------------+    
  
  
1 row in set (0.00 sec)  
 
 

Set 192.168.1.201 as the master server on 192.168.1.202.

 

 
 

  
  
MySQL> change master to master_host=192.168.1.201,master_user=replication,master_password=replication,master_log_file=MySQL-bin.000003,master_log_pos=374;    
  
  
Query OK, 0 rows affected (0.05 sec)    
  
  
   
  
  
MySQL> start slave;    
  
  
Query OK, 0 rows affected (0.00 sec)    
  
  
   
  
  
MySQL> show slave statusG    
  
  
*************************** 1. row ***************************    
  
  
             Slave_IO_State: Waiting for master to send event    
  
  
                Master_Host: 192.168.1.201    
  
  
                Master_User: replication    
  
  
                Master_Port: 3306    
  
  
              Connect_Retry: 60    
  
  
            Master_Log_File: MySQL-bin.000003    
  
  
        Read_Master_Log_Pos: 374    
  
  
             Relay_Log_File: MySQL-master2-relay-bin.000002    
  
  
              Relay_Log_Pos: 235    
  
  
      Relay_Master_Log_File: MySQL-bin.000003    
  
  
           Slave_IO_Running: Yes    
  
  
          Slave_SQL_Running: Yes    
  
  
            Replicate_Do_DB:     
  
  
        Replicate_Ignore_DB:     
  
  
         Replicate_Do_Table:     
  
  
     Replicate_Ignore_Table:     
  
  
    Replicate_Wild_Do_Table:     
  
  
Replicate_Wild_Ignore_Table:     
  
  
                 Last_Errno: 0    
  
  
                 Last_Error:     
  
  
               Skip_Counter: 0    
  
  
        Exec_Master_Log_Pos: 374