【TIDB】TIDB 负载均衡

1.安装haproxy软件 

[tidb@mysql1 haproxy-1.8.9]$ sudo make install PREFIX=/usr/local/haproxy
install -d "/usr/local/haproxy/sbin"
install haproxy  "/usr/local/haproxy/sbin"
install -d "/usr/local/haproxy/share/man"/man1
install -m 644 doc/haproxy.1 "/usr/local/haproxy/share/man"/man1
install -d "/usr/local/haproxy/doc/haproxy"
for x in configuration management architecture peers-v2.0 cookie-options lua WURFL-device-detection proxy-protocol linux-syn-cookies network-namespaces DeviceAtlas-device-detection 51Degrees-device-detection netscaler-client-ip-insertion-protocol peers close-options SPOE intro; do \
	install -m 644 doc/$x.txt "/usr/local/haproxy/doc/haproxy" ; \
done
[tidb@mysql1 haproxy-1.8.9]$ /usr/local/haproxy/sbin/haproxy -v
HA-Proxy version 1.8.9-83616ec 2018/05/18
Copyright 2000-2018 Willy Tarreau <willy@haproxy.org>

2.配置haproxy 

[root@mysql1 log]# cat  /usr/local/haproxy/haproxy.cfg
global
	log 127.0.0.1 local0 notice
	user haproxy
	group haproxy
		daemon
		#quiet
		nbproc 40
		chroot /usr/local/haproxy
		pidfile /usr/local/haproxy/haproxy.pid
defaults
	log global
	retries 2
	timeout connect 2s
	timeout client 30000s
	timeout server 30000s
listen mytidbcluster 
	bind 0.0.0.0:3390
	mode tcp
	stats hide-version
	balance roundrobin
	server mysql1 192.168.1.11:4000 check inter 2000 rise 2 fall 3 
	server mysql2 192.168.1.12:4000 check inter 2000 rise 2 fall 3
	server mysql3 192.168.1.13:4000 check inter 2000 rise 2 fall 3
listen haproxy_stats
	mode http
	bind *:8080
	option httplog
	stats refresh 30s
	stats uri /haproxy
	stats realm xxx moritor
	stats realm Haproxy Manager
	stats auth haproxy:haproxy
	stats hide-version

3.登陆访问HAPROXY 

--测试第一个节点HAPROXY 的负载均衡。
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.11 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+

第二节点访问的负载均衡测试

--通过第二个HAPROXY节点访问集群。
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql2     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql1     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+
[root@mysql1 log]# mysql -uroot -h192.168.1.12 -P 3390 -p'rootroot' -e "select @@hostname;select * from test.my_test1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------+
| @@hostname |
+------------+
| mysql3     |
+------------+
+------+------------+
| id   | name       |
+------+------------+
|    1 | 薛双奇1    |
|    2 | 薛双奇2    |
+------+------------+

由此可见，所有节点，均可访问，实现了负载均衡的目的。