How to use nginx as a load balancer for mysql

Note: The nginx version requirement is 1.9 or above. When compiling nginx, you need to add --with-stream

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

Note

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 mysql listens to. For example, I use 3307

2. Ensure that the root user can remotely connect to mysql

For example, the database mysql table user

##nginx.conf

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

stream{
include /data/apps/nginx/conf/stream/*.conf;
}

stream/db.conf

server {
listen 3307; #注意端口不能跟mysql监听的一样
proxy_pass db;
}
upstream db {
server 127.0.0.1:3306;
server 192.168.233.1:3306;
}

Restart nginx and check whether nginx is listening on port 3307

Then the php code is like this

#其实就是new mysqli的时候只需改端口号与nginx反向代理设置的端口号一样就可以了
$mysqli = new mysqli('127.0.0.1','root','root','test',3307);

Complete php code

<?php
class mysqlclass
{
private static $obj = null; //mysqlclass对象
public $host;
public $database;
public $user;
public $pwd;
public $port;
public $mysqli = null;
//禁止对象被克隆
private function __clone(){}
//禁止外部实例化
private function __construct($host="127.0.0.1",$database="test",$user="root",$pwd="root",$port="3307")
{
$this->host = $host;
$this->database = $database;
$this->user = $user;
$this->pwd = $pwd;
$this->port = $port;
$this->mysqli = $this->db_connect();
}
//获取mysqli连接
private function db_connect()
{
$mysqli = new mysqli($this->host,$this->user,$this->pwd,$this->database,$this->port);
if($mysqli->connect_errno)
{
printf("connect failed: %s\n", $mysqli->connect_errno);
exit();
}
$mysqli->query("set names utf8 ");
return $mysqli;
}
//获取db实例
public static function get_db()
{
if(self::$obj === null)
{
self::$obj = new self();
}
return self::$obj;
}
public function db_query($sql)
{
$result = $this->mysqli->query($sql);
$arr = [];
while ($row = $result->fetch_assoc()) {
$arr[] = $row;
}
$result->close();
$this->mysqli->close();
return $arr;
}
public function db_insert()
{
}
public function db_update()
{
}
public function __destruct() {
$this->mysqli->close();
}
}
$db = mysqlclass::get_db();
$r = $db->db_query("show tables");
var_dump($r);

Result

##

The above is the detailed content of How to use nginx as a load balancer for mysql. For more information, please follow other related articles on the PHP Chinese website!