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

This article will explain in detail how to achieve the high availability of Ubuntu building Mysql+Keepalived for you. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Implementation scheme of dual-computer hot standby for Mysql5.5

Install two Mysql

Install Mysql5.5

Sudo apt-get updateapt-get install aptitudeaptitude install mysql-server-5.5 or sudo apt-cache search mariadb-serverapt-get install-y mariadb-server-5.5

Unloading

Sudo apt-get remove mysql-*dpkg-l | grep ^ rc | awk'{print $2}'| sudo xargs dpkg-P

Configure permissions

Vim / etc/mysql/my.cnf#bind-address = 127.0.0.1mysql-u root-pgrant all on *. * to root@'%' identified by 'root' with grant option;flush privileges

Configure two Mysql master master synchronization

Configure Node 1

Vim / etc/mysql/my.cnf

Server-id = 1 # node IDlog_bin = mysql-bin.log # log binlog_format = "ROW" # log format auto_increment_increment = 2 # self-increment ID interval (= number of nodes Prevent ID conflicts) auto_increment_offset = 1 # starting value of increasing ID (node ID) binlog_ignore_db=mysql # out-of-sync database binlog_ignore_db=information_schemabinlog_ignore_db=performance_schema

Restart mysql

Service mysql restartmysql-u root-p

Record the binlog log location of node 1

Show master status;mysql-bin.000001 245 mysql,information_schema,performance_schema

Configure Node 2

Vim / etc/mysql/my.cnf

Server-id = 2log_bin = mysql-bin.log relay_log = mysql-relay-bin.log # Relay log _ slave_updates = ON # after the relay log is executed Changes are recorded in the log read _ only = 0binlog_format = "ROW" auto_increment_increment = 2auto_increment_offset = 2binlog_ignore_db=mysqlbinlog_ignore_db=information_schemabinlog_ignore_db=performance_schemareplicate_ignore_db=mysqlreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schema

Configure master and slave

Mysql- u root-pCHANGE MASTER TO MASTER_HOST='192.168.1.21', MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;# enable synchronization start slave# to check synchronization status Slave_IO_Running and Slave_SQL_Running need to be Yes show slave status

Record the binlog log location of node 2

Show master status;mysql-bin.000001 1029 mysql,information_schema,performance_schema

Configure Master Master (Node 1)

Vim / etc/mysql/my.cnf

Relay_log = mysql-relay-bin.loglog_slave_updates = ONread_only = 0replicate_ignore_db=mysqlreplicate_ignore_db=information_schemareplicate_ignore_db=performance_schema

Turn on synchronization

Mysql- u root-pCHANGE MASTER TO MASTER_HOST='192.168.1.20', MASTER_USER='root', MASTER_PASSWORD='root', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1029;# enable synchronization start slave# to check synchronization status Slave_IO_Running and Slave_SQL_Running need to be Yes show slave status

Exception handling

Could not initialize master info structure, more error messages can be found in the MySQL error log

Solution: reset slave

Install and configure Keepalived

Install Keepalived

# depending on sudo apt-get install-y libssl-devsudo apt-get install-y openssl sudo apt-get install-y libpopt-devsudo apt-get install-y libnl-dev libnl-3-dev libnl-genl-3.devapt-get install daemonapt-get install libc-devapt-get install libnfnetlink-devapt-get install libnl-genl-3.dev# to install apt-get install keepalived#, compile and install cd / usr/localwget https://www.keepalived.org/software/keepalived-2.2.2.tar.gztar-zxvf Keepalived-2.2.2.tar.gz mv keepalived-2.2.2 keepalived./configure-- prefix=/usr/local/keepalivedsudo make & & make install# opens the log sudo vim / etc/rsyslog.d/50-default.conf *. = info *. = notice;*.=warn;\ auth,authpriv.none;\ cron,daemon.none \ mail,news.none-/ var/log/messagessudo service rsyslog restart tail-f / var/log/messagessudo mkdir / etc/sysconfigsudo cp / usr/local/keepalived/etc/sysconfig/keepalived / etc/sysconfig/sudo cp / usr/local/keepalived/etc/rc.d/init.d/keepalived / etc/init.d/sudo cp / usr/local/keepalived/sbin/keepalived / sbin/sudo mkdir / etc/keepalivedsudo cp / usr/local/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

Configure node information

Node 1 192.168.1.21

Vim / etc/keepalived/keepalived.conf

