MySQL master-slave data plus keepalive

Then on a "configuration MySQL database master-Slave synchronization Experiment", this paper mainly from the perspective of application, in two MySQL server deployed keepalived service, can do in any one MySQL server failure situation, does not affect the use of MySQL database.

The address of the MySQL database configured by the application server is a VIP (Business virtual address), the VIP as the Virtual_server address in keepalived, keepalived Real_ The server address is the address of two MySQL database servers, respectively.

1, install keepalived. on both servers, do the following:

Yum Install Keepalived-y

2, configure the keepalived configuration file. To operate on the MySQL-01

Empty the/etc/keepalived/keepalived.conf file first, and then copy the following content:

! Configuration File for Keepalived
Global_defs {
Notification_email {
[Email protected]
}
Notification_email_from [email protected]
Smtp_server 127.0.0.1
Smtp_connect_timeout 30
router_id mysql-ha
}

Vrrp_instance Vi_1 {
State Master
Interface eth0
VIRTUAL_ROUTER_ID 51
Priority 100
Advert_int 1
Nopreempt

Authentication {
Auth_type PASS
Auth_pass 1111
}
virtual_ipaddress {
192.168.8.243
}
}

Virtual_server 192.168.8.243 3306 {
Delay_loop 6
Lb_algo WRR
Lb_kind DR
Persistence_timeout 50
Protocol TCP
Real_server 192.168.8.241 3306 {
Weight 3
notify_down/var/lib/mysql/killkeepalived.sh
Tcp_check {
Connect_timeout 10
Nb_get_retry 3
Delay_before_retry 3
Connect_port 3306
}
}
}
on the MySQL-02. To do the following:

First, the/etc/keepalived/keepalived.conf file is emptied, the above content:

"Real_server 192.168.8.241 3306" should read:

  "  real_server 192.168.8.242 3306"

do the following on both servers :

vim  /var/lib/mysql/killkeepalived .sh       #!/bin/sh   pkill keepalived      

chmod+x /var/lib/mysql/killkeepalived.sh




3. Test is available

To create a test user:

mysql> grant all privileges on *.* to  ‘test‘ @ ‘%‘ identified by  ‘123456‘ ; mysql> flush privileges;

Open one more terminal on the MySQL-01, Tailf/var/log/messages, and then another terminal to start the Keepalived Services service keepalived start.

In MySQL-02 also open a new terminal to view the log information, and then start keepalived

The

can be viewed through the IP addr command keepalived its VIP is   192.168.8.243

[[email protected] ~]# IP addr
1:lo: <loopback, Up,lower_up> MTU 16436 qdisc noqueue State UNKNOWN
    link/loopback 00:00:00:00:00:00 BRD 00:00:00 : 00:00:00
    inet 127.0.0.1/8 Scope host lo
    inet6:: 1/128 scope host
        Valid_lft Forever Preferred_lft Forever
2:eth0: <broadcast,multicast,up,lower_ Up> MTU Qdisc pfifo_fast State UNKNOWN Qlen $
    link/ether 08:00:27:a3:63:54 BRD ff:ff:ff:f F:FF:FF
    inet 192.168.8.241/24 BRD 192.168.8.255 scope global eth0
    inet 192.168.8.243/32 Scope global eth0
    inet6 fe80::a00:27ff:fea3 : 6354/64 scope link
       valid_lft forever Preferred_lft Forever

Manually stop the MySQL service on the MySQL-01. Also get log information:

Feb 09:22:45 localhost keepalived_healthcheckers[31710]: TCP connection to [192.168.8.241]:3306 failed!!!
Feb 09:22:45 localhost keepalived_healthcheckers[31710]: removing service [192.168.8.241]:3306 from VS [192.168.8.243 ]:3306
Feb 09:22:45 localhost keepalived_healthcheckers[31710]: executing [/var/lib/mysql/killkeepalived.sh] for service [ 192.168.8.241]:3306 in VS [192.168.8.243]:3306
Feb 09:22:45 localhost keepalived_healthcheckers[31710]: Lost quorum 1-0=1 > 0 for VS [192.168.8.243]:3306
Feb 09:22:45 localhost keepalived_healthcheckers[31710]: Remote SMTP Server [127.0.0.1]:25 connected.
Feb 09:22:45 localhost Kernel:ipvs: __ip_vs_del_service:enter
Feb 09:22:45 localhost keepalived[31708]: stopping keepalived v1.2.13 (03/19,2015)
Feb 09:22:45 localhost keepalived_vrrp[31711]: vrrp_instance (vi_1) Sending 0 priority
Feb 09:22:45 localhost keepalived_vrrp[31711]: vrrp_instance (vi_1) removing protocol VIPs.
Feb 09:22:45 localhost keepalived_healthcheckers[31710]: NetLink Reflector reports IP 192.168.8.243 removed

The log information on the MySQL-02 is as follows:

feb 09:22:46 localhost keepalived_vrrp[31896]: vrrp_ Instance (vi_1) Transition to MASTER State
Feb 09:22:47 localhost keepalived_vrrp[31896]: vrrp_instance (vi_1) Entering MASTER State
Feb 09:22:47 localhost keepalived_vrrp[31896]: vrrp_instance (vi_1) setting protocol VIPs.
Feb 09:22:47 localhost keepalived_vrrp[31896]: vrrp_instance (vi_1) sending gratuitous ARPs on eth0 for 192.168.8.243
feb 09:22:47 localhost keepalived_ HEALTHCHECKERS[31894]: NetLink Reflector reports IP 192.168.8.243 added
Feb 09:22:52 localhost keepalived_vrrp[ 31896]: vrrp_instance (vi_1) sending gratuitous ARPs on eth0 for 192.168.8.243

Perform the IP addr command view on both servers and find that the VIP address 192.168.8.243 has switched to MySQL-02 . Therefore, it also realizes the automatic switch of the application connection database.

at this point, you can test with the MySQL-connected tool, and the virtual IP address is still available. is as follows:

[Email protected] ~]# mysql-u test-p123456-h 192.168.8.243
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 243
Server Version:5.1.73-log Source Distribution

Copyright (c) and/or, Oracle, its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql>

This article is from the "Yuweibing Technology blog" blog, make sure to keep this source http://yuweibing.blog.51cto.com/3879355/1744722

MySQL master-slave data plus keepalive