Rootop 服务器运维与web架构

2018-04-26
发表者 Venus
linux下nginx使用Let’s Encrypt证书机构颁发ssl证书已关闭评论

linux下nginx使用Let’s Encrypt证书机构颁发ssl证书

Let’s Encrypt是一个证书机构,被浏览器信任,它会颁发一个免费3个月的证书,到期可以重新颁发或者更新。
这个机构搞了一个协议叫ACME (Automatic Certificate Management Environment)
根据这个协议搞了一个官网客户端叫 Certbot 便于给使用者申请证书。

官网地址:https://certbot.eff.org/
github地址:https://github.com/certbot/certbot

# 克隆 certbot:

[root@www ~]# git clone https://github.com/certbot/certbot.git

# 生成证书

[root@www certbot]# ./certbot-auto certonly --webroot -w /mnt/web/wx.rootop.org/ -d wx.rootop.org
--webroot 是通过验证网站目录
-w 是指定网站目录路径
-d 指定域名

Saving debug log to /var/log/letsencrypt/letsencrypt.log
Plugins selected: Authenticator webroot, Installer None
Enter email address (used for urgent renewal and security notices) (Enter 'c' to
cancel): # 输入你邮箱

-------------------------------------------------------------------------------
Please read the Terms of Service at
https://letsencrypt.org/documents/LE-SA-v1.2-November-15-2017.pdf. You must
agree in order to register with the ACME server at
https://acme-v01.api.letsencrypt.org/directory
-------------------------------------------------------------------------------
(A)gree/(C)ancel: # 输入 A 同意

-------------------------------------------------------------------------------
Would you be willing to share your email address with the Electronic Frontier
Foundation, a founding partner of the Let's Encrypt project and the non-profit
organization that develops Certbot? We'd like to send you email about EFF and
our work to encrypt the web, protect its users and defend digital rights.
-------------------------------------------------------------------------------
(Y)es/(N)o: N # 发送邮件,我选了N
Obtaining a new certificate
Performing the following challenges:
http-01 challenge for wx.rootop.org
Using the webroot path /mnt/web/wx.rootop.org for all unmatched domains.
Waiting for verification...
Cleaning up challenges

IMPORTANT NOTES:
 - Congratulations! Your certificate and chain have been saved at:
 /etc/letsencrypt/live/wx.rootop.org/fullchain.pem
 Your key file has been saved at:
 /etc/letsencrypt/live/wx.rootop.org/privkey.pem
 Your cert will expire on 2018-07-25. To obtain a new or tweaked
 version of this certificate in the future, simply run certbot-auto
 again. To non-interactively renew *all* of your certificates, run
 "certbot-auto renew"
 - Your account credentials have been saved in your Certbot
 configuration directory at /etc/letsencrypt. You should make a
 secure backup of this folder now. This configuration directory will
 also contain certificates and private keys obtained by Certbot so
 making regular backups of this folder is ideal.
 - If you like Certbot, please consider supporting our work by:

 Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate
 Donating to EFF: https://eff.org/donate-le

看到 Congratulations 即为成功。
/etc/letsencrypt/live/wx.rootop.org/fullchain.pem 证书路径
/etc/letsencrypt/live/wx.rootop.org/privkey.pem 私钥路径

# 原理推测:

[root@www certbot]# ./certbot-auto certonly --webroot -w /mnt/web/a/ -d wx.rootop.org # 我写了一个非网站目录地址

如果上面的 -w 输入的网站路径不是网站的根目录,那么在验证信息的时候会报错。

Waiting for verification...
Cleaning up challenges
Failed authorization procedure. wx.rootop.org (http-01): urn:acme:error:unauthorized :: The client lacks sufficient authorization :: Invalid response from http://wx.rootop.org/.well-known/acme-challenge/o-2oEswrYaKXstl3mckB9KFRt9kaxPsGhCDPngATQFA: "<html>
<head><title>404 Not Found</title></head>
<body bgcolor="white">
<center><h1>404 Not Found</h1></center>
<hr><center>"

