Centos7通过rpm包安装Mysql5.7

卸载系统自带的mariadb和mysql-libs（首次安装）

//删除mariadb
rpm -e --nodeps `rpm -qa|grep mariadb`
//再次查询是否已删除
rpm -qa | grep mariadb
//删除
rpm -e --nodeps `rpm -qa|grep mysql-libs`
//再次查询是否已删除
rpm -qa | grep mysql-libs

SELINUX（首次安装）

vi /etc/selinux/config
SELINUX=disabled

需要有的依赖（首次安装）

rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
[root@test mysql]# rpm -qa|grep libaio
libaio-0.3.109-13.el7.x86_64

rpm -ivh net-tools-2.0-0.25.20131004git.el7.x86_64.rpm
[root@test mysql]# rpm -qa|grep net-tools
net-tools-2.0-0.25.20131004git.el7.x86_64

rpm -ivh --nodeps --force perl-5.16.3-297.el7.x86_64.rpm
[root@test xyp9x]# rpm -qa|grep perl
perl-5.16.3-297.el7.x86_64

rpm -ivh --nodeps --force perl-Getopt-Long-2.40-3.el7.noarch.rpm
[root@test xyp9x]# rpm -qa|grep perl-Getopt
perl-Getopt-Long-2.40-3.el7.noarch

卸载旧MySql（非首次安装）

service mysqld stop
//查看数据路径
vi /etc/my.cnf
datadir=/var/lib/mysql
//数据在哪个路径就备份到哪个路径
//备份数据（预估剩余磁盘是否可以容纳备份数据）
cp -r /var/lib/mysql /var/lib/mysql_bak
//备份数据（数据量大不建议，等待时间很久）
mysqldump -uxyp9x -p xyp9x > xyp9x20210826.sql
//备份存储过程
mysqldump -R -ndt -uxyp9x -p xyp9x > xyp9x_procedure20210826.sql
//备份配置文件
cp /etc/my.cnf /etc/my.cnf_bak

rpm -qa | grep mysql

//卸载的时候不会删除mysql数据目录和my.cnf配置文件
rpm -e --nodeps mysql-community-common-5.7.35-1.el7.x86_64
rpm -e --nodeps mysql-community-libs-5.7.35-1.el7.x86_64
rpm -e --nodeps mysql-community-client-5.7.35-1.el7.x86_64
rpm -e --nodeps mysql-community-server-5.7.35-1.el7.x86_64

rpm -qa | grep mysql

依次安装rpm包，依赖关系依次为common→libs→client→server（首次安装和非首次安装）

mkdir /home/xyp9x/mysql/
tar -xvf mysql-5.7.35-1.el7.x86_64.rpm-bundle.tar

rpm -ivh mysql-community-common-5.7.35-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.35-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.35-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.35-1.el7.x86_64.rpm

//在/etc/下生成my.cnf文件、my.cnf.d文件夹
//在/var/lib/下生成mysql、mysql-files、mysql-keyring文件夹
//在/var/log/下生成mysqld.log文件
//在/var/run/下生成mysqld文件夹

还原数据（非首次安装）

//备份数据
cp /var/lib/mysql_bak/* /var/lib/mysql/
//备份配置文件
mv /etc/my.cnf_bak /etc/my.cnf

配置文件my.cnf（首次安装）

vi /etc/my.cnf

[mysqld]
添加：
character-set-server=utf8
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
lower_case_table_names=1
event_scheduler=ON
secure_file_priv=""

配置文件my.cnf（如果需要修改数据存放路径）（首次安装）

//默认数据路径
datadir=/var/lib/mysql
//如果需要修改数据存放路径
vi /etc/my.cnf
//修改要保存数据的路径
datadir=/home/xyp9x/mysql
//然后备份
cp -r /var/lib/mysql /home/xyp9x/
//修改权限
chmod 755 /home
cd /home/xyp9x/
chown -R mysql:mysql mysql/

启动（首次安装和非首次安装）

//启动
service mysqld start
//状态
service mysqld status
//关闭
service mysqld stop

修改密码（首次安装）

cat /var/log/mysqld.log | grep 'temporary password'
//通过找到的密码登陆
mysql -uroot -p
//更改密码策略
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> alter user root@localhost identified by 'root';
mysql> flush privileges;

远程访问与权限（首次安装）

mysql> select host, user from mysql.user;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+

//指定root用户能远程连接mysql
mysql> update mysql.user set host = '%' where user = 'root';

//创建普通用户和用户密码
mysql> create user 'xyp9x'@'%' identified by 'xyp9x';

//修改指定用户密码
mysql> update mysql.user set authentication_string = password('xyp9x') where user = 'xyp9x';

//删除账户及权限
mysql> drop user 'xyp9x'@'%';
mysql> flush privileges;

//开启super权限
//普通用户导入数据权限
mysql> select super_priv from mysql.user where user = 'xyp9x';
mysql> update mysql.user set super_priv='Y' where user = 'xyp9x';

//查看普通用户权限（无任何权限）
mysql> show grants for xyp9x;
+-----------------------------------+
| Grants for xyp9x@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'xyp9x'@'%' |
+-----------------------------------+

//所有权限赋给普通用户
mysql> grant all privileges on dongxin.* to 'dongxin'@'%';
//指定权限赋给普通用户
mysql> grant select,insert,update,delete,create on xyp9x.* to 'xyp9x'@'%';

//查看普通用户权限
mysql> show grants for xyp9x;
+--------------------------------------------------+
| Grants for xyp9x@%                               |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'xyp9x'@'%'       |
| GRANT ALL PRIVILEGES ON `xyp9x`.* TO 'xyp9x'@'%' |
+--------------------------------------------------+

//撤销普通用户的权限
mysql> revoke all privileges on xyp9x.* from 'xyp9x'@'%';
mysql> flush privileges;

//创建数据库
mysql> create database xyp9x;
mysql> flush privileges;