MySQL的管理

1、用户管理

MySQL用户的作用:
1）登陆MySQL数据库
2）管理数据库对象

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
MySQL用户管理：
1）创建用户：create user
2）删除用户：delete user，drop user
3）修改用户：update

用户的定义：
1) username@'主机域'  这是一个完整的用户
mysql> select user,host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| root | 127.0.0.1   |
|      | db02        |
| root | db02        |
| root | localhost   |
+------+-------------+

2）主机域：可以理解为是MySQL登陆的白名单

3）主机域格式：
#这3个常用
10.0.0.5%   10.0.0.50~10.0.0.59 10.0.0.5
localhost
10.0.0.%

10.0.0.51
10.0.%.%
10.%.%.%
%
db01
10.0.0.51/255.255.255.0

mysql> grant all on *.* to test1@'10.0.0.0/24' identified by '123';
mysql> grant all on *.* to test2@'10.0.0.0/255.255.255.0' identified by '123';
#注意：使用IP授权的用户，登陆时，都要指定IP去连接
mysql> grant all on *.* to asm@'10.0.0.%' identified by '123';
mysql> select user,host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| asm  | 10.0.0.%    |
| test | 10.0.0.0/24 |
| root | 127.0.0.1   |
|      | db02        |
| root | db02        |
| root | localhost   |
+------+-------------+
[root@db02 ~]# mysql -uasm -p123 -h10.0.0.52


#命令前面加空格，历史纪录不保存
vim /etc/profile.d/xxxx.sh
HISTCONTROL=ignorespace

#对用户的管理就是 
增
删
改
查

增：创建用户

1
2
3
4
5
6
7
8
#符合SQL92标准的，先创建，在grant
create user asm@'localhost' identified by '123';
grant all on *.* to asm@'localhost';

#创建一个不存在的用户并授权
grant all on *.* to asm@'localhost' identified by '123';

#insert into 这个相当于编辑/etc/passwd文件去增加用户

删：删除用户

1
2
3
4
5
6
mysql> drop user asm@'localhost';   #相当于userdel
#查看用户已被删除
mysql> select user,host from mysql.user;  


mysql> delete from mysql.user where user='asm' and host='localhost'   #相当于vim /etc/passwd

改：修改用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
改密码  改权限  经常用来改密码

#改密码
方法一
命令提示行中修改
[root@db04 ~]#mysqladmin -u用户  -p'旧密码' password '新密码'

方法二  只有5.7以上版本专用
mysql> alter asm@'localhost' identified by '123';

方法三  更改当前用户的密码 PASSWORD函数加密123
mysql> set password=PASSWORD('123');    

方法四   只有使用updatae修改用户密码时，才需要执行flush privileges
#MYSQL5.6
mysql> updata mysql.user set password=PASSWORD('123') where user='asm' and host='localhost';
mysql> flush privileges   #刷新授权表   //很危险的命令，是全局的SQL语句

#MYSQL5.7
mysql> updata mysql.user set authentication_string=PASSWORD('123') where user='asm' and host='localhost';
mysql> flush privileges  

方法五
mysql> grant all on *.* to asm@'localhost' identified by '123';

查：用户的查询

1
2
3
4
5
6
7
mysql> select user,host from mysql.user;  相当于cat /etc/passwd

mysql> show grant for asm@'localhost'

mysql> selcet * from mysql.user\G

\G  格式化输出

2、权限管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
#全部权限

INSERT,SELECT, 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  

#这两条命令都是授权，没什么区别
mysql> grant   all  on   *.*   to  test@'10.0.0.%' identified by '123';
mysql> grant   all privileges  on   *.*   to  test@'10.0.0.%' identified by '123';
             权限           作用对象        归属                       密码

想要用户有grant权限 ，需要在授权命令的后面加：with grant option

#授权用户
mysql> grant all on *.* to aaa@'10.0.0.%' identified by '123';
#查看
mysql> show grants for aaa@'10.0.0.%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for aaa@10.0.0.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'aaa'@'10.0.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
+--------------------------------------------------------------------------------------------------------------------+


