Build mysql server load balancer and high availability environment

Build mysql server load balancer and high availability environment _ MySQL

Last Update:2018-04-13 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Build mysql server load balancer and high-availability environment bitsCN.com

Build a mysql server load balancer and high-availability environment

Abstract: rhel5.8, mysql, keepalived, and haproxy are used to build a cluster with high availability and load balancing. mysql binary replication is also used to maintain data consistency. single node failure is avoided, it also provides device utilization. Although this experiment only has two devices, it can be used as an example to understand the concept and then zoom in to other environments;

Objective: to use two hosts to achieve Mysql load balancing and redundancy, and achieve dual-master-slave mutual backup;

Environment: rhel5.8, mysql-5.0.77, keepalived-1.2.7, haproxy-1.4.20;

Note:

Keepalived achieves host redundancy through vrrp protocol by switching a virtual IP address (or floating IP address) between the master and slave devices;

The client accesses the virtual IP address to obtain the service;

Haproxy is a server load balancer software that forwards requests to different hosts according to policies to achieve load balancing;

Can realize judgment and allocation at the network layer or application layer;

Mysql enables binary logs to achieve bidirectional replication between databases and maintain data consistency;

# Remind me that no one in the actual production environment will set up this way. we can only use this experiment to understand these concepts and hope that readers can get them;

Serverslave IP = 192.168.5.11

Server2_ip = 192.168.5.12

Server_vip = 192.168.5.111

========================================================== ==========

# Disable the firewall here. if the firewall is enabled, configure the corresponding rules.

Service iptables stop

Chkconfig iptables off

Ls/opt/soft/# extract preparation software here

Haproxy-1.4.20.tar.gz keepalived-1.2.7.tar.gz

Mkdir/opt/keepalived

Mkdir/opt/scripts/# script directory used in this experiment

Mkdir/opt/log/# this experiment log folder

========================================================== ==========

[Install_mysql]

# Install mysql on two hosts. here, the rpm Package is used for installation, and the yum environment can be set to the local source;

Yum install-y mysql-server

Service mysqld start

========================================================== ====================

[Create_mysql_test_table]

# Create a table with the same name and field, and insert different values to test whether ha and round robin are successful;

# Create an identical user

# Server1

Mysql

> Use test;

> Create table mywait (name char (9), phone char (14 ));

> Insert into mywait (name, phone) Value ('wait', 15000000000 );

# Server2

> Use test;

> Create table mywait (name char (9), phone char (14 ));

> Insert into mywait (name, phone) values ('Chen', 15611111111 );

[New_mysql_test_user]

> Mysql

> Grant all on test. * to diaosi @ '%' identified by '20140901 ';

> Flush privileges;

========================================================== ====================

# Test it on the client;

Mysql-udiaosi-p123456-h 192.168.5.11-e "select * from test. mywait ;"

Mysql-udiaosi-p123456-h 192.168.5.12-e "select * from test. mywait ;"

# So far, the basic mysql environment has been set up;

========================================================== ====================

[Install_keepalived]

Yum install-y libnl-devel # resolve dependencies

Tar xf/opt/soft/keepalived-1.2.7.tar.gz-C/opt/soft

Keepalived-1.2.7/cd/opt/soft/

# Use the TAB key to complete the kernel version specified by with-kernel based on the local operating system.

./Configure -- prefix =/opt/keepalived -- with-kernel-dir =/usr/src/kernels/2.6.18-308. el5-i686/

Make & make install

[Keepalived_config]

# Because it is not installed in the/directory, you need to copy the startup and configuration files;

Cp/opt/keepalived/sbin/keepalived/usr/sbin/

Cp/opt/keepalived/etc/rc. d/init. d/keepalived/etc/init. d/

Cp/opt/keepalived/etc/sysconfig/

Mkdir/etc/keepalived

Cp/opt/keepalived/etc/keepalived. conf/etc/keepalived/

Chkconfig keepalived on # set startup

========================================================== ====================

Vim/etc/keepalived. conf # Master configuration file of keepalived

# The following configurations are applicable to the situation where keepalived itself is a service provider;

! Configuration File for keepalived

# Simple header. here, you can set email notification alarms. this is not configured for the moment;

Global_defs {

Notificationd LVS_DEVEL

}

# Define a script in advance to facilitate subsequent calls or multiple definitions for easy selection;

Vrrp_script mysql_chk {

Script "/opt/scripts/mysql_chke.sh"

Interval 2 # script cycle interval

Weight 2 # The script result leads to priority change, successful + 2

}

