本文核心内容

第一部分：从入门到精通 | MySQL知识整理（上）

一、体系结构详解

1、安装MySQL所必需的体系结构组件：MySQL服务器，客户机程序以及MySQL非客户机程序；中央程序可以充当服务器，客户机程序连接到该服务器用以发出数据请求。

2、MySQL客户机/服务器通信可以运行在不同的操作系统中实现。

• 客户机程序可以连接到在相同主机或不同主机上运行的服务器
• 客户机/服务器通信可以发生在计算机运行不同操作系统的环境中

客户机程序：

1、mysql/mysqldump客户机程序是使用最多的一个客户段工具；

2、MySQL Workbench是一款GUI工具,可以用来：

• 为数据库建模
• 执行数据库查询
• 执行管理任务

MySQL服务器：

1、服务器和主机有什么区别：服务器：一个程序(mysqld),具有版本号和一系列功能；主机：物理计算机，其中包含：硬件配置、操作系统、网络地址

2、多个mysqld实例可同时运行在一台主机上;

服务器进程：

1、应用程序主要以mysqld进程来访问数据库服务器,由mysqld来操作不同的存储引擎，包括：磁盘、内存、网络;

2、mysqld(服务器程序)进程可以划分为以下三个层：

• 连接层：处理连接,此层存在于所有服务器软件(Web服务器)上;
  
• SQL层:SQL查询发送到所连接的应用程序处理;
  
• 存储层:处理数据存储,数据可以按不同格式和结构存储在不同物理介质上

连接层：

1、连接层可通过多种通信协议接受来自应用程序的连接，包含以下几种:

• TCP/IP;
• UNIX套接字;
• 共享内存;
• 命名管道;
  

2、其中TCP/IP是最常用的连接方式，适用于整个网络;客户机和服务器在同一台计算机上运行时,上面列出的其他协议仅支持本地连接;

3、此层针对每个连接维护一个线程,此线程处理查询执行;在某个连接可以开始发送SQL查询之前,将会通过验证用户名+口令+客户机主机来对该连接进行验证;

通信协议：

1、TCP/IP：该通信协议套件用于连接Internet上的主机;在Linux操作系中,TCP/IP是内置的,供 Internet使用,从而使其成为通过网络传输数据的标准;

2、UNIX套接字：一种进程间通信形式,用于在同一台计算机上的进程之间形成双向，通信链路的一端;套接字需要本地系统上的物理文件,这是适用于Linux的最佳连接类型;

3、共享内存：一种在程序之间传递数据的有效方式;一个程序创建其他进程(如果允许)可以访问的内存部分;;默认情况下,共享内存处于禁用状态,要启用共享内存连接,必须使用–shared-memory选项启动服务器;

4、命名管道：命名管道的使用偏向于客户机/服务器通信,其工作方式与套接字非常相似;命名管道支持读/写操作,以及服务器应用程序的显式“被动”模式;此协议仅适用于单台(Windows)计算机;默认情况下,命名管道处于禁用状态,要启用命名管道连接,必须使用–enable-named-pipe选项启动服务器;

SQL层：

1、解析器:验证语法是否正确;

2、授权:验证是否允许所连接的用户运行特定查询;

3、优化器:创建每个查询的执行计划,这是有关如何以最优化的方式执行查询的分步指令集,确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分;

4、查询执行:完成每个查询的执行计划;

5、查询高速缓存:(可选)可配置的查询高速缓存,可用于存储(并立即返回)执行的查询和结果;

6、查询日志记录:可以启用以跟踪执行的查询;SQL语句处理：第一次的选择主要是查看是否有配置开启[高速缓存查询]的特性;

存储层：

1、通过MySQL,可以使用称为“存储引擎”的不同类型的存储,数据可以存储在磁盘,内存和网络中;

2、数据库中的每个表可以使用任何可用的存储引擎,“磁盘”存储便宜且持久,而“内存”存储则要快得多;

3、InnoDB是默认存储引擎,它可提供事务,全文索引和外键约束,因此适用于各种混合查询;它具有多种用途,支持读密集型工作负荷,读/写工作负荷和事务工作负荷;

4、其他存储引擎包括:

• MyISAM:适用于频繁读取但很少更新的数据;
• MEMORY:在内存中存储所有数据;
• NDB:供MySQL Cluster用来为高可用性数据提供冗余的可伸缩拓扑

注:存储引擎可扩展,超越存储层,而不只包含存储,它们还包括其他结构和实现机制;

存储引擎概览：

