虚拟机环境安装MySQL

Centos7的MySQL安装

卸载mariaDb

mysql被oracle收购后为了防止mysql有可能变成闭源,因此mysql创始人maria就开源做了一个mariaDb, centos7是自带使用了这个数据库的.因此安装mysql之前,应当首先卸载mariadb数据库:

$ sudo yum list installed | grep mariadb    #检查mariadb是否已安装

$ sudo yum list installed | grep mariadb
mariadb-libs.x86_64                     1:5.5.56-2.el7                 @anaconda

$ sudo yum -y remove mariadb*    #全部卸载

同理如果是已经安装过其他版本的MySQL,安装新的版本之前也需要按照如上方法进行卸载。

安装MySQL

下载mysql的YUM源: https://dev.mysql.com/downloads/repo/yum/

YUM源的官方安装说明:https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/

下载源mysql的YUM源,官网提供的是最新的MySQL8.0的源,内含其他较低版本的源

wget -P /home/hadoop/softwares https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm  --no-check-certificate

由于我们是下载到/home/hadoop/softwares目录下,所以先切换到该目录下:

cd /home/hadoop/softwares

安装mysql的YUM源:

sudo rpm -ivh mysql80-community-release-el7-3.noarch.rpm

检查mysql的YUM源是否安装成功:

sudo yum repolist enabled | grep mysql

mysql-connectors-community/x86_64       MySQL Connectors Community           175
mysql-tools-community/x86_64            MySQL Tools Community                120
mysql80-community/x86_64                MySQL 8.0 Community Server           211

选择要启用的mysql版本

查看源里包含的mysql版本,执行:

mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community      禁用
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - So 禁用
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community      禁用
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - So 禁用
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community      禁用
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - So 禁用
mysql-connectors-community/x86_64  MySQL Connectors Community       启用:    175
mysql-connectors-community-source  MySQL Connectors Community - Sou 禁用
mysql-tools-community/x86_64       MySQL Tools Community            启用:    120
mysql-tools-community-source       MySQL Tools Community - Source   禁用
mysql-tools-preview/x86_64         MySQL Tools Preview              禁用
mysql-tools-preview-source         MySQL Tools Preview - Source     禁用
mysql55-community/x86_64           MySQL 5.5 Community Server       禁用
mysql55-community-source           MySQL 5.5 Community Server - Sou 禁用
mysql56-community/x86_64           MySQL 5.6 Community Server       禁用
mysql56-community-source           MySQL 5.6 Community Server - Sou 禁用
mysql57-community/x86_64           MySQL 5.7 Community Server       禁用
mysql57-community-source           MySQL 5.7 Community Server - Sou 禁用
mysql80-community/x86_64           MySQL 8.0 Community Server       启用:    211
mysql80-community-source           MySQL 8.0 Community Server - Sou 禁用

可以通过类似下面的语句来启动和禁用某些版本,比如这里是禁用默认启用的MySQL8.0的源,转而启用我们需要安装的MySQL5.7的源。

或者通过修改/etc/yum.repos.d/mysql-community.repo文件,改变默认安装的mysql版本。比如要安装5.7版本,将8.0源的enabled=1改成enabled=0,然后再将5.7源的enabled=0改成enabled=1即可。

sudo yum-config-manager --enable mysql57-community
sudo yum-config-manager --disable mysql80-community

要使用yum-config-manager命令, 需要先安装yum-utils: sudo yum install yum-utils

注意: 同一时间只允许enable一个MySQL版本。

如果只需要安装MySQL5.7的版本,也可以不看上面,直接本地安装指定的远程MySQL5.7源

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

查看当前的启用的 MySQL 版本:

yum repolist enabled | grep mysql

mysql-connectors-community/x86_64       MySQL Connectors Community           175
mysql-tools-community/x86_64            MySQL Tools Community                120
mysql57-community/x86_64                MySQL 5.7 Community Server           464

安装MySQL

sudo yum install -y mysql-community-server

已加载插件:fastestmirror
mysql-connectors-community                                             | 2.6 kB  00:00:00     
mysql-tools-community                                                  | 2.6 kB  00:00:00     
mysql57-community                                                      | 2.6 kB  00:00:00     
mysql57-community/x86_64/prima FAILED                                          
http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/repodata/cae8c564a11e5fca11dbe958c273358f206f1bba-primary.sqlite.bz2: [Errno 14] curl#7 - "Failed connect to repo.mysql.com:80; Connection refused"
正在尝试其它镜像。
mysql57-community/x86_64/primary_db                                    | 247 kB  00:00:00     
Loading mirror speeds from cached hostfile
 * base: mirrors.aliyun.com
 * extras: mirrors.aliyun.com
 * updates: mirrors.aliyun.com
