MySQL 基础、安装、配置

1. MySQL 基础

• 1.1 什么是数据库？
• 1.2 数据库的类型
• 1.3 关系型数据库的优点
• 1.4 MySQL 简介
• 1.5 MySQL 数据类型
• 1.6 MySQL 字符集

2. MySQL 安装

• 1）选择安装方式
• 2）清理环境
• 3）MySQL 启停
• 4）MySQL 客户端命令参数
• 5）查看当前运行信息

3. MySQL 配置

• 3.1 MySQL 密码
• 3.2 MySQL 的安装目录
• 3.3 MySQL 配置文件
• 3.4 MySQL 参数配置

 

 

1. MySQL 基础

1.1 什么是数据库？

数据库（DataBase）是以一定规则组织起来的、长期保存在计算机存储设备上的、各种用户或应用可以共享的数据集合。

用户可以对数据文件以及文件内容进行增删改查的操作。

 

1.2 数据库的类型

1）关系型数据库

关系型数据库以行和列的形式存储数据，和我们常见的表格非常类似，每一个表可以形象的理解为一个表格。

关系型数据库支持 SQL：

• SQL 指结构化查询语言（Structured Query Language），是 ANSI（American National Standards Institute 美国国家标准化组织）定义的标准，用于访问和处理数据库的一种标准的计算机语言。
• 各数据库厂商都支持的 SQL 标准（普通话）。
• 各数据库厂商在标准的基础上做了自己的扩展（方言）。

常见的关系型数据库有：MySQL、Oracle、SqlServer、DB2 等。

2）非关系型数据库

NoSQL（Not Only SQL）指的是非关系型的数据库。随着访问量的上升，传统的关系型数据库性能容易出现问题，于是 NoSQL 被设计出来，它的出现主要是为了解决传统数据库系统的规模问题。

NoSQL 常用于超大规模数据的存储（例如 google 或 facebook 每天为他们的用户收集万亿比特的数据）。这些类型的数据存储不需要固定的模式，无需多余操作就可以横向扩展。

常见的菲关系型数据库有：Redis（键值对存储）、MongoDB（文档存储）、HBase（列存储）等。

详见《NoSQL & Redis 介绍、缓存穿透 & 击穿 & 雪崩》 

 

1.3 关系型数据库的优点

1. 容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对于其它的网状、层次等模型来说，更容易理解。
2. 使用方便：通用的 SQL 使得操作关系型数据库非常方便，程序员甚至于数据管理员可以方便地在逻辑层面操作数据库，而完全不必理解其底层实现。
3. 易于维护：丰富的完整性（实体完整性、参照完整性和用户定义的完整性）大大降低了数据冗余和数据不一致的概率。

 

1.4 MySQL 简介

MySQL 是目前最流行的、开源的、关系型数据库。

三个发展阶段

1. 初期开源阶段：开发者为瑞典 MySQL AB 公司
2. Sun MySQL 阶段：2008 年被 Sun 公司收购——mysql 5.0
3. Oracle MySQL 阶段：2009 年 Sun 被 Oracle 收购——mysql 5.1 - mysql 8.0

MySQL（Oracle MySQL）、Percona（MySQL 分支）、Mariadb（MySQL 创始人开发的新分支）三者的使用和维护基本上是一致的，各自有自己的一些特性。

MySQL 组成

MySQL Server（服务端）

• 一个中央的程序作为服务端运行，其他客户端程序通过访问服务端进行数据请求。
• 服务端和客户端可以运行在不同的主机上，也可以运行在不同的操作系统上。
• 数据库的服务程序名为 mysqld。
• 是单进程（process）多线程（thread）的程序，可以充分利用多核 CPU 的计算能力。
• 管理磁盘和内存中的数据访问。
• 支持并发的客户端连接。
• 支持多种存储引擎。
• 支持事务表和非事务表。

MySQL Client（客户端）

• 用于连接 MySQL Server 以获取或修改数据。

 

1.5 MySQL 数据类型 

1. 数值类型
2. 字符串类型
3. 日期类型
4. 根据存储引擎选择合适数据类型

1）数值类型

整数型

