mysql 数据库

docker-compose

创建必要的目录，权限通过yml配置指定容器用户的uid来解决

mkdir -p /www/mysql/data/mysqllib
vi /www/mysql/data/my.cnf
vi /www/mysql/data/init.sql
touch /www/mysql/data/error.log

version: '3.8'

services:
  mysql:
    image: mysql:8.0
    container_name: mysql # 容器名称
    restart: unless-stopped # 选项：no不重启 always总是重启 on-failure[:n]失败重启，可限n次 unless-stopped 重启除非手动停止
    user: "997:1000"   # 使用宿主机的ghost用户的UID和GID
    # privileged: true # 有映射权限问题，先用它启动一次，再注释掉
    networks:
      - ghost_net # 加入ghost_net网络
    environment:
      MYSQL_ROOT_PASSWORD: 123456
      MYSQL_DATABASE: atdata
      # 关键设置：解决认证插件问题
      MYSQL_DEFAULT_AUTHENTICATION_PLUGIN: mysql_native_password
    command:
      - "--bind-address=0.0.0.0"
      - "--character-set-server=utf8mb4"
      - "--collation-server=utf8mb4_unicode_ci"
      - "--default-authentication-plugin=mysql_native_password"
      # - "--skip-host-cache"
      # - "--skip-name-resolve"
      - "--log-error=/var/log/mysql/error.log"
    volumes:
      - ./data/mysqllib:/var/lib/mysql # 数据持久化到data目录
      - ./data/my.cnf:/etc/mysql/my.cnf # 自定义配置文件
      - ./data/init.sql:/docker-entrypoint-initdb.d/init.sql # 初始化sql脚本
      - ./data/error.log:/var/log/mysql/error.log # 错误日志，清空命令: > /www/mysql/data/error.log

networks:
  ghost_net:
    external: true # 使用外部网络

设置init.sql

-- 删除默认的开放 IP 用户（可选）
DROP USER IF EXISTS 'at'@'%';
DROP USER IF EXISTS 'root'@'%';

-- at用户：访问权限
CREATE USER 'at'@'172.%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON atdata.* TO 'at'@'172.%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

-- root用户：访问权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
CREATE USER 'root'@'172.%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

设置my.cnf

[mysqld]
# 基础设置
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql

# 网络设置
bind-address = 0.0.0.0
port = 3306
skip-name-resolve

# 字符集设置
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 存储引擎
default-storage-engine = InnoDB

# 性能优化
max_connections = 1000
innodb_buffer_pool_size = 1G
innodb_redo_log_capacity = 256M

# 安全设置
local-infile = 0
skip-external-locking
secure-file-priv = NULL

# 认证插件 (兼容旧客户端)
# default_authentication_plugin = mysql_native_password

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4

nginx.conf

检查nginx是否支持steam：docker exec nginx nginx -V 2>&1 | grep -q with-stream && echo "Stream module enabled" || echo "Stream NOT enabled"

给nginx添加steam配置扩展，找到http模块之前，添加一个steam模块

[ghost@VMCentos7 data]$ vi nginx.conf
# 添加 TCP/UDP 代理模块
stream {
    include /etc/nginx/conf.d/*.stream;
}

创建一个mysql.stream

# MySQL TCP proxy configuration
upstream mysql_backend {
    server mysql:3306;  # Docker 网络中的 MySQL 容器
}

server {
    listen 3306;
    proxy_pass mysql_backend;
    proxy_connect_timeout 600s;
    proxy_timeout 600s;
    proxy_buffer_size 16k;
}

服务验证

容器内部
- Nginx日志：less /www/nginx/data/logs/error.log
- Nginx验证配置：docker exec nginx nginx -t
- nginx映射端口含3306：docker port nginx
- nginx开启steam模块：docker exec nginx nginx -V 2>&1 | grep stream
- Mysql日志：docker logs mysql -f
- Mysql绑定IP：docker exec mysql cat /etc/mysql/my.cnf | grep bind-address
- Mysql本地连接：docker exec -it mysql mysql -u root -p
docker内网
- nginx和mysql已加入内网：docker network inspect ghost_net|grep “nginx\|mysql”
- nginx访问mysql：docker exec nginx sh -c "apk add curl --no-cache 2>/dev/null || apt-get update && apt-get install -y curl; curl -v telnet://mysql:3306"
服务器内网
- 访问端口：telnet 192.168.1.88 3306
外网
- 查询DNS：nslookup mysql.atibmlocal.com
- 查询IP：ping mysql.atibmlocal.com
- 访问端口：telnet mysql.atibmlocal.com 3306
客户端
- sqlyog连接登录

常见问题

caching_sha2_password cannot be loaded：mysql 8.0之后使用该方式加密，客户端过旧，8.4之前还可以指定旧插件mysql_native_password使用
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)：mysql docker对映射文件读写权限问题影响了初始化完整性，需要留意一下映射目录和容器用户的读写权限
ERROR 1524 (HY000): Plugin 'mysql_native_password' is not loaded：mysql8.4之后mysql_native_password插件被彻底弃用，别折腾了，升级客户端或服务端降级