云计算21----mysql用户创建与授权

上午
1、环境准备
 [root@mysql ~]# rm -rf /etc/my.cnf    //清空/etc目录下的my.cnf
 [root@mysql ~]# yum -y remove mariadb   //移除mariadb
 [root@mysql ~]# find / -name "*mysql*" -exec rm -rf {} \;    //删除mysql所有遗留文件
2、安装mysql绿包
 [root@mysql ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
 [root@mysql ~]# ls
 mysql-8.0.33-linux-glibc2.12-x86_64.tar
 mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
 mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz
 mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz
 [root@mysql ~]# tar -xf  mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz 
 [root@mysql ~]# ls
 mysql-8.0.33-linux-glibc2.12-x86_64
 mysql-8.0.33-linux-glibc2.12-x86_64.tar
 mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
 mysql-router-8.0.33-linux-glibc2.12-x86_64.tar.xz
 mysql-test-8.0.33-linux-glibc2.12-x86_64.tar.xz
 [root@mysql ~]# cd mysql-8.0.33-linux-glibc2.12-x86_64/
 [root@mysql mysql-8.0.33-linux-glibc2.12-x86_64]# ls
 bin  docs  include  lib  LICENSE  man  README  share  support-files
3、配置mysql工作环境
 [root@mysql ~]# vim support-......     //查看配置文件
 /basedir
 [root@mysql ~]# cp -r mysql-8.0.33-linux-glibc2.12-x86_64  /usr/local/mysql/   //将项目文件移动到/usr/local/mysql
 [root@mysql ~]# tree /usr/local/mysql
 [root@mysql ~]# yum list installed | grep libaio   //查看libaio是否存在
 [root@mysql ~]# echo $?   //查看上一命令是否执行成功
 [root@mysql ~]# id mysql   //查看是否有mysql用户
 useradd -r -s /sbin/nologin mysql   //如果没有mysql用户，创建用户
 [root@mysql ~]# mkdir /usr/local/mysql/mysql-files        //在/usr/local/mysql目录下创建mysql-files目录
 [root@mysql ~]# chown mysql:mysql /usr/local/mysql/mysql-files/     //修改mysql-files所属的组和属主都是mysql
 [root@mysql ~]# chmod 750 /usr/local/mysql/mysql-files/      //修改mysql-files的权限为750
 [root@mysql ~]# ll /usr/local/mysql/
 总用量 292
 drwxr-xr-x.  2 root  root    4096 8月   5 09:55 bin
 drwxr-xr-x.  2 root  root      38 8月   5 09:55 docs
 drwxr-xr-x.  3 root  root     282 8月   5 09:55 include
 drwxr-xr-x.  6 root  root     201 8月   5 09:55 lib
 -rw-r--r--.  1 root  root  284945 8月   5 09:55 LICENSE
 drwxr-xr-x.  4 root  root      30 8月   5 09:55 man
 drwxr-x---.  2 mysql mysql      6 8月   5 09:56 mysql-files
 -rw-r--r--.  1 root  root     666 8月   5 09:55 README
 drwxr-xr-x. 28 root  root    4096 8月   5 09:55 share
 drwxr-xr-x.  2 root  root      77 8月   5 09:55 support-files
 [root@mysql ~]# cd /usr/local/mysql/bin/     //cd到安装目录
 [root@mysql bin]# ls
 ibd2sdi            mysqlcheck             mysqlpump
 innochecksum       mysql_config           mysql_secure_installation
 lz4_decompress     mysql_config_editor    mysqlshow
 myisamchk          mysqld                 mysqlslap
 myisam_ftdump      mysqld-debug           mysql_ssl_rsa_setup
 myisamlog          mysqld_multi           mysql_tzinfo_to_sql
 myisampack         mysqld_safe            mysql_upgrade
 my_print_defaults  mysqldump              perror
 mysql              mysqldumpslow          zlib_decompress
 mysqladmin         mysqlimport
 mysqlbinlog        mysql_migrate_keyring
 [root@mysql bin]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/    //初始化数据库

[root@mysql ~]# ls /usr/local/mysql/     //查看是否生成了data目录
 bin   docs     lib      man          README  support-files
 data  include  LICENSE  mysql-files  share
 [root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data    //创建安全加密连接
 [root@mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8    //将mysql.server文件放到/etc/init.d/目录下，方便启动mysql服务 service mysql start
 [root@mysql ~]# service mysql8 start    //启动mysql服务
 Starting MySQL.Logging to '/usr/local/mysql/data/mysql.err'.
 ....... SUCCESS!
 [root@mysql ~]# systemctl stop firewalld   
 [root@mysql ~]# systemctl disable firewalld
 Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
 Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
 [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（远程连接使用）

4、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';    //创建root远程登录
 mysql> grant all privileges on *.* to 'root'@'%' with grant option;     //为root远程登录授权
 mysql> flush privileges;     //刷新操作
 mysql> desc mysql.user;   //查看表结构
 mysql> select host,user from mysql.user;   //查看用户与主机对应关系

5、mysql操作命令
 mysql> create user 'li'@'%' identified by 'Root@123456';    //创建用户li，并且可以在所有主机连接mysql
 mysql> grant all on *.* to 'li';     //为li用户授予所有数据库的所有表格的所有权限
 Query OK, 0 rows affected (0.01 sec)
 [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)
 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
 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      //创建软链接
下午
1、脚本安装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
2、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

-h    主机ip或者是域名    如果是localhost或者是127.0.0.1可以省略
-P    端口    默认的是3306，如果是默认的，可以省略
-u    用户名    必须输入
-p    密码    可以不换行直接输入，也可以换行，不回显输入密码
创建用户

 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");
3、mysql用户权限设置
 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
 [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@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库。）
 mysql> grant system_user on *.* to 'root';       //将system_user权限给root
 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
 [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'
4、命令练习
（1）创建三个账号，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)
（2）删除用户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)
（3）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)
（4）权限添加练习
（1）添加aaa账户，设置密码aaaa

 mysql> create user 'aaa'@'%' identified by 'aaaa';
 Query OK, 0 rows affected (0.01 sec)
（2）使用aaa账户访问mysql服务

 [root@mysql ~]# mysql -uaaa -paaaa
（3）查看test数据库发现没有权限

 mysql> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | performance_schema |
 +--------------------+
 2 rows in set (0.00 sec)
（4）退出并使用root账户登录

 mysql> quit
 Bye
 [root@mysql ~]# mysql -uroot -pRoot@123456
（5）为aaa账户添加查看test.user表的权限

 mysql> grant select on test.user to 'aaa';
 Query OK, 0 rows affected (0.00 sec)
（6）退出root，使用aaa账户登录

 mysql> quit
 Bye
 [root@mysql ~]# mysql -uaaa -paaaa
（7）查看数据库，查看表，查看表内容，能够正常查看

 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)
（8）输入数据，没有权限

 mysql> inster into user values(6,"aaa","aaaa");
 ERROR 1142 (42000): INSERT command denied to user 'aaa'@'localhost' for table 'user'
（9）退出aaa使用root登录

 mysql> quit
 Bye
 [root@mysql ~]# mysql -uroot -pRoot@123456
（10）为aaa添加insert权限

 mysql> grant insert on test.user to 'aaa';
 Query OK, 0 rows affected (0.00 sec)
（11）退出root使用aaa登录

 mysql> quit
 Bye
 [root@mysql ~]# mysql -uaaa -paaaa
（12）使用aaa账户，想user表中添加一行新的数据

 mysql> insert into test.user values(6,"aaa","aaaa");
 Query OK, 1 row affected (0.01 sec)
（13）修改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'
（14）为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)
6、创建权限角色
 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)
7、角色练习
新增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)