Rootop 服务器运维与web架构

2018-04-28
发表者 Venus
python版本问题导致pip install出现证书错误问题已关闭评论

python版本问题导致pip install出现证书错误问题

在python下执行pip install,报错:

$ ./pip install pyopenssl ndg-httpsclient pyasn1
Collecting pyopenssl
Could not fetch URL https://pypi.python.org/simple/pyopenssl/: There was a problem confirming the ssl certificate: [Errno 1] _ssl.c:499: error:1407742E:SSL routines:SSL23_GET_SERVER_H ELLO:tlsv1 alert protocol version – skipping
C:\Python27\lib\site-packages\pip-9.0.1-py2.7.egg\pip\_vendor\requests\packages\urllib3\util\ssl_.py:318: SNIMissingWarning: An HTTPS request has been made, but the SNI (Subject Name In dication) extension to TLS is not available on this platform. This may cause the server to present an incorrect TLS certificate, which can cause validation failures. You can upgrade to a newer version of Python to solve this. For more information, see https://urllib3.readthedocs.io/en/latest/security.html#snimissingwarning.
SNIMissingWarning
C:\Python27\lib\site-packages\pip-9.0.1-py2.7.egg\pip\_vendor\requests\packages\urllib3\util\ssl_.py:122: InsecurePlatformWarning: A true SSLContext object is not available. This preven ts urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. You can upgrade to a newer version of Python to solve this. For more information, see https: //urllib3.readthedocs.io/en/latest/security.html#insecureplatformwarning.
InsecurePlatformWarning
Could not find a version that satisfies the requirement pyopenssl (from versions: )
No matching distribution found for pyopenssl
C:\Python27\lib\site-packages\pip-9.0.1-py2.7.egg\pip\_vendor\requests\packages\urllib3\util\ssl_.py:122: InsecurePlatformWarning: A true SSLContext object is not available. This preven ts urllib3 from configuring SSL appropriately and may cause certain SSL connections to fail. You can upgrade to a newer version of Python to solve this. For more information, see https: //urllib3.readthedocs.io/en/latest/security.html#insecureplatformwarning.
InsecurePlatformWarning

alading@venus MINGW64 /c/Python27/Scripts

最后查原因是python版本低了,老版本中的tls 1.0版本ssl不再支持,需要更新python2.7到最后一个版本。这里更新到python 2.7.14 解决。
$ ./pip.exe install redis
Collecting redis
Downloading https://files.pythonhosted.org/packages/3b/f6/7a76333cf0b9251ecf49efff635015171843d9b977e4ffcf59f9c4428052/redis-2.10.6-py2.py3-none-any.whl (64kB)
Installing collected packages: redis
Successfully installed redis-2.10.6
You are using pip version 9.0.1, however version 10.0.1 is available.
You should consider upgrading via the ‘python -m pip install –upgrade pip’ command.

 

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)