高级1章 Linux下MySQL的安装与使用

1. 安装前说明

1.1 Linux系统及工具的准备

安装并启动好两台虚拟机： CentOS 7
- 掌握克隆虚拟机的操作
  - mac地址
  - 主机名
```
vim /etc/hostname
```
    修改之后需要重启才会生效
  - ip地址
    
    此处需要注意的是：如果虚拟机使用的是动态ip分配，那么不需要更改ip，如果想改为静态ip，请修改：
```
vim /etc/sysconfig/network-scripts/ifcfg-ens33
```
    我自己电脑是动态IP，所以无需修改
    
    修改完之后，需要重启网络生效
```
systemctl restart network
```
  - UUID
安装有 Xshell 和 Xftp 等访问CentOS系统的工具，我是用的是finalshell

CentOS6和CentOS7在MySQL的使用中的区别

1. 防火墙：6是iptables，7是firewalld

2. 启动服务的命令：6是service，7是systemctl

1.2 查看是否安装过MySQL

如果你是用rpm安装, 检查一下RPM PACKAGE：
```
rpm -qa | grep -i mysql # -i 忽略大小写
```
检查mysql service：
```
systemctl status mysqld.service
```
如果存在mysq-libs的I旧版本包，显示如下：
如果不存在mysql-lib的版本，显示如下：

1.3 MySQL的卸载

关闭MySQL服务
```
systemctl stop mysqld.service
```

查看当前MySQL安装情况

rpm -qa | grep -i mysql
# 或
yum list installed | grep mysql

卸载上述命令查询出的已安装程序
```
yum remove mysql-xxx mysql-xxx mysql-xxx mysqk-xxxx
```
务必卸载干净，反复执行rpm -qa | grep -i mysql确认是否有卸载残留
删除MySQL相关文件
- 查找相关文件：
```
find / -name mysql
```
- 删除上述命令查找出的相关文件
```
rm -rf xxx
```
删除 my.cnf
```
rm -rf /etc/my.cnf
```

2. MySQL的Linux版安装

2.1 下载MySQL指定版本

下载地址

官网： https://www.mysql.com
打开官网，点击DOWNLOADS

然后，点击 MySQL Community(GPL) Downloads
点击 MySQL Community Server
在General Availability(GA) Releases中选择适合的版本
- 如果安装Windows 系统下MySQL ，推荐下载 MSI安装程序；点击 Go to Download Page 进行下载即可
- Windows下的MySQL安装有两种安装程序
  
  mysql-installer-web-community-8.0.33.0.msi 下载程序大小：2.4M；安装时需要联网安装组件。
  
  mysql-installer-community-8.0.33.0.msi 下载程序大小：435.7M；安装时离线安装即可。推荐。
Linux系统下安装MySQL的几种方式
- rpm命令
  
  使用rpm命令安装扩展名为”.rpm“的软件包。
  
  .rpm包的一般格式：
- yum命令
  
  需联网，从互联网获取的yum源，直接使用yum命令安装。
- 编译安装源码包
  
  针对 tar.gz 这样的压缩格式，要用tar命令来解压；如果是其它压缩格式，就使用其它命令。
这里不能直接选择CentOS 7系统的版本，所以选择与之对应的 Red Hat Enterprise Linux

https://downloads.mysql.com/archives/community/

直接点Download下载RPM Bundle全量包。包括了所有下面的组件。不需要一个一个下载了。

或者选择如下方式：
下载的tar包，用压缩工具打开

解压后rpm安装包（红框为抽取出来的安装包）

我是将文件放到了Centos的/opt/目录下了

2.2 CentOS7下检查MySQL依赖

检查/tmp临时目录权限（必不可少）

由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限。执行：
```
chmod -R 777 /tmp
```
安装前，检查依赖
```
rpm -qa|grep libaio
rpm -qa|grep net-tools
```
如果不存在需要到centos安装盘里进行rpm安装。安装Linux如果带图形化界面，这些都是安装好的。

2.3 CentOS7下MySQL安装过程

