mysql 8 安装&账户权限控制

安装：

wget -i -c https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
yum -y install mysql80-community-release-el7-3.noarch.rpm
yum -y install mysql-community-server

设置selinux：

setenforce 0

修改/etc/selinux/config 文件
将SELINUX=enforcing改为SELINUX=disabled

启动：

systemctl start  mysqld.service
systemctl enable  mysqld.service

初始密码：

grep "password" /var/log/mysqld.log

登录：

mysql -uroot -p
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '密码';
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
> use mysql;

mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password。设置root能在navicat客户机上远程登录需要进行下面操作。
查看一下host是否已经有了%这个值

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

有了就可以了.没有就执行：

update user set host='%' where user='root';

设置root能远程链接：

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;         
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '密码';         
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,authentication_string,plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

收回权限

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'sit'@'%';
Query OK, 0 rows affected (0.00 sec)

只给查询权限

mysql> GRANT SELECT  ON *.* TO 'sit'@'%';
Query OK, 0 rows affected (0.00 sec)

添加用户并且限制ip访问

CREATE USER 'test'@'10.10.%' IDENTIFIED BY 'password';

GRANT ALL ON *.* TO 'test'@'10.10.%';

flush privileges;

注意 10.10.% 是docker swarm节点的ip。仅添加这个网段，容器（有3个网段）也能访问mysql。

给测试人员开通远程登录账户并且授权

mysql> use mysql
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> create user 'liulijun'@'%' identified by '密码';
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,update,insert,delete on *.* to 'liulijun'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'liulijun'@'%' IDENTIFIED BY '密码' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'liulijun'@'%' IDENTIFIED WITH mysql_native_password BY '密码';
Query OK, 0 rows affected (0.00 sec)

mysql>  select host,user,authentication_string,plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | liulijun         | *69FDB0C61F12B8195505BB07DCCA2DD8E8F9D31A                              | mysql_native_password |
| %         | sit              | *E720A611DD9D897B1504AAF314A405F5D9BFE967                              | mysql_native_password |
| 10.10.%   | test             | $A$005$='kQ )>? O[^h
                                                     m;aZKJxCCpytrajtcqM9EhVvJAKQYfHkrf2UfZTo4BFv3 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root             |                                                                        | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
7 rows in set (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)