Centos7MySQL5.7RPM安装

请注意,本文编写于 121 天前,最后修改于 120 天前,其中某些信息可能已经过时。

下载

[root@learn ~] wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
[root@learn ~] mkdir -p /usr/local/tools/mysql
[root@learn ~] mv ./mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar /usr/local/tools/mysql

安装

我已经把安装包放到/usr/local/tools/mysql路径下面

[root@learn ~] cd /usr/local/tools/mysql
[root@learn mysql] ls
mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar

查看是否已经安装mysql

rpm -qa | grep mysql

解压tar文件

  • 命令
tar -xvf mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
  • 执行结果
[root@learn mysql]# tar -xvf mysql-5.7.23-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-devel-5.7.23-1.el7.x86_64.rpm
mysql-community-client-5.7.23-1.el7.x86_64.rpm
mysql-community-server-5.7.23-1.el7.x86_64.rpm
mysql-community-test-5.7.23-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.23-1.el7.x86_64.rpm
mysql-community-minimal-debuginfo-5.7.23-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.23-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.23-1.el7.x86_64.rpm
mysql-community-common-5.7.23-1.el7.x86_64.rpm
mysql-community-embedded-5.7.23-1.el7.x86_64.rpm
mysql-community-devel-5.7.23-1.el7.x86_64.rpm
mysql-community-libs-5.7.23-1.el7.x86_64.rpm

卸载冲突的RPM组件

在我们安装mysql相关组件的时候,如果不将此冲突的组件删除掉,我们是安装不成功的。
我们可以先跳过这步,直接进入下步操作,在安装的过程中会有相应的提示。下面是我所碰到的一个提示:

# 安装mysql组件时出现的依赖错误
error: Failed dependencies:
        mysql-community-common(x86-64) >= 5.7.9 is needed by mysql-community-libs-5.7.23-1.el7.x86_64
        mariadb-libs is obsoleted by mysql-community-libs-5.7.23-1.el7.x86_64
        
