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

This article introduces how to achieve load balancing in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

1. Environment:

Mysql 5

Ubuntu10.04 x86_64

Mdb1 eth0 192.168.5.11

Mdb2 eth0 192.168.5.12

Sdb1 eth0 192.168.5.21

Sdb2 eth0 192.168.5.22

Sdb3 eth0 192.168.5.23

Sdb4 eth0 192.168.5.24

Haproxy

Eth0 192.168.5.10 (mdb vip write)

Eth2 192.168.5.20 (sdb vip read)

Description: mdb vip is used for DB writing, sdb vip is used for DB reading, reading and writing separation and load balancing are realized, and automatic switching with fault detection is realized.

two。 Architecture diagram

Web1 web2 web3

| | |

-

| |

Haproxy (lb db write/read)

| |

-

| | |

Mdb1 mdb2

| | |

--

| | |

Sdb1 sdb2 sdb3 sdb4

Description:

1) mdb1 and mdb1 are configured in master-master mode and synchronized with each other, and a lb write ip is provided through haproxy.

2) sdb1 and sdb2 are configured as slaves of mdb1, and sdb3 and sdb4 are configured as slaves of mdb2

3) the 4 slave libraries of sdb1,sdb2,sdb3,sdb4 provide a read ip of lb through haproxy

4) when mdb2 stops replicating, mdb1 is the main library, and haproxy stops sending requests to mdb2 and sdb3,sdb4

5) when mdb1 stops replicating, mdb2 is the main library, and haproxy stops sending requests to mdb1 and sdb1,sdb2

6) when mdb1 and mdb2 stop replication at the same time, the two main libraries become readonly mode and the database cannot be written

7) when mdb2 offline, mdb1 enters backup mode and stops sending requests to mdb2,sdb3,sdb4

8) when mdb1 offline, mdb2 enters backup mode and stops sending requests to mdb1,sdb1,sdb2

9) when mdb1 mdb2 offline at the same time, the whole DB stops working

3. Install mysql-server

Log in to mdb1,mdb2,sdb1,sdb2,sdb3,sdb4 and enter the following command to install:

Apt-get install mysql-server-y

During installation, you will be prompted for the mysql root user password and gaojinbo.com.

Modify mysql configuration to listen on all interfaces

Vi / etc/mysql/my.cnf

Modified to:

Bind-address = 0.0.0.0

Restart mysql

/ etc/init.d/mysql restart

4. Configure mdb1,mdb2 master-master synchronization

1) mdb1:

Vi / etc/mysql/my.cnf

Server-id = 1

Log_bin = mysql-bin

Log-slave-updates # is important that data synchronized from the previous machine can be synchronized to the next machine

Expire_logs_days = 10

Max_binlog_size = 100m

Auto_increment_offset = 1

Auto_increment_increment = 2

2) mdb2:

Vi / etc/mysql/my.cnf

Server-id = 2

Log_bin = mysql-bin

Log-slave-updates # is important that data synchronized from the previous machine can be synchronized to the next machine

Expire_logs_days = 10

Max_binlog_size = 100m

Auto_increment_offset = 2

Auto_increment_increment = 2

3) mdb1 and mdb2:

Restart mysql

/ etc/init.d/mysql restart

Add replication user

Mysql-uroot-pgaojinbo.com

GRANT REPLICATION SLAVE ON *. * TO 'repl'@'192.168.5.%' IDENTIFIED BY' gaojinbo'

Log files and pos

Mysql-uroot-pgaojinbo.com

Show master status\ G

4) mdb1:

Change master to master_host='192.168.5.12',master_port=3306,master_user='repl',master_password='gaojinbo',master_log_file='mysql-bin.000003',master_log_pos=106

Start slave

Show slave status\ G

Note: mysql-bin.000003 and 106 are the information recorded in step 3) of the main library configuration.

The following appears to explain the synchronization of ok

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

5) mdb2:

Change master to master_host='192.168.5.11',master_port=3306,master_user='repl',master_password='gaojinbo',master_log_file='mysql-bin.000001',master_log_pos=249