# VRRP vro Redundancy Protocol configuration

Vrrp_instance VI_1 {# VI_1 is a custom name;

State MASTER # indicates that this is a MASTER device and the standby device is BACKUP

Interface eth0 # specify the physical Nic to be bound to the VIP

Virtual_router_id 11 # VRID vro ID, also known as the group name. the devices in this group must be the same

Priority 150 # define the priority of this device 1-254;

Advert_int 1 # interval for sending multicast information during survival detection, consistent in the group

Authentication {# set verification information, consistent in the group

Auth_type PASS # There are two types: PASS and AH.

Auth_pass 111 # Password

}

Virtual_ipaddress {# specify the VIP address, which is consistent in the group. you can set multiple IP addresses.

192.168.5.111/24

}

Track_script {# use a pre-defined script in this domain

Mysql_chk

}

# The scripts loaded in this part are external scripts and do not need to be pre-defined;

# Do not add this experiment. this experiment is added only after haproxy is installed in the second part;

Notify_master/opt/scripts/start_haproxy.sh # indicates the script to be executed when the master status is changed.

Notify_fault/opt/scripts/stop_keepalived.sh # script executed during fault

Notify_stop/opt/scripts/stop_haproxy.sh # keepalived stop the script before running

}

# The master and slave configuration files of keepalived are basically the same. you only need to modify them:

State BACKUP # Change to BACKUP device

Priority 100 # The priority is lower than the master.

# Adjustments can be made elsewhere based on actual conditions;

========================================================== ====================

# Create the script defined when keepalived is configured. it is used to end keepalived after mysql dies.

#! /Bin/bash

# Mysql_chke.sh

A = 'PS-C mysqld -- no-header | wc-L'

If [$ a-eq 0]; then

Sleep 3

/Sbin/service keepalived stop

Echo "'date + % c' stop keepalived">/opt/log/stop_keepalived.log

Service keepalived start # start on two devices

# Start testing

Ip address

# Check whether the vip address is generated on the master device 11;

# Note: The VIP generated by keepalived is invisible to the ifconfig command, so you need to use the ip Command;

Mysql-udiaosi-p123456-h 192.168.5.111-e "select * from test. mywait ;"

# Normally, only the data of the MASTER database can be queried;

1. try to stop the keepalived of the MASTER.

2. drop the Nic of the MASTER.

3. mysql cannot be started. for example, comment out the mysql User first, and then killall mysqld,

Check whether the mysql_chke script stops keepalived;

# At this time, the vip address will be moved to the Slave host; whether the HA build is successful reflects the change in the query value;

# After the MASTER is restored, the VIP will return to the 11 device;

# Keepalived has successfully implemented the HA mode for mysql;

========================================================== ======================================

[Install_haproxy]

# Used to round requests to 192.168.5.11 192.168.5.12

Tar xf/opt/soft/haproxy-1.4.20.tar.gz-C/opt/soft/

Haproxy-1.4.20/cd/opt/soft/

Make TARGET = linux26 PREFIX =/opt/haproxy install

Mkdir/opt/haproxy/conf

Mkdir/opt/haproxy/logs

Touch/opt/haproxy/conf/haproxy. cfg

========================================================== ======================================

[Mysql_config]

# Modify the mysql listener so that mysql avoids 192.168.5.111, because haproxy also needs to listen to the IP address and port 11:3306;

Vim/etc/my. cnf

# Server1

[Mysqld]

Bind-address = 192.168.5.11 # mysql listening. just add this sentence.

# Server2

[Mysqld]

Bind-address = 192.168.5.12

# Restart mysql

Service mysqld restart

# Currently, the database cannot be accessed through 192.168.5.111. configure haproxy

========================================================== ======================================

[Config_haproxy]

# The Master configuration files of the master and slave servers are consistent;

Vim/opt/haproxy/conf/haproxy. cfg

Global # global system configuration

Log 127.0.0.1 local0 info # define the log level [err warning info debug]

# Local0 is a log device and must be one of 24 standard syslog devices;

Maxconn 4096 # maximum number of connections

Uid 0 # The user who runs the program. if there are no other users, the root user is used.

Gid 0

Daemon # run as a slave

Nbproc 1 # Number of processes

Defaults # default configuration

Mode tcp # type of processing http | tcp | health

Option redispatch # after the server corresponding to the serverId fails, it is forcibly redirected to another healthy server.

Retries 3 # the server does not need to use the three failed connections

