RedHat7上安装MySQL5.7.16

1、查看系统中是否已将安装MySQL，如果安装了，需要卸载。

[root@chenguo etc]#rpm -qa|grep -i mysql

2、创建用户和组

[root@chenguo ~]#groupadd mysql

[root@chenguo ~]#useradd -r -g mysql -p root mysql

3、通过SSH将mysql安装包放到/usr/local目录下面，并进行解压操作。

[chenguo@chenguo ~]$cd /usr/local/

[chenguo@chenguo local]$tar -xvf mysql-5.7.16-1.el7.x86_64.rpm-bundle.tar

解压之后出现如下：

mysql-community-libs-compat-5.7.16-1.el7.x86_64.rpm

mysql-community-devel-5.7.16-1.el7.x86_64.rpm

mysql-community-minimal-debuginfo-5.7.16-1.el7.x86_64.rpm

mysql-community-libs-5.7.16-1.el7.x86_64.rpm

mysql-community-common-5.7.16-1.el7.x86_64.rpm

mysql-community-embedded-compat-5.7.16-1.el7.x86_64.rpm

mysql-community-test-5.7.16-1.el7.x86_64.rpm

mysql-community-embedded-devel-5.7.16-1.el7.x86_64.rpm

mysql-community-server-minimal-5.7.16-1.el7.x86_64.rpm

mysql-community-server-5.7.16-1.el7.x86_64.rpm

mysql-community-client-5.7.16-1.el7.x86_64.rpm

mysql-community-embedded-5.7.16-1.el7.x86_64.rpm

4、安装时候会提示与已经安装的RPM包有冲突，所以我们先卸载一些RPM包，要卸载哪些呢？我们要卸载的是包含有mariadb关键字的RPM包，执行命令：

[chenguo@chenguo local]$ rpm -qa|grep mariadb

mariadb-libs-5.5.41-2.el7_0.x86_64

使用命令卸载：

[chenguo@chenguo local]$su -

[root@chenguo ~]#rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps

或者使用：

[root@chenguo ~]#rpm -e --nodeps mariadb-libs-5.5.41-2.el7_0.x86_64

5、进行MySQL包安装

[root@chenguo lib]#su -chenguo

[chenguo@chenguo lib]$cd /usr/local/

[chenguo@chenguo local]$rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm

warning: mysql-community-common-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

error: can't create transaction lock on /var/lib/rpm/.rpm.lock (Permission denied)

出现错误，改用root用户进行安装：

[chenguo@chenguo local]$su -

[root@chenguo local]#rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm

warning: mysql-community-common-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-common-5.7.16-1.e################################# [100%]

[root@chenguo local]#rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm

warning: mysql-community-libs-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-libs-5.7.16-1.el7################################# [100%]

[root@chenguo local]#rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm

warning: mysql-community-client-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-client-5.7.16-1.er################################# [100%])

[root@chenguo local]#rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm

warning: mysql-community-server-5.7.16-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql-community-server-5.7.16-1.e################################# [100%]

6、命令执行完毕后，mysql就安装完成，下面需要做的就是对mysql 进行初始化

[root@chenguo bin]#cd /usr/local/bin

[root@chenguo bin]#mysqld --initialize --user=mysql

[root@chenguo bin]#mysqld --initialize-insecure --user=mysql

2016-11-18T08:46:09.910921Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2016-11-18T08:46:09.912342Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

2016-11-18T08:46:09.912368Z 0 [ERROR] Aborting

解决办法：

进入/var/lib/目录中查看是否有mysql目录，有的话进行删除：

[root@chenguo bin]#cd /var/lib

[root@chenguo lib]#rm -rf mysql

[root@chenguo local]#cd /usr/local/bin/

[root@chenguo bin]#mysqld --initialize-insecure --user=mysql

7、启动mysql服务

[root@chenguo lib]#systemctl start mysqld

Job for mysqld.service failed. See 'systemctl status mysqld.service' and 'journalctl -xn' for details.

启动失败解决办法：

首先，查看/var/lib/mysql-files目录是否存在：

[root@chenguo lib]#ls -ld /var/lib/mysql-files

如果不显示任何信息就表示不存在，执行命令：

[root@chenguo lib]#mkdir /var/lib/mysql-files

这时，我们试着执行命令：

[root@chenguo lib]#systemctl start mysqld

如果还出现和下图一样的提示，我们执行另一条命令：

[root@chenguo lib]#chown -R mysql:mysql /var/lib/mysql

依然出错。

查看日志信息：

[root@chenguo ~]# journalctl |grep mysql

启动失败也有可能是SElinux导致，关闭SElinux功能：

vim /etc/selinux/config 修改文件永久关闭

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - No SELinux policy is loaded.

SELINUX=enforcing

# SELINUXTYPE= can take one of three two values:

# targeted - Targeted processes are protected,

# minimum - Modification of targeted policy. Only selected processes are protected.

# mls - Multi Level Security protection.

SELINUXTYPE=targeted

SELINUX=permissive #修改此参数为permissive

查看系统日志：

[root@chenguo ~]# cd /var/log

[root@chenguo log]#cat mysqld.log

2016-11-22T02:51:32.860176Z 0 [ERROR] InnoDB: Operating system error number 13 in a file operation.

