迷你 VPS 部署 LEMP 及优化

前言

　　现在市面上有很多迷你型的 VPS 产品，一般都是单核 512M 内存的。许多人觉得这样的小鸡就是拿来玩玩，做不了正事。其实不然，这些迷你 VPS 部署 LEMP 并经过调教优化后，在一些普通场景是可以把负载发挥到极致的。前段时间看到位仁兄用 DigitalOcean 最便宜的套餐（单核/512M）跑起三个 WordPress，抗下了差不多等同于日均 UV 一万、PV 四千万的负载……

　　所以说，小鸡装上 LEMP 只要优化得好，还是大有可为的！

目标环境

CentOS 6.10
Nginx
MySQL 5.7
PHP 7.3
两个 Web 站点：static.benhon.net（静态） dynamic.benhon.net

准备工作

系统优化

详细优化参考 CentOS 6.x 优化
墙内服务器推荐安装 CLI 代理工具 ProxyChains

创建用户

Debian:
apt install -y bsdmainutils
getent passwd | column -t -s ":"
getent passwd | grep -v /nologin | column -t -s ":"

groupadd -r www-data; useradd -r -g www-data -M -s /sbin/nologin www-data

创建目录

mkdir -p /etc/nginx/conf.d
mkdir -p /etc/nginx/others
mkdir -p /etc/nginx/ssl
mkdir -p /var/www/static.benhon.net
mkdir -p /var/www/dynamic.benhon.net

创建证书

yum upgrade -y curl
yum install -y ca-certificates

安装 acme.sh

yum install -y socat
curl https://get.acme.sh | sh
source ~/.bashrc

acme.sh --set-default-ca --server letsencrypt
acme.sh --set-default-ca --server letsencrypt_test
acme.sh --set-default-ca --server buypass
acme.sh --set-default-ca --server buypass_test
acme.sh --set-default-ca --server zerossl
acme.sh --set-default-ca --server sslcom
acme.sh --set-default-ca --server google
acme.sh --set-default-ca --server googletest

ACME server list

acme.sh --register-account -m admin@benhon.net
acme.sh --set-default-ca --server zerossl

生成证书

acme.sh --issue -d static.benhon.net --standalone -k ec-256

安装证书

acme.sh --installcert -d static.benhon.net --fullchainpath /etc/nginx/ssl/static.benhon.net.crt --keypath /etc/nginx/ssl/static.benhon.net.key --ecc

添加计划任务，每个奇数月的第一天5点30分（间隔两个月）自动更新证书：

crontab -e

25 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --upgrade > /dev/null 2>&1
30 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --renew -d xxx.benhon.net --force --ecc > /dev/null 2>&1
35 5 1 1,3,5,7,9,11 * /usr/sbin/nginx -s reload > /dev/null 2>&1

使用 DNS 验证的方式生成泛域名证书

# Cloudflare

export CF_Key="*************"
export CF_Email="***@***.com"

acme.sh --issue --dns dns_cf -d benhon.net -d *.benhon.net -k ec-256
acme.sh --installcert -d benhon.net --fullchainpath /etc/nginx/ssl/benhon.net.crt --keypath /etc/nginx/ssl/benhon.net.key --ecc

crontab -e

30 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --upgrade > /dev/null 2>&1
30 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --renew --dns dns_cf -d benhon.net -d *.benhon.net --force --ecc > /dev/null 2>&1

# Aliyun

export Ali_Key="************"
export Ali_Secret="*********"

acme.sh --issue --dns dns_ali -d benhon.net -d *.benhon.net -k ec-256
acme.sh --installcert -d benhon.net --fullchainpath /etc/nginx/ssl/benhon.net.crt --keypath /etc/nginx/ssl/benhon.net.key --ecc

crontab -e
30 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --upgrade > /dev/null 2>&1
30 5 1 1,3,5,7,9,11 * /root/.acme.sh/acme.sh --renew --dns dns_ali -d benhon.net -d *.benhon.net --force --ecc > /dev/null 2>&1

