Linux下安装Mysql【CentOS7 】_linux mysql下载安装

一、Linux下安装Mysql-5.7.41【tar包下载安装】

Mysql：版本选择

• 5.7版本
  • 5.7.22
  • 5.7.30
• 8.0版本
  • 8.0.20开始评测
    安装方式：
• 一线大厂 ：源码功能修改与编译(百度)
• 二线(58，陌陌)：tar.gz方式，方便做打包的生产标准MySQL包管理

1.1.首先检查是否已经安装过mysql

rpm -qa | grep mysql
1

1.2.下载Linux版本的Mysql-5.7

Mysql官网：https://www.mysql.com/

下载网址：https://downloads.mysql.com/archives/community/

cat /etc/redhat-release
1

1.3.解压缩

首先使用第三方工具上传

# 创建一个文件夹,存放mysql
mkdir -p /usr/mysql

cd /usr/mysql

# 在当前目录下（mysql）下创建一个 mysql-5.7.41 文件夹
mkdir mysql-5.7.41

# 解压安装包到该目录下
tar -xvf mysql-5.7.41-1.el7.x86_64.rpm-bundle.tar -C mysql-5.7.41
1
2
3
4
5
6
7
8
9
10

[root@localhost mysql-5.7.41]# ls
mysql-community-client-5.7.41-1.el7.x86_64.rpm    
mysql-community-embedded-compat-5.7.41-1.el7.x86_64.rpm  
mysql-community-server-5.7.41-1.el7.x86_64.rpm
mysql-community-common-5.7.41-1.el7.x86_64.rpm    
mysql-community-embedded-devel-5.7.41-1.el7.x86_64.rpm   
mysql-community-test-5.7.41-1.el7.x86_64.rpm
mysql-community-devel-5.7.41-1.el7.x86_64.rpm     
mysql-community-libs-5.7.41-1.el7.x86_64.rpm
mysql-community-embedded-5.7.41-1.el7.x86_64.rpm  
mysql-community-libs-compat-5.7.41-1.el7.x86_64.rpm

1
2
3
4
5
6
7
8
9
10
11
12

1.4.安装执行 rpm 安装包需要先下载 openssl-devel 插件

解压后的文件都是 rpm 文件，因此需要用到 rpm 包资源管理器相关的指令安装这些 rpm 的安装包

yum install openssl-devel
1

1.5.安装 Mysql5.7 执行 rpm 安装包

安装完该插件之后，依次执行以下命令安装这些 rpm 包

yum install openssl-devel

rpm -ivh mysql-community-common-5.7.41-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm

yum remove mysql-libs 

rpm -ivh mysql-community-libs-compat-5.7.41-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-5.7.41-1.el7.x86_64.rpm

# 至此所有依赖已安装完毕，下面安装客户端和服务端

rpm -ivh mysql-community-client-5.7.41-1.el7.x86_64.rpm
yum -y install net-tools
rpm -ivh mysql-community-server-5.7.41-1.el7.x86_64.rpm

# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# yum remove openssl-devel
# yum remove net-tools
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

问题1

