Use Keepalived to build highly available MySQL

The company's Daniel asked keepalived to build a high-availability MySQL. He saw a complete article on the Internet, so he followed the footsteps of his predecessors and added his own supplement. If the preceding settings are correct

The company's Daniel asked keepalived to build a high-availability MySQL. He saw a complete article on the Internet, so he followed the footsteps of his predecessors and added his own supplement. If the preceding settings are correct

Company Daniel asked keepalived to build high-availability MySQL. He saw a complete article on the Internet, so he followed the footsteps of his predecessors and added his own supplement.

The environment topology is as follows:

MySQL-VIP: 192.168.1.150
MySQL-master1: 192.168.1.151
MySQL-master2: 192.168.1.152

OS Version: RedHat 5.4
MySQL version: 5.0.77
Keepalived version: 1.1.20

I. installation and configuration of MySQL master-master
1. Install mysql
# Yum install mysql-server-y \ In order to save time, both servers are directly installed in yum. I hope you have configured the yum source.

2. modify the configuration file
On server1:
# Vim/etc/my. cnf
[Mysqld]
Server-id = 10
Log-bin = mysql-bin
Replicate-do-db = ccledb
Auto-increment = 2
Auto-increment-offset = 1
On server2:
# Vim/etc/my. cnf
[Mysqld]
Server-id = 20
Log-bin = mysql-bin
Replicate-do-db = ccledb
Auto-increment = 2
Auto-increment-offset = 2
3. Create an authorized user
On server1:
Mysql> grant replication client, replication slave on *. * to cclo1 @ '2017. 192.% 'identified by '20160301 ';

On server2:
Mysql> grant replication client, replication slave on *. * to cclo2 @ '192. 192.% 'identified by '2016 ';

4. Specify the master server
On server1
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| MySQL-bin.000003 | 374 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> change master to master_host = '192. 168.1.152 ', master_user = 'cclo2', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.05 sec)

On server2
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| MySQL-bin.000003 | 374 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
Mysql> change master to master_host = '192. 168.1.151 ', master_user = 'cclo1', master_password = '000000', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.05 sec)

5. Enable the primary service
Mysql> start slave; \ two servers run
MySQL> show slave status \ G \ view Master status
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes \ if both items are yes, the master-master configuration is successful.

If the preceding settings are correct, data update on any MySQL instance is synchronized to another MySQL instance.

Ii. install and configure keepalived

1. install and configure keepalived on 192.168.1.151 Server

Install keepalived

# Tar zxvf keepalived-1.1.20.tar.gz
# Cd keepalived-1.1.20
#./Configure -- prefix =/usr/local/keepalived -- with-kernel-dir =/usr/src/kernels/2.6.18-164. el5-i686
\ Make sure that your linux has/usr/src/kernels/2.6.18-164. el5-i686 this directory, if not please install the kernel-devel package, it is recommended to download and your linux kernel version of the same devel package installation (general installation image has ), directly # yum install-y kernel-devel
# Make & make install

Configure keepalived
You need to create a configuration file here. By default, when keepalived is started, it will go to the/etc/keepalived directory to find the configuration file

# Mkdir/etc/keepalived
# The content of the vim/etc/keepalived. conf \ configuration file is as follows:
! Configuration File for keepalived
Global_defs {
Notification_email {
Cclo@cer.cn
}
Notification_email_from cclo@cer.cn
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
Router_id MySQL-ha
}

Vrrp_instance VI_1 {
State BACKUP # both configurations are BACKUP
Interface eth0
Virtual_router_id 51
Priority 100 # priority, and the other is changed to 90
Advert_int 1
Nopreempt # do not take the initiative to seize resources. You can only set it on a machine with a higher priority. Do not set a machine with a lower priority.
Authentication {
Auth_type PASS
Auth_pass 1111
}
Virtual_ipaddress {
192.168.1.150
}
}

Virtual_server 192.168.1.150 3306 {
Delay_loop 2 # Check the real_server status every 2 seconds
Lb_algo wrr # LVS Algorithm
Lb_kind DR # LVS Mode
Persistence_timeout 60 # session persistence time
Protocol TCP
Real_server 192.168.1.151 3306 {
Weight 3
Notify_down/usr/local/my. sh # scripts executed after the service is down
TCP_CHECK {
Connect_timeout 10 # connection timeout
Nb_get_retry 3 # Number of reconnections
Delay_before_retry 3 # reconnection Interval
Connect_port 3306 # Health Check Port
}
}

Compile the script to be executed after the detection service is down:
# Vim/usr/local/my. sh
#! /Bin/sh
Pkill keepalived
# Chmod + x/usr/local/my. sh
Note: This script is used by the notify_down option in the configuration file above. keepalived uses the notify_down option to check the service status of real_server. This script is triggered when the real_server service is found to be faulty; we can see that the script is just a command that forces the keepalived process to be killed through pkill keepalived, thus achieving automatic MySQL failover. In addition, we do not have to worry that two MySQL databases will provide data update operations at the same time, because the keepalived configuration on each MySQL Server only contains the IP address + VIP address of the local MySQL server, instead of the IP address and VIP address of two MySQL servers

Start keepalived:
#/Usr/local/keepalived/sbin/keepalived-D
# Ps-aux | grep keepalived

Test
Find a lan pc and ping the VIP address of MySQL. At this time, the VIP address of MySQL can be pinged.
Stop the MySQL service. # ps-aux | grep keepalived: Check whether the keepalived health check program triggers the script we wrote.