nginx代理tcp协议连接mysql-ITB运维部落—http://www.itbcn.cn—ITB运维技术交流之家平台
记录工作点滴
分享运维知识

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 [email protected] identified by 'password';

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

mysql>grant all on *.* to [email protected] indentified by 'password';

授予外网登陆权限

mysql>grant all privileges on *.* to [email protected]'%' identified by 'password';

授予权限并且可以授权

mysql>grant all privileges on *.* to [email protected]'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端口的监听

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

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

未经允许不得转载:ITB运维部落—http://www.itbcn.cn—ITB运维技术交流之家平台 » nginx代理tcp协议连接mysql

如果文章对你有帮助,欢迎点击上方按钮打赏作者

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址