Press "Enter" to skip to content

CentOS7安装配置MySQL

zocodev 2
文章目录
  1. CentOS7添加MySQL官方源
  2. CentOS7安装MySQL 5.7/5.8/8.0
  3. MySQL初始化设置
  4. MySQL新建数据库并指定为UTF8字符集
  5. MySQL新建用户并赋予数据库操作权限
  6. CentOS7系统开启MySQL远程访问

最近公司一个项目要用到MySQL,因为涉及到MySQL直接对JSON的存储、索引及查询,所以对MySQL的版本要求为MySQL 5.7及以上,安装及配置过程发现和之前的版本有点不一样,这里做点笔记记录一下。

由于CentOS7官方源并没有MySQL,而自带的MariaDB版本又太低,作为一个包管理器支持党,能用二进制包解决的,肯定不会手贱去编译,恰好MySQL官方给我们提供了MySQL Server的源,我们只需要简单几步就能添加好。

CentOS7添加MySQL官方源

添加MySQL的yum源其实很简单,两行命令就能搞定:

wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum localinstall mysql57-community-release-el7-11.noarch.rpm

以上下载地址如果有变,到这里查看最新的就行:https://dev.mysql.com/downloads/repo/yum/

如果上述命令执行无报错的话,可以用下面的命令查看有哪些版本可选择安装:

[root@LocalServer emqx]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community   disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community   disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-connectors-community/x86_64  MySQL Connectors Community    enabled:     74
mysql-connectors-community-source  MySQL Connectors Community -  disabled
mysql-tools-community/x86_64       MySQL Tools Community         enabled:     74
mysql-tools-community-source       MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64         MySQL Tools Preview           disabled
mysql-tools-preview-source         MySQL Tools Preview - Source  disabled
mysql55-community/x86_64           MySQL 5.5 Community Server    disabled
mysql55-community-source           MySQL 5.5 Community Server -  disabled
mysql56-community/x86_64           MySQL 5.6 Community Server    disabled
mysql56-community-source           MySQL 5.6 Community Server -  disabled
mysql57-community/x86_64           MySQL 5.7 Community Server    enabled:    307
mysql57-community-source           MySQL 5.7 Community Server -  disabled
mysql80-community/x86_64           MySQL 8.0 Community Server    disabled
mysql80-community-source           MySQL 8.0 Community Server -  disabled

可以看到,除了MySQL 5.7,MySQL官方源也提供别的版本,比如说5.5、5.6、8.0,如果对别的版本有需求,安装需要的版本就行,只需用yum-config-manager启用或禁用相关的源即可,比如说禁用MySQL5.7启用MySQL5.6:

yum-config-manager --disable mysql57-community
yum-config-manager --enable mysql56-community

CentOS7安装MySQL 5.7/5.8/8.0

确定之后,可以安装MySQL Server了:

yum install mysql-community-server

只需上面一行命令即可安装好,安装过程不需要人工干预,直接就完成了安装过程。

其中,和别的安装过程有点不一样的是,MySQL Server的root密码也在安装过程中被设置成了随机密码,如果安装之后发现不知道MySQL的root密码而无法登录千万别方,可以通过下述命令找到MySQL的root临时密码:

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

2019-01-17T03:04:39.023156Z 1 [Note] A temporary password is generated for root@localhost: oW=-)jtRbxxx

其中,localhost冒号后面的字符串就是MySQL的临时root密码了。

MySQL初始化设置

找到MySQL的root密码之后,建议对MySQL进行初始化设置,自带的命令如下:

[root@LocalServer emqx]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:  ##输入上面的临时root密码

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

New password: ##设置新密码

Re-enter new password:  ##重复密码
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : #是否更改root密码

 ... skipping.
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) : ##是否移除匿名用户

 ... skipping.


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) : y ##是否禁止root远程登录
Success.

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) : y ##是否删除测试数据库
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

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) : y ##是否立即刷新权限
Success.

