MySQL架构组成、物理文件组成

L宝宝聊IT

发布于 2018-08-16 10:33:21

1.1K0

发布于 2018-08-16 10:33:21

Mysql是由MYSQL AB公司（目前已经被SUN公司收归麾下）自主研发的。是目前IT行业最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。 MySQL数据库以其简单高效可靠的特点，在最近短短几年的时间就从一个名不见经传的数据库系統，变成一个在IT行业几乎是无人不知的开源数据库管理系统。从小型的web网站，至大型的企业级应用，到处都可见其身影的存在。

一、MYSQL与其他数据库的简单比较

1）功能比较：

MySQL经过多年的改进和完善之后，已经基本具备了所有通用数据库管理系统所需要的相关功能。

MySQL基本实现了 ANSI SQL 92的大部分标准，仅有少部分并不经常被使用的部分没有实现。比如在字段类型支持方面，另一个著名的开源数据库 PostGreSQL支持的类型是最完整的，而Oracle和其他一些商业数据库，比如DB2、 Sybase等，较 MYSQL来说也要相对少一些。在事务支持方面，虽然MSQL自己的存储引擘井没有提供，但是己经通过第三方插件式存储引擎 Innodb实现了SQL 92标准所定义的四个事务隔离级别的全部。

不过在可编程支持方面，MySQL和其他数据库相比还有一定的差距，虽然最新版的 MySQL已经开始提供一些简单的可编程支持，如开始支持 Procedure， Function， Trigger等，但是所支持的功能还比较有限，和其他几大商用数据库管理系统相比，还存在较大的不足。如Oracle有强大的PL/SQL，SQL Server有T－SQL， PostGreSQL也有功能很完善的PL/PGSQL的支持。

整体来说， MYSQL的功能完全可以满足我们的通用商业需求，提供足够强大的服务。

2）易用性比较

从系统易用性方面来比较，每一个使用过 MYSQL的用户都能够明显地感觉出MYSQL在这方面与其他通用数据库管理系统之间的优势所在。尤其是相对于一些大型的商业数据库管理系统如 Oracle、DB2以及 Sybase米说，对于普通用户来说，操作的难易程度明显不处于一个级别。 MYSQL一直都奉行简单易用的原则，也正是靠这一特性，吸引了大量的初级数据库用户最终选择了MYSQL。

从安装方面来说，MYSQL安装包大小仅仅只有100MB左右，这与几大商业数据库完全不在一个数量级，安装难易程度也要比Oracle等商业数据库简单很多，不论是通过已经编译好的二进制分发包还是源码编译安装，都非常简单。

再从数据库创建来比较，MYSQL仅仅只需要一个简单的 CREATE DATABASE命令，即可在瞬间完成建库的动作，而 Oracle数据库与之相比，创建一个数据库简直就是一个非常庞大的工程。当然，二者数据库的概念存在一定差别。

3）性能比较

性能方面，一直是 MySQL引以为自豪的一个特点。在权威的第三方评测机构多次测试较量各种数据库TPCC值的过程中， MYSQL一直都有非常优异的表现，而且在其他所有商用的通用数据库管理系统中，仅仅只有 Oracle数据库能够与其一较高下。

4）可靠性

当前最火的Facebook这样大型的网站都是使用MYSQL数据库，而且排在全球前10位的大型网站里面，大部分都有部分业务是运行在MYSQL数据库环境上，如Yahoo，Google等。

总的来说，MYSQL数据库在发展过程中一直有自己的三个原则：简单、高效、可靠。

二、Mysql的主要适用场景

web网站系统、日志记录系统、数据仓库系统

三、MYSQL架构组成

日志文件：错误日志、查询日志、慢查询日志、事物日志、二进制日志。

1、错误日志：Error Log

在 mysql数据库中，错误日志功能是默认开启的。默认情况下，错误日志存储在 mysql数据库的数据目录中，错误日志文件通常的名称为 hostname.err．其中，hostname表示服务器主机名。

错误日志信息可以自己进行配置，错误日志所记录的信息是可以通过Iog-error和log-warnings来定义的，其中 log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。

默认情况下错误日志大概记录以下几个方面的信息：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件、如何初始化自己的存储引擎等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。

注1：mysql提供两组命令，分别查看系统设置和运行状态（1）查看系统设置：（show variables）

