mysql中怎么實現(xiàn)負載均衡
這篇文章給大家介紹MySQL中怎么實現(xiàn)負載均衡，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
創(chuàng)新互聯(lián)專注于南部企業(yè)網(wǎng)站建設,響應式網(wǎng)站建設,購物商城網(wǎng)站建設。南部網(wǎng)站建設公司,為南部等地區(qū)提供建站服務。全流程按需規(guī)劃網(wǎng)站，專業(yè)設計，全程項目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務
1.環(huán)境：
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)
說明：mdb vip用于DB的寫，sdb vip用于DB讀，實現(xiàn)讀寫分離和負載均衡，帶故障檢測自動切換
2.架構圖
web1 web2 web3
| | |
—————————-
|
haproxy(lb db write/read)
|
———————————-
| |
mdb1 mdb2
| |
————– —————-
| | | |
sdb1 sdb2 sdb3 sdb4
說明：
1）mdb1和mdb1配置成主-主模式，相互同步,通過haproxy提供一個lb的寫ip
2）sdb1和sdb2配置為mdb1的從，sdb3和sdb4配置為mdb2的從
3）sdb1,sdb2,sdb3,sdb4這4臺從庫，通過haproxy提供一個lb的讀ip
4) 當mdb2停止復制，mdb1為主庫，haproxy停止發(fā)送請求到mdb2和sdb3,sdb4
5) 當mdb1停止復制，mdb2為主庫，haproxy停止發(fā)送請求到mdb1和sdb1,sdb2
6) 當mdb1和mdb2同時停止復制，這時2臺主庫變成readonly模式，數(shù)據(jù)庫不能寫入
7）當mdb2 offline時，mdb1進入backup mode,停止發(fā)送請求到mdb2,sdb3,sdb4
8）當mdb1 offline時，mdb2進入backup mode,停止發(fā)送請求到mdb1,sdb1,sdb2
9) 當mdb1 mdb2同時offline,整個DB停止工作
3.安裝mysql-server
登錄mdb1,mdb2,sdb1,sdb2,sdb3,sdb4，輸入以下命令進行安裝：
apt-get install mysql-server -y
安裝時會提示輸入mysql root用戶密碼，輸入gaojinbo.com
修改mysql配置，監(jiān)聽所有接口
vi /etc/mysql/my.cnf
修改為：
bind-address = 0.0.0.0
重啟mysql
/etc/init.d/mysql restart
4.配置mdb1,mdb2主-主同步
1)mdb1:
vi /etc/mysql/my.cnf
server-id = 1
log_bin = mysql-bin
log-slave-updates #很重要，從前一臺機器上同步過來的數(shù)據(jù)才能同步到下一臺機器
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 #很重要，從前一臺機器上同步過來的數(shù)據(jù)才能同步到下一臺機器
expire_logs_days = 10
max_binlog_size = 100M
auto_increment_offset = 2
auto_increment_increment = 2
3)mdb1和mdb2:
重啟mysql
/etc/init.d/mysql restart
添加復制用戶
mysql -uroot -pgaojinbo.com
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.5.%’ IDENTIFIED BY ‘gaojinbo’;
記錄日志文件和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
說明：mysql-bin.000003和106是主庫配置第3)步記錄的信息
出現(xiàn)以下內(nèi)容，說明同步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
說明：mysql-bin.000001和249是主庫配置第3)步記錄的信息
出現(xiàn)以下內(nèi)容，說明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
6）測試主-主同步
mdb1:
mysql -uroot -pgaojinbo.com
show databases;
create database gaojinbo;
mdb2:
mysql -uroot -pgaojinbo.com
show databases;
即可看到在mdb1上建立的數(shù)據(jù)庫gaojinbo
至此mdb1,mdb2主-主配置完成！
5.4臺從庫配置
sdb1-4配置(注：server-id不能相同)：
vi /etc/mysql/my.cnf
server-id = 3
log_bin = mysql-bin
重啟mysql
/etc/init.d/mysql restart
sdb1和sdb2配置成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
說明：mysql-bin.000001和345是主庫配置第3)步記錄的信息
出現(xiàn)以下內(nèi)容，說明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
sdb3和sdb4配置成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
說明：mysql-bin.000003和106是主庫配置第3)步記錄的信息
出現(xiàn)以下內(nèi)容，說明同步ok
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
測試：
mdb1:
mysql -uroot -pgaojinbo.com
show databases;
create database eossc;
在其他DB上，這時會看到剛建立的數(shù)據(jù)庫eossc
至此4臺從數(shù)據(jù)庫配置完成！
6.編寫mysql檢測腳本
1）mdb1和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/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/24 # recommended to put the IPs that need
# to connect exclusively (security purposes)
}
添加服務端口
vi /etc/services
mysqlchk_write 9200/tcp #mysqlchk_write
mysqlchk_replication 9201/tcp #mysqlchk_replication
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
測試同步檢測腳本：
mysql -uroot -pgaojinbo.com
stop slave sql_thread; #或者 stop slave io_thread;
/opt/mysqlchk_replication.sh
mdb2上操作：
添加和mdb1一樣的腳本，把
/opt/mysqlchk_status.sh 里面的192.168.5.11修改為192.168.5.12
/opt/mysqlchk_replication.sh 里面的192.168.5.11修改為192.168.5.12
2）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/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
注：腳本/opt/mysqlchk_replication.sh里面的ip
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"
添加服務端口
vi /etc/services
mysqlchk_replication 9201/tcp #mysqlchk_replication
3)所有DB上操作：
增加檢測腳本執(zhí)行權限
chmod +x /opt/mysql*.sh
重啟系統(tǒng)
reboot
查看監(jiān)聽端口
netstat -antup|grep xinetd
tcp 0 0 0.0.0.0:9200 0.0.0.0:* LISTEN 903/xinetd
tcp 0 0 0.0.0.0:9201 0.0.0.0:* LISTEN 903/xinetd
注：sdb只有9201監(jiān)聽
7.haproxy安裝配置
下載編譯安裝：
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
配置
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.測試
1）正常情況，backup和splitbrain狀態(tài)down
2）停止mdb2復制，mdb2和sdb3,sdb4狀態(tài)down，數(shù)據(jù)庫仍可讀寫
3)同時停止mdb1,mdb2復制，mdb1和sdb1,sdb2,sdb3,sdb4狀態(tài)down，數(shù)據(jù)庫只能讀
4）關閉mdb1數(shù)據(jù)庫，mdb1,mdb2和sdb1,sdb2狀態(tài)down，數(shù)據(jù)庫仍可讀寫
5）關閉mdb2數(shù)據(jù)庫，mdb1,mdb2和sdb3,sdb4狀態(tài)down，數(shù)據(jù)庫仍可讀寫
關于mysql中怎么實現(xiàn)負載均衡就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
分享標題：mysql中怎么實現(xiàn)負載均衡
本文地址：http://bm7419.com/article32/pcidsc.html
成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供外貿(mào)建站、網(wǎng)站排名、靜態(tài)網(wǎng)站、響應式網(wǎng)站、ChatGPT、企業(yè)建站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 建網(wǎng)站應該從哪幾個方面提升用戶體驗 2014-06-30
- 小程序最近又一度火了，淘寶也加入了小程序的隊伍 2013-11-03
- 從SE0的角度談網(wǎng)站改版 2015-06-08
- 響應式網(wǎng)站和自適應網(wǎng)站會有什么樣的區(qū)別？ 2016-08-24
- 網(wǎng)站制作好之后，后期的維護僅僅是給網(wǎng)站添加內(nèi)容嗎 2016-11-12
- 短時間提升網(wǎng)站收錄量的方法 2016-08-05
- 網(wǎng)站建設后期網(wǎng)站維護的內(nèi)容 2016-11-06
- 再論從搜索引擎角度出發(fā)如何做好網(wǎng)站維護工作 2022-05-14
- 分析小程序運營過程中用戶量太少的原因 2016-10-20
- 企業(yè)建站的相關注意細節(jié) 2016-08-07
- 網(wǎng)站維護和運營都做什么工作？ 2016-11-01
- 什么是響應式網(wǎng)站 2015-08-23