Shulou(Shulou.com)05/31 Report--

How to achieve MySQL database load balancing in Nginx? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

prerequisite

Note: to use Nginx to achieve load balancing of MySQL database, the premise is to build the master replication environment of MySQL. The construction of MySQL master replication environment will be described in detail in the MySQL project later. Here, we assume that the master replication environment for MySQL has been set up, and the IP and port of the MySQL server are shown below.

192.168.1.101 3306

192.168.1.102 3306

The IP and port for accessing MySQL through Nginx are shown below.

192.168.1.100 3306

Implementation of MySQL load balancing with Nginx

Nginx supports tcp load balancer after version 1.9.0. For more information, please see the description of module ngx_stream_core_module on the official website. The link address is as follows:

Http://nginx.org/en/docs/stream/ngx_stream_core_module.html#tcp_nodelay .

Nginx introduces the module ngx_stream_core_module after 1.9.0. The module is not compiled and needs to be compiled. The-- with-stream configuration parameter needs to be added during compilation. The official configuration example of stream load balancer is shown below.

Worker_processes auto; error_log / var/log/nginx/error.log info; events {worker_connections 1024;} stream {upstream backend {hash $remote_addr consistent; server backend1.example.com:12345 weight=5; server 127.0.0.1 worker_connections 12345 max_fails=3 fail_timeout=30s; server unix:/tmp/backend3;} upstream dns {server 192.168.0.1 worker_connections 53535 Server dns.example.com:53;} server {listen 12345; proxy_connect_timeout 1s; proxy_timeout 3s; proxy_pass backend;} server {listen 127.0.0.1 listen 53 udp; proxy_responses 1; proxy_timeout 20s; proxy_pass dns;} server {listen [:: 1]: 12345 Proxy_pass unix:/tmp/stream.socket;}}

At this point, it is relatively simple to use Nginx to achieve load balancing for MySQL. We can configure the load balancer of MySQL by referring to the official configuration example above. Here, we can configure Nginx to look like this.

User nginx; # user root; worker_processes 1; error_log / var/log/nginx/error.log warn; pid / var/run/nginx.pid; events {worker_connections 1024;} http {include / etc/nginx/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 / var/log/nginx/access.log main; sendfile on; # tcp_nopush on; keepalive_timeout 65 # gzip on; include / etc/nginx/conf.d/*.conf;} stream {upstream mysql {server 192.168.1.101 stream 3306 weight=1; server 192.168.1.102 VR 3306 weight=1;} server {listen 3306; server_name 192.168.1.100; proxy_pass mysql;}}

After the configuration is complete, we can access the MySQL database in the following ways.

Jdbc:mysql://192.168.1.100:3306/ database name this is the answer to the question about how to implement MySQL database load balancing in Nginx. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.