# 卸载mariadb-libs时出现的依赖错误
error: Failed dependencies:
        libmysqlclient.so.18()(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64
        libmysqlclient.so.18(libmysqlclient_18)(64bit) is needed by (installed) postfix-2:2.10.1-6.el7.x86_64

可以看出,我们需要卸载postfixmariadb-libs相关的组件。卸载我们可以使用rpm -ev xxx

  • 查看postfixmariadb-libs
rpm -qa | grep postfix
rpm -qa | grep mariadb
  • 执行过程
[root@learn mysql]# rpm -qa | grep postfix
postfix-2.10.1-6.el7.x86_64

[root@learn mysql]# rpm -qa | grep mariadb
mariadb-libs-5.5.52-1.el7.x86_64
  • 卸载postfixmariadb-libs
rpm -ev postfix-2.10.1-6.el7.x86_64
rpm -ev mariadb-libs-5.5.52-1.el7.x86_64
  • 执行过程
[root@learn mysql]# rpm -ev postfix-2.10.1-6.el7.x86_64
Preparing packages...
postfix-2:2.10.1-6.el7.x86_64
[root@learn mysql]# rpm -ev mariadb-libs-5.5.52-1.el7.x86_64
Preparing packages...
mariadb-libs-1:5.5.52-1.el7.x86_64

安装依赖组件

在我进行安装msql-community-server-xxx的时候出现了下面的问题

[root@learn mysql]# rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm 
warning: mysql-community-server-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
        /usr/bin/perl is needed by mysql-community-server-5.7.23-1.el7.x86_64
        libaio.so.1()(64bit) is needed by mysql-community-server-5.7.23-1.el7.x86_64
        libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.23-1.el7.x86_64
        libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.23-1.el7.x86_64
        net-tools is needed by mysql-community-server-5.7.23-1.el7.x86_64
        perl(Getopt::Long) is needed by mysql-community-server-5.7.23-1.el7.x86_64
        perl(strict) is needed by mysql-community-server-5.7.23-1.el7.x86_64
[root@learn mysql]# 

由上面的错误可以看出我们需要安装相应的依赖

  1. libaio
  2. net-tools
  3. perl
  • 安装依赖
yum -y install libaio
yum -y install net-tools
yum -y install perl

安装mysql组件

经过上面的解压操作,我们得到了很多rpm文件。但是我们不需要这么多,我们只需要安装一下四个组件就可以了:

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

因为具有依赖关系,所以我们需要按顺序执行。
rpm -ivh 文件名就能安装相应的组件。
在执行server的时候,需要依赖安装一些工具组件,已经在上文有说明了

  • 安装命令
rpm -ivh mysql-community-common-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.23-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm
  • 执行过程
[root@learn mysql]# rpm -ivh mysql-community-common-5.7.23-1.el7.x86_64.rpm 
warning: mysql-community-common-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-common-5.7.23-1.e################################# [100%]
[root@learn mysql]# rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm 
warning: mysql-community-libs-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-libs-5.7.23-1.el7################################# [100%]
[root@learn mysql]# rpm -ivh mysql-community-client-5.7.23-1.el7.x86_64.rpm 
warning: mysql-community-client-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-client-5.7.23-1.e################################# [100%]
[root@learn mysql]# rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.23-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-community-server-5.7.23-1.e################################# [100%]

启动数据库

# 查看mysql是否启动
service mysqld status

# 启动mysql
service mysqld start

# 停止mysql
service mysqld stop

# 重启mysql
service mysqld restart

修改密码

MySQL安装完成之后我们是没有设置密码的,但是MySQL为我们设置了一个临时的密码,我们可以查看MySQL的日志知道这个临时密码。

  • 查看临时密码
grep password /var/log/mysqld.log
  • 执行过程
[root@learn ~]# grep password /var/log/mysqld.log 
2018-09-14T09:43:10.889769Z 1 [Note] A temporary password is generated for root@learn: 
scMQZxcon3?q

这样我们得知临时密码是:scMQZxcon3?q
然后我们用这个临时密码登录数据库。
数据库的密码需要满足以下条件:大小写字母,数字和特殊符号

  • 执行命令
# 登录mysql,之后需要输入密码
mysql -p

# 设置新密码
set password = password("123456");

# 退出当前登录
quit;
  • 执行过程
[root@learn ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set password = password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit;

开机启动

[root@learn ~]# vi /etc/rc.local
service mysqld start #在文件末尾添加

我的my.cnf

[client]
#password    = your_password
port        = 3306
socket        = /tmp/mysql.sock

[mysqld]
port        = 3306
socket        = /tmp/mysql.sock
datadir = /www/server/data
default_storage_engine = MyISAM
#skip-external-locking
#loose-skip-innodb
key_buffer_size = 32M
max_allowed_packet = 100G
table_open_cache = 128
sort_buffer_size = 768K
net_buffer_length = 4K
read_buffer_size = 768K
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 8M
thread_cache_size = 16
query_cache_size = 16M
tmp_table_size = 32M

#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id    = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 64M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 1
innodb_write_io_threads = 1
sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
character-set-server=utf8 
collation-server=utf8_general_ci 
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 768K
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

远程登录

[root@learn ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;

任意账号进入数据库问题

[root@learn ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mysql;
Database changed
mysql> DELETE FROM user WHERE user='';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> quit;

防火墙策略——端口访问

设置步骤

检测端口

如果远程连接不上MySQL,检查下默认的3306端口是否允许访问:

[root@learn /]# iptables -L -n|grep 3306
ACCEPT     tcp  --  0.0.0.0/0            0.0.0.0/0            state NEW tcp dpt:3306

允许访问

如果没有,则需要允许此端口访问:

[root@learn /]# iptables -I INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT

保存策略

这是临时的允许访问,需要保存防火墙的策略:

[root@learn /]# iptables-save > iptables.rules

禁用firewall

因为Centos7的改动,把firewall禁用:

[root@learn /]# systemctl stop firewalld.service 
[root@learn /]# systemctl mask firewalld.service 
Created symlink from /etc/systemd/system/firewalld.service to /dev/null.

安装iptables

安装iptables-services:

[root@learn /]# yum install iptables-services.x86_64 -y
Loaded plugins: fastestmirror, langpacks
base                                                                | 3.6 kB  00:00:00     
extras                                                              | 3.4 kB  00:00:00     
mysql-connectors-community                                          | 2.5 kB  00:00:00     
mysql-tools-community                                               | 2.5 kB  00:00:00     
mysql56-community                                                   | 2.5 kB  00:00:00     
updates                                                             | 3.4 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirrors.yun-idc.com
 * extras: mirrors.cn99.com
 * updates: mirrors.zju.edu.cn
Resolving Dependencies
--> Running transaction check
---> Package iptables-services.x86_64 0:1.4.21-17.el7 will be installed
--> Processing Dependency: iptables = 1.4.21-17.el7 for package: iptables-services-1.4.21-17.el7.x86_64
--> Running transaction check
---> Package iptables.x86_64 0:1.4.21-16.el7 will be updated
---> Package iptables.x86_64 0:1.4.21-17.el7 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================
 Package                     Arch             Version                 Repository      Size
===========================================================================================
Installing:
 iptables-services           x86_64           1.4.21-17.el7           base            50 k
Updating for dependencies:
 iptables                    x86_64           1.4.21-17.el7           base           426 k

Transaction Summary
===========================================================================================
Install  1 Package
Upgrade             ( 1 Dependent package)

Total download size: 476 k
Downloading packages:
No Presto metadata available for base
(1/2): iptables-services-1.4.21-17.el7.x86_64.rpm                   |  50 kB  00:00:00     
(2/2): iptables-1.4.21-17.el7.x86_64.rpm                            | 426 kB  00:00:01     
-------------------------------------------------------------------------------------------
Total                                                      251 kB/s | 476 kB  00:00:01     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Updating   : iptables-1.4.21-17.el7.x86_64                                           1/3 
  Installing : iptables-services-1.4.21-17.el7.x86_64                                  2/3 
  Cleanup    : iptables-1.4.21-16.el7.x86_64                                           3/3 
  Verifying  : iptables-services-1.4.21-17.el7.x86_64                                  1/3 
  Verifying  : iptables-1.4.21-17.el7.x86_64                                           2/3 
  Verifying  : iptables-1.4.21-16.el7.x86_64                                           3/3 

Installed:
  iptables-services.x86_64 0:1.4.21-17.el7                                                 

Dependency Updated:
  iptables.x86_64 0:1.4.21-17.el7                                                          

Complete!

防火墙开机启动

安装成功后,设置开机启动:

[root@learn /]# systemctl enable iptables.service 
Created symlink from /etc/systemd/system/basic.target.wants/iptables.service to /usr/lib/systemd/system/iptables.service.

加载策略

重新加载策略:

[root@learn /]# iptables-restore iptables.rules

初始化

策略初始化保存:

[root@learn /]# /usr/libexec/iptables/iptables.init save
iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]

总结

分成两部分,一是安装iptables-service,二是修改策略。

安装iptables-service

  1. 禁用firewall: systemctl stop firewalld.service / systemctl mask firewalld.service;
  2. 安装iptables-services:yum install iptables-services;
  3. 开机启动:systemctl enable iptables.service;

修改策略

  1. 检测端口访问:iptables -L -n|grep 3306;
  2. 允许访问:iptables -I INPUT -p tcp -m state –state NEW -m tcp –dport 3306 -j ACCEPT;
  3. 保存策略:iptables-save > iptables.rules;
  4. 加载策略:iptables-restore iptables.rules;
  5. 初始化策略:/usr/libexec/iptables/iptables.init save;