2.3.1 将安装程序拷贝到/opt目录下

在mysql的安装文件目录下执行：（必须按照顺序执行）

rpm -ivh mysql-community-common-8.0.33-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-plugins-8.0.33-1.el7.x86_64.rpm

rpm -ivh mysql-community-libs-8.0.33-1.el7.x86_64.rpm

rpm -ivh mysql-community-client-8.0.33-1.el7.x86_64.rpm

rpm -ivh mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm #最开始这个文件没有传入，如果没有传入Linux，这里最后一步安装会报错

rpm -ivh mysql-community-server-8.0.33-1.el7.x86_64.rpm

rpm是Redhat Package Manage缩写，通过RPM的管理，用户可以把源代码包装成以rpm为扩展名的文件形式，易于安装。
-i, –install 安装软件包
-v, –verbose 提供更多的详细信息输出
-h, –hash 软件包安装的时候列出哈希标记 (和 -v 一起使用效果更好)，展示进度条

2.3.2 安装过程可能出现报错：

清除之前安装过的依赖即可：

yum remove mysql-libs

出现这个报错只需要将icu文件传入Linux在安装即可。

执行：

rpm -ivh mysql-community-icu-data-files-8.0.33-1.el7.x86_64.rpm

2.3.3 查看MySQL版本

mysql --version 
#或
mysqladmin --version

执行如下命令，查看是否安装成功。需要增加 -i 不用去区分大小写，否则搜索不到。

rpm -qa|grep -i mysql

2.3.4 服务的初始化

为了保证数据库目录与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化：

mysqld --initialize --user=mysql

说明： –initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码。生成的临时密码会往日志中记录一份。

查看密码：

cat /var/log/mysqld.log

root@localhost: 后面就是初始化的密码

2.3.5 启动MySQL，查看状态

加不加.service后缀都可以

启动:
```
systemctl start mysqld.service 
```
关闭：
```
systemctl stop mysqld.service 
```
重启：
```
systemctl restart mysqld.service 
```
查看状态：
```
systemctl status mysqld.service
```

mysqld 这个可执行文件就代表着 MySQL 服务器程序，运行这个可执行文件就可以直接启动一个服务器进程。

查看进程：

ps -ef | grep -i mysql

2.3.6 查看MySQL服务是否自启动

systemctl list-unit-files|grep mysqld.service

默认是enabled

如不是enabled可以运行如下命令设置自启动
```
systemctl enable mysqld.service
```
如果希望不进行自启动，运行如下命令设置
```
systemctl disable mysqld.service
```

3. MySQL登陆

3.1 首次登录

通过mysql -hlocalhost -P3306 -uroot -p进行登录，在Enter password：录入初始化密码

初始密码登陆

3.2 修改密码

因为初始化密码默认是过期的，所以查看数据库会报错

修改密码：

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

5.7版本之后（不含5.7），mysql加入了全新的密码安全机制。设置新密码太简单会报错
改为更复杂的密码规则之后，设置成功，可以正常使用数据库了

注意：我这里使用简单密码成功了：

3.3 设置远程登录

3.3.1 当前问题

在用SQLyog或Navicat中配置远程连接Mysql数据库时遇到如下报错信息，这是由于Mysql配置了不支持远程连接引起的。

3.3.2 确认网络

在远程机器上使用ping ip地址保证网络畅通
在远程机器上使用telnet命令 保证端口号开放访问
```
telnet ip地址 端口号
```
拓展：telnet命令开启：

3.3.3 关闭防火墙或开放端口

方式一：关闭防火墙

#开启防火墙
systemctl start firewalld.service

#查看防火墙状态
systemctl status firewalld.service

#关闭防火墙
systemctl stop firewalld.service

#设置开机启用防火墙 
systemctl enable firewalld.service 

#设置开机禁用防火墙 
systemctl disable firewalld.service

方式二：开放端口

查看开放的端口号
```
firewall-cmd --list-all
```

设置开放的端口号

firewall-cmd --add-service=http --permanent
firewall-cmd --add-port=3306/tcp --permanent