• 整数型的数值类型本身已经限制了取值范围，其 M 值并不代表可以存储的数值字符长度，它代表的是数据在显示时的最大长度；当存储的字符长度超过 M 值时也没有任何的影响，只要不超过数值类型限制的范围就行。
• 当存储的字符长度小于 M 值时，只有在设置了 zerofill 即用 0 来填充时才能够看到效果。换句话就是说，没有 zerofill，M 值就是没有效果的。
  • 举例，如果设置了 int(11)，有个字段值是 123，那么这个值在显示宽度上是 3 位，而设计的是显示 11 位，所以这时如果在字段设计的时候选择 zerofill 就可以发现，123 变成了 00000000123，也就是剩下的 8 位用 0 补足了。
• 在设计建表时，MySQL 会自动为整数型字段分配显示长度，如：int(11)、tinyint(4)、smallint(6)、mediumint(9)、bigint(20)。所以，使用这些默认的显示长度就可以了，不用再去自己填长度。
• tinyint 常用来存储状态值，比如 status（0 表示付款未发货，1 表示已收货，2 表示订单完成，3 表示退货中）、gender（0 表示男生，1 表示女生）等。
• 不同的数据类型，占用的存储空间不同，使用没有存储范围过大的类型会造成空间浪费，且越小的字段存储越紧密，更有利于索引查询。

浮点型

例如 double(5,2) 表示最多 5 位，其中必须有 2 位小数，即最大值为 999.99。

2）字符串类型 

 

CHAR(M)

• 定义的字段长度为固定的，M 取值可以为 0～255 之间。当保存 CHAR 值时，在字段值的右边填充空格以达到指定的长度。比如定义 CHAR(10)，那么不论你存储的数据是否达到了 10 个字节，都要占去 10 个字节的空间，不足的自动用空格填充。
• 当检索到 CHAR 值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。
• CHAR 存储定长数据很方便，CHAR 字段上的索引效率级高。

VARCHAR(M)

• 定义的列的长度为可变长字符串，M 取值可以为 0~65535 之间（VARCHAR 的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是 65,532 字节）。
• VARCHAR 值保存时只保存需要的字符数，另加一个字节来记录长度（如果列声明的长度超过 255，则使用两个字节）。
• VARCHAR 值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准 SQL。
• VARCHAR 存储变长数据，如果一个字段可能的值是不固定长度的，我们只知道它不可能超过 10 个字符，把它定义为 VARCHAR(10) 是最合算的。
• CHAR 和 VARCHAR 最大的不同就是一个是固定长度，一个是可变长度。

总结一下，从空间上考虑，用 VARCHAR 合适；从效率上考虑，用 CHAR 合适。关键是根据实际情况找到权衡点。

• 当需要大量查询需求时（追求效率），用 CHAR；
• 当对于保存数据量过大的需求时，为了节省储存空间用 VARCHAR。

TEXT

• TEXT 类型的字段最大长度为 65,535（2^16-1）字节，主要用来存放非二进制的文本，如论坛帖子、题目或者百度知道的问题和回答之类。
• TEXT 列不能有默认值，存储或检索过程中，不存在大小写转换。
• TEXT 如果指定长度，不会报错误，但是这个长度是不起作用的，意思就是插入数据的时候，超过指定的长度还是可以正常插入。
• 可以总结为用来储存大批量的文本信息的时候，使用 TEXT。

总结

• 长度：CHAR 范围是 0～255 字节；VARCHAR 是 0~65535 字节（64k）；如果遇到了大文本，考虑使用 TEXT，最大能到 4G。
• 效率：CHAR > VARCHAR > TEXT。
• 默认值：CHAR 和 VARCHAR 可以有默认值；TEXT 不能指定默认值。

3）日期类型

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的、MySQL 不能表示的值时，则使用"零"值。

• DATETIME：用在需要同时包含日期和时间信息的值时。MySQL 检索并且以“YYYY-MM-DD HH:MM:SS”格式显示 DATETIME 值，支持的范围是“1000-01-01 00:00:00”到“9999-12-31 23:59:59”。
• DATE：用在仅需要日期值时，没有时间部分。MySQL 检索并且以“YYYY-MM-DD”格式显示 DATE 值，支持的范围是“1000-01-01”到“9999-12-31”。
• TIME：表示一天中的时间。MySQL 检索并且以“HH:MM:SS”格式显示 TIME 值。支持的范围是“00:00:00”到“23:59:59”。
• TIMESTAMP：提供一种类型，你可以使用它自动地用当前的日期和时间标记 INSERT 或 UPDATE 的操作，其格式为“yyyy-MM-dd hh:mm:ss”且会自动赋值。

