实训日记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)