目录
一、安装及配置mysql
1.下载
2.环境准备
3.解压
4.配置
1.创建用户
2.创建一个文件
3.修改权限
4.初始化数据库
5.判断是否初始化成功
6.设置ssl安全加密连接
7.其他配置
8.环境路径配置
5.启动服务
二、mysql管理
1.连接数据库
2.设置密码
3.修改登录权限
4.新创建一个用户并设置权限和密码
5.创建库
6.创建表
7.添加数据
三、安全用户角色权限
1.密码安全策略
1.查看密码策略
2.修改策略
2.用户
3.角色
4.权限
1.刷新权限
2.修改权限
3.删除权限
一、安装及配置mysql
1.下载
官网地址：MySQL :: Download MySQL Community Server
点击圈出来的按钮可以选择需要的版本，这里选择安装的是8.0.33版本的mysql
选好版本和操作系统，在下面的下载列表里面选择glibc
点击下载即可。
2.环境准备
1.清空/etc/目录下的my.cnf
[root@localhost ~]# rm -rf /etc/my.cnf2.删除mariadb
[root@localhost ~]# yum -y remove mariadb3.清空带有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 @anaconda3.解压
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-files3.修改权限
[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_0017.其他配置
[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)