Start slave

Show slave status\ G

Note: mysql-bin.000001 and 249 are the information recorded in step 3) of the main library configuration.

The following appears to explain the synchronization of ok

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

6) Test master-master synchronization

Mdb1:

Mysql-uroot-pgaojinbo.com

Show databases

Create database gaojinbo

Mdb2:

Mysql-uroot-pgaojinbo.com

Show databases

You can see the database gaojinbo established on mdb1

So far, the mdb1,mdb2 master-master configuration is complete!

5.4 slave library configuration

Sdb1-4 configuration (Note: server-id cannot be the same):

Vi / etc/mysql/my.cnf

Server-id = 3

Log_bin = mysql-bin

Restart mysql

/ etc/init.d/mysql restart

Sdb1 and sdb2 are configured as slave libraries for mdb1:

Mysql-uroot-pgaojinbo.com

Change master to master_host='192.168.5.11',master_port=3306,master_user='repl',master_password='gaojinbo',master_log_file='mysql-bin.000001',master_log_pos=345

Start slave

Show slave status\ G

Note: mysql-bin.000001 and 345 are the information recorded in step 3) of the main library configuration.

The following appears to explain the synchronization of ok

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Sdb3 and sdb4 are configured as slave libraries for mdb2:

Change master to master_host='192.168.5.12',master_port=3306,master_user='repl',master_password='gaojinbo',master_log_file='mysql-bin.000003',master_log_pos=106

Start slave

Show slave status\ G

Note: mysql-bin.000003 and 106 are the information recorded in step 3) of the main library configuration.

The following appears to explain the synchronization of ok

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Test:

Mdb1:

Mysql-uroot-pgaojinbo.com

Show databases

Create database eossc

On other DB, you will see the newly created database eossc

So far 4 sets have been configured from the database!

6. Write mysql detection script

1) mdb1 and mdb2:

Vi / etc/xinetd.d/mysqlchk

#

# / etc/xinetd.d/mysqlchk

#

Service mysqlchk_write

{

Flags = REUSE

Socket_type = stream

Port = 9200

Wait = no

User = nobody

Server = / opt/mysqlchk_status.sh

Log_on_failure + = USERID

Disable = no

Only_from = 192.168.5.0 only_from 24 # recommended to put the IPs that need

# to connect exclusively (security purposes)

}

Service mysqlchk_replication

{

Flags = REUSE

Socket_type = stream

Port = 9201

Wait = no

User = nobody

Server = / opt/mysqlchk_replication.sh

Log_on_failure + = USERID

Disable = no

Only_from = 192.168.5.0 only_from 24 # recommended to put the IPs that need

# to connect exclusively (security purposes)

}

Add service port

Vi / etc/services

Mysqlchk_write 9200/tcp # mysqlchk_write

Mysqlchk_replication 9201/tcp # mysqlchk_replication

Operations on mdb1:

Vi / opt/mysqlchk_status.sh

#! / bin/bash

MYSQL_HOST= "192.168.5.11"

MYSQL_PORT= "3306"

MYSQL_USERNAME= "root"

MYSQL_PASSWORD= "gaojinbo.com"

ERROR_MSG= `/ usr/bin/mysql-host=$MYSQL_HOST-port=$MYSQL_PORT-user=$MYSQL_USERNAME-password=$MYSQL_PASSWORD-e "show databases;" 2 > / dev/ null`

If ["$ERROR_MSG"! = ""]

Then

# mysql is fine, return http 200

/ bin/echo-e "HTTP/1.1 200 OK\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL is running.\ r\ n"

/ bin/echo-e "\ r\ n"

Else

# mysql is down, return http 503

/ bin/echo-e "HTTP/1.1 503 Service Unavailable\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL is * down*.\ r\ n"

/ bin/echo-e "\ r\ n"

Fi

Vi / opt/mysqlchk_replication.sh

#! / bin/bash

MYSQL_HOST= "192.168.5.11"

MYSQL_PORT= "3306"

MYSQL_USERNAME= "root"

MYSQL_PASSWORD= "gaojinbo.com"

