Shulou(Shulou.com)06/01 Report--

Keepalived+mysql 's double principal principle:

(1) after the keepalived on the main library 1 is started, it will check whether the mysql service is alive. If it is alive, keepalived will enter the master state and get the VIP.

(2) after the keepalived on the main library 2 starts, it will also check whether the mysql is alive, and then check whether there is a master status in the keepalived group. If so, the keepalived on the main library 2 enters the backup state and is in the state of taking over VIP at any time.

(3) if the mysql on main library 1 dies and keepalived enters fault state and releases VIP, the keepalived on main library 2 will become master state and get VIP

Experimental environment:

OS:CentOS release 6.6 (Final)

Database: mysql 5.7.14

A: master: 192.168.91.23

B: slave: 192.168.91.22

VIP:192.168.91.100

Operating system time consistent change:

Date-s "20170227 16:25"

Hwclock-systohc

Master-slave parameters:

A:

Server_id = 330623

Gtid_mode=ON

Log_slave_updates = 0

Enforce_gtid_consistency = ON

Auto_increment_offset = 1

Auto_increment_increment = 2

B:

Server_id = 330622

Gtid_mode=ON

Log_slave_updates = 0

Enforce_gtid_consistency = ON

Auto_increment_offset=2

Auto_increment_increment=2

Configure AB for each other as master and slave:

A:

Create a replication account:

Create user rep@'192.168.91.%' identified by '147258'

Grant replication slave on *. * to rep@'192.168.91.%'

Make a complete set of An and restore it to B (omitted here)

B: add the main library with An as B:

Change master to master_host='192.168.91.23', master_port=3306, master_user='rep',master_password='147258', master_auto_position=1

Start slave

A: add B as the main library of A:

Change master to master_host='192.168.91.22', master_port=3306, master_user='rep',master_password='147258', master_auto_position=1

Start slave

Create a monitoring account: (later, the checkMySQL.py script will be used to check the status of the mysql database, as long as the user has usage permission)

GRANT REPLICATION CLIENT ON *. * TO 'monitor'@'%' IDENTIFIED BY' m0n1tor'

Both An and B should install keepalived software:

Yum install keepalived-y

Yum install MySQL-python-y

Keepalived configuration file for A:

[root@Darren1 keepalived] # cat keepalived.conf

Vrrp_script vs_mysql_23 {# can be named according to the actual situation

Script "/ etc/keepalived/checkMySQL.py-h 192.168.91.23-P 3306"

Interval 60 # switching time

}

Vrrp_instance VI_23 {# can be named according to the actual situation

State BACKUP # initially put it in backup state

Nopreempt # is set to not preemptive, M1 is down, m2 takes over VIP,m1 restart will not automatically grab VIP

Network card for interface eth0 # VIP

Virtual_router_id 23 # routing id, which ranges from 0 to 255.It cannot be the same as the highly available id of a router, but must be the same in the same cluster.

Priority 100 # priority, the MASTER priority of the same vrrp_instance must be higher than BACKUP.

Advert_int 5

Authentication {

Auth_type PASS # authenticated encryption

Auth_pass 1111 # authentication password, but no more than 8 digits

}

Track_script {

Vs_mysql_23 # calls this script. If 0 is returned, the VIP will be held. If 1 is returned, the VIP will be released.

}

Virtual_ipaddress {

192.168.11.100 # VIP address

}

}

EOF

B profile:

[root@Darren2 keepalived] # cat keepalived.conf

Vrrp_script vs_mysql_22 {

Script "/ etc/keepalived/checkMySQL.py-h 192.168.91.22-P 3306" # here is different from A, the others are the same

Interval 60

}

Vrrp_instance VI_22 {

State BACKUP

Nopreempt

Interface eth0

Virtual_router_id 23

Priority 90

Advert_int 5

Authentication {

Auth_type PASS

Auth_pass 1111

}

Track_script {

Vs_mysql_22

}

Virtual_ipaddress {

192.168.91.100

}

}

EOF

The role of checkMySQL.py script (omitted here):

The function of the script is to determine whether the mysql process exists, return 0 if it exists, and return 1 if it does not exist

An and B enable keepalived

/ etc/init.d/keepalived start (at the beginning, VIP will be on whoever starts An or B first)

Chkconfig-level 2345 keepalived on

Keepalived startup process:

At this point, A starts the keepalived service:

[root@Darren1 ~] # / etc/init.d/keepalived start

[root@Darren1] # tail-f / var/log/messages

May 9 14:41:05 Darren1 Keepalived [28172]: Starting Keepalived v1.2.13.

May 9 14:41:05 Darren1 Keepalived [28173]: Starting Healthcheck child process, pid=28175

May 9 14:41:05 Darren1 Keepalived [28173]: Starting VRRP child process, pid=28176

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Netlink reflector reports IP 192.168.91.23 added

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Netlink reflector reports IP 192.168.91.23 added

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Netlink reflector reports IP fe80::20c:29ff:fe56:5380 added

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Registering Kernel netlink reflector

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Registering Kernel netlink command channel

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Registering gratuitous ARP shared channel

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Opening file'/ etc/keepalived/keepalived.conf'.

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Netlink reflector reports IP fe80::20c:29ff:fe56:5380 added

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Registering Kernel netlink reflector

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Registering Kernel netlink command channel

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Opening file'/ etc/keepalived/keepalived.conf'.

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Configuration is using: 62873 Bytes

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: Using LinkWatch kernel netlink reflector...

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Entering BACKUP STATE

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Configuration is using: 5173 Bytes

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: VRRP sockpool: [ifindex (2), proto (112), unicast (0), fd (10L11)]