All done!

这里有一点需要注意的是,由于validate_password的存在,所更改的密码不能太简单,必须符合validate_password要求才能更改成功,根据官方文档,MySQL密码要求是:
至少包含一个大写字母,一个小写字母,一个数字,一个特殊符号,且不能少于8位

当然如果觉得麻烦,也可以把这个插件关闭,虽然我不建议:

vim /etc/my.conf

[mysqld]
validate_password=Off

MySQL 5.7及以上版本的安装及初步配置到这一步就完成了,但是出于实际需求,我后面还有一部分是我的MySQL使用过程的记录。

MySQL新建数据库并指定为UTF8字符集

出于兼容性考虑,我们最好在新建数据库的时候指定字符集,这样在后面的开发中可以减少很多莫名其妙的问题:

create database if not exists zocodev default character set utf8 collate utf8_unicode_ci;

当然,如果有emoji需求,也可以指定为utf8mb4字符集:

create database if not exists zocodev default character set utf8mb4 collate utf8mb4_unicode_ci;

MySQL新建用户并赋予数据库操作权限

出于安全,最好不要使用root账户来操作自定义的数据库,一个用户指定一个数据库比较合适。比如说用来建网站,可以一个WordPress网站新建一个数据库及对应的数据库用户。

新建MySQL用户并设置密码:

create user zocodev_user@'localhost' IDENTIFIED BY 'ABCabc123@@';

如果需要允许新建的MySQL用户可以远程登录,可以把上面的localhost替换成%,也就是:

create user zocodev_user@'%' IDENTIFIED BY 'ABCabc123@@';

需要注意的是,指定密码也需要符合上述说的validate_password插件的要求,否则会报如下错误:

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

然后赋予新建的MySQL用户指定数据库的操作权限:

grant all privileges on zocodev.* to zocodev_user@localhost identified by 'ABCabc123@@';

赋予权限之后,需要刷新一下权限,当然,重启一下MySQL Server也可以:

flush privileges;

如果没有意外,就能用上面新建的用户及对应的密码登录MySQL及对指定的数据库进行操作了,命令如下:

[root@LocalServer emqx]# mysql -u zocodev_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.24 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| zocodev            |
+--------------------+
2 rows in set (0.00 sec)

mysql>

针对MySQL创建新用户,这里有更详细的介绍:
https://dev.mysql.com/doc/refman/5.7/en/create-user.html

CentOS7系统开启MySQL远程访问

因为我们的MySQL不一定是安装在本地,可能是内网或者别的机器上,有的时候就有了远程访问需求,下面是配置CentOS7系统下开启MySQL远程访问的步骤。

要开启CentOS7下的MySQL远程访问,需要满足如下三个要求:
1、MySQL Server监听的IP可以被外界访问,比如说:192.168.1.x或者别的IP地址。
2、用于远程登录的用户需要指定允许在别的IP地址登录。新建用户的时候指定允许登录地址为%,SQL语句参考上面新建用户部分。
3、开放了对应的端口。

在MySQL配置文件的[mysqld]添加一行,指定MySQL监听IP:

vim /etc/my.cnf

[mysqld]
bind-address=0.0.0.0

关于MySQL绑定IP,可以参考这里:https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_bind-address
添加了之后,还需要重启MySQL:

systemctl restart mysqld

如果开启了防火墙的话,还需要开放MySQL监听端口:

firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

如果上面的命令都没问题,到这一步可以使用如下命令从本地连接远程的数据库了:

mysql -u username -h 1.2.3.4 -P 3306 -p
  1. leaf leaf

    你可以试一下rpm安装

    • zocodev zocodev

      能使用包管理器的情况下,不考虑直接安装rpm,毕竟直接安装RPM,脱离了包管理器,后续版本升级、依赖等管理不是很友好

发表评论

电子邮件地址不会被公开。 必填项已用*标注