Mysql 在 Linux 下更改表名不区分大小写

1 区分大小写

• Linux下的mysql表名数据库名默认区分大小写
  docker下run启动时在最后添加参数

docker run -p 3306:3306 -v /etc/localtime:/etc/localtime:ro --restart=always --name mysql_57 -e MYSQL_ROOT_PASSWORD=root -d mysql:5.7 --lower_case_table_names=1

• 各参数含义：
  0：原样存储在磁盘上，名称比较区分大小写，默认。
  1：以小写形式存储，名称比较不区分大小写。转换为小写的查找。
  2：原样存储在磁盘上，名称比较不区分大小写。转换为小写的查找。适用于不区分大小写的文件系统。

• 如果docker已经启动：

docker exec -it mysql bash
echo "lower_case_table_names=1" >> /etc/mysql/mysql.conf.d/mysqld.cnf
service mysql restart #容器内运行

• windows 下 mysql 重启 (服务名不区分大小写):

net stop MySQL   
net start MySQL

or 5.7

net stop MySQL57   
net start MySQL57

2 无法使用 root 远程登陆的问题

# 允许远程访问
use mysql;update user set host='%' where user='root';flush privileges;
# 查看
select host,user from user;

3 修改列

add|drop|change|modify					
#[add]最后接first或者after ColumName，没有before
#change 可以更改 列名 和 列类型 (只改类型也要把新列名和旧列名写上, 即旧名字写两遍, 只改类型)

4 命令行多行模式 & 纵向模式

1. 多条语句需要执行时，替换分隔符 DELIMITER //，要改回分号执行 DELIMITER ;

2. 在 Mysql 命令行界面使用 \G 可将一列显示为纵向. (必须是大写)

5 修改密码

mysql> set password for root@localhost = password(‘123’);

6 创建用户

mysql> create user 'rose'@'%' IDENTIFIED BY '123456'; // 任意远程主机登陆，通配符%
mysql> create user 'jack'@'localhost' IDENTIFIED BY '123456';  //本地用户

创建用户后，该用户只能连接到数据库服务器，但并没有操作该数据库服务器的权限。

7 授权

mysql> GRANT SELECT, INSERT ON bookdb.usertable TO 'jack'@'%';
mysql> GRANT ALL ON DBname.* TO 'rose'@'localhost' WITH GRANT OPTION;
#   授权后需要重连

8 撤销授权

mysql> REVOKE SELECT ON *.* FROM 'jack'@'%';

9 删除用户

mysql> DROP USER 'usernamexxx'@'hostxxx';

10 设置与更改用户密码

mysql> SET PASSWORD FOR 'usernamexxx'@'hostxxx' = PASSWORD('newpasswordxxx');

11 设置与更改用户密码（新 MySQL）

MySQL 8.0.4权限认证插件服务从 mysql_native_password 改成 caching_sha2_password.可以这样修改密码：

mysql>  ALTER USER 'student'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass123';
or
mysql>  ALTER USER 'student'@'localhost' IDENTIFIED BY 'pass123';

12 查看docker映射mysql的IP地址

其他容器使用mysql容器会用到（容器使用容器）

docker inspect mysql:5.7 |grep "IPAddress"

13 mysql内的变量设置

set [global|session(default)] variable_name=xxx;  #默认是会话有效, 单独设置global可改为全局
set @variable_name=xxx;                   #定义用户变量需要加@
select @variable_name:=xxx;				  #必须使用:=赋值, 因为在SELECT语句中“=”号被占用
#展示mysql系统变量
show variables like "%case%";
select @variable_name;
# 查看mysql版本
select version();

14 查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程

select * from information_schema.processlist where Command != ‘Sleep’ order by Time desc;

15 找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀

select concat('kill ', id, ‘;’) from information_schema.proce sslist where Command != ‘Sleep’ and Time > 300 order by Time desc;

16 HY000

问题: [HY000] Host ‘host-92…*.as13285.net’ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts’

• 方法1: 执行 shell 命令

