keepalived + mysqlroute +mysql for MySQL high availability

Environmental Introduction: 1.1 Server Role configuration:

mysql-master 10.0.0.101(外)  172.168.1.101(内) mysql-slave  10.0.0.103 (外) 172.168.1.103(内)mysql-router01   10.0.0.102 (外) 172.168.1.102(内)mysql-router02   10.0.0.104 (外) 172.168.1.104(内)jumpserver      10.0.0.128 (外) 172.168.1.128(内)

1.2 One master two-slave library for the configuration database

Both SLAVE01 and master are allowed to read and write, and SLAVE02 provide read-only
At the same time, MySQL master-slave replication is pre-configured successfully (this environment is to turn on Gtid mode replication)

1.3 Deployment of Web applications Jumpserver

Jumpserver Web 10.0.0.128 (outside) 172.168.1.128 (inside)
The Jumpsever configuration file is as follows:

[[email protected] jumpserver]# cat /opt/jumpserver/jumpserver.conf [base]url = http://10.0.0.128key = f1tty6elu8h03x2kip = 0.0.0.0  ###默认监听任何IPport = 8000  ###为jumpsever的默认的web端口log = debug[db]engine = mysqlhost = 172.168.1.20  ####为虚拟VIPport = 7001     ####为mysqlroute读写模式的默认端口####jumpserver的连接数据库的地址user = jumpserverpassword = jumpserverdatabase = jumpserver[mail]mail_enable = 1email_host = [email protected]email_port = 25email_host_user = [email protected]email_host_password = weuidnreemail_use_tls = Falseemail_use_ssl = False[connect]nav_sort_by = ip

To start the Jumpsever service:

[[email protected] jumpserver]# /opt/jumpserver/service.sh stop[[email protected] jumpserver]# /opt/jumpserver/service.sh start[[email protected] jumpserver]# /opt/jumpserver/service.sh statusjumpserver is running...                                   [  OK  ][[email protected] jumpserver]#[[email protected] jumpserver]# ps -ef|grep pythonroot       2443   2441  0 May14 ?        00:00:00 /bin/bash -c ulimit -S -c 0 >/dev/null 2>&1 ; python ./run_server.pyroot       2444   2443  0 May14 ?        00:00:04 python ./run_server.pyroot       9081   8974  0 14:50 pts/0    00:00:00 grep python

1.4 Installing keepalived and Mysql-router

Mysql-router01 10.0.0.102 Mysql-router02 10.0.0.104
Installing keepalived and Mysql-router on the 10.0.0.102 machine
Installing keepalived and Mysql-router on the 10.0.0.104 machine

[[email protected] ~]# cat /etc/hosts127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4::1         localhost localhost.localdomain localhost6 localhost6.localdomain6slave01 172.168.1.103master01 172.168.1.101router01  172.168.1.102router02  172.168.1.104slave02 172.168.1.105

Important NOTES:
For the rapid implementation of the environment to achieve a demonstration effect, it is recommended that 6 virtual machines to turn off SELinux and iptables, to ensure that the server's system time is consistent, you must ensure that the iptables is not boot, install keepalived and Mysql-router, Make sure that the 2 services are turned on and self-booting.

Second, keepalived related principle Introduction:

Keepalived is a high-performance server-ready or hot-standby solution, keepalived can be used to prevent a single point of failure of a server (a single point of failure is the failure of a certain point of the entire system architecture will be unavailable), The high availability of Web front-end services can be achieved with nginx. The following article describes keepalived combined with Mysql-router to enable high availability of databases

The keepalived implementation is based on the VRRP protocol, keepalived is the use of VRRP protocol to achieve high availability (HA).

VRRP (Virtual Router Redundancy Protocol) protocol is used to implement router redundancy protocol, the VRRP protocol to virtual two or more router devices into a device, external to provide virtual router IP (one or more), and within the router group, If the router that actually owns this external IP is master if it works, or if it is elected by the algorithm, master implements various network functions for the virtual router IP, such as ARP request, ICMP, and data forwarding, etc. other devices do not own the IP, Status is backup, which does not perform external network functions except receiving the VRRP status notification information of master. When the host fails, backup takes over the network functionality of the original master.

