[MY-010457]、 [MY-013236]错误
熟悉的两个错误:
[ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
[ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files
docker start 容器也启动不了
最终原因–initialize --lower-case-table-names=1 惹的祸
解决办法
–initialize --lower-case-table-names=1 不要写在容器创建语句中,把lower-case-table-names=1 加入到挂载目录/conf/my.cnf文件中,替换镜像的配置文件/etc/mysql/my.cnf
-v /home/data/mysqlyx/conf/my.cnf:/etc/mysql/my.cnf
以上为干货,直接解决问题,以下为原因分析及解决过程,希望对大家理解问题及解决问题有帮助。
#以mysqlyx为容器名示例进行说明docker logs --tail 50 --follow --timestamps mysqlyx#后面出现类似如下的错误: 2022-09-14T07:09:39.806206263Z 2022-09-14 07:09:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.2022-09-14T07:09:39.942722466Z 2022-09-14 07:09:39+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'2022-09-14T07:09:39.956643812Z 2022-09-14 07:09:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.2022-09-14T07:09:40.100100257Z 2022-09-14 07:09:40+00:00 [Note] [Entrypoint]: Initializing database files2022-09-14T07:09:40.125093527Z 2022-09-14T07:09:40.118405Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 432022-09-14T07:09:40.129628141Z 2022-09-14T07:09:40.129522Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2022-09-14T07:09:41.114997148Z 2022-09-14T07:09:41.114854Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2022-09-14T07:09:43.132741452Z 2022-09-14T07:09:43.132579Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main2022-09-14T07:09:43.132774092Z 2022-09-14T07:09:43.132604Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main2022-09-14T07:09:43.352238721Z 2022-09-14T07:09:43.352109Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.2022-09-14T07:09:47.267751506Z 2022-09-14 07:09:47+00:00 [Note] [Entrypoint]: Database files initialized2022-09-14T07:09:47.271081676Z 2022-09-14 07:09:47+00:00 [Note] [Entrypoint]: Starting temporary server2022-09-14T07:09:47.300216889Z 2022-09-14T07:09:47.297151Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 922022-09-14T07:09:47.300230820Z 2022-09-14T07:09:47.300062Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.2022-09-14T07:09:47.300238120Z 2022-09-14T07:09:47.300080Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.2022-09-14T07:09:47.305642452Z 2022-09-14 07:09:47+00:00 [ERROR] [Entrypoint]: Unable to start server.2022-09-14T07:12:30.351300702Z 2022-09-14 07:12:30+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.2022-09-14T07:12:30.377699965Z 2022-09-14 07:12:30+00:00 [ERROR] [Entrypoint]: mysqld failed while attempting to check config2022-09-14T07:12:30.377731662Z command was: mysqld --initialize --lower-case-table-names=1 --verbose --help --log-bin-index=/tmp/tmp.Aeh6i8SECV2022-09-14T07:12:30.377738666Z 2022-09-14T07:12:30.368865Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 102022-09-14T07:12:30.377744704Z 2022-09-14T07:12:30.370835Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.2022-09-14T07:12:30.377750404Z 2022-09-14T07:12:30.370847Z 0 [ERROR] [MY-013236] [Server] The designated data directory /var/lib/mysql/ is unusable. You can remove all files that the server added to it.2022-09-14T07:12:30.377770391Z 2022-09-14T07:12:30.372871Z 0 [ERROR] [MY-010119] [Server] Aborting
错误出现创建语句分析
#/home/data/为本地卷,非容器的docker run --name mysqlyx -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=rootmysql \-v /home/data/mysqlyx/conf/my.cnf:/etc/mysql/my.cnf \-v /home/data/mysqlyx/data:/var/lib/mysql \-v /home/data/mysqlyx/log:/var/log/mysql \ mysqlyx --initialize --lower-case-table-names=1
网上查了很多办法,给出如下1-5的方法都没法解决本人的问题。
- 开通宿主机挂载目录权限 chmod 777 /home/data/mysqlyx;
- 换新的挂载目录等 ;
- 增加**–privileged=true** ;
- 还有进入容器进行初始化的,但都没有启动容器,也不行;
查找问题
尝试一些创建语句进行,通过逐步加载容器创建参数来查找和定位问题,究竟哪个窗口创建参数的加入会出现问题,步骤如下:
#先运行基本的创建容器语句,下面语句成功运行,docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -d mysqlyx#删除容器,然后再加挂载目录的参数(-v 的后面:左为主机目录或文件,右边为容器目录或文件)docker run --name mysqlyx -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=rootmysql \-v /home/data/mysqlyx/conf/my.cnf:/etc/mysql/my.cnf \-v /home/data/mysqlyx/data:/var/lib/mysql \-v /home/data/mysqlyx/log:/var/log/mysql \ mysqlyx #上面依然成功,然后再加参数:--initialize --lower-case-table-names=1 docker run --name mysqlyx -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=rootmysql \-v /home/data/mysqlyx/conf/my.cnf:/etc/mysql/my.cnf \-v /home/data/mysqlyx/data:/var/lib/mysql \-v /home/data/mysqlyx/log:/var/log/mysql \ mysqlyx --initialize --lower-case-table-names=1 #发现只要加入 --initialize --lower-case-table-names=1出现上述错误
分析问题 --initialize --lower-case-table-names=1 惹的祸
- –initialize --lower-case-table-names=1 参数加上就出错。为什么要加这个参数呢?
注意由于docker、MySQL8.0的限制,必须在初始化数据库的时候就要指定该参数。
阿里巴巴Java开发手册,在MySql建表规约里:【强制】表名、字段名必须使用小写字母或数字 , 禁止出现数字开头,禁止两个下划线中间只出现数字。数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
说明: MySQL 在 Windows 下不区分大小写,但在 Linux 下默认是区分大小写。因此,数据库名、 表名、字段名,都不允许出现任何大写字母,避免节外生枝。
一句话:为了让部署在Linux系统的MySQL不区分大小写,默认的区分大小写的。
不同系统,参数lower-case-table-names的默认值是不同的
lower_case_table_names = 1 表名存储在磁盘是小写的,但是比较的时候是不区分大小写
lower_case_table_names=0 表名存储为给定的大小写和比较是区分大小写的
lower_case_table_names=2, 表名存储为给定的大小写但是比较的时候是小写的
- 1:windows环境默认,不区分,如果想大小写区分则在my.ini 里面的mysqld部分 ,加入 lower_case_table_names=0
- 0:linux环境默认 ,区分;改变表名的大小写区分规则的方法,修改/etc/my.cnf,在[mysqld]后添加添加lower_case_table_names=1,重启MySQL服务,若设置成功,则不再区分表名的大小写。
- 2:macos环境默认
上面说到linux系统的lower_case_table_names = 0 默认为0,但是通常开发测试过程中习惯性的sql语句是不区分大小写的,需要修改该参数。但是在/etc/mysql/my.cnf中直接修改lower_case_table_names = 1 是不可以的,重启mysql直接导致docker无法启动。
官方给出了回答
翻看Mysql官网,https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lower_case_table_names
有这个一段说明:
lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.
也就是说如果想修改这个属性,必须在初始化数据库的时候就要指定该参数。
通常我们下载的docker mysql 镜像启动时会默认初始化,因此想要修改该参数就要重新初始化docker中的数据库。
只有在初始化的时候设置 lower_case_table_names=1才有效,比如:
–initialize --lower-case-table-names=1
解决问题
- –initialize --lower-case-table-names=1 加入到/home/data/mysqlyx/conf/my.cnf文件中:
vi /home/data/mysqlyx/conf/my.cnf# Custom config should go here!includedir /etc/mysql/conf.d/lower_case_table_names = 1 #my.cnf这个文件怎么来的?有以下3种方式:1之前运行好的数据库里拷贝过来2 默认启动好的容器里直拷贝过来:docker cp mysql:/etc/mysql/my.cnf /home/data/mysqlyx/conf/3直接写一下
总结
- Docker中MySQL初始化,不一定非要创建容器中使用 --initialize --lower-case-table-names=1 ,可以放到加载的my.cnf文件中去,用挂载的方式
# 前面为本地目录文件路径,后面为容器文件及路径-v /home/data/mysqlyx/conf/my.cnf:/etc/mysql/my.cnf
- 结论:同时也会发现,挂载的时候已经对默认的数据目录进行初始化了,–initialize --lower-case-table-names=1再执行初始化肯定会出错。
来源地址:https://blog.csdn.net/jiguang127/article/details/126864288