Global_defs {router_id MYSQL_HA # current Node name} vrrp_instance VI_1 {state BACKUP # both configuration nodes are BACKUP interface eth0 # network interface virtual_router_id 51 # VRRP group names bound to virtual IP. The settings of both nodes must be the same. To indicate the priority of each node belonging to the same VRRP group priority 101 # node, and the other to change the priority to a lower advert_int 1 # multicast message sending interval. The settings of the two nodes must be the same, nopreempt # does not preempt, it can only be set on the machines with high priority, and the machines with low priority do not set authentication {# to set verification information. Two nodes must be consistent auth_type PASS auth_pass 123456} virtual_ipaddress {# specify virtual IP, and both node settings must be the same 192.168.1.111} virtual_server 192.168.1.111 3306 {# linux Virtual Server (LVS) configuration delay_loop 2 # check real_server status lb_algo wrr # LVS scheduling algorithm every 2 seconds Rr | wrr | lc | wlc | lblc | sh | dh lb_kind DR # LVS cluster mode NAT | DR | TUN persistence_timeout 60 # session persistence time protocol TCP # uses TCP or UDP real_server 192.168.1.21 3306 {weight 3 # weight notify_down / usr/local/bin/mysql.sh # script TCP_CHECK {connect_timeout 10 # connection timeout after the service down is detected Nb_get_retry 3 # reconnection times delay_before_retry 3 # reconnection interval connect_port 3306 # Health check port}

Node 2 192.168.1.20

Vim / etc/keepalived/keepalived.conf

Global_defs {router_id MYSQL_HA # current Node name} vrrp_instance VI_1 {state BACKUP # both configuration nodes are BACKUP interface eth0 # network interface virtual_router_id 51 # VRRP group names bound to virtual IP. The settings of both nodes must be the same. To indicate the priority of each node belonging to the same VRRP group priority 100 # node, and the other to change the priority to a lower advert_int 1 # multicast message sending interval. The settings of the two nodes must be the same, nopreempt # does not preempt, it can only be set on the machines with high priority, and the machines with low priority do not set authentication {# to set verification information. Two nodes must be consistent auth_type PASS auth_pass 123456} virtual_ipaddress {# specify virtual IP, and both node settings must be the same 192.168.1.111} virtual_server 192.168.1.111 3306 {# linux Virtual Server (LVS) configuration delay_loop 2 # check real_server status lb_algo wrr # LVS scheduling algorithm every 2 seconds Rr | wrr | lc | wlc | lblc | sh | dh lb_kind DR # LVS cluster mode NAT | DR | TUN persistence_timeout 60 # session persistence time protocol TCP # uses TCP or UDP real_server 192.168.1.20 3306 {weight 3 # weight notify_down / usr/local/bin/mysql.sh # script TCP_CHECK {connect_timeout 10 # connection timeout after the service down is detected Nb_get_retry 3 # reconnection times delay_before_retry 3 # reconnection interval connect_port 3306 # Health check port}

Write exception handling scripts

Vim / usr/local/bin/mysql.sh

#! / bin/shkillall keepalived

Assign permissions

Chmod + x / usr/local/bin/mysql.sh

# testing

Restart keepalived

Service keepalived restart

View the log

Tail-f / var/log/messages

View virtual IP

Ip addr # or ip an or ifconfig# master node will have a virtual IPeth0: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 52:54:9e:17:53:e5 brd ff:ff:ff:ff:ff:ff inet 192.168.1.21 scope global eth0 valid_lft forever preferred_lft forever inet 24 brd 192.168.1.255 scope global eth0 valid_lft forever preferred_lft forever inet 192.168.1.111 scope global eth0 valid_lft forever preferred_lft forever

Shut down the mysql service of the primary node

Service mysql stop

Log information

# Master node Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers [4949]: TCP connection to [192.168.1.20]: 3306 failed! Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers [4949]: Removing service [192.168.1.20]: 3306 from VS [192.168.1.111]: 3306Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers [4949]: Executing [/ usr/local/bin/mysql.sh] for service [192.168.1. 20]: 3306 in VS [192.168.1.111]: 3306Aug 10 15:00:30 i-7jaope92 Keepalived_healthcheckers [4949]: Lost quorum 1-031 > 0 for VS [192.168.1.111]: 3306Aug 10 15:00:30 i-7jaope92 Keepalived_vrrp [4950]: VRRP_Instance (VI_1) sending 0 priorityAug 10 15:00:30 i-7jaope92 kernel: [100918.976041] IPVS: _ _ ip_vs_del_service: enter# slave node Aug 10 15:00 I-6gxo6kx7 Keepalived_vrrp: VRRP_Instance (VI_1) Transition to MASTER STATEAug 10 15:00:32 i-6gxo6kx7 Keepalived_vrrp: VRRP_Instance (VI_1) Entering MASTER STATE

Virtual IP drifts from master node to slave node

Ip aeth0: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 52:54:9e:e7:26:5c brd ff:ff:ff:ff:ff:ff inet 192.168.1.20/24 brd 192.168.1.255 scope global eth0 valid_lft forever preferred_lft forever inet 192.168.1.111/32 scope global eth0 valid_lft forever preferred_lft forever

Mysql connection testing

Mysql-h 192.168.1.111-u root-p on "Ubuntu build Mysql+Keepalived high availability how to achieve" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it for more people to see.

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.