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

uyfrjk

　　一、MySQL简介
　　MySQL是由MySQL AB公司（已被ORACLE公司收购）自主研发的，目前IT行业最流行的开放源代码的数据库管理系统之一，是一个支持多线程高并发多用户的关系型数据库管理系统。
　　MySQL数据库发展的三个原则：简单、高效、可靠。
　　应用场景：
　　1）web网站系统
　　web站点是MySQL最大的客户群，原因主要是mysql数据库安装配置都非常简单、使用过程中的维护也并不复杂、最重要的一个就是mysql开放源代码完全免费。
　　2）日志记录系统
　　mysql数据库的插入和查询性能都非常的高效，在使用MyISAM存储引擎的时候，能够两者可以做到互不锁定，达到很高的并发性能。所以，对需要大量的插入和查询日志记录的系统来说，mysql是一个非常不错的选择。
　　3）数据仓库系统
　　通过mysql的简单复制功能，可以很好的将数据从一台主机复制到另外一台，局域网和广域网都可以。这样，我们可以将数据水平拆分，使用多台廉价的pc server和本地磁盘来存储数据，每台机器上面都只有所有数据的一部分，所有pc server一起并行计算，既可以解决计算性能的问题又可以解决I/O性能问题，同时成本也很低廉。
　　二、MySQL架构组成
　　mysql物理文件组成
　　1.日志文件
　　主要包括：错误日志、二进制日志、事务日志、查询日志、慢查询日志
　　1）错误日志：error log
　　（1）默认情况下：
　　错误日志功能是开启的
　　存储在mysql数据库的数据目中
　　通常命名为hostname.err，其中hostname表示服务器主机名。
　　记录信息包括：服务器启动和关闭过程中的信息、服务器运行过程中的错误信息、事件调度
　　器运行一个事件产生的信息、在从服务器上启动服务器进程时产生的信息。
　　注意1：mysql有很多系统变量可以设置（可以修改my.cnf添加系统变量），系统变量设置不同，会导致系统运行状态的不同。
　　mysql提供了两组命令，分别查看系统设置和运行状态：
　　（1）查看系统设置
　　show  [global | session]  variables  [like_or_where]
　　（2）运行状态
　　show  [global | session]  status  [like_or_where]
　　修改系统配置文件方法：
　　方法一：修改my.cnf，添加配置行，例如binlog_cache_size=1M
　　方法二：set  global  binlog_cache_size=1048576;
　　注意2：查看mysql的版本
　　方法一：[root@localhost ~]# mysql --version

　　方法二：mysql> status;

　　方法三：select  version();

　　（2）查看错误日志的状态：
　　mysql> show  global  variable  like  ‘%log_error%’;

　　其显示结果中，
　　log_error：定义错误日志文件路径
　　log_error_verbosity：可以理解为错误日志接受信息的级别，数值可以是1（errors only）、
　　2（errors and warnings）、3（errors,warnings and notes），默认为3.
　　（3）更改错误日志位置方法：
　　vim  /etc/my.cnf
　　log-error = /usr/local/mysql/data/mysql.err
　　（4）将错误日志中的内容备份并重新开始记录（flush logs）：
　　[root@localhost ~]# cd /usr/local/mysql/data/
　　[root@localhost data]# mv mysqld.err mysqld.err.old
　　[root@localhost data]# mysqladmin -u root -p flush logs
　　//注意：同样可以登录到mysql数据库中执行”flush  logs”来开启新的错误日志。
　　2）二进制日志：binary log & binary log index
　　（1）二进制日志，也就是常说的binlog，通过二进制日志可以查询mysql数据库中的变化情况。
　　（2）主要用于记录：修改数据或可能引起数据改变的mysql语句、语句的发生时间、执行时长、
　　操作的数据等。
　　（3）一般体积上限为1G
　　（4）打开二进制日志的记录功能：
　　修改my.cnf，添加配置行”log-bin=file_name”，如果未指定file_name，则二进制日志文件名称为  "主机名-bin.******"（*代表0~9之间的数字，来表示该日志的序号）。
　　（5）查看二进制日志开启状态：
　　mysql>show  global  variables  like  ‘%log_bin%’;

