WARNING: The TCP backlog setting of 511 ......报错解决

安装好redis后,如果系统没有调优,启动的时候会报错:

WARNING: The TCP backlog setting of 511 cannot be enforced because /proc/sys/net/core/somaxconn is set to the lower value of 128.
警告:TCP backlog的值设定是511,这是无法启动的,因为/proc/sys/net/core/somaxconn的设定值是128,比你的511要低。

解决方法:

echo 2048 > /proc/sys/net/core/somaxconn
echo 'net.core.somaxconn=1024' >> /etc/sysctl.conf
sysctl -p

在重启redis服务即可,log里面无报错了·
net.core.somaxconn参数说明:

net.core.somaxconn是linux中的一个kernel参数,表示socket监听(listen)的backlog上限。
backlog是socket的监听队列,当一个请求(request)尚未被处理或建立时,他会进入backlog。
而socket server可以一次性处理backlog中的所有请求,处理后的请求不再位于监听队列中。
当server处理请求较慢,以至于监听队列被填满后,新来的请求会被拒绝。
所以说net.core.somaxconn限制了接收新 TCP 连接侦听队列的大小。
对于一个经常处理新连接的高负载 web服务环境来说,默认的 128 太小了。
大多数环境这个值建议增加到 2048 或者更多。

CentOS6/7中禁用Transparent Huge Pages(大页)

RHEL6/CentOS6版本开始引入了Transparent Huge Pages(THP),从CentOS7版本开始,该特性默认就会启用。尽管THP的本意是为提升内存的性能,不过某些数据库厂商还是建议直接关闭THP(Oracle、MariaDB、MongoDB、Redis等),否则可能会导致性能出现下降,
查看THP的启用状态:

