Shulou(Shulou.com)05/31 Report--

Editor to share with you Mysql5.6.22 two-way replication + KeepAlived how to configure the high availability of mysql, I hope you will learn something after reading this article, let's discuss it together!

1. Basic installation environment, using mysql5.6.22 and keepalived-1.2.15 versions to install mysql high availability

Here) collapse or open

[root@xxt01 ~] # groupadd mysql

[root@xxt01] # useradd-g mysql-s / sbin/nologin-d / opt/mysql mysql

2.3 check mysql account

Here

) collapse or open

[root@xxt01] # mkdir-p / usr/local/mysql

[root@xxt01] # mkdir-p / usr/local/mysql/data

2.4.1 execute cmake:

Here

) collapse or open

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-binlog.000002 | 624556 | |

+-+

1 row in set (0.00 sec)

Mysql >

Master synchronization operation. If the parameters Slave_IO_Running and Slave_SQL_Running are YES, the operation is successful without any other errors.

Click (here) to collapse or open

Mysql > grant replication slave on *. * to 'slave'@'10.6.0.203' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > change master to master_host='10.6.0.204',master_user='slave',master_password='123456',master_log_file='mysql-binlog.000002',master_log_pos=624556

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.6.0.204

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000002

Read_Master_Log_Pos: 624556

Relay_Log_File: xxt01-relay-bin.000002

Relay_Log_Pos: 286

Relay_Master_Log_File: mysql-binlog.000002

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 624556

Relay_Log_Space: 459

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

Master_UUID: aa208c56-216f-11e6-ace3-000c29065661

Master_Info_File: / usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

4. Configure keepalived to realize the principle of master-slave automatic switching: keepalived is used to solve a single point of failure, providing vrrp (Virtual routing redundancy Protocol) and healthcheck functions. The function of providing a vip virtual ip;keepalived is to detect the status of the server. When one of the servers has a problem, remove it from the group and add it to it after recovery.

Service installation while keeping the master and slave service configurations the same.

Click (here) to collapse or open

[root@xxt01 ~] # tar zxf keepalived-1.2.15.tar.gz

[root@xxt01 keepalived-1.2.15] #. / configure-prefix=/usr/local/keepalived

[root@xxt01 keepalived-1.2.15] # make & & make install

[root@xxt01 etc] # cp-a / usr/local/keepalived/etc/rc.d/init.d/keepalived / etc/init.d/

[root@xxt01 etc] # cp-a / usr/local/keepalived/etc/sysconfig/keepalived / etc/sysconfig/

[root@xxt01 etc] # mkdir / etc/keepalived/

[root@xxt01 etc] # cp-a / usr/local/keepalived/etc/keepalived/keepalived.conf / etc/keepalived/

[root@xxt01 etc] # cp-a / usr/local/keepalived/sbin/keepalived / usr/sbin/

4.1 modify the / etc/keepalived/keepalived.conf file of 203

Click (here) to collapse or open

! Configuration File for keepalived

Global_defs {

Notification_email {

Acassen@firewall.loc

Failover@firewall.loc

Sysadmin@firewall.loc

}

Notification_email_from Alexandre.Cassen@firewall.loc

Smtp_server 192.168.200.1

Smtp_connect_timeout 30

Router_id LVS_DEVEL

}

Vrrp_instance VI_1 {

State MASTER

Interface eth0

Virtual_router_id 51

Priority 100

Advert_int 1

Authentication {

Auth_type PASS

Auth_pass 1111

}

Virtual_ipaddress {

10.6.0.205

}

}

# set mysqld

Virtual_server 10.6.0.205 3306 {

Delay_loop 6

Lb_algo wrr

Lb_kind DR

Persistence_timeout 50

Protocol TCP

Real_server 10.6.0.203 3306 {

Weight 3

Notify_down / etc/keepalived/killkeepalived.sh

TCP_CHECK {

Connect_timeout 10

Nb_get_retry 3

Delay_before_retry 3

Connect_port 3306

}

}

}

4.2 configure killkeepalived.sh

Click (here) to collapse or open

[root@xxt01 etc] # vi / etc/keepalived/killkeepalived.sh

#! / bin/sh

Pkill keepalived

4.3 check the / var/log/message logs of mater and slave respectively, and test the start and stop of mysqld service. The following message indicates that the service is normal.

Click (here) to collapse or open

203 master server

May 24 15:47:38 xxt01 Keepalived_vrrp [14242]: Configuration is using: 63545 Bytes

May 24 15:47:38 xxt01 Keepalived_vrrp [14242]: Using LinkWatch kernel netlink reflector...

May 24 15:47:38 xxt01 Keepalived_vrrp [14242]: VRRP sockpool: [ifindex (2), proto (112), unicast (0), fd (10L11)]

May 24 15:47:38 xxt01 Keepalived_healthcheckers [14241]: Using LinkWatch kernel netlink reflector...

May 24 15:47:38 xxt01 Keepalived_healthcheckers [14241]: Activating healthchecker for service [10.6.0.203]: 3306

May 24 15:47:39 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Transition to MASTER STATE

May 24 15:47:40 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Entering MASTER STATE

May 24 15:47:40 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) setting protocol VIPs.

May 24 15:47:40 xxt01 Keepalived_healthcheckers [14241]: Netlink reflector reports IP 10.6.0.205 added

May 24 15:47:40 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 15:47:45 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 15:56:27 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Received higher prio advert

May 24 15:56:27 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Entering BACKUP STATE

May 24 15:56:27 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) removing protocol VIPs.

