lower_case_table_names 参数设置

在启动 MySQL 容器时，使用相关指令：

[root@localhost ~]# docker run ...
WARNING: IPv4 forwarding is disabled. Networking will not work.
6dc8fa34ff7...e3ed12a1b2f6e0edbc8e6

看着样子应该是启动成功了，但是通过 docker ps 指令查看，发现并没有刚刚启动的 MySQL 容器；

那让我们看一下日志，排查一下问题，docker logs 6dc8fa34ff7...e3ed12a1b2f6e0edbc8e6：

发现问题所在：

Different lower_case_table_names settings for server ('0') and data dictionary ('1').

根据字面意思理解一下就是：

服务器（'0'）和数据字典（'1'）的不同 lower_case_table_names 设置。

具体的话可以看到 官方文档：

If set to 0, table names are stored as specified and comparisons are case-sensitive. If set to 1, table names are stored in lowercase on disk and comparisons are not case-sensitive. If set to 2, table names are stored as given but compared in lowercase. This option also applies to database names and table aliases. For additional details, see Section 9.2.3, “Identifier Case Sensitivity”.

The default value of this variable is platform-dependent (see lower_case_file_system). On Linux and other Unix-like systems, the default is 0. On Windows the default value is 1. On macOS, the default value is 2. On Linux (and other Unix-like systems), setting the value to 2 is not supported; the server forces the value to 0 instead.

You should not set lower_case_table_names to 0 if you are running MySQL on a system where the data directory resides on a case-insensitive file system (such as on Windows or macOS). It is an unsupported combination that could result in a hang condition when running an INSERT INTO ... SELECT ... FROM tbl_name operation with the wrong tbl_name lettercase. With MyISAM, accessing table names using different lettercases could cause index corruption.

An error message is printed and the server exits if you attempt to start the server with --lower_case_table_names=0 on a case-insensitive file system.

The setting of this variable affects the behavior of replication filtering options with regard to case sensitivity. For more information, see Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”.

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are determined by the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

It is therefore necessary to configure lower_case_table_names to the desired setting before initializing the server.

正如官方文档所说，On Linux and other Unix-like systems, the default is 0.，我用的系统是 Linux 的，因此 lower_case_table_names 默认值是 0，因此我需要对其进行改变，将其与数据字典一致，即 lower_case_table_names=1；

TIP

lower_case_table_names 该参数为静态，可设置为0、1、2。

• 0 -- 大小写敏感。（Unix，Linux 默认）；
• 1 -- 大小写不敏感。（Windows 默认）；
• 2 -- 大小写不敏感。（OS X 默认）；

先将旧的容器移除：

docker rm 6dc8fa34ff7...e3ed12a1b2f6e0edbc8e6

然后再重新启动一遍容器：

docker run -d --name sid10t-mysql \
-v $(pwd)/mysql/data:/var/lib/mysql \
-v $(pwd)/mysql/conf:/etc/mysql \
-v $(pwd)/mysql/log:/var/log/mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=pwd \
mysql --lower-case-table-names=1

测试一下连接，连接成功，搞定！

IPv4 forwarding is disabled 参数设置

本以为解决上面的问题就大功告成了，可是天不如人愿，当我使用远程连接时，结果如下：

当使用 pymysql 连接时也被告知连接超时：

OperationalError: (2003, "Can't connect to MySQL server on '10.20.152.70' (timed out)")
复制代码

那第一时间就是怀疑 3306 是否没有对外开放，因此直接防火墙开放 3306 端口：

firewall-cmd --zone=public --add-port=3306/tcp --permanent
复制代码

但是却发现 3306 端口一直是开放状态的：

那就查看其占用情况，是否只允许本地访问：

发现也不是这个问题；

突然想起，刚刚启动容器的时候，好像是弹出了一个警告：

WARNING: IPv4 forwarding is disabled. Networking will not work.

查阅资料后发现，是没有开启 IPv4 转发，网桥配置完后，需要开启转发，不然容器启动后，就会没有网络，

因此进行如下操作即可：

vim /etc/sysctl.conf

或者

vi /usr/lib/sysctl.d/00-system.conf

添加如下代码：

net.ipv4.ip_forward=1

重启 network 服务：

systemctl restart network

查看是否修改成功：

sysctl net.ipv4.ip_forward

完成之后重启新的 MySQL 容器，再使用远程连接就可以连上了