安装MySQL

1、安装MySQL8.0资源库

yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

如图

2、安装MySQL8.0

默认安装路径：/var/lib/mysql

yum install mysql-community-server

3、配置MySQL

vim /etc/my.cnf

# MySQL设置大小写不敏感：默认：区分表名的大小写，不区分列名的大小写
# 0：大小写敏感 1：大小写不敏感
lower_case_table_names=1
# 默认字符集
character-set-server=utf8

4、启动MySQL并配置开机自启动

systemctl start mysqld
systemctl enable mysqld

5、查看默认密码并重置

• 查看密码

grep 'temporary password' /var/log/mysqld.log

• 登录MySQL

mysql -uroot -p    //回车，提示输入密码，粘贴刚才查看的默认密码回车登录

如图

• 修改密码强度&长度

mysql5.7以后对密码的强度是有要求的，必须是字母+数字+符号组成的，如果想设置简单密码例 如‘123456’，需要做以下设置

设置密码强度级别
set global validate_password.policy=0;


设置密码长度最低位数
set global validate_password.length=1;

validate_password_policy有以下取值： 默认是1，即MEDIUM，所以刚开始设置的密码必须符合长度，且必须含有数字，小写或大写字母，特殊字符。

• 重置密码

更改密码为123456

alter user 'root'@'localhost' identified by '123456';  

• 退出后重新登录

quit   //退出

mysql -p123456   //登录

6、远程连接授权

MySQL 8需先创建用户再进行授权，可参考：https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0

• 创建用户并授权

CREATE USER 'root'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

1、创建root用户，%表示任意ip地址都可以登录，密码为123456；

2、为root用户分配所有库表的全部操作权限，ALL PRIVILEGES表示全部权限；*. *表示任意库下的任意表；

• 修改加密规则

mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password

所以使用navicat进行mysql登录时出现弹窗报错

• 修改加密方式

#修改密码为永不过期
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;  
Query OK, 0 rows affected (0.01 sec)

#修改密码并指定加密规则为mysql_native_password
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.00 sec)

#刷新权限
mysql>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> 




#修改完成后，可以查看修改结果
mysql> use mysql
mysql> select host, user, authentication_string, plugin from user;

连接成功！

• 练习：创建用户、角色、分配权限

mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';    //1
Query OK, 0 rows affected (0.01 sec)

mysql> grant select,insert,update  on *.* to 'test'@'%' WITH GRANT OPTION;     //2
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';    //3
Query OK, 0 rows affected (0.00 sec)


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> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | root             | *971DA3E9F6B338BC1AC31CC229AD59C5D99907EF                              | mysql_native_password |
| %         | test             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | 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             | $A$005$+ T7+,QY%{{lD,gM2ydFwrQmv7M1kLx4CtHcKLX2PxdADlPEChvN.Zd0N9 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

mysql> 

1、创建test用户

2、分配权限 grant A_privileges on B_table_name C_user_name;

​ 2.1、A_privileges：需要授予的权限，如：select,update,delete,insert等，如果赋予多个权限，中间用逗号隔开；如果要赋予全部权限，用：all privileges;
​ 2.2、B_table_name：数据库名.表名；* . *代表全部；
​ 2.3、C_user_name：需要将上面对表的权限赋予给哪个用户。

3、修改密码并指定加密规则为mysql_native_password

每日一皮

今日不皮