MySQL 8.0 大小写敏感参数修改

发表于： 2022-09-02 22:00:33
分类：数据库文章分享
浏览：浏览(81)

概述

MySQL大小写敏感由lower_case_table_names参数控制，默认为0表示大小写敏感，MySQL 8.0 之前版本，直接在/etc/my.cnf加入lower_case_table_names = 1参数重启MySQL即可修改成功，但MySQL 8.0以后此参数只能在数据库软件第一次启动时修改，之后无法修改，如果强行修改启动会报错。

参数默认值

lower_case_table_names参数默认值为0，表示大小写敏感，查看默认参数值，测试大小写敏感。

[root@node5 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3575
Server version: 8.0.29 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| employees          |
| information_schema |
| mysql              |
| performance_schema |
| platform           |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> use TEST;
ERROR 1049 (42000): Unknown database 'TEST'

强行修改报错

1.通过set global修改报错

mysql> set global lower_case_table_names=1;
ERROR 1238 (HY000): Variable 'lower_case_table_names' is a read only variable

2.修改/etc/my.cnf文件重启报错

[root@node5 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/opt/mysql/data
socket=/opt/mysql/mysql.sock

server_id=3
log_bin = /opt/mysql/logs/binlogs/server3
slow_query_log = 1
slow_query_log_file = /opt/mysql/logs/mysql-slow.log
long_query_time = 1

log-error=/opt/mysql/logs/mysqld.log
pid-file=/opt/mysql/mysqld.pid
lower_case_table_names = 1

[root@node5 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2022-08-18 10:02:58 CST; 4min 5s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 23914 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 23943 (mysqld)
   Status: "Server is operational"
    Tasks: 40
   CGroup: /system.slice/mysqld.service
           └─23943 /usr/sbin/mysqld

Aug 18 10:02:55 node5 systemd[1]: Starting MySQL Server...
Aug 18 10:02:58 node5 systemd[1]: Started MySQL Server.
[root@node5 ~]# systemctl restart mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
[root@node5 ~]# journalctl -xe
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun starting up.
Aug 18 10:02:58 node5 systemd[1]: Started MySQL Server.
-- Subject: Unit mysqld.service has finished start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has finished starting up.
-- 
-- The start-up result is done.
Aug 18 10:02:58 node5 polkitd[693]: Unregistered Authentication Agent for unix-process:23907:75005136 (system bus name :1.3017, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US
Aug 18 10:07:09 node5 polkitd[693]: Registered Authentication Agent for unix-process:24380:75030531 (system bus name :1.3018 [/usr/bin/pkttyagent --notify-fd 5 --fallback], object path /org/freedesktop
Aug 18 10:07:09 node5 systemd[1]: Stopping MySQL Server...
-- Subject: Unit mysqld.service has begun shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun shutting down.
Aug 18 10:07:18 node5 systemd[1]: Stopped MySQL Server.
-- Subject: Unit mysqld.service has finished shutting down
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has finished shutting down.
Aug 18 10:07:18 node5 systemd[1]: Starting MySQL Server...
-- Subject: Unit mysqld.service has begun start-up
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has begun starting up.
Aug 18 10:07:20 node5 systemd[1]: mysqld.service: main process exited, code=exited, status=1/FAILURE
Aug 18 10:07:20 node5 systemd[1]: Failed to start MySQL Server.
-- Subject: Unit mysqld.service has failed
-- Defined-By: systemd
-- Support: http://lists.freedesktop.org/mailman/listinfo/systemd-devel
-- 
-- Unit mysqld.service has failed.
-- 
-- The result is failed.
Aug 18 10:07:20 node5 systemd[1]: Unit mysqld.service entered failed state.
Aug 18 10:07:20 node5 systemd[1]: mysqld.service failed.
Aug 18 10:07:20 node5 polkitd[693]: Unregistered Authentication Agent for unix-process:24380:75030531 (system bus name :1.3018, object path /org/freedesktop/PolicyKit1/AuthenticationAgent, locale en_US

正确修改方法

必须在安装后第一次初始化启动前修改此参数，在另一台服务器重新安装MySQL8.0并在第一次启动前修改/etc/my.cnf配置，添加lower_case_table_names = 1

[root@node4 ~]# ls
anaconda-ks.cfg                                         mysql-community-common-8.0.29-1.el7.x86_64.rpm           mysql-community-libs-8.0.29-1.el7.x86_64.rpm          rlwrap-0.43-2.el7.aarch64.rpm
GbasePrepare.sh                                         mysql-community-debuginfo-8.0.29-1.el7.x86_64.rpm        mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm   rlwrap-0.45.2-1.el7.x86_64.rpm
mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar                mysql-community-devel-8.0.29-1.el7.x86_64.rpm            mysql-community-server-8.0.29-1.el7.x86_64.rpm        test_db-master
mysql-community-client-8.0.29-1.el7.x86_64.rpm          mysql-community-embedded-compat-8.0.29-1.el7.x86_64.rpm  mysql-community-server-debug-8.0.29-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm  mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm   mysql-community-test-8.0.29-1.el7.x86_64.rpm
[root@node4 ~]# rpm -ivh mysql-community-common-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-common-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-8.0.29-1.e################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-icu-data-files-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-icu-data-files-8.################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-client-plugins-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-plugins-8.################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-libs-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-libs-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-8.0.29-1.el7################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-libs-compat-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-compat-8.0.2################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-client-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-client-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-8.0.29-1.e################################# [100%]
[root@node4 ~]# rpm -ivh mysql-community-server-8.0.29-1.el7.x86_64.rpm 
warning: mysql-community-server-8.0.29-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-8.0.29-1.e################################# [100%]
[root@node4 ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names=1

[root@node4 ~]# systemctl start mysqld
[root@node4 ~]# cat /var/log/mysqld.log 

2022-08-18T03:06:15.531216Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.29) initializing of server in progress as process 31222
2022-08-18T03:06:15.545897Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-18T03:06:16.904154Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-18T03:06:18.449899Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Nek)ky(T>4Ga
2022-08-18T03:06:21.729059Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.29) starting as process 31274
2022-08-18T03:06:21.738337Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-08-18T03:06:22.296229Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-08-18T03:06:22.731074Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-08-18T03:06:22.731138Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-08-18T03:06:22.763112Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.29'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-08-18T03:06:22.763211Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2022-08-18T03:06:30.645162Z 8 [Warning] [MY-010055] [Server] IP address '192.168.59.33' could not be resolved: Name or service not known
2022-08-18T03:06:30.730208Z 9 [Warning] [MY-010055] [Server] IP address '192.168.59.30' could not be resolved: Name or service not known

[root@node4 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> alter user 'root'@'localhost' identified by 'xxxxxx';
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%case%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| lower_case_file_system             | OFF   |
| lower_case_table_names             | 1     |
| validate_password.mixed_case_count | 1     |
+------------------------------------+-------+
3 rows in set (0.01 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> use TEST;
Database changed

总结

MySQL8对大小写敏感的设置要求变得严格，在软件第一次初始化启动时才能设置，之后不能再做修改，除非删除重建。

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论