docker搭建mysql目录挂载,完成数据迁移

搭建mysql

docker run -d --name mysql -p 3306:3306 --restart=always -e MYSQL_ROOT_PASSWORD=root mysql:5.7 --lower_case_table_names=1

参数
- -d 后台运行
- --name 容器命名
- -p 端口映射宿主机端口:容器端口
- -e 环境变量用来设置默认用户名密码
- --restart 控制docker重启时, 容器是否(no)自动启动
- --lower_case_table_names
  
  0 表名存储为给定的大小和比较是区分大小写的 1 表名存储在磁盘是小写的，但是比较的时候是不区分大小写
  
  2 表名存储为给定的大小写但是比较的时候是小写的

拷贝mysql默认配置文件, 映射默认配置, 数据保存目录

copy mysql 默认配置 docker cp mysql:/etc/mysql /usr/local/share/env/mysql-conf
删除未映射数据存储目录和配置的mysql容器 docker rm mysql -f
重新运行一个容器, 并加入数据存储目录映射, 目录映射

docker run -d --name mysql -p 3306:3306 --restart=always -v /usr/local/share/env/mysql57-data:/var/lib/mysql -v /usr/local/share/env/mysql-conf:/etc/mysql -e MYSQL_ROOT_PASSWORD=root mysql:5.7 --lower_case_table_names=1

image.png

通过navicat数据传输迁移数据,报Invalid default value for 'created_time',如下

[图片上传中...(image.png-4b66fc-1594446819574-0)]

[ERR] 1067 - Invalid default value for 'created_time'

image.png

查询资料说是sql_mode的原因, 可通过 select @@sql_mode 默认值为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

可通过SET GLOBAL sql_mode='ONLY_FULL_GROUP',临时有效
修改my.cnf配置

查看my.cnf, 引用两个配置文件, 所以直接修改mysql.conf.d/mysqld.cnf配置,在配置中加入,

去掉ONLY_FULL_GROUP_BY, 修改默认group by策略,
去掉NO_ZERO_IN_DATE,NO_ZERO_DATE 修改datetime和timestamp默认值可为null
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

改了sql_mode模式为严格或者宽松模式情况下，都会报错。本地和线上服务器相同的模式也不行
网上搜索资料，
mysql5.6.6之前，timestamp时间类型有一个默认行为：
TIMESTAMP列如果没有明确声明NULL属性，默认为NOT NULL。（而其他数据类型，如果没有显示声明为NOT NULL，则允许NULL值。）
insert插入一条数据，TIMESTAMP的列值为NULL，会自动存储时候，会将当前timestamp存储到这个timestamp列中。
也就是说会自动分配 DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP 属性。每次更新记录都会将timestamp列更新为当前的时间戳对应的时间值现在mysql5.6版本以后，timestamp字段的默认行为发生的变化，多了一些限制。如果timestamp列设置默认值为NULL,Default NULL 这会发生报错 1067 - Invalid default value for如果需要让timestamp列在创建表时可以为NULL值，需要将explicit_defaults_for_timestamp设为ONexplicit_defaults_for_timestamp默认为OFF关闭状态，打开后可以阻止timestamp的默认行为。

继续在mysqld.cnf配置加入explicit_defaults_for_timestamp = ON

运行 docker restart mysql 重启mysql后就可以正常执行上面的数据传输

image.png