/ usr/bin/mysql-host=$MYSQL_HOST-port=$MYSQL_PORT-user=$MYSQL_USERNAME-password=$MYSQL_PASSWORD-e "show slave status\ G;" > / tmp/check_repl.txt

Iostat= `grep "Slave_IO_Running" / tmp/check_repl.txt | awk'{print $2}'`

Sqlstat= `grep "Slave_SQL_Running" / tmp/check_repl.txt | awk'{print $2}'`

# echo iostat:$iostat and sqlstat:$sqlstat

If ["$iostat" = "No"] | | ["$sqlstat" = "No"]

Then

# mysql is down, return http 503

/ bin/echo-e "HTTP/1.1 503 Service Unavailable\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL replication is * down*.\ r\ n"

/ bin/echo-e "\ r\ n"

Else

# mysql is fine, return http 200

/ bin/echo-e "HTTP/1.1 200 OK\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL replication is running.\ r\ n"

/ bin/echo-e "\ r\ n"

Fi

Test synchronization detection script:

Mysql-uroot-pgaojinbo.com

Stop slave sql_thread; # or stop slave io_thread

/ opt/mysqlchk_replication.sh

Operations on mdb2:

Add the same script as mdb1 to set the

192.168.5.11 in / opt/mysqlchk_status.sh is modified to 192.168.5.12

192.168.5.11 in / opt/mysqlchk_replication.sh is modified to 192.168.5.12

2) Operation on sdb1,sdb2,sdb3,sdb4:

Vi / etc/xinetd.d/mysqlchk

#

# / etc/xinetd.d/mysqlchk

#

Service mysqlchk_replication

{

Flags = REUSE

Socket_type = stream

Port = 9201

Wait = no

User = nobody

Server = / opt/mysqlchk_replication.sh

Log_on_failure + = USERID

Disable = no

Only_from = 192.168.5.0 only_from 24 # recommended to put the IPs that need

# to connect exclusively (security purposes)

}

Vi / opt/mysqlchk_replication.sh

#! / bin/bash

MYSQL_HOST= "192.168.5.21"

MYSQL_PORT= "3306"

MYSQL_USERNAME= "root"

MYSQL_PASSWORD= "gaojinbo.com"

/ usr/bin/mysql-host=$MYSQL_HOST-port=$MYSQL_PORT-user=$MYSQL_USERNAME-password=$MYSQL_PASSWORD-e "show slave status\ G;" > / tmp/check_repl.txt

Iostat= `grep "Slave_IO_Running" / tmp/check_repl.txt | awk'{print $2}'`

Sqlstat= `grep "Slave_SQL_Running" / tmp/check_repl.txt | awk'{print $2}'`

# echo iostat:$iostat and sqlstat:$sqlstat

If ["$iostat" = "No"] | | ["$sqlstat" = "No"]

Then

# mysql is down, return http 503

/ bin/echo-e "HTTP/1.1 503 Service Unavailable\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL replication is * down*.\ r\ n"

/ bin/echo-e "\ r\ n"

Else

# mysql is fine, return http 200

/ bin/echo-e "HTTP/1.1 200 OK\ r\ n"

/ bin/echo-e "Content-Type: Content-Type: text/plain\ r\ n"

/ bin/echo-e "\ r\ n"

/ bin/echo-e "MySQL replication is running.\ r\ n"

/ bin/echo-e "\ r\ n"

Fi

Note: ip in script / opt/mysqlchk_replication.sh

Sdb1 MYSQL_HOST= "192.168.5.21"

Sdb2 MYSQL_HOST= "192.168.5.22"

Sdb3 MYSQL_HOST= "192.168.5.23"

Sdb4 MYSQL_HOST= "192.168.5.24"

Add service port

Vi / etc/services

Mysqlchk_replication 9201/tcp # mysqlchk_replication

3) Operation on all DB:

Increase the permission to execute the detection script

Chmod + x / opt/mysql*.sh

Restart the system

Reboot

View listening port

Netstat-antup | grep xinetd