Timeout connect 5000 # connection timeout

Timeout client 50000 # client timeout

Timeout server 50000 # server timeout

Timeout check 2000 # heartbeat detection timeout

Listen proxy

Bind 192.168.5.111: 3306 # listening address

Mode tcp

Balance roundrobin # defines the load mode, which is round robin.

Log 127.0.0.1 local0 info # define the log type

# Rise 3 three times correctly indicates that the server is available, and fall 3 indicates that three failures indicate that the server is unavailable.

Server db1 192.168.5.11: 3306 check inter 1200 rise 2 fall 3 weight 1

Server db2 192.168.5.12: 3306 check inter 1200 rise 2 fall 3 weight 1

# Server status monitoring configuration. you can view the cluster status through the defined address;

Listen haproxy_stats

Log 127.0.0.1 local0 info

Mode http

Bind 192.168.5.111: 8888

Option httplog

Stats uri/status

Stats realm Haproxy Manager

Stats auth admin: admin # set the account and password of the monitored address

# Add the external definition script described at the end of the keepalived master configuration file

# Start the service

/Opt/haproxy/sbin/haproxy-f/opt/haproxy/conf/haproxy. cfg

# Note: keepalived can run on both the master and slave servers. In fact, only the server that obtains the VIP address is valid;

# However, when haproxy is started, it needs to listen to the VIP address, so the first backup device cannot start the service manually;

# Set the script in the notify_master configuration item of keepalived. the haproxy is started only when the device obtains the VIP address;

# There is a problem here. we only set to end haproxy when keepalived stops the service, but not set whether to end when VIP is handed over;

# In fact, there is no need to worry about this, because there is no VIP address on the host, even the listener is ineffective, and there is no interference;

========================================================== ======================================

# Start testing

# Detection listening

Netstat-tunlp | grep ha

Netstat-tunlp | grep 3306

# On the master device, two programs listen to 3306 of different addresses;

# Currently, mysql master-slave is not configured to facilitate troubleshooting at this stage, so as to perform more accurate tests;

Mysql-udiaosi-p123456-h 192.168.5.111-e "select * from test. mywait ;"

# The returned value should be between server1 and server2;

Sed-I's/^ mysql. * $/# & // '/etc/passwd # Comment out mysql Users

Service mysqld stop

# At this time, the mysql service cannot be started. the mysql_chke.sh script will stop keepalived;

Before keepalived is stopped, haproxy is killed. when the backup device obtains the vip, it starts haproxy through the start_haproxy.sh script;

As a result, the server or mysql server failure will not affect our client's query operations of 111. the experiment is half done;

# However, during the test, the client will not be able to access the database for 2-3 seconds during the VIP address switching process, which is temporarily ignored;

Sed's/^ # // '/etc/passwd # After the test, remember to restore the mysql User;

# After restoring mysql, start mysqld keepalived of the master device, and use ip a to check whether the VIP address is returned;

# Killall haproxy on one of the devices, and then view the cluster status;

Http: // 192.168.5.111: 8888/status

# View and then start haproxy

# Now, load balancing and high availability have been completed. The Load mode is round robin.

========================================================== ======================================

# The three scripts are very simple, so we will not introduce them anymore. They are mainly used for logging and service termination;

#! Bin/bash

# Start_haproxy.sh

Sleep 5

Get = 'IP addr | grep 192.168.5.111 | wc-L'

Echo $ get>/opt/log/start_haproxy.log

If [$ get-eq 1]

Then

Echo "'date + % c' success to get vip">/opt/log/start_haproxy.log

/Opt/haproxy/sbin/haproxy-f/opt/haproxy/conf/haproxy. cfg

Else

Echo "'date + % c' can not get vip">/opt/log/start_haproxy.log

#! Bin/bash

# Stop_haproxy.sh

Pid = 'pidof haproxy'

Echo "'date + % c' stop haproxy">/opt/log/stop_haproxy.log

Kill-9 $ pid

#! Bin/bash

# Stop_keepalived.sh

Pid = 'pidof keepalived'

If [$ pid = ""]

Then

Echo "'date + % c' no keepalived process id">/opt/log/stop_keepalived.log

Else

Echo "'date + % c' will stop keepalived">/opt/log/stop_keepalived.log

/Etc/init. d/keepalived stop

========================================================== ====================

[Mysql Manager Slave]

# Mysql master/slave configuration; add users to two devices;

Root # mysql

Create database db1;

Grant replication slave on *. * TO 'diaosi1' @ '%' identified by '123 ';

