Skip to main content

3 posts tagged with "linux"

View All Tags

· 2 min read
Innocence

Mysql的infromation_schema库,可以查询数据库中每个表占用的空间、表记录行数

  • TABLE_SCHEMA: 数据库名
  • TABLE_NAME: 表名
  • ENGINE: 使用的存储引擎
  • TABLES_ROWS: 记录数
  • DATA_LENGTH: 数据大小
  • INDEX_LENGTH: 索引大小

查看所有库大小

use information_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

查看指定库大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='xxx';

查看指定库的指定表的大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='xxx' and table_name='xxx';

查看指定库的索引大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'xxx';

查看指定库的指定表的索引大小

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'xxx' and table_name='xxx';

查看一个库中的情况

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'xxx';

查看非 Sleep 状态的链接,按消耗时间倒序展示

使用show full processlist可以查看所有链接情况

select id, db, user, host, command, time, state, info
from information_schema.processlist
where command != 'Sleep'
order by time desc;

查询执行时间超过2分钟的线程,然后拼接成kill语句

select concat('kill ', id, ';')
from information_schema.processlist
where command != 'Sleep'
and time > 2*60
order by time desc;

快速杀死所有进程

mysql -e "show full processlist;" -ss | awk '{print "KILL "$1";"}'| mysql

· 6 min read
Innocence

获取客户端真实IP、域名、协议、端口

proxy_set_header Host $http_host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
  • Host 包含客户端真实的域名和端口号;
  • X-Forwarded-Proto 表示客户端真实的协议;
  • X-Real-IP 表示客户端真实的IP;
  • X-Forwarded-ForX-Real-IP 类似,但它在多层代理时会包含真实客户端及中间每个代理服务器的IP;

负载均衡配置

fail_timeout时间内失败了max_fails次请求后,将该服务地址剔除掉,fail_tiemout时间后会再次将该服务器加入存活列表,进行重试

http {
upstream server_name {
server IP:Port weight=1 max_fails=2 fail_timeout=60s;
server IP:Port weight=2 max_fails=2 fail_timeout=60s;
}
server {
listen 80;
location / {
proxy_pass http://server_name;
}
}
}

静态资源缓存

location ~* .(gif|jpg|jpeg|bmp|png|ico|txt|js|css)$ {
expires 3d;
add_header Static Nginx-Proxy;
}

动态黑名单

  • 一般配置
location / {
deny 192.168.1.1;
deny 192.168.1.0/24;
allow 10.0.0.0/16;
allow 2001:0db8::/32;
deny all;
}
  • Lua+Redis动态黑名单(OpenResty)
yum install yum-utils
yum-config-manager --add-repo https://openresty.org/package/centos/openresty.repo
yum install openresty
yum install openresty-resty
yum --disablerepo="*" --enablerepo="openresty" list available
service openresty start

配置(/usr/local/openresty/nginx/conf/nginx.conf)

lua_shared_dict ip_blacklist 1m;

server {
listen 80;

location / {
access_by_lua_file lua/ip_blacklist.lua;
proxy_pass http://server_name;
}
}

ip_blacklist.lua

local redis_host    = "192.168.1.100"
local redis_port = 6379
local redis_pwd = 123456
local redis_db = 1

-- connection timeout for redis in ms.
local redis_connection_timeout = 100

-- a set key for blacklist entries
local redis_key = "ip_blacklist"

-- cache lookups for this many seconds
local cache_ttl = 60

-- end configuration

local ip = ngx.var.remote_addr
local ip_blacklist = ngx.shared.ip_blacklist
local last_update_time = ip_blacklist:get("last_update_time");

-- update ip_blacklist from Redis every cache_ttl seconds:
if last_update_time == nil or last_update_time < ( ngx.now() - cache_ttl ) then

local redis = require "resty.redis";
local red = redis:new();

red:set_timeout(redis_connect_timeout);