Tcp 00 0.0.0.0 9200 0.0.0.015 * LISTEN 903/xinetd

Tcp 0 0 0.0.0. 0. 0. 0. 0. 0. 0. 0. 9. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0. 0

Note: sdb only has 9201 snooping

7.haproxy installation configuration

Download, compile and install:

Wget http://haproxy.1wt.eu/download/1.4/src/haproxy-1.4.11.tar.gz

Tar xvzf haproxy-1.4.11.tar.gz

Cd haproxy-1.4.11

Make TARGET=linux26 ARCH=x86_64

Make install

Configuration

Vi / etc/haproxy.cfg

Global

Maxconn 40000

Debug

# quiet

User haproxy

Group haproxy

Nbproc 1

Log 127.0.0.1 local3

Spread-checks 2

Defaults

Timeout server 3s

Timeout connect 3s

Timeout client 60s

Timeout http-request 3s

Timeout queue 3s

Frontend db_write

Bind 192.168.5.10:3306

Default_backend cluster_db_write

Frontend db_read

Bind 192.168.5.20:3306

Default_backend cluster_db_read

Frontend web_haproxy_status

Bind: 80

Default_backend web_status

Frontend monitor_mdb1

Bind 127.0.0.1:9301

Mode http

Acl no_repl_mdb1 nbsrv (mdb1_replication) eq 0

Acl no_repl_mdb2 nbsrv (mdb2_replication) eq 0

Acl no_mdb1 nbsrv (mdb1_status) eq 0

Acl no_mdb2 nbsrv (mdb2_status) eq 0

Monitor-uri / dbs

Monitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb2

Monitor fail if no_mdb1 no_mdb2

Frontend monitor_mdb2

Bind 127.0.0.1:9302

Mode http

Acl no_repl_mdb1 nbsrv (mdb1_replication) eq 0

Acl no_repl_mdb2 nbsrv (mdb2_replication) eq 0

Acl no_mdb1 nbsrv (mdb1_status) eq 0

Acl no_mdb2 nbsrv (mdb2_status) eq 0

Monitor-uri / dbs

Monitor fail unless no_repl_mdb1 no_repl_mdb2 no_mdb1

Monitor fail if no_mdb1 no_mdb2

Frontend monitor_sdb1

Bind 127.0.0.1:9303

Mode http

Acl no_repl_sdb1 nbsrv (sdb1_replication) eq 0

Acl no_repl_mdb1 nbsrv (mdb1_replication) eq 0

Acl no_mdb2 nbsrv (mdb2_status) eq 1

Monitor-uri / dbs

Monitor fail if no_repl_sdb1

Monitor fail if no_repl_mdb1 no_mdb2

Frontend monitor_sdb2

Bind 127.0.0.1:9304

Mode http

Acl no_repl_sdb2 nbsrv (sdb2_replication) eq 0

Acl no_repl_mdb1 nbsrv (mdb1_replication) eq 0

Acl no_mdb2 nbsrv (mdb2_status) eq 1

Monitor-uri / dbs

Monitor fail if no_repl_sdb2

Monitor fail if no_repl_mdb1 no_mdb2

Frontend monitor_sdb3

Bind 127.0.0.1:9305

Mode http

Acl no_repl_sdb3 nbsrv (sdb3_replication) eq 0

Acl no_repl_mdb2 nbsrv (mdb2_replication) eq 0

Acl no_mdb1 nbsrv (mdb1_status) eq 1

Monitor-uri / dbs

Monitor fail if no_repl_sdb3

Monitor fail if no_repl_mdb2 no_mdb1

Frontend monitor_sdb4

Bind 127.0.0.1:9306

Mode http

Acl no_repl_sdb4 nbsrv (sdb4_replication) eq 0

Acl no_repl_mdb2 nbsrv (mdb2_replication) eq 0

Acl no_mdb1 nbsrv (mdb1_status) eq 1

Monitor-uri / dbs

Monitor fail if no_repl_sdb4

Monitor fail if no_repl_mdb2 no_mdb1

Frontend monitor_splitbrain

Bind 127.0.0.1:9300

Mode http