# Note: The Slave permission is required. I initially used the all Permission. as a result, the master and Slave nodes are not synchronized at all. it will be OK if I change it to Slave;

Flush privileges;

Show grants for mydiaosi1 @ '% ';

========================================================== ======================================

# Mysql configuration of server1

Server_id = 1 # server ID, unique

Log_bin = mysqlbinlog # enable binary log

Log_bin_index = mysqlbinlog-index # Log index File

Log_slave_updates = 1 # Let the slave server write the events and records copied by itself to its own binary log

Relay_log = relay-log # relay log location; stores the binary file information obtained from the server load balancer master.

Replicate_do_db = db1 # specify the database to be synchronized

# Mysql configuration of server2

Server_id = 2

Log_bin = mysqlbinlog

Log_bin_index = mysqlbinlog-index

Log_slave_updates = 1

Relay_log = relay-log

Replicate_do_db = db1

# Restart two services respectively

Service mysqld restart

Mysql> show master status; # view the current mysql binary log file

+ -------------------- + ---------- + -------------- + ------------------ +

+ -------------------- + ---------- + -------------- + ------------------ +

| Mysqlbinlog.000001 | 98 |

+ -------------------- + ---------- + -------------- + ------------------ +

1 row in set (0.28 sec)

# Connect the mysql log of the other party and start the backup. remember to replace Master_Host and log name with MASTER_LOG_POS;

> Change master to MASTER_HOST = '192. 168.5.12 ', master_port = 192, MASTER_USER = 'mywait', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'mysqlbinlog. 100', MASTER_LOG_POS = 98;

> Start slave; # START synchronization

Mysql> show slave status/G # View mysql synchronization STATUS

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.5.11

Master_User: mywait

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlbinlog.000001

Read_Master_Log_Pos: 98

Relay_Log_File: relay-log.000002

Relay_Log_Pos: 237

Relay_Master_Log_File: mysqlbinlog.000001

Slave_IO_Running: Yes # indicates that the connection to the log file of the other party is successfully obtained;

Slave_ SQL _Running: Yes # The obtained logs are converted into SQL statements and written back to the local database successfully;

Replicate_Do_DB: db1

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: 98

Relay_Log_Space: 237

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

1 row in set (0.00 sec)

# Mainly refer to Slave_IO_Running and Slave_ SQL _Running.

# In most cases, Slave_IO_Running problems, firewalls, user permissions, and whether logs are enabled must be identified;

========================================================== ==================================

# So far, the dual-master and dual-slave mysql architecture has been completed, and all our experiment plans have been completed;

# Enter the full test stage;

# Create a table in DB1 of server1 and assign values

> Use db1;

> Create table mywait (name char (9), phone char (14 ));

> Insert into mywait (name, phone) Value ('wait', 15888888888 );

# Create a user with db1 permissions

Grant all on db1. * to diaoi2 @ '%' identified by '20140901 ';

> Flush privileges;

# Switch to the test database and insert a data entry into the mywait table;

Use test;

Insert into mywait (name, phone) values ('Diaosi', 15002839961 );

# Verify in server2;

> Use db1;

> Show tables;

> Select * from mywait;

# At this time, the data is consistent with that of server1, indicating that mysql synchronization is successful;

Select user, host, password from mysql. user;

# Users created in server1 will also be synchronized by server2;

# Select * from test. mywait

# The test database on server1 is not synchronized;

# Test on client

Mysql-udiaosi2-p123456-h 192.168.5.111-e "select * from db1.mywait ;"

# Complete;

========================================================== ====================

# Problems;

When executing the mysql_chke script, I plan to check whether the service is started in the form of a process;

'PS-C mysqld -- no-header | wc-L'

When you check that the mysql process does not exist, first start mysqld and then detect it. if the service still cannot be started, stop keepalived;

However, some problems may occur after start using/etc/rc. d/init. d/mysqld start;

For example, if the mysql configuration file is incorrect or the user is logged out, the mysql service is no longer available, although it cannot be started manually;

However, the use of ps-C mysqld can still check out a mysqld process. I don't know what the situation is. I hope I can give some advice;

There are many features available for optimization items. haproxy can be used for weight-based distribution, or based on the access address, or even using cookies for judgment;

You can test it more;

In some cases, you need to configure the two servers at the same time. when you look at it, pay more attention to it. it is the best troubleshooting method to read more logs during the test;

# Ip a | ip addr | the ip address effect is the same;

BitsCN.com

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More