local ok, err = red:connect(redis_host, redis_port);
if not ok then
ngx.log(ngx.ERR, "Redis connection error while connect: " .. err);
else
local ok, err = red:auth(redis_pwd)
if not ok then
ngx.log(ngx.ERR, "Redis password error while auth: " .. err);
else
local new_ip_blacklist, err = red:smembers(redis_key);
if err then
ngx.log(ngx.ERR, "Redis read error while retrieving ip_blacklist: " .. err);
else
ngx.log(ngx.ERR, "Get data success:" .. new_ip_blacklist)
-- replace the locally stored ip_blacklist with the updated values:
ip_blacklist:flush_all();
for index, banned_ip in ipairs(new_ip_blacklist) do
ip_blacklist:set(banned_ip, true);
end
-- update time
ip_blacklist:set("last_update_time", ngx.now());
end
end
end
end

if ip_blacklist:get(ip) then
ngx.log(ngx.ERR, "Banned IP detected and refused access: " .. ip);
return ngx.exit(ngx.HTTP_FORBIDDEN);
end

Websocket

map $http_upgrade $connection_upgrade { 
default upgrade;
'' close;
}
upstream ws_backend{
server IP:Port;
keepalive 1000;
}
server {
listen 80;
location /{
proxy_http_version 1.1;
proxy_pass http://ws_backend;
proxy_redirect off;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_read_timeout 3600s;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header Upgrade $http_upgrade;
proxy_set_header Connection $connection_upgrade;
}
}

nginx 的转发规则

location [=|~|~*|^~] /uri/ { ... }
  • = 严格匹配,如果请求匹配这个location,那么将停止搜索立即处理请求
  • ~ 区分大小写匹配(可用正则表达式)
  • ~* 不区分大小写匹配(可用正则表达式)
  • !~ 区分大小写不匹配
  • !~* 不区分大小写不匹配
  • ^~ 如果把这个前缀用于一个常规字符串,那么告诉nginx如果路径匹配那么不测试正则表达式

nginx.conf 配置

user  nginx;
worker_processes auto;
worker_rlimit_nofile 65535;

