Linux上安装MySQL（glibc8.0版）、mysql管理和安全用户角色权限

一、安装及配置mysql

1.下载

官网地址：MySQL :: Download MySQL Community Server

点击圈出来的按钮可以选择需要的版本，这里选择安装的是8.0.33版本的mysql

选好版本和操作系统，在下面的下载列表里面选择glibc

点击下载即可。

2.环境准备

1.清空/etc/目录下的my.cnf

[root@localhost ~]# rm -rf /etc/my.cnf

2.删除mariadb

[root@localhost ~]# yum -y remove mariadb

3.清空带有mysql的所有文件

[root@localhost ~]# find / -name "*mysql*" -exec rm -rf {} \;

4.依赖库

查看有没有安装mysql依赖库libaio，没有就安装:yum -y install libaio

[root@localhost ~]# yum list installed |grep libaio
libaio.x86_64                         0.3.109-13.el7                   @anaconda

3.解压

1.解压mysql压缩包

[root@localhost ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar

2.解压查看

[root@localhost ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar
[root@localhost ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

3.将项目文件移动到/usr/local/mysql/

[root@localhost ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/

4.配置

1.创建用户

[root@localhost ~]# useradd -r -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=997(mysql) gid=995(mysql) 组=995(mysql)

2.创建一个文件

[root@localhost ~]# mkdir /usr/local/mysql/mysql-files

3.修改权限

[root@localhost ~]# chown mysql:mysql /usr/local/mysql/mysql-files
[root@localhost ~]# chmod 750 /usr/local/mysql/mysql-files/

4.初始化数据库

[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
2024-08-05T02:12:23.829793Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 3849
2024-08-05T02:12:23.841538Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-05T02:12:24.709625Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-05T02:12:25.810204Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: v,es=69BKU25

最后一行‘root@localhost:’后面是初始密码

5.判断是否初始化成功

[root@localhost ~]# ls /usr/local/mysql/

出现data表示初始化成功

6.设置ssl安全加密连接

[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# ls ./bin/*ssl*
./bin/mysql_ssl_rsa_setup

[root@localhost mysql]# ./bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
[root@localhost mysql]# ls ./data/
auto.cnf         #ib_16384_0.dblwr  #innodb_temp        public_key.pem   undo_002
ca-key.pem       #ib_16384_1.dblwr  mysql               server-cert.pem
ca.pem           ib_buffer_pool     mysql.ibd           server-key.pem
client-cert.pem  ibdata1            performance_schema  sys
client-key.pem   #innodb_redo       private_key.pem     undo_001

7.其他配置

[root@localhost ~]# ls /usr/local/mysql/support-files/
mysqld_multi.server  mysql-log-rotate  mysql.server
[root@localhost ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8[root@localhost ~]# ls /etc/init.d/mysql8 
/etc/init.d/mysql8

8.环境路径配置

[root@localhost ~]# vim /etc/profile
...
export PATH=$PANTH:/usr/local/mysql/
[root@localhost ~]# source /etc/profile

5.启动服务

[root@localhost ~]# cd /usr/local/mysql/
[root@localhost mysql]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
. SUCCESS! 

二、mysql管理

1.连接数据库

[root@localhost mysql]# mysql -uroot -pv,es=69BKU25

2.设置密码

mysql> set password='123';
Query OK, 0 rows affected (0.00 sec)

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

3.修改登录权限

mysql> use mysql;    #选择数据库

mysql> update user set host="%" where user='root';   #host设置为%是允许远程登录
mysql> select host,user from mysql.user

4.新创建一个用户并设置权限和密码

mysql> create user 'qingtang'@'%' identified by 'Qing_tang123';
mysql> select host,user from mysql.user;

给创建的用户所有的权限

grant all on *.* to 'qingtang'

5.创建库

mysql> create database if not exists test;

6.创建表

mysql> use test;
mysql> create table user(
    -> id int primary key,
    -> username varchar(45) not null,
    -> password varchar(45) not null
    -> );

7.添加数据

mysql> insert into user values(1,"zhangsan","123");
Query OK, 1 row affected (0.02 sec)

mysql> insert into user values(2,"lsis","456");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(3,"wangwu","789");
Query OK, 1 row affected (0.00 sec)

mysql> insert into user values(4,"zhaoiu","aaa");
Query OK, 1 row affected (0.00 sec)

三、安全用户角色权限

1.密码安全策略

1.查看密码策略

mysql> show variables like 'validate%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

2.修改策略

mysql> set global validate_password.length=0;
mysql> show variables like 'validate%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 4     |
| validate_password.mixed_case_count   | 0     |
| validate_password.number_count       | 0     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 0     |
+--------------------------------------+-------+

2.用户

1.添加aaa账户，设置密码aaaa

drop user aaa;
create user 'aaa'@'%' identified by 'aaaa';

2.使用aaa账户访问mysql服务

mysql -h127.0.0.1 -P3306 -uaaa -paaaa

3.查看test数据库发现没有权限
show databases;
4.退出并使用root账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
5.为aaa账户添加查看test.user表的权限
grant select on test.user to 'aaa';
6.退出root，使用aaa账户登录
quit|exit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
7.查看数据库，查看表，查看表内容 能够正常查看
show databases;
user test;
show tables;
select * from user;
8.输入数据，没有权限
insert into user values(5,"ermazi","ermazi");####
9.退出aaa使用root登录
quit|exit
mysql -h127.0.0.1 -P3306 -uroot -proot0000
10.为aaa添加insert权限
grant insert on test.user to 'aaa';
11.退出root使用aaa登录
exit|quit
mysql -h127.0.0.1 -P3306 -uaaa -paaaa
12.向user表添加一行新的数据
insert into test.user values(6,"zhangsanfeng","zhangsanfen");
13.修改user中一行的数据的password（密码）为111，没有update权限
update test.user set password='zsf' where username-'zhangsanfeng';

3.角色

创建角色

mysql> create role 'a';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'a';
+-------------------------------+
| Grants for a@%                |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)

4.权限

1.刷新权限

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

为root账号添加权限

mysql> grant system_user on . to "root";

mysql> show grants for 'root';

2.修改权限

mysql> show grants for "efgh";
+--------------------------------------------------
-+
| Grants for efgh@%                                 
|
+--------------------------------------------------
-+
| GRANT SELECT, INSERT, DELETE ON *.* TO `efgh`@`%` 
|
+--------------------------------------------------
-+
1 row in set (0.00 sec)

3.删除权限

mysql> revoke all on *.* from "efgh";
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "efgh";
+----------------------------------+
| Grants for efgh@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `efgh`@`%` |
+----------------------------------+
1 row in set (0.00 sec)