How to use nginx as a load balancer for mysql

Note: the nginx version requires more than 1.9, and you need to add-- with-stream when compiling nginx

Such as:

/ configure-- prefix=/Data/apps/nginx-- with-http_stub_status_module-- with-http_ssl_module-- with-http_realip_module-- with-http_image_filter_module-- with-stream

Be careful

1. Because mysql uses port 3306 by default, when configuring nginx tcp reverse proxy mysql, be careful that the port is not the same as the port that mysql listens on. For example, I use 3307.

two。 Ensure that root users can connect to mysql remotely

Such as database mysql table user

Nginx.conf

This code is appended to the end of the nginx.conf file. Please note that it cannot be added to http {}.

Stream {include / Data/apps/nginx/conf/stream/*.conf;}

Stream/db.conf

Server {listen 3307; # Note that the port cannot be the same as the mysql listeners proxy_pass db;} upstream db {server 127.0.0.1 mysql 3306 server 192.168.233.1 mysql 3306;}

Restart nginx to see if nginx is listening on port 3307

And the php code looks like this.

# in fact, when it is new mysqli, you only need to change the port number to be the same as the port number set by the nginx reverse proxy. $mysqli = new mysqli ('127.0.0.1)

Complete php code