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

I don't know if you have any understanding of previous articles like how to implement highly available architecture through Keepalived+ in MySQL 5.6. today I'm here to tell you a little bit about it. If you are interested, let's take a look at the body. I'm sure you'll get something after watching how to achieve high availability architecture through Keepalived+ in MySQL 5.6.You will surely get something from it.

I. Test environment

Operating system version: Red Hat Enterprise Linux Server release 6.5 (Santiago)

Mysql version: MySQL-5.6.38-1.el6.x86_64.rpm-bundle.tar

Keepalived version: keepalived-1.2.7-3.el6.x86_64.rpm

Node01:192.168.10.71

Node02:192.168.10.72

VIP:192.168.10.70

Second, configure master-slave synchronization with node01 as master and node02 as slave

1. Node01 and node02 have installed Mysql 5.6.38 respectively. For installation method, please refer to the previous blog article "MySQL 5.6.38 is installed on RedHat 6.5 through RPM package".

2. Edit / etc/my.cnf in node01 and node02, respectively, and configure as follows:

[mysqld]

Log-bin=mysql-bin

Server-id = 1

[mysqld_safe]

Log-error = / var/log/mysqld.log

Pid-file = / var/run/mysqld/mysqld.pid

Replicate-do-db = all

3. Restart the mysql service

[root@node01 ~] # service mysql restart

[root@node02 ~] # service mysql restart

4. Log in to the mysql of node01, create an account repl for synchronization with a password of 123456, query the master status, and write down the file name and posttion value

Mysql > GRANT REPLICATION SLAVE ON *. * to 'repl'@'%' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

Mysql > show master status

5. Log in to the mysql of node02 and execute the following statement to enable the slave server. Note that you need to enter the IP of node01 in master_host.

Mysql > change master to master_host='192.168.10.71',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=318

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

Command parameter interpretation: master_host='192.168.10.71' # # IP address of Master master_user='repl' # # user used to synchronize data (user authorized in Master) master_password='123456' # # password of synchronous data user master_log_file='mysql-bin.000001' # # specify the log file from which Slave starts reading and copying data (can be seen on Master using show master status Log file name) master_log_pos=429 # # from which POSITION number to start reading

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

6. Query the status of slave service. The status is normal.

7. Create a database and a table in node01 and insert a row of data to test whether node02 can synchronize the past

Mysql > create database mysql_long

Query OK, 1 row affected (0.00 sec)

Mysql > use mysql_long

Database changed

Mysql > create table test (id int (3), name char (5))

Query OK, 0 rows affected (0.13 sec)

Mysql > insert into test values (001)

Query OK, 1 row affected (0.01sec)

8. Log in to the Mysql of node02 and the synchronization is normal.

Configure master-slave synchronization with node02 as master and node01 as slave

1. Log in to the mysql of node02, create an account repl for synchronization with a password of 123456, and query master status, write down file name and posttion value, and query master status.

Mysql > GRANT REPLICATION SLAVE ON *. * to 'repl'@'%' identified by' 123456'

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.10 sec)

2. Log in to the mysql of node01 and execute the following statement to enable the slave server. Note that you need to enter the IP of node02 here in master_host.

Mysql > change master to master_host='192.168.10.72',master_user='repl',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=318

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

Mysql > start slave

Query OK, 0 rows affected (0.03 sec)

3. Query the status of slave service. The status is normal.

4. Create a database and a table in node02 and insert a row of data to test whether node01 can synchronize the past.

Mysql > create database mysql_long2

Query OK, 1 row affected (0.00 sec)

Mysql > use mysql_long2

Database changed

Mysql > create table test2 (id int (3), name char (10))

Query OK, 0 rows affected (0.71 sec)

Mysql > insert into test2 values (001)

