CentOS7 安装 MySQL5.7.X


安装前准备

MySQL安装需要准备root账户

卸载MariaDB相关组件

由于MariaDB与MySQL类似,在安装时候会提示与已经安装的RPM包有冲突,因此需要卸载一些包含有MariaDB关键字的RPM包。

执行rpm -qa | grep mysql检查需要卸载的包

执行rpm -qa | grep mariadb检查需要卸载的包

如发现存在mariadbmysql则使用rpm -e --nodeps xxx进行卸载

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -qa | grep mariadb # 检查mariadb
mariadb-libs-5.5.64-1.el7.x86_64

# 如有就进行卸载
[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -e --nodeps mariadb-libs-5.5.64-1.el7.x86_64

下载安装文件

这里我直接在官网拿到了MySQL5.7.28的下载地址,通过wget进行下载

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar

解压安装文件

这里解压文件以后,直接放到新文件夹mysql-5.7.28-1.el7.x86_64了,可以看到目录下面有很多rpm包

tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C mysql-5.7.28-1.el7.x86_64

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# ll
总用量 595272
-rw-r--r-- 1 7155 31415  45109364 930 16:04 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415    318768 930 16:04 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   7037096 930 16:04 mysql-community-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415  49329100 930 16:04 mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415  23354908 930 16:04 mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 136837816 930 16:04 mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   4374364 930 16:04 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   1353312 930 16:04 mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 208694824 930 16:05 mysql-community-server-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 133129992 930 16:05 mysql-community-test-5.7.28-1.el7.x86_64.rpm

开始安装

检查3306端口

MySQL默认使用3306端口,安装前查看端口是否被占用

netstat -anp|grep 3306

安装相应的rpm包

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

以上四个文件按照顺序依次安装,顺序不能错乱

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

以下为安装日志,只要之前的mariadbmysql卸载干净了,一般不会有问题

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
警告:mysql-community-common-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.7.28-1.e################################# [100%]
[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
警告:mysql-community-libs-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.7.28-1.el7################################# [100%]
[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
警告:mysql-community-client-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.28-1.e################################# [100%]
[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
警告:mysql-community-server-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-5.7.28-1.e################################# [100%]

后续工作

启动MySQL服务

systemctl start mysqld

查看MySQL服务状态

systemctl status mysqld

输出日志

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 三 2020-01-08 21:02:06 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 22483 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 22450 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 22486 (mysqld)
    Tasks: 27
   Memory: 193.8M
   CGroup: /system.slice/mysqld.service
           └─22486 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.468981Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.469015Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.470439Z 0 [Warning] CA certificate ca.pem is self signed.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.470543Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.471329Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.471412Z 0 [Note] IPv6 is available.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.471436Z 0 [Note]   - '::' resolves to '::';
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.471467Z 0 [Note] Server socket created on IP: '::'.
108 21:02:06 vm31_123 mysqld[22483]: 2020-01-08T13:02:06.485478Z 0 [Note] Event Scheduler: Loaded 0 events
108 21:02:06 vm31_123 systemd[1]: Started MySQL Server.

修改默认密码

查看MySQL安装成功以后生成的零时密码

sudo cat /var/log/mysqld.log |grep 'temporary password'

输出结果,可以看到我们的mysql零时密码是;2o1:2h%!ruT

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# sudo cat /var/log/mysqld.log |grep 'temporary password'
2020-01-08T12:21:09.914526Z 1 [Note] A temporary password is generated for root@localhost: ;2o1:2h%!ruT

使用零时密码登录修改

输入mysql -uroot -p以后会提示我们输入密码,这时候输入刚刚生成的零时密码;2o1:2h%!ruT即可登录

接下来就可以使用ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@123';(此处MySQL@123为数据库密码,根据需求自行设置更改)

[root@vm31_123 mysql-5.7.28-1.el7.x86_64]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28

Copyright (c) 2000, 2019, 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> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL@123';
Query OK, 0 rows affected (0.00 sec)

mysql>

设置外部IP访问MySQL

为了能够让外部访问数据库,需要将root的Host值改为’%’,具体步骤如下:

查看允许连接到本数据库的信息,执行命令select host,user from mysql.user;

默认root只能通过localhost连接,不能远程访问。

  • 选择数据库:use mysql;
  • 先执行:update user set Host='%' where User='root';
  • 再执行:flush privileges; # 刷新权限
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set Host='%' where User='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

当设置完host=%以后,就可以使用我们熟悉navicat或者sequel进行连接了

修改MySQL默认编码为utf8

当我们MySQL安装完成以后,默认编码不一定是utf8,虽然在建库建表的时候可以指定,但是始终不方便,那么接下来我们将MySQL默认编码设置为utf8

查看MySQL默认编码

使用show variables like 'character_set_%';

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| 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.00 sec)

可以看到character_set_server这里是latin1,并不是我们想要的utf8,这时候我们就需要编辑/etc/my.cnf文件进行设置

编辑my.cnf文件

sudo vim /etc/my.cnf进行编辑,加入以下内容,然后保存重启MySQL服务systemctl restart mysqld

注意:my.cnf中默认包含[mysqld],修改的时候就在原来的基础上改就好了,不要重复了

[client]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8

[mysql]
default-character-set=utf8

再次查看修改以后的编码

mysql> show variables like 'character_set_%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

到这里,MySQL的默认编码就是我么熟悉的utf8

忘记MySQL密码

当忘记MySQL密码或者MySQL密码错误是会提示错误ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

如果找不到MySQL密码时可以使用跳过mysql权限表启动的方式进行密码重置

  1. 停止MySQL服务

    systemctl stop mysqld
  2. 跳过mysql权限表启动

    /usr/bin/mysqld_safe --skip-grant-tables
  3. 无密码登录

    mysql -uroot
  1. 选择mysql数据库

    use mysql; 
  1. 重置密码

    update user set authentication_string=password('MySQL@123') where user='root'; 
  1. 刷新权限

    flush privileges;

常见问题

  1. 修改默认配置,当插入或更新的数据比较大时,需要修改/etc/my.cnf配置文件

    在[mysqlId]下加上 max_allowed_packet=20M (可以通过 mysql –help | grep my.cnf查找文件路径)

  1. 当重启服务器后,MySQL无法正常启动,遇到以下问题 Job for mysqld.service failed. See 'systemctl status mysqld.service' and 'journalctl -xn' for details. 查看日志,出现如下错误内容:

    [ERROR] /usr/sbin/mysqld: Can't create/write to file '/var/run/mysqld/mysqld.pid' (Errcode: 2 - No such file or directory)
    [ERROR] Can't start server: can't create PID file: No such file or directory
    Copy

    解决方案:

    ##授权
    chown mysql.mysql /var/run/mysqld/
    
    ##启动
    /etc/init.d/mysqld start

文章作者: 小张哥
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 小张哥 !
评论
 上一篇
Java中使用元组 Java中使用元组
元组(Tuple)是固定数量的不同类型的元素的组合。元组与集合的不同之处在于,元组中的元素类型可以是不同的,而且数量固定。元组的好处在于可以把多个元素作为一个单元传递。如果一个方法需要返回多个值,可以把这多个值作为元组返回,而不需要创建额外
2020-01-09
下一篇 
Java8 中的日期类序列化问题 Java8 中的日期类序列化问题
自从习惯了使用Java8 中的日期类以后,就已经完全抛弃了java.sql、java.util中的日期处理类,但是Java8中的日期类在序列化与反序列化时不是我们正常看到的标准格式,这里记录一下如何使用Gson进行序列化和反序列化时正常对J
2020-01-07
  目录