[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
错误：依赖检测失败：
        mysql-community-common(x86-64) >= 5.7.9 被 mysql-community-libs-5.7.41-1.el7.x86_64 需要
        mariadb-libs 被 mysql-community-libs-5.7.41-1.el7.x86_64 取代
1
2
3
4
5

解决方案1:清除之前安装过的依赖即可，最后重新安装下。

[root@localhost mysql-5.7.41]# yum remove mysql-libs 
1

问题2

[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-server-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
错误：依赖检测失败：
        net-tools 被 mysql-community-server-5.7.41-1.el7.x86_64 需要
1
2
3
4

解决方案2:

yum -y install net-tools
1

[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-common-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-common-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-5.7.41-1.e################################# [100%]
[root@localhost mysql-5.7.41]# 
[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-libs-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-libs-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-5.7.41-1.el7################################# [100%]
[root@localhost mysql-5.7.41]# 
[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-libs-compat-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-libs-compat-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-compat-5.7.4################################# [100%]
[root@localhost mysql-5.7.41]# 
[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-devel-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-devel-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-devel-5.7.41-1.el################################# [100%]
[root@localhost mysql-5.7.41]# 
[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-client-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-client-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-5.7.41-1.e################################# [100%]
[root@localhost mysql-5.7.41]# 
[root@localhost mysql-5.7.41]# rpm -ivh mysql-community-server-5.7.41-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.41-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-5.7.41-1.e################################# [100%]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

1.6.Mysql相关操作命令

# 启动 MySQL 服务
systemctl start mysqld

# 重启 MySQL 服务
systemctl restart mysqld

# 关闭 MySQL 服务
systemctl stop mysqld

#设置开机自启
systemctl enable mysqld

# 查看状态
systemctl status mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14

1.7.查看Mysql-5.7 临时密码

rpm 安装 MySQL 会自动生成一个随机密码

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

cat /var/log/mysqld.log
1
2
3

A temporary password is generated for root@localhost: 2yiNb5Pvum<4
账号：root
随机密码：2yiNb5Pvum<4

1.8.修改Mysql-5.7密码

# 登录
mysql -u root -p
2yiNb5Pvum<4

# 修改密码
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '123456';

ALTER  USER  'root'@'localhost'  IDENTIFIED BY 'Mysql.123456';

# 设置密码的验证强度等级
#Mysql 5.7 版本
# 将密码复杂度校验调整简单类型
# set global validate_password_policy=MEDIUM;
set global validate_password_policy=LOW;
# 设置密码最少位数限制为 4 位
set global validate_password_length=4;
# 查看 mysql 初始的密码策略
SHOW VARIABLES LIKE 'validate_password%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 查看 mysql5.7 初始的密码策略
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      |
+--------------------------------------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13

1.9.远程连接navicat–遇见问题

1.9.1.问题：1130 - Host ‘192.168.229.1’ is not allowed to connect to this MySQL server

自己安装在本地的mysql默认时不允许进行远程登陆，如果想要远程访问本地的数据库需要修改访问权限，将’root‘访问权限修改为’%‘

1.9.2.连接服务器–修改权限

# 连接服务器
mysql -u root -p

# 看当前所有数据库
show databases;

# 进入mysql数据库
use mysql;

# 查看mysql数据库中所有的表
show tables;

# 执行更新权限语句
# 其中“%”代表所有的地址，意味着所有的地址都可以访问“root”
update user set Host='%' where User='root';

# 查看权限是否修改成功
select host,user from user;
select host,user,plugin,authentication_string from user;

# 刷新服务器配置
FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.9.3.连接成功

1.10.卸载Mysql-5.7

# 停止Mysql
systemctl stop mysqld

# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 卸载上述查询的所有安装包
rpm -e mysql-community-libs-5.7.41-1.el7.x86_64 --nodeps
rpm -e mysql-community-devel-5.7.41-1.el7.x86_64 --nodeps
rpm -e mysql-community-common-5.7.41-1.el7.x86_64 --nodeps
rpm -e mysql-community-libs-compat-5.7.41-1.el7.x86_64 --nodeps
rpm -e mysql-community-client-5.7.41-1.el7.x86_64 --nodeps
rpm -e mysql-community-server-5.7.41-1.el7.x86_64 --nodeps

# 再次查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 删除MySQL的数据存放目录
rm -rf /var/lib/mysql

# 找出服务器中分散的mysql文件夹，查询残留的文件和相关依赖，并删除
find / -name mysql 
rm -rf [显示的文件夹路径，每个绝对路径中间用空格隔开]

# MySQL配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf下，MySQL日志文件通常位于/var/log/mysql下
# 删除mysql日志
rm -rf /var/log/mysqld.log
# 删除MySQL的配置文件备份
rm -rf /etc/my.cnf.rpmsave
# 删除mysql配置文件
rm -rf /etc/mysql/my.cnf
rm -rf /etc/my.cnf

# 清除MySQL用户和组
userdel mysql
groupdel mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

二、Linux下安装Mysql-8.0.34【tar包下载安装】

1.1.首先检查是否已经安装过mysql

rpm -qa | grep mysql
1

2.2.下载Linux版本的Mysql-8.0.34

Mysql官网：https://www.mysql.com/

下载网址：https://downloads.mysql.com/archives/community/

cat /etc/redhat-release
1

2.3.解压缩

首先使用第三方工具上传

# 创建一个文件夹,存放mysql
mkdir -p /usr/mysql

cd /usr/mysql

# 在当前目录下（mysql）下创建一个 mysql-5.7.41 文件夹
mkdir mysql-8.0.34

# 解压安装包到该目录下
tar -xvf mysql-8.0.34-1.el7.x86_64.rpm-bundle.tar -C mysql-8.0.34
1
2
3
4
5
6
7
8
9
10

[root@localhost mysql-8.0.34]# ls
mysql-community-client-8.0.34-1.el7.x86_64.rpm          
mysql-community-debuginfo-8.0.34-1.el7.x86_64.rpm        
mysql-community-icu-data-files-8.0.34-1.el7.x86_64.rpm  
mysql-community-server-8.0.34-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm  
mysql-community-devel-8.0.34-1.el7.x86_64.rpm            
mysql-community-libs-8.0.34-1.el7.x86_64.rpm            
mysql-community-server-debug-8.0.34-1.el7.x86_64.rpm
mysql-community-common-8.0.34-1.el7.x86_64.rpm          
mysql-community-embedded-compat-8.0.34-1.el7.x86_64.rpm  
mysql-community-libs-compat-8.0.34-1.el7.x86_64.rpm     
mysql-community-test-8.0.34-1.el7.x86_64.rpm
1
2
3
4
5
6
7
8
9
10
11
12
13

2.4.安装执行 rpm 安装包需要先下载 openssl-devel 插件

解压后的文件都是 rpm 文件，因此需要用到 rpm 包资源管理器相关的指令安装这些 rpm 的安装包

yum install openssl-devel
1

2.5.安装 Mysql8.0 执行 rpm 安装包

安装完该插件之后，依次执行以下命令安装这些 rpm 包

yum install openssl-devel
# yum install net-tools

rpm -ivh mysql-community-common-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-devel-8.0.34-1.el7.x86_64.rpm

# 至此所有依赖已安装完毕，下面安装客户端和服务端

rpm -ivh mysql-community-client-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.34-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.34-1.el7.x86_64.rpm

# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# yum remove openssl-devel
# yum remove net-tools
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.6.Mysql相关操作命令

# 启动 MySQL 服务
systemctl start mysqld

# 重启 MySQL 服务
systemctl restart mysqld

# 关闭 MySQL 服务
systemctl stop mysqld

#设置开机自启
systemctl enable mysqld

# 查看状态
systemctl status mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.7.查看Mysql-8.0 临时密码

rpm 安装 MySQL 会自动生成一个随机密码

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

cat /var/log/mysqld.log
1
2
3

A temporary password is generated for root@localhost: ij9uN+hmchw2
账号：root
随机密码：ij9uN+hmchw2

2.8.修改Mysql-8.0 密码

# 登录
mysql -u root -p
ij9uN+hmchw2

# 修改密码
ALTER  USER  'root'@'localhost'  IDENTIFIED BY '123456';

ALTER  USER  'root'@'localhost'  IDENTIFIED BY 'Mysql.123456';

# 设置密码的验证强度等级
#Mysql 8.0 版本
# 将密码复杂度校验调整简单类型
# set global validate_password.policy=MEDIUM;
set global validate_password.policy=LOW;
# 设置密码最少位数限制为 4 位
set global validate_password.length=4;
# 查看 mysql 初始的密码策略
SHOW VARIABLES LIKE 'validate_password%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

mysql> SHOW VARIABLES LIKE 'validate_password%';
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
1
2

提示ERROR 1820，让我们先修改密码后才能查看规则。

# 查看 mysql8.0 初始的密码策略
mysql> ALTER  USER  'root'@'localhost'  IDENTIFIED BY 'Mysql.123456';
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0      |
| validate_password.check_user_name               | ON     |
| 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      |
+-------------------------------------------------+--------+
8 rows in set (0.01 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.9.远程连接navicat–遇见问题

2.9.1.问题：1130 - Host ‘192.168.229.1’ is not allowed to connect to this MySQL server

自己安装在本地的mysql默认时不允许进行远程登陆，如果想要远程访问本地的数据库需要修改访问权限，将’root‘访问权限修改为’%‘

2.9.2.连接服务器–修改权限

# 连接服务器
mysql -u root -p

# 看当前所有数据库
show databases;

# 进入mysql数据库
use mysql;

# 查看mysql数据库中所有的表
show tables;

# 执行更新权限语句
# 其中“%”代表所有的地址，意味着所有的地址都可以访问“root”
update user set Host='%' where User='root';

# 查看权限是否修改成功
select host,user from user;
select host,user,plugin,authentication_string from user;

# 刷新服务器配置
FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2.9.3.连接成功

2.10.MySQL 8.0 配置mysql_native_password身份验证插件的password

方法一：

# 登录MySQL后输入
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';

FLUSH PRIVILEGES;
1
2
3
4

方法二：

# 编辑my.cnf文件，更改默认的身份认证插件。
vi /etc/my.cnf

# 在[mysqld]中添加下边的代码
default_authentication_plugin=mysql_native_password

# 然后重启MySQL
service mysqld restart
1
2
3
4
5
6
7
8

2.11.卸载Mysql-8.0

# 停止Mysql
systemctl stop mysqld

# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 卸载上述查询的所有安装包
rpm -e mysql-community-client-plugins-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-libs-compat-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-client-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-server-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-common-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-libs-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-devel-8.0.34-1.el7.x86_64 --nodeps
rpm -e mysql-community-icu-data-files-8.0.34-1.el7.x86_64 --nodeps


# 再次查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 找出服务器中分散的mysql文件夹，查询残留的文件和相关依赖，并删除
find / -name mysql 
rm -rf [显示的文件夹路径，每个绝对路径中间用空格隔开]

# MySQL配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf下，MySQL日志文件通常位于/var/log/mysql下
# 删除mysql日志
rm -rf /var/log/mysqld.log
# 删除MySQL的配置文件备份
rm -rf /etc/my.cnf.rpmsave
# 删除mysql配置文件
rm -rf /etc/mysql/my.cnf
rm -rf /etc/my.cnf

# 清除MySQL用户和组
userdel mysql
groupdel mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

三、Linux下–yum源安装Mysql

3.1.进入Mysql官网获取yum源安装包【无需登录账号】

# linux8 yum源
https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

# linux7 yum源
https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

# linux6 yum源
https://dev.mysql.com/get/mysql80-community-release-el6-10.noarch.rpm
1
2
3
4
5
6
7
8

3.2.通过yum搜索命令查询mysql相关的包数据

yum list | grep mysql*

yum list mysql*
1
2
3

3.3.确保系统中已经安装了wget和rpm工具

# 确保系统中已经安装了wget和rpm工具
sudo yum install wget rpm
1
2

3.4.安装Mysql的yum源【centos7为例】

# linux8 yum源
wget https://dev.mysql.com/get/mysql80-community-release-el8-9.noarch.rpm

# linux7 yum源
wget https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm

# linux6 yum源
wget https://dev.mysql.com/get/mysql80-community-release-el6-10.noarch.rpm
1
2
3
4
5
6
7
8

3.5.安装Mysql的yum源

cd /etc/yum.repos.d

rpm -ivh mysql80-community-release-el7-11.noarch.rpm
1
2
3

3.6.默认安装Mysql8.0，修改为Mysql5.7

vi mysql-community.repo
1

3.7.安装mysql-server

# 安装方式一
yum -y install mysql-community-server

# 安装方式二
# 安装MySQL服务器、MySQL客户端和MySQL依赖库
yum -y install mysql-server mysql mysql-libs

# 安装方式三
# 只想安装MySQL服务器
yum -y install mysql-server
1
2
3
4
5
6
7
8
9
10

3.8.启动mysql-server

# 查看Mysql版本信息
mysql --version

# 启动mysql
systemctl start mysqld

# 查看mysql状态信息
systemctl status mysqld
1
2
3
4
5
6
7
8

3.9.获取mysql-server初始登陆的密码

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

cat /var/log/mysqld.log
1
2
3

3.10.其余同上

3.11.卸载mysql

# 停止MySQL服务
systemctl stop mysqld

# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 删除已安装mysql
yum -y remove mysql80-community-release-el7-11.noarch
yum -y remove mysql-community-common-5.7.44-1.el7.x86_64
yum -y remove mysql-community-libs-compat-5.7.44-1.el7.x86_64
yum -y remove mysql-community-server-5.7.44-1.el7.x86_64
yum -y remove mysql-community-libs-5.7.44-1.el7.x86_64
yum -y remove mysql-community-client-5.7.44-1.el7.x86_64


# 查询 MySQL 的安装文件
rpm -qa | grep -i mysql

# 查看mysql相关的文件目录，并删除
find / -name mysql 
rm -rf [显示的文件夹路径，每个绝对路径中间用空格隔开]

# MySQL配置文件通常位于/etc/my.cnf或/etc/mysql/my.cnf下，MySQL日志文件通常位于/var/log/mysql下
# 删除mysql日志
rm -rf /var/log/mysqld.log
# 删除MySQL的配置文件备份
rm -rf /etc/my.cnf.rpmsave
# 删除mysql配置文件
rm -rf /etc/mysql/my.cnf
rm -rf /etc/my.cnf

# 清除MySQL用户和组
userdel mysql
groupdel mysql

cd ~
find / -name mysql*
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

3.12.Mysql的yum源仓库

http://repo.mysql.com/

3.13.配置MySQL的安全性–废掉 root 的远程登陆，只允许本地登陆

mysql_secure_installation 主要做了以下工作

为 root 用户设置密码
删除匿名账号
取消 root 用户远程登录
删除 test 库和对 test 库的访问权限
刷新授权表使修改生效
1
2
3
4
5
6
7

mysql_secure_installation
1

• 重置root账号的密码

新密码，长度为8至30个字符，必须同时包含大小写英文字母、数字和特殊符号。特殊符号可以是()` ~!@#$%^&*-+=|{}[]:;‘<>,.?/
1

• 删除匿名用户账号
• 禁止root账号远程登录
• 删除test库以及对test库的访问权限
• 重新加载授权表

四、建库建表–字符集修改【防止插入中文报错】

linux中mysql5.7默认的字符集是latin1，在插入中文时会报错，通常是由于数据库字符集和字符串编码不匹配导致的。

所以一般在配置好mysql时需要修改字符集为utf8【又叫utfmb3,一般开发够用，一个字符用3个字节表示】

或者utfmb4【一个字符用4个字节表示，如果存储emoji表情，必须用utfmb4】

4.1.建库建表–指定字符集

show databases;

CREATE DATABASE IF NOT EXISTS school CHARACTER SET utf8mb4;

use school;

DROP TABLE IF EXISTS user; 
CREATE TABLE `user` (
  `id` int(11) PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(50) NOT NULL COMMENT '姓名'
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ALTER TABLE user AUTO_INCREMENT = 1;

INSERT INTO user (name) VALUES('张三'),('李四'),('lily');

select * from user;

-- 查询表结构
desc user;

-- 表级
show table status from school like '%use%';

-- 列级
show full columns from user;

-- 查询指定表的建表语句
show create table user;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

4.2.建库建表–不指定字符集

DROP TABLE IF EXISTS customer;
CREATE TABLE customer(
	id INT AUTO_INCREMENT,
	name VARCHAR(200),
	PRIMARY KEY(id)
);

INSERT INTO customer (id,name) VALUES(1,'张三'),(2,'李四'),(3,'lily');
# ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'NAME' at row 1

-- 表级
show table status from school like '%customer%';

-- 列级
show full columns from customer;

-- 查询指定表的建表语句
show create table customer;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4.3.当前MySQL 支持的字符集

show charset;

show char set;
1
2
3

mysql> show char set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

4.4.将表字符集设置为utf8

# 对新建表、列，新插入数据有效
alter database 数据库名 character set utf8 collate utf8_bin;

#将表字符集设置为utf8
alter table 表名 default character set utf8 collate utf8_bin;
alter table customer character set utf8;

#将想要插入中文的字段的字符集改为utf8
alter table 表名 change 列名 新列名 char(20) character set utf8;
alter table customer change name nikname char(20) character set utf8;

INSERT INTO customer (id,nikname) VALUES(1,'张三'),(2,'李四'),(3,'lily');

#查看表的字符集和字段的字符集
show create table customer;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

4.5.数据库中查看MySQL状态

status;
1

4.6.查看当前数据库的字符集

show variables like "%server%";
show variables like '%char%';
1
2

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

mysql> show variables like "%server%";
+---------------------------------+--------------------------------------+
| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+
| character_set_server            | latin1                               |
| collation_server                | latin1_swedish_ci                    |
| innodb_ft_server_stopword_table |                                      |
| server_id                       | 1                                    |
| server_id_bits                  | 32                                   |
| server_uuid                     | 72c696aa-cee7-11ee-9fe1-000c29c833e8 |
+---------------------------------+--------------------------------------+
6 rows in set (0.00 sec)

# 默认是latin1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

五、修改Mysql字符集my.cnf【防止插入中文报错】

5.1.编辑my.cnf配置文件【永久修改字符集】

# 查找my.cnf配置文件
find / -name my.cnf

vi /etc/my.cnf
1
2
3
4

my.cnf内容添加

[client]
default_character_set=utf8
[mysqld]
default-storage-engine=INNODB
collation_server = utf8_general_ci
character_set_server = utf8
1
2
3
4
5
6

5.2.重启mysql

systemctl restart mysqld

show variables like "%server%";
show variables like '%char%';
1
2
3
4

六、mysql5.7使用group by语句：Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

6.1.报错信息

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated 
column 'company.emp.ename' which is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by
1
2
3

6.2.原因分析

mysql 5.7以后默认启用sql_mode=only_full_group_by模式特性
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，
也就是说查出来的列必须在group by后面出现否则就会报错，或者这个字段出现在聚合函数里面。
1
2
3

6.3.解决方法

6.3.1.方法一：登录Mysql

--1.查看模式中是否有only_full_group_by
select @@global.sql_mode;
--2.删除only_full_group_by
set @@global.sql_mode 
='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
1
2
3
4
5

mysql> select @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

6.3.2.方法二：修改mysql的配置文件 /etc/my.cnf 或my.ini

在尾部添加以下内容，重新启动 mysql 即可

#Linux 用户
find / -name my.cnf

vi /etc/my.cnf

service restart mysqld
1
2
3
4
5
6

# 配置my.cnf或my.ini
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1
2
3

七、Mysql字符集my.cnf【完整版】

[client]
default_character_set=utf8
[mysqld]
# 表示永久表(permanent tables)的默认存储引擎
default-storage-engine=INNODB
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
collation_server = utf8_general_ci
character_set_server = utf8

# mysql8.0
default_authentication_plugin=mysql_native_password
1
2
3
4
5
6
7
8
9
10
11

八、MySQL登录报错

8.1.问题

ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)
1

8.2.原因

MySQL服务器停止则重新启动即可

密码输入错误
1
2
3

8.3.解决方法–修改密码

8.3.1.加上登录时跳过权限检查

#Linux 用户
find / -name my.cnf

vi /etc/my.cnf

#在[mysqld]中添加skip-grant-tables（登录时跳过权限检查）
skip-grant-tables
1
2
3
4
5
6
7

8.3.2.修改密码

# 重启数据库
systemctl restart mysqld

# 登录mysql，回车即可
mysql -uroot -p

# 进入mysql数据库
use mysql;

# 查看权限是否修改成功
select host,user,plugin,authentication_string from user;

# 刷新服务器配置
FLUSH PRIVILEGES;

# 没有用户创建用户 可能需要刷新
create user 'root'@'localhost' identified by 'Mysql.123456';

# 执行更新权限语句
# 其中“%”代表所有的地址，意味着所有的地址都可以访问“root”
update user set Host='%' where User='root';

# 更改密码
update mysql.user set authentication_string ='Mysql.123456' where user="root";
# 更改密码
alter user 'root'@'localhost' identified by 'Mysql.123456';

# 刷新服务器配置
FLUSH PRIVILEGES;

exit

# 将my.cnf中的skip-grant-tables去除
vi /etc/my.cnf
# 重启数据库
systemctl restart mysqld
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

8.3.2.去掉登录时跳过权限检查

endl