【Docker】MySQL8问题_docker容器设置sql_mode

1. 远程连接问题

MySql 8.0版本修改了默认的加密规则，用Navicat连接会报错。
解决方法：加密方式改为mysql_native_password即可

$ mysql -u root -p
--输入密码
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| seckill            |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
--查询当前数据库相关信息
mysql> select host,user,authentication_string,plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1                              | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

--将root用户设置为所有地址可登录，原来是localhost表示只用本机可登录
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

--刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

--将用户root密码设置为永不过期
mysql> alter user 'root'@'%' identified by '12345678' password expire never;
Query OK, 0 rows affected (0.01 sec)

--将root用户密码加密方式改为mysql_native_password ，上面查到root用户密码的加密方式为caching_sha2_password 
mysql> alter user 'root'@'%' identified with mysql_native_password by '12345678';
Query OK, 0 rows affected (0.00 sec)

--刷新权限，在别的机器上即可登录
mysql> flush privileges;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

2. 数据库区分大小写

• 是否区分大小写是通过lower_case_table_names=1参数来设置

  lower_case_table_names = 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
  lower_case_table_names=0 表名存储为给定的大小和比较是区分大小写的
  lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的

• 通过show Variables like ‘%table_names’ 来查看默认的值

虚拟机

修改MySql配置文件 vi /etc/my.cnf ，在[mysqld]配置中增加 lower_case_table_names=1

-- 修改配置文件
cd /etc/mysql/
vi my.cnf

-- 添加一行配置
[mysqld]
datadir=/home/data
log-error=/home/logs/error.log
lower_case_table_names=1
1
2
3
4
5
6
7
8
9

docker容器

• 以上方式不适用容器技术，修改配置文件后restart失败

• 需要在创建容器的时候就指定lower_case_table_names的属性
  命令如下：

  docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root -d -v /home/mysql/:/var/lib/mysql mysql --lower_case_table_names=1
  1

  lower_case_table_names=1要加在镜像名后面，镜像名前面是参数，后面是mysql配置

• 进入数据库查看是否成功

  show global variables like '%lower_case%';
  
  +------------------------+-------+
  | Variable_name          | Value |
  +------------------------+-------+
  | lower_case_file_system | OFF   |
  | lower_case_table_names | 1     |
  +------------------------+-------+
  2 rows in set (0.00 sec)
  1
  2
  3
  4
  5
  6
  7
  8
  9

3. only_full_group_by

错误一般发生在mysql 5.7以及 5.7以上的版本中，其原因是mysql的默认 sql_mode=“ONLY_FULL_GROUP_BY” 这个配置严格执行了 ‘SQL92标准’

进入mysql服务

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
1
2
3

4. 快速开始

docker run -d --name xxx --privileged -p 3306:3306 -e MYSQL_ROOT_PASSWORD=xxx  xxx镜像 \
--lower_case_table_names=1 \
--sql_model=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION \
--default-authentication-plugin=mysql_native_password \
--max_connections=300 \
--group_concat_max_len=102400 \
--log_bin_trust_function_creators=1
1
2
3
4
5
6
7

• lower_case_table_names：忽略大小写
• sql_model：only_full_group_by
• default-authentication-plugin：加密方式
• max_connections：最大连接
• group_concat_max_len：最大拼接长度
• log_bin_trust_function_creators：二进制日志参数