The VRRP protocol uses multicast data to transmit VRRP data, VRRP data sends data using a special virtual source MAC address instead of the MAC address of its own network card, and the VRRP runtime only has the master router periodically sending VRRP notification messages. Indicates that master is working properly and the virtual router IP (group), backup receives only VRRP data, does not send data, and if no notification of master is received within a certain period of time, each backup will declare itself master, send notification information, Re-conduct the master election status.

Three Mysql-router related principles: 3.1. What is MySQL route

MySQL Router is a lightweight agent between the application client and DBServer that detects, parses, and forwards queries to the backend DB instance and returns the results to the client. is a substitute for mysql-proxy. Its frame composition and function are as follows:

(1) router to achieve read and write separation, the program is not directly connected to the database IP, but fixed connection to the MySQL router. MySQL router is transparent to the front-end application. The application treats MySQL Router as a normal MySQL instance, sends queries to MySQL Router, and MySQL Router returns the query results to the front-end application.

(2) from the database server failure, the business can run normally. The server is not available for automatic downline by MySQL router. The program configuration does not require any modifications.

(3) The main database failure, the MySQL router to determine the master-slave automatic switch, the business can be accessed normally. The program configuration does not require any modifications.

3.2. Read and write separation principle

After the MySQL router accepts the front-end application request, according to the different port to distinguish reads and writes, sends all queries that connect the read-write port to the main library, and sends the select query with the read-only port to multiple slave libraries for the purpose of the read-write separation. The read-write return results are given to MySQL Router, which is returned by MySQL Router to the client application.

3.3.Mysql Router Use

The main uses of MySQL router are read-write separation, master failure auto-switching, load balancing, connection pooling and so on.

3.4.Mysql Router main main fault automatic switch pit

Mysql Router master failover function tested no problem, but there is a bigger pit to be aware of
MySQL Router master failover if it is running in a master from the case, from the library as a backup of the main library, this situation is the use of MySQL router master failover is no problem, However, in the case of a master multiple from the use of master failover will be in the following problem: The MySQL Main library is hung off, from the library slave01 and SLAVE02 has been synchronized with the original main library master's IP address, resulting in the IO thread on slave has been connecting state, Causes SLAVE02 replication to fail.

Four Software-specific installation deployment

Installation of Mysql-router and keepalived on 10.0.0.102 machines
The keepalived on this machine is the primary

yum -y install openssl-devel wget http://www.keepalived.org/software/keepalived-1.4.0.tar.gz tar xf keepalived-1.4.0.tar.gz  -C /usr/local/ cd /usr/local/keepalived-1.4.0/ ./configure make make install  mkdir /etc/keepalived find /usr/local/keepalived-1.4.0/ -name "keepalived.conf" cp /usr/local/keepalived-1.4.0/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/  find / -name "keepalived" cp /usr/local/etc/sysconfig/keepalived  /etc/sysconfig/ cp /usr/local/keepalived-1.4.0/keepalived/etc/init.d/keepalived /etc/init.d/ chmod +x /etc/init.d/keepalived  chkconfig keepalived on cp /usr/local/sbin/keepalived /usr/sbin/ which keepalived cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.ori

The following configuration file is the configuration file for Master Master keepalived

vim /etc/keepalived/keepalived.conf [[email protected] ~]# cat /etc/keepalived/keepalived.confglobal_defs {   notification_email {   [email protected]   }   notification_email_from [email protected]   smtp_server 192.168.200.1   smtp_connect_timeout 30   **router_id LVS_01**}vrrp_instance VI_1 {   ** state MASTER**    **interface eth1**    virtual_router_id 51    **priority 120**    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {    **172.168.1.20/24**    }} /etc/init.d/keepalived status /etc/init.d/keepalived start

The same way to install Mysql-router and keepalived on 10.0.0.104 machines

The keepalived on this machine is from
The following is a description of the configuration file from the keepalived machine:

[[email protected] ~]# cat /etc/keepalived/keepalived.confglobal_defs {   notification_email {   [email protected]   }   notification_email_from [email protected]   smtp_server 192.168.200.1   smtp_connect_timeout 30  ** router_id LVS_02**}vrrp_instance VI_1 {**    state BACKUP    interface eth1**    virtual_router_id 51   ** priority 80**    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {   ** 172.168.1.20/24**    }}