　　（6）每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件。
　　（7）binlog的一些附加选项参数：
　　max_binlog_size：定义binlog的最大存储上限，一般为512M或1G，一般不能超过1G。当日志达到该上限时，mysql会重新创建一个日志开始继续记录。特殊情况：偶尔出现超出，是因为在即将到达上限时，产生了一个较大的事务，为保证事务安全，mysql不会将同一个事务分开记录到两个binlog中。
　　binlog-do-db=db_name：忽略其他数据库执行的query，仅仅记录指定数据库的query。
　　binlog-ignore-db=db_name：与”binlog-do-db=db_name”相反。
　　mysql-bin.index：记录所有binary log的绝对路径，保证mysql线程能够根据它找到所需的binary log 文件。
　　binlog_cache_size = 32768：默认值为32768，一个事务，在没有提交的时候，产生的日志会记录到cache中；等到事务提交需要提交的时候，则把日志持久化到磁盘。一般，没有什么大事务，写入也不是特别频繁，1MB~4MB是一个合适的选择；如果大事务较多，写入量较大，可以适当调高binlog_cache_size大小。同时，可以通过”binlog_cache_use”和”binlog_cache_disk_use”（由于内存大小不够而使用临时文件）来分析binlog_cache_size是否足够。
　　binlog_stmt_cache_size=32768：当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句。
　　binlog-format={row|statement|mixed}：指定二进制日志的类型，默认为mixed。
　　注意：mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication,SBR)，基于行的复制(row-based-replication,RBR)，混合模式复制(mixed-based replication,MBR)，对应的，binlog的格式也有三种statement、row、mixed。
　　sync_binlog=10：设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正整数都表示对二进制每多少次写操作之后同步一次。
　　（8）查看二进制日志：
　　注意：二进制日志的定义方式为二进制格式，使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高，但是不能直接使用查看命令(cat、tail)打开并查看二进制日志。
　　mysql> show  binary  logs;    //列出所有的二进制日志

　　mysql> show  master  status;    //当前使用的二进制文件及所处位置

　　（8）查看二进制日志信息
　　语法格式：
　　show  binlog  events  [in  ‘log_name’]  [from pos]  [limit [offset,]  row_count]

　　mysql> show  binlog  events  in  ‘localhost-bin.000002’\G;    //查看指定日志的二进制信息

　　mysql> show  binlog  events  in  ‘localhost-bin.000002’  from  123\G;    //从指定的事件位置开始

　　注意：二进制日志的记录位置，通常为上一个事件执行结束时间的位置。
　　mysql> shoiw  binlog  events  in  ‘mysql-bin.000016’  from  727  limit  3;
　　//指定偏移量（不是语句，是事件）
　　[root@localhost ~]#mysqlbinlog /usr/local/mysql/data/mysql-bin.000016
　　//在命令行下查看二进制日志内容，正在执行mysql读写操作时建议不要这样打开。
　　（8）删除二进制日志信息
　　删除二进制日志首先将其和数据库备份一份，新产生的二进制日志信息不可删，也不可以在关闭mysql服务器之后直接删除因为这样会给数据库带来错误。
　　方法一：根据文件或时间点来删除二进制日志
　　语法形式：
　　mysql> purge  {binary | master}  logs  {to  ‘log_name’ | before  datetime_expr}
　　to  ‘log_name’：表示把这个文件之前的其他文件都删除
　　before  datetime_expr：表示把这个时间之前的二进制文件删除。
　　例：
　　mysql> purge  binary  logs  to  ‘mysql-bin.000006’;
　　mysql> purge  binary  logs  before  ‘2016-08-28  20:30：00’；
　　方法二：删除所有的二进制日志（谨慎使用）
　　mysql> reset  master;
　　3）事务日志（redo日志）
　　（1）事务日志（InnoDB特有的日志）可以帮助提高事务的效率，体现在一下几个方面：
　　·存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。
　　·事务日志采用追加方式，即写日志的操作是在磁盘上一小区块内的顺序I/O，不像随机I/O需要在磁盘的多个地方移动磁头。
　　·事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回磁盘。
　　（2）查看存储引擎
　　mysql> show  engines;//查看mysql提供的引擎都有哪些
　　mysql> show  variables  like  ‘%storage_engine%’;//查看当前默认的存储引擎
　　mysql> create  table  库名.表名  engine=innodb;//创建表时指定引擎
　　mysql> show  create  table  表名;//显示结果中可以看到该表使用的引擎

　　mysql>>

　　（3）查看事务日志的定义
　　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：最安全，性能也相对最差。
　　设为2：对数据一致性和完整性要求不高，性能又比设为1的时候好
　　设为3：只最求性能，例如高并发的日志服务器
　　（3）在mysql中，事务日志默认以ib_logfile0，ib_logfile1的名称存在
　　4）慢查询日志：slow  query  log
　　（1）慢查询日志记录的是执行时间较长的query，即slowquery，
　　包括：语句执行的时刻、执行所消耗的时间、执行用户、连接主机等相关信息。
　　（2）采用简单的文本格式，可以通过各种文本编辑器查看其内容。
　　（3）mysql提供了专门用来分析慢查询日志的工具程序mysqldumpslow。
　　（4）查看慢查询日志的定义：
　　mysql> show  global  variables  like  '%slow_query_log%';