IMPORTANT NOTES:
 - The following errors were reported by the server:

 Domain: wx.rootop.org
 Type: unauthorized
 Detail: Invalid response from
 http://wx.rootop.org/.well-known/acme-challenge/o-2oEswrYaKXstl3mckB9KFRt9kaxPsGhCDPngATQFA:
 "<html>
 <head><title>404 Not Found</title></head>
 <body bgcolor="white">
 <center><h1>404 Not Found</h1></center>
 <hr><center>"

 To fix these errors, please make sure that your domain name was
 entered correctly and the DNS A/AAAA record(s) for that domain
 contain(s) the right IP address.
 - Your account credentials have been saved in your Certbot
 configuration directory at /etc/letsencrypt. You should make a
 secure backup of this folder now. This configuration directory will
 also contain certificates and private keys obtained by Certbot so
 making regular backups of this folder is ideal.

可以看到验证的时候是访问了 http://wx.rootop.org/.well-known/acme-challenge/o-2oEswrYaKXstl3mckB9KFRt9kaxPsGhCDPngATQFA
这个路径的文件,但是我-w指定的路径是错的,并不是网站的目录,所以访问不到。
这样就可以推测,certbot是去网站根目录自动创建文件夹及文件,用于官方确认你对域名拥有所有权。

通过上面的错误提示,如下2个原因。
原因1,未添加dns解析
原因2,网站根目录配置错误
所以要确认dns已经解析及路径无误。

还有一种情况就是nginx做为反向代理,这样的话就需要自己写个location,并指定目录。如:

location ~ ^/.well-known/
{
 root /mnt/web/a/;
}

这样就让服务器在验证的时候,直接去/mnt/web/a下找自动生成的验证文件。

# 配置nginx

listen 443;
ssl on;
ssl_certificate /etc/letsencrypt/live/wx.rootop.org/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/wx.rootop.org/privkey.pem;

重启nginx,就可以https访问了。

证书续期:

[root@www certbot]# ./certbot-auto renew # 执行此命令自动检测续期,不需要干预。
Saving debug log to /var/log/letsencrypt/letsencrypt.log

——————————————————————————-
Processing /etc/letsencrypt/renewal/wx.rootop.org.conf
——————————————————————————-
Cert is due for renewal, auto-renewing…
Plugins selected: Authenticator webroot, Installer None
Renewing an existing certificate
Performing the following challenges:
http-01 challenge for wx.rootop.org
Waiting for verification…
Cleaning up challenges

——————————————————————————-
new certificate deployed without reload, fullchain is
/etc/letsencrypt/live/wx.rootop.org/fullchain.pem
——————————————————————————-
Plugins selected: Authenticator webroot, Installer None

——————————————————————————-

Congratulations, all renewals succeeded. The following certs have been renewed:
/etc/letsencrypt/live/wx.rootop.org/fullchain.pem (success)
——————————————————————————-

2018-04-25
发表者 Venus
fatal: HTTP request failed已关闭评论

fatal: HTTP request failed

git clone报错提示

git clone https://github.com/xxxx.git Initialized empty Git repository in /root/xxxx/.git/ error: while accessing https://github.com/xxxx.git/info/refs fatal: HTTP request failed

解决办法:

yum update -y nss curl libcurl

2018-04-19
发表者 Venus
DBProxy实现禁止不带where条件的delete、update语句已关闭评论

DBProxy实现禁止不带where条件的delete、update语句

github地址:https://github.com/Meituan-Dianping/DBProxy
官方安装文档:https://github.com/Meituan-Dianping/DBProxy/blob/master/doc/QUICK_START.md

环境:centos7
建议独立机器或在docker中运行。

# 克隆代码

[root@localhost ~]# git clone https://github.com/Meituan-Dianping/DBProxy.git

# 安装percona yum源rpm包

[root@localhost ~]# wget -c https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
[root@localhost ~]# rpm -ivh percona-release-0.1-4.noarch.rpm

# 安装依赖包

[root@localhost ~]# yum install -y Percona-Server-devel-55.x86_64 Percona-Server-client-55.x86_64 Percona-Server-shared-55 jemalloc jemalloc-devel libevent libevent-devel openssl openssl-devel lua lua-devel bison flex libtool.x86_64 libffi-devel

# 安装glib 不要按照github文档装

