Nginx Mysql负载均衡初步配置及验证笔记

需求

两台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>

相关阅读:
OSCP系列靶场-Esay-Dawn
Mysql高级20-性能分析
 应用场景丨迭代市政综合管廊监测系统建设
 C++笔记之关于函数名前的取址符
 Java多线程超级详解(看这篇就足够了)
Vue中使用Switch开关用来控制商品的上架与下架情况、同时根据数据库商品的状态反应到前台、前台修改商品状态保存到数据库
 [附源码]Python计算机毕业设计Django茂名特产销售商城网站
 【SOLIDWORKS学习笔记】工程图基础操作
 字节面经总结
 Tomcat一些漏洞的汇总
原文地址：https://blog.csdn.net/wangdonghao137/article/details/127774014

Nginx Mysql负载均衡 初步配置及验证 笔记

需求