May 9 14:41:05 Darren1 Keepalived_healthcheckers [28175]: Using LinkWatch kernel netlink reflector...

May 9 14:41:05 Darren1 Keepalived_vrrp [28176]: VRRP_Script (vs_mysql_23) succeeded

May 9 14:41:21 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Transition to MASTER STATE

May 9 14:41:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Entering MASTER STATE

May 9 14:41:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) setting protocol VIPs.

May 9 14:41:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Sending gratuitous ARPs on eth0 for 192.168.91.100

May 9 14:41:26 Darren1 Keepalived_healthcheckers [28175]: Netlink reflector reports IP 192.168.91.100 added

May 9 14:41:31 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Sending gratuitous ARPs on eth0 for 192.168.91.100

Summarize the startup process:

(1) start three keepalived processes, namely, the main process, the health check sub-process and the VRRP protocol sub-process.

(2) after startup, VRRP_Instance begins to enter backup state.

(3) after entering backup successfully, VRRP_Instance changes its status to master, and then enters master status.

(4) get VIP and tell other servers with ARP broadcast

Keepalived switching process:

Stop the mysql service for A:

[root@Darren1 ~] # / etc/init.d/mysqld stop

Shutting down MySQL. SUCCESS!

The change of An at this time:

[root@Darren1] # tail-f / var/log/messages

May 9 14:43:25 Darren1 Keepalived_vrrp [28176]: VRRP_Script (vs_mysql_23) failed

May 9 14:43:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Entering FAULT STATE

May 9 14:43:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) removing protocol VIPs.

May 9 14:43:26 Darren1 Keepalived_vrrp [28176]: VRRP_Instance (VI_23) Now in FAULT state

May 9 14:43:26 Darren1 Keepalived_healthcheckers [28175]: Netlink reflector reports IP 192.168.91.100 removed

Summarize the changes of A: VRRP_Instance enters fault state and VIP is released.

The change of B at this time:

[root@Darren2] # tail-f / var/log/messages

May 9 14:43:26 Darren2 Keepalived_vrrp [35138]: VRRP_Instance (VI_22) Transition to MASTER STATE

May 9 14:43:31 Darren2 Keepalived_vrrp [35138]: VRRP_Instance (VI_22) Entering MASTER STATE

May 9 14:43:31 Darren2 Keepalived_vrrp [35138]: VRRP_Instance (VI_22) setting protocol VIPs.

May 9 14:43:31 Darren2 Keepalived_vrrp [35138]: VRRP_Instance (VI_22) Sending gratuitous ARPs on eth0 for 192.168.91.100

May 9 14:43:31 Darren2 Keepalived_healthcheckers [35137]: Netlink reflector reports IP 192.168.91.100 added

May 9 14:43:36 Darren2 Keepalived_vrrp [35138]: VRRP_Instance (VI_22) Sending gratuitous ARPs on eth0 for 192.168.91.100

Summarize the changes of B: VRRP_Instance enters master status and gets VIP,ARP broadcast notification

Log in to the database using VIP:

Create a login user:

Create user 'keepalived'@'%' identified by' 147258'

Grant all on *. * to 'keepalived'@'%'

Now VIP is on B:

[root@Darren2 ~] # ip addr | grep 192

Inet 192.168.91.22/24 brd 192.168.91.255 scope global eth0

Inet 192.168.91.100/32 scope global eth0

You can log in successfully with your 'keepalived'@'%' account on A, which proves that the VIP is valid:

[root@Darren1] # mysql-ukeepalived-p147258-h292.168.91.100

Keepalived@192.168.91.100 [(none)] > select user (), current_user ()

+-+ +

| | user () | current_user () |

+-+ +

| | keepalived@192.168.91.23 | keepalived@% |

+-+ +

Summary:

Several VIP handoff situations:

(1) if M1 host goes down, VIP will switch to m2.

(2) if the mysql on M1 dies, VIP will switch to m2.

(3) the keepalived service on M1 is down, which can be divided into two situations:

/ etc/init.d/keepalived stop: switch normally

Kill-9 keepalived_pid: because keepalived exits directly, both M1 and m2 have VIP, but only one is valid when connecting

(4) in the case of cerebral fissure, M1 and m2 each think of themselves as master state, and preempt VIP,VIP on M1 and m2 respectively.

There is no brain fissure under the same switch, which can happen in a more complex network environment.

You can guard against it in the script: ping the gateway. If you can't even ping the gateway, the vrrp_script script will put back 1pm keepalived and enter the fault state.

M1 is dead, m2 takes over the VIP,m1 and what happens after it is fixed?

(1) if it is GTID replication, put it directly on m1change master to m2. If it is traditional replication, you need to find the binlog location.

(2) wait for M1 synchronization to complete

(3) start keepalived

Two main disadvantages of keepalived+mysql and corresponding methods:

(1) it is difficult to guarantee database consistency:

You can use enhanced semi-synchronization to increase the parameter rpl_semi_sync_master_timeout that the master library is waiting for a response from the slave library.

If the master log fails to be transferred to the slave in real time, you need to manually intercept the binlog and add it to the slave database. If the system no longer exists, you can use binlog server to supplement the log.

(2) the malfunctioning master library needs to be added to the original structure manually.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.