重启防火墙
```
firewall-cmd --reload
```

这里我选择了关闭防火墙：

连接仍然报错：

3.3.4 Linux下修改配置

在Linux系统MySQL下测试：

use mysql;

select Host,User from user;

可以看到root用户的当前主机配置信息为localhost。

表示只允许使用root用户登陆本机。

如果你想远程连接，那么你就需要将localhost更改掉

修改Host为通配符%

Host列指定了允许用户登录所使用的IP，比如user=root Host=192.168.1.1。这里的意思就是说root用户只能通过192.168.1.1的客户端去访问。 user=root Host=localhost，表示只能通过本机客户端去访问。而 %是个通配符 ，如果Host=192.168.1.%，那么就表示只要是IP地址前缀为“192.168.1.”的客户端都可以连接。如果 Host=% ，表示所有IP都有连接权限。
注意：在生产环境下不能为了省事将host设置为%，这样做会存在安全问题，具体的设置可以根据生产环境的IP进行设置。就比如只对最后一个网段使用%：Host=192.168.1.%
```
update user set host = '%' where user ='root';
```
我自己测试，为了方便，所以设置了%

Host修改完成后记得执行flush privileges使配置立即生效：
```
flush privileges;
```

3.3.5 测试

如果是 MySQL5.7 版本，接下来就可以使用SQLyog或者Navicat成功连接至MySQL了。
如果是 MySQL8 版本，连接时还会出现如下问题：

配置新连接报错：错误号码 2058，分析是 mysql 密码加密方法变了。

解决方法：Linux下 mysql -u root -p 登录你的 mysql 数据库，然后执行这条SQL：

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; #这里也就是填写你的密码

4. 字符集的相关操作

4.1 修改步骤

在MySQL 8.0版本之前，默认字符集为 latin1 ，utf8字符集指向的是 utf8mb3 。网站开发人员在数据库设计的时候往往会将编码修改为utf8字符集。如果遗忘修改默认的编码，就会出现乱码的问题。

从MySQL8.0开始，数据库的默认编码将改为 utf8mb4 ，从而避免上述乱码的问题。

操作1：查看默认使用的字符集

show variables like 'character%';
# 或者
show variables like '%char%';

在MySQL8.0执行：
在MySQL5.7执行：

MySQL 5.7 默认的客户端和服务器都用了 latin1 ，不支持中文，保存中文会报错。MySQL5.7截图如下：

在MySQL5.7中添加中文数据时，报错：

因为默认情况下，创建表使用的是 latin1 。如下：

操作2：修改字符集

vim /etc/my.cnf

在MySQL5.7或之前的版本中，在文件最后加上中文字符集配置：

character_set_server=utf8

操作3：重新启动MySQL服务

systemctl restart mysqld

但是原库、原表的设定不会发生变化，参数修改只对新建的数据库生效。

如果要改变原表、库的字符集，只有使用修改命令来更改了

4.2 各级别的字符集

MySQL有4个级别的字符集和比较规则，分别是：

服务器级别
数据库级别
表级别
列级别

执行如下SQL语句：

show variables like 'character%';

character_set_server：服务器级别的字符集
character_set_database：当前数据库的字符集
character_set_client：服务器解码请求时使用的字符集
character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection
character_set_results：服务器向客户端返回数据时使用的字符集

4.2.1 服务器级别

character_set_server ：服务器级别的字符集。

我们可以在启动服务器程序时通过启动选项或者在服务器程序运行过程中使用 SET 语句修改这两个变量的值。比如我们可以在配置文件中这样写：

[server]
character_set_server=gbk # 默认字符集
collation_server=gbk_chinese_ci #对应的默认的比较规则

当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

4.2.2 数据库级别

character_set_database ：当前数据库的字符集

我们在创建和修改数据库的时候可以指定该数据库的字符集和比较规则，具体语法如下：

CREATE DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];
ALTER DATABASE 数据库名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [[DEFAULT] COLLATE 比较规则名称];

4.2.3 表级别

我们也可以在创建和修改表的时候指定表的字符集和比较规则，语法如下：

CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]
ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则

4.2.4 列级别

对于存储字符串的列，同一个表中的不同的列也可以有不同的字符集和比较规则。我们在创建和修改列定义的时候可以指定该列的字符集和比较规则，语法如下:

CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);
ALTER TABLE 表名 MODIFY 列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

对于某个列来说，如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则。

提示

在转换列的字符集时需要注意，如果转换前列中存储的数据不能用转换后的字符集进行表示会发生错误。比方说原先列使用的字符集是utf8，列中存储了一些汉字，现在把列的字符集转换为ascii的话就会出错，因为ascii字符集并不能表示汉字字符

4.2.5 小结

如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

5. SQL大小写规范

5.1 Windows和Linux平台区别

在 SQL 中，关键字和函数名是不用区分字母大小写的，比如 SELECT、WHERE、ORDER、GROUP BY 等关键字，以及 ABS、MOD、ROUND、MAX 等函数名。

不过在 SQL 中，你还是要确定大小写的规范，因为在 Linux 和 Windows 环境下，你可能会遇到不同的大小写问题。 windows系统默认大小写不敏感 ，但是 linux系统是大小写**敏感的 **。

通过如下命令查看：

SHOW VARIABLES LIKE '%lower_case_table_names%';

Windows系统下：
Linux系统下：
lower_case_table_names参数值的设置：
- 默认为0，大小写敏感。
- 设置1，大小写不敏感。创建的表，数据库都是以小写形式存放在磁盘上，对于sql语句都是转换为小写对表和数据库进行查找。
- 设置2，创建的表和数据库依据语句上格式存放，凡是查找都是转换为小写进行。
两个平台上SQL大小写的区别具体来说：
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的：
1. 数据库名、表名、表的别名、变量名是严格区分大小写的；
2. 关键字、函数名称在 SQL 中不区分大小写；
3. 列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；
MySQL在Windows的环境下全部不区分大小写

5.2 Linux下大小写规则设置

当想设置为大小写不敏感时，要在 my.cnf 这个配置文件 [mysqld] 中加入lower_case_table_names=1 ，然后重启服务器。

但是要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名
此参数适用于MySQL5.7。在MySQL 8下禁止在重新启动 MySQL 服务时将lower_case_table_names 设置成不同于初始化 MySQL 服务时设置的
lower_case_table_names 值。如果非要将MySQL8设置为大小写不敏感，具体步骤为：
```
1、停止MySQL服务
2、删除数据目录，即删除 /var/lib/mysql 目录
3、在MySQL配置文件（ /etc/my.cnf ）中添加 lower_case_table_names=1
4、启动MySQL服务
```

5.3 SQL编写建议

如果你的变量名命名规范没有统一，就可能产生错误。这里有一个有关命名规范的建议：

关键字和函数名称全部大写；

数据库名、表名、表别名、字段名、字段别名等全部小写；

SQL 语句必须以分号结尾。

数据库名、表名和字段名在 Linux MySQL 环境下是区分大小写的，因此建议你统一这些字段的命名规则，比如全部采用小写的方式。

虽然关键字和函数名称在 SQL 中不区分大小写，也就是如果小写的话同样可以执行。但是同时将关键词和函数名称全部大写，以便于区分数据库名、表名、字段名。

6. sql_mode的合理设置

6.1 宽松模式 vs 严格模式

宽松模式：

如果设置的是宽松模式，那么我们在插入数据的时候，即便是给了一个错误的数据，也可能会被接受，并且不报错。

举例 ：我在创建一个表时，该表中有一个字段为name，给name设置的字段类型时 char(10) ，如果我在插入数据的时候，其中name这个字段对应的有一条数据的长度超过了10 ，例如’1234567890abc’，超过了设定的字段长度10，那么不会报错，并且取前10个字符存上，也就是说你这个数据被存为
了’1234567890’，而’abc’就没有了。但是，我们给的这条数据是错误的，因为超过了字段长度，但是并没有报错，并且mysql自行处理并接受了，这就是宽松模式的效果。