正在解决依赖关系
--> 正在检查事务
---> 软件包 mysql-community-server.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在处理依赖关系 mysql-community-common(x86-64) = 5.7.32-1.el7,它被软件包 mysql-community-server-5.7.32-1.el7.x86_64 需要
--> 正在处理依赖关系 mysql-community-client(x86-64) >= 5.7.9,它被软件包 mysql-community-server-5.7.32-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 mysql-community-client.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在处理依赖关系 mysql-community-libs(x86-64) >= 5.7.9,它被软件包 mysql-community-client-5.7.32-1.el7.x86_64 需要
---> 软件包 mysql-community-common.x86_64.0.5.7.32-1.el7 将被 安装
--> 正在检查事务
---> 软件包 mysql-community-libs.x86_64.0.5.7.32-1.el7 将被 安装
--> 解决依赖关系完成

依赖关系解决

==============================================================================================
 Package                      架构         版本                 源                       大小
==============================================================================================
正在安装:
 mysql-community-server       x86_64       5.7.32-1.el7         mysql57-community       173 M
为依赖而安装:
 mysql-community-client       x86_64       5.7.32-1.el7         mysql57-community        25 M
 mysql-community-common       x86_64       5.7.32-1.el7         mysql57-community       308 k
 mysql-community-libs         x86_64       5.7.32-1.el7         mysql57-community       2.3 M

事务概要
==============================================================================================
安装  1 软件包 (+3 依赖软件包)

总下载量:201 M
安装大小:875 M

....

已安装:
  mysql-community-server.x86_64 0:5.7.32-1.el7                                                

作为依赖被安装:
  mysql-community-client.x86_64 0:5.7.32-1.el7  
  mysql-community-common.x86_64 0:5.7.32-1.el7 
  mysql-community-libs.x86_64 0:5.7.32-1.el7 

启动MySQL服务

systemctl start mysqld.service # 或 service mysqld start

配置开机启动

systemctl enable mysqld.service

查看运行状态

systemctl status mysqld.service

mysql      2574      1  1 23:49 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sBc3vZmP-1617158166557)(C:\Users\时年\AppData\Roaming\Typora\typora-user-images\image-20210327152522279.png)]

查看到进程信息

netstat -anpl  | grep mysql

查看端口,可以看出mysql server的进程mysqld所使用的默认端口即3306

$ sudo netstat -anpl | grep tcp
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1412/sshd         
tcp        0     52 192.168.186.103:22      192.168.186.1:54058     ESTABLISHED 2287/sshd: hadoop 
tcp6       0      0 :::3306                 :::*                    LISTEN      2574/mysqld       
tcp6       0      0 192.168.186.103:3888    :::*                    LISTEN      2060/java         
tcp6       0      0 :::22                   :::*                    LISTEN      1412/sshd         
tcp6       0      0 :::37791                :::*                    LISTEN      2060/java         
tcp6       0      0 :::2181                 :::*                    LISTEN      2060/java 

找到临时密码

但是要登陆mysql,我们需要root密码,这个密码是安装时随机生成在MySQL的服务器日志中的

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

2020-02-26T17:05:45.104999Z 1 [Note] A temporary password is generated for root@localhost: bl/!6qaU.wuX

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ueqB13Dk-1617158166561)(C:\Users\时年\AppData\Roaming\Typora\typora-user-images\image-20210327152629379.png)]

登录MySQL

# 回车后输入在日志中找到的临时root登录密码登录
mysql -u root -p 

问题: 日志里没有找到临时密码, 或者密码不能登录,可能的原因就是之前安装过MySQL

解决办法:

sudo rm -rf /var/lib/mysql

再重启服务后就可以在日志文件中找到重新生成的临时密码了

systemctl restart mysqld.service

$ cat /var/log/mysqld.log | grep "temporary password"
2020-02-26T17:05:45.104999Z 1 [Note] A temporary password is generated for root@localhost: bl/!6qaU.wuX
2020-08-01T16:07:26.941916Z 1 [Note] A temporary password is generated for root@localhost: x/ttqh=)_6Z/

也可以直接使用明文密码登陆,像这样

mysql -uroop -p"x/ttqh=)_6Z/"

初始密码我们可以这样操作,因为很快我们会将将其改为其他的密码。

但是正常使用时不建议这样 不安全

黑客可以使用history命令查看到你在命令行的输入获取到你的MySQL的root密码

另外MySQL报错在/var下面的log目录下可以查看


如果是虚拟机环境为了方便可以选择配置跳过密码验证

vim /etc/my.cnf

