keepalived Build High Availability Mysql-ha

With regard to mysql-ha, there are many solutions, such as heartbeat, DRBD, MMM, shared storage, but each has its pros and cons. Heartbeat, DRBD configuration is more complex, need to write their own script to achieve MySQL automatic switching, for those who do not script language, this is a problem of brain crack; for MMM, few people in the production environment, and MMM management end need to run a separate server, If you want to achieve high availability, you have to do to the MMM management end ha, which undoubtedly increased the hardware costs; for shared storage, the individual feel that MySQL data or on the local security, storage equipment, after all, a single point of trouble. The use of MySQL dual master+keepalived is a very good solution, in the mysql-ha environment, MySQL interoperability from the relationship, so as to ensure the consistency of the two MySQL data, and then use keepalived to achieve virtual IP, Through the keepalived of the service monitoring function to achieve MySQL failure automatically switch.

Below, I will be on the line of a production environment in the framework to share with you, look at this architecture, mysql-ha is how to achieve the environment topology 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
 
  
  

First, MySQL master-master configuration

1, modify the MySQL configuration file

Both MySQL to open the Binlog log function, open method: In the MySQL configuration file [MySQLd] section, add log-bin=mysql-bin option

Two MySQL Server-id can not be the same, by default, two MySQL ServerID are 1, you need to modify one of the 2 can be

2. Set 192.168.1.201 as 192.168.1.202 Primary server

Create a new 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 your primary 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 Status\g
  
  

  
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
  
  

  
relay_log_space:235
  
  

  
Until_condition:none
  
  

  
Until_log_file:
  
  

  
until_log_pos:0
  
  

  
Master_ssl_allowed:no
  
  

  
Master_ssl_ca_file:
  
  

  
Master_ssl_ca_path:
  
  

  
Master_ssl_cert:
  
  

  
Master_ssl_cipher:
  
  

  
Master_ssl_key:
  
  

  
seconds_behind_master:0
  
  

  
1 row in Set (0.00 sec)
 
 

3. Set 192.168.1.202 as 192.168.1.201 Primary server

Create a new authorized user on 192.168.1.202

 
 

  
  

  
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)
 
 

On the 192.168.1.201, set 192.168.1.202 as your own home server

 
 

  
  

  
mysql> Change Master to master_host= ' 192.168.1.202 ', 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 Status\g
  
  

  
1. Row ***************************
  
  

  
Slave_io_state:waiting for Master to send event
  
  

  
master_host:192.168.1.202
  
  

  
Master_user:replication
  
  

  
master_port:3306
  
  

  
Connect_retry:60
  
  

  
master_log_file:mysql-bin.000003
  
  

  
read_master_log_pos:374
  
  

  
relay_log_file:mysql-master1-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
  
  

  
relay_log_space:235
  
  

  
Until_condition:none
  
  

  
Until_log_file:
  
  

  
until_log_pos:0
  
  

  
Master_ssl_allowed:no
  
  

  
Master_ssl_ca_file:
  
  

  
Master_ssl_ca_path:
  
  

  
Master_ssl_cert:
  
  

  
Master_ssl_cipher:
  
  

  
Master_ssl_key:
  
  

  
seconds_behind_master:0
  
  

  
1 row in Set (0.00 sec)
 
 

4, MySQL synchronization test

If all of the above are properly configured, now any MySQL update data will sync to another mysql,mysql sync here no more demo