Docker 安装mysql 8+

1、创建存储文件夹

# 创建挂载文件夹用于存储 数据、配置、日志
mkdir -p  /usr/local/mysql/{data,conf,logs}

2、获取mysql 镜像

# 获取指定镜像

docker pull mysql:version # version = 版本号

# 获取最新镜像
docker pull mysql:latest # 获取最新的版本

2.1、启动mysql镜像，获得启动后的my.cnf 配置文件（如果有my.cnf的直接忽略该步骤）

# 快速启动mysql镜像
docker run  --name mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest

# 复制配置文件到本地

docker ps # 查看运行的mysql CONTAINER ID


docker cp [CONTAINER ID]:/etc/my.cnf /usr/local/mysql/conf/


# 拷贝出来之后删除掉运行的镜像

docker rm -f mysql

2.2、mysql 配置文件 my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
# 忽略大小写
lower_case_table_names=1
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache # 新版本里面没有该属性了 改成了 host-cache-size=0
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

3、启动 mysql 镜像并挂载目录

3.1、mac 启动

docker run --restart always -p 3306:3306 --name mysql -v C:\docker\mysql\conf\my.cnf:/etc/my.cnf -v C:\docker\mysql\logs:/var/log/ -v C:\docker\mysql\data:/var/lib/mysql--privileged=true -e MYSQL_ROOT_PASSWORD=123456 -d mysql:latest --character-set-server=utf8mb4 --lower_case_table_names=1

3.2、widnows 启动

docker run --restart always -p 3306:3306 --name mysql \
-v /mac电脑路径/docker/mysql/conf/my.cnf:/etc/my.cnf \
-v /mac电脑路径/docker/mysql/logs:/var/log/ \
-v /mac电脑路径/docker/mysql/data:/var/lib/mysql \
--privileged=true \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:latest \
--character-set-server=utf8mb4 \
--lower_case_table_names=1

3.3、linux 启动

docker run -p 3306:3306 --name mysql \
-v /usr/local/mysql/conf/my.cnf:/etc/my.cnf \
-v /usr/local/mysql/logs:/var/log/ \
-v /usr/local/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:latest \
--lower_case_table_names=1

3.4、启动过程中出现一个问题：changing ownership of ‘/var/lib/mysql/’: Operation not permitted 挂载的目录权限问题

sudo chmod 777 /本地挂载的路径

4、mysql 授权远程访问

4.1、登录到docker中

docker exec -it 运行中的容器ID /bin/bash

# 进入后
mysql -uroot -p # 输入docker run 过程中设置的mysql 密码

4.2、在mysql命令行中配置root 账号远程访问

# 使用root权限登陆mysql 执行命令
grant all privileges on *.* to 'root'@'%' identified by '密码';
grant all privileges on *.* to 'root'@'%' identified by '123456';
# 刷新授权
flush privileges;

# 关闭授权
revoke all on *.* from dba@localhost;

4.3、配置过程中8.0+ 验证错误修复

# 通过设置解决，同样的先进入到docker 容器中

#使用root权限登陆mysql
mysql -uroot -p

#输入密码登陆

# 设置密码永不超时
alter user 'root'@'%' identified by '输入密码' password expire never;
alter user 'root'@'%' identified by '123456' password expire never;

alter user 'root'@'%' identified with mysql_native_password by '输入密码';
alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;

4.4、mysql 8+ 设置远程登录授权问题解决

# 检查当前用户有哪些
select user,host from user;

#+------------------+-----------+
#| user             | host      |
#+------------------+-----------+
#| root             | %         |
#| mysql.infoschema | localhost |
#| mysql.session    | localhost |
#| mysql.sys        | localhost |
#| root             | localhost |
#+------------------+-----------+
#5 rows in set (0.01 sec)

# 如果没有用户则执行：
create user 'test'@'%' identified with mysql_native_password by '123456';

grant all privileges on *.* to 'test'@'%';

flush privileges;

5、mysql 默认配置下面 Too many connections 问题导致无法连接数据库

5.1、进入数据库查看mysql最大连接数

mysql -uroot -p
# 输入密码进入

show variables like '%max_connections%';

show global status like 'Max_used_connections';

5.2、主动设置mysql最大连接数

set GLOBAL max_connections=1024;

5.3、查看当前等待超时

show global variables like 'wait_timeout'; #查看最大睡眠时间

set global wait_timeout=300; # 设置最大睡眠时间 s

show global variables like 'interactive_timeout'; # 查看超时时间

set global interactive_timeout=500; # 设置超时时间 s

5.4、永久设置是修改my.cnf文件

group_concat_max_len=10240
wait_timeout=300
interactive_timeout=500

5.5、global中的常见参数说明 show global variables;