绿色mysql，安全用户权限

绿色mysql

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

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

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

[root@mysql ~]# tar -xf mysql-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

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

[root@mysql ~]# yum list installed |grep libaio                              //查看是否安装依赖

libaio.x86_64                          0.3.109-13.el7                  @anaconda

[root@mysql ~]# useradd -r -s /sbin/nologin mysql

[root@mysql ~]# id mysql

uid=27(mysql) gid=27(mysql) 组=27(mysql)

[root@mysql ~]# cd /usr/local/

[root@mysql local]# mkdir mysql/mysql-files

[root@mysql local]# chown mysql:mysql /usr/local/mysql/mysql-files

[root@mysql local]# chmod 750 /usr/local/mysql/mysql-files

[root@mysql ~]# /usr/local/mysql/bin/mysqld --initialize --basedir=/usr/local/mysql/

3HgeAd+8HumE

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

bin  data  docs  include  lib  LICENSE  man  mysql-files  README  share  support-files

[root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data

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

root@mysql ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

[root@mysql ~]# service mysql8 start

[root@repo ~]# /usr/local/mysql/bin/mysql -u root -p

Enter password:

mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'root';   //在mysql中将密码修改为root

Query OK, 0 rows affected (0.00 sec)

远程连接指令

mysql -h主机ip或者域名 -P端口号 -u账号 -p密码

[root@mysql ~]# /usr/local/bin/mysql_secure_installation   改密码，不允许远程等

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.01 sec)

//修改安全策略

mysql> set global validate_password.length=0;

Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password.policy=LOW;

Query OK, 0 rows affected (0.00 sec)

mysql>  flush privileges;                   //刷新权限

mysql>  create user 'efg'@'%' identified by 'efgh';             //创建用户

Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;             //查看用户权限

+-----------+------------------+

| host      | user             |

+-----------+------------------+

| %         | efg              |

| %         | zhangsan         |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

| localhost | root             |

+-----------+------------------+

6 rows in set (0.00 sec)

mysql> drop user 'efg'；           //删除用户

Query OK, 0 rows affected (0.00 sec)

//设置root用户远程连接

mysql> grant all on root.* to 'root'@'%';

ERROR 1410 (42000): You are not allowed to create a user with GRANT

//报错，这是因为MySQL的新版本，不再允许使用GRANT给root用户授权，因为这可能引发安全问题。

mysql>  update user set host='%' where user='root';       //使用这个命令

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;            //刷新权限

Query OK, 0 rows affected (0.02 sec)

mysql> grant all on root.* to 'root'@'%';           //重新赋予权限

Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from mysql.user;             //查看

+-----------+------------------+

| host      | user             |

+-----------+------------------+

| %         | root             |

| %         | zhangsan         |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

+-----------+------------------+

5 rows in set (0.00 sec)

//        现在就可以使用navicat远程连接root账户

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)

mysql> grant select ,insert,delete,update on test.user to 'a';          //为a添加select insert delete update权限

Query OK, 0 rows affected (0.00 sec)

mysql> select host,user from mysql.user;             //查看角色保存的表格

+-----------+------------------+

| host      | user             |

+-----------+------------------+

| %         | a                |

| %         | root             |

| %         | zhangsan         |

| localhost | mysql.infoschema |

| localhost | mysql.session    |

| localhost | mysql.sys        |

+-----------+------------------+

6 rows in set (0.00 sec)