mysql 安装 权限_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)