[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never
[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

这个状态就说明都是启用.
禁用THP立即生效:

echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag

写入启动项,下次重启时生效:

chmod +x /etc/rc.d/rc.local
vim /etc/rc.d/rc.local
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
 echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
 fi
 if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
 echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
 fi

最后重启系统,以后再检查THP应该就是被禁用了

[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
 always madvise [never]
[root@localhost ~]# cat /sys/kernel/mm/transparent_hugepage/defrag 
 always madvise [never]

Zabbix housekeeper processes more than 75% busy

收到Zabbix housekeeper processes more than 75% busy 报警,
问题原因
为了防止数据库持续增大,zabbix有个自动删除历史数据的机制,就是housekeeper,而mysql数据库删数据的时候,性能会降低,就会报这个错误,
解决方法如下:
调整HousekeepingFrequency参数,设置为3小时执行一次

vim /usr/local/zabbix/etc/zabbix_server.conf
HousekeepingFrequency=3            #间隔时间
MaxHousekeeperDelete=3000       #最大删除量

重新启动zabbix_server生效:

/etc/init.d/zabbix_server restart

查看log发现删除时已经带上了最大3000行

tail -f /tmp/zabbix_server.log
 10622:20181109:162644.037 resuming Zabbix agent checks on host "ETS-DB": connection restored
 10005:20181109:162646.629 slow query: 6.579011 sec, "delete from history_uint where itemid=40531 limit 3000"
 10005:20181109:162659.356 slow query: 4.364114 sec, "delete from history_uint where itemid=40399 limit 3000"
 10024:20181109:162701.359 item "dbnode1:uptime" became not supported: Unsupported item key.
 10033:20181109:162713.585 item "OA-DB:uptime" became not supported: Unsupported item key.
 10676:20181109:162718.903 cannot send list of active checks to "192.168.255.148": host [PingAn-Bank] not found
 10005:20181109:162722.824 slow query: 23.467607 sec, "delete from history_uint where itemid=40510 limit 3000"
 10680:20181109:162724.601 cannot send list of active checks to "192.168.255.132": host [WINN5F1QHDELEP] not found
 10038:20181109:162735.390 item "dbnode1_zs:uptime" became supported
 10005:20181109:162745.124 slow query: 22.299782 sec, "delete from history_uint where itemid=40515 limit 3000"
 10025:20181109:162757.104 item "dbnode1_zs:uptime" became not supported: Unsupported item key.
 10005:20181109:162806.766 slow query: 21.642425 sec, "delete from history_uint where itemid=40505 limit 3000"
 10005:20181109:162812.225 slow query: 5.458863 sec, "delete from history_uint where itemid=40511 limit 3000"
 10016:20181109:162817.036 item "dbnode1_zs:uptime" became supported
 10005:20181109:162818.444 slow query: 6.217885 sec, "delete from history_uint where itemid=40516 limit 3000"
 10005:20181109:162824.287 slow query: 5.843189 sec, "delete from history_uint where itemid=40506 limit 3000"

平滑升级Nginx到最新版本

Nginx目前爆出在HTTP/2 和 MP4 模块中存在安全漏洞,容易被DOS攻击。
8cf51541726528.jpg

Nginx官方于11月6日发布了新版本,用于修复影响 1.15.6, 1.14.1 之前版本的多个安全问题,被发现的安全问题有一种这样的情况 —— 允许潜在的攻击者触发拒绝服务(DoS)状态并访问敏感的信息,见官方公告:http://nginx.org/en/security_advisories.html
低版本升级到目前最新版nginx-1.14.1方法步骤如下:
1、查看原来安装nginx的版本以及编译的参数:

[root@xshell ~]# /usr/local/nginx/sbin/nginx -V
nginx version: nginx/1.14.0
built by gcc 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC) 
built with OpenSSL 1.0.2o  27 Mar 2018
TLS SNI support enabled
configure arguments: --user=www --group=www --prefix=/usr/local/nginx --with-http_stub_status_module --with-http_ssl_module --with-http_v2_module --with-http_gzip_static_module --with-http_sub_module --with-stream --with-stream_ssl_module --with-openssl=/data/lnmp1.5-full/src/openssl-1.0.2o

2、下载nginx最新稳定版本

wget http://nginx.org/download/nginx-1.14.1.tar.gz

3、解压ningx压缩包并编译make

tar xvf nginx-1.14.1.tar.gz
cd nginx-1.14.1
./configure --user=www --group=www --prefix=/usr/local/nginx --with-http_stub_status_module --with-http_ssl_module --with-http_v2_module --with-http_gzip_static_module --with-http_sub_module --with-stream --with-stream_ssl_module --with-openssl=/data/lnmp1.5-full/src/openssl-1.0.2o
make

4、make编译完后会在安装目录下生成一个objs目录且在该目录下有一个nginx执行文件

[root@xshell nginx-1.14.1]# ll
total 756
-rw-r--r-- 1 www  www  287441 Nov  6 21:52 CHANGES
-rw-r--r-- 1 www  www  438114 Nov  6 21:52 CHANGES.ru
-rw-r--r-- 1 www  www    1397 Nov  6 21:52 LICENSE
-rw-r--r-- 1 root root    376 Nov  9 10:56 Makefile
-rw-r--r-- 1 www  www      49 Nov  6 21:52 README
drwxr-xr-x 6 www  www    4096 Nov  9 10:55 auto
drwxr-xr-x 2 www  www    4096 Nov  9 10:55 conf
-rwxr-xr-x 1 www  www    2502 Nov  6 21:52 configure
drwxr-xr-x 4 www  www    4096 Nov  9 10:55 contrib
drwxr-xr-x 2 www  www    4096 Nov  9 10:55 html
drwxr-xr-x 2 www  www    4096 Nov  9 10:55 man
drwxr-xr-x 3 root root   4096 Nov  9 11:00 objs
drwxr-xr-x 9 www  www    4096 Nov  9 10:55 src
[root@xshell nginx-1.14.1]# ll objs/
total 10348
-rw-r--r-- 1 root root    52252 Nov  9 10:56 Makefile
-rw-r--r-- 1 root root    17763 Nov  9 10:55 autoconf.err
-rwxr-xr-x 1 root root 10394568 Nov  9 11:00 nginx
-rw-r--r-- 1 root root     5341 Nov  9 11:00 nginx.8
-rw-r--r-- 1 root root     7555 Nov  9 10:56 ngx_auto_config.h
-rw-r--r-- 1 root root      657 Nov  9 10:55 ngx_auto_headers.h
-rw-r--r-- 1 root root     8401 Nov  9 10:55 ngx_modules.c
-rw-r--r-- 1 root root    89712 Nov  9 11:00 ngx_modules.o
drwxr-xr-x 9 root root     4096 Nov  9 10:55 src

5、备份老的nginx文件,复制新文件

mv /usr/local/nginx/sbin/nginx /usr/local/nginx/sbin/nginx_bak
cp objs/nginx /usr/local/nginx/sbin/

6、检测配置文件是否正常

/usr/local/nginx/sbin/nginx -t
nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful

7、使用make upgrade替换老的nginx进程

make upgrade
/usr/local/nginx/sbin/nginx -t
nginx: the configuration file /usr/local/nginx/conf/nginx.conf syntax is ok
nginx: configuration file /usr/local/nginx/conf/nginx.conf test is successful
kill -USR2 `cat /usr/local/nginx/logs/nginx.pid`
sleep 1
test -f /usr/local/nginx/logs/nginx.pid.oldbin
kill -QUIT `cat /usr/local/nginx/logs/nginx.pid.oldbin`

8、执行/usr/local/nginx2/sbin/nginx -V查看nginx最新的版本及编译的参数

/usr/local/nginx/sbin/nginx -V
nginx version: nginx/1.14.1
built by gcc 4.8.5 20150623 (Red Hat 4.8.5-28) (GCC) 
built with OpenSSL 1.0.2o  27 Mar 2018
TLS SNI support enabled
configure arguments: --user=www --group=www --prefix=/usr/local/nginx --with-http_stub_status_module --with-http_ssl_module --with-http_v2_module --with-http_gzip_static_module --with-http_sub_module --with-stream --with-stream_ssl_module --with-openssl=/data/lnmp1.5-full/src/openssl-1.0.2o

9、重新reload服务

/usr/local/nginx/sbin/nginx -s reload

至此平滑升级完成

CentOS安装VMware Tools

vmtools有以下功能:

正常执行虚拟机的电源操作。
在执行各种电源操作期间在客户机操作系统中运行 VMware 提供的脚本或用户配置的脚本。
在客户机操作系统中运行应用程序、命令和文件系统操作,以增强客户机自动化。
针对客户机用户操作进行身份验证。
定期从客户机收集网络、磁盘和内存使用情况信息。
生成从客户机操作系统到主机的检测信号,以便 VMware High Availability 可以确定客户机操作系统的可用性。
在客户机操作系统与主机或客户端桌面之间同步时钟。
使客户机文件系统进入静默状态,以便主机可以捕获与文件系统一致的客户机快照。
在使客户机文件系统进入静默状态时,运行 pre-freeze-script.bat 和 post-thaw-script.bat。
打开虚拟机电源后立即自定义客户机操作系统。
在 VMware Workstation 和 VMware Fusion 上,在主机文件系统与客户机文件系统之间启用“共享文件夹”。
支持在客户机操作系统与主机或客户端桌面之间复制和粘贴文本、图形和文件。
没有安装vmtools的虚拟机,通过vsphere client查看虚拟机信息时,是看不到虚拟机IP地址与hostname.

VMware Tools安装步骤:
1、登录vsphere控制台在要安装的虚机上,更改配置,将光驱设置为客户端设备。
1.png
2、右键客户机操作系统--安装VMware Tools
3、ssh登陆要安装的虚机,挂载vmtools镜像

mkdir /root/cdrom
mount /dev/cdrom /root/cdrom

4、解压

cd /root/cdrom
tar zvxf VMwareTools-10.2.1-8267844.tar.gz -C /root/

5、开始安装:

yum install perl -y  #安装perl包
cd /root/cdrom/VMwareTools
./vmware-install.pl 
# 询问是否继续安装,默认为否
open-vm-tools packages are available from the OS vendor and VMware recommends 
using open-vm-tools packages. See http://kb.vmware.com/kb/2073803 for more 
information.
Do you still want to proceed with this installation? [no] yes
 
# 安装路径,默认即可
Creating a new VMware Tools installer database using the tar4 format.
 
Installing VMware Tools.
 
In which directory do you want to install the binary files? 
[/usr/bin] 
 
# 自动启动文件路径,默认即可
What is the directory that contains the init directories (rc0.d/ to rc6.d/)? 
[/etc/rc.d] 
 
# init文件夹路径,默认即可
What is the directory that contains the init scripts? 
[/etc/rc.d/init.d] 
 
# sbin文件夹路径,默认即可
In which directory do you want to install the daemon files? 
[/usr/sbin] 
 
# vmware-tools库目录路径,默认即可
In which directory do you want to install the library files? 
[/usr/lib/vmware-tools] 
 
# vmware-tools库目录不存在,需要创建,默认即可
The path "/usr/lib/vmware-tools" does not exist currently. This program is 
going to create it, including needed parent directories. Is this what you want?
[yes] 
 
# agent library文件夹路径,默认即可
In which directory do you want to install the common agent library files? 
[/usr/lib] 
 
# agent transient文件夹路径,默认即可
In which directory do you want to install the common agent transient files? 
[/var/lib] 
 
# 帮助文档路径,默认即可
In which directory do you want to install the documentation files? 
[/usr/share/doc/vmware-tools] 
 
# 帮助文档文件夹并不存在,需要创建,默认即可
The path "/usr/share/doc/vmware-tools" does not exist currently. This program 
is going to create it, including needed parent directories. Is this what you 
want? [yes] 
 
# 执行configure脚本,默认即可
Before running VMware Tools for the first time, you need to configure it by 
invoking the following command: "/usr/bin/vmware-config-tools.pl". Do you want 
this program to invoke the command for you now? [yes] 
 
# 是否启用文件文件夹共享,esxi环境无法使用,默认即可
The VMware Host-Guest Filesystem allows for shared folders between the host OS 
and the guest OS in a Fusion or Workstation virtual environment.  Do you wish 
to enable this feature? [no] 
 
# vmblock,不支持esxi环境,默认即可
The vmblock enables dragging or copying files between host and guest in a 
Fusion or Workstation virtual environment.  Do you wish to enable this feature?
[no] 
 
# 是否启用VMware automatic kernel modules,默认即可
Would you like to enable VMware automatic kernel modules?
[yes] 
 
# 是否启用vgauth,默认即可
Do you want to enable Guest Authentication (vgauth)? Enabling vgauth is needed 
if you want to enable Common Agent (caf). [yes] 
 
# 是否启用Common Agent,默认即可
Do you want to enable Common Agent (caf)? [no] 
No X install found.

开始安装:

Creating a new initrd boot image for the kernel.
Generating the key and certificate files.
Successfully generated the key and certificate files.
manageSELinux install: The 'semanage' utility was not found.
There was an error configuring the SELinux security context for VMware Tools.  
Please make certain that SELinux is configured correctly.
 
The configuration of VMware Tools 10.2.1 build-8267844 for Linux for this 
running kernel completed successfully.
 
You must restart your X session before any mouse or graphics changes take 
effect.
 
To enable advanced X features (e.g., guest resolution fit, drag and drop, and 
file and text copy/paste), you will need to do one (or more) of the following:
1. Manually start /usr/bin/vmware-user
2. Log out and log back into your desktop session
3. Restart your X session.
 
Enjoy,
 
--the VMware team
 
Found VMware Tools CDROM mounted at /root/cdrom. Ejecting device /dev/sr0 ...

安装完成后会自动卸载cdrom.
6、删除安装包:

rm /root/cdrom /root/VMwareTools -rf

7、查看vmtools服务状态:

systemctl status vmware-tools

参考文档:
https://www.vmware.com/files/cn/pdf/vmware-tools-installation-configuration.pdf
https://ngx.hk/2018/08/22/vmware-tools%E7%AE%80%E4%BB%8B%E4%B8%8E%E5%AE%89%E8%A3%85.html

CentOS 7.5快速安装ss服务

gfw
1、安装epel源:

yum install epel-release -y

2、更新当前系统:

yum update -y

3、安装升级pip

yum install python-pip -y
pip install -U pip

4、pip安装shadowsocks

pip install shadowsocks

5、创建配置文件:

cat <<EOF>> /etc/shadowsocks.json
{
    "server":"VPS IP",
    "local_address":"127.0.0.1",
    "local_port":8888,
    "port_password":{
         "9991":"PASSWORD",    #根据需要增减
         "9992":"PASSWORD"
    },
    "timeout":600,
    "method":"aes-256-cfb",
    "fast_open": false
}
EOF

参数说明:

server:主机名或服务器IP(IPv4/IPv6)。
server_port:服务器端口号。
local_port:本地端口号。
port_password:用于加密传输的端口和密码。
timeout:连接超时(以秒为单位)。
method:加密方法,“bf-cfb”,“aes-256-cfb”,“des-cfb”,“rc4”等。默认为表,不安全。建议使用“aes-256-cfb”。
auth:一次性认证,设置为true以启用一次性认证功能。
fast_open:true 或 false。Linux 内核在3.7+,可以开启 fast_open 以降低延迟。

6、启动服务并加入开机启动项:

/usr/bin/python2 /usr/bin/ssserver -c /etc/shadowsocks.json >> /data/ss.log 2>&1 &
echo "/usr/bin/python2 /usr/bin/ssserver -c /etc/shadowsocks.json >> /data/ss.log 2>&1 &" >> /etc/rc.d/rc.local
chmod +x /etc/rc.d/rc.local 

至此,安装完成,直接在ShadowsocksR客户端配置相关IP和端口密码、加密算法即可。

Docker环境下安装部署Redis5.0挂载外部配置和数据

作为一种新兴的虚拟化方式,Docker 跟传统的虚拟化方式相比具有众多的优势。
docker-redis.png
本文记录docker下安装部署redis5.0的过程:
os版本centos7.5
先更新 yum 软件管理器,然后再安装 Docker

[root@localhost /] yum -y update
[root@localhost /] yum install -y docker

验证安装,查看 Docker 版本信息

[root@localhost /] docker -v
Docker version 1.13.1, build 8633870/1.13.1
You have new mail in /var/spool/mail/root

启动、停止、重启docker服务、加入开机启动

systemctl start docker
systemctl stop docker
systemctl restart docker
systemctl enable docker

安装redis5.0镜像

[root@sso-nginx-b ~]# systemctl start docker
[root@sso-nginx-b ~]# docker pull redis:5.0
Trying to pull repository docker.io/library/redis ... 
5.0: Pulling from docker.io/library/redis
f17d81b4b692: Pull complete 
b32474098757: Pull complete 
8980cabe8bc2: Pull complete 
2719bdbf9516: Pull complete 
f306130d78e3: Pull complete 
3e09204f8155: Pull complete 
Digest: sha256:481678b4b5ea1cb4e8d38ed6677b2da9b9e057cf7e1b6c988ba96651c6f6eff3
Status: Downloaded newer image for docker.io/redis:5.0

创建redis运行目录及文件

mkdir -p /data/docker/redis
touch /data/docker/redis/redis.conf

redis.conf文件内容如下:

bind 172.17.0.2 127.0.0.1
#bind 0.0.0.0
protected-mode yes
port 6380
tcp-backlog 511
timeout 0
tcp-keepalive 300
daemonize no   #如果daemonize yes无法启动容器
supervised no
pidfile /data/docker/redis/6380.pid
loglevel notice
logfile "6380.log"
databases 16
always-show-logo yes
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename dump.rdb
dir ./
slave-serve-stale-data yes
slave-read-only yes
repl-diskless-sync no
repl-diskless-sync-delay 5
repl-disable-tcp-nodelay no
slave-priority 100
lazyfree-lazy-eviction no
lazyfree-lazy-expire no
lazyfree-lazy-server-del no
slave-lazy-flush no
appendonly no
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
aof-use-rdb-preamble no
lua-time-limit 5000
slowlog-log-slower-than 10000
slowlog-max-len 128
latency-monitor-threshold 0
notify-keyspace-events ""
hash-max-ziplist-entries 512
hash-max-ziplist-value 64
list-max-ziplist-size -2
list-compress-depth 0
set-max-intset-entries 512
zset-max-ziplist-entries 128
zset-max-ziplist-value 64
hll-sparse-max-bytes 3000
activerehashing yes
client-output-buffer-limit normal 0 0 0
client-output-buffer-limit slave 256mb 64mb 60
client-output-buffer-limit pubsub 32mb 8mb 60
hz 10
aof-rewrite-incremental-fsync yes

创建redis实例:

docker run -d --privileged=true --restart=always -p 6380:6380 -v /data/docker/redis/redis.conf:/etc/redis/redis.conf -v /data/docker/redis:/data --name redistest1 redis:5.0 redis-server /etc/redis/redis.conf --appendonly yes

实例说明如下:

--privileged=true 容器内的root拥有真正root权限,否则容器内root只是外部普通用户权限
--restart=always 开机启动容器
-p 6380:6380 映射宿主机6380端口到docker端口6380
-v /data/docker/redis/redis.conf:/etc/redis/redis.conf   映射配置文件
-v /data/docker/redis:/data  映射数据目录地址
--name redistest1 redis:5.0 redis-server /etc/redis/redis.conf   #docker名字 redis实例名 配置文件
--appendonly yes:开启持久化

查看redistest1容器的IP地址:

docker inspect redistest1 | grep -i address
            "LinkLocalIPv6Address": "",
            "SecondaryIPAddresses": null,
            "SecondaryIPv6Addresses": null,
            "GlobalIPv6Address": "",
            "IPAddress": "172.17.0.2",
            "MacAddress": "02:42:ac:11:00:02",
                    "IPAddress": "172.17.0.2",
                    "GlobalIPv6Address": "",
                    "MacAddress": "02:42:ac:11:00:02"

将/data/docker/redis/redis.conf里面的bind地址修改为

bind 172.17.0.2 127.0.0.1

重启redistest1容器:

docker restart redistest1

进入redis控制台:

redis-cli -h 172.17.0.2 -p 6380  #172.17.0.2是redistest1容器的IP地址,也可以用宿主机IP地址进入

启动另外一个redis实例:

docker run -d --privileged=true --restart=always -p 6381:6381 -v /data/docker/redis-6381/redis.conf:/etc/redis/redis.conf -v /data/docker/redis-6381:/data --name redistest2 redis:5.0 redis-server /etc/redis/redis.conf --appendonly yes

至此,docker上安装部署redis完毕.

Windows修改端口连接数范围及快速释放time_wait

查看当前端口范围:(win10/win2008/win2012默认是一致的)

C:\Users\Administrator>netsh int ipv4 show dynamicportrange tcp

协议 tcp 动态端口范围
---------------------------------
启动端口        : 49152
端口数          : 16384

C:\Users\Administrator>netsh int ipv4 show dynamicportrange udp

协议 udp 动态端口范围
---------------------------------
启动端口        : 49152
端口数          : 16384

C:\Users\Administrator>netsh int ipv6 show dynamicport tcp

协议 tcp 动态端口范围
---------------------------------
启动端口        : 49152
端口数          : 16384

C:\Users\Administrator>netsh int ipv6 show dynamicport udp

协议 udp 动态端口范围
---------------------------------
启动端口        : 49152
端口数          : 16384

调整此范围:
netsh int < ipv4 | ipv6 > 设置动态 < tcp | udp > 开始数数 = =范围

netsh int ipv4 set dynamicport tcp start=2000 num=63000
netsh int ipv4 set dynamicport udp start=2000 num=63000
netsh int ipv6 set dynamicport tcp start=2000 num=63000
netsh int ipv6 set dynamicport udp start=2000 num=63000

time_wait超时时间调整

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters
“MaxUserPort”=dword:0000fffe
“TcpTimeWaitDelay”=dword:0000005 (缩短为5秒)

重启生效。
via: https://support.microsoft.com/zh-cn/help/929851/the-default-dynamic-port-range-for-tcp-ip-has-changed-in-windows-vista
http://www.361way.com/win-dynamic-port-timewait/5255.html

MySQL5.7.5X在线调整innodb_buffer_pool_size参数

MySQL5.7.5以前,调整innodb_buffer_pool_size需要重启mysql进程才可以生效,建议业务低峰时间执行,在MySQL 5.7.5版本后,innodb_buffer_pool_size参数的值可以动态的设置,可以通过innodb_buffer_pool_chunk_size参数配置块的大小,Innodb_buffer_pool_resize_status状态变量记录了从调整操作的状态。

innodb_buffer_pool_size参数表示缓冲池字节大小,InnoDB缓存表和索引数据的内存区域。mysql默认的值是128M。最大值与你的CPU体系结构有关,在32位操作系统,最大值是 4294967295 (2^32-1) ,在64 位操作系统,最大值为18446744073709551615 (2^64-1)。在32位操作系统中,CPU和操作系统实用的最大大小低于设置的最大值。

innodb_log_file_size表示在一个日志组每个日志文件的字节大小。日志文件的总大小(innodb_log_file_size* innodb_log_files_in_group)不能超过最高值512GB。例如一对255 GB的日志文件,已经接近了极限,不能超过它。默认值是48M。比较合适的值的范围是从1MB到1 / N个的缓冲池大小,其中N是该组中的日志文件的数量。该值越大,缓冲池中必要的检查点刷新活动就会越少,节省磁盘I/ O。但是越大的日志文件,mysql的崩溃恢复就越慢,尽管在mysql5.5之后改进了恢复性能和日志文件恢复的代价,一般2G左右就可以了;

在线调整innodb_buffer_pool_size方法如下:
当前大小为128M

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
row in set (0.03 sec)

select 134217728/1024/1024;  
+---------------------+
| 134217728/1024/1024 |
+---------------------+
|        128.00000000 |
+---------------------+
row in set (0.00 sec)

动态调整为512M

set global innodb_buffer_pool_size = 512*1024*1024;
Query OK, 0 rows affected (0.18 sec)

show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
row in set (0.02 sec)

select 536870912/1024/1024;
+---------------------+
| 536870912/1024/1024 |
+---------------------+
|        512.00000000 |
+---------------------+
1 row in set (0.00 sec)

调整时,内部会把数据页移动到一个新的位置,单位是块。如果想提升移动速度,则需要调整
innodb_buffer_pool_chunk_size的参数大小,默认是128M
innodb_buffer_pool_size/innodb_buffer_pool_instances = innodb_buffer_pool_chunk_size的参数大小,默认是128M
调整完以后在my.cnf的mysqld部分增加

innodb_buffer_pool_size = 536870912

以防止重启后仍然生效。

Siege安装与使用 并发性能压力测试工具

siege 是Linux/Unix下的一个WEB系统的压力性能测试工具,官网地址https://www.joedog.org/siege-home
目前最新版本是siege-4.0.4
下载编译安装:

wget http://download.joedog.org/siege/siege-4.0.4.tar.gz
tar xvf siege-4.0.4.tar.gz
./configure
make
make install
[root@hongsin-monitor data]# siege -V
SIEGE 4.0.4

Copyright (C) 2017 by Jeffrey Fulmer, et al.
This is free software; see the source for copying conditions.
There is NO warranty; not even for MERCHANTABILITY or FITNESS
FOR A PARTICULAR PURPOSE.

参数说明:
可以使用"siege -h"命令来查看帮助信息:

[root@hongsin-monitor data]# siege -h
SIEGE 4.0.4
Usage: siege [options]
       siege [options] URL
       siege -g URL
Options:
  -V, --version             VERSION, prints the version number.
  -h, --help                HELP, prints this section.
  -C, --config              CONFIGURATION, show the current config.
  -v, --verbose             VERBOSE, prints notification to screen. 要不要显示过程。
  -q, --quiet               QUIET turns verbose off and suppresses output.
  -g, --get                 GET, pull down HTTP headers and display the
                            transaction. Great for application debugging.
  -p, --print               PRINT, like GET only it prints the entire page.
  -c, --concurrent=NUM      CONCURRENT users, default is 10
  -r, --reps=NUM            REPS, number of times to run the test.
  -t, --time=NUMm           TIMED testing where "m" is modifier S, M, or H
                            ex: --time=1H, one hour test.
  -d, --delay=NUM           Time DELAY, random delay before each requst
  -b, --benchmark           BENCHMARK: no delays between requests.
  -i, --internet            INTERNET user simulation, hits URLs randomly.
  -f, --file=FILE           FILE, select a specific URLS FILE.
  -R, --rc=FILE             RC, specify an siegerc file
  -l, --log[=FILE]          LOG to FILE. If FILE is not specified, the
                            default is used: PREFIX/var/siege.log
  -m, --mark="text"         MARK, mark the log file with a string.
                            between .001 and NUM. (NOT COUNTED IN STATS)
  -H, --header="text"       Add a header to request (can be many)
  -A, --user-agent="text"   Sets User-Agent in request
  -T, --content-type="text" Sets Content-Type in request
      --no-parser           NO PARSER, turn off the HTML page parser
      --no-follow           NO FOLLOW, do not follow HTTP redirects

1088183-20180124190620881-1013084004.png
使用说明:
(1) 直接请求URL:

siege -c 200 -r 100 http://www.xxx.com

参数说明: -c 是并发量,并发数为200人 -r 是重复次数, 重复100次

(2) 随机选取urls.txt中列出所有的网址

在当前目录下创建一个名为"urls-demo.txt"的文件。 文件里边填写URL地址,可以有多条,每行一条,比如:

# URLs:
http://www.sogou.com/web?query=php&from=wang_yong_tao
https://www.baidu.com/

执行

siege -c 5 -r 10 -f urls-demo.txt $ siege -c 5 -r 10 -f /tmp/urls-demo.txt

参数说明: -c 是并发量,并发数为5人 -r 是重复次数, 重复10次 -f 指定使用文件,urls-demo.txt就是一个文本文件,每行都是一个url,会从里面随机访问的

Siege从Siege-V2.06起支持POST和GET两种请求方式。 如果想模拟POST请求,可以在urls-demo.txt中安装一下格式填写URL:

# URL (POST):
http://xxx.com/index.php POST UserId=XXX&StartIndex=0&OS=Android&Sign=cff6wyt505wyt4c
http://xxx.com/articles.php POST UserId=XXX&StartIndex=0&OS=iOS&Sign=cff63w5905wyt4c

使用示例:

// 请求http://www.xxx.com,并发人数为10,重复5次,每次请求间隔3秒
$ siege --concurrent=10 --reps=5 --delay=3 http://www.xxx.com
$ siege -c 10 -r 5 -d 3 http://www.xxx.com

发送POST请求
新建参数文件

vim uncollect.json
{
    "token":"c23f5fac69190de92a50973803409811",
    "type":0,
    "uni_id":"1acbb47a1d4f7b1f29cca86de70c84b4"
}

压力测试语句

siege -c 10 -r 30 --H "Content-Type:application/json" "http://47.104.18.17:9005/api/v1.0/user/collect?appName=leqv&version=99&build=1.0.0&os=ios&channel=appstore  POST < ./uncollect.json"

以上语句的意思就是:加一个headers头的 Content-Type:application/json 然后POST发送uncollect.json中的数据

Siege输出结果说明

Transactions:   siege对服务器的访问次数。如果页面发生了redirect,那么siege会将跳转过的请求算成是另一个transaction
Availability:   socket连接的成功率。算法是,如果页面发生了timeout,4xx,5xx,那么该请求算是失败请求,成功率就等于(所有请求-失败请求) / 总请求数
Elapsed time:   所有请求耗费的时间
Data transferred: 所有请求传输的数据量,包括请求的headers和content。所以这个数值可能比server端统计的数值要大一点
Response time: 平均响应时间
Transaction rate: Transactions / Elapsed time
Throughput: 每秒平均传输的数据量
Concurrency: 平均并发的请求数
Successful transactions: 所有status code < 400的transactions数量
Failed transactions: 所有status code >= 400的transactions数量
Longest transaction: 最耗时的请求时间
Shortest transaction: 最短单个请求时间

模拟移动站点(移动端:Android、iOS)访问进行压测
需要发送相应的user-agent,服务器就会根据这个值判断浏览器是移动版还是桌面版,在这里用iphone的user-agent模拟iPhone5对百度站点进行访问。

siege -c1 -r1 -A"Apple-iPhone5C2/1001.525" http://www.baidu.com
siege -c1 -r1 -A"Apple-iPhone5C2/1001.525" http://www.baidu.com
[alert] Zip encoding disabled; siege requires zlib support to enable it
** SIEGE 4.0.4
** Preparing 1 concurrent users for battle.
The server is now under siege...
HTTP/1.1 200     0.09 secs:    2925 bytes ==> GET  /
HTTP/1.1 200     0.07 secs:    2340 bytes ==> GET  /static/index/u.png

Transactions:                   2 hits
Availability:              100.00 %
Elapsed time:                0.16 secs
Data transferred:            0.01 MB
Response time:                0.08 secs
Transaction rate:           12.50 trans/sec
Throughput:                0.03 MB/sec
Concurrency:                1.00
Successful transactions:           2
Failed transactions:               0
Longest transaction:            0.09
Shortest transaction:            0.07
1、发送post请求时,url格式为:http://www.xxxx.com/ POST p1=v1&p2=v2
2、如果url中含有空格和中文,要先进行url编码,否则siege发送的请求url不准确
3、siege -C 可以查看相关的配置参数,可以自行修改,比如是否显示log,超时时间

Chrome浏览器对于POST页面执行history.back返回或表单数据丢失的解决办法

PHP页面POST数据页面时,都会都POST的数据进行校验,如果不符合的数据或是空项我们都会给出提示,并返回前一个页面。但是经常发现有用户提出,会出现返回后当前页面所填写的内容都丢失了,或是出现页面无法显示的问题。

确认重新提交表单 此网页需要使用您之前输入的数据才能正常显示。您可以重新发送这些数据,不过,这么做会重复执行此网页之前执行过的所有操作。
按“重新加载”按钮,重新提交加载该网页所需的数据。
ERR_CACHE_MISS

如下图:
post-page.png

原因:
是因为使用session_start()的问题,我们可以这样处理,在你的 Session_start 函数后加入 header("Cache-control:private");
"private" 则表示该响应是专用于某单个用户的,中间人不能缓存此响应,该响应只能应用于浏览器私有缓存中。
注意在本行之前你的PHP程序不能有任何输出。
解决方法如下:

session_start();//开启session功能,此前不能有任何输出
header("Cache-control:private"); //开启网页表单缓存,一定要放在Session_start 函数后

在此测试,问题解决。
via: https://developer.mozilla.org/zh-CN/docs/Web/HTTP/Caching_FAQ

MySQL报错“Illegal mix of collations for operation 'like'”错误代码: 1271

在使用LIKE对日期或者时间字段进行模糊查询的时候,MySQL5.7X会报错误:

错误代码: 1271
Illegal mix of collations for operation 'like'

解决方法:在 MySQL5.5以上版本, 必需改成like binary '%中文%' ;
若字段 Type 是 time,date,datetime 在 select 時若使用 like '%中文%' 会出现 Illegal mix of collations for operation 'like'在写程序时要对每个字段进行搜索,在执行时可能就会出现时间字段 like '%中文%' 这种语法,
低版本MySQL是不会出现错误的。

SQL Server2008R2日志文件过大 大日志文件清理方法 不分离数据库

SQL Server日志文件过大    大日志文件清理方法 ,网上提供了很多分离数据库——〉删除日志文件-〉附加数据库 的方法,此方法风险太大,过程也比较久,有时候也会出现分离不成功的现象。下面的方式是不需要做数据库分离和附加操作的。

SQL 2008收缩清空日志方法:

1.在SQL2008中清除日志就必须在简单模式下进行,等清除动作完毕再调回到完整模式,一定必务要再改回完整模式,不然数据库就不支持时间点备份了。
1).选择数据库–属性—选项—恢复模式–选择简单。
2).收缩数据库后,再调回完整。
2.可以用命令直接操作

USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY SIMPLE      --简单模式
GO
USE 要清理的数据库名称
GO
DBCC SHRINKFILE (N'要清理的数据库名称_log' , 2, TRUNCATEONLY)  --设置压缩后的日志大小为2M,可以自行指定
GO
USE [master]
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE 要清理的数据库名称 SET RECOVERY FULL   --还原为完全模式
GO

使用sysbench对MySQL进行测试

sysbench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的系统、数据库负载情况,项目地址:https://github.com/akopytov/sysbench
sysbench主要支持以下几种测试模式:
CPU运算性能
磁盘IO性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)
目前sysbench主要支持 Mysql,Drizzle,PgSQL,Oracle等几种数据库。
快速安装:

yum -y install make automake libtool pkgconfig libaio-devel        
yum -y install mariadb-devel openssl-devel    # For MySQL support, replace with mysql-devel on RHEL/CentOS 5
yum -y install postgresql-devel        #For PostgreSQL support
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
yum -y install sysbench

查看版本

[root@hongsin-monitor]# sysbench --version
sysbench 1.0.15

测试脚本路径

[root@hongsin-monitor sysbench]# pwd
/usr/share/sysbench
[root@hongsin-monitor sysbench]# ll
total 64
-rwxr-xr-x 1 root root  1452 Jul  4 04:06 bulk_insert.lua
-rw-r--r-- 1 root root 14369 Jul  4 04:06 oltp_common.lua
-rwxr-xr-x 1 root root  1290 Jul  4 04:06 oltp_delete.lua
-rwxr-xr-x 1 root root  2415 Jul  4 04:06 oltp_insert.lua
-rwxr-xr-x 1 root root  1265 Jul  4 04:06 oltp_point_select.lua
-rwxr-xr-x 1 root root  1649 Jul  4 04:06 oltp_read_only.lua
-rwxr-xr-x 1 root root  1824 Jul  4 04:06 oltp_read_write.lua
-rwxr-xr-x 1 root root  1118 Jul  4 04:06 oltp_update_index.lua
-rwxr-xr-x 1 root root  1127 Jul  4 04:06 oltp_update_non_index.lua
-rwxr-xr-x 1 root root  1440 Jul  4 04:06 oltp_write_only.lua
-rwxr-xr-x 1 root root  1919 Jul  4 04:06 select_random_points.lua
-rwxr-xr-x 1 root root  2118 Jul  4 04:06 select_random_ranges.lua
drwxr-xr-x 4 root root  4096 Oct 20 16:48 tests