Query OK, 1 row affected (0.00 sec

5. Node01 synchronization is normal.

In this way, the mutual master and slave are configured. The two machines are both each other's Master and each other's Slave. No matter which machine the data changes, the other machine can synchronize the data in time. Let's configure keepalived to achieve high availability.

IV. Keepalived configuration

1. Use yum to install keepalived on node01

[root@node01 ~] # yum install keepalived-y

2. Also use yum to install keepalived on node02

[root@node02 ~] # yum install keepalived-y

3. Edit the configuration file of node01's keepalived

[root@node01 ~] # cat / etc/keepalived/keepalived.conf! 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 BACKUP # # node01 and node02 are configured as BACKUP Roles are determined by priority interface eth3 virtual_router_id 71 priority 100 # # node01's priority setting is higher than node02's advert_int 1 nopreempt # # setting (valid only if it is set in BACKUP) authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.10.70} virtual_server192.168.10 .70 3306 {delay_loop 6 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 192.168.10.71 3306 {weight 100 notify_down / etc/keepalived/stopkeepalived.sh # 3306 port unavailable execute script TCP_CHECK {connect_timeout 10 nb_get_retry 3 delay_before_retry 3 Connect_port 3306}

4. Edit the configuration file of node02's keepalived

[root@node02 ~] # cat / etc/keepalived/keepalived.conf! 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 BACKUP # # node01 and node02 are configured as BACKUP Roles are determined by priority the priority setting of interface eth4 virtual_router_id 71 priority 90 # # node02 is lower than the lower advert_int 1 nopreempt # # setting of node01 (valid only if it is set in BACKUP) authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.10.70}} virtual_server192.168 .10.70 3306 {delay_loop 6 lb_algo wrr lb_kind DR nat_mask 255.255.255.0 persistence_timeout 50 protocol TCP real_server 192.168.10.72 3306 {weight 100 notify_down / etc/keepalived/stopkeepalived.sh # 3306 port unavailable execute script TCP_CHECK {connect_timeout 10 nb_get_retry 3 delay_before_retry 3 connect_port 3306}} [root@node02 ~] #

5. Both node01 and node02 edit a suicide script / etc/keepalived/stopalived.sh of keepalived. Once port 3306 of Mysql is detected, the script is executed to trigger the switch of vip. The content of the script is very simple, that is, service keepalived stop, because the highly available switching action will be triggered when the keepalived service is stopped.

6. Start the keepalived service

[root@node01 ~] # service keepalived start

Starting keepalived: [OK]

[root@node02 ~] # service keepalived start

Starting keepalived: [OK]

7. Observe the message log of node01. Because node01 has a high priority, it has entered the role of master, and vip has also been added.

[root@node01~] # tail-f / var/log/messagesOct 30 22:47:46node01 Keepalived [3170]: Starting Keepalived v1.2.7 Oct 30 22:47:46node01 Keepalived [3171]: Starting Healthcheck child process, pid=3173Oct 30 22:47:46node01 Keepalived [3171]: Starting VRRP child process Pid=3174Oct 30 22:47:46node01 Keepalived_vrrp [3174]: Interface queue is emptyOct 30 22:47:46node01 Keepalived_vrrp [3174]: Netlink reflector reports IP 192.168.10.71 addedOct 30 22:47:46node01 Keepalived_vrrp [3174]: Netlink reflector reports IPfe80::20c:29ff:fe20:a6c8 addedOct 30 22:47:46node01 Keepalived_vrrp [3174]: Registering Kernel netlink reflectorOct 30 22:47:46node01 Keepalived_vrrp [3174]: Registering Kernel netlink command channelOct 30 22:47:46node01 Keepalived_vrrp [3174]: Registering Gratuitous ARP shared channelOct 30 22:47:46node01 kernel: IPVS: Registered protocols (TCP UDP, SCTP, AH, ESP) Oct 30 22:47:46node01 kernel: IPVS: Connection hash table configured (size=4096 Memory=64Kbytes) Oct 30 22:47:46node01 kernel: IPVS: ipvs loaded.Oct 30 22:47:46node01 Keepalived_vrrp [3174]: Opening file'/ etc/keepalived/keepalived.conf'.Oct 30 22:47:46node01 Keepalived_vrrp [3174]: Configuration is using: 63319 BytesOct 30 22:47:46node01 Keepalived_vrrp [3174]: Using LinkWatch kernel netlink reflector...Oct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Interface queue is emptyOct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Netlink reflector reports IP192. 168.10.71 addedOct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Netlink reflector reports IPfe80::20c:29ff:fe20:a6c8 addedOct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Registering Kernel netlink reflectorOct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Registering Kernel netlink command channelOct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Opening file'/ etc/keepalived/keepalived.conf'Oct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Configuration is using: 11970 BytesOct 30 22:47:46node01 Keepalived _ vrrp [3174]: VRRP sockpool: [ifindex (2) Proto (112) Fd (11 scheduler registered.Oct 12)] Oct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Using LinkWatch kernel netlink reflector...Oct 30 22:47:46node01 Keepalived_healthcheckers [3173]: Activating healthchecker for service [192.168.10.71]: 3306Oct 30 22:47:46node01 kernel: IPVS: [wrr] scheduler registered.Oct 30 22:47:47node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) Transition to MASTER STATEOct 30 22:47:48node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) Entering MASTER STATEOct 30 22:47:48node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) setting protocol VIPs.Oct 30 22:47:48node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) Sending gratuitous ARPs oneth3 for 192.168.10.70Oct 30 22:47:48node01 Keepalived_healthcheckers [3173]: Netlink reflector reports IP192.168.10.70 added

8. If you look at the message log of node02, node02 has entered the role of BACKUP, and VIP will not be added naturally.

[root@node02] # tail-f / var/log/messagesOct 30 22:48:54node02 Keepalived [16633]: Starting Keepalived v1.2.7 Oct 30 22:48:54node02 Keepalived [16634]: Starting Healthcheck child process, pid=16636Oct 30 22:48:54node02 Keepalived [16634]: Starting VRRP child process Pid=16637Oct 30 22:48:54node02 Keepalived_vrrp [16637]: Interface queue is emptyOct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Interface queue is emptyOct 30 22:48:54node02 Keepalived_vrrp [16637]: Netlink reflector reports IP 192.168.10.72 addedOct 30 22:48:54node02 Keepalived_vrrp [16637]: Netlink reflector reports IPfe80::250:56ff:fe34:ca7 addedOct 30 22:48:54node02 Keepalived_vrrp [16637]: Registering Kernel netlink reflectorOct 30 22:48:54node02 Keepalived_vrrp [16637]: Registering Kernel netlink Command channelOct 30 22:48:54node02 Keepalived_vrrp [16637]: Registering gratuitous ARP shared channelOct 30 22:48:54node02 Keepalived_vrrp [16637]: Opening file'/ etc/keepalived/keepalived.conf'.Oct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Netlink reflector reports IP192.168.10.72 addedOct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Netlink reflector reports IPfe80::250:56ff:fe34:ca7 addedOct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Registering Kernel netlink reflectorOct 30 22 Registering Kernel netlink reflectorOct 48: 54node02 Keepalived_healthcheckers [16636]: Registering Kernel netlink command channelOct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Opening file'/etc/keepalived/keepalived.conf'.Oct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Configuration is using: 11988 BytesOct 30 22:48:54node02 Keepalived_vrrp [16637]: Configuration is using: 63337 BytesOct 30 22:48:54node02 Keepalived_vrrp [16637]: Using LinkWatch kernel netlink reflector...Oct 30 22:48:54node02 Keepalived_vrrp [16637]: VRRP_Instance (VI_1 ) Entering BACKUP STATEOct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Using LinkWatch kernel netlink reflector...Oct 30 22:48:54node02 Keepalived_vrrp [16637]: VRRP sockpool: [ifindex (2) Proto (112), fd (10 Magi 11)] Oct 30 22:48:54node02 Keepalived_healthcheckers [16636]: Activating healthchecker forservice [192.168.10.72]: 3306

9. Test whether vip can be used to log in to mysql. Since vip is on node01, we use vip on node02 to test login. It can be seen that there is no problem.

5. Mysql High availability Test

1. When the mysql service of node01 is stopped, port 3306 is naturally blocked. When keepalived detects that port 3306 is blocked, it executes a suicide script to stop its own service, and the VIP is removed and released.

[root@node01 ~] # service mysql stopShutting down MySQL.. [OK]

2. Looking at node01's messages log, we can clearly see the whole process, and VIP has disappeared.

[root@node01] # tail-f / var/log/messagesOct 30 23:06:42node01 Keepalived_healthcheckers [3173]: TCP connection to [192.168.10.71]: 3306 failed!! Oct 30 23:06:42node01 Keepalived_healthcheckers [3173]: Removing service [192.168.10.71]: 3306from VS [192.168.10.70]: 3306Oct 30 23:06:42node01 Keepalived_healthcheckers [3173]: Executing [/ etc/keepalived/stopkeepalived.sh] for service [192.168.10.71 ]: 3306 in VS [192.168.10.70]: 3306Oct 30 23:06:42node01 Keepalived_healthcheckers [3173]: Lost quorum 1-031 > 0 for VS [192.168.10.70]: 3306Oct 30 23:06:42node01 Keepalived_healthcheckers [3173]: SMTP connection ERROR to [192.168.200.1]: 25.Oct 30 23:06:42node01 kernel: IPVS: _ ip_vs_del_service: enterOct 30 23:06:42node01 Keepalived [3171]: Stopping Keepalived v1.2.7 ( Oct 30 23:06:42node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) sending 0 priorityOct 30 23:06:42node01 Keepalived_vrrp [3174]: VRRP_Instance (VI_1) removing protocol VIPs.

3. Looking at the messages log of node02, you can see that node02 has entered the MASTER role and taken over VIP,VIP has been added. Judging from the log time, the switching process takes only 1 second, which can be said to be switched in seconds.

[root@node02] # tail-f / var/log/messagesOct 30 23:06:42node02 Keepalived_vrrp [16637]: VRRP_Instance (VI_1) Transition to MASTER STATEOct 30 23:06:43node02 Keepalived_vrrp [16637]: VRRP_Instance (VI_1) Entering MASTER STATEOct 30 23:06:43node02 Keepalived_vrrp [16637]: VRRP_Instance (VI_1) setting protocol VIPs.Oct 30 23:06:43node02 Keepalived_vrrp [16637]: VRRP_Instance (VI_1) Sending gratuitous ARPson eth4 for 192.168 .10.70Oct 30 23:06:43node02 Keepalived_healthcheckers [16636]: Netlink reflector reports IP 192.168.10.70 added

Because the keepalived process for node01 is stopped by a suicide script, it needs to be started manually. I wondered if I needed to run a monitoring script to start the keepalived service automatically, but then I didn't think it was necessary, because if the mysql service is still abnormal, even if the keepalived service is up, it will commit suicide again if it detects that port 3306 of the machine is not working. Since the mysql service is abnormal and the port is blocked, it is generally necessary to manually check and intervene to start the mysql, so it is good to start the keepalived manually after the mysql service is normal.

After reading the article on how to achieve high availability architecture through Keepalived+ in MySQL 5.6, what do you think? If you want to know more about it, you can continue to follow our industry information section.

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.