mysql维护

mysql
- 字
2024-06-22

登录※

-u用户
-p密码

-S socket

-h 主机ip

-e 非交互式
# mysql登录示例：
mysql -uroot -p -S /tmp/mysql.sock -P3306 -hlocalhost -e "show
databases;

可以自动补全mysql的客户端：mycli※

安装※

在 Debian/Ubuntu 上安装

sudo apt update
sudo apt install python3-pip
pip3 install mycli --break-system-packages

在 RedHat/Fedora 上安装

sudo yum install python3-pip
pip3 install mycli

使用 MyCLI

mycli -u root -p -h 127.0.0.1

自带的补全表，库方案※

vim ~/.inputrc
# Enable auto-completion in MySQL client
"\e[B": history-search-forward
"\e[A": history-search-backward
TAB: menu-complete

用户管理※

查看当前登录用户

select user();

查看用户表的表结构

mysql> desc mysql.user;
===========或=============
mysql> use mysql
mysql> desc user;

查询用户

mysql> select user,host from mysql.user;

查询更多列（用户列，主机列，密码列，插件列）

mysql> select user,host,authentication_string,plugin from mysql.user;

创建或更新用户※

-- 为 'msl' 创建或更新从 'localhost' 登录的权限
CREATE USER 'msl'@'localhost' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'msl'@'localhost' WITH GRANT OPTION;

-- 为 'msl' 创建或更新从任何主机登录的权限
CREATE USER 'msl'@'%' IDENTIFIED BY 'admin';
GRANT ALL PRIVILEGES ON *.* TO 'msl'@'%' WITH GRANT OPTION;

-- 刷新权限
FLUSH PRIVILEGES;

mysql新旧版差异※

mysql8.0之前可以授权的时候同事添加用户，8.0以后需要创建完用户之后再授权

密码插件：

  • MySQL 8.0 之前默认使用 mysql_native_password 插件。
  • MySQL 8.0 之后默认使用 caching_sha2_password 插件，提供更好的安全性。
CREATE USER 'msl'@'10.0.0.%' IDENTIFIED WITH mysql_native_password BY '123';
alter user msl@'10.0.0.%' identified with mysql_native_password by
'123';

锁定用户※

锁定和解锁用户（不确定是否有用的账户，可以先锁定）

mysql> alter user oldgirl@'10.0.0.%' ACCOUNT LOCK; # 锁定
mysql> select user,host,account_locked from mysql.user; # 查询
+------------------+-----------+----------------+
| user | host | account_locked |
+------------------+-----------+----------------+
| oldgirl | 10.0.0.% | Y | #account_locked列为Y表示锁
定。
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| root | localhost | N |
+------------------+-----------+----------------+
mysql> alter user oldgirl@'10.0.0.%' ACCOUNT UNLOCK; # 解锁

删除用户※

mysql> drop user msl@'10.0.0.%';

管理MySQL用户密码※

Linux命令行设置管理员root密码※

首次登录设置密码
mysqladmin password 'admin'

# 修改密码
mysqladmin -uroot -proot password 'admin'
# .SQL语句修改法（适合8.0）
mysql> alter user root@'localhost' identified by 'admin';
mysql> flush privileges;

# 通过直接修改mysql.user表用户记录修改密码方法（适合5.7）
mysql> update mysql.user set authentication_string=PASSWORD('admin') \
where user='root' and host='localhost';
mysql> flush privileges;
# set password语句修改密码法（适合5.7）
mysql> set password for 'oldboy'@'localhost'=PASSWORD('admin');
mysql> flush privileges;

忘记密码

#方法1：
# 1.首先停止MySQL服务。
systemctl stop mysqld
netstat -lntup|grep 330
# 2.带参数启动mysql服务。
mysqld_safe --skip-grant-tables --skip-networking &
###--skip-grant-tables（忽略授权登录验证）和--skip-networking（忽略网络）
# 3.无需密码登录MySQL。
mysql
# 4.修改root密码为新密码。
mysql> flush privileges; #<==此命令必须先执行。
mysql> alter user root@'localhost' identified by 'newpass';
mysql> quit
# 5.重启mysql
killall mysqld
systemctl start mysqld
netstat -lntup|grep 330
# 6.登录验证
mysql -uroot -poldboy123
#方法2：
# 1.编辑/etc/my.cnf，加如下参数，直接重启数据库。
[mysqld]
--skip-networking
--skip-grant-tables
# 2.直接正常重启数据库。
systemctl restart mysqld
# 3.无需密码登录MySQL。
mysql
# 4.修改root密码为新密码。
mysql> flush privileges; #<==此命令必须先执行。
mysql> alter user root@'localhost' identified by 'oldboy123';
mysql> quit
# 5.重启mysql
systemctl restart mysqld

mysql用户权限※

权限类型权限名称说明
数据权限SELECT允许读取数据
INSERT允许插入新数据
UPDATE允许更新已有数据
DELETE允许删除数据
FILE允许读取和写入文件
结构权限CREATE允许创建数据库和表
ALTER允许修改表结构
INDEX允许创建和删除索引
DROP允许删除数据库和表
CREATE TEMPORARY TABLES允许创建临时表
SHOW VIEW允许查看视图的定义
CREATE ROUTINE允许创建存储过程和函数
ALTER ROUTINE允许修改和删除存储过程和函数
EXECUTE允许执行存储过程和函数
CREATE VIEW允许创建视图
EVENT允许创建、修改和删除事件
TRIGGER允许创建和删除触发器
管理权限GRANT允许授予和撤销用户的权限
SUPER允许执行高级管理操作，如终止进程、重置主从复制等
PROCESS允许查看正在执行的查询
RELOAD允许执行刷新操作，重新加载授权表、日志等
SHUTDOWN允许关闭数据库服务器
SHOW DATABASES允许查看数据库列表
LOCK TABLES允许锁定表
REFERENCES允许在外键约束中使用
REPLICATION CLIENT允许查询主从复制相关状态
REPLICATION SLAVE允许配置从服务器复制
CREATE USER允许创建、修改和删除用户

授权

grant insert,select on mysql.user to zhangsan@'10.0.0.0/24';

撤销授权

REVOKE INSERmT ON *.* FROM 'jeffrey'@'localhost';

用户资源管理※

密码过期时间※

select @@default_password_lifetime;#查看默认密码过期时间
SET PERSIST default_password_lifetime = 180;#设置密码过期时间（天）
SET PERSIST default_password_lifetime = 0;
CREATE USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;#创建用户的同事·	
ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;#修改现有用户的密码过期时间
CREATE USER 'root'@'localhost' PASSWORD EXPIRE NEVER;#创建用户并设置密码永不过期
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;#修改现有用户的密码为永不过期

密码重用

password_history=6#用户在更改密码时，不能使用最近 6 次使用过的密码
password_reuse_interval=365#用户在更改密码时，不能使用在过去 365 天内使用过的密码

连接资源限制※

with
MAX_QUERIES_PER_HOUR count # 限制用户每小时执行查询的次数，以防止过度使用查询资源。
MAX_UPDATES_PER_HOUR count # 限制用户每小时执行更新操作的次数，以防止过度使用写操作资源。
MAX_CONNECTIONS_PER_HOUR 2000; # 限制用户每小时创建新连接的次数，以防止连接资源滥用。
MAX_USER_CONNECTIONS count # 限制用户同时打开的连接数，以防止单个用户占用过多的连接资源。
目录