[root@localhost ~]# wget -c https://src.fedoraproject.org/repo/pkgs/mingw-glib2/glib-2.42.0.tar.xz/71af99768063ac24033ac738e2832740/glib-2.42.0.tar.xz
[root@localhost ~]# cd glib-2.42.0
[root@localhost glib-2.42.0]# ./configure --prefix=/usr/ --libdir=/usr/lib64/
[root@localhost glib-2.42.0]# make && make install

# 安装dbproxy

[root@localhost ~]# cd /root/DBProxy/
[root@localhost DBProxy]# sh autogen.sh
[root@localhost DBProxy]# sh bootstrap.sh
[root@localhost DBProxy]# make && make install

# 创建配置文件目录及配置文件

[root@localhost DBProxy]# mkdir /usr/local/mysql-proxy/conf
[root@localhost DBProxy]# cp script/source.cnf.samples /usr/local/mysql-proxy/conf/source.cnf

配置文件中有中文解释。根据自己情况修改。

# 启动服务

[root@localhost ~]# /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/source.cnf

github上说,仅支持mysql5.5、5.6版本。
这里我只用dbproxy中的”不支持没有where条件的update、delete操作”这个功能。
代理了一个mysql5.7的版本,测试更新删除阻断没问题。其它功能就不清楚。

2018-04-18
发表者 Venus
使用SQLAdvisor对SQL语句分析索引优化建议已关闭评论

使用SQLAdvisor对SQL语句分析索引优化建议

SQLAdvisor是美团做的一个分析sql给出索引优化建议的工具。

建议放到一个独立的系统或者docker中。

系统:centos7
项目地址:https://github.com/Meituan-Dianping/SQLAdvisor

安装步骤:
# 拉取代码

[root@localhost ~]# git clone https://github.com/Meituan-Dianping/SQLAdvisor.git

# 安装percona yum源

[root@localhost ~]# wget -c https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-4.noarch.rpm
[root@localhost ~]# rpm -ivh percona-release-0.1-4.noarch.rpm

# 安装依赖

[root@localhost ~]# yum install -y Percona-Server-shared-56 cmake libaio-devel libffi-devel glib2 glib2-devel openssl-devel

# 编译依赖项sqlparser

[root@localhost ~]# cd /root/SQLAdvisor
[root@localhost SQLAdvisor]# cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./
[root@localhost SQLAdvisor]# make && make install

# 安装SQLAdvisor

[root@localhost ~]# cd /root/SQLAdvisor/sqladvisor/
[root@localhost sqladvisor]# cmake -DCMAKE_BUILD_TYPE=debug ./
[root@localhost sqladvisor]# make
然后在当前路径下生成一个 sqladvisor 的可执行文件。

# 拷贝到/usr/local/bin/下便于命令调用

[root@localhost sqladvisor]# cp sqladvisor /usr/local/bin/

# 查看sqladvisor支持的参数

[root@localhost ~]# sqladvisor --help
-f, --defaults-file sqls file
-u, --username username
-p, --password password
-P, --port port
-h, --host host
-d, --dbname database name
-q, --sqls sqls
-v, --verbose 1:output logs 0:output nothing

# 命令行传参调用

[root@localhost ~]# sqladvisor -h x -P x -u x -p 'x' -d x -q "sql" -v 1
注意命令行传参时,参数名与值需要用空格隔开

# 使用配置文件传参调用

[root@localhost ~]# cat sql.cnf
[sqladvisor]
username=root
password=root
host=192.168.10.24
port=3306
dbname=test
sqls=SELECT * FROM sys_login_log WHERE userId = 100580;

如果多个sql语句格式即 sql=sql1;sql2;sql3…
# 开始分析

[root@localhost ~]# sqladvisor -f sql.cnf -v 1
第1步: 对SQL解析优化之后得到的SQL:select `*` AS `*` from `test`.`sys_login_log` where (`userId` = 100580)
第2步:开始解析where中的条件:(`userId` = 100580)
show index from sys_login_log
show table status like 'sys_login_log'
select count(*) from ( select `userId` from `sys_login_log` FORCE INDEX( PRIMARY ) order by id DESC limit 10000) `sys_login_log` where (`userId` = 100580)
第3步:表sys_login_log的行数:365166,limit行数:10000,得到where条件中(`userId` = 100580)的选择度:10000
第4步:开始验证 字段userId是不是主键。表名:sys_login_log
show index from sys_login_log where Key_name = 'PRIMARY' and Column_name ='userId' and Seq_in_index = 1
第5步:字段userId不是主键。表名:sys_login_log
第6步:开始验证 字段userId是不是主键。表名:sys_login_log
show index from sys_login_log where Key_name = 'PRIMARY' and Column_name ='userId' and Seq_in_index = 1
第7步:字段userId不是主键。表名:sys_login_log
第8步:开始验证表中是否已存在相关索引。表名:sys_login_log, 字段名:userId, 在索引中的位置:1
show index from sys_login_log where Column_name ='userId' and Seq_in_index =1
第9步:开始输出表sys_login_log索引优化建议:
Create_Index_SQL:alter table sys_login_log add index idx_userId(userId)
第10步: SQLAdvisor结束!