Acl no_repl01 nbsrv (mdb1_replication) eq 0

Acl no_repl02 nbsrv (mdb2_replication) eq 0

Acl mdb1 nbsrv (mdb1_status) eq 1

Acl mdb2 nbsrv (mdb2_status) eq 1

Monitor-uri / dbs

Monitor fail unless no_repl01 no_repl02 mdb1 mdb2

Backend mdb1_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server mdb1 192.168.5.11:3306 check port 9201 inter 1s rise 1 fall 1

Backend mdb2_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server mdb2 192.168.5.12:3306 check port 9201 inter 1s rise 1 fall 1

Backend sdb1_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server sdb1 192.168.5.21:3306 check port 9201 inter 1s rise 1 fall 1

Backend sdb2_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server sdb2 192.168.5.22:3306 check port 9201 inter 1s rise 1 fall 1

Backend sdb3_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server sdb3 192.168.5.23:3306 check port 9201 inter 1s rise 1 fall 1

Backend sdb4_replication

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server sdb4 192.168.5.24:3306 check port 9201 inter 1s rise 1 fall 1

Backend mdb1_status

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server mdb1 192.168.5.11:3306 check port 9200 inter 1s rise 2 fall 2

Backend mdb2_status

Mode tcp

Balance roundrobin

Option tcpka

Option httpchk

Server mdb2 192.168.5.12:3306 check port 9200 inter 1s rise 2 fall 2

Backend cluster_db_write

Mode tcp

Option tcpka

Balance roundrobin

Option httpchk GET / dbs

Server mdb1 192.168.5.11:3306 weight 1 check port 9201 inter 1s rise 5 fall 1

Server mdb2 192.168.5.12:3306 weight 1 check port 9201 inter 1s rise 5 fall 1 backup

Server mdb1_backup 192.168.5.11:3306 weight 1 check port 9301 inter 1s rise 2 fall 2 addr 127.0.0.1 backup

Server mdb2_backup 192.168.5.12:3306 weight 1 check port 9302 inter 1s rise 2 fall 2 addr 127.0.0.1 backup

Backend cluster_db_read

Mode tcp

Option tcpka

Balance roundrobin

Option httpchk GET / dbs

Server mdb1 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1

Server mdb2 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2

Server mdb1_backup 192.168.5.11:3306 weight 1 track cluster_db_write/mdb1_backup

Server mdb2_backup 192.168.5.12:3306 weight 1 track cluster_db_write/mdb2_backup

Server mdb1_splitbrain 192.168.5.11:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1

Server mdb2_splitbrain 192.168.5.12:3306 weight 1 check port 9300 inter 1s rise 1 fall 2 addr 127.0.0.1

Server sdb1_slave 192.168.5.21:3306 weight 1 check port 9303 inter 1s rise 5 fall 1 addr 127.0.0.1

Server sdb2_slave 192.168.5.22:3306 weight 1 check port 9304 inter 1s rise 5 fall 1 addr 127.0.0.1

Server sdb3_slave 192.168.5.23:3306 weight 1 check port 9305 inter 1s rise 5 fall 1 addr 127.0.0.1

Server sdb4_slave 192.168.5.24:3306 weight 1 check port 9306 inter 1s rise 5 fall 1 addr 127.0.0.1

Backend web_status

Mode http

Stats enable

# stats scope

# stats hide-version

Stats refresh 5s

Stats uri / status

Stats realm Haproxy\ statistics

Stats auth ylmf:gaojinbo

8. test

1) normal, backup and splitbrain status down

2) stop mdb2 replication, mdb2 and sdb3,sdb4 status down, and the database can still be read and written

3) stop mdb1,mdb2 replication, mdb1 and sdb1,sdb2,sdb3,sdb4 status down at the same time, and the database can only be read

4) close the mdb1 database, mdb1,mdb2 and sdb1,sdb2 status down, and the database can still be read and written

5) close the mdb2 database, mdb1,mdb2 and sdb3,sdb4 status down, and the database can still be read and written

So much for sharing about how to achieve load balancing in mysql. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can 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.