Linux CentOS 安装 MySQL5.7

移除自带 MySQL

• 检测系统是否自带安装MySQL

yum list installed | grep mysql

• 移除自带MySQL

yum -y remove mysql-libs.x86_64

• 检测系统是否自带安装MariaDB（CentOS7自带MariaDB）

yum list installed | grep mariadb

• 移除自带MariaDB

yum -y remove mariadb-libs.x86_64

• 查看MySQL服务

chkconfig --list | grep -i mysql

• 删除MySQL服务

chkconfig --del mysql

• 查询是否还有MySQL文件并删除

whereis mysql

列出MySQL可安装版本

yum list | grep mysql

发现最新只有5.1版本

mysql-libs.x86_64                           5.1.73-8.el6_8              @anaconda-CentOS-201806291108.x86_64/6.10
apr-util-mysql.x86_64                       1.3.9-3.el6_0.1             base    
bacula-director-mysql.x86_64                5.0.0-13.el6                base    
bacula-storage-mysql.x86_64                 5.0.0-13.el6                base    
dovecot-mysql.x86_64                        1:2.0.9-22.el6              base    
freeradius-mysql.x86_64                     2.2.6-7.el6_9               base    
libdbi-dbd-mysql.x86_64                     0.8.3-5.1.el6               base    
mod_auth_mysql.x86_64                       1:3.0.0-11.el6_0.1          base    
mysql.x86_64                                5.1.73-8.el6_8              base    
mysql-bench.x86_64                          5.1.73-8.el6_8              base    
mysql-connector-java.noarch                 1:5.1.17-6.el6              base    
mysql-connector-odbc.x86_64                 5.1.5r1144-7.el6            base    
mysql-devel.i686                            5.1.73-8.el6_8              base    
mysql-devel.x86_64                          5.1.73-8.el6_8              base    
mysql-embedded.i686                         5.1.73-8.el6_8              base    
mysql-embedded.x86_64                       5.1.73-8.el6_8              base    
mysql-embedded-devel.i686                   5.1.73-8.el6_8              base    
mysql-embedded-devel.x86_64                 5.1.73-8.el6_8              base    
mysql-libs.i686                             5.1.73-8.el6_8              base    
mysql-server.x86_64                         5.1.73-8.el6_8              base    
mysql-test.x86_64                           5.1.73-8.el6_8              base    
pcp-pmda-mysql.x86_64                       3.10.9-9.el6                base    
php-mysql.x86_64                            5.3.3-49.el6                base    
qt-mysql.i686                               1:4.6.2-28.el6_5            base    
qt-mysql.x86_64                             1:4.6.2-28.el6_5            base    
rsyslog-mysql.x86_64                        5.8.10-12.el6               base    
rsyslog7-mysql.x86_64                       7.4.10-7.el6                base

安装 MySQL5.7 版本

下载地址

https://dev.mysql.com/downloads/mysql/

选择对应版本及平台

查看CentOS版本

cat /etc/redhat-release

查看Ubuntu版本

cat /etc/lsb-release

• 默认最新版本，点击Looking for the latest GA version?选择5.7版本
• CentOS6选择：Red Hat Enterprise Linux 6 / Oracle Linux 6 (x86, 64-bit)
• CentOS7选择：Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit)
• 拷贝RPM Package, MySQL Server、RPM Package, Client Utilities、RPM Package, MySQL Configuration下载地址

下载 rpm

CentOS6

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.27-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.27-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.27-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.27-1.el6.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.27-1.el6.x86_64.rpm

CentOS7

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-common-5.7.27-1.el7.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-5.7.27-1.el7.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-client-5.7.27-1.el7.x86_64.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-community-server-5.7.27-1.el7.x86_64.rpm

安装 MySQL

MySQL Server依赖MySQL Common、MySQL Libs、MySQL Client，所以先安装mysql-community-common、mysql-community-libs和mysql-community-server。

依次安装mysql-community-common、mysql-community-libs、mysql-community-client、mysql-community-server。