TIMESTAMP 与 DATETIME 的区别

• 两者的存储方式不一样
  • 对于 TIMESTAMP，它把客户端插入的时间（MySQL 所在主机的系统时间）从当前时区转化为 UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。TIMESTAMP 类型的列还有个特性：默认情况下，在 insert 或 update 数据时，TIMESTAMP 列会自动以当前时间（CURRENT_TIMESTAMP）填充/更新。
  • 而对于 DATETIME，不做任何改变，基本上是原样输入和输出。
• 两者所能存储的时间范围不一样
  • TIMESTAMP 所能存储的时间范围为：“1970-01-01 00:00:01.000000”到“2038-01-19 03:14:07.999999”（采用的是一种时间戳计数法，记录的是距离 1970-01-01 过去了多少秒）。
  • DATETIME 所能存储的时间范围为：“1000-01-01 00:00:00.000000”到“9999-12-31 23:59:59.999999”。
• 总结
  • TIMESTAMP 和 DATETIME 除了存储范围和存储方式不一样，没有太大区别。
  • 对于跨时区的业务，TIMESTAMP 更为合适。

4）根据存储引擎选择合适的数据类型 

在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同，这里简单概括如下：

• MyISAM：建议使用固定长度的数据列代替可变长度的数据列。
• MEMORY：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或 VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
• InnoDB：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

  

1.6 MySQL 字符集

如何选择合适的字符集：

虽然没有一定之规，但在选择数据库字符集时，可以根据应用的需求，结合上面介绍的些字符集的特点来权衡，主要考虑因素包括：

1. 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，目前就是 UTF-8。
2. 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字，如果选择 GB2312-80 为数据库字符集，就很可能出现某些文字无法正确导入的问题。
3. 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。因为，相对于 UTF-8 而言，GBK 比较“小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节，这样可以减少磁盘 I/O、数据库 cache 以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8 更好（仅需 1 个字节），因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节会造成很大不必要的开销。
4. 如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好因为定长字符集的处理速度要比变长字符集的处理速度快。
5. 如果所有客户端程序都支持相同的字符集，应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

查看数据库字符集信息：

--查看数据库编码的具体信息 
show variables like 'character%';

--临时更改客户端和服务器结果集的编码 
set character_set_client=gbk; 
set character_set_results=gbk;

 

2. MySQL 安装

MySQL 官方下载地址

1）选择安装方式

1. rpm 是二进制安装包，可以通过简单的 rpm 命令进行安装。
2. tar 是源码包，需要编译。
3. yum 是系统 rpm 安装包管理器。可以用 yum 安装 mysql-server，但注意 yum 安装的包都是从配置的源站进行下载的，这就导致了 yum 安装的程序包版本只有源站的已经有的版本，无法安装其他版本。

因此尽量选择二进制包，安装更简单。

2）清理环境

卸载掉系统中已有的 mysql 安装程序或者类库，防止版本冲突。

# 查看已有的包
rpm -qa|grep -i mysql
yum list|grep mysql
# 卸载
rpm -e mysqlxxxx
yum -y remove mysqlxxxx
# 安装
rpm -ivh mysql-community-server-8.0.25-1.el8.x86_64_\(1\).rpm
yum -y install mysql-server

若报错缺少依赖包，解决办法是缺少什么就安装什么（可用 yum 补充安装依赖包）。

3）MySQL 启停

# 启动数据库
systemctl start mysqld

# 查看当前mysql服务的状态
service mysqld status

# 关闭服务
systemctl stop mysqld

4）MySQL 客户端命令参数

帮助文档：mysql --help

常用参数：

• -u：用户名
• -p：密码
• -P：端口
• -h：IP
• -e：登录后执行的 Mysql 命令

5）查看当前运行信息

 

3. MySQL 配置

3.1 MySQL 密码

Mysql 默认 root 用户没有密码，输入 mysql -u root 即可登录 mysql。

• Mysql 8.0 引入了新特性 caching_sha2_password；这种密码加密方式限制了远程连接。
• 远程连接支持的是 mysql_native_password 这种加密方式。

root 用户的验证器插件默认为 caching_sha2_password，以下为修改身份验证类型（并修改密码）的方法：

