实训day21

nginx代理mysql服务
关闭防火墙和selinux
解压
[root@3 ~]# yum -y remove *mar*
[root@3 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar

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

[root@3 ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64
[root@3 mysql-8.0.33-linux-glibc2.12-x86_64]# ls

[root@3 mysql-8.0.33-linux-glibc2.12-x86_64]# vim support-files/mysql.server
#默认目录
配置
[root@2 ~]# yum install -y libaio-devel     #下载依赖包
[root@3 ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql
[root@3 ~]# useradd -r -s /sbin/nologin mysql   #创建用户和组
[root@3 ~]# id mysql
uid=997(mysql) gid=994(mysql) groups=994(mysql)
[root@3 ~]# cd /usr/local/mysql/
[root@3 mysql]# mkdir mysql-files

[root@3 ~]# chown mysql:mysql /usr/local/mysql/mysql-files/
[root@3 ~]# chmod 750 /usr/local/mysql/mysql-files/       #修改权限
[root@3 ~]# ll /usr/local/mysql/
drwxr-x---.  2 mysql mysql      6 Aug  5 09:55 mysql-files
安全加密连接
[root@3 ~]# /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --initialize      #密码： VtrXtdaFW0,q

#有data目录才算成功

[root@3 ~]# ls /usr/local/mysql/data/

[root@2 ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
#创建安全加密连接
[root@3 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql      #移动文件，方便启动服务
[root@3 ~]# service mysql start

[root@mysql ~]# /usr/local/mysql/bin/mysql -uroot -p    //进入mysql中
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.33
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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>

mysql -hip地址 -p3306 -uroot -p（远程连接使用）
mysql基础命令
修改密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
授权远程登陆
mysql> create user 'root'@'%' identified with mysql_native_password by 'Root@123456';
刷新
mysql> flush privileges;     //刷新操作
查看表结构
mysql> desc mysql.user;   //查看表结构
查看用户与主机关系
mysql> select host,user from mysql.user;   //查看用户与主机对应关系

MySQL操作命令
创建li用户
mysql> create user 'li'@'%' identified by 'Root@123456';    //创建用户li，并且可以在所有主机连接mysql
为li用户授权
mysql> grant all on *.* to 'li';     //为li用户授予所有数据库的所有表格的所有权限
Query OK, 0 rows affected (0.01 sec)
使用li用户连接mysql
[root@mysql ~]# /usr/local/mysql/bin/mysql -uli -pRoot@123456    //使用li用户连接mysql
查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
创建tset数据库
mysql> create database if not exists test charset utf8;   //创建一个名为test的数据库
Query OK, 1 row affected, 1 warning (0.01 sec)
使用数据库
mysql> use test;    //使用test数据库
Database changed
创建user表
mysql> create table `user` (            //创建user表
   `id` int NOT NULL,
   `username` varchar(45) NOT NULL,
   `password` varchar(45) NOT NULL,
   primary key ('id')
);
向表中插入数据
mysql> insert into user values(1,"zhangsan","123"),(2,"lisi","456"),(3,"wangwu","789"),(4,"zhaoliu","aaa");    //为user表插入数据
Query OK, 4 rows affected (0.03 sec)
Records: 4  Duplicates: 0  Warnings: 0
查看表内容
mysql> select * from user;     //查看user表内容

[root@mysql ~]# ln -s  /usr/local/mysql/bin/mysql /usr/bin      //创建软链接
MySQL脚本安装
[root@mysql ~]# vim mysql.sh
#!/bin/bash
cp $1 /usr/local/mysql/
mkdir /usr/local/mysql/mysql-files/
grep /mysql/ /etc/password
if [ $? -ne 0 ];then
        useradd -r -s /sbin/nologin mysql
fi
chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files
# init
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
# password
# service
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8
# start server
service mysql8 start
mysql命令回顾
（1）远程登录前提条件是mysql.user表中的host属性为%，如果是localhost就不允许远程登录，
update mysql.user set host=“%” where user=“root”;    //修改表内容
flush privileges;    //刷新
（2）远程管理，可以使用图形化工具，sqlyog，navicat，掌握命令工具，客户端工具mysql
（3）mysql -h10.0.0.3 -P3306 -uli -pRoot@123456

创建用户
create user 'li'@'%' identified by 'Root@123456';
给权限
grant all on *.* to 'li';
创建库
create database if not exists test;
创建表
use test;
create table user(
    'id' int primary key,
    'username' varchar(45) not null,
    'password' varchar(45) not null
    );
添加数据
inster into test.user values(1,"zhangsan","123"),(2,"lisi","456"),(3,"wangwu","789"),(4,"zhaoliu","aaa");
mysql用户权限设置
创建lilaosi用户
mysql> create user 'lilaosi'@'%' identified by  'Lilaosi@123456';    //添加lilaosi账号
修改密码
mysql> alter user 'lilaosi'@'%' identified by 'Lilaosi@123456';    //修改密码
查看用户与主机关系
mysql> select host,user from mysql.user;  //查看mysql.user信息
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | li               |
| %         | lilaosi          |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)
退出数据库
mysql> quit
Bye
使用lilaosi登录mysql（发现无任何权限）
[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456
mysql> show databases;    //只有自己的权限
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.04 sec)
mysql> quit
Bye
使用root用户为lilaosi用户赋予test数据库中所有表的所有权限
[root@mysql ~]# mysql -uroot -pRoot@123456
mysql> grant all on test.* to 'lilaosi';   //使用root账号为lilaosi账号添加test库中所有表的权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -ulilaosi -pLilaosi@123456
mysql> show databases;    //对test数据库有一定的权限
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)
（root没有给lilaosi mysql库的权限，所有lilaosi账户无法查看mysql库。）
将system_user权限给root
mysql> grant system_user on *.* to 'root';       //将system_user权限给root
为aaa用户赋予查看test数据库user表的权限
mysql> show grants for aaa;     //只有自己的权限
+---------------------------------+
| Grants for aaa@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
mysql> grant select on test.user to 'aaa';  //为aaa用户赋予test数据库user表的查看权限
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -p123
mysql> select * from test.user;   //查看成功
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 456      |
|  3 | wangwu   | 789      |
|  4 | zhaoliu  | aaa      |
+----+----------+----------+
4 rows in set (0.00 sec)
mysql> insert into user values(5,"ermazi","bbb");  //没有插入权限
ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'
mysql> update user set password="bbb" where username="zhaoliu";   //没有修改权限
ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'
mysql> quit
Bye
为aaa用户赋予test数据库user表插入权限
[root@mysql ~]# mysql -uroot -pRoot@123456
mysql> grant insert on test.user to 'aaa';  //赋予插入权限
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -p123
mysql> insert into test.user values(5,"ermazi","bbb");    //插入成功
Query OK, 1 row affected (0.01 sec)
mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 456      |
|  3 | wangwu   | 789      |
|  4 | zhaoliu  | aaa      |
|  5 | ermazi   | bbb      |
+----+----------+----------+
5 rows in set (0.00 sec)
mysql> update test.user set password='000' where username='ermazi';    //没有修改权限
ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'
命令练习
创建三个账号，abc[abcd]，ccc[a1b2c3],ddd[231343]
mysql> create user 'abc'@'%' identified by 'abcd';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'ccc'@'%' identified by 'a1b2c3';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'ddd'@'%' identified by '231343';
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | li               |
| %         | lilaosi          |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
11 rows in set (0.00 sec)
删除用户lilaosi
删除lilaosi用户
mysql> drop user 'lilaosi';
Query OK, 0 rows affected (0.02 sec)
mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | li               |
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
10 rows in set (0.00 sec)
aaa，ccc，ddd三个账户的密码修改为123
mysql> alter user 'aaa'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'ccc'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> alter user 'ddd'@'%' identified by '123';
Query OK, 0 rows affected (0.01 sec)
权限添加练习
添加aaa账户，设置密码aaaa
mysql> create user 'aaa'@'%' identified by 'aaaa';
Query OK, 0 rows affected (0.01 sec)
使用aaa账户访问mysql服务
[root@mysql ~]# mysql -uaaa -paaaa
查看test数据库发现没有权限
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
退出并使用root账户登录
mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pRoot@123456
为aaa账户添加查看test.user表的权限
mysql> grant select on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
退出root，使用aaa账户登录
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -paaaa
查看数据库，查看表，查看表内容，能够正常查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user           |
+----------------+
1 row in set (0.00 sec)
mysql> select * from test.user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | 123      |
|  2 | lisi     | 456      |
|  3 | wangwu   | 789      |
|  4 | zhaoliu  | aaa      |
|  5 | ermazi   | bbb      |
+----+----------+----------+
5 rows in set (0.00 sec)
输入数据，没有权限
mysql> inster into user values(6,"aaa","aaaa");
ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'
退出aaa使用root登录
mysql> quit
Bye
[root@mysql ~]# mysql -uroot -pRoot@123456
为aaa添加insert权限
mysql> grant insert on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
退出root使用aaa登录
mysql> quit
Bye
[root@mysql ~]# mysql -uaaa -paaaa
使用aaa账户，想user表中添加一行新的数据
mysql> insert into test.user values(6,"aaa","aaaa");
Query OK, 1 row affected (0.01 sec)
修改user表中一行的数据的password（密码）为111，没有update权限
mysql> update test.user set password="111" where username='aaa';
ERROR 1142 (42000): UPDATE command denied to user 'aaa'@'localhost' for table 'user'
为aaa用户一次性添加delect，update权限
mysql> grant delete,update on test.user to 'aaa';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'aaa';
+--------------------------------------------------------------------+
| Grants for aaa@%                                                   |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `aaa`@`%` |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
权限角色
创建角色
mysql> create role 'jingli';    //创建jingli角色
Query OK, 0 rows affected (0.00 sec)
mysql> create role 'yuangong';      //创建yonghu角色
Query OK, 0 rows affected (0.00 sec)
为角色赋予权限
mysql> grant insert,delete,update,select on test.user to 'jingli';    //为jingli角色添加select，insert，delete，update权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'jingli';      //查看jingli角色权限
+-----------------------------------------------------------------------+
| Grants for jingli@%                                                   |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jingli`@`%`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.`user` TO `jingli`@`%` |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> grant select,insert on test.user to 'yuangong';     //为yuangong添加select，insert权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yuangong';     //查看yonghu角色权限
+---------------------------------------------------------+
| Grants for yuangong@%                                   |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO `yuangong`@`%`                    |
| GRANT SELECT, INSERT ON `test`.`user` TO `yuangong`@`%` |
+---------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> select host,user from mysql.user;    //查看角色保存的表格
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| %         | abc              |
| %         | ccc              |
| %         | ddd              |
| %         | jingli           |
| %         | li               |
| %         | root             |
| %         | yuangong         |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
12 rows in set (0.00 sec)
用户授予角色权限
mysql> grant jingli to 'ddd';          //将ddd用户授予jingli角色，拥有jingli权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for ddd;           //查看权限授权
+---------------------------------+
| Grants for ddd@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `ddd`@`%` |
| GRANT `jingli`@`%` TO `ddd`@`%` |
+---------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges;            //刷新权限
Query OK, 0 rows affected (0.00 sec)
删除角色权限
mysql> revoke all on *.* from "yuangong";    //删除yuangong所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for "yuangong";           //查看yuangong权限
+---------------------------------------+
| Grants for yuangong@%                 |
+---------------------------------------+
| GRANT USAGE ON *.* TO `yuangong`@`%`  |
+---------------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
角色练习
新增bbb和ccc两个用户，bbb需要增删改查权限，ccc需要新增与查看权限
mysql> create user 'bbb'@'%' identified by 'bbb';    //创建bbb用户
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'ccc'@'%' identified by 'ccc';    //创建ccc用户
Query OK, 0 rows affected (0.01 sec)
mysql> grant jingli to 'bbb';          //将bbb用户授予jingli角色，拥有jingli权限
Query OK, 0 rows affected (0.00 sec)
mysql> grant yuangong to 'ccc';       //将ccc用户授予yuangong角色，拥有yuangong权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for bbb;           //查看权限授权
+---------------------------------+
| Grants for bbb@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `bbb`@`%` |
| GRANT `jingli`@`%` TO `bbb`@`%` |
+---------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for ccc;           //查看权限授权
+-----------------------------------+
| Grants for ccc@%                  |
+-----------------------------------+
| GRANT USAGE ON *.* TO `ccc`@`%`   |
| GRANT `yuangong`@`%` TO `ccc`@`%` |
+-----------------------------------+
2 rows in set (0.00 sec)
mysql> flush privileges;            //刷新权限
Query OK, 0 rows affected (0.00 sec)