CentOS6

yum install -y mysql-community-common-5.7.27-1.el6.x86_64.rpm
yum install -y mysql-community-libs-5.7.27-1.el6.x86_64.rpm
yum install -y mysql-community-libs-compat-5.7.27-1.el6.x86_64.rpm
yum install -y mysql-community-client-5.7.27-1.el6.x86_64.rpm
yum install -y mysql-community-server-5.7.27-1.el6.x86_64.rpm

CentOS7

yum install -y mysql-community-common-5.7.27-1.el7.x86_64.rpm
yum install -y mysql-community-libs-5.7.27-1.el7.x86_64.rpm
yum install -y mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm
yum install -y mysql-community-client-5.7.27-1.el7.x86_64.rpm
yum install -y mysql-community-server-5.7.27-1.el7.x86_64.rpm

创建文件夹

mkdir -p /home/mysql/{data,log,temp}
chown -R mysql mysql
chgrp -R mysql mysql
cd /home/mysql

启动 MySQL 服务

service mysqld start

查看并修改 MySQL 临时密码

查看临时密码

临时密码必须修改，否则会报：ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

grep 'temporary password' /var/log/mysqld.log

输出：

2019-09-23T18:24:15.254058Z 1 [Note] A temporary password is generated for root@localhost: ;lr!+ll4/KHb

使用临时密码登录

mysql -uroot -p

设置密码规则

set global validate_password_policy=0;
set global validate_password_length=1;

修改 root 用户密码

alter user 'root'@'localhost' identified by '123456';

解决：1130 - Host '192.168.200.1' is not allowed to connect to this MySQL server

update mysql.user set host='%' where user='root';
flush privileges;

查看配置文件位置

my.cnf位置。

select @@basedir;

MySQL 安全设置

mysql_secure_installation

重置密码

简单密码会报错，默认长度必须8位及以上：... Failed! Error: Your password does not satisfy the current policy requirements

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 
 ... Failed! Error: Your password does not satisfy the current policy requirements

New password:

是否删除匿名用户

By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

是否禁用 root 用户远程登录

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

是否删除 MySQL 用于测试的数据库

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

是否重新加载权限表

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

进入 MySQL 命令行

mysql -uroot -p

基础配置

Vim下输入:set paste进入粘贴模式，否则可能会出现全是注释。

:set paste!还原为插入模式。

[client]
port=3306
socket=/home/mysql/data/mysql.sock

[mysqld]

########################## 
##### Basic Settings ##### 
########################## 

user=mysql
port=3306
server_id=1
datadir=/home/mysql/data
tmpdir=/home/mysql/temp
socket=/home/mysql/data/mysql.sock
pid_file=/home/mysql/data/mysqld.pid
log_error=/home/mysql/mysqld.log


default_time_zone='+8:00'
character_set_server=utf8
collation_server=utf8_general_ci

init_connect='SET NAMES utf8'
lower_case_table_names=1
skip_name_resolve=OFF


transaction_isolation=READ-COMMITTED



########################
##### Log Settings #####
########################

slow_query_log_file=/home/mysql/log/slow.log
general_log_file=/home/mysql/log/general.log
relay_log=/home/mysql/log/relay.log
relay_log_index=/home/mysql/log/relay.index
log_bin=/home/mysql/log/bin.log
log_bin_index=/home/mysql/log/binlog.index
binlog_format=ROW
binlog_rows_query_log_events=ON
binlog_cache_size=1M
max_binlog_size=1024M
log_bin_trust_function_creators=ON
innodb_flush_log_at_trx_commit=1
sync_binlog=1
expire_logs_days=0

slow_query_log=ON
log_queries_not_using_indexes=ON
log_slow_admin_statements=ON
log_slow_slave_statements=ON
log_throttle_queries_not_using_indexes=10
long_query_time=5
min_examined_row_limit=100

参考

MySQL官方Linux安装教程：https://dev.mysql.com/doc/refman/5.7/en/linux-installation-yum-repo.html