提示 创建索引 Create_Index_SQL:alter table sys_login_log add index idx_userId(userId)


 

2018-04-13
发表者 Venus
使用ngrok实现内网穿透已关闭评论

使用ngrok实现内网穿透

go官网:https://golang.org/dl/

# 安装 go 环境

[root@www ~]# wget -c https://dl.google.com/go/go1.10.1.linux-amd64.tar.gz
[root@www ~]# tar zxvf go1.10.1.linux-amd64.tar.gz
[root@www ~]# mv go /usr/local/

# 设置环境变量

[root@www ~]# vi /etc/profile
export PATH=$PATH:/usr/local/go/bin

# 生效

[root@www ~]# source /etc/profile

# 下载 ngrok 并解压

[root@www ~]# git clone https://github.com/inconshreveable/ngrok.git

# 生成自签名证书,并编译内置此证书的客户端

[root@www ~]# cd ngrok

NGROK_DOMAIN="ngrok.rootop.org"
openssl genrsa -out base.key 2048
openssl req -new -x509 -nodes -key base.key -days 10000 -subj "/CN=$NGROK_DOMAIN" -out base.pem
openssl genrsa -out server.key 2048
openssl req -new -key server.key -subj "/CN=$NGROK_DOMAIN" -out server.csr
openssl x509 -req -in server.csr -CA base.pem -CAkey base.key -CAcreateserial -days 10000 -out server.crt

# 替换证书

[root@www ngrok]# cp base.pem assets/client/tls/ngrokroot.crt

# 生成服务端 ngrokd,在bin目录下

[root@www ngrok]# make release-server

# 启动服务端

[root@www ngrok]# ./bin/ngrokd -tlsKey=server.key -tlsCrt=server.crt -domain="ngrok.rootop.org" -httpAddr=":8080" -httpsAddr=":8443"
略
[13:20:25 CST 2018/04/13] [INFO] (ngrok/log.Info:112) Listening for public http connections on [::]:8080
[13:20:25 CST 2018/04/13] [INFO] (ngrok/log.Info:112) Listening for public https connections on [::]:8443
[13:20:25 CST 2018/04/13] [INFO] (ngrok/log.Info:112) Listening for control and proxy connections on [::]:4443
httpAddr # 监听http端口 (转发此端口请求到客户端指定端口)
httpsAddr # 监听https端口 (转发此端口请求到客户端指定端口)
4443 端口用于服务端和客户端通信,可以用 -tunnelAddr=":1234" 指定

# 生成linux客户端,在bin目录下

[root@www ngrok]# make release-client

# 生成mac客户端,在bin目录下

[root@www ngrok]# GOOS=darwin GOARCH=amd64 make release-client

# 生成windows客户端,在bin目录下

[root@www ngrok]# GOOS=windows GOARCH=amd64 make release-client

然后在bin目录下会生成一个 windows_amd64 文件夹,是windows的客户端。下载下来。

# 创建客户端配置文件 ngrok.cfg

server_addr: "ngrok.rootop.org:4443"
trust_host_root_certs: false

在命令提示符,启动服务。
[code type="shell"]
C:\ngrok>ngrok.exe -config=ngrok.cfg -log=log.log -subdomain=abc 8080

-config指定配置文件 -log指定日志 -subdomain指定三级域名 加 本地端口
运行以后,出现下面字样就成功了。


这样,访问服务器的8080端口,就会访问到内网机器的8080.
根据提示来看,还有一个web查看界面。


需要注意:
服务端监听的端口需要放行。
三级域名需要解析。