docker-compose.yml mysql部署说明

mysql.cnf 配置文件

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4 

[mysqld]
#一般设置为IP，注意要唯一
server_id= 1
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为8比特编码的latin1字符集
character_set_server    = utf8mb4
# 创建新表时将使用的默认存储引擎
default_storage_engine= InnoDB
#写入binlog二进制日志中数据库
binlog-do-db=education
#时区设置
default-time-zone= '+8:00'
log_timestamps= SYSTEM
#8.0不加会报错
secure-file-priv= NULL

# 缓存配置
thread_cache_size= 64
#mysql的query_cache在8.0已经被取消

# 日志配置
log_error= mysql-error.log
slow_query_log_file= mysql-slow.log
relay-log= mysql-relay
log-bin= mysql-bin
slow_query_log= 1
long_query_time= 1

# GTID配置
gtid-mode= ON
enforce-gtid-consistency= 1

yml文件

version: '3.4'

services:
  fire.mysql:
    image: mysql:8.0
    container_name: firemysql
    ports:
      - 3306:3306
    environment:
      MYSQL_ROOT_PASSWORD: 你的密码
      TZ: Asia/Shanghai
    volumes:
      - /app/storage/mysql/data:/var/lib/mysql
      - /app/storage/mysql/my.cnf:/etc/mysql/my.cnf
      - /app/storage/mysql/init_sql:/docker-entrypoint-initdb.d
      - /app/storage/mysql/backup/data:/app/storage/mysql/backup/data
    restart: always

mysql7.0以上版本部署完之后需要执行sql让mysql外网可访问

use mysql;
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '密码';
FLUSH PRIVILEGES;
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
grant replication client,replication slave on *.* to 'repl'@'%';
      

mysql定时备份示例

#sql的登录用户名和密码
mysql_user="用户名"
mysql_password="密码"
mysql_host="IP地址"
mysql_port="3306"
mysql_charset="utf8mb4"
# 备份文件存放地址
backup_location=/app/storage/mysql/backup/data
# 是否删除过期数据
expire_backup_delete="ON"
expire_days=7
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
# 备份指定数据库中数据
docker exec -i firemysql mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B firemanagement --set-gtid-purged=OFF > $backup_dir/项目_$backup_time.sql
echo "备份文件路径: $backup_dir/项目_$backup_time.sql"
# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
        `find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
        echo "已清理过期的数据库备份文件..."
fi
#创建定时任务
#crontab -e
#0 3 * * * cd /app/storage/mysql/backup;sh mysql_backup.sh >> /app/storage/mysql/backup/backup_logs.log
#crontab -l