CentOS7.x安装Mysql8.x教程，Mysql8.x创建新用户教程，Mysql8.x修改密码规则

• 安装Mysql8.x

1、配置mysql8.x安装源

        sudo rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

2、安装mysql8.x

        sudo yum --enablerepo=mysql80-community install -y mysql-community-server

3、启动mysql服务，有可能没有service命令，参考这里

        sudo service mysqld start

4、查看mysql服务状态

        sudo service mysqld status      --如下启动成功

5、登陆重置mysql密码的两种方式：

•     使用root用户的临时密码，然后修改()

        grep "A temporary password" /var/log/mysqld.log    --有标记部分为密码

        mysql -uroot -p                           --不能用密码登陆，然后输入查到的临时密码

        直接修改密码为新密码，需要是大写，小写，数字，特殊字符的组合

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before execu
ting this statement.mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass@123';
Query OK, 0 rows affected (0.01 sec)

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

        用navicat连接当前mysql报错：1130 - Host‘xxx.xxx.xxx.xxx'is not allowed to connect to this MySQL server

        解决方案：

            开启本机3306端口，或者你配置的端口

            

[root@liam ~]# firewall-cmd --permanent --add-port=3306/tcp
success
[root@liam ~]# firewall-cmd --reload
success

            开启阿里云安全规则里的3306端口，或者你配置的端口(阿里云主机适用)

            mysql账户配置远程连接

mysql> update mysql.user set host = '%' where user ='root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

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

            这是因为navicat不支持mysql的加密规则导致的，修改mysql账户的加密规则

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'MyNewPass@123';
Query OK, 0 rows affected (0.01 sec)

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

         搞定！！！       

•  配置mysql安全策略(生产环境)

[root@liam ~]# mysql_secure_installation    #配置mysql安全配置向导

Securing the MySQL server deployment.

Enter password for user root:   #查到的临时密码
The 'validate_password' component is installed on the server.
The subsequent steps will run with the existing configuration
of the component.
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y #是否更改mysql临时密码 ，，输入y更改

New password:        #输入密码

Re-enter new password:  #再次输入密码

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for
 No) : yBy default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #是否删除匿名用户,生产环境建议删除，所以直接输入y回车
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y ##是否禁止root远程登录,根据自己的需求选择Y/n并回车,建议禁止
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n # 是否删除test数据库，我没有删除

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #是否重新加载权限表，重新加载
Success.

All done!

           搞定！！！

•  Mysql8.x修改密码规则

1、mysql8.x默认的密码规则是(需要大小写，数字和符号四种组成最短8位，适用生产环境，不适用测试环境)

2、查看当前安全变量值：mysql>SHOW VARIABLES LIKE 'validate_password%';

mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| 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.14 sec)

3、修改变量  注意：注意到8.0 比5.7多了带“.”的变量

    mysql>set global validate_password.policy=0;
    mysql>set global validate_password.length=4;

•  Mysql8.x创建新用户

1、查看用户

select user,host from mysql.user;

2、新建一个用户

CREATE USER '用户名'@'%' IDENTIFIED BY '密码';

3、给这个用户授权

GRANT ALL ON *.* TO '用户名'@'%';

#其中 ALL表示所有权限   *.*表示所有库的所有表有赋予的权限，可以 databasename.tablename 赋予某个库的某个表

# ‘用户名’@‘%’ 表示给该用户所有ip地址都可以远程访问的权限   with grant option表示有grant权限，可以把权限给第三方

#其他的权限有：ALTER、ALTER ROUTINE、CREATE、CREATE ROUTINE、CREATE TABLESPACE、CREATE TEMPORARY TABLES、CREATE USER、CREATE VIEW、DELETE、DROP、EVENT、EXECUTE、FILE、GRANT OPTION、INDEX、INSERT、LOCK TABLES、PROCESS、PROXY、REFERENCES、RELOAD、REPLICATION CLIENT、REPLICATION SLAVE、SELECT、SHOW DATABASES、SHOW VIEW、SHUTDOWN、SUPER、TRIGGER、UPDATE、USAGE。

4、刷新权限

flush privileges;

5、使用Navicat连接如果报错

"2059 - Authentication plugin 'caching_sha2_password' cannot be loaded: 乱码"

错误原因：Mysql 8.0的新特性，旧版本Navicat不支持。

解决方案：

        1）以旧版本的方式重置密码

         ALTER USER '用户名'@'%' IDENTIFIED WITH mysql_native_password BY '密码';

        2）给navicat更新驱动 

•  LInux7.x完全卸载mysql8.x

 1、关闭mysql，取消mysqld的开机启动

[root@liam ~]# systemctl disable mysqld  #关闭开启启动
Removed symlink /etc/systemd/system/multi-user.target.wants/mysqld.service.
[root@liam ~]# systemctl stop mysqld #停止mysql

2、卸载安装的mysql软件

[root@liam ~]# rpm -qa|grep -i mysql  #查看有那些组件
mysql80-community-release-el7-1.noarch
mysql-community-client-8.0.17-1.el7.x86_64
mysql-community-libs-8.0.17-1.el7.x86_64
mysql-community-common-8.0.17-1.el7.x86_64
mysql-community-server-8.0.17-1.el7.x86_64
[root@liam ~]# rpm -ev mysql-community-server-8.0.17-1.el7.x86_64 --nodeps #一个个删除组件
-------
-------
-------

3、删除mysql创建的文件夹及文件

[root@liam ~]# find / -name mysql #查看有哪些文件夹
/usr/lib64/mysql
/usr/bin/mysql
/etc/logrotate.d/mysql
/var/lib/mysql
/var/lib/mysql/mysql
[root@liam ~]# rm -rf /usr/lib64/mysql #一个个删除这些
----------
----------
----------
[root@liam ~]# rm -rf /etc/my.cnf   #删除mysql配置文件
[root@liam ~]# rm -rf /var/log/mysqld.log   #删除mysqld的一个日志文件(防止以后安装临时密码有问题)

 

 

•  参考文章

 CentOS 7 安装Mysql8.0教程

 CentOS 7配置firewall防火墙