Tips
更新证书 acme.sh --renew -d static.benhon.net --force --ecc
查看证书有效期 openssl x509 -in /etc/nginx/ssl/*.crt -noout -dates
更新 acme acme.sh --upgrade
查看证书列表 acme.sh --list
移除证书 acme.sh --remove -d benhon.net
卸载 acme.sh --uninstall

Nginx

安装

vi /etc/yum.repos.d/nginx.repo

写入如下内容：

[nginx]
name=nginx repo
baseurl=https://nginx.org/packages/centos/$releasever/$basearch/
gpgcheck=0
enabled=1

yum install -y nginx

Debian:

apt update
apt install -y curl
curl -o /tmp/nginx_signing.key https://nginx.org/keys/nginx_signing.key
apt-key add /tmp/nginx_signing.key

echo "deb https://nginx.org/packages/debian $(lsb_release -cs) nginx" | tee -a /etc/apt/sources.list
echo "deb-src https://nginx.org/packages/debian $(lsb_release -cs) nginx" | tee -a /etc/apt/sources.list

apt update
apt install -y nginx

配置

编辑 Nginx 配置

rm -f /etc/nginx/conf.d/*
openssl dhparam -out /etc/nginx/dhparam.pem 2048

nano /etc/nginx/nginx.conf

user                 www-data;
pid                  /run/nginx.pid;
worker_processes     1;
worker_rlimit_nofile 10240;

# Load Brotli Compression Module
load_module modules/ngx_http_brotli_filter_module.so;
load_module modules/ngx_http_brotli_static_module.so;

events {
    multi_accept       on;
    worker_connections 10240;
}

http {
    charset                       utf-8;
    client_body_timeout           10;
    client_header_timeout         10;
    client_header_buffer_size     32k;
    client_max_body_size          8M;
    keepalive_timeout             65;
    large_client_header_buffers   4 32k;
    log_not_found                 off;
    open_file_cache               max=1000 inactive=20s;
    open_file_cache_valid         30s;
    open_file_cache_min_uses      2;
    open_file_cache_errors        on;
    output_buffers                1 32k;
    postpone_output               1460;
    sendfile                      on;
    send_timeout                  60;
    server_name_in_redirect       off;
    server_names_hash_bucket_size 128;
    server_tokens                 off;
    tcp_nopush                    on;
    tcp_nodelay                   off;
    # tcp_nodelay                 on;
    types_hash_bucket_size        64;
    types_hash_max_size           2048;

    # MIME
    include                mime.types;
    default_type           application/octet-stream;

    # Logging
    access_log             off;
    error_log              /dev/null;

    # SSL
    ssl_session_timeout    1d;
    ssl_session_cache      shared:SSL:10m;
    ssl_session_tickets    off;

    # Diffie-Hellman parameter for DHE ciphersuites
    ssl_dhparam            /etc/nginx/dhparam.pem;

    # Mozilla Intermediate configuration
    ssl_protocols          TLSv1.2 TLSv1.3;
    ssl_ciphers            TLS13_AES_128_GCM_SHA256:TLS13_AES_256_GCM_SHA384:TLS13_CHACHA20_POLY1305_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305;
    ssl_prefer_server_ciphers on;

    # OCSP Stapling
    ssl_stapling           on;
    ssl_stapling_verify    on;
    resolver               1.1.1.1 [2606:4700:4700::1111] 8.8.8.8 [2001:4860:4860::8888] valid=60s;
    resolver_timeout       2s;

    # Load configs
    include                /etc/nginx/conf.d/*.conf;
    include                others/ip_cloudflare.conf;
    include                others/ip_blacklist.conf;
}

TCP_NODELAY 是一种 TCP 协议的选项，用于控制数据包是否应该被立即发送，还是应该等待一段时间以便将多个数据包组合成一个较大的数据块后再发送。
当 tcp_nodelay 设置为 on 时：表示启用 TCP_NODELAY 选项，数据会立即发送，不会进行等待。这对于某些实时性要求较高的应用（如实时游戏、实时通信等）可能是有益的，因为它可以降低延迟。
当 tcp_nodelay 设置为 off 时：表示禁用 TCP_NODELAY 选项，数据包会等待一段时间后再发送，此时数据会被缓冲，有可能会合并多个数据包一起发送。这对于某些应用如文件传输等，可能会提高传输效率。
在一些情况下，tcp_nodelay 设置为 on 可以提高实时性，但同时会增加网络传输的开销。而在某些应用场景下，如文件传输或者大数据块的传输，将 tcp_nodelay 设置为 off 可以提高数据传输的效率。
需要根据你的应用场景来决定是否启用或禁用 TCP_NODELAY。

编辑站点 static.benhon.net 配置
nano /etc/nginx/conf.d/static.benhon.net.conf

server {
    listen              443 ssl http2;
    listen              [::]:443 ssl http2;
    server_name         static.benhon.net;
    set                 $base /var/www/static.benhon.net/;
    root                $base;

    # SSL
    ssl_certificate     /etc/nginx/ssl/static.benhon.net.crt;
    ssl_certificate_key /etc/nginx/ssl/static.benhon.net.key;

    # auth_basic "Authorized";
    # auth_basic_user_file /etc/nginx/htpasswd.conf;

    # index.html fallback
    location / {
        try_files $uri $uri/ /index.html;
    }

    # handle .php for sub-folders
    location ^~ /(folders1|folders2)/ {
        alias $base$1/;
    #   alias /path/to/folders/located/$1/files;
        try_files $uri $uri/ /index.php?$query_string;

        location ~ \.php$ {
            fastcgi_pass unix:/dev/shm/php7.4-fpm.sock;
            include others/php_fastcgi.conf;
        }
    }

    # security
    include             others/security.conf;

    # additional config
    include             others/general.conf;
}

# HTTP redirect
server {
    listen      80;
    listen      [::]:80;
    server_name static.benhon.net;
    return      301 https://$host$request_uri;
}

编辑站点 dynamic.benhon.net 配置
nano /etc/nginx/conf.d/dynamic.benhon.net.conf

server {
    listen              443 ssl http2;
    listen              [::]:443 ssl http2;
    server_name         dynamic.benhon.net;
    set                 $base /var/www/dynamic.benhon.net/;
    root                $base;

    # SSL
    ssl_certificate     /etc/nginx/ssl/dynamic.benhon.net.crt;
    ssl_certificate_key /etc/nginx/ssl/dynamic.benhon.net.key;

    # index.php
    index               index.php index.html;

    # index.php fallback
    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    # handle .php
    location ~ \.php$ {
        fastcgi_pass    unix:/dev/shm/php7.4-fpm.sock;
        include         others/php_fastcgi.conf;
    }

    # reverse proxy for NPS
    location /nps {
        proxy_pass http://127.0.0.1:8080;
        include others/proxy.conf;
    }

    # reverse proxy
    location /path {
        if ($http_upgrade != "websocket") {
            return 404;
        }
        proxy_pass      http://unix:/dev/shm/ws.sock;
        include         others/proxy.conf;
    }

    # security
    include             others/security.conf;

    # additional config
    include             others/general.conf;
    include             others/wordpress.conf;
}

# HTTP redirect
server {
    listen              80;
    listen              [::]:80;
    server_name         dynamic.benhon.net;
    return 301 https://$host$request_uri;
}

添加通用配置
nano /etc/nginx/others/general.conf

# favicon.ico
location = /favicon.ico {
    log_not_found off;
}

# robots.txt
location = /robots.txt {
    log_not_found off;
}

# assets, media
location ~* \.(?:css(\.map)?|js(\.map)?|jpe?g|png|gif|ico|cur|heic|webp|tiff?|mp3|m4a|aac|ogg|midi?|wav|mp4|mov|webm|mpe?g|avi|ogv|flv|wmv)$ {
    expires 7d;
}

# svg, fonts
location ~* \.(?:svgz?|ttf|ttc|otf|eot|woff2?)$ {
    add_header Access-Control-Allow-Origin "*";
    expires    7d;
}

# gzip
gzip              on;
gzip_vary         on;
gzip_proxied      any;
gzip_comp_level   5;
gzip_types        text/plain text/css text/xml application/json application/javascript application/rss+xml application/atom+xml image/svg+xml;

# brotli
brotli            on;
brotli_comp_level 5;
brotli_types      text/plain text/css text/xml application/json application/javascript application/rss+xml application/atom+xml image/svg+xml;

添加安全配置
nano /etc/nginx/others/security.conf

# security headers
add_header X-XSS-Protection          "1; mode=block" always;
add_header X-Content-Type-Options    "nosniff" always;
add_header Referrer-Policy           "no-referrer-when-downgrade" always;
add_header Content-Security-Policy   "default-src 'self' http: https: ws: wss: data: blob: 'unsafe-inline'; frame-ancestors 'self';" always;

# for WordPress Admin Dashboard
# add_header Content-Security-Policy "default-src 'self' http: https: ws: wss: data: blob: 'unsafe-inline'; frame-ancestors 'self'; script-src 'self' 'unsafe-inline' 'unsafe-eval';" always;

add_header Permissions-Policy        "interest-cohort=()" always;
add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;

# . files
location ~ /\.(?!well-known) {
    deny all;
}

添加 FastCGI 配置
nano /etc/nginx/others/php_fastcgi.conf

# 404
try_files                     $fastcgi_script_name =404;

# default fastcgi_params
include                       fastcgi_params;

# fastcgi settings
fastcgi_index                 index.php;
# fastcgi_buffers             4 32k; (previous)
fastcgi_buffers               8 16k;
fastcgi_buffer_size           32k;
# fastcgi_connect_timeout     300; (previous)
# fastcgi_read_timeout        300; (previous)
# fastcgi_send_timeout        300; (previous)

# fastcgi params
fastcgi_param DOCUMENT_ROOT   $realpath_root;
fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;
fastcgi_param PHP_ADMIN_VALUE "open_basedir=$base/:/usr/lib/php/:/tmp/";

添加转发配置（如果需要的话）
nano /etc/nginx/others/proxy.conf

proxy_http_version                 1.1;
proxy_cache_bypass                 $http_upgrade;
proxy_redirect                     off;

# Proxy SSL
# proxy_ssl_server_name            on;

# Proxy headers
proxy_set_header Host              $host;
proxy_set_header Upgrade           $http_upgrade;
proxy_set_header Connection        "upgrade";
proxy_set_header X-Real-IP         $remote_addr;
# proxy_set_header Forwarded       $proxy_add_forwarded; (new)
proxy_set_header X-Forwarded-For   $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-Host  $host;
proxy_set_header X-Forwarded-Port  $server_port;

# Proxy timeouts
proxy_connect_timeout              60s;
proxy_send_timeout                 60s;
# proxy_read_timeout               60s;
proxy_read_timeout                 600s;

添加 WordPress 安全配置
nano /etc/nginx/others/wordpress.conf

# WordPress: allow TinyMCE
location = /wp-includes/js/tinymce/wp-tinymce.php {
    include others/php_fastcgi.conf;
}

# WordPress: deny wp-content, wp-includes php files
location ~* ^/(?:wp-content|wp-includes)/.*\.php$ {
    deny all;
}

# WordPress: deny wp-content/uploads nasty stuff
location ~* ^/wp-content/uploads/.*\.(?:s?html?|php|js|swf)$ {
    deny all;
}

# WordPress: SEO plugin
location ~* ^/wp-content/plugins/wordpress-seo(?:-premium)?/css/main-sitemap\.xsl$ {}

# WordPress: deny wp-content/plugins (except earlier rules)
location ~ ^/wp-content/plugins {
    deny all;
}

# WordPress: deny general stuff
location ~* ^/(?:xmlrpc\.php|wp-links-opml\.php|wp-config\.php|wp-config-sample\.php|readme\.html|license\.txt)$ {
    deny all;
}

添加 Cloudflare CDN 配置（如果需要的话）
nano /etc/nginx/others/ip_cloudflare.conf

# cloudflare ips
set_real_ip_from unix:;
set_real_ip_from 127.0.0.1;
set_real_ip_from ::1;
set_real_ip_from 173.245.48.0/20;
set_real_ip_from 103.21.244.0/22;
set_real_ip_from 103.22.200.0/22;
set_real_ip_from 103.31.4.0/22;
set_real_ip_from 141.101.64.0/18;
set_real_ip_from 108.162.192.0/18;
set_real_ip_from 190.93.240.0/20;
set_real_ip_from 188.114.96.0/20;
set_real_ip_from 197.234.240.0/22;
set_real_ip_from 198.41.128.0/17;
set_real_ip_from 162.158.0.0/15;
set_real_ip_from 104.16.0.0/13;
set_real_ip_from 104.24.0.0/14;
set_real_ip_from 172.64.0.0/13;
set_real_ip_from 131.0.72.0/22;
set_real_ip_from 2400:cb00::/32;
set_real_ip_from 2606:4700::/32;
set_real_ip_from 2803:f800::/32;
set_real_ip_from 2405:b500::/32;
set_real_ip_from 2405:8100::/32;
set_real_ip_from 2a06:98c0::/29;
set_real_ip_from 2c0f:f248::/32;

# use any of the following two
# real_ip_header CF-Connecting-IP;
real_ip_header X-Forwarded-For;
# real_ip_header proxy_protocol;

real_ip_recursive on;

添加屏蔽 IP 配置（如果需要的话）
nano /etc/nginx/others/ip_blacklist.conf

# IP Blacklist
# deny 61.19.68.0/22;

运行

启动 systemctl start nginx
设置开机自启 systemctl enable nginx

PHP

Nginx 与 PHP 部署在同一台主机时，可以使用 Unix domain socket 来传输数据。相比起本地环回网络（local loopback）来说，不会占用网络缓存，速度也略快一些。

准备 Unix domain socket 文件

touch /dev/shm/php7.4-fpm.sock
chown -R www-data /dev/shm/php7.4-fpm.sock

安装

yum install -y http://rpms.remirepo.net/enterprise/remi-release-7.rpm
yum install -y yum-utils
yum-config-manager --enable remi-php74
yum install -y php php-mcrypt php-cli php-gd php-curl php-mysql php-ldap php-zip php-fileinfo php-sqlite php-fpm php-mbstring

Debian:
apt update
apt install -y php7.4 php7.4-cli php7.4-gd php7.4-curl php7.4-mysql php7.4-ldap php7.4-zip php7.4-fileinfo php7.4-sqlite3 php7.4-fpm php7.4-mbstring

dpkg --list | grep apache2
apt remove --purge apache2 apache2-bin apache2-data apache2-utils

Debian12:
curl -o /tmp/php.key https://packages.sury.org/php/apt.gpg
apt-key add /tmp/php.key
echo "deb https://packages.sury.org/php $(lsb_release -sc) main" | tee -a /etc/apt/sources.list.d/php.list

apt update
apt install -y php7.4 php7.4-cli php7.4-gd php7.4-curl php7.4-mysql php7.4-ldap php7.4-zip php7.4-fileinfo php7.4-sqlite3 php7.4-fpm php7.4-mbstring

php -v

Tip yum install -y php-pgsql php-pdo_pgsql for PostgreSQL

配置

php -i | grep "Loaded Configuration File"

Loaded Configuration File => /etc/php.ini

vi /etc/php.ini

Debian:
nano /etc/php/7.4/fpm/php.ini

zlib.output_compression = On
realpath_cache_size = 1M
realpath_cache_ttl = 300
zend.enable_gc = On
memory_limit = 32M
log_errors = Off
date.timezone = Asia/Shanghai

vi /etc/php-fpm.conf

Debian:
nano /etc/php/7.4/fpm/php-fpm.conf

process_control_timeout = 5

vi /etc/php-fpm.d/www.conf

Debian:
nano /etc/php/7.4/fpm/pool.d/www.conf

user = www-data
group = www-data
;listen = 127.0.0.1:9000
listen = /dev/shm/php7.4-fpm.sock
listen.allowed_clients = 172.16.0.88 // Nginx 服务器地址
pm = dynamic
pm.max_children = 6                  // 同时活动的最大 worker 数
pm.start_servers = 1                 // 启动时创建的 worker 数
pm.min_spare_servers = 1             // 允许空闲的最小 worker 数
pm.max_spare_servers = 1             // 允许空闲的最大 worker 数
pm.max_requests = 500                // 在 worker 重启前可以处理最大请求数
request_terminate_timeout = 30
security.limit_extensions = .php .php3 .php4 .php5 .php7

运行

systemctl start php-fpm
systemctl enable php-fpm

Debian:
systemctl restart php7.4-fpm

新建页面并测试

echo "<?php phpinfo(); ?>" >> /var/www/dynamic.benhon.net/info.php
chown -R www-data:www-data /var/www/dynamic.benhon.net
chown -R www-data /var/lib/php/session(s)
chown -R www-data /dev/shm/php7.4-fpm.sock

访问 https://dynamic.benhon.net/info.php 测试

新增服务单元 vi /etc/systemd/system/php-fpm-sock-permissions.service 填入以下内容：

[Unit]
Description=Set permissions for php-fpm sock file
After=php-fpm.service

[Service]
Type=oneshot
ExecStart=/bin/chown -R www-data /dev/shm/php7.4-fpm.sock

[Install]
WantedBy=multi-user.target

最后执行：

systemctl enable php-fpm-sock-permissions
systemctl start php-fpm-sock-permissions
systemctl daemon-reload

echo "touch /dev/shm/php7.4-fpm.sock" >> /etc/rc.d/rc.local

MySQL

卸载系统自带
yum list installed | grep mysql

mysql-libs.x86_64 5.1.73-8.el6_8

yum remove -y mysql-libs.x86_64

安装

使用官方源安装 MySQL 5.7

Debian:

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-server_5.7.42-1debian10_amd64.deb-bundle.tar
mkdir mysql-server_5.7.42-1
tar -xvf mysql-server_5.7.42-1debian10_amd64.deb-bundle.tar -C mysql-server_5.7.42-1/

apt install -y psmisc libaio1 libnuma1 libatomic1 libmecab2

dpkg -i mysql-common_5.7.42-1debian10_amd64.deb
dpkg -i mysql-community-client_5.7.42-1debian10_amd64.deb
dpkg -i mysql-client_5.7.42-1debian10_amd64.deb
dpkg -i mysql-community-server_5.7.42-1debian10_amd64.deb
dpkg -i mysql-server_5.7.42-1debian10_amd64.deb

yum localinstall -y https://repo.mysql.com/mysql57-community-release-el7.rpm
yum repolist enabled | grep mysql

mysql-connectors-community MySQL Connectors Community 129
mysql-tools-community MySQL Tools Community 90
mysql57-community MySQL 5.7 Community Server 396

yum-config-manager --enable mysql57-community
yum install -y mysql-community-server --nogpgcheck
systemctl start mysqld

Initializing MySQL database: [ OK ]
Starting mysqld: [ OK ]

查看临时密码

grep 'temporary password' /var/log/mysqld.log

A temporary password is generated for root@localhost: vsKf!qo,S4oi

安全安装向导

mysql_secure_installation

// 输入临时密码 vsKf!qo,S4oi 以登录
Enter password for user root:
The 'validate_password' plugin is installed on the server. The subsequent steps will run with the existing configuration of the plugin. Using existing password for root.
// 是否为 root 修改新密码
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) :y
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.
// 是否删除匿名用户
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
// 是否禁用远程登陆（前段程序与数据库安装在同一台服务器上选 yes）
Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' thatanyone can access. This is also intended only for testing, and should be removed before moving into a production environment.
// 是否删除测试数据库
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
Dropping test database...
Success.
Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes made so far will take effect immediately.
// 是否重新加载以使设置生效
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!

mysql -V

mysql Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using EditLine wrapper

配置

Debian:
systemctl stop mysql
nano /etc/mysql/mysql.conf.d/mysqld.cnf

systemctl start mysql
systemctl enable mysql

systemctl stop mysqld
vi /etc/my.cnf

[mysqld]
# 监听地址
# bind-address = 0.0.0.0
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

# for Debian
# socket = /var/run/mysqld/mysqld.sock
# log-error = /var/log/mysql/error.log
# pid-file = /var/run/mysqld/mysqld.pid

# 开启查询结果集缓存
query_cache_type = 1
# 查询结果集缓存大小
query_cache_size = 16M
# 单个结果集缓存的最大值
query_cache_limit = 1M

max_allowed_packet = 1M
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 16M
innodb_log_file_size = 4M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50

# 禁用性能监测功能
performance_schema = off

# 禁用来访 IP 反向解析（面向公网提供服务不建议禁用）
#skip-name-resolve

# 禁用符号链接减少安全风险
symbolic-links = 0

# 禁用密码强度插件（面向公网提供服务不建议禁用）
#validate_password = off

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

# 如果你不熟悉 SQL 就去掉注释符
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

MySQL 5.7 默认启用 validate_password 密码强度插件，密码强度要求是至少要包含一个大写字母、一个小写字母、一位数字和一个特殊符号，长度至少为8个字符。

运行

systemctl start mysqld
systemctl enable mysqld

偶尔会有些本地程序无法访问数据库，错误提示是找不到 Socket 通讯文件 /tmp/mysql.sock。可以看到前面配置 my.cnf 文件中 Socket 位于 /var/lib/mysql/mysql.sock，碰到这种状况只需建立一个软链接就可以解决了。
ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock for Debian

登陆 Console mysql -h localhost -uroot -p

常用指令：

// 创建用户
mysql> CREATE USER '<用户名>'@'localhost' IDENTIFIED BY '<密码>';

// 修改用户密码
mysql> ALTER USER '<用户名>'@'localhost' IDENTIFIED BY '<新密码>';

// 创建数据库
mysql> CREATE DATABASE <数据库名>;

// 赋予用户指定数据库权限
mysql> GRANT ALL ON <数据库名>.* TO '<用户名>'@'localhost';

// 更改数据库字符集（示例）
mysql> ALTER DATABASE <数据库名> CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

// 更改表字符集（示例）
mysql> use <数据库名>
mysql> ALTER TABLE <表名> CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

// 重载设置
mysql> FLUSH PRIVILEGES;

进阶（可选）

　　小内存 VPS 还可以使用 MyISAM 引擎，内存开销要比 InnoDB 引擎少得多，当然性能也要差一些。但如果只是跑个博客或者挂个发布站点，没有什么频繁的写入操作，那用户体验基本没区别。如果之前在用 InnoDB，这里有个小脚本可以将 InnoDB 表转换成 MyISAM:（注意数据备份，不能保证 100% 兼容。）

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
        for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
                TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
                if [ $TABLE_TYPE = "InnoDB" ] ; then
                        mysqldump $db $table > $db.$table.sql
                        echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
                fi
        done
done

版权声明：本文为原创文章，版权归 BenhoN 所有。
本文链接：https://blog.benhon.net/archives/deploy_and_opti_of_lemp_stack_manually.html
所有原创文章采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可，你可以自由地转载和修改，但请务必注明文章来源并且不可用于商业目的。