• notice
  • Congratulations on the launch of the Sought Tech site

How to use Nginx to achieve load balancing of MySQL database?

write in front

Nginx can implement load balancing of HTTP and HTTPS protocols, as well as load balancing of TCP protocol. So, the question is, is it possible to achieve load balancing of MySQL database through Nginx? The answer is: yes. Next, let's discuss how to use Nginx to achieve MySQL load balancing.

Preconditions

Note: Using Nginx to achieve load balancing of MySQL database, the premise is to build a MySQL master-master replication environment. The construction of MySQL master-master replication environment will be explained in detail in the MySQL topic later. Here, we assume that the master-master replication environment of MySQL has been set up, and the IP and port of the MySQL server are as follows.

  • 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

Nginx implements MySQL load balancing

Nginx supports tcp load balancing after version 1.9.0. For details, please refer to the description of the module ngx_stream_core_module on the official website . The link address is: http://nginx.org/en/docs/stream/ngx_stream_core_module.html#tcp_nodelay.

Nginx introduced 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 when compiling. The official configuration example of stream load balancing 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:12345 max_fails=3 fail_timeout=30s;
        server unix:/tmp/backend3;
    }

    upstream dns {
       server 192.168.0.1: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:53 udp;
        proxy_responses 1;
        proxy_timeout 20s;
        proxy_pass dns;
    }
   
    server {
        listen [::1]:12345;
        proxy_pass unix:/tmp/stream.socket;
    }
}

Having said that, using Nginx to implement MySQL load balancing is relatively simple. We can refer to the above official configuration example to configure MySQL's load balancing. Here, we can configure Nginx as follows.

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:3306 weight=1;
server 192.168.1.102: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/Name database

At this point, Nginx will route requests to access MySQL to MySQL with IP addresses 192.168.1.101 and 192.168.1.102.


Tags

Technical otaku

Sought technology together

Related Topic

0 Comments

Leave a Reply

+