1、客户机通过以SQL语句形式向服务器发送请求从表中检索数据或更改表中的数据;

2、服务器通过使用双层处理模型执行每条语句;

3、客户机通常不需要关心哪些引擎参与SQL语句处理,这种独立于引擎的SQL语句的一些例外情况包括:

• CREATE TABLE具有ENGINE选项,可基于每个表指定要使用的引擎;
• ALTER TABLE具有ENGINE选项,允许将表转换为使用不同的存储引擎;
• 某些索引类型仅适用于特定存储引擎;例如,仅InnoDB和MyISAM引擎支持全文索引;
• COMMIT和ROLLBACK操作仅影响事务存储引擎(例如InnoDB和NDB)管理的表;

依赖于存储引擎的功能：

1、存储介质:表存储引擎可以在磁盘上,在内存中或通过网络存储数据;

2、事务功能:某些存储引擎支持全面的ACID事务功能,而其他存储引擎可能不具有事务支持;

3、锁定:存储引擎可能使用不同的锁定粒度(例如表级别锁定或行级别锁定)和机制来提供与并发事务的一致性;

4、备份和恢复:可能会受到存储引擎存储和操作数据的方式的影响;

5、优化:不同的索引实现可能会影响优化,存储引擎以不同的方式使用内部高速缓存,缓冲区和内存以优化性能;

6、特殊功能:某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力;TIPS:优化器可能需要根据存储引擎进行不同的选择,但这均是通过每种存储引擎支持的标准化接口(API)进行处理的;

MySQL如何使用磁盘空间：

1、默认情况下,程序文件随数据目录一起存储在服务器安装目录下;执行各种客户机程序,管理程序和实用程序时将创建程序可执行文件和日志文件;

2、首要使用磁盘空间的是数据目录:服务器日志文件和状态文件包含有关服务器处理的语句的信息,日志可用于进行故障排除/监视/复制和恢复;InnoDB日志文件(适用于所有数据库)驻留在数据目录级别;InnoDB系统表空间包含数据字典,撤消日志和缓冲区;每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表),数据库目录存储以下内容:

