centos7 安装压缩版MySQL5.7.24数据库，以及重置密码

                               mysql数据库安装

1.首先官网下载压缩包 mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz

2.解压到/usr/local 目录下，解压完成后修改文件名 mv mysql-5.7.24**** mysql

[root@localhost local]# ls
mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz  redis-4.0.6    tomcat8
mongodb4.0.0  mysql-5.7.24-linux-glibc2.12-x86_64  nginx   redis-4.0.6.tar.gz  
[root@localhost local]# mv mysql-5.7.24-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ls
 mysql   mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz  redis-4.0.6  tomcat8
 mongodb4.0.0   nginx   redis-4.0.6.tar.gz  

3.由于CentOS 7 版本将MySQL数据库软件从默认的程序列表中移除，用mariadb代替了。要先卸载mariadb，除非不安装mysql，而直接使用mariadb，请参考以下方法：来自centos7 mysql数据库安装和配置 - starof - 博客园

---

安装mariadb

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。

安装mariadb，大小59 M。

[root@yl-web yl]# yum install mariadb-server mariadb 

mariadb数据库的相关命令是：

systemctl start mariadb  #启动MariaDB

systemctl stop mariadb  #停止MariaDB

systemctl restart mariadb  #重启MariaDB

systemctl enable mariadb  #设置开机启动

所以先启动数据库

[root@yl-web yl]# systemctl start mariadb

然后就可以正常使用mysql了

[root@yl-web yl]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.41-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> 

安装mariadb后显示的也是 MariaDB [(none)]> ，可能看起来有点不习惯。

---

以下是继续安装压缩版mysql：

