mysql5.7容器升级8.3

小ྂ渊ྂ

已于 2024-04-03 12:48:39 修改

阅读量1.7k

点赞数 38

文章标签： docker mysql

于 2024-02-22 15:37:20 首次发布

本文链接：https://blog.csdn.net/daysg/article/details/136234185

版权

文章描述了在使用Docker容器升级MySQL8.x时遇到的三个问题：废弃选项导致启动失败、redolog升级错误和lower_case_table_names配置冲突。作者提供了相应的解决方法，包括检查和修改配置、清理日志和检查文件权限。

摘要由CSDN通过智能技术生成

升级过程参考的以下文章：

【docker版MySQL升级 - CSDN App】http://t.csdnimg.cn/b22Na

#记录升级过程遇到的问题#

1，配置已弃用问题

[root@taosnode1 conf]# docker logs -f mysql-dev1
2024-02-21 02:38:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
2024-02-21 02:38:39+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-02-21 02:38:39+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-02-21T02:38:40.389788Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-02-21T02:38:40.745036Z 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.
2024-02-21T02:38:40.747996Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-02-21T02:38:40.760875Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-21T02:38:41.173473Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-21T02:38:41.624939Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2024-02-21T02:38:41.625028Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2024-02-21T02:38:41.627196Z 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.
2024-02-21T02:38:41.630947Z 0 [ERROR] [MY-000068] [Server] unknown option '--skip-host-cache'.
2024-02-21T02:41:21.994966Z 0 [ERROR] [MY-000068] [Server] unknown option '--skip-name-resolv'.
2024-02-21T02:38:41.632850Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-02-21T02:38:43.237875Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.3.0)  MySQL Community Server - GPL.
2024-02-21T02:38:43.237946Z 0 [System] [MY-015016] [Server] MySQL Server - end.

MySQL 8.x 中已经没有 --skip-name-resolv --skip-host-cache 这两个选项了，因为它在 MySQL 8.x 中已被弃用并移除。因此，MySQL 容器启动时无法识别这个选项而导致失败。

解决办法：

检查您的配置文件或启动命令，并删除或注释掉其中的--skip-name-resolv --skip-host-cache 这两个选项了

[root@taosnode1 conf]# pwd
/home/mysql/conf
[root@taosnode1 conf]# cat mysqld.cnf
[mysqld]
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
datadir		= /var/lib/mysql
max_connections = 500

#skip-host-cache
#skip-name-resolv

#log-error	= /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address	= 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

---------------------------------------------------------------------------------------------------------------------------------

2，docker运行mysql无法运行提示升级出错redo log问题

[root@10 data]# docker  logs -f mysql8
2024-02-22 03:07:23+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
2024-02-22 03:07:24+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-02-22 03:07:24+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
mysqld: [Warning] World-writable config file '/etc/my.cnf' is ignored.
2024-02-22T03:07:24.792725Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-02-22T03:07:25.078417Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-02-22T03:07:25.087623Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-22T03:07:26.128852Z 1 [ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 5.7.36, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.3/en/upgrading.html
2024-02-22T03:07:26.128885Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Generic error.
2024-02-22T03:07:26.552053Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2024-02-22T03:07:26.552523Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2024-02-22T03:07:26.552587Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-02-22T03:07:26.556333Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.3.0)  MySQL Community Server - GPL.
2024-02-22T03:07:26.556370Z 0 [System] [MY-015016] [Server] MySQL Server - end.

[ERROR] [MY-012526] [InnoDB] Upgrade is not supported after a crash or shutdown with innodb_fast_shutdown = 2. This redo log was created with MySQL 5.7.36, and it appears logically non empty. Please follow the instructions at http://dev.mysql.com/doc/refman/8.3/en/upgrading.html

解决办法：

#删除data目录下
mysql_upgrade_info
ib_logfile0
ib_logfile1

--------------------------------------------------------------------------------------------------------------------------------

3，lower_case_table_names = 1配置