写性能测试,数据准备

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=xxxxxx --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Initializing worker threads...

Creating table 'sbtest9'...Creating table 'sbtest8'...
Creating table 'sbtest3'...
Creating table 'sbtest4'...Creating table 'sbtest6'...


Creating table 'sbtest1'...
Creating table 'sbtest7'...
Creating table 'sbtest5'...
Creating table 'sbtest2'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest4'
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Inserting 100000 records into 'sbtest5'
Inserting 100000 records into 'sbtest8'
Inserting 100000 records into 'sbtest6'
Inserting 100000 records into 'sbtest9'
Inserting 100000 records into 'sbtest7'
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest8'...
Creating a secondary index on 'sbtest9'...
Creating a secondary index on 'sbtest6'...
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
参数的解释:
--threads=4500  表示发起4500个并发连接
--oltp-read-only=off 表示不要进行只读测试,也就是会采用读写混合模式测试
--report-interval=10 表示每10秒输出一次测试进度报告
--rand-type=uniform 表示随机类型为固定模式,其他几个可选随机模式:uniform(固定),gaussian(高斯),special(特定的),pareto(帕累托)
--time=120 表示最大执行时长为 120秒
--max-requests=0 表示总请求数为 0,因为上面已经定义了总执行时长,所以总请求数可以设定为 0;也可以只设定总请求数,不设定最大执行时长
--tables=10  表示10个表
--table-size=100000  单表100000条记录
--percentile=99 表示设定采样比例,默认是 95%,即丢弃1%的长请求,在剩余的99%里取最大值