May 24 15:56:27 xxt01 Keepalived_healthcheckers [14241]: Netlink reflector reports IP 10.6.0.205 removed

May 24 16:01:28 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Transition to MASTER STATE

May 24 16:01:29 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Entering MASTER STATE

May 24 16:01:29 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) setting protocol VIPs.

May 24 16:01:29 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 16:01:29 xxt01 Keepalived_healthcheckers [14241]: Netlink reflector reports IP 10.6.0.205 added

May 24 16:01:34 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 16:08:36 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Received higher prio advert

May 24 16:08:36 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) Entering BACKUP STATE

May 24 16:08:36 xxt01 Keepalived_vrrp [14242]: VRRP_Instance (VI_1) removing protocol VIPs.

May 24 16:08:36 xxt01 Keepalived_healthcheckers [14241]: Netlink reflector reports IP 10.6.0.205 removed

204 slave server

May 24 16:08:36 xxt02 Keepalived [15014]: Starting Keepalived v1.2.15.

May 24 16:08:36 xxt02 Keepalived [15015]: Starting Healthcheck child process, pid=15016

May 24 16:08:36 xxt02 Keepalived [15015]: Starting VRRP child process, pid=15017

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Netlink reflector reports IP 10.6.0.204 added

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Netlink reflector reports IP fe80::20c:29ff:fe06:5661 added

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Registering Kernel netlink reflector

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Registering Kernel netlink command channel

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Netlink reflector reports IP 10.6.0.204 added

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Netlink reflector reports IP fe80::20c:29ff:fe06:5661 added

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Registering Kernel netlink reflector

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Opening file'/ etc/keepalived/keepalived.conf'.

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Registering Kernel netlink command channel

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Configuration is using: 11978 Bytes

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Registering gratuitous ARP shared channel

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Opening file'/ etc/keepalived/keepalived.conf'.

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Configuration is using: 63545 Bytes

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: Using LinkWatch kernel netlink reflector...

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Using LinkWatch kernel netlink reflector...

May 24 16:08:36 xxt02 Keepalived_healthcheckers [15016]: Activating healthchecker for service [10.6.0.204]: 3306

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: VRRP sockpool: [ifindex (2), proto (112), unicast (0), fd (10L11)]

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) Transition to MASTER STATE

May 24 16:08:36 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) Received lower prio advert, forcing new election

May 24 16:08:37 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) Entering MASTER STATE

May 24 16:08:37 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) setting protocol VIPs.

May 24 16:08:37 xxt02 Keepalived_healthcheckers [15016]: Netlink reflector reports IP 10.6.0.205 added

May 24 16:08:37 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 16:08:42 xxt02 Keepalived_vrrp [15017]: VRRP_Instance (VI_1) Sending gratuitous ARPs on eth0 for 10.6.0.205

May 24 16:10:14 xxt02 yum [15023]: Installed: 2:nmap-5.51-3.el6.x86_64

4.4 Inspection service

Click (here) to collapse or open

[root@xxt01 mysql] # nmap 10.6.0.205

Starting Nmap 5.51 (http://nmap.org) at 2016-05-24 16:14 CST

Mass_dns: warning: Unable to determine any DNS servers. Reverse DNS is disabled. Try using-system-dns or specify valid servers with-dns-servers

Nmap scan report for 10.6.0.205

Host is up (0.00029s latency).

Not shown: 998 closed ports

PORT STATE SERVICE

111/tcp open rpcbind

3306/tcp open mysql

MAC Address: 00:0C:29:06:56:61 (VMware)

Nmap done: 1 IP address (1 host up) scanned in 1.21 seconds

[root@xxt01 mysql] # nmap 10.6.0.203

Starting Nmap 5.51 (http://nmap.org) at 2016-05-24 16:15 CST

Mass_dns: warning: Unable to determine any DNS servers. Reverse DNS is disabled. Try using-system-dns or specify valid servers with-dns-servers

Nmap scan report for 10.6.0.203

Host is up (0.0000050s latency).

Not shown: 998 closed ports

PORT STATE SERVICE

111/tcp open rpcbind

3306/tcp open mysql

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds

[root@xxt01 mysql] #

[root@xxt01 mysql] # nmap 10.6.0.205

Starting Nmap 5.51 (http://nmap.org) at 2016-05-24 16:14 CST

Mass_dns: warning: Unable to determine any DNS servers. Reverse DNS is disabled. Try using-system-dns or specify valid servers with-dns-servers

Nmap scan report for 10.6.0.205

Host is up (0.00029s latency).

Not shown: 998 closed ports

PORT STATE SERVICE

111/tcp open rpcbind

3306/tcp open mysql

MAC Address: 00:0C:29:06:56:61 (VMware)

Nmap done: 1 IP address (1 host up) scanned in 1.21 seconds

[root@xxt01 mysql] # nmap 10.6.0.203

Starting Nmap 5.51 (http://nmap.org) at 2016-05-24 16:15 CST

Mass_dns: warning: Unable to determine any DNS servers. Reverse DNS is disabled. Try using-system-dns or specify valid servers with-dns-servers

Nmap scan report for 10.6.0.203

Host is up (0.0000050s latency).

Not shown: 998 closed ports

PORT STATE SERVICE

111/tcp open rpcbind

3306/tcp open mysql

Nmap done: 1 IP address (1 host up) scanned in 0.08 seconds

[root@xxt01 mysql] #

After reading this article, I believe you have a certain understanding of "Mysql5.6.22 two-way replication + KeepAlived how to configure the high availability of mysql". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

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.