Nginx Proxy TCP protocol connection MySQL

Environment:

Ip	System	Service
192.168.182.155	centos7.4	Installing MARIADB
192.168.182.156	centos7.4	Installing Nginx

First, MARIADB installation and configuration 1.1 in 192.168.182.155 installation mariadb

Yum install mariadb-server mariadb systemctl start mariadb #启动MariaDBsystemctl stop mariadb #停止MariaDBsystemctl Resta RT mariadb #重启MariaDBsystemctl Enable MARIADB #设置开机启动

The following is a simple configuration of mariadb

Mysql_secure_installation

The first is to set the password, you will be prompted to enter the password

Enter current password to root (enter for none): <– First run direct carriage return

Set Password

Set root Password? [y/n] <– whether to set the root user password, enter Y and return or direct enter
New Password: <– Set the root user's password
Re-enter new password: <– Enter the password you set again

Other configurations

Remove anonymous users? [y/n] <– Whether to delete anonymous users, enter

Disallow Root login remotely? [y/n] <– Whether root login is forbidden, enter,

Remove test database and access to it? [y/n] <– Whether to delete the test database, enter

Reload privilege tables now? [y/n] <– whether to reload the permissions table, enter

Initialize MARIADB complete, next Test login

Mysql-uroot-ppassword

Complete.

1.2 Configuring the MARIADB character Set

File/etc/my.cnf

Vi/etc/my.cnf

Add under the [Mysqld] tab

init_connect= ' Set collation_connection = Utf8_unicode_ci ' init_connect= ' Set NAMES UTF8 ' Character-set-server=utf8 Collation-server=utf8_unicode_ci Skip-character-set-client-handshake

File/etc/my.cnf.d/client.cnf

Vi/etc/my.cnf.d/client.cnf

Add in [client]

Default-character-set=utf8

File/etc/my.cnf.d/mysql-clients.cnf

Vi/etc/my.cnf.d/mysql-clients.cnf

Add in [MySQL]

Default-character-set=utf8

Complete configuration, restart MARIADB

Systemctl Restart MARIADB

Then enter MARIADB to view the character set

Mysql> Show variables like "%character%", show variables like "%collation%";

Displayed as

+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | UTF8 |
| character_set_connection | UTF8 |
| Character_set_database | UTF8 |
| Character_set_filesystem | binary |
| Character_set_results | UTF8 |
| Character_set_server | UTF8 |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/share/mysql/charsets/|
+--------------------------+----------------------------+
8 rows in Set (0.00 sec)

+----------------------+-----------------+
| variable_name | Value |
+----------------------+-----------------+
| collation_connection | Utf8_unicode_ci |
| Collation_database | Utf8_unicode_ci |
| Collation_server | Utf8_unicode_ci |
+----------------------+-----------------+
3 Rows in Set (0.00 sec)

The character set configuration is complete.

1.3 Adding users, setting permissions

Create user command

mysql>create user [email protected] identified by ' password ';

Direct creation of user and authorized commands

Mysql>grant all on * * to [e-mail protected] indentified by ' password ';

Grant access to the extranet

Mysql>grant all privileges on * * to [e-mail protected] '% ' identified by ' password ';

Grant permissions and can authorize

Mysql>grant all privileges on * * to [e-mail protected] ' hostname ' identified by ' password ' with GRANT option;

Simple user and permission configuration is basically the case.

Where only partial permissions are granted to change all privileges or all to Select,insert,update,delete,create,drop,index,alter,grant,references,reload, Shutdown,process,file part of it.

1.4 Firewall settings

Add 3306 port access right here, add it after permanent firewall-cmd--zone= public --add-port=3306/tcp--Permanent    firewall-cmd--is-enabled Firewalld.service View list of services started: Systemctl lists- Unit-files|grep enabled

Second, Nginx installation and Configuration 1.1 installation Nginx

Download more than 1.9 version is only supported by more than 1.9 versions, the installation process is slightly

Note that when compiling, add--with-stream

./configure--prefix=/usr/local/Nginx--conf-path=/etc/nginx/nginx.conf \--error-log-path=/var/log/nginx/Error.log--http-log-path=/var/log/nginx/Access.log--pid-path=/var/run/Nginx.pid--Lock-path=/var/run/nginx.Lock  --http-client-body-temp-path=/var/cache/nginx/client_temp--http-proxy-temp-path=/var/cache/nginx/proxy_temp--http-fastcgi-temp-path=/var/cache/nginx/fastcgi_temp--http-uwsgi-temp-path=/var/cache/nginx/uwsgi_temp--http-scgi-temp-path=/var/cache/nginx/scgi_temp--user=nginx--group=Nginx--with-Http_ssl_module--with-Http_realip_module--with-Http_addition_module--with-Http_sub_module--with-Http_dav_module--with-Http_flv_module--with-Http_mp4_module--with-Http_gunzip_module--with-Http_gzip_static_module--with-Http_random_index_module--with-Http_secure_link_module--with-Http_stub_status_module--with-Http_auth_request_module--with-Threads--with-Stream--with-Stream_ssl_module--with-Http_slice_module--with-file-aio--with-http_v2_module--with-ipv6

2.2. Configuration

cat/etc/nginx/Nginx.conf#user nobody;worker_processes1; #error_log logs/Error.log, #error_log logs/error.log notice; #error_log logs/error.log info; #pid logs/nginx.pid;events {worker_connections1024x768;}    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_timeout0; Keepalive_timeout $;    #gzip on; server {Listen the;        server_name localhost; #charset Koi8-R; #access_log logs/Host.access.log Main; Location/{root HTML;        Index index.html index.htm; } #error_page404/404. html; # REDIRECT Server error pages to theStaticPage/50x.html # Error_page - 502 503 504/50x.html; Location= /50x.html {root html; } # Proxy The PHP scripts to Apache listening on127.0.0.1: the# #location~\.php$ {# Proxy_pass http://127.0.0.1;#} # Pass the PHP scripts to FastCGI server listening on127.0.0.1:9000# #location~\.php$ {# root HTML; # Fastcgi_pass127.0.0.1:9000;        # Fastcgi_index index.php; # Fastcgi_param Script_filename/Scripts$fastcgi_script_name;        # include Fastcgi_params; #} # Deny access to. htaccess files,ifApache's Document Root# concurs with Nginx'S One# #location~ /\.ht {# deny all; #}} # anotherVirtualHostusingMix of ip-, name-, and port-based configuration # #server {# listen8000; # Listen Somename:8080;    # server_name somename alias Another.alias; # Location/{# root HTML;    # index index.html index.htm; #} #} # HTTPS Server # #server {# listen443SSL;    # 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;      #} #}}stream {Upstream Cloudsocket {hash $remote _addr consistent;       # $binary _remote_addr; Server 192.168.182.155:3306 weight=5 max_fails=3 fail_timeout=30s;       } server {listen 3306; #数据库服务器监听端口 proxy_connect_timeout 1s;       Proxy_timeout 3s;    Proxy_pass Cloudsocket; }}

2.3. Restart Nginx

Third, verification

Log on to the 192.168.182.156 server to see if there is a 3306-port listener

3306 TCP         0      0 0.0. 0.0:3306            0.0.  0.0:*               LISTEN      89870

Test if you can connect using the Navicat for MYSQ tool

Nginx Proxy TCP protocol connection MySQL