nginx代理tcp協議串連mysql

標籤：init   convert   char   查看   代理   mod   ica   技術   log   

環境：

 

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 #啟動MariaDBsystemctl stop mariadb #停止MariaDBsystemctl restart mariadb #重啟MariaDBsystemctl 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 1s;       proxy_timeout 3s;       proxy_pass cloudsocket;    }}

 2.3、重啟nginx

 

/usr/local/nginx/sbin/nginx 

 三、驗證

登入192.168.182.156伺服器執行看是否有3306連接埠的監聽

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

用Navicat for MySQ工具測試是否能串連

 

nginx代理tcp協議串連mysql