Master-Slave keepalived parameter description:
First the primary server:
Global_defs
{
Notification_email #通知email, according to the actual situation configuration br/>{
[Email protected]

Notification_email_from [email protected]
Smtp_server 127.0.0.1
Stmp_connect_timeout 30
router_id Node1 #节点名标识, mainly used in the notification
}

Vrrp_instance Vi_node {
State MASTER #配置为主服务器
Interface Eth0 #通讯网卡
virtual_router_id #路由标识
Priority #优先级, 0-254
Advert_int 5 #通知间隔, the actual deployment can be set to a smaller point, reduce latency

Authentication {
Auth_type PASS
Auth_pass 123456 #验证密码 for communication inter-host authentication
}

virtual_ipaddress {
192.168.1.206 #虚拟ip, you can define multiple
}
}
Next is set from server:
Global_defs {
Notification_email {br/>[email protected]

Notification_email_from [email protected]
Smtp_server 127.0.0.1
Stmp_connect_timeout 30
router_id Node2
}

Vrrp_instance Vi_node {
State BACKUP #与主服务器对应
Interface Eth0 #从服务器的通信网卡
virtual_router_id #路由标识, same as primary server
Priority #优先级, less than the primary server can
Advert_int 5 #这里是接受通知间隔, with the primary server to set the same

Authentication {
Auth_type PASS
Auth_pass 123456 #验证密码, same as primary server
}

virtual_ipaddress {
192.168.1.206 #虚拟IP, same as the primary server
}
}
The above setting is the most basic setting, and the function is to switch the virtual IP to the primary server after the primary server is restored, if the primary keepalived stop service (in general, the server is down).

4.1, keepalived configuration file Introduction to achieve virtual VIP drift

Note: 1, two keepalived.conf configuration files to add the same virtual network card, and do not have to use the server's internal and external network card like this can not achieve the heartbeat.
Can be the same network segment local network card, can also be the same network segment external network network card
2, when the main keepalived application is open, you can see the virtual IP address

[[email protected] ~]# ps -ef|grep keepalivedroot       9265      1  0 07:24 ?        00:00:00 keepalived -Droot       9267   9265  0 07:24 ?        00:00:00 keepalived -Droot       9268   9265  0 07:24 ?        00:00:05 keepalived -Droot       9517   9492  0 10:02 pts/3    00:00:00 grep keepalived[[email protected] ~]# ip addr|grep 172.168.1.20inet 172.168.1.20/24 scope global secondary eth1

This is also enabled from the Keepalived program, but does not have a virtual IP address from the local grep

[[email protected] ~]#  ps -ef|grep keepalivedroot      13115      1  0 07:08 ?        00:00:00 keepalived -Droot      13117  13115  0 07:08 ?        00:00:00 keepalived -Droot      13118  13115  0 07:08 ?        00:00:01 keepalived -Droot      13271  13232  0 10:02 pts/2    00:00:00 grep keepalived[[email protected] ~]# ip a|grep 172.168.1.20[[email protected] ~]#

Once the main keepalived program shuts down or the server hangs up, the virtual VIP can jump to the keepalived machine.

  [[email protected]  ~]#/etc/init.d/keepalived stopstopping keepalived: [OK][[email protected]  ~]# ps-ef|grep keepalivedroot 954 0 9492 0 10:21 pts/3 00:00:00 grep keepalived[[email protected]  ~]#[[ Email protected]  ~]# IP a|grep 172.168.1.20 inet 172.168.1.20/24 Scope Global Secondary Eth1[[email protected]  ~]#  

Tips:
If the virtual network card is not grep from the Load Balancer server, the reason is:
1, the local server keepalived program may be closed, the restart from the start line, in order to achieve the virtual network card jumping.
2, is the main load Balancer Server keepalived program does not shut down and cause
3, the primary load Balancer server as long as the keepalived service startup will not appear the above problems.
4. The iptables of the server is not turned off, or if the Selinxu is not closed, it may cause

4.2. Installing Mysql-router

10.0.0.102 10.0.0.104 two machine installation Mysql-router

Binary installation Mysql-router:

tar xf mysql-router-2.1.6-linux-glibc2.12-x86-64bit.tar.gz -C /usr/local/cd /usr/local/ln -s mysql-router-2.1.6-linux-glibc2.12-x86-64bit mysql-routermkdir /etc/mysql-route/mkdir /data/log/mysql-route -pcp /usr/local/mysql-router/share/doc/mysqlrouter/sample_mysqlrouter.conf /etc/mysql-route/mysqlrouter.confcp  /etc/mysql-route/mysqlrouter.conf  /etc/mysql-route/mysqlrouter.conf.ori

4.3 configuration files are described below:

[[email protected] mysql-route]# cat mysqlrouter.conf[default]# #日志存放目录logging_folder =/data/log/mysql-route# #插件存放目录plugin_folder =/ usr/local/mysql-router/lib/mysqlrouter## #配置文件存放目录config_folder =/etc/mysql-route## #运行目录runtime_folder =/var/run[ logger]## #日志运行级别level = Info#[fabric_cache] #address = Your_fabric_node.example.com:32275#user =## #主节点故障转移 [Routing: Basic_failover] #To is more transparent, use MySQL Server port 3306# #写节点地址bind_address =172.168.1.20 ##{This IP address is virtual vip}## The Write node port bind_port = 7001# #主库为读写模式mode = read-write## #172.168.1.101 for MySQL master 172.168.1.103 for MySQL from # # # # Primary node Address: By default the first primary database is the write main library, and when the first primary database is down, the second database is promoted to the primary library destinations = 172.168.1.101:3306,172.168.1.103:3306[routing: Balancing]bind_address=172.168.1.20bind_port = 7002connect_timeout = 3max_connections = 1024### For MySQL from the library (read only here) destinations = 172.168.1.105:3306mode = Read-only#[routing:homepage_reads_fabric] #bind_port = 7002# Destinations = Fabric+cache:///group/homepage_group?allow_primary_reads=yes#mode = Read-only#If No plugin is Configured WHich starts a service, Keepalive#will make sure MySQL Router would not immediately exit. It Is#safe to remove once Router is configured. [Keepalive]interval = 60

Start the Mysqlrouter service

/usr/local/mysql-router/bin/mysqlrouter -c /etc/mysql-route/mysqlrouter.conf &

Turn off iptables to ensure mysql-router boot from boot

[[email protected]~]# chkconfig--list|grep iptablesiptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off[[email protected]~]# chkconfig iptables off[[email protected]~]# vim/etc/rc.local #!/bin/shtouch/var/lock/subsys/local/usr/local/mysql-router/bin/mysqlrouter-c/etc/ Mysql-route/mysqlrouter.conf &[[email protected]~]# chkconfig--list|grep iptablesiptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off[[email protected]~]# chkconfig iptables off[[email protected]~]# vim/etc/rc.local #!/bin/shtouch/var/lock/subsys/local/usr/local/mysql-router/bin/mysqlrouter-c/etc/ Mysql-route/mysqlrouter.conf &

4.4 Create a test account for testing:

Once the MySQL Master Master Library is signed in, the account will be synced to each slave.

Mysql> Grant all on jumpserver.* to[email protected]' 172.168.1.% ' identified by ' jumpserver '; [[email protected]~]# mysql-h172.168.1.20-p7001-ujumpserver-pjumpserver-e "Show variables like ' hostname ';" MySQL: [Warning] Using a password on the command line interface can be insecure.+---------------+----------+| variable_name | Value |+---------------+----------+| hostname | Master01 | [[email protected]~]# mysql-h172.168.1.20-p7001-ujumpserver-pjumpserver-e "Show variables like ' hostname ';" +---------------+----------+| variable_name | Value |+---------------+----------+| hostname | Master01 | [[email protected]~]# mysql-h172.168.1.20-p7001-ujumpserver-pjumpserver-e "Show variables like ' hostname ';" MySQL: [Warning] Using a password on the command line interface can be insecure.+---------------+----------+| variable_name | Value |+---------------+----------+| hostname | Master01 |

keepalived + mysqlroute +mysql for MySQL high availability