运行测试

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:
Number of threads: 4500
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 4500 tps: 618.00 qps: 4951.50 (r/w/o: 0.00/1950.97/3000.53) lat (ms,95%): 8038.61 err/s: 91.70 reconn/s: 0.00
[ 20s ] thds: 4500 tps: 562.83 qps: 3806.01 (r/w/o: 0.00/1849.30/1956.71) lat (ms,95%): 15934.78 err/s: 227.01 reconn/s: 0.00
[ 30s ] thds: 4500 tps: 434.80 qps: 3051.53 (r/w/o: 0.00/1599.01/1452.51) lat (ms,95%): 22842.77 err/s: 226.20 reconn/s: 0.00
[ 40s ] thds: 4500 tps: 479.70 qps: 3310.41 (r/w/o: 0.00/1803.90/1506.50) lat (ms,95%): 28352.44 err/s: 224.60 reconn/s: 0.00
[ 50s ] thds: 4500 tps: 495.10 qps: 3403.11 (r/w/o: 0.00/1910.30/1492.80) lat (ms,95%): 28867.59 err/s: 219.60 reconn/s: 0.00
[ 60s ] thds: 4500 tps: 453.20 qps: 3140.89 (r/w/o: 0.00/1769.59/1371.29) lat (ms,95%): 28867.59 err/s: 215.80 reconn/s: 0.00
[ 70s ] thds: 4500 tps: 469.90 qps: 3246.01 (r/w/o: 0.00/1848.80/1397.20) lat (ms,95%): 28867.59 err/s: 228.90 reconn/s: 0.00
[ 80s ] thds: 4500 tps: 466.59 qps: 3238.67 (r/w/o: 0.00/1859.68/1378.99) lat (ms,95%): 29926.15 err/s: 225.20 reconn/s: 0.00
[ 90s ] thds: 4500 tps: 441.30 qps: 3084.43 (r/w/o: 0.00/1765.82/1318.61) lat (ms,95%): 29926.15 err/s: 225.30 reconn/s: 0.00
[ 100s ] thds: 4500 tps: 434.20 qps: 3026.20 (r/w/o: 0.00/1737.20/1289.00) lat (ms,95%): 29926.15 err/s: 217.40 reconn/s: 0.00
[ 110s ] thds: 4500 tps: 471.70 qps: 3247.39 (r/w/o: 0.00/1872.59/1374.80) lat (ms,95%): 32161.14 err/s: 218.40 reconn/s: 0.00
[ 120s ] thds: 4500 tps: 476.39 qps: 3289.44 (r/w/o: 0.00/1892.36/1397.07) lat (ms,95%): 31023.52 err/s: 232.80 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0         读操作
        write:                           233015     --写总数
        other:                           197026  --其他操作总数(SELECT、INSERT、UPDATE、DELETE之外的操作,例如COMMIT等)
        total:                           430041  --全部总数
    transactions:                        62617  (486.84 per sec.)  --总事务数(每秒事务数)
    queries:                             430041 (3343.55 per sec.) --查询数(每秒查询数)
    ignored errors:                      27087  (210.60 per sec.)  --忽略错误数
    reconnects:                          0      (0.00 per sec.)  --重新连接次数

