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