mariadb 数据库

维护

• 验证

  docker exec -it mariadb mariadb -u root -p		# 数据库容器内部验证

• 免密模式

  docker-compose.yml
      command: --skip-grant-tables   # 免密模式

• 用户权限调整

  docker exec -it mariadb mariadb -u root		# 进容器数据库终端
  
  ----------激活-----------
  FLUSH PRIVILEGES;							# 激活权限
  
  ----------查询-----------
  SELECT User, Host FROM mysql.user WHERE User = 'root';
  
  ----------修改-----------
  写入sql执行
  FLUSH PRIVILEGES;

256M.cnf

[mysqld]
# ====================================================================
# 1. 核心内存限制与并发控制 (针对 256M 内存定制)
# ====================================================================
innodb_buffer_pool_size = 64M
max_connections = 30
# innodb_buffer_pool_instances = 1  # 缓冲池小，强制单实例，降 CPU 消耗，MariaDB 11 重写了缓冲池逻辑，注释掉

# ====================================================================
# 2. 极致省内存大招 (干掉隐藏刺客)
# ====================================================================
performance_schema = OFF
thread_cache_size = 4
key_buffer_size = 8M              # 限制 MyISAM 占用的内存

# 限制内存临时表大小 (防突发大表吃光内存)
tmp_table_size = 16M
max_heap_table_size = 16M

# ====================================================================
# 3. 速度与网络优化
# ====================================================================
skip-name-resolve = 1             # 关闭 DNS 解析，连接速度拉满, 会影响用户权限配置在域名上
innodb_log_buffer_size = 16M      # 事务日志缓冲，足够应对中小并发
max_allowed_packet = 16M          # 防止单条 SQL 过大

# ====================================================================
# 4. 连接回收与稳定性 (防死锁、防僵尸连接)
# ====================================================================
wait_timeout = 660                # 10 分钟自动清理闲置非交互连接
interactive_timeout = 660         # 10 分钟自动清理闲置交互连接 (如 Navicat)
innodb_lock_wait_timeout = 20     # 遇到死锁 20 秒快速熔断释放
# innodb_file_per_table = 1         # 开启独立表空间，方便碎片回收, MariaDB 11 默认强制开启，注释掉

# ====================================================================
# 5. 安全与字符集
# ====================================================================
symbolic-links = 0
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

docker-compose.yml 

services:
  mariadb:
    image: mariadb:lts
    container_name: mariadb
    restart: unless-stopped
    # command: --skip-grant-tables	# 跳过权限验证，调试用
    #environment:
    #  - MARIADB_ROOT_PASSWORD=123456
    #　user: "1000:1000"
    #ports: ["0.0.0.0:3306:3306"]                     # 暴露端口到宿主机
    volumes:
      - ./data/mariadb:/var/lib/mysql
      - ./data/conf.d:/etc/mysql/conf.d
      - ./data/init.sql:/docker-entrypoint-initdb.d/init.sql:ro
      - /etc/localtime:/etc/localtime:ro
    networks:
      - ghost_net
    deploy:
      resources:
        limits:
          memory: 256M
          cpus: 0.5
networks:
  ghost_net:
    external: true

mariadb.stream

# MariaDB TCP 代理（生产增强版）
# limit_conn_zone只能由一个，不能共存
limit_conn_zone $binary_remote_addr zone=addr:10m;

upstream mariadb_backend {
    server mariadb:3306 max_fails=3 fail_timeout=30s;
}

server {
    listen 3306 so_keepalive=on;

    proxy_pass mariadb_backend;
    proxy_connect_timeout 5s;
    proxy_timeout 3600s;
    proxy_buffer_size 16k;
    proxy_socket_keepalive on;

    limit_conn addr 50;
    limit_conn_log_level warn;
}

init.sql

-- ./data/init.sql
-- docker exec -it mariadb mariadb -u root -p123456
-- SELECT User, Host FROM mysql.user;
-- 创建数据库
CREATE DATABASE IF NOT EXISTS forgejo_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS kc_portfolio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- root用户
CREATE OR REPLACE USER 'root'@'172.%' IDENTIFIED BY '123456';
CREATE OR REPLACE USER 'root'@'192.%' IDENTIFIED BY '123456';
CREATE OR REPLACE USER 'root'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

-- ghost用户
CREATE OR REPLACE USER 'ghost'@'172.%' IDENTIFIED BY '123456';
CREATE OR REPLACE USER 'ghost'@'192.%' IDENTIFIED BY '123456';
CREATE OR REPLACE USER 'ghost'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON forgejo_db.* TO 'ghost'@'172.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON forgejo_db.* TO 'ghost'@'192.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON forgejo_db.* TO 'ghost'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON kc_portfolio.* TO 'ghost'@'172.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON kc_portfolio.* TO 'ghost'@'192.%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON kc_portfolio.* TO 'ghost'@'localhost' WITH GRANT OPTION;

-- 刷新生效
FLUSH PRIVILEGES;