[root@10 data]# docker logs -f mysql8-3307
2024-02-22 06:19:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
2024-02-22 06:19:17+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-02-22 06:19:17+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.3.0-1.el8 started.
'/var/lib/mysql/mysql.sock' -> '/var/run/mysqld/mysqld.sock'
2024-02-22T06:19:18.067820Z 0 [System] [MY-015015] [Server] MySQL Server - start.
2024-02-22T06:19:18.401035Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.3.0) starting as process 1
2024-02-22T06:19:18.408787Z 0 [Warning] [MY-013907] [InnoDB] Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=62914560. Please use innodb_redo_log_capacity instead.
2024-02-22T06:19:18.411776Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2024-02-22T06:19:18.411875Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-02-22T06:19:21.425962Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-02-22T06:19:24.271648Z 2 [ERROR] [MY-013520] [Server] Schema name 'MONITORSIGNAL' containing upper case characters is not allowed with lower_case_table_names = 1.
2024-02-22T06:19:24.295022Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2024-02-22T06:19:24.295113Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-02-22T06:19:26.335581Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.3.0)  MySQL Community Server - GPL.
2024-02-22T06:19:26.335605Z 0 [System] [MY-015016] [Server] MySQL Server - end.

Schema name 'MONITORSIGNAL' containing upper case characters is not allowed with lower_case_table_names = 1.

这个错误表示，在配置中将 lower_case_table_names 设置为 1，但数据库中的某个模式（Schema）名包含大写字母，这与该配置不兼容。

要解决这个问题，您可以采取以下步骤：

修改配置：在 MySQL 配置文件中将 lower_case_table_names 参数设置为 0 或 2。这样可以允许数据库中的模式名包含大写字母。
修改模式名：如果可能的话，修改数据库中的模式名，确保它们全部小写，以符合 lower_case_table_names 的设置。
重新启动容器：在修改了配置或数据库后，重新启动 MySQL 容器，以使更改生效

要修改 MySQL 配置文件，您需要编辑 MySQL 配置文件（通常是 my.cnf）并添加或修改以下行：

[mysqld]
lower_case_table_names=0

或

[mysqld]
lower_case_table_names=2

#我是参照其他服务器的msysql配置注释掉了

lower_case_table_names 是一个 MySQL 配置选项，用于控制数据库对象（表名和数据库名）的大小写敏感性。

这个选项的值可以是以下几种：

0：在这种模式下，表名和数据库名的存储方式是操作系统的默认方式。这意味着在 UNIX 系统上，表名和数据库名是区分大小写的；而在 Windows 系统上，它们是不区分大小写的。
1：在这种模式下，MySQL 将所有表名和数据库名视为小写。这意味着不管输入的表名是大写、小写还是混合大小写，MySQL 都会将它们转换为小写存储，并且在进行表名匹配时不区分大小写。
2：在这种模式下，MySQL 将所有表名和数据库名视为大小写敏感的，但在创建新表时将其存储为小写。这意味着 MySQL 在比较表名和数据库名时是区分大小写的，但在创建新表时，如果使用了大写字母，MySQL 将会自动将其转换为小写。

------------------------------------------------------------------------------------------------------------------------------

4容器启动后，进入容器无法连接mysql数据库

[root@10 data]# docker exec -it mysql8 /bin/bash bash-4.4# mysql -uroot -p123
mysal: [Warning] World-writable config file /etc/mysql/conf.d/my.cnf is ignored. mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR
2002 (HY000): Can't connect to local MySQL server through socket /var/run/mysqld/mysqld.sock* (2)

解决方法：

检查 MySQL 日志：MySQL 服务器的错误日志文件通常位于 MySQL 数据目录中的 .err 文件中。你可以查看这个文件来了解 MySQL 在启动过程中是否遇到了问题。

cat /var/log/mysql/error.log

尝试手动启动 MySQL：在容器内部，你可以尝试手动启动 MySQL 服务器以查看是否有任何错误消息。

mysqld --user=mysql --console

检查文件权限：确保 MySQL 数据目录及其子目录的权限设置正确，MySQL 进程应该具有足够的权限读写数据目录。

ls -l /var/lib/mysql

小ྂ渊ྂ

关注

38
点赞
踩
34

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7容器升级8.3

在这种模式下，MySQL 将所有表名和数据库名视为大小写敏感的，但在创建新表时将其存储为小写。这意味着 MySQL 在比较表名和数据库名时是区分大小写的，但在创建新表时，如果使用了大写字母，MySQL 将会自动将其转换为小写。这意味着不管输入的表名是大写、小写还是混合大小写，MySQL 都会将它们转换为小写存储，并且在进行表名匹配时不区分大小写。：在这种模式下，表名和数据库名的存储方式是操作系统的默认方式。参数设置为 0 或 2。：如果可能的话，修改数据库中的模式名，确保它们全部小写，以符合。
复制链接

扫一扫