RPM安装MySQL5.7.20
mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
https://blog.csdn.net/qq_23167527/article/details/85236999
https://www.cnblogs.com/kevingrace/p/8340690.html
1、手动配置yum源
[root@localhost ~]# vi /etc/yum.repos.d/Centos7.repo
[base]
name=centos7
baseurl=ftp://172.16.229.83
gpgcheck=0
yum clean all
yum makecache
yum -y install vim
yum -y install lrzsz
2、卸载系统自带的mariadb相关的rpm包
# 查看mariadb版本
rpm -qa | grep mariadb
# 卸载mariadb
rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
此方法卸载失败或者报错请使用
yum remove mysql-libs
3、安装依赖
yum -y install net-tools perl perl-devel perl-JSON.noarch autoconf libaio libaio-devel ncurses-devel numactl.x86_64
4、从网易镜像下载mysql安装包
wget https://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
5、解压mysql安装包
tar -xvf mysql-5.7.20-1.el7.x86_64.rpm-bundle.tar
6、安装mysql-server服务，只需要安装如下4个软件包即可，使用rpm -ivh进行安装（按顺序安装，后面的服务依赖前面的服务）
必须安装(注意顺序)
rpm -ivh mysql-community-common-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.20-1.el7.x86_64.rpm
非必要安装（注意顺序）
rpm -ivh mysql-community-embedded-compat-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-embedded-devel-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-test-5.7.20-1.el7.x86_64.rpm
rpm -ivh mysql-community-minimal-debuginfo-5.7.20-1.el7.x86_64.rpm
7、初始化数据库
mysqld --initialize --console
8、目录授权，否则启动失败
chown -R mysql:mysql /var/lib/mysql/
9、设置开机自启并启动msyqld服务
# 设置开机自启
systemctl enable mysqld
# 启动
service mysqld start
systemctl start mysqld
# 停止
service mysqld stop
systemctl stop mysqld
# 重启
service mysqld restart
systemctl restart mysqld
10、查看msyqld服务的状态
service mysqld status
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
11、在/var/log/mysqld.log下查看临时密码
cat /var/log/mysqld.log
12、用临时密码登录到数据库
[root@localhost ~]# mysql -u root -p
# 修改密码
mysql> alter USER ‘root‘@‘localhost‘ IDENTIFIED BY ‘webapp‘;
Query OK, 0 rows affected (0.00 sec)
# 查看所有用户是否可以远程连接，依次执行下列命令：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (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> select host, user, authentication_string, plugin from user;
+-----------+---------------+-------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+---------------+-------------------------------------------+-----------------------+
| localhost | root | *BF7C27E734F86F28A9386E9759D238AFB863BDE3 | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+-----------+---------------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
host为localhsot，说明用户只能本地连接msyql服务
# 依次执行下列mysql命令 授权远程连接
# 修改root用户host值，使root用户可以远程登录
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 host, user, authentication_string, plugin from user;
+-----------+---------------+-------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+---------------+-------------------------------------------+-----------------------+
| % | root | *BF7C27E734F86F28A9386E9759D238AFB863BDE3 | mysql_native_password |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password |
+-----------+---------------+-------------------------------------------+-----------------------+
3 rows in set (0.00 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
如果客户端登录不上，解决办法：
mysql -u root -p+ 回车键+输入root用户的密码 登录 ;
show databases;
use mysql;
alter USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘root用户的密码‘;
flush privileges;
备注：
mysql8 之前的版本中加密规则是mysql_native_password，而在mysql8之后,加密规则是caching_sha2_password。
解决问题方法有两种：
方法1.升级navicat驱动；
方法2.把mysql用户登录密码加密规则还原成mysql_native_password.。
此处我使用的是方法2。
13、创建用户和授权
用户创建：
create user ‘quanran‘@‘%‘ identified by ‘QuanRan123:‘;
授权：
grant all privileges on *.* to ‘quanran‘@‘%‘ with grant option;
查看用户权限：
select host, user, authentication_string, plugin from user;
备注：
1.创建用户命令：
CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;
说明：
username： 你将创建的用户名
host： 指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password： 该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器
例子：
CREATE USER ‘dog‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘quanran‘@‘192.168.1.101_‘ IDENDIFIED BY ‘123456‘;
CREATE USER ‘quanran‘@‘%‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘quanran‘@‘%‘ IDENTIFIED BY ‘‘;
CREATE USER ‘quanran‘@‘%‘;
2.授权命令:
GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘
说明:
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*
例子：
GRANT SELECT, INSERT ON test.user TO ‘quanran‘@‘%‘;
GRANT ALL ON *.* TO ‘quanran‘@‘%‘;
GRANT ALL ON maindataplus.* TO ‘quanran‘@‘%‘;
注意：
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘ WITH GRANT OPTION;
3.设置与更改用户密码命令:
SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘);
如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
例子：
SET PASSWORD FOR ‘quanran‘@‘%‘ = PASSWORD("123456");
4.撤销用户权限命令：
REVOKE privilege ON databasename.tablename FROM ‘username‘@‘host‘;
说明:
privilege, databasename, tablename：同授权部分
例子：
REVOKE SELECT ON *.* FROM ‘quanran‘@‘%‘;
注意：
1.如果用 GRANT SELECT ON test.user TO ‘quanran‘@‘%‘ 给用户’quanran’@’%‘授权，则使用 REVOKE SELECT ON . FROM ‘quanran‘@‘%‘; 并不能撤销该用户对test数据库中user表的SELECT 操作。
2.如果用 GRANT SELECT ON . TO ‘quanran‘@‘%‘; 给’quanran’@’%‘授权，则使用 REVOKE SELECT ON test.user FROM ‘quanran‘@‘%‘; 命令也不能撤销该用户对test数据库中user表的Select权限。
3.具体信息可以用命令SHOW GRANTS FOR ‘quanran‘@‘%‘; 查看。
5.删除用户
命令：
DROP USER ‘username‘@‘host‘;