应用场景 ：通过设置sql mode为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。

严格模式：

出现上面宽松模式的错误，应该报错才对，所以MySQL5.7版本就将sql_mode默认值改为了严格模式。所以在 生产等环境 中，我们必须采用的是严格模式，进而 开发、测试环境 的数据库也必须要设置，这样在开发测试阶段就可以发现问题。并且我们即便是用的MySQL5.6，也应该自行将其改为严格模式。

开发经验 ：MySQL等数据库总想把关于数据的所有操作都自己包揽下来，包括数据的校验，其实开发中，我们应该在自己 开发的项目程序级别将这些校验给做了 ，虽然写项目的时候麻烦了一些步骤，但是这样做之后，我们在进行数据库迁移或者在项目的迁移时，就会方便很多。

改为严格模式后可能会存在的问题：

若设置模式中包含了 NO_ZERO_DATE ，那么MySQL数据库不允许插入零日期，插入零日期会抛出错误而不是警告。例如，表中含字段TIMESTAMP列（如果未声明为NULL或显示DEFAULT子句）将自动分配DEFAULT ‘0000-00-00 00:00:00’（零时间戳），这显然是不满足sql_mode中的NO_ZERO_DATE而报错。

6.2 模式查看和设置

查看当前的sql_mode

select @@session.sql_mode;
select @@global.sql_mode;
#或者
show variables like 'sql_mode';

临时设置方式：设置当前窗口中设置sql_mode

SET GLOBAL sql_mode = 'modes...'; #全局

SET SESSION sql_mode = 'modes...'; #当前会话

举例：

#改为严格模式。此方法只在当前会话中生效，关闭当前会话就不生效了。
set SESSION sql_mode='STRICT_TRANS_TABLES';

#改为严格模式。此方法在当前服务中生效，重启MySQL服务后失效。
set GLOBAL sql_mode='STRICT_TRANS_TABLES';

永久设置方式：在/etc/my.cnf中配置sql_mode

在my.cnf文件(windows系统是my.ini文件)，新增：
```
[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR
_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
```
然后 重启MySQL 。
当然生产环境上是禁止重启MySQL服务的，所以采用 临时设置方式 + 永久设置方式 来解决线上的问题，那么即便是有一天真的重启了MySQL服务，也会永久生效了。

念心卓

http://blog.cqwulyj.cn/posts/58f3773e.html

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源念心卓 !

MySQL高级部分

MySQL高级—18章(主从复制)

2024-01-24 数据库

MySQL高级部分

MySQL高级—19章(数据库备份与恢复)

2024-01-24 数据库

MySQL高级部分

MySQL高级—1章(Linux下MySQL的安装与使用)

高级1章 Linux下MySQL的安装与使用

1. 安装前说明

1.1 Linux系统及工具的准备

1.2 查看是否安装过MySQL

1.3 MySQL的卸载

2. MySQL的Linux版安装

2.1 下载MySQL指定版本

2.2 CentOS7下检查MySQL依赖

2.3 CentOS7下MySQL安装过程

2.3.1 将安装程序拷贝到/opt目录下

2.3.2 安装过程可能出现报错：

2.3.3 查看MySQL版本

2.3.4 服务的初始化

2.3.5 启动MySQL，查看状态

2.3.6 查看MySQL服务是否自启动

3. MySQL登陆

3.1 首次登录

3.2 修改密码

3.3 设置远程登录

3.3.1 当前问题

3.3.2 确认网络

3.3.3 关闭防火墙或开放端口

3.3.4 Linux下修改配置

3.3.5 测试

4. 字符集的相关操作

4.1 修改步骤

4.2 各级别的字符集

4.2.1 服务器级别

4.2.2 数据库级别

4.2.3 表级别

4.2.4 列级别

4.2.5 小结

5. SQL大小写规范

5.1 Windows和Linux平台区别

5.2 Linux下大小写规则设置

5.3 SQL编写建议

6. sql_mode的合理设置

6.1 宽松模式 vs 严格模式

6.2 模式查看和设置