　　slow_query_name的状态为OFF，表示慢查询日志功能处于关闭状态。
　　（5）开启慢查询
　　方法一：
　　修改my.cnf文件

　　重启mysqld服务
　　再次查询慢查询日志定义：

　　注意：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过show   variables  like  ‘%slow%’ 和 show  variables  like  ‘%long%’查看出来。
　　各个参数的含义：
　　slow_query_log：指定是否开启慢查询，为1时表示开启。
　　slow_query_log_file：指定慢查询日志的存放地点，可以为空，系统会给一个缺省的文件host_name-slow.log。
　　long_query_time：指定一个时间值，时间以秒为单位，可以精确到微妙。如果查询时间超过了这个时间（默认为10秒），这个查询语句会被记录到慢查询日志中，设置为0的话表示记录所有的查询。
　　slow_launch_time：表示如果建立线程花费了比这个值更长的时间，slow_launch_threads计数机将增加，”slow_launch_threads”用于记录创建线程时间过长的线程个数，可以通过show  variables  like  ‘slow_launch_time’来查查询。
　　方法二：
　　通过登录mysql服务器直接定义
　　mysql> set  global  slow_query_log=1;//开启慢查询日志
　　mysql> set  session  long_query_time=0.0001;//更改时间（当前session中，退出则重置）
　　mysql> set  global  long_query_time=0.0001;//更改时间（全局中，重启服务则重置）
　　mysql>
　　（2）mysqldumpslow慢查询分析工具
　　·直接查看慢查询日志

　　查询之前，降低慢查询时间上限，方便测试。

　　第一行，表示记录日志的时间，格式是YYYY-MM-DD HH:MM:SS。注意：这个是服务器时间。
　　第二行，mysql用户、服务器、主机名
　　第三行，总的查询时间、锁定时间、返回的行数、总共查的行数
　　第四行，这个时间戳表示这个查询实际的发生时间，可以使用”date  -d  @时间戳”来转换。
　　·慢查询日志文件内容很多时，可以使用mysqldumpslow工具来对慢查询日志进行分类汇总。

　　mysqldumpslow  --help
　　-s，表示按照何种方式排序，c、t、l、r分别表示按照记录数、查询时间、锁定时间、返回行数来排序。
　　-t，是top n的意思，返回前面多少条的数据
　　-g，后面可以写一个正则匹配模式
　　例：
　　mysqldumpslow  -s  r  -t  10  mysql-slow.log//得到返回记录集最多的10个查询
　　mysqldumpslow  -s  t  -t  10  -g  “left  join”  mysql-slow.log
　　//得到按照时间排序的前10条里面含有左连接的查询语句。
　　2.数据文件
　　在mysql中每一个数据库都会在定义好的数据库目录下存在一个以数据据库名字命名的文件夹，用来存放各种表数据文件。
　　存储引擎不同，数据文件也不相同，例如MyISAM用”.MYD”作为扩展名，”Innodb”用”.ibd”作为扩展名，等等。
　　注：mysql> show  engines;                               //查看mysql提供的引擎
　　mysql> show  variables  like  ‘%storage_engine%’;    //查看当前默认引擎
　　mysql> show  create  table  表名;                                               //查看某个表使用的引擎
　　例：create table 库名.表名 engine=innodb;这样就可以将表的引擎更改为innodb引擎了。
　　登录mysql，创建一个数据库testdb，并在数据库中创建一个表，如下图所示：

　　查看数据库所在目录会发现数据目录下会存在一个数据库名字命名的文件夹

　　查看testdb目录的文件列表

　　从上图可以看出表使用的是innodb存储引擎。
　　以MyISAM存储引擎创建一个测试表tb2

　　查看数据库目录