#卸载系统自带的Mariadb
[root@localhost ~]# rpm -qa|grep mariadb
mariadb-libs-5.5.44-2.el7.centos.x86_64
[root@localhost ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64

#删除etc目录下的my.cnf文件

[root@localhost ~]# rm /etc/my.cnf
rm: cannot remove ?etc/my.cnf? No such file or directory

#检查mysql是否存在
[root@localhost ~]# rpm -qa | grep mysql
[root@localhost ~]# 

#检查mysql组和用户是否存在，如无创建
[root@localhost ~]# cat /etc/group | grep mysql 
[root@localhost ~]#  cat /etc/passwd | grep mysql

#创建mysql用户组
[root@localhost ~]# groupadd mysql
#创建一个用户名为mysql的用户并加入mysql用户组
[root@localhost ~]# useradd -g mysql mysql

在创建用户组和group分组时，由于此前手残，把文件删除了，一直报错，这时要复制他们的备份文件

[root@localhost mysql]# groupadd mysql
groupadd：无法打开 /etc/group
[root@localhost mysql]# cd ../../../
[root@localhost /]# groupadd mysql
groupadd：无法打开 /etc/group
[root@localhost /]# lsattr /etc/passwd  
lsattr: 没有那个文件或目录 当尝试对/etc/passwd进行stat调用时
[root@localhost /]# cp /etc/passwd- /etc/passwd
[root@localhost /]# cp /etc/group- /etc/group
[root@localhost /]# groupadd mysql
[root@localhost /]# useradd -g mysql mysql

以上cp 就是解决办法

更改所属的组和用户

#更改所属的组和用户
[root@localhost local]# chown -R mysql mysql/
[root@localhost local]# chgrp -R mysql mysql/
[root@localhost local]# cd mysql
#创建data文件夹
[root@localhost mysql]# mkdir data
[root@localhost mysql]# ls
bin  COPYING  data  docs  include  lib  man  README  share  support-files

安装和初始化

在etc目录下创建my.cnf文件,编辑文件

[root@localhost mysql]# touch /etc/my.cnf
[root@localhost mysql]# vim /etc/my.cnf

 添加以下内容

①，普通版

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
skip-name-resolve
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M

 ②，支持表情包的 emoji

[mysql]
# 设置mysql默认字符集
# default-character-set=utf8 
# 设置mysq默认使用字符集 utf8mb4 支持emoji 表情包
default-character-set=utf8mb4
[client]
# 设置mysql客户端默使用认字符集 支持emoji 
default-character-set=utf8mb4

[mysqld]
skip-name-resolve
#设置3306端口
port = 3306 
# 设置mysql的安装目录
basedir=/usr/local/mysql
# 设置mysql数据库的数据的存放目录
datadir=/usr/local/mysql/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
# character-set-server=utf8
# 以下内容配置 是为了使数据库支持 emoji 表情
character-set-client-handshake=FALSE
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
# 支持配置结束
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB 
lower_case_table_names=1
max_allowed_packet=16M

然后初始化

[root@localhost mysql]# bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
2019-01-03 10:54:13 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-01-03 10:54:28 [WARNING] The bootstrap log isn't empty:
2019-01-03 10:54:28 [WARNING] 2019-01-03T02:54:13.085298Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

 在初始化时碰到了一个问题：

2019-02-16 16:46:17 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2019-02-16 16:46:17 [ERROR]   Child process: /usr/local/mysql/bin/mysqldterminated prematurely with errno= 32
2019-02-16 16:46:17 [ERROR]   Failed to execute /usr/local/mysql/bin/mysqld --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
-- server log begin --
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

-- server log end --

 解决办法：

yum install -y libaio 

2020-07-02 09:41:28 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2020-07-02 09:41:28 [ERROR]   Child process: /usr/local/mysql/bin/mysqldterminated prematurely with errno= 32
2020-07-02 09:41:28 [ERROR]   Failed to execute /usr/local/mysql/bin/mysqld --bootstrap --datadir=/usr/local/mysql/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql
-- server log begin --
/usr/local/mysql/bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

-- server log end --

解决办法：

yum -y install numactl

安装后再进行初始化就行了

拷贝启动文件到etc/init.d/mysqld下，赋予其权限，启动mysql

[root@localhost mysql]# cp ./support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql]# chown 777 /etc/my.cnf 
[root@localhost mysql]# chmod +x /etc/init.d/mysqld
[root@localhost mysql]# /etc/init.d/mysqld restart

启动时如果报以下错，说明可能已经启动过了，端口被用了

[root@localhost mysql]# /etc/init.d/mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
 SUCCESS! 

可以通过命令查看，执行完后在重启，不会报如上错误，启动成功，再查看进程号

[root@localhost mysql]# ps -ef|grep mysqld
root     31544     1  0 11:05 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql    31743 31544  0 11:05 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --port=3306
root     31959 20119  0 11:07 pts/0    00:00:00 grep --color=auto mysqld
[root@localhost mysql]# kill -9 31544
[root@localhost mysql]# kill -9 31743
[root@localhost mysql]# /etc/init.d/mysqld restart
 ERROR! MySQL server process #31743 is not running!
Starting MySQL. SUCCESS! 
[root@localhost mysql]# ps -ef|grep mysqld
root     32067     1  0 11:07 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/localhost.localdomain.pid
mysql    32268 32067  1 11:07 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/usr/local/mysql/data/localhost.localdomain.pid --port=3306
root     32338 20119  0 11:08 pts/0    00:00:00 grep --color=auto mysqld

其它情况参考： MySQL server PID file could not be found!_「已注销」的博客-CSDN博客

启动完成后设置开机自启动

[root@localhost mysql]# chkconfig --level 35 mysqld on
[root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld
[root@localhost mysql]# chkconfig --add mysqld
[root@localhost mysql]# service mysqld status
 SUCCESS! MySQL running (32649)

获取mysql初始密码

[root@localhost mysql]# cat /root/.mysql_secret 
# Password set for user 'root@localhost' at 2019-01-03 10:54:13 
8pw0u8dayNm.

登录mysql，用 ./mysql -uroot  -p

[root@localhost mysql]# cd bin
[root@localhost bin]# mysql -uroot -p
-bash: mysql: 未找到命令
[root@localhost bin]# ./mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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>

修改密码，flush privileges; 很重要，否则无效

mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

然后 exit 退出，用新密码重新登录一次测试

修改mysql可以远程连接，把user为root的账号的host改为%

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 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> select user,host,password from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select user,host from user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> update 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.session | localhost |
| mysql.sys     | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> exit
Bye
[root@localhost bin]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

修改完重启，否则不生效

2.重置密码

登录时报错 ：ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)，排除其他原因后重置密码

1.重置密码的第一步就是跳过MySQL的密码认证过程，如下：

#vim /etc/my.cnf(注：windows下修改的是my.ini)

在末尾添加 ： 

skip-grant-tables

   然后保存文档并退出
2.重启MySQL：如上安装时重启命令：/etc/init.d/mysqld restart 

3.重启之后输入./mysql进入

4.接下来就是用sql来修改root的密码：

use mysql;

update mysql.user set authentication_string=password('123456') where user='root';mysql> flush privileges;

（注：mydql5.7 密码字段password 改为了 authentication_stringmysql）
exit

5.如果root用户误删了，则新建root用户

insert into user(user,host,authentication_string,ssl_type,ssl_cipher,x509_issuer,x509_subject) values('root','localhost',PASSWORD('123456'),'','','','');

 

如图：

重置密码完成，去掉my.cnf的内容，重启mysql，用密码登录即可