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插件被彻底弃用,别折腾了,升级客户端或服务端降级