General statistics:
    total time:                          128.6147s  --运行总时间
    total number of events:              62617  --事件总数

Latency (ms):
         min:                                    2.09
         avg:                                 8957.64
         max:                                99069.80
         95th percentile:                    27846.48
         sum:                            560900840.54

Threads fairness:    #线程平均数
    events (avg/stddev):           13.9149/3.92
    execution time (avg/stddev):   124.6446/2.39

清理数据:

sysbench --test=/usr/share/sysbench/oltp_write_only.lua --mysql-host=192.168.255.90 --mysql-port=3306 --mysql-user=ice --mysql-password=hongsinCS --mysql-db=a1 --report-interval=10 --max-requests=0 --time=120 --threads=4500 --tables=10 --table-size=100000 cleanup
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.15 (using bundled LuaJIT 2.1.0-beta2)

Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
Dropping table 'sbtest6'...
Dropping table 'sbtest7'...
Dropping table 'sbtest8'...
Dropping table 'sbtest9'...
Dropping table 'sbtest10'...

可以根据/usr/share/sysbench下面的lua脚本进行读、写、更新,随机等等测试,条件允许建议时间1小时以上,以便测试数据准确·

mysqlslap: [ERROR] unknown variable 'default-character-set=utf8mb4'

运行mysqlslap报错如下:

