Build a mysql Server Load balancer and a high-availability environment and a mysql Server Load balancer

Build a mysql Server Load balancer and a high-availability environment and a mysql Server Load balancer

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 as the local source; yum install-y mysql-server service mysqld start ============================ =====================================[ create_mysql_test_table] # create a table with the same name and field respectively, insert different values to test ha and round robin successfully. # create the same user # server1 mysql> use test;> create table mywait (name char (9 ), phone char (14);> insert into mywait (name, phone) values ('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 '000000';> flush privileges; ========================================================== ==================## test from 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; "# at this point, 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 cd/opt/soft/keepalived-1.2.7/# with-kernel specifies the kernel version, use the TAB key to complete the settings based on your local needs. /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 # keepalived master configuration file # The following configuration is suitable for keepalived itself as a service provider;

! Configuration File for keepalived # simple header. Here, you can set email notification alarms. This setting is not available for the moment. global_defs {icationicationd LVS_DEVEL} # define a script in advance, it is convenient to call later. You can also define multiple options for convenience; vrrp_script mysql_chk {script "/opt/scripts/mysql_chke.sh" interval 2 # The execution interval weight 2 # the result of the script causes the priority to change, succeeded + 2 }# VRRP vro redundancy protocol configuration vrrp_instance VI_1 {# VI_1 is the custom name; state MASTER # indicates that this is a MASTER device, the standby device is BACKUP interface eth0 # specify the physical network adapter virtual_router_id 11 # VRID virtual route ID to be bound to the VIP, which is also called the group name, the devices in this group must have the same priority 150 # define the priority of this device 1-254; advert_int 1 # Send interval of multicast information during survival detection, consistent authentication in the Group {# set verification information, consistent in-group auth_type PASS # There are two types: PASS and AH. Commonly Used PASS auth_pass 111 # password} virtual_ipaddress {# specifies the VIP address, consistent in-group, you can set multiple IP addresses 192.168.5.111/24} track_script {# use the pre-defined script mysql_chk} in this domain. # The Scripts loaded in this section are external scripts and do not need to be pre-defined; # Do not add it. This experiment is added only after haproxy is installed in the second half. yy_master/opt/scripts/start_haproxy.sh # indicates that when the master status is changed, script to be executed: yy_fault/opt/scripts/stop_keepalived.sh # script executed during the fault: yy_stop/opt/scripts/stop_haproxy.sh # keepalived script before stopping 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        fifi

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 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 # users who run the program, no other users here, root gid 0 daemon # Run nbproc 1 # process count defaults # default mode tcp # processed http | tcp | health option redispatch # server corresponding to serverId after mounting, force redirect to retries 3 of other healthy servers # If the connection fails three times, the server does not need timeout connect 5000 # connection timeout client 50000 # client timeout server 50000 # server timeout check 2000 # Heartbeat Detection timeout listen proxy bind 192.168.5.111: 3306 # listener address mode tcp balance roundrobin # defines the load mode. Here, the round-robin log 127.0.0.1 local0 info # defines the log Type # rise 3 three times correctly indicates that the server is available, fall 3 indicates three failures, indicating 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 monitoring 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.shsleep 5get=`ip addr  |grep 192.168.5.111 |wc -l`echo $get >> /opt/log/start_haproxy.logif [ $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.cfgelse        echo "`date +%c` can not get vip" >> /opt/log/start_haproxy.logfi

#!/bin/bash#stop_haproxy.shpid=`pidof haproxy`echo "`date +%c` stop haproxy" >> /opt/log/stop_haproxy.logkill -9 $pid

#!/bin/bash#stop_keepalived.shpid=`pidof keepalived`if [ $pid == "" ]then        echo "`date +%c` no keepalived process id"  >> /opt/log/stop_keepalived.logelse        echo "`date +%c` will stop keepalived "  >> /opt/log/stop_keepalived.log        /etc/init.d/keepalived stopfi

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

[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 '000000'; # note that the Slave permission is required. I initially used the all permission. As a result, the master and Slave nodes are not synchronized at all, change to Slave and then OK; flush privileges; show grants for diaosi1 @ '% '; ========================================================== ======================================#server1 mysql configuration server_id = 1 # server ID, unique log_bin = mysqlbinlog # enable binary log log_bin_index = mysqlbinlog-index # log index file log_slave_updates = 1 # enable the slave server to write its own copied events and records to its own binary log relay_log = relay-log # relay log location; the slave database obtains the binary file information of the master database from the slave database. slave = db1 # specify the database to be synchronized # server2 mysql configuration server_id = 2 log_bin = mysqlbinlog log_bin_index = mysqlbinlog-index log_slave_updates = 1 Relay = relay-log replicate_do_db = db1 # restart two services respectively

Service mysqld restart

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

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| 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 = 'diaosi1', 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;