mysqladmin flush-hosts;
or
mysqladmin -u[username] -p flush-hosts

• 方法2: 执行 sql 命令

FLUSH HOSTS;

• 增大连接数设置

SET GLOBAL max_connect_errors=10000;
set global max_connections = 200;
-- 查看
show variables like "max_connections";
show variables like "max_connect_errors";

http://dev.mysql.com/doc/refman/5.6/en/flush.html

http://dev.mysql.com/doc/refman/5.6/en/mysqladmin.html

17 public key retrieval is not allowed

如果在连接字符串中明确设置了useSSL=false，但仍然收到 “public key retrieval is not allowed” 的错误消息，这可能是由于以下几种情况:

1. MySQL版本较新：从MySQL 8.0.22版本开始，即使在禁用SSL/TLS加密的情况下，仍会执行公钥检索的验证。这是为了提高安全性。因此，即使你明确禁用了SSL/TLS加密（useSSL=false），也会收到公钥检索不允许的错误消息。(刚好我使用的版本是 8.0.26)

2. 连接选项被覆盖：在某些情况下，MySQL连接选项可能会被其他配置或代码覆盖。即使你在连接字符串中设置了useSSL=false，但在其他地方（如代码中的连接参数或MySQL配置文件）设置了SSL/TLS选项，仍然会启用SSL/TLS加密，导致公钥检索错误。

3. MySQL服务器配置：MySQL服务器可能已经配置为要求公钥检索，无论客户端的连接选项如何。在这种情况下，无论你如何设置连接字符串中的参数，都无法绕过服务器的公钥检索要求。

• 解决办法: &allowPublicKeyRetrieval=true

允许在 SSL/TLS 握手过程中从服务器检索公钥. 在MySQL中，当您与服务器建立SSL/TLS加密连接时，客户端会使用其公钥验证服务器的真实性。默认情况下，MySQL要求公钥 存在 并 有效 才能成功连接。但是，在某些情况下，服务器的公钥可能丢失或未验证。

allowPublicKeyRetrieval=true 参数会覆盖默认行为，并允许客户端连接，即使服务器的公钥 无法检索 或 无效。本质上是放宽了 SSL/TLS 握手期间对有效公钥的要求。

18 授予 DROP FUNCTION 权限给用户

在 MySQL 中，Routine（例程）指的是存储过程（Stored Procedure）和函数（Function）。一组经过编译和保存的 SQL 语句集合，可重复使用。

1. 当出现报错: [42000][1227] Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation.

drop function if exists bigint2hex;

[42000][1227] Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
[HY000][1405] Failed to revoke all privileges to dropped routine

报错信息展示: 没有 system_user 权限

原因是 MySQL 在 8.0.16 版本中新增了一个 system_user 帐户类型，我们需要添加权限:

grant system_user on db_name.* to 'username'@'%';
or
grant system_user on *.* to 'root';

SYSTEM_USER: Designate account as system account. Level: Global.

2. 如果是单独账户, 还需要授权 DELETE 权限;

GRANT DELETE, DROP ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

• 两种权限的区别:

  • DROP: Permissible Static Privileges for GRANT and REVOKE
  • SYSTEM_USER: Permissible Dynamic Privileges for GRANT and REVOKE

3. 其他原因: function 还存在其他依赖, 需要先解除依赖:

• 查看 routine 信息

select * from information_schema.routines where ROUTINES.ROUTINE_NAME like 'func_%';
or
show function status like 'func_%';

• 根据查询结果, 再依次解开权限.

(最后发现错误的原因是, 函数的 definer 是 root@% 所以普通用户没办法删除)

Reference: https://dev.mysql.com/doc/refman/8.0/en/grant.html

19 SYSTEM_USER 权限

MySQL从8.0.16版本开始，MySQL利用system_user权限来区分普通用户和系统用户:

1. 拥有system_user权限的账号，是系统账号
2. 没有system_user权限的账号，是普通账号

系统账号可以修改系统账号自身和普通账号；
普通账号只能修改普通账号自身。