[root@monitor-db ~]# mysqlslap --version
mysqlslap: Error when connecting to server: Access denied for user 'root'@'localhost' (using password: NO)

解决方法:带上--no-defaults参数

[root@sso-db-a binlog]# mysqlslap --no-defaults --version
mysqlslap  Ver 1.0 Distrib 5.7.23, for Linux (x86_64)

或者在my.cnf里面将default-character-set修改为utf8,需重启生效

[client]
#设置MySQL客户端的字符集
default-character-set=utf8

mysqlslap.png

FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"

使用sysbench做MySQL性能压测的时候,报错:

FATAL: mysql_stmt_prepare() failed
(last message repeated 2 times)
FATAL: MySQL error: 1461 "Can't create more than max_prepared_stmt_count statements (current value: 16382)"
(last message repeated 1 times)

max_prepared_stmt_count参数限制了同一时间在mysqld上所有session中prepared 语句的上限。
它的取值范围为“0 - 1048576”,默认为16382。
mysql对于超出max_prepared_stmt_count的prepare语句就会报1461的错误。
sysvar_max_prepared_stmt_count.png
官方文档:https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html
一般而言,默认值应该是足够用的,因为现场的并发其实没有那么的大。
一个可能的原因是应用端那边没有关闭prepared的语句。
直连后端master执行如下命令
mysql> show global status like ‘com_stmt%’;
查看如下3个参数值:
Com_stmt_close prepare语句关闭的次数
Com_stmt_execute prepare语句执行的次数
Com_stmt_prepare prepare语句创建的次数

请确认Com_stmt_close的值是否接近于Com_stmt_prepare。

mysql> show global status like 'com_stmt%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Com_stmt_execute        | 79358467 |
| Com_stmt_close          | 18100    |
| Com_stmt_fetch          | 0        |
| Com_stmt_prepare        | 67646    |
| Com_stmt_reset          | 0        |
| Com_stmt_send_long_data | 0        |
| Com_stmt_reprepare      | 0        |
+-------------------------+----------+
7 rows in set (0.00 sec)
mysql> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.01 sec)
mysql> set global max_prepared_stmt_count=1048576;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count |1048576|
+-------------------------+-------+
1 row in set (0.00 sec)

修改之后再次使用100并发压测则没有报错了。
1 row in set (0.01 sec)
为保证mysql实例重启生效可以写到配置文件my.cnf

[mysqld]
max_prepared_stmt_count=1048576

PHP过滤输入表单XSS\HTML特殊字符等预防WEB攻击

hacker.png
Web应用系统的攻击大部分是来自于外部,如Url上添加一些字段注入($_GET输入),表单的提交注入(一般为$_POST),所以在接收数据时对数据进行过滤,是必须的:
过滤方法有以下几种:
trim过滤字符串首尾空格

$test1 = trim($_POST['test1']);

strip_tags函数过滤PHP、HTML标签 :
strip_tags会将字符串中的php标签(<?php ?>)Html标签(<h1></h1><script></script>....等)移除。一定程序上阻止了恶意注入。

$_POST['name'] = "<script>alert('hehe');</script>";
var_dump($_POST['name']);//弹出信息框 'hehe'
$name = strip_tags($_POST['name']);
var_dump($name);  //string(14) "alert('hehe');"

转数据类型
接收的数据是整形或浮点形,可以直接转数据类型。

//转整形 
$number = intval($_POST['number']);
$price  = floatval($_POST['price']);

移除攻跨站脚本xss攻击
xss攻击有时会把标签转换成其他数据,strip_tags防止不了,
ThinkPHP中有个remove_xss方法,可以将大部分xss攻击阻止。./ThinkPHP/Extend/Function/extend.php中,为了方便使用,可以放到项目的common.php里或公用文件里面直接调用:

/**
 * @from extend.php
 * 过滤xss攻击
 * @param str $val
 * @return mixed
 */
