docker安装mysql，映射本地目录

有点细节。lower_case_table_names卡我好久，另外这是增加docker熟练程度。

先在/volume2/docker/mysql/mysql8-2 建好目录:

bash-4.4# ls
conf    data    init    log     my.cnf

init/init.sql。刷新root账号，设置默认密码，非本机也可以访问。

-- 设置 root 用户的认证方式为 mysql_native_password
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

my.cnf:

[client]
port=3306
default-character-set = utf8mb4

[mysqld]
port=3306
character-set-server = utf8mb4
skip-character-set-client-handshake
skip-name-resolve
lower_case_table_names = 1
default-storage-engine = InnoDB
innodb_file_per_table = 1
default_authentication_plugin = mysql_native_password
local-infile = 1
bind-address = 0.0.0.0

log_error = /var/log/mysql/error.log
general_log_file = /var/log/mysql/general.log
general_log = 0
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
# 死锁日志
log_error_verbosity = 3
innodb_print_all_deadlocks = ON
# 去掉only_full_group_by
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

sql_mode自定义，去掉only_full_group_by。 是为了避免出现only_full_group_by问题。navicat运行个简单sql都会报错。这样：

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

另外就是设置大小写不区分：

lower_case_table_names = 1

0：表名和数据库名以实际大小写存储，并且查询时区分大小写。

1：表名和数据库名以小写形式存储在磁盘上，查询时不区分大小写。

2：表名和数据库名以实际大小写存储在磁盘上，但查询时不区分大小写。

2 这里是个坑，仅在大小写不敏感的文件系统上被允许。设置了启动日志是会出warn级别日志，然后重新设置为0。基本上大部分系统都不能设置为2。

lower_case_table_names was set to 2, even though your the file system '/var/lib/mysql/' is case sensitive.  Now setting lower_case_table_names to 0

最后还有一个点，mysql8初始化后，不支持再修改 lower_case_table_names，my.cnf改为不一致的值会报错的：

Different lower_case_table_names settings for server ('1') and data dictionary ('0')

好像是因为支持了字典什么的新特性。

运行:

docker run -d \
--name mysql8 \
-p 3308:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-e MYSQL_INITDB_SKIP_TZINFO=1 \
--mount type=bind,src=/volume2/docker/mysql/mysql8-2/my.cnf,dst=/etc/my.cnf \
-v /volume2/docker/mysql/mysql8-2/data:/var/lib/mysql \
-v /volume2/docker/mysql/mysql8-2/log:/var/log/mysql \
-v /volume2/docker/mysql/mysql8-2/conf:/etc/my.cnf.d \
-v /volume2/docker/mysql/mysql8-2/init:/docker-entrypoint-initdb.d \
--sysctl net.ipv4.tcp_timestamps=0 \
--ulimit nofile=1048576:1048576 \
--ulimit nproc=65535:65535 \
--restart unless-stopped \
mysql:8.0 

检测是否运行成功：

1. docker没有报错日志。
2. 进去docker容器，能连上mysql

docker exec -it $containerId sh

# 会提示要输入密码。
mysql -h 127.0.0.1 -P 3306 -uroot -p