使用Haproxy对MariaDB做负载均衡

2021-07-28 15:50:00

服务器环境:

使用Haproxy对MariaDB做负载均衡


安装

1
2
3
4
5
6
vim /etc/apt/sources.list.d/mariadb.list     # 添加以下两句
deb http://mirror.yongbok.net/mariadb/repo/5.5/ubuntu precise main
deb-src http://mirror.yongbok.net/mariadb/repo/5.5/ubuntu precise main
apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
apt-get update
apt-get install mariadb-server-5.5

/etc/mysql/my.cnf 

多实例配置文件:3306,3307,3308


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
[mysqld_multi]
mysqld      = /usr/bin/mysqld_safe
mysqladmin  = /usr/bin/mysqladmin
user        = root           # 指定用于启动、停止mysql实例的用户
log     = /var/log/mysql/mysqld_multi.log 
[client]
port        = 3306
socket      = /var/run/mysql/mysql.sock
default-character-set   = utf8
[mysqld3306]
port        = 3306
socket      = /var/run/mysql/mysql.sock
pid-file    = /var/run/mysql/mysql.pid
datadir     = /var/lib/mysql/3306
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine  = MyISAM
character-set-server    = utf8
skip-name-resolve
collation-server        = utf8_general_ci
log-error               = /var/log/mysql/mysqld-error.log
log-slow-admin-statements
long-query-time         = 3
slow-query-log
slow-query-log-file     = /var/log/mysql/mysqld-slow.log
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
max_connections     = 1000
tmpdir      = /tmp/
server-id   = 2
log-slave-updates
log-bin         = mysql-bin
binlog_format   = mixed
relay_log       = mysql-relay-bin
replicate-do-db        = wordpress
replicate-ignore-db        = mysql
replicate-ignore-db        = information_schema
replicate-ignore-db        = performance_schema
replicate-ignore-db        = test
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld3307]
port        = 3307
socket      = /var/run/mysql/mysql-3307.sock
pid-file    = /var/run/mysql/mysql-3307.pid
datadir     = /var/lib/mysql/3307
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine  = MyISAM
character-set-server    = utf8
skip-name-resolve
collation-server        = utf8_general_ci
log-error               = /var/log/mysql/mysqld-error-3307.log
log-slow-admin-statements
long-query-time         = 3
slow-query-log
slow-query-log-file     = /var/log/mysql/mysqld-slow-3307.log
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
max_connections     = 1000
tmpdir      = /tmp/
server-id   = 2
log-slave-updates
log-bin         = mysql-bin
binlog_format   = mixed
relay_log       = mysql-relay-bin
replicate-do-db        = futurestar
replicate-do-db        = verywx
replicate-ignore-db        = mysql
replicate-ignore-db        = information_schema
replicate-ignore-db        = performance_schema
replicate-ignore-db        = test
[mysqldump]
quick
max_allowed_packet = 16M
[mysqld3308]
port        = 3308
socket      = /var/run/mysql/mysql-3308.sock
pid-file    = /var/run/mysql/mysql-3308.pid
datadir     = /var/lib/mysql/3308
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-storage-engine  = MyISAM
character-set-server    = utf8
skip-name-resolve
collation-server        = utf8_general_ci
log-error               = /var/log/mysql/mysqld-error-3308.log
log-slow-admin-statements
long-query-time         = 3
slow-query-log
slow-query-log-file     = /var/log/mysql/mysqld-slow-3308.log
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
max_connections     = 1000
tmpdir      = /tmp/
server-id   = 2
log-slave-updates
log-bin         = mysql-bin
binlog_format   = mixed
relay_log       = mysql-relay-bin
replicate-do-db        = futurestar
replicate-do-db        = verywx
replicate-ignore-db        = mysql
replicate-ignore-db        = information_schema
replicate-ignore-db        = performance_schema
replicate-ignore-db        = test
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
socket  = /var/run/mysql/mysql.sock
auto-rehash
default-character-set   = utf8
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


初始化数据库文件:

1
2
3
mysql_install_db --datadir=/var/lib/mysql/3306 --user=mysql
mysql_install_db --datadir=/var/lib/mysql/3307 --user=mysql
mysql_install_db --datadir=/var/lib/mysql/3308 --user=mysql


启动数据库:

1
2
3
mysqld_multi start 3306
mysqld_multi start 3307
mysqld_multi start 3308


查看启动情况:

1
netstat -tunlp | grep -E '(3306|3307|3308)'

使用Haproxy对MariaDB做负载均衡


使用Haproxy对MariaDB做负载均衡

使用Haproxy对MariaDB做负载均衡


安装Haporxy

1
apt-get install haproxy

然后vim etc/default/haproxy

修改 ENABLED=0 -> ENABLED=1 保存。


修改haproxy配置文件:

vim /etc/haproxy/haproxy.cfg

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
# this config needs haproxy-1.1.28 or haproxy-1.2.1
global
    log 127.0.0.1   local0
    log 127.0.0.1   local1 notice
    #log loghost    local0 info
    maxconn 4096
    #chroot /usr/share/haproxy
    user haproxy
    group haproxy
    daemon
    #debug
    #quiet
defaults
    log global
    mode    http
    #option httplog
    option  dontlognull
    retries 3
    option redispatch
    maxconn 2000
    contimeout  5000
    clitimeout  50000
    srvtimeout  50000
listen  mysql 0.0.0.0:6666
    mode tcp
    option mysql-check user root
    balance roundrobin
    server  mysql_1 127.0.0.1:3306 weight 1 check inter 1s rise 2 fall 5
    server  mysql_2 127.0.0.1:3307 weight 1 check inter 1s rise 2 fall 5
    server  mysql_3 127.0.0.1:3308 weight 1 check inter 1s rise 2 fall 5
                                                                                                          
listen stats
    mode http
    bind 0.0.0.0:8888
    stats enable
    stats uri /dbs
    stats realm Global\ statistics
    stats auth admin:admin
                                                                                                          
    errorfile   400 /etc/haproxy/errors/400.http
    errorfile   403 /etc/haproxy/errors/403.http
    errorfile   408 /etc/haproxy/errors/408.http
    errorfile   500 /etc/haproxy/errors/500.http
    errorfile   502 /etc/haproxy/errors/502.http
    errorfile   503 /etc/haproxy/errors/503.http
    errorfile   504 /etc/haproxy/errors/504.http

执行命令:

1
/etc/init.d/haproxy start


测试结果:

使用Haproxy对MariaDB做负载均衡

使用Haproxy对MariaDB做负载均衡

使用Haproxy对MariaDB做负载均衡


查看haproxy监控页面:


使用Haproxy对MariaDB做负载均衡

配置成功。



本文转自birdinroom 51CTO博客，原文链接：http://blog.51cto.com/birdinroom/1408525，如需转载请自行联系原作者

上一篇：grpc（1）：Centos 安装java的grpc服务，使用haproxy进行负载均衡，nginx不支持


下一篇：Photoshop巧用图层样式反复叠加打造一层风格的咖啡杯教程