Docker compose Mysql 8 sql_mode 配置及修改only_full_group_by

mysql默认的配置sql_mode包含ONLY_FULL_GROUP_BY，我们要去掉，网上找了好多帖子，大多数copy来copy去，说的乱七八糟，很无奈，最后折腾了半天才搞定，主要是注意2点
1、先明白你的mysql版本，mysql8是没有 NO_AUTO_CREATE_USER，所有Sql_mode中不能包含这一项
2、因为我是用docker-compose，最好不要修改原来镜像文件

一、增加用户配置文件

vi custom.cnf


[mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

二、增加数据库目录

mkdir db

三、编辑compose文件

vi docker-compose.yml 
version: '3'
services:
    mysql:
        container_name: "mysql"
        network_mode: "host"
        environment:
            MYSQL_ROOT_PASSWORD: "setYourPass"
        image: "mysql:latest" 
        restart: always
        volumes:
            - "./db:/var/lib/mysql"
            - "./custom.cnf:/etc/mysql/conf.d/custom.cnf"
        ports:
            - "3306:33060"

四、启动mysql

docker-compose up -d

五、查看日志

docker logs mysql  -f

就能看到mysql启动的输出了，如下：
2020-05-19 05:39:16+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
2020-05-19 05:39:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2020-05-19 05:39:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.18-1debian9 started.
2020-05-19T05:39:17.476672Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.
2020-05-19T05:39:17.476830Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.18) starting as process 1
2020-05-19T05:39:17.999178Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-05-19T05:39:18.005713Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/var/run/mysqld' in the path is accessible to all OS users. Consider choosing a different directory.
2020-05-19T05:39:18.029171Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.18' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server - GPL.
2020-05-19T05:39:18.182854Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: '/var/run/mysqld/mysqlx.sock' bind-address: '::' port: 33060

六、验证sql_mode是否生效
直接查询分析器执行下面任一命令，随意：

show variables like '%sql_mode';

show variables like '%sql_mode';

@@GLOBAL.sql_mode
STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

可以看到输出的结果中已经没有only_full_group_by

最后编辑于：2020.05.19 13:56:18