alter user 'root'@'localhost' identified with mysql_native_password by '123456';  --'root'@'localhost'可以不加引号，但加了可以避免歧义

验证查看 mysql 库中 user 表的 plugin 字段：

若忘记密码而不能登录，能用查看日志找到密码吗？或重置密码？答案是：No

解决方案：

Mysql Server 有安全启动模式，该模式需要重启服务。

mysqld_safe --skip-grant-tables&

通过这种模式来重置账号密码（root 密码重置回空），修改过程会影响服务正常。

mysqld_safe --skip-grant-tables&
mysql -u root mysql
mysql>alter user root@'localhost' identified with mysql_native_password by '123456';
mysql> FLUSH PRIVILEGES;

 

3.2 MySQL 的安装目录

/var/lib/mysql

数据库的数据目录（datadir），其中存储的就是库表数据，innodb 日文件等。

/usr/sbin/mysqld

Mysql 服务端启动程序。

/usr/bin/

这里存放的是 Mysql 客户端命令集或者说工具集。

/etc/my.cnf

Mysql 的相关配置文件。

 

3.3 MySQL 配置文件

/etc/my.cnf 管理 Mysql 的配置信息。

/etc/my.cnf.d/mysql-server.cnf：配置文件内容

[mysqld] ：这个模块下的所有配置信息是对 Mysql Server 端生效的。

[mysqld]
datadir=/var/lib/mysql  # 数据文件的存放目录
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid

selinux

SELinux：即安全增强型 Linux（Security-Enhanced Linux），它是一个 Linux 内核模块，也是 Linux 的一个安全子系统。

它的主要作用：最大限度地减小系统中服务进程可访问的资源（最小权限原则）。这个服务一般需要关掉，否则修改一些服务配置可能会失败。

1）首先使用 sestatus 命令来检查 selinux 运行状态以及运行模式：

上面的输出显示，selinux 被启用，并且设置在 enforcing 模式。 

2）关闭 selinux

当被启用的时候，selinux 可以被设置成 enforcing 或者 permissive 模式。通过下面的命令，你可以临时将模式修改为 permissive：

setenforce 0

这个修改仅仅对于当前运行的会话有效，并且不会持久化，重启后失效。

或修改配置文件（永久生效）：

vi /etc/sysconfig/selinux  # 其是 /etc/selinux/config 的符号链接

SELINUX=disabled

然后重启机器。

 

3.4 MySQL 参数配置

按生效方式分类

• 静态参数：在 MySQL 启动的时候加载，只能通过修改配置文件或者 mysqld 启动时添加参数生效。比如 datadir。
• 动态参数：可以在 MySQL 运行时修改生效，可以直接 set。

在 Mysql 命令行下查看数据库的配置参数

show variables;

查看某个具体的参数：

show variables like "%datadir%";
show variables like "sock%";

Mysql 的服务端口，默认是 3306：

如何修改这个端口：set port=3307 会报错，因为这是个静态变量 ，需要修改配置文件。

1. service mysqld stop
2. 修改配置文件：

按照参数变量的作用域

1. session_only：仅线程级别意义的，只对当前连接生效，断开重新连接就没了。比如 last_insert_id 。
2. global_only ：仅全局级别有意义的，对当前连接不生效，需要重新连。比如 sync_master_info。
3. both：同时有全局和线程两个状态。

这类变量需要特别注意它的规则

1. 每个新线程创建时从 global 获取值，设置为线程值。
2. 单独执行 set var_name=var_value 时，只改变本线程的值，不改变 global.value。
3. 单独执行 set global var_name=var_value 时，只改变全局的值，本线程的不改变。

binlog_format 就是这种 both 类的变量。因此即使两个命令都执行了，但是对于那些执行命令之前已经存在了的长连接线程的本地值，仍然是 statement。

区分变量类型

1. 无论是修改本次会话的变量还是全局变量，当 Mysql 服务器重启时，都会失效。要想永久生效，还是要将配置写入配置文件中。
2. 对于全局变量的更改可以被访问该全局变量的任何客户端看见。然而，它只影响更改后连接的客户的从该全局变量初始化的相应会话变量，不影响目前已经连接的客户端的会话变量（即使客户端执行 SET GLOBAL 语句也不影响）。简而言之，全局变量修改后，客户端必须重新连接才会生效。
3. 对于局部变量修改后，只会对本次连接生效，客户端重新连接后失效。