wenyl 的个人博客
docker pull mysql:5.7.40
sudo mkdir -p /opt/docker_data/mysql/5.7.40/{conf,data,log}
sudo chmod -R 755 /opt/docker_data/mysql/5.7.40
vi /opt/docker_data/mysql/5.7.40/conf/custom.cnf
内容如下
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
# 字符集配置
init_connect="SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake
# 网络与解析
skip-name-resolve
explicit_defaults_for_timestamp=true
# 表名大小写
lower_case_table_names=1
# SQL模式（MySQL 5.7适用）
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# InnoDB配置优化
innodb_buffer_pool_size = 128M
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 2
# 连接配置
max_connections = 200
wait_timeout = 600
interactive_timeout = 600
# 通用配置
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
bind-address = 0.0.0.0
docker run --name mysql_5.7.40 \
-v /opt/docker_data/mysql/5.7.40/log:/var/log/mysql \
-v /opt/docker_data/mysql/5.7.40/data:/var/lib/mysql \
-v /opt/docker_data/mysql/5.7.40/conf:/etc/mysql/conf.d \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-d mysql:5.7.40
docker pull mysql:8.0
mkdir E:\docker_data\mysql\8\data
mkdir E:\docker_data\mysql\8\conf
mkdir E:\docker_data\mysql\8\logs
E:\docker_data\mysql\8\conf\my.cnf文件中写入
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
docker run -d --name mysql-8.0 -p 3306:3306 -v E:\docker_data\mysql\8.0\data:/var/lib/mysql -v E:\docker_data\mysql\8.0\config\my.cnf:/etc/mysql/my.cnf -v E:\docker_data\mysql\8.0\logs:/logs -e MYSQL_ROOT_PASSWORD=root mysql:8.0
在前面两章的操作中都是在宿主机建立好my.conf文件直接挂载到容器，这里选择启动容器，然后再去修改配置文件，防止自己定义的conf文件和容器内置的conf文件配置冲突导致无法启动容器
这里要拿到容器内原始的配置，需要先创建一个容器，我还是选择挂载到data，config，logs三个目录下
docker run -d --name mysql-8.0 -p 33060:3306 -v D:\download\docker_data\mysql\8.0\data:/var/lib/mysql -v D:\download\docker_data\mysql\8.0\config:/etc/mysql/conf.d -v D:\download\docker_data\mysql\8.0\logs:/logs -e MYSQL_ROOT_PASSWORD=root mysql:8.0
此时可以看到目录下没有任何文件，此时我们把容器中的my.conf文件内容复制一份到宿主机中这里有两种方式，一种是直接在宿主机新建一个my.cnf文件，然后进入容器的/etc目录下，复制出my.cnf文件的内容到宿主机的my.cnf文件，还有一种就是使用docker cp命令把文件直接复制出来
docker cp mysql-8.0:/etc/my.cnf D:\download\docker_data\mysql\8.0\config\my.cnf
执行完这个命令，就能看到目录下多了一个cnf文件，内容大致如下，不同版本mys
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# 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
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/
配置文件最后明确指出了包含了/etc/mysql/conf.d/目录下的配置文件，这里我们就有了两种选择，可以直接在my.cnf中修改，也可以自己新建conf.d目录然后新建配置文件在重新挂载到容器,这里我还是选择放在conf.d目录下，尽量避免直接修改my.cnf
我们先删除之前创建的容器
docker rm -f mysql-8.0
然后再config目录下新增conf.d目录,然后再conf.d目录下新增custom.cnf
内容大致如下
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default-authentication-plugin=mysql_native_password
log_error = /logs/mysql-error.log
# 慢查询日志
slow_query_log=1
slow_query_log_file=/logs/mysql-slow.log
long_query_time=2
此时重新创建容器，挂载my.cnf和conf.d目下的custom.cnf命令如下
docker run -d --name mysql-8.0 -p 33060:3306 -v D:\download\docker_data\mysql\8.0\data:/var/lib/mysql -v D:\download\docker_data\mysql\8.0\config\conf.d:/etc/mysql/conf.d -v D:\download\docker_data\mysql\8.0\config\my.cnf:/etc/my.cnf -v D:\download\docker_data\mysql\8.0\logs:/logs -e MYSQL_ROOT_PASSWORD=root mysql:8.0
安装完成后直接登录会提示错误
先进入容器
docker exec -it mysql_5.7.40 /bin/bash
连接mysql
mysql -u root -p
查询当前用户
SELECT user, host FROM mysql.user WHERE user = 'root';
结果如下
这里如果没有user=root和host=%的用户，则运行如下命令
CREATE USER 'root'@'%' IDENTIFIED BY '你的密码';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
如果有的话直接执行
GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;
再刷新一下权限即可
FLUSH PRIVILEGES;