nginx代理MySQL
一、环境准备
ip | 域名解析 | 服务 | 作用 |
---|---|---|---|
10.11.59.38 | c.nginx.com | nginx | 反向代理 |
10.11.59.37 | b.nginx.com | MySQL(mariadb) | 后端数据库 |
10.11.59.147 | a.nginx.com | MySQL(mariadb) | 后端数据库 |
二、部署
1、相互域名解析(可选项)
要是想域名解析的话,每一台都需要配置
[root@c-nginx-com nginx]# vim /etc/hosts
10.11.59.38 c.nginx.com
10.11.59.147 a.nginx.com
10.11.59.37 b.nginx.com
[root@c-nginx-com nginx]# ping -c1 a.nginx.com
PING a.nginx.com (10.11.59.147) 56(84) bytes of data.
64 bytes from a.nginx.com (10.11.59.147): icmp_seq=1 ttl=64 time=0.591 ms
--- a.nginx.com ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.591/0.591/0.591/0.000 ms
2、安装数据库
10.11.59.147、10.11.59.37安装MySQL,如果觉得MySQL安装太慢可以选择mariadb,我选择了MySQL
#!/bin/bash
# by xiaohei
# time 2020-4-28
echo "此脚本用于yum 安装mysql"
if [[ $UID -ne 0 ]];then
echo "使用root 执行此脚本"
exit 1
fi
if [[ $# != 1 ]];then
echo "Usage script (5.5|5.6|5.7|8.0)"
exit 123
fi
echo "清理环境"
systemctl stop mysqld mariadb &>/dev/null
yum erase -y `rpm -qa |grep mariadb` 2>/dev/null
yum erase -y `rpm -qa |grep mysql` 2>/dev/null
rm -rvf /etc/my.cnf /var/lib/mysql /var/log/mysql*
userdel -rf mysql &>/dev/null
ping -c1 -w1 www.baidu.com &>/dev/null
if [[ $? -eq 0 ]];then
yum install -y wget yum-utils &>/dev/null
if [[ $? -ne 0 ]];then
echo "yum 配置错误"
exit 110
fi
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
yum clean all
yum makecache fast
else
echo "网络错误"
exit 22
fi
case $1 in
5.5)
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql55-community
;;
5.6)
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql56-community
;;
5.7)
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community
;;
8.0)
:
;;
*)
yum repolist all |grep mysql
echo "其他版本可自行选择下载并配置"
exit 13
;;
esac
sleep 2
yum -y install mysql-community-server
systemctl start mysqld
echo "启动成功,初始密码如下(mysql5.7前版本没有初始密码)"
grep -o 'root@localhost.*' /var/log/mysqld.log
echo "安装完成"
手动修改密码
[root@b-nginx-com ~]# mysqladmin -u root -p"脚本显示的密码" password 'QianFeng@123'
3、创建数据库和表
mysql> create database db1;
mysql> use db1
mysql> create table t1(id int,name char(50));
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
+---------------+
1 row in set (0.00 sec)
mysql> into t1(id,name) values(1,'b.mysql.com'); #在10.11.59.37上面做的,为了效果需要把name数据换成二个不一样的,其他一样
mysql> select * from t1;
+------+-------------+
| id | name |
+------+-------------+
| 1 | b.mysql.com |
+------+-------------+
1 row in set (0.00 sec)
4、数据库创建一个用户
创建一个用户,为了让用户通过代理机器能够登录后端数据库服务器
mysql>grant all on db1.* to test@'%' identified by '密码';
5、安装部署nginx
版本最少要1.9以上,以为需要用到4层代理,我使用是1.18
[root@c-nginx-com nginx]# nginx -v
nginx version: nginx/1.18.0
配置yum源
[root@c-nginx-com ~]# vim /etc/yum.repos.d/nginx.repo
[nginx-stable]
name=nginx stable repo
baseurl=http://nginx.org/packages/centos/$releasever/$basearch/
gpgcheck=1
enabled=1
gpgkey=https://nginx.org/keys/nginx_signing.key
[nginx-mainline]
name=nginx mainline repo
baseurl=http://nginx.org/packages/mainline/centos/$releasever/$basearch/
gpgcheck=1
enabled=0
gpgkey=https://nginx.org/keys/nginx_signing.key
安装
[root@c-nginx-com ~]# yum -y install nginx
三、配置nginx
四层负载,四层的负载不在http模块里面,他是和http模块同级别的,所以需要在主配置文件去进行配置
[root@c-nginx-com ~]# vim /etc/nginx/nginx.conf
user nginx;
worker_processes 1;
error_log /var/log/nginx/error.log warn;
pid /var/run/nginx.pid;
events {
worker_connections 1024;
}
#-----------------往下--------------------
#四层负载,四层的负载不在http模块里面,他是和http模块同级别的
stream {
upstream mysql {
server 10.11.59.147:3306; #后端数据库的ip和端口,如果进行了域名解析,直接写域名就好
server 10.11.59.37:3306; #同上
}
server {
listen 3306; #如果监听3306,远程登录的时候不用加-p参数
proxy_connect_timeout 10s;
proxy_timeout 30s;
proxy_pass mysql;
}
}
#------------------往上--------------------
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 0;
#keepalive_timeout 65;
#gzip on;
include /etc/nginx/conf.d/*.conf;
}
启动
[root@c-nginx-com ~]# systemctl start nginx
[root@c-nginx-com ~]# systemctl enable nginx
四、访问
[root@c-nginx-com ~]# mysql -u test -p'QianFeng@123' -h '10.11.59.38' -e "select * from db1.t1;"
+------+-------------+
| id | name |
+------+-------------+
| 1 | a.mysql.com |
+------+-------------+
[root@c-nginx-com ~]# mysql -u test -p'QianFeng@123' -h '10.11.59.38' -e "select * from db1.t1;"
+------+-------------+
| id | name |
+------+-------------+
| 1 | b.mysql.com |
+------+-------------+
[root@c-nginx-com ~]# mysql -u test -p'QianFeng@123' -h '10.11.59.38' -e "select * from db1.t1;"
+------+-------------+
| id | name |
+------+-------------+
| 1 | a.mysql.com |
+------+-------------+
[root@c-nginx-com ~]# mysql -u test -p'QianFeng@123' -h '10.11.59.38' -e "select * from db1.t1;"
+------+-------------+
| id | name |
+------+-------------+
| 1 | b.mysql.com |
+------+-------------+