添加以下代码

default-authentication-plugin=mysql_native_password #配置文件本来就有去掉注释即可
symbolic-links=0
skip-grant-tables #跳过密码验证

第一次登陆后系统会很快提示你修改掉默认密码

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

尝试修改密码(虚拟机可以设置简单密码,如果是购买的服务器则需设置复杂密码并修改默认端口号)

mysql> alter user 'root'@'localhost' identified by 'Xiao@123';

注意:基于密码策略,所设置的密码必须要包含大小写字母、数字和字符。

密码安全策略

修改策略并设置简单密码

将策略要求置为0(LOW),长度要求置为1

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

关于密码安全策略:

通过show命令查看当前的策略

mysql> show variables like 'validate_password%';

+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

1) validate_password_policy:密码安全策略,默认MEDIUM策略

策略 检查规则
0 or LOW Length
1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters
2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

2)validate_password_dictionary_file:密码策略文件,策略为STRONG才需要

3)validate_password_length:密码最少长度

4)validate_password_mixed_case_count:大小写字符长度,至少1个

5)validate_password_number_count :数字至少1个

6)validate_password_special_char_count:特殊字符至少1个

这样以后就可以使用新密码登陆了

开启远程登陆

mysql命令的-h选项可以设置需要远程登陆mysql的服务器的ip地址

如果是本地的话可以使用127.0.0.1或者直接省略

但是远程访问因为安全原因默认是关闭的

mysql> select Host,User from user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
3 rows in set (0.00 sec)

这里可以看到host都是localhost,这就是因为这些账号只有本地访问的权限

我们需要将root的访问权限扩大都允许任意方式访问(包括远程访问)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY 'niit1234' WITH GRANT OPTION;

也可以创建一个专门用来支持远程访问的单独用户

GRANT ALL PRIVILEGES ON *.* TO 'remote'@'%' IDENTIFIED BY 'niit1234' WITH GRANT OPTION; 

如果你想允许用户myuser从ip为192.168.1.6的主机连接到mysql服务器,并使用mypassword作为密码

若你想限制能连接到mysql服务器的客户端所在的IP地址为’192.168.1.6’

并使用mypassword作为密码,则命令如下:

GRANT ALL PRIVILEGES ON . TO 'mysqluser'@'192.168.1.6'IDENTIFIED BY 'mypassword' WITH GRANT OPTION;  

使修改生效

现在虽然修改了远程访问权限,但是还没有生效

我们需要刷新权限(或者重启服务),当然直接在mysql执行环境刷新权限是更好的方法

mysql>FLUSH PRIVILEGES;

然后我们可以试试使用navicat远程连接虚拟机中mysql试试

navicat虽然是收费的还是可以下载到破解的版本

最后一点

如果你的服务器开启了防火墙也可能导致远程无法连接

因为mysql默认使用3306端口,我们需要开放这个端口给mysql客户端连接

修改编码

默认安装的设置对中文的支持不好,一般需要修改成utf8,但这里我们修改成utf8mb4编码,因为它是uft8的超集,减少更多乱码的问题,也是推荐使用的编码,比如需要插入emoji字符在数据库中,utf8的显示就会出现问题,而utf8mb4则没有问题。

首先查看当前的编码:

mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| 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/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)

为了修改编码,我们需要修改mysql的核心配置文件如下:

[hadoop@hadoop000 download]$ sudo vim /etc/my.cnf

# For advice on how to change settings please see
# 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

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

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

# 默认服务器内部操作字符集
character-set-server=utf8mb4
# 默认服务器内部操作字符集校对规则
collation-server=utf8mb4_general_ci
# 默认的存储引擎
default-storage-engine=InnoDB
# 初始化连接时设置以下字符集:
# character_set_client
# character_set_results
# character_set_connection
init_connect='set names utf8mb4'

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4
  • 30行以下为手动添加的配置

验证

mysql> show variables like '%char%';
+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8mb4                    |
| character_set_connection             | utf8mb4                    |
| character_set_database               | utf8mb4                    |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8mb4                    |
| character_set_server                 | utf8mb4                    |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+
9 rows in set (0.00 sec)
  • character_set_client 是指客户端发送过来的语句的编码
  • character_set_database 是指服务器内部使用的编码
  • character_set_connection 是指mysqld收到客户端的语句后,要转换到的编码
  • character_set_results 是指server执行语句后,返回给客户端的数据的编码
  • character_set_system是元数据编码,无需修改
  • character_set_filesystem是文件系统的编码,2进制存储最有效,不能修改

【END】

上一篇:cetnos7安装mysql5.7


下一篇:RPM包安装MYSQL