2016-11-22T02:51:32.860231Z 0 [ERROR] InnoDB: The error means mysqld does not have the access rights to the directory.

2016-11-22T02:51:32.860238Z 0 [ERROR] InnoDB: os_file_get_status() failed on './ibdata1'. Can't determine file permissions

2016-11-22T02:51:32.860246Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

2016-11-22T02:51:33.471773Z 0 [ERROR] Plugin 'InnoDB' init function returned error.

2016-11-22T02:51:33.471974Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2016-11-22T02:51:33.472096Z 0 [ERROR] Failed to initialize plugins.

2016-11-22T02:51:33.472245Z 0 [ERROR] Aborting

[root@chenguo bin]#ls -ld /usr/sbin/mysqld

-rwxr-xr-x. 1 root root 241273154 Sep 28 13:35 /usr/sbin/mysqld

[root@chenguo bin]#chown -R mysql:mysql /usr/sbin/mysqld

启动之后依然报错

还原权限：

[root@chenguo bin]#chown -R root:root /usr/sbin/mysqld

在重启了系统之后，使用：

[root@chenguo lib]#systemctl start mysqld

居然神奇的成功了，搞不懂。应该是最后的vim /etc/selinux/config 修改文件永久关闭，这个操作实现的

8、允许mysql远程访问

[root@chenguo ~]#mysql

mysql> use mysql;

mysql> select host, user from user;

mysql> update user set host = '%' where user = 'root';

mysql> select host, user from user;

9、退出mysql

mysql> exit

10、查看和修改数据库编码

[root@chenguo ~]#mysql

mysql> use mysql;

mysql> select Host,user from user where User='root';

mysql> show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | latin1 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | latin1 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.01 sec)

设置数据库的编码：

mysql> exit

[root@chenguo ~]#vi /etc/my.cnf

在其中加入如下内容：

character-set-server=utf8

collation-server=utf8_general_ci

如果要修改其他的属性可以继续添加：

character-set-server=utf8

collation-server=utf8_general_ci

max_connections=1000

table_open_cache=6000

thread_cache_size=50

open_files_limit=8000

event_scheduler=ON

 

group_concat_max_len=999999999999

拷贝my.cnf文件内容：

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#

# Remove leading # and set to the amount of RAM for the most important data

# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

#

# Remove leading # to turn on a very important data integrity option: logging

# changes to the binary log between backups.

# log_bin

#

# Remove leading # to set options mainly useful for reporting servers.

# The server defaults are faster for transactions and fast SELECTs.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

character-set-server=utf8

collation-server=utf8_general_ci

max_connections=1000

table_open_cache=6000

thread_cache_size=50

open_files_limit=8000

event_scheduler=ON

group_concat_max_len=999999999999

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

修改配置后需要重启mysql：

[root@chenguo ~]#systemctl stop mysqld

[root@chenguo ~]#systemctl restart mysqld

进入mysql，查看修改内容是否成功：

[root@chenguo ~]#mysql

mysql> use mysql

mysql> SHOW VARIABLES LIKE 'event_scheduler';

mysql> SHOW VARIABLES LIKE 'group_concat_max_len';

PS：当切换mysql的时候出现错误：

[root@chenguo ~]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

我的错误解决是直接将上面的第6步重新做一次

6、命令执行完毕后，mysql就安装完成，下面需要做的就是对mysql 进行初始化

[root@chenguo bin]# cd /usr/local/bin

[root@chenguo bin]# mysqld --initialize --user=mysql

[root@chenguo bin]# mysqld --initialize-insecure --user=mysql

2016-11-18T08:46:09.910921Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2016-11-18T08:46:09.912342Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

2016-11-18T08:46:09.912368Z 0 [ERROR] Aborting

解决办法：

进入/var/lib/目录中查看是否有mysql目录，有的话进行删除：

[root@chenguo bin]# cd /var/lib

[root@chenguo lib]# rm -rf mysql

[root@chenguo local]# cd /usr/local/bin/

[root@chenguo bin]# mysqld --initialize-insecure --user=mysql

修改root用户密码的操作：

[root@chenguo ~]# mysql

mysql> use mysql

mysql> update user set password=PASSWORD('root') where User='root';

出现如下错误：

ERROR 1054 (42S22): Unknown column 'password' in 'field list'

原因：mysql数据库中的user下已经没有password这个字段，而变成了authentication_string，因此修改语句为：

mysql> update user set authentication_string=PASSWORD('root') where User='root';

修改密码后，使用：

[root@chenguo ~]#systemctl stop mysqld

[root@chenguo ~]#systemctl restart mysqld

然后使用：

[root@chenguo ~]# mysql -u root -p

Enter password:

才能登录mysql

当通过IDE链接数据库出现下面的异常信息：

Access denied for user 'root'@'192.168.1.13' (using password: YES)

解决办法：

[root@chenguo ~]# mysql -uroot -proot

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

mysql> flush privileges;

mysql> exit

修改后，使用：

[root@chenguo ~]#systemctl stop mysqld

[root@chenguo ~]#systemctl restart mysqld

ps：当在Navicat中执行sql的时候，出现[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_mo...

解决办法：在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'

在sql_mode 中去掉only_full_group_by

解决问题！

注意：需要停止mysql服务，再修改文件，然后重启。