• 数据文件:特定于存储引擎的数据文件,这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎;
• 格式文件(.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中;
• 触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象;

数据目录的位置取决于配置,操作系统,安装包和分发;典型位置是/var/lib/mysql;MySQL在磁盘上存储系统数据库(mysql),mysql包含诸如用户/特权/插件/帮助列表/事件/时区实现和存储例程之类的信息;

MySQL如何使用内存：

1、内存分配可以划分为以下两种类别:

• 全局(每实例内存):服务器启动时分配一次并在服务器关闭时释放,此内存在所有会话间共享;当所有物理内存用尽时,操作系统开始交换,这会对MySQL服务器性能具有不利影响,可能会导致服务器崩溃;
  
• 会话(每会话内存):基于每个会话(有时称为“线程”)动态进行分配;此内存可在会话结束时或不再需要会话时释放,此内存多用于处理查询结果,所使用的缓冲区大小基于每个连接;例如,read_buffer为10MB且具有100个连接意味着可能总共有100*10MB同时用于所有读取缓冲区;

内存结构：

服务器在运行时会为许多种类的数据分配内存:

1、查询高速缓存还用于加速处理重复发出的查询;

2、线程高速缓存:在MySQL(和其他程序)中使用线程将应用程序执行划分为两个或更多个同时运行的任务,将会为连接到MySQL服务器的每个客户机创建单独的线程以处理该连接;

3、缓冲区和高速缓存:缓冲区和高速缓存提供数据管理子系统并支持快速访问项目,例如授权表缓冲区,存储引擎缓冲区(如InnoDB的日志缓冲区)和保存开放表说明符的表开放缓冲区;如果使用MEMORY存储引擎,MySQL将使用主内存作为主体数据存储,其他存储引擎也可能使用主内存进行数据存储,但MEMORY是唯一的,未设计为在磁盘上存储数据;

4、连接/会话:1）.内部临时表:在某些查询执行情况下,MySQL会创建一个临时表来解析查询;可以在内存中或在磁盘上创建临时表,具体取决于其大小或内容或者查询语法;2）.特定于客户机的缓冲区:专门设计为支持所连接的各个客户机;缓冲区示例包括:用于交换信息的通信缓冲区;排序操作:表读取缓冲区(包括支持联接的缓冲区);

MySQL插件接口：

1、当前,插件API支持:

• 可用于替换或扩充内置全文解析器的全文解析器插件;例如,某个插件可以使用不同于内置解析器所使用的规则将文本解析为字,要解析具有不同于内置解析器所预期的特征的文本,这很有用;
• 向服务器提供低级别存储,检索和数据索引的存储引擎;
• 信息模式插件;信息模式插件作为MySQL INFORMATION_SCHEMA数据库中的表出现,稍后将更详细地讨论INFORMATION_SCHEMA数据库;
• 守护进程插件启动在服务器内运行的后台进程(例如,定期执行心跳处理);

2、插件接口需要mysql数据库中的PLUGINS表,此表是在MySQL安装过程中创建的

二、系统管理

MySQL服务器分发

1.MySQL可用于多个操作系统,包括Linux/Windows/Mac OS X和等;本文仅讲述Linux;

2.MySQL可以作为二进制文件分发和源代码分发的形式提供:

• 二进制文件分发:是预编译的,可以运行的程序,可用于Enterprise和Community MySQL Server版本,这些二进制文件是正式的经过Oracle测试的版本;
  
• 源代码分发:不保证与商业代码更新一致,它们也不包括Oracle支持;

MySQL二进制文件分发

1.用于Linux的二进制文件:

• RPM文件可用于基于RPM的Linux分发,例如Oracle Linux;通过使用rpm程序或者通过使用yum等软件包管理器来安装这些文件,每个RPM的安装布局由RPM文件自身内包含的规范文件提供;(使用rpm -qpl 来查看安装时RPM文件内容的位置)
• TAR文件可用于多种Linux和UNIX类似系统,要安装这种类型的分发,使用.tar程序在安装目录中解压缩该分发;

2.用于Windows的二进制文件:

• 完整分发:包含MySQL安装的所有文件以及配置向导。
  
• 非安装分发:.zip归档文件,不使用安装或配置向导,您只需解压缩该归档文件并将其移至所需的安装位置;
  

TIPS:二进制文件分发还可以压缩文件形式用于多个其他操作系统(包括Oracle Solaris);

MySQL源代码分发

1.如果需要预编译分发中可能没有的功能(例如完整的调试支持),可以根据源代码编译MySQL;

2.要使服务器在运行时使用较少内存,可能需要禁用不需要的功能;例如,可能需要禁用可选存储引擎,或者仅编译实际需要的那些字符集;

3.二进制文件分发仅可用于已发行的版本,不可用于最新的开发源代码;

4.源代码分发可以安装在任何所需位置;默认Linux安装位置为/usr/local/mysql;用于Linux的MySQL RPM安装文件

1.Oracle提供两种类型的MySQL RPM:

• 与分发无关:MySQL提供给社区的RPM,它们应该可以在支持RPM软件包并使用glibc 2.3的所有Linux版本上运行;(查看方式:ldd –version)
• 特定于分发:面向目标Linux平台,Oracle为许多平台提供了RPM文件;
  

2.MySQL的RPM安装通常分为不同的软件包,对于标准安装,必须至少安装服务器程序 和客户机程序,标准安装不需要其他软件包

Linux MySQL RPM安装过程

1.解压缩包:unizp MySQLxxx.zip;

2.创建用户:useradd mysql;

3.安装RPM包:rpm -ivh MySQLxxx.rpm;安装在运行时自动执行以下任务:

• 将RPM文件提取到其默认位置;
• 在/etc/init.d目录中注册名为mysql的启动脚本;
• 执行mysql_install_db,即创建系统数据库和默认my.cnf文件的脚本,为root帐户设置随机口令并将该口令保存在安装用户主目录中名为.mysql_secret的文件中;
• 为mysql设置登录帐户以及用户名和组名称(用于管理和运行服务器);

4.发生与自带版本冲突的话:

• 删除原来的包:yum remove mysql-xxx
• 添加–replacefiles选项:rpm -ivh –replacefiles MySQL–advanced;

5.产生的目录:

• /usr/bin:客户端程序和脚本;/usr/sbin:mysqld服务程序;/var/lib/mysql:数据库和日志文件,之后讲到数据库结构会讲每个文件的作用;/usr/share/info:info格式的MySQL手册;/usr/share/man:标准的Unix man格式手册;/usr/include/mysql:MySQL所需的头文件;/usr/lib64/mysql:库文件;/usr/share/mysql:其它杂项,包括支持文件,错误信息,字符集文件,示例配置文件和数据库安装的SQL文件等;/usr/share/sql-bench:测试基线;/etc/my.cnf, /usr/my.cnf:缺省配置文件;/etc/init.d/:包含了mysql启动脚本;/var/log:mysqld.log文件;

6.启动数据库:service mysql start;

• 查看后台进程:ps -ef | grep mysql;
  
• 本来是启动的mysqld服务,后台多了一个mysqld_safe服务,之后讲数据库启动的几种方式会提到;

• 查看到密码后再登录:mysql -uroot -p;如何使用SET PASSWORD: help SET PASSWORD,发现报错,必须先修改:SET PASSWORD = PASSWORD(“mysql”);

8.卸载数据库:

• 查看安装了哪些软件:yum list | grep MySQL;删除软件:yum remove MySQL-*;

在Linux上启动MySQL服务器;

1.可以使用多种方法在Linux上启动服务器:

• mysqld:手动调用服务器来调试MySQL服务器;默认情况下,错误消息传至终端,而不是错误日志;
• mysqld_safe:设置错误日志,然后启动mysqld并对其进行监视,如果mysqld异常终止(kill -9 pid),mysqld_safe会将其重新启动;如果服务器未正常启动,请查看错误日志;
• mysql.server:用作mysqld_safe的包装,针对使用System V运行级别目录的Linux和Oracle Solaris等系统;
• mysqld_multi:该Perl脚本用于简化单台主机上的多个服务器管理,它可以启动或停止服务器,它还可以报告服务器是否正在运行;
• 其它:mysqladmin -uroot -p start;

2.安装正确的脚本以使服务器在启动时自动运行:

• 在BSD样式的Linux系统上,最常见的是通过某一个系统启动脚本(例如/etc目录中的rc.local脚本)调用mysqld_safe;
• 在/etc/init.d下具有运行级别目录的Linux和UNIX System V变体使用mysql.server脚本,预先构建的 Linux二进制软件包针对相应的运行级别在名称mysql安装mysql.server;使用chkconfig注册服务;

在Linux上停止MySQL服务器

1.要手动停止服务器,请使用以下方法之一:

• mysqladmin:具有关闭命令,它作为客户机连接到服务器并且可以关闭本地或远程服务器;
  mysqladmin -uroot -p shutdown;
• mysql.server:在使用stop参数调用时停止和/或关闭本地服务器;
  
• mysqld_multi:停止和/或关闭其管理的任何服务器,它通过调用mysqladmin来执行此操作;

2.mysqld_safe没有服务器关闭功能;

提高安装安全性

1.从RPM软件包安装MySQL时,将为root帐户设置随机口令并将该口令保存到安装用户主目录中的.mysql_secret文件;对于所有其他安装,初始口令为空白;2.在不带参数的情况下调用mysql_secure_installation,这将提示您确定要执行的操作;

数据目录

1.每个表都具有*.frm文件(包括视图);

2.MySQL服务器开始执行时会将其当前工作目录转到其data目录,必须确保MySQL服务器具有正确的访问权限,以便在data目录中创建文件,该服务器必须可以访问它要在其中创建数据文件或日志文件的所有目录;

3.MySQL服务器将每个数据库映射到MySQL data目录下的一个目录,并且默认情况下,它将数据库中的表映射到数据库目录中的文件名;这具有以下含义:

• 数据库和表名称仅在具有区分大小写的文件名的操作系统(例如大多数Linux系统)上的MySQL服务器中才区分大小写;
  
• 可以通过将数据目录,数据库和/或单个表(具体取决于存储引擎选项)移至不同的物理位置来分割磁盘使用,这可以提高性能;

MySQL服务器发行版

1.升级之前还应该查看readme文档:

• 在关于升级的部分中,一定要阅读与正在执行的升级类型有关的注释,按照建议的过程执行操作;
  
• 在关于新版本的更改注释部分中,查看在当前版本与要安装的版本之间发生的所有更改,请注意不与当前版本向后兼容的所有更改;

2.RPM和源代码升级通常不需要进行重新配置,因为它们往往使用相同安装目录位置,而不考虑MySQL版本;3.需要进行一些重新配置的情况:

• 如果使用通用Linux二进制文件进行升级,可以选择创建特定于新版本的目录来包含升级的发行版;
  

4.设置指向旧安装目录的软链接,从而可以轻松删除并重新创建该链接来指向新安装目录,对该符号链接的后续引用将访问新安装;比如:把data目录做成一个软链接;

5.如果您的安装最初是通过安装多个RPM软件包而生成的,则最好升级所有软件包,而不仅是其中的一些;例如,如果先前安装了服务器和客户机RPM,则不要仅升级服务器RPM;

补充:

1.升级MySQL总体上可以简单使用以下步骤进行操作:

• 备份你的数据库;
• 关闭Server;
• 在已存版本上安装新版本MySQL;
  
• 启动Server;

2.MySQL当前提供的升级不提供跳级升级,因此,如果你当前的MySQL版本为5.1,那么升级到5.7的流程为:5.1->5.5->5.6->5.7;

检查升级的表

1.在每次进行MySQL升级时,都需要运行mysql_upgrade程序,主要是执行了以下操作:

• 检查数据库中的所有表与MySQL服务器当前版本是否兼容性;
  
• 修复表中发现的所有问题以及可能的不兼容性;
  
• 升级系统表来添加新版本中可用的所有新特权或功能;
  
• 使用当前MySQL版本号标记所有已检查和已修复的表;

2.mysql_upgrade会将MySQL版本号保存在数据目录下的一个mysql_upgrade_info的文件中,这个文件被用于快速查看是否所有表针对升级版本已经做了检查,是否可以跳过表检查;当然,运行mysql_upgrade时,你也可以使用–force项来跳过查看此文件;3.在MySQL 5.7.5之前,为了检查和修正表并进行系统表升级,mysql_upgrade会调用以下2条命令:

• mysqlcheck –check-upgrade –all-databases –auto-repairmysql_fix_privilege_tables

从MySQL 5.7.5以后,mysql_upgrade将直接和MySQL Server交互,发送所需的SQL语句来执行升级;补充:升级过程;

• 1.停止服务:service mysql stop;
• 2.备份文件:- 创建临时目录:mkdir /tmp/mbackup;- 备份配置文件:cp /etc/my.cnf /tmp/mbackup/;- 备份数据文件:cp -rf /var/lib/mysql/ /tmp/mbackup/mysql;也可以用mysqldump/xtrabackup;
• 3.删除软件:yum -y remove MySQL-*-advanced-5.6.25-1.el6.x86_64;(不推荐)
• 4.安装软件:rpm -ivh –replacefiles MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm;
• 5.拷贝回配置文件:cp /tmp/mbackup/my.cnf /etc/my.cnf;
• 6.尝试重启:service mysql restart;
• 7.执行更新:mysql_upgrade -uroot -p;
• 8.查看生成的更新文件并且尝试登录查看版本信息:less $MYSQL_DATADIR/mysql_upgrade_info;

使用多个服务器

1.要在运行生产服务器的同一台计算机上测试MySQL的新发行版时,需要运行多个服务器;假定每个组具有其自己的指定root用户,该用户无法查看属于其他组的数据库,如果所有 客户机将共享同一服务器则可能会这样;

2.不允许任何服务器共享必须由单个服务器独占使用的资源;

3.mysqld_multi脚本设计用来管理多个mysqld进程,这些进程监听不同UNIX套接字文件和TCP/IP端口上的连接,该脚本搜索my.cnf中名为[mysqldN]的组,然后将该N的设置应用于编号的实例;例如,要启动两个mysqld实例,它们分别应用来自my.cnf部分[mysqld3]和[mysqld5]的设置,请运行以下命令:

• shell> mysqld_multi start 3, 5

多个服务器选项

使用mysqld或mysqld_multi以及每个服务器函数的相应选项来调用每个MySQL服务器:

1.数据目录:使用–datadir选项的唯一值启动每个服务器;

2.网络:通过使用–port,–socket和–shared-memory-basename选项的唯一值启动每个服务器,将每个服务器设置为使用其自己的网络接口;

3.组名称:使用mysqld_multi时,每个服务器组在Linux或UNIX上必须具有唯一的mysqldN名称;

4.日志文件:每个服务器必须具有其自己的日志和PID文件;

5.InnoDB表空间和日志文件:不能由多个服务器共享;

6.Windows服务名称:每个mysqld Windows服务必须使用唯一的服务名称,通过使用–install设置服务名称;服务器启动时,它们从标准选项文件中的各个相应服务组中读取选项;

练习任务

1.创建和导入world_innodb数据库;

• $ mysql -uroot -p;mysql> CREATE DATABASE world_innodb;mysql> USE world_innodbmysql> SET autocommit=0;mysql> SOURCE /labs/world_innodb.sql;mysql> SET autocommit=1; // 为了提高运行速度,批量提交;

2.查看本地MySQL服务器数据目录;

• mysql> SHOW VARIABLES LIKE ‘datadir’\G

3.检查MySQL服务器的状态;

• service mysql status;关闭/启动MySQL服务器;service mysql stop/start;

更多内容请关注微信公众号：数据与人