#作用对象分解
*.* [当前MySQL实例中所有库下的所有表]
wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）]#常用的级别
wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]
单字段级别


#给开发开权限（原始人版）
INSERT,SELECT, UPDATE, DELETE
#给开发开权限（规范版本）
INSERT,SELECT, UPDATE



#单字段级别：脱敏 ：脱离敏感信息
比如一个表里面有用户的银行卡号，开发使用这个表，我不想让他看到表里面的银行卡信息，这个时候可以使用脱敏
#只给开发查看user host字段的信息，其他的不能看
mysql> grant insert,select(user,host),update,delete on mysql.user to dev@'localhost' identified by '123';

#测试
[root@db02 ~]# mysql -udev -p123
mysql> select user,host from mysql.user;
+------+-------------+
| user | host        |
+------+-------------+
| aaa  | 10.0.0.%    |
| root | db02        |
| dev  | localhost   |
| root | localhost   |
+------+-------------+
8 rows in set (0.00 sec)
#查看passwprd字段，报错没有权限
mysql> select user,host,password from mysql.user;
ERROR 1143 (42000): SELECT command denied to user 'dev'@'localhost' for column 'password' in table 'user'
1
2
3
4
5
6
7
8
9
10
开发人员说：请给我开一个用户
沟通：
填写审批工单或者发邮件让老板同意，有一个审批流程  #开发 ---开发领导审批 ---运维总监---你
1、用户名
2、从哪里连接过来
3、密码要求
4、需要对哪些库、表进行操作

开发说：你把root用户给我呗？  
把利弊给运维总监说，如果他同意，你就把用户名密码，发给运维总监，让总监发给他，你不要发

3、mysql的连接管理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
[root@db01 ~]# mysql
-u：指定用户
-p：指定密码
-h：指定主机
-S：指定socket
-P：指定端口
-e：免交互指定MySQL的SQL语句


连接工具
2)第三方的连接工具
sqlyog、navicat
应用程序连接MySQL
注意：需要加载对应语言程序的API

2.连接方式
1) socket连接
mysql -uroot -poldboy123 -S/application/mysql/tmp/mysql.sock
mysql -uroot -poldboy123
2) TCP/IP
mysql -uroot -poldboy123 -h10.0.0.51 -P3306

数据库在生产环境中不给公网IP,不会暴露在公网上，eth0会down掉，这个时候想要用第三方工具连接数据库，就要端口转发，在生产环境中会有一台机器暴露在公网的，类似于负载均衡的机器，就从负载均衡的机器跳过去。

image-20240809152949694

image-20240809153134945

4、MYSQL启动关闭流程

image-20240809114555924

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
/etc/init.d/mysqld start ------> mysqld_safe ------> mysqld

# 启动
/etc/init.d/mysqld start
systemctl mysqld start
mysqld_safe --defaults-file=/etcc/my.cnf &

# 停止
/etc/init.d/mysqld stop
systemctl mysqld stop
[root@db01 ~]# mysqladmin -uroot -p123 shutdown


### 以下停止方式别用
kill -9 pid ?
killall mysqld ?
pkill mysqld ?
pkill -9 mysqld ?
1
data buffer page 只记录变化过程

5、MYSQL启动配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
预编译：MySQL配置 datadir=/app/mysql/data
配置文件：MySQL配置 datadir=/app/tmp/data
命令行：mysqld_safe --datadir=/app/xxx/data

配置文件读取顺序：
/etc/my.cnf
/etc/mysql/my.cnf
$basedir/my.cnf（前提是在环境变量中定义了MYSQL_HOME变量）
mysqld_safe --defaults-extra-file=/opt/my.cnf （类似include）
~/.my.cnf   这个是自己写的

## 当MySQL启动时，指定了该参数，那么其他配置文件都不读取
--defaults-file

读取顺序和优先级相反  最后读的会覆盖前面的   当mysql启动时指定了配置文件，其他的都不读取   命令行的优先级最高

#在相同配置情况下，配置生效的优先级
预编译 < 配置文件 （ /etc/my.cnf < /etc/mysql/my.cnf < $besedir/my.cnf < defaults-extra-file < ~/.my.cnf < defaults-file ） < 命令行