需求
两台Mysql服务器(windows),使用Nginx作为反向代理对Mysql做读数据库的负载均衡。
A机mysql IP端口:192.168.7.161:3306(防火墙已允许该端口通过)
B机mysql IP端口:192.168.7.162:3306(防火墙已允许该端口通过)
Nginx所在机器IP端口:192.168.7.161:3333(与A机共用,防火墙已允许该端口通过)
外界统一通过192.168.7.161:3333访问数据库。
nginx版本:nginx-1.22.1,测试平台:三台机器都是win7 64.
安装
不再赘述,参考本博客文章Nginx 负载均衡 初步配置&验证 笔记_既择远方-风雨兼程的博客-CSDN博客
Nginx为Mysql配置负载均衡
配置nginx-1.22.1\conf目录中的nginx.conf文件,记事本打开修改,在文件尾部增加以下关键配置。
#wdh config mysql dbservers proxy
stream{
upstream mysql_dbServers{
server 192.168.7.161:3306;
server 192.168.7.162:3306;
}
server{
listen 3333;
proxy_pass mysql_dbServers;
proxy_connect_timeout 10s;
proxy_timeout 30s;
}
}
mysql_dbServers节内配置了A机和B机地址及端口供Nginx轮询。server中指定了对外开放3333端口访问nginx-mysql.
修改完上述配置后保存nginx.conf。
cmd窗口中cd到nginx目录使用【nginx -s reload】命令重启nginx;
示例如下
D:\JavaDevEnv\nginx-1.22.1>nginx -s reload
完整nginx.conf内容如下(含前述为Web配置的负载均衡参数)
#user nobody;
worker_processes 1;
#error_log logs/error.log;
#error_log logs/error.log notice;
#error_log logs/error.log info;
#pid logs/nginx.pid;
events {
worker_connections 1024;
}
http {
include mime.types;
default_type application/octet-stream;
#log_format main '$remote_addr - $remote_user [$time_local] "$request" '
# '$status $body_bytes_sent "$http_referer" '
# '"$http_user_agent" "$http_x_forwarded_for"';
#access_log logs/access.log main;
sendfile on;
#tcp_nopush on;
#keepalive_timeout 0;
keepalive_timeout 65;
#gzip on;
server {
listen 8123;
server_name localhost;
#charset koi8-r;
#access_log logs/host.access.log main;
location / {
root html;
index index.html index.htm;
}
#error_page 404 /404.html;
# redirect server error pages to the static page /50x.html
#
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root html;
}
# proxy the PHP scripts to Apache listening on 127.0.0.1:80
#
#location ~ \.php$ {
# proxy_pass http://127.0.0.1;
#}
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
#location ~ \.php$ {
# root html;
# fastcgi_pass 127.0.0.1:9000;
# fastcgi_index index.php;
# fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
# include fastcgi_params;
#}
# deny access to .htaccess files, if Apache's document root
# concurs with nginx's one
#
#location ~ /\.ht {
# deny all;
#}
}
# another virtual host using mix of IP-, name-, and port-based configuration
#
#server {
# listen 8000;
# listen somename:8080;
# server_name somename alias another.alias;
# location / {
# root html;
# index index.html index.htm;
# }
#}
# HTTPS server
#
#server {
# listen 443 ssl;
# server_name localhost;
# ssl_certificate cert.pem;
# ssl_certificate_key cert.key;
# ssl_session_cache shared:SSL:1m;
# ssl_session_timeout 5m;
# ssl_ciphers HIGH:!aNULL:!MD5;
# ssl_prefer_server_ciphers on;
# location / {
# root html;
# index index.html index.htm;
# }
#}
#wdh config the servers
upstream myhttpIISServer{
server 192.168.7.54:8052;
server 192.168.7.161:8051;
}
#wdh config the proxy
server{
listen 8050;
server_name localhost;
location /{
proxy_pass http://myhttpIISServer;
}
}
}
#wdh config mysql dbservers proxy
stream{
upstream mysql_dbServers{
server 192.168.7.161:3306;
server 192.168.7.162:3306;
}
server{
listen 3333;
proxy_pass mysql_dbServers;
proxy_connect_timeout 10s;
proxy_timeout 30s;
}
}
测试mysql负载均衡
测试前的说明
本次测试使用mysql的root账号,测试前需要将mysql的FromHost权限从默认的localhost提升到不限。
测试通过对比同一条指令查询到A机B机数据库列表对比查看。
A机B机的数据库列表数量不一致。A机有11个,B机有7个,A机多。
无nginx时访问A机
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.161 -P 3306 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| dbdemo |
| hibernatetestdb |
| mysql |
| performance_schema |
| sakila |
| spring_08_06_tx |
| springdatajpatestdb |
| sys |
| tacocloud |
| world |
+---------------------+
11 rows in set (0.00 sec)
mysql>
无nginx时访问B机
Microsoft Windows [版本 6.1.7601]
版权所有 (c) 2009 Microsoft Corporation。保留所有权利。
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.162 -P 3306 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbdemo |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql>
增加Nginx配置后访问nginx3333端口
测试步骤及结论
1.cmd窗口通过nginx对外开放的3333端口登录mysql
mysql -h 192.168.7.161 -P 3333 -u root -p
1.1.登录mysql后输入命令【show databases;】查看当前数据库列表。(结论:执行两遍返回均为7rows,证明是连接到了B机)
1.2.在mysql的cmd窗口输入命令【exit;】退出登录(相当于断开当前数据库连接)
2.第二次在cmd窗口通过nginx对外开放的3333端口登录mysql(相当于新建了一个数据库连接)
2.1.登录mysql后输入命令【show databases;】查看当前数据库列表。(结论:执行两遍返回均为11rows,证明是连接到了A机)
2.2在mysql的cmd窗口输入命令【exit;】退出登录(相当于断开当前数据库连接)
3.第三次在cmd窗口通过nginx对外开放的3333端口登录mysql(相当于新建了一个数据库连接)
3.1.登录mysql后输入命令【show databases;】查看当前数据库列表。(结论:执行两遍返回均为7rows,证明是连接到了B机)
3.2在mysql的cmd窗口输入命令【exit;】退出登录(相当于断开当前数据库连接)
4.第四次在cmd窗口通过nginx对外开放的3333端口登录mysql(相当于新建了一个数据库连接)
4.1.登录mysql后输入命令【show databases;】查看当前数据库列表。(结论:执行两遍返回均为11rows,证明是连接到了A机)
4.2在mysql的cmd窗口输入命令【exit;】退出登录(相当于断开当前数据库连接)
结论:nginx已实现了对mysql的负载均衡,nginx为mysql提供的负载均衡是切换不同的数据库连接。
测试过程中cmd命令实际记录(连续)
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.161 -P 3333 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbdemo |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbdemo |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> exit;
Bye
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.161 -P 3333 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| dbdemo |
| hibernatetestdb |
| mysql |
| performance_schema |
| sakila |
| spring_08_06_tx |
| springdatajpatestdb |
| sys |
| tacocloud |
| world |
+---------------------+
11 rows in set (0.00 sec)
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| dbdemo |
| hibernatetestdb |
| mysql |
| performance_schema |
| sakila |
| spring_08_06_tx |
| springdatajpatestdb |
| sys |
| tacocloud |
| world |
+---------------------+
11 rows in set (0.00 sec)
mysql> exit;
Bye
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.161 -P 3333 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbdemo |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbdemo |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.00 sec)
mysql> exit;
Bye
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -h 192.168.7.161 -P 3333 -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| dbdemo |
| hibernatetestdb |
| mysql |
| performance_schema |
| sakila |
| spring_08_06_tx |
| springdatajpatestdb |
| sys |
| tacocloud |
| world |
+---------------------+
11 rows in set (0.00 sec)
mysql> show databases;
+---------------------+
| Database |
+---------------------+
| information_schema |
| dbdemo |
| hibernatetestdb |
| mysql |
| performance_schema |
| sakila |
| spring_08_06_tx |
| springdatajpatestdb |
| sys |
| tacocloud |
| world |
+---------------------+
11 rows in set (0.00 sec)
mysql>