安装:
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)