MySQL 基础、安装、配置

1. MySQL 基础

2. MySQL 安装

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

3. 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)数值类型

MySQL 基础、安装、配置

整数型

  • 整数型的数值类型本身已经限制了取值范围,其 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)字符串类型

MySQL 基础、安装、配置

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

总结

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

3)日期类型

MySQL 基础、安装、配置

每个时间类型有一个有效值范围和一个"零"值,当指定不合法的、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 基础、安装、配置

MySQL 基础、安装、配置

 

1.6 Mysql 存储引擎

MySQL 基础、安装、配置

  • MyISAM 是非事务的,因此读取更快。
  • InnoDB 支持细颗粒度的事务锁定(比如:commit/rollback)。

如何选择合适的存储引擎:

MySQL 基础、安装、配置

MySQL 基础、安装、配置

查询存储引擎信息的相关 SQL

--查看 mysql 现在已提供什么存储引擎
show engines; --查看 mysql 当前默认的存储引擎
show variables like '%storage_engine%'; --查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎)
show create table 表名;

1.7 MySQL 字符集

MySQL 基础、安装、配置

如何选择合适的字符集:

MySQL 基础、安装、配置

查看数据库字符集信息:

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

MySQL 基础、安装、配置

--临时更改客户端和服务器结果集的编码
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 基础、安装、配置

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

MySQL 基础、安装、配置

# 关闭服务
systemctl stop mysqld

4)MySQL 客户端命令参数

帮助文档:mysql --help

常用参数:

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

MySQL 基础、安装、配置

5)查看当前运行信息

MySQL 基础、安装、配置

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 字段:

MySQL 基础、安装、配置

忘记密码而不能登录,能用查看日志找到密码吗?或重置密码?答案是: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 日文件等。

MySQL 基础、安装、配置

/usr/sbin/mysqld

Mysql 服务端启动程序。

/usr/bin/

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

MySQL 基础、安装、配置

/etc/my.cnf

Mysql 的相关配置文件。

3.3 MySQL 配置文件

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

MySQL 基础、安装、配置

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 运行状态以及运行模式:

MySQL 基础、安装、配置

上面的输出显示,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:

MySQL 基础、安装、配置

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

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

MySQL 基础、安装、配置

按照参数变量的作用域

MySQL 基础、安装、配置

  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. 对于局部变量修改后,只会对本次连接生效,客户端重新连接后失效。

3.5 MySQL 日志

主要分为 5 种:

1)Error Log(错误日志)

/var/log/mysql.log:记录启动、运行、停止 mysql 服务端时遇到的问题,主要用来分析定位问题。

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log # 定义错误日志的路径
pid-file=/run/mysqld/mysqld.pid

2)General Log(通用日志)

记录服务端所有访问记录、执行的 SQL 等记录下来。

该日志功能默认不开启,因为 log 的量会非常庞大,但个别情况下可能会临时的开一会儿 general log 以供排障使用。

相关参数一共有 3 个:general_log、log_output、general_log_file

show variables like 'general_log';  -- 查看日志是否开启

show variables like 'log_output';  -- 看看日志输出类型:table 或 file

show variables like 'general_log_file';  -- 看看日志文件保存位置

set global general_log_file='tmp/general.log'; -- 设置日志文件保存位置

set global general_log=on; -- 开启日志功能

set global log_output='table'; -- 设置输出类型为 table

set global log_output='file';   -- 设置输出类型为 file

3)Binary Log(二进制日志)

该日志文件以二进制进行存储,记录的是数据库所有的变更(比如增删改)操作,主要用来做主从复制。

MySQL 基础、安装、配置

  • log_bin         | ON   二进制日志功能开关
  • max_binlog_size  | 1073741824    单个二进制日志大小

MySQL 基础、安装、配置

二进制日志如何查看:使用系统带的 mysqlbinlog 工具。

MySQL 基础、安装、配置

4)Relay Log(中继日志)

也是二进制日志,和 binlog 是对应关系,是存在于主从复制的从节点上,简单理解就是主节点 master 上的是 binlog,传到从节点写入的就是 relaylog。

5)Slow Log(慢查询日志)

慢查询日志,记录下所有执行慢的 SQL

如何定义 SQL 是执行慢的 SQL,使用的是一个参数来控制:慢查询阈值,执行时间超过该参数的,被认为是慢 SQL 并记录到慢查询日志中。

show variables like "long_query_time";

MySQL 基础、安装、配置

打开慢查询:

show variables like "slow_query_log";

set global slow_query_log=on;

查看慢查询:

MySQL 基础、安装、配置

上一篇:javascript学习-原生javascript的小特效(多个运动效果整理)


下一篇:BBED的安装及简单的使用方法