nginx代理tcp协议连接mysql

环境：

 

ip	系统	服务
192.168.182.155	centos7.4	安装mariadb
192.168.182.156	centos7.4	安装nginx

一、mariadb安装及配置

1.1 在192.168.182.155安装mariadb

yum install mariadb-server mariadb 

systemctl start mariadb #启动MariaDB

systemctl stop mariadb #停止MariaDB

systemctl restart mariadb #重启MariaDB

systemctl enable mariadb #设置开机启动

 

接下来进行MariaDB的相关简单配置

mysql_secure_installation

首先是设置密码，会提示先输入密码

Enter current password for root (enter for none):<–初次运行直接回车

设置密码

Set root password? [Y/n] <– 是否设置root用户密码，输入y并回车或直接回车
New password: <– 设置root用户的密码
Re-enter new password: <– 再输入一次你设置的密码

其他配置

Remove anonymous users? [Y/n] <– 是否删除匿名用户，回车

Disallow root login remotely? [Y/n] <–是否禁止root远程登录,回车,

Remove test database and access to it? [Y/n] <– 是否删除test数据库，回车

Reload privilege tables now? [Y/n] <– 是否重新加载权限表，回车

初始化MariaDB完成，接下来测试登录

mysql -uroot -ppassword

完成。

 

1.2 配置MariaDB的字符集

文件/etc/my.cnf

vi /etc/my.cnf

在[mysqld]标签下添加

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

文件/etc/my.cnf.d/client.cnf

vi /etc/my.cnf.d/client.cnf

在[client]中添加

default-character-set=utf8

文件/etc/my.cnf.d/mysql-clients.cnf

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

在[mysql]中添加

default-character-set=utf8

 全部配置完成，重启mariadb

systemctl restart mariadb

之后进入MariaDB查看字符集

mysql> show variables like "%character%";show variables like "%collation%";

显示为

+--------------------------+----------------------------+
| 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)

字符集配置完成。

 

1.3  添加用户，设置权限

创建用户命令

mysql>create user username@localhost identified by 'password';

直接创建用户并授权的命令

mysql>grant all on *.* to username@localhost indentified by 'password';

授予外网登陆权限 

mysql>grant all privileges on *.* to username@'%' identified by 'password';

授予权限并且可以授权

mysql>grant all privileges on *.* to username@'hostname' identified by 'password' with grant option;

简单的用户和权限配置基本就这样了。

其中只授予部分权限把 其中 all privileges或者all改为select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file其中一部分。

 1.4 防火墙设置

添加3306端口的访问权限，这里添加后永久生效
firewall-cmd --zone=public --add-port=3306/tcp --permanent    
firewall-cmd --reload

启动： systemctl start firewalld
查看状态： systemctl status firewalld 
停止： systemctl disable firewalld
禁用： systemctl stop firewalld
启动服务：systemctl start firewalld.service
关闭服务：systemctl stop firewalld.service
重启服务：systemctl restart firewalld.service
服务的状态：systemctl status firewalld.service
在开机时启用一个服务：systemctl enable firewalld.service
在开机时禁用一个服务：systemctl disable firewalld.service
查看服务是否开机启动：systemctl is-enabled firewalld.service
查看已启动的服务列表：systemctl list-unit-files|grep enabled

 

二、nginx安装及配置

1.1 安装nginx

下载1.9以上版本只有1.9以上版本才支持，安装过程略

注意编译的时候加上--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、配置

cat /etc/nginx/nginx.conf

#user  nobody;
worker_processes  1;

#error_log  logs/error.log;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;

#pid        logs/nginx.pid;


events {
    worker_connections  1024;
}


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_timeout  0;
    keepalive_timeout  65;

    #gzip  on;

    server {
        listen       80;
        server_name  localhost;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;

        location / {
            root   html;
            index  index.html index.htm;
        }

        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    }


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    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 10s;
       proxy_timeout 300s;#设置客户端和代理服务之间的超时时间，如果5分钟内没操作将自动断开。
       proxy_pass cloudsocket;
    }
}

 2.3、重启nginx

 

/usr/local/nginx/sbin/nginx 

 三、验证

登录192.168.182.156服务器执行看是否有3306端口的监听

[root@localhost sbin]# netstat -nap|grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      89870/nginx: master 

用Navicat for MySQ工具测试是否能连接