mysql 数据库

docker-compose

version: '3.8'
services:
  mysql:
    image: mysql:8.0 # 使用MySQL 8.0镜像
    container_name: mysql # 容器名称
    restart: unless-stopped # 选项：no不重启 always总是重启 on-failure[:n]失败重启，可限n次 unless-stopped 重启除非手动停止
    networks:
      - ghost_net # 加入ghost_net网络
    environment:
      MYSQL_ROOT_PASSWORD: root_password_cheantycat1
      MYSQL_ROOT_HOST: 0.0.0.0

      MYSQL_USER: at
      MYSQL_PASSWORD: at_password_sdf234j265
      MYSQL_HOST: 0.0.0.0
      # 关键设置：解决认证插件问题
      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"
    volumes:
      - ./data/mysql:/var/lib/mysql # 数据持久化到data目录
      - ./data/my.cnf:/etc/mysql/my.cnf # 自定义配置文件
networks:
  ghost_net:
    external: true # 使用外部网络

创建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

# 日志设置
log-error = /var/log/mysql/error.log
general_log = 1
general_log_file = /var/log/mysql/general.log

# 性能优化
max_connections = 1000
innodb_buffer_pool_size = 1G
innodb_log_file_size = 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;
}

服务验证

  • 客户端 网络
    • 访问DNS：nslookup mysql.atibmlocal.com
    • 追踪路由：tracert mysql.atibmlocal.com
    • 访问端口：telnet mysql.atibmlocal.com 3306
  • 客户端 sqlyog
    • 测试连接
    • 登录：
  • 服务端 Docker网络
    • docker内网：docker network inspect ghost_net|grep "nginx\|mysql"
    • 容器端口映射：docker port nginx
      • Nginx的docker-compose.yml添加-p 3306:3306端口映射
    • 容器连通：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"
      • [ghost@VMCentos7 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"
Get:1 http://deb.debian.org/debian buster InRelease [122 kB]
Get:2 http://deb.debian.org/debian buster-updates InRelease [56.6 kB]
Get:3 http://security.debian.org/debian-security buster/updates InRelease [34.8 kB]
Get:4 http://deb.debian.org/debian buster/main amd64 Packages [7909 kB]
Get:5 http://deb.debian.org/debian buster-updates/main amd64 Packages [8788 B]
Get:6 http://security.debian.org/debian-security buster/updates/main amd64 Packages [610 kB]
Fetched 8741 kB in 27s (318 kB/s)
Reading package lists...
Reading package lists...
Building dependency tree...
Reading state information...
The following additional packages will be installed:
  libcurl4
The following packages will be upgraded:
  curl libcurl4
2 upgraded, 0 newly installed, 0 to remove and 54 not upgraded.
Need to get 603 kB of archives.
After this operation, 4096 B of additional disk space will be used.
Get:1 http://security.debian.org/debian-security buster/updates/main amd64 curl amd64 7.64.0-4+deb10u9 [267 kB]
Get:2 http://security.debian.org/debian-security buster/updates/main amd64 libcurl4 amd64 7.64.0-4+deb10u9 [336 kB]
debconf: delaying package configuration, since apt-utils is not installed
Fetched 603 kB in 2s (383 kB/s)
(Reading database ... 7638 files and directories currently installed.)
Preparing to unpack .../curl_7.64.0-4+deb10u9_amd64.deb ...
Unpacking curl (7.64.0-4+deb10u9) over (7.64.0-4+deb10u2) ...
Preparing to unpack .../libcurl4_7.64.0-4+deb10u9_amd64.deb ...
Unpacking libcurl4:amd64 (7.64.0-4+deb10u9) over (7.64.0-4+deb10u2) ...
Setting up libcurl4:amd64 (7.64.0-4+deb10u9) ...
Setting up curl (7.64.0-4+deb10u9) ...
Processing triggers for libc-bin (2.28-10) ...
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0*   Trying 172.18.0.6...
* TCP_NODELAY set
* Connected to mysql (172.18.0.6) port 3306 (#0)
* RCVD IAC 2
* RCVD IAC 223
100    78    0    78    0     0      8      0 --:--:--  0:00:09 --:--:--     0* RCVD IAC 135
100   132    0   132    0     0     13      0 --:--:--  0:00:10 --:--:--    11
* Closing connection 0
  • 服务端 Nginx
    • Nginx验证配置：docker exec nginx nginx -t
    • Nginx日志：less /www/nginx/data/logs/error.log
    • Nginx Steam模块：docker exec nginx nginx -V 2>&1 | grep stream
    • Nginx端口监听：docker exec nginx netstat -tuln | grep 3306
  • 服务端 Mysql
    • Mysql容器正常：docker ps -f name=mysql
    • Mysql日志：docker logs mysql
    • Mysql绑定IP：docker exec mysql cat /etc/mysql/my.cnf | grep bind-address
      • 添加规则：sed -i '/\[mysqld\]/a bind-address = 0.0.0.0' /www/mysql/data/my.cnf
      • 重启：docker restart mysql
    • Mysql本地连接：docker exec -it mysql mysql -u root -p