《MySQL系列-部署维护》Linux部署MySQL及忘记密码

一、安装MySQL

安装包

1.MySQL安装

## 1. 切换到本地yum仓库
[root@hadoop ~]# cd /etc/yum.repos.d/
[root@hadoop yum.repos.d]# ll
total 8
-rw-r--r-- 1 root root 614 Apr 25 10:07 CentOS-Base.repo
-rw-r--r-- 1 root root 230 Apr 25 10:07 CentOS-Epel.repo

##2. 本地安装引导
[root@hadoop software]# yum -y localinstall mysql-community-release-el6-5.noarch.rpm
## 这里是提前下载好的MySQL
## https://dev.mysql.com/downloads/mysql/

##3. 安装
[root@hadoop yum.repos.d]# yum -y install mysql-server

##4. 启动服务
[root@hadoop yum.repos.d]# service mysqld start

##5. 初始化密码
[root@hadoop yum.repos.d]# mysqladmin -uroot password '123456'

##6. 登陆
[root@hadoop yum.repos.d]# mysql -uroot -p123456

action:
1.当执行yum -y install mysql-server的时候报如下错误，是因为MySQL的GPG升级了，需要重新获取

Public key for mysql-community-libs-compat-5.7.38-1.el7.x86_64.rpm is not installed


 Failing package is: mysql-community-libs-compat-5.7.38-1.el7.x86_64
 GPG Keys are configured as: file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[root@zxy_master software]# systemctl start mysqld
Failed to start mysqld.service: Unit not found.

2.重新获取GPG并重新安装mysql-server

[root@zxy_master software]# rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022
# 获取后，重新安装即可
[root@zxy_master software]# yum -y install mysql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
......
Replaced:
  mariadb-libs.x86_64 1:5.5.68-1.el7

Complete!

# 启动成功
[root@zxy_master software]# systemctl start mysqld
[root@zxy_master software]#

2.远程授权

##1. 登陆
[root@hadoop yum.repos.d]# mysql -uroot -p123456

##2.查询权限
mysql> mysql> select user,host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.01 sec)

##3.修改权限
mysql> mysqle user set host = '%' where user = 'root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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

##4.远程授权
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

##5. 登陆
[root@hadoop yum.repos.d]# systemctl stop firewalld

3.阿里云开放3306端口

4.Navicat连接

二、MySQL密码重置

1.登录密码忘记

[root@zxy_master software]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

2.在mysqld.log中查找临时密码

[root@zxy_master software]# grep "password" /var/log/mysqld.log
2022-06-11T14:51:40.690022Z 1 [Note] A temporary password is generated for root@localhost: 3Jt8y&Pi=(<O
2022-06-11T14:56:27.838371Z 2 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2022-06-11T14:56:55.068648Z 3 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2022-06-11T14:57:03.115152Z 4 [Note] Access denied for user 'root'@'localhost' (using password: YES)
2022-06-11T14:58:32.833108Z 5 [Note] Access denied for user 'root'@'localhost' (using password: NO)
2022-06-11T14:58:40.850061Z 6 [Note] Access denied for user 'root'@'localhost' (using password: YES)

3.使用临时密码登录

## 如果是这种方式登录，可能会因为临时密码中的特殊符号无法登录
[root@zxy_master software]# mysql -uroot -p3Jt8y&Pi=(<O
-bash: syntax error near unexpected token `<'

## 采用这种方式即可，登录成功
[root@zxy_master software]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.38

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

4.修改密码

mysql> alter user 'root'@'localhost' identified by '@Aa123456';
Query OK, 0 rows affected (0.00 sec)

如果不被允许修改为简单的密码，可更改安全策略，重启即可

## 在/etc/my.cnf文件中添加如下两行，重启后，即可永久生效
validate_password_policy=0
validate_password_length=1