　　1）”.frm”文件
　　·与表相关的元数据文件(meta)信息都存放在”.frm”文件中，包括表结构的定义信息等。
　　·不论是什么存储引擎，每一个表都会有一个以表名命名的".frm"文件。
　　·所有的".frm"文件都存放在所属数据库的文件夹下面。
　　MyISAM数据库表文件：.MYD文件，表数据文件；.MYI文件，索引文件
　　2）".MYD"文件
　　".MYD"文件是MyISAM存储引擎专用，存放表的数据。每一个MyISAM表都都会有一个".MYD"文件与之对应，同样存放在所属数据库的文件夹下，和".frm"文件在一起。
　　3)".MYI"文件
　　·".MYI"文件也是专属于MyISAM存储引擎，主要存放表的索引相关信息。
　　·对于MyISAM存储引擎来说，可以被cache的内容主要就是来源于".MYI"文件中。
　　·每一个MyISAM表对应一个".MYI"文件，存放位置和".MYD"的一样。
　　InnoDB采用表空间(tablespace)来管理数据，存放表数据和索引。
　　它的数据文件类型有两种：单表表空间文件（独享表空间）、共享表空间文件。
　　Innodb的数据存储方式能够通过配置来决定是使用共享还是独享表空间存放数据。
　　4）".ibd"文件
　　·单表表空间存储方式使用".ibd"文件来存放数据。
　　·每一个表都有一个".ibd"文件，存放表数据和索引。
　　·文件存放在和MyISAM数据相同的位置。
　　5）"ibdata"文件
　　·共享表空间文件会使用ibdata文件来存放数据
　　·所有表共同使用一个（或多个，可自行配置）ibdata文件
　　·ibdata文件的大小、名称、位置可以自定义，用到连个配置项："innodb_data_home_dir"和"innodb_data_file_path"。
　　共享表空间与独享表空间优缺点
　　共享表空间
　　优点：
　　可以将表空间分成多个文件放到各个磁盘上，数据和文件放在一起方便管理。
　　缺点：
　　所有表的数据和索引存放到一个文件中，对于一个表做了大量删除操作后表空间中会有大量的空隙，特别对于系统分析、日志系统这类应用最不适合用共享表空间。
　　独享表空间
　　优点：
　　·每个表都会有自己独立的表空间
　　·每个表的数据和索引都会存放在自己的表空间中
　　·可以实现单表在不同的数据库中移动
　　·空间可以回收
　　相比较之下，使用独享表空间的效率以及性能会更高一点。
　　查看当前数据库的表空间管理类型

　　ON代表独立表空间管理，OFF代表共享表空间管理
　　Innodb共享表空间配置：
　　修改my.cnf文件

　　innodb_data_home_dir：指定数据库文件的存放目录
　　innodb_data_file_path：配置ibdata文件名称及大小，文件可以有多个，但是只有最后一个可以配制成自动扩展类型。
　　innodb_file_per_table：指定表空间类型，0表示共享表空间，1表示独占表空间
　　innodb_log_group_home_dir：日志存放目录
　　重启服务发现报错

　　查看mysql的错误日志，从错误日志显示的内容看出：
　　ibdata1应该设置为768pages，而64pages相当于1M，则768pages就是12M。

　　重新修改my.cnf，然后重启mysqld服务成功。

　　登录mysql，查看表空间控制类型，已经变为了共享表空间。

　　查看共享表空间文件

　　这时新建的表就会使用共享表空间了。
　　下面创建一个表，并且查看表空间的占用情况。
　　在库testdb中创建一个表tb3。

　　新建一个存储过程，用来向表tb3中插入数据。

　　重新登录了一下数据库，调用存储过程。

　　查看表中的行数

　　查看表空间占用情况：
　　方法一：

　　方法二：

　　数据库information_schema中的表tables相当于一个数据字典，这个表中的主要字段分别是：
　　table_schema：数据库名
　　table_name：表名
　　engine：所使用的存储引擎
　　table_rows：记录数
　　data_length：数据大小
　　index_length：索引大小
　　3.Replication相关文件（与主从复制相关）
　　1）master.info文件
　　·master.info文件存在于slave端的数据目录下
　　·存放了该slave的master端的相关信息，包括master的主机地址、连接用户、连接密码、连接端口、当前日志位置、已经读取到的日志位置等信息。
　　2）relay log和relay log index
　　· mysql-relay-bin.xxxxxn文件用于存放slave端的I/O线程从master读取到的Binary log信息，然后由slave端的SQL线程从该relay log中读取并解析，转化成为master所执行的SQL语句，然后在slave中应用。
　　· mysql-relay-bin.index文件功能类似于mysql-bin.index，用于记录日志的存放位置的绝对路径，只不过它记录的不是Binary log，而是relay log。
　　3）relay-log.info文件
　　类似于master.info，它存放通过slave的I/O线程写入到本地的relay log的相关信息，供slave端SQL线程以及某些管理操作随时能够获得当前复制的相关信息。
　　4.其他文件
　　1）system config file
　　·MySQL的系统配置文件一般都是my.cnf，默认存放在”/etc”目录下。
　　·my.cnf文件中包含多种参数选项组，每一种参数组都通过中括号给定固定的组名，如”[mysql]”组中包括了mysqld服务启动时的初始化参数，”[client]”组中包含了客户端工具可以读取的参数。
　　2）pid file
　　Unix/Linux环境特有的，存放服务进程的id
　　3）socket file
　　也是Unix/Linux环境特有的，通过该文件，用户在unix/linux环境下客户端连接可以不使用tcp/ip网络而直接使用unix socker 来连接mysql。
　　注：mysql有两种连接方式：
　　mysql  -u用户名  -p密码  -h主机
　　mysql  -S  /path/mysql.sock    //只适用mysql和应用在同一台PC上