error_log /data/logs/nginx/error.log notice;
pid /var/run/nginx.pid;
events {
worker_connections 65535;
multi_accept on;
use epoll;
}
http {
include /etc/nginx/mime.types;
default_type application/octet-stream;
log_format main '[$time_local] RemoteAddr:"$remote_addr" RemoteUser:"$remote_user" Host:"$host" '
'RequestUil:"$request" HttpStatus:"$status" BodyBytesSent:"$body_bytes_sent" '
'HttpReferer:"$http_referer" HttpUserAgent:"$http_user_agent" '
'Http_X_ForwardedFor:"$http_x_forwarded_for" UpstreamResponseTime:"$upstream_response_time" '
'UpstreamAddr:"$upstream_addr" RequestTime:"$request_time" --- $server_port';
log_format json '{'
'"RemoteAddr":"$remote_addr",'
'"RemoteUser":"$remote_user",'
'"TimeLocal":"$time_local",'
'"RequestUil":"$request",'
'"HttpHost":"$http_host"'
'"HttpStatus":"$status",'
'"BodyBytesSent":"$body_bytes_sent",'
'"HttpReferer":"$http_referer",'
'"HttpUserAgent":"$http_user_agent",'
'"Http_X_ForwardedFor":"$http_x_forwarded_for",'
'"SslProtocol":"$ssl_protocol"'
'"SslCipher":"$ssl_cipher"'
'"UpstreamResponseTime":"$upstream_response_time",'
'"UpstreamAddr":"$upstream_addr",'
'"RequestTime":"$request_time",'
'}';

access_log /data/logs/nginx/access.log main;

access_log off;
server_tokens off;
sendfile on;
tcp_nopush on;
tcp_nodelay on;
send_timeout 300;
keepalive_timeout 300;
resolver_timeout 60;
server_names_hash_max_size 512;
server_names_hash_bucket_size 128;

client_body_timeout 300;
client_header_timeout 300;
client_header_buffer_size 512k;
client_max_body_size 300m;
large_client_header_buffers 8 32k;
client_body_buffer_size 256k;

fastcgi_connect_timeout 300;
fastcgi_send_timeout 300;
fastcgi_read_timeout 300;
fastcgi_buffer_size 128k;
fastcgi_buffers 8 256k;
fastcgi_busy_buffers_size 256k;
fastcgi_temp_file_write_size 256k;
fastcgi_temp_path /tmp/ngx_fcgi_tmp;
fastcgi_cache_path /tmp/fcgi_cache_path levels=1:2 keys_zone=ngx_fcgi_cache:512m inactive=1d max_size=10g;

gzip on;
gzip_http_version 1.1;
gzip_min_length 1k;
gzip_buffers 4 16k;
gzip_comp_level 9;
gzip_types text/plain application/json application/x-javascript text/css application/xml text/javascript application/x-httpd-php image/jpeg image/gif image/png;
gzip_vary on;
gzip_disable "MSIE [1-6]\.";

proxy_http_version 1.1;
proxy_set_header Connection "";
proxy_set_header Host $host;
proxy_connect_timeout 300;
proxy_read_timeout 300;
proxy_send_timeout 300;
proxy_buffering on;
proxy_buffer_size 128k;
proxy_buffers 8 128k;
proxy_busy_buffers_size 256k;
proxy_temp_file_write_size 256k;
proxy_temp_path /tmp/proxy_temp_path;
proxy_cache_path /tmp/proxy_cache_path levels=1:2 keys_zone=ngx_proxy_cache:512m inactive=1d max_size=10g;
include /etc/nginx/conf.d/*.conf;
}
include /etc/nginx/stream.d/*.conf;

stream 配置

stream {
upstream server_name {
server IP:Port;
}
server {
listen Port;
proxy_pass server_name;
proxy_connect_timeout 1h;
proxy_timeout 1h;
}
}

server 配置

server {
listen 80;
listen 81;
server_name 127.0.0.1 example.com;
index index.php index.html index.htm;
root /data/www;
charset utf-8;
access_log /data/logs/example.com.log main;

location / {
if (!-e $request_filename) {
rewrite ^/(.*) /index.php?$1 last;
}
}
location /xxx/ {
if ($arg_icpid = "4pd1mtsDhfe" ) {
proxy_pass http://127.0.0.1:38888/test$request_uri&tbid=aldIthSBg04;
}
proxy_pass http://127.0.0.1:9302;
}
location /xxx/ {
proxy_pass http://127.0.0.1:38888/;
}
location ~ \.php$ {
fastcgi_param REMOTE_ADDR $http_x_real_ip;
fastcgi_param LY_ADDRESS $remote_addr;
fastcgi_pass unix:/dev/shm/php-cgi.sock;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
fastcgi_param SERVERNAME $hostname;
include fastcgi_params;
}
location = /favicon.ico {
log_not_found off;
access_log off;
}
location = /robots.txt {
allow all;
log_not_found off;
access_log off;
}
}

server {
listen 80;
listen 443 ssl;
server_name example.com;
charset utf-8;
access_log /data/logs/example.com.log main;

ssl_certificate /etc/nginx/cert/example.com.pem;
ssl_certificate_key /etc/nginx/cert/example.com.key;
ssl_session_timeout 5m;
ssl_ciphers ECDHE-RSA-AES128-GCM-SHA256:ECDHE:ECDH:AES:HIGH:!NULL:!aNULL:!MD5:!ADH:!RC4;
ssl_protocols TLSv1 TLSv1.1 TLSv1.2;
ssl_prefer_server_ciphers on;

location / {
proxy_pass http://127.0.0.1:38888;
}
location = /favicon.ico {
log_not_found off;
access_log off;
}
location = /robots.txt {
allow all;
log_not_found off;
access_log off;
}
}

· One min read
Innocence

导入仓库源

rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org

rpm -Uvh http://www.elrepo.org/elrepo-release-7.0-2.el7.elrepo.noarch.rpm

查看可安装的软件包

# ML 版本为最新版本,TL 版本为稳定版本
yum --enablerepo="elrepo-kernel" list --showduplicates | sort -r | grep kernel-ml.x86_64

# 安装 ML 版本
yum --enablerepo=elrepo-kernel install kernel-ml-devel kernel-ml -y

# 安装 LT 版本
yum --enablerepo=elrepo-kernel install kernel-lt-devel kernel-lt -y

查看现有内核启动顺序

awk -F\' '$1=="menuentry " {print $2}' /etc/grub2.cfg

CentOS Linux (4.4.179-1.el7.elrepo.x86_64) 7 (Core)

CentOS Linux (3.10.0-693.el7.x86_64) 7 (Core)

设置内核启动序号

grub2-set-default 0