mysql大小写敏感设置

一.mysql大小写敏感设置

1. 停止MySQL服务器

[root@localhost ~]# systemctl stop mysqld

2. 修改mysql配置，添加lower_case_table_names=1

[root@localhost ~]# 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]…省略# default-authentication-plugin=mysql_native_passwordlower_case_table_names=1datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.socklog-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[root@localhost ~]#

3. 删文件/var/lib/mysql

[root@localhost ~]# rm /var/lib/mysql/* -rf

4. 启动mysql

[root@localhost ~]# systemctl start mysqld

5. 查看MySQL端口是否已经使用，使用netstat -anp 命令查看服务器端口使用情况。

[root@localhost ~]#  netstat -anp | grep 3306

6. 查看MySQL的启动状态

[root@localhost ~]# systemctl status mysqld.service

7. 进入mysql数据库控制台（注意，修改配置并重启mysql后，root账户的密码发生了变化，需要重新获取）

[root@localhost ~]# mysql -u root -pEnter password:ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

查询临时密码进行重新登录

[root@localhost ~]#  cat /var/log/mysqld.log | grep password2020-09-21T01:19:26.618543Z 6 [Note] [MY-010454] [Server] A temporary password is generate            d for root@localhost: =q+l:d3GLC4:[root@localhost ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 15573Server version: 8.0.21

8. 修改root用户密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';Query OK, 0 rows affected (0.03 sec)

刷新权限

mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

9. 设置root访问权限

mysql> create user 'root'@'%' identified with mysql_native_password by 'P@ssw0rd';Query OK, 0 rows affected (0.00 sec)mysql> grant all privileges on *.* to 'root'@'%' with grant option;Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

10. 修改root用户密码的有效期

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd' PASSWORD EXPIRE NEVER;Query OK, 0 rows affected (0.01 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)

11. 确认远程访问用户与权限

mysql> select host,user from mysql.user;+-----------+------------------+| host      | user             |+-----------+------------------+| %         | root             || localhost | mysql.infoschema || localhost | mysql.session    || localhost | mysql.sys        || localhost | root             |+-----------+------------------+5 rows in set (0.00 sec)mysql>

12. 查看大小写敏感设置

mysql> show variables like '%low%';+----------------------------+-----------------------------------+| Variable_name              | Value |+----------------------------+-----------------------------------+| log_slow_admin_statements  | OFF   || log_slow_extra             | OFF   || log_slow_slave_statements  | OFF   || low_priority_updates       | OFF   || lower_case_file_system     | OFF   || lower_case_table_names     | 1     || max_allowed_packet         | 67108864                          || mysqlx_max_allowed_packet  | 67108864                          || slave_allow_batching       | OFF   || slave_max_allowed_packet   | 1073741824                        || slow_launch_time           | 2     || slow_query_log             | OFF   || slow_query_log_file        | /var/lib/mysql/localhost-slow.log || transaction_allow_batching | OFF   |+----------------------------+-----------------------------------+14 rows in set (0.01 sec)mysql>

来源地址：https://blog.csdn.net/weixin_42589681/article/details/129747850