docker docker-compose 安装 mysql:5.7.31

一、背景

在企业中相信大家会遇到很多不同的测试服务器，在运维人员中有大量的部署工作要做，需要连接mysql数据库进行测试，做压力测试的，这里选择docker安装mysql5.7，简单便捷。

至于为什么选择mysql5.7，是因为mysql5.7版本稳定，其次安装相较于其他高版本（mysql8）实在是简单太多了，少了很多grant等初始化操作，比如access denied for user root......password no。看到这些连接错误提示信息实在头疼。

前提

二、安装 docker，docker-compose

国内镜像源

Docker中国区官方镜像
https://registry.docker-cn.com

网易
http://hub-mirror.c.163.com

ustc
https://docker.mirrors.ustc.edu.cn

通过daemon.json文件来修改

（1）在/etc/docker目录中添加daemon.json文件，内容如下：
        {
              "registry-mirrors": ["http://hub-mirror.c.163.com","https://registry.docker-cn.com","https://docker.mirrors.ustc.edu.cn"]
        }
（2）重启docker服务：
     systemctl restart docker
（3）执行：docker info，查看是否配置成功：

三、创建docker-compose 编排文件

# vim docker-compose.yml

version: '3.8'
services:
  mysql:
    container_name: mysql57
    image: mysql:5.7.31
    restart: always
    ports:
      - 3307:3306
    privileged: true
    volumes:
      - $PWD/mysql57/log:/var/log/mysql 
      - $PWD/mysql57/conf/my.cnf:/etc/mysql/my.cnf
      - $PWD/mysql57/data:/var/lib/mysql
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      MYSQL_USER: 'haima'
      MYSQL_PASS: '123456'
    command: [
        '--character-set-server=utf8mb4',
        '--collation-server=utf8mb4_general_ci',
        '--max_connections=3000'
    ]
    networks:
      - myweb

networks:

  myweb:
    driver: bridge

三.创建初始化脚本 init-mysql.sh

#!/bin/bash
mkdir -p $PWD/mysql57/{conf,data,log}  #创建本地文件夹


#新建配置文件
tee $PWD/mysql57/conf/my.cnf<<-'EOF'
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
lower_case_table_names=1 #实现mysql不区分大小（开发需求，建议开启）
# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
default-time_zone = '+8:00'

# 更改字符集 如果想Mysql在后续的操作中文不出现乱码,则需要修改配置文件内容
symbolic-links=0
character-set-server=utf8mb4
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4

EOF

四、编排文件以及安装脚本目录结构

[root@centos7 mysql]# chmod +x init-mysql.sh docker-compose.yml  #加执行权限
[root@centos7 mysql]# ./init-mysql.sh

[root@centos7 mysql]# tree ./  #查看目结构
./
├── docker-compose.yml
├── init-mysql.sh
└── mysql57
    ├── conf
    │   └── my.cnf
    ├── data
    └── log

五、启动服务

docker-compose up -d
此时服务已经启动成功了.使用角本是不是很爽,嘿嘿...

登陆mysql

[root@centos7 mysql57]# docker exec -it mysql57 mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

mysql 配置My.cnf

[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
datadir = /data/
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 100G
table_open_cache = 512
sort_buffer_size = 2M
net_buffer_length = 4K
read_buffer_size = 2M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 64M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""


innodb_data_home_dir = /data/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/
innodb_buffer_pool_size = 512M
innodb_log_file_size = 256M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

常用命令

docker ps -a #查看启动的服务
docker-compose up -d #后台启动服务
docker-compose -h #帮助命令
docker-compose down #停止并删除服务
docker-compose restart #重启服务
docker-compose stop #停止服务
docker-compose start #停止服务
docker-compose logs #停止日志