1.体系结构
1.1 TCP/IP 方式（远程、本地）
root@999cfd37fd68:/# mysql -uroot -p
Enter password:
1.2 查看连接线程基本情况
mysql> show processlist;
2.用户、权限管理
2.1 用户
用户名@'白名单'
白名单支持的方式？
wordpress@'10.0.0.%'
wordpress@'%'
wordpress@'10.0.0.200'
wordpress@'localhost'
wordpress@'db02'
wordpress@'10.0.0.5%'
wordpress@'10.0.0.0/255.255.254.0'
增：
mysql> create user summertest@'10.0.0.%' identified by '123';
解释：创建一个用户为summertest@ip 密码为123
查：
mysql> select user ,host ,authentication_string from mysql.user;
改：
mysql> alert user summertest@'10.10.0.%' identidied by '456';
解释：修改用户为summertest@ip 密码为456
删：
mysql> drop user summertest@'10.10.0.%';
2.2 权限
2.2.1 常见的权限操作
mysql> grant select on ruyiadmin.* summertest@'183.209.110.173';
解释：给某个用户授权只有某个数据库的只有查询权限
mysql> grant all privileges on *.* summertest@'183.209.110.173';
解释：给某个用户授权所有的权限
mysql> show grants for summertest@'183.209.110.173';
解释：查询某个用户的授权
mysql> revoke all privileges on *.* from 'summertest'@'183.209.110.173';
解释：删除某一个的权限
mysql> GRANT SELECT, UPDATE ON ruyiadmin.* TO 'summertest'@'183.209.110.173';
解释：修改权限，从之前的SELECT 增加一个Update
2.2.2 常见的权限介绍
ALL:
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
ALL : 以上所有权限，一般是普通管理员拥有的
with grant option：超级管理员才具备的，给别的用户授权的功能
2.2.3 MysqlAdmin 修改密码
- 隐藏密码的修改操作
mysqladmin -uroot -p password
[root@VM-4-8-centos ~]# mysqladmin -uroot -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
2.2.4 通过修改配置文件，来修改密码
[root@VM-4-8-centos etc]# cat /etc/mysql_3307.cnf
[mysqld]
skip-grant-tables
port=3307
user=mysql
basedir=/opt/mysql/
datadir=/linux0224/mysql_3307/
socket=/linux0224/mysql_3307/mysql.sock
log_error=/linux0224/mysql_3307/mysql.log
-----------------------------------------------
[root@VM-4-8-centos etc]# bash /linux0224/3307.sh stop
Stoping MySQL...
[root@VM-4-8-centos etc]# bash /linux0224/3307.sh start
Starting MySQL...
-----------------------------------------------
[root@VM-4-8-centos etc]# mysql -S /linux0224/mysql_3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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> exit
Bye
2.2.5 通过命令行，来修改密码
- 原启动配置文件
[root@VM-4-8-centos etc]# ps -ef | grep 'mysql'
root 10573 1 0 23:51 pts/4 00:00:00 /bin/sh /opt/mysql/bin//mysqld_safe --defaults-file=/etc/mysql_3307.cnf --pid-file=/linux0224/mysql_3307/mysqld_3307.pid
mysql 10738 10573 0 23:51 pts/4 00:00:00 /opt/mysql/bin/mysqld --defaults-file=/etc/mysql_3307.cnf --basedir=/opt/mysql/ --datadir=/linux0224/mysql_3307 --plugin-dir=/opt/mysql//lib/plugin --user=mysql --log-error=/linux0224/mysql_3307/mysql.log --pid-file=/linux0224/mysql_3307/mysqld_3307.pid --socket=/linux0224/mysql_3307/mysql.sock --port=3307
/opt/mysql/bin/mysqld_safe --defaults-file=/etc/mysql_3307.cnf --pid-file=/linux0224/mysql_3307/mysqld_3307.pid --skip-grant-tables &
- 修改后启动配置文件,带有 --skip-grant-tables 标识
[root@VM-4-8-centos etc]# ps -ef | grep 'mysql'
root 22413 9248 0 00:10 pts/4 00:00:00 /bin/sh /opt/mysql/bin/mysqld_safe --defaults-file=/etc/mysql_3307.cnf --pid-file=/linux0224/mysql_3307/mysqld_3307.pid --skip-grant-tables
mysql 22578 22413 1 00:10 pts/4 00:00:00 /opt/mysql/bin/mysqld --defaults-file=/etc/mysql_3307.cnf --basedir=/opt/mysql/ --datadir=/linux0224/mysql_3307 --plugin-dir=/opt/mysql//lib/plugin --user=mysql --skip-grant-tables --log-error=/linux0224/mysql_3307/mysql.log --pid-file=/linux0224/mysql_3307/mysqld_3307.pid --socket=/linux0224/mysql_3307/mysql.sock --port=3307
[root@VM-4-8-centos etc]# mysql -S /linux0224/mysql_3307/mysql.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
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>
- 重启，让需要再重新输入密码
[root@VM-4-8-centos etc]# bash /linux0224/3307.sh stop
Stoping MySQL...
2024-09-02T16:13:43.322772Z mysqld_safe mysqld from pid file /linux0224/mysql_3307/mysqld_3307.pid ended
[1]+ Done /opt/mysql/bin/mysqld_safe --defaults-file=/etc/mysql_3307.cnf --pid-file=/linux0224/mysql_3307/mysqld_3307.pid --skip-grant-tables
[root@VM-4-8-centos etc]# bash /linux0224/3307.sh start
Starting MySQL...
[root@VM-4-8-centos etc]# mysql -S /linux0224/mysql_3307/mysql.sock
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@VM-4-8-centos etc]#
