CentOS6.5手动安装mysql5.7(CentOS6.5、6.10、7亲测)

1.查询版本

rpm -qa|grep -i mariadb 
rpm -qa|grep -i mysql 

2.卸载旧版本

rpm -e --nodeps MySQL-server-5.5.38-1.el6.x86_64 
rpm -e --nodeps MySQL-client-5.5.38-1.el6.x86_64 
rpm -qa|grep -i mysql 

3.删除数据目录

ls -l /var/lib|grep mysql 
rm -rf /var/lib/mysql 

4.解压安装包

tar -xvf mysql-5.7.29-1.el6.x86_64.rpm-bundle.tar 

顺便提醒，安装环境是CentOS6.5，所以应该选用el6的安装包；CentOS7应该选用el7安装包。
5.按照依赖顺序安装rpm包
依赖关系依次为common→libs→client→server

rpm -ivh mysql-community-common-5.7.29-1.el6.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.29-1.el6.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.29-1.el6.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.29-1.el6.x86_64.rpm 

注：我在centos7安装server报错无法启动mysql服务
（rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm ）

安装了几个依赖可以了

# yum install libaio 
# yum install perl 
# yum install net-tools 

6.初始化mysql服务(如果成功直接到第9步)
service mysqld start
额，centos7上没出错误，centos6.5爆了

第一次失败是真正原因
查看日志/var/log/mysqld.log

第二次失败是已经存在文件所以初始化报错
Initializing MySQL database: 2020-05-06T03:24:10.136060Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-05-06T03:24:10.138005Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.
2020-05-06T03:24:10.138023Z 0 [ERROR] Aborting
第二个错误是有第一个问题造成的
7.修改/etc/my.cnf文件
注：首次创建必须要配置my.cnf这五个文件，成功后可以把配置去除，然后将所有的.pem文件放在datadir目录下这里是/var/lib/mysql
添加这五个变量

sha256_password_private_key_path=/app/software/mysql57/mykey.pem 
sha256_password_public_key_path=/app/software/mysql57/mykey.pub 
ssl-ca=/app/software/mysql57/mysql/ca.pem 
ssl-cert=/app/software/mysql57/mysql/server-cert.pem 
ssl-key=/app/software/mysql57/mysql/server-key.pem 

创建共有私有key

openssl genrsa -out mykey.pem 1024 
openssl rsa -in mykey.pem -pubout -out mykey.pub 
chmod 400 mykey.pem 
chmod 444 mykey.pub 
chown mysql:mysql mykey.pem 
chown mysql:mysql mykey.pub 

创建另八个文件

/usr/bin/mysql_ssl_rsa_setup --datadir=/app/software/mysql57/mysql --user=mysql 
chown mysql:mysql /app/software/mysql57/mysql -R 
ll /app/software/mysql57/mysql -rt 

其实这八个文件已经包含了 mykey.pem，mykey.pub 我未试是否可以引用（private_key.pem,public_key.pem）
8.再次重启，先删除数据文件

rm -fr /var/lib/mysql 
service mysqld start 

这次时间有点长，感觉要成功额 失败，再来一次成功了

9.获取临时密码
cat /var/log/mysqld.log | grep password

10.修改临时密码，设置客户端访问权限
mysql -uroot -p;

alter user 'root'@'localhost' identified by 'bw.2018'; 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'bw.2018' WITH GRANT OPTION; 

'%'代表任意地址，也可以指定IP
添加gmp用户

create user 'gmp'@'%' identified by 'bw.2018'; 

设置权限

GRANT privileges ON databasename.tablename TO 'username'@'host'; 

说明:
• privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如要授予所的权限则使用ALL
databasename：数据库名
• tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*

GRANT ALL PRIVILEGES ON *.* TO 'gmp'@'%' IDENTIFIED BY 'bw.2018' WITH GRANT OPTION; 

11.修改防火墙

vi /etc/sysconfig/iptables 

在-A RH-Firewall-1-INPUT -j REJECT –reject-with icmp-host-prohibited之前，添加
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
12.重启防火墙

service iptables restart 

13.兼容程序
有时很多从5.6升级到5.7时，为了语法兼容，大部分都会选择调整sql_mode，使其保持跟5.6一致，为了尽量兼容程序。

TRUNCATE TABLE kline_spot; 

[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
/etc/my.cnf 文件添加

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 

重启：

service mysqld restart 

再次执行

TRUNCATE TABLE kline_spot;