修改系统设置的方法：方法一：修改配置文件my.cnf 如：binlog_cache_size= 1M 方法二：set global binlog_cache_size = 1048576；（2）运行状态：(show status）

注2：查看MySQL的版本方法一：#mysql -V 方法二：

方法三：

1）查看错误日志的状态：

其中Log_error_verbosity：错误日志的级别（包括以下三种级别）

2）更改错误日志位置可以到配置文件my.cnf更改

3）查看错误日志：

4）删除和备份

在 mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。 mysql数据库中，可以使用 mysqladmin命令开启新的错误日志。

mysqladmin命令的语法如下：

mysqladmin -u root -p flush-logs

也可以登录 mysql数据库中使用 FLUSH LOGS语句来开启新的错误口志。

在mysq5.5.7之后：服务器将关闭此项功能。只能使用重命名原来的错误日志文件，手动冲洗日志创建一个新的：方式如下：

2、二进制日志：Binary Log & Binary LogIndex

二进制日志(binlog)，是 MYSQL Server中最为重要的日志之一，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以询 mysql数据库中进行了哪些变化。一般大小体积上限为1G

当我们通过“log_bin＝file-name”打开了记录的功能之后， Mysql会将所有修改数据库数据的 query以二进制形式记录到日志文件中。当然，日志中并不仅限于 query语句这么简单，还包括每一条 query所执行的时间，所消耗的资源，以及相关的事务信息，所以 binlog是事务安全的。

1）二进制日志开启状态：

2）Binlog的附加选项参数：

（1）”max_binlog_size”设置binlog的最大存储上限，一般设置为512M或1G，一般不能超过1G，当日志达到该上限时，mysql会重新创建一个日志开始继续记录。

（2）“binlog-do-db=db_name”用来明确告诉mysql需要对某个数据库记录binlog，忽略其它数据库，仅记录这个数据库执行的query。

（3）“binlog-ignore-db=db_name”与“binlog-do-db=db_name”完全相反，用来指定忽略哪个数据库。

（4）myq-bin.index文件（ binary log index）的功能是记录所有 Binary Log的绝对路径，保证MYSQL各种线程能够顺利的根据它找到所有需要的 Binary Log文件。

（5）binlog_cache_size＝32768＃默认值32768 binlog_cache_size：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到 Cache中：等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务,写入也不是特别须繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可以适当调高 binlog_cache_size。同时，我们可以通过 binlog_cache_use以及 binlog_cache_disk_use来分析设置的 binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。

（6）”binlog_stmt_cache_size = 32768” 当非事物语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存方这些语句。

（7）binlog-format＝ {ROW | STATEMENT | MIXED}＃指定二进制日志的类型，默认为MIXED

概念解释：mysql复制主要有三种方式：基于sql语句的复制（ statement-based replication，SBR），基于行的复制 （row-based replication，RBR），混合模式复制（mixed-based replication．MBR），对应的，binlog的格式也有三种： STATEMENT，ROW，MXED

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到 binlog中。优点是井不需要记录每一行的数据变化，减少了 binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致 master-slave中的数

据不一致（如sleep（）函数， last_insert_id（），以及 user-defined functions（udf）等会出现问题）

② ROW模式（RBR）

不记录每条sql句的信息，仅需记录哪条数据被修改了，修改成什么样了。缺点是会产生大量的日志，让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用 STATEMENT模式保存 binlog，对于 STATEMENT模式无法复制的操作使用ROW模式保存 biniog，MNQL会根据执行的sql语句选择日志保存方式，即交替使用行和语句、由mysql服务器自行判断。

其中基于行的定义格式数据会大一些但是可以保证数据的精确性。

（8）sync_binlog＝10＃设定多久同步一次二级制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步

（9）max_binlog_cache_size={4096 .. 18446744073709547520}

二进制日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。

（10）max_binlog_stmt_cache_size={4096 .. 18446744073709547520}

二进日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存

（11）expire_log_days ={0 .. 99}

设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或 FLUSH日志时。

3）通过编辑 my.cnf中的log_bin选项可以开启二进制日志：

Log_bin[=DIR/[filename])

其中，DIR参数指定二进制文件的存储路径；filename参数指定二级制文件的文件名，其形式为 filename.number，number的形式为000001、000002等，每次重启mysql服务或运行mysql＞ flush logs；都会生成一个新的二进日志文件，这样日志文件的number会不断地通增，除了生成上述的文件外还会生成一个名为 filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

4）查看二进制日志

5）当前使用的二进制文件及所处位置

6）查看当前二进制文件的信息：

7）查看二进制日志信息的语法：

SHOW BINLOG EVENTS[IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]

查看所有二进制信息

Mysql > show binlog events\G;

查看指定日志的二进制信息

Mysql > show binlog events in ‘mysql-bin.000003’\G;

从指定的事件位置开始

Mysql > show binlog events in ‘mysql-bin.000003’ from 721;

注：二进制日志的记录位置，通常为上一个事件执行结束时间的位置

8）指定偏移量（不是语句，是事件）

9）命令行下查看二进制日志：

必须使用mysqlbinlog命令。

10）删除二进制日志信息：

二进制日志会记录大量的信息（其中包含一些无用的信息）。如果很长时间不清理二进制日志。会浪费很多磁盘空间。但是删除之后可能导致数据库崩遗时无法进行恢复，所以删除日志前先将其和数据库备份一份。其中也只能删除备份前的二进制日志，新产生的日志信息不可删。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误。

删除二进制文件的方法如下。

方法1：根据文件或时间点来删除二进制日志。

语法：

Mysql > PURGE { BINARY | MASTER} LOGS {TO ‘log_name’ | BEFORE datetime_expr }

其中TO ‘log_name’表示把这个文件之前的其他文件都删除掉，也可使用BEFORE datetime_expr指定把哪个时间之前的二进制文件删除。

方法二：删除所有的二进制日志（慎用）:

Mysql > reset master;

注：删除所有的二进制日志后，mysql将会重新创建新的二进制日志。新二进制日志的编号从000001开始。

3、事物日志（redo日志）

事务日志（InnoDB特有的日志）可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

一般情况下，mysql会默认提供多种存储引擎，你可以通过下面的查看：

1）查看你的mysq现在已提供什么存储引擎

Mysql > show engines;

查看mysql当前默认的存储引擎：

Mysql > show variables like ‘%storage_engine%’;

查看某个表使用的引擎：

Mysql > show create table 表名；

注：

改变表的存储引擎(创建表时)

Create table 库名.表名 engine = innodb;

创建表后：

Alter table 库名.表名 engine = innodb;

查看事务日志的定义：

Mysql > show global variables like ‘%log%’;

其中

innodb_flush_log_at_timeout 1

innodb_flush_log_at_trx_commit 1 #在事务提交时innodb是否同步日志从缓冲区文件中，当这个值为1（默认值）时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新，性能会很差造成大量的磁盘I/O但这种方式最安全；如果设为2，每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100％每秒一定都刷会到磁盘，这要取决于进程的调度。每次事务提交的时假将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有缓存的，所以这个写入并不能保证数据已经写入到物理磁盘。设置为0，日志缓冲每秒一次地被写到日文件，并且对日志文件做到磁盘操作的刷新，但是在一个事务提交不做任何操作。

注：刷写的概念刷写其实是两个操作，刷（flush）和写（write），区分这两个概念是很重要的。在大多数的操作系统中，把Innodb的log buffer（内存）写入日志（调用系统调用write），只是简单的把数据移到操作系统缓存中，操作系统缓存同样指的是内存。并没有实际的持久化数据。所以，通常设为0和2的时候，在崩溃或断电的时候会丢失最后一秒的数据，因为这个时候数据只是存在于操作系统缓存。之所以说“通常”，可能会有丢失不只1秒的数据的情况，比如说执行flush操作的时候阻塞了。总结：设为1当然是最安全的，但性能也是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2，如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能。

每个事物日志的大小都是50兆的文件（不同版本有差异）

4、慢查询日志：slow query log

慢查询日志中记录的是执行时间较长的query，也就是我们常说的 slow query。

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻、执行所消耗的时间、执行用户、连接主机等相关信息。

慢查询日志的作用：

慢查询日志是用来记录执行时间超过指定时间的查询语句。Mysql还提供了专门用来分析慢查询日志的工具程序 mysqldumpslow，用来帮助数据库管理人员解决国可能存在的性能问题。

1）查看慢查询日志的定义：

2）启用和设置慢查询日志：

方法1：通过修改配置文件my.cnf开启

Slow_query_log：OFF关状态 ,ON开启状态 Slow_query_log_file：慢查询日志存放地点 Long_query_time：设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认认为10秒）这个査询语句将被记录到慢查询日志中，设置为0的话表示记录所有的查询。
Slow_launch_time：表示如果建立线程花费了比这个值更长的时间，slow_launch_threads计数器将增加。

注：如果不指定存储路径，慢査询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为 hostname-slow.log

修改my.cnf文件：