function remove_xss($val) {
    // remove all non-printable characters. CR(0a) and LF(0b) and TAB(9) are allowed
    // this prevents some character re-spacing such as <java\0script>
    // note that you have to handle splits with \n, \r, and \t later since they *are* allowed in some inputs
    $val = preg_replace('/([\x00-\x08,\x0b-\x0c,\x0e-\x19])/', '', $val);

    // straight replacements, the user should never need these since they're normal characters
    // this prevents like <IMG SRC=@avascript:alert('XSS')>
    $search = 'abcdefghijklmnopqrstuvwxyz';
    $search .= 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $search .= '1234567890!@#$%^&*()';
    $search .= '~`";:?+/={}[]-_|\'\\';
    for ($i = 0; $i < strlen($search); $i++) {
        // ;? matches the ;, which is optional
        // 0{0,7} matches any padded zeros, which are optional and go up to 8 chars

        // @ @ search for the hex values
        $val = preg_replace('/(&#[xX]0{0,8}'.dechex(ord($search[$i])).';?)/i', $search[$i], $val); // with a ;
        // @ @ 0{0,7} matches '0' zero to seven times
        $val = preg_replace('/(&#0{0,8}'.ord($search[$i]).';?)/', $search[$i], $val); // with a ;
    }

    // now the only remaining whitespace attacks are \t, \n, and \r
    $ra1 = array('javascript', 'vbscript', 'expression', 'applet', 'meta', 'xml', 'blink', 'link', 'style', 'script',
                  'embed', 'object', 'iframe', 'frame', 'frameset', 'ilayer', 'layer', 'bgsound', 'title', 'base');
    $ra2 = array('onabort', 'onactivate', 'onafterprint', 'onafterupdate', 'onbeforeactivate', 'onbeforecopy', 'onbeforecut',
             'onbeforedeactivate', 'onbeforeeditfocus', 'onbeforepaste', 'onbeforeprint', 'onbeforeunload', 'onbeforeupdate', 
             'onblur', 'onbounce', 'oncellchange', 'onchange', 'onclick', 'oncontextmenu', 'oncontrolselect', 'oncopy', 'oncut',
             'ondataavailable', 'ondatasetchanged', 'ondatasetcomplete', 'ondblclick', 'ondeactivate', 'ondrag', 'ondragend',
             'ondragenter', 'ondragleave', 'ondragover', 'ondragstart', 'ondrop', 'onerror', 'onerrorupdate', 'onfilterchange',
             'onfinish', 'onfocus', 'onfocusin', 'onfocusout', 'onhelp', 'onkeydown', 'onkeypress', 'onkeyup', 'onlayoutcomplete',
             'onload', 'onlosecapture', 'onmousedown', 'onmouseenter', 'onmouseleave', 'onmousemove', 'onmouseout', 'onmouseover',
             'onmouseup', 'onmousewheel', 'onmove', 'onmoveend', 'onmovestart', 'onpaste', 'onpropertychange','onreadystatechange',
             'onreset', 'onresize', 'onresizeend', 'onresizestart', 'onrowenter', 'onrowexit', 'onrowsdelete', 'onrowsinserted', 
             'onscroll', 'onselect', 'onselectionchange', 'onselectstart', 'onstart', 'onstop', 'onsubmit', 'onunload');
    $ra = array_merge($ra1, $ra2);

    $found = true; // keep replacing as long as the previous round replaced something
    while ($found == true) {
        $val_before = $val;
        for ($i = 0; $i < sizeof($ra); $i++) {
            $pattern = '/';
            for ($j = 0; $j < strlen($ra[$i]); $j++) {
                if ($j > 0) {
                    $pattern .= '(';
                    $pattern .= '(&#[xX]0{0,8}([9ab]);)';
                    $pattern .= '|';
                    $pattern .= '|(&#0{0,8}([9|10|13]);)';
                    $pattern .= ')*';
                }
                $pattern .= $ra[$i][$j];
            }
            $pattern .= '/i';
            $replacement = substr($ra[$i], 0, 2).'<x>'.substr($ra[$i], 2); // add in <> to nerf the tag
            $val = preg_replace($pattern, $replacement, $val); // filter out the hex tags
            if ($val_before == $val) {
                // no replacements were made, so exit the loop
                $found = false;
            }
        }
    }
    return $val;
}

调用方法:

$name = remove_xss($_POST['name']);
也可以和strip_tags结合起来使用
$name = strip_tags(remove_xss($_POST['name']));

保存文章内容类转义:
使用kindeditor之类的内容编辑器时,因为提交到后台时是以Html形式提交的,而且需要保存到数据库,为了防止sql注入,需要在进数据库前进行特殊字符转义,这时用过滤标签的方法或各类的方法都不适合。只能对标签和特殊符号进行转义,这时使用到的方法是addslashes。
addslashes在使用前先检查一下,php是否自动开启了自动转义。用get_magic_quotes_gpc()方法判断,如果已开,则是true,否为false。

    if(!get_magic_quotes_gpc()){
     $content = addslashes($_POST['content']);
    }else{
      $content= $_POST['content'];
    }

这样就完成了转义,然而在展示页面,从数据库拿出来的内容是经过转义的html,如果直接展示,html标签等都识别不到,会直接输出转义过的字符串。这时需要用反转义来还原数据。如下

echo stripslashes($content);

PHP内置过滤器参考使用 PHP内置过滤器filter_input
其他参考:
http://www.w3school.com.cn/php/func_filter_input_array.asp
https://my.oschina.net/jiec/blog/309467

JS弹出确认删除的提示信息

在后台管理系统中,在一些重要操作如删除按钮或连接被触发时,应给予弹窗提示,常用的代码有以下几种:
用户点击删除按钮时,弹出一个确定框,如果用户点击“确定”执行删除操作,否则不执行
1、通过链接来删除数据出现提示

<a href="del.php?id=1" onclick="return confirm('您确定删除该记录吗?')">删除</a>

2、通过提交表单来删除,也就是批量删除多条数据

<script type="text/javascript"> 
function DelFromLst(){  
        if(confirm('确认删除已选择数据吗?')){
          document.selform.action.value='batdel';
          document.selform.submit();
        }
}
</script>

这里的按钮是 button类型的,不是submit。

<input type="button" name="delbtn" value="批量删除" /> 

3、直接使用js方法

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
<html xmlns="http://www.w3.org/1999/xhtml"> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
<title>确认是否删除</title> 
<script type="text/javascript"> 
function del(){ 
if(!confirm("确认要删除?")){ 
window.event.returnValue = false; 
} 
} 
</script> 
</head> 
<body> 
<a href="http://www.baidu.com" onclick="return del()">删除</a> 
</body> 
</html>

OneDrive 一直显示"正在处理更改"的解决方法

OneDrive 一直显示"正在处理更改",能打开OneDrive网站查看,确认网络正常没被墙,解决方法如下:

1. 按下 Windows 按键 + R ,

2. 在弹出的运行/ Run视窗输入:%localappdata%\Microsoft\OneDrive\onedrive.exe /reset

3. 然后请点击OK (桌面右下角的OneDrive系统小图标将消失并在大约1-2分钟后再次出现)

4. 若桌面右下角的OneDrive系统小图标在数分钟后并没有再次出现,请再运行/ Run视窗并输入:%localappdata%\Microsoft\OneDrive\onedrive.exe

5.操作完成之后,再查看。

via:https://answers.microsoft.com/zh-hans/windows/forum/windows_10-files/onedrive/83687705-a43e-4b79-8db2-bed56abfbadf

最新

分类

归档

评论

  • 安安: 都是af
  • Liang: 嗯,有点不通顺·
  • 王庭威: “MySQL互为主从...
  • Liang: 贴下编译参数和步骤,...
  • shao3911: 您好,为什么我在编译...
  • aliang: 先看是yum安装还是...
  • aliang: 将原来的nginx安...
  • yen: 3、如果要回滚的话,...
  • yen: 刚好需要升级ngin...
  • 文雨: 一些新的method...

其它