mysql物理文件组成

一：MySQL 与其他数据库比较

创新互联是一家专业提供阿尔山企业网站建设,专注与成都做网站、网站设计、外贸营销网站建设、H5建站、小程序制作等业务。10年已为阿尔山众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。

1）功能比较

作为一个成熟的数据库系统管理系统，现在已经具备通用数据库管理系统的相关功能

2）易用性比较

对于普通用户操作难度相对简单易用原则，从安装方面来说，MySQL安装包仅仅只有100MB左右，安装难易程度要比Oracl，数据库简单很多，不论是通过已经编译好的二进制分发包还是源码编译安装，都比较简单

3）性能比较

MySQL非常优异表现，而且其他商用的通用数据库系统中，只有Oracle数据库能相提并论。

4可靠性）MySQL数据库发展过程三个原则， 简单，高效，可靠。

二：MySQL主要场景

1）web网站系统

MySQ;是开放源代码，可以免费使用

2）日志记录系统

MySQL数据库插入和查询都非常高效，使用MyISAM存储时候 两者可以互不锁定，达到很高效的并发能力，例如，登陆日志，操作日志，都非常应用场景。

3）数据仓库系统

随着数据量增长，需要空间越来越大，使数据统计分析变得越来越低，解决思路

采用高性能主机提高运算，用高端存储设备提高I\O性能，2就是通过数据复制多台使用大容量硬盘上以提高性能运算能力，但是存储空间有一定限制，3可以通过水平拆分来存放数据，MySQL都有较大优势，通过简单复制功能，可以将数据从一台主机复制到另外一台主机、

三：物理文件组成

日志文件：错误日志 查询日志 慢查询日志 事物日志

日志记录这mysql数据库运行期间发生变化，相当于记录mysql数据库连接客户的连接状况

SQL语句执行情况和错误信息等，当数据意外损坏时，可以通过错误日志看出原因，并且日志进行数据恢复。

错误日志：在mysql中默认是开启的，错误日志存储在mysql数据库目录中

错误日志信息可以自己进行配置，错误日志可以通过log-error和存储位置log-warninge定义

是否将警示信息定义至错误日志中，

服务器运行过程错误信息，事件调度器运行一个事件产生，在从服务器启动服务器进程产生的信息。

因此mysql提供两组命令，分别查看系统设置和运行状态。 
1、查看系统设置： 
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] 
SHOW VARIABLES：shows the values of MySQL system variables. 
2、运行状态： 
SHOW [GLOBAL | SESSION] STATUS [like_or_where] 
SHOW STATUS：provides server status information.

 如何修改系统配置

方法1：配置文件设置my.cnf

如：binlog_cache_size = 1M

方法2：set globalbinlog_cache_size = 1048576;

使用set 将安全级别改为2

3：查看mysql版本

Mysql -V     mysql> status;   select version ();

4错误日志状态

注 log_error定义错误文件路径

   Log__error_verbosity  安全级别

 查看错误日志

 Tail  /usr/local/mysql/data/mysqld.err

二：删除错误日志

数据库管理员可以删除很久以前的错误日志，为保证服务器硬盘空间，可以使用、mysqladmin -u root  flush-logs  FLUSH LOGS开启新的错误日志

二进制日志

主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录语句发生时间，执行时长，操作数据等等。一般体积上限为1G

首先看到二进制是关闭的OFF 需要在vim /etc/my.conf进行配置

Bin_log=/usr/local/mysql/data/log_bin  开启

一般设置为512或者1个G达到上限时mysql重新创建一个日志开始记录

为保证mysql安全 不会将同一个事物分开记录俩个binlog中

事务日志

事物日志表将一个事物任务提交，对包含在事物中的多条语句要么执行，要么全部不执行。

非事物表则不支持此操作，处理中如果遇到错误，在错误前语句执行成功，之后则执行不成功

 指定二进制日志类型

 Mysql复制主要有三种方式 基于SQL语句复制 混合模式复制  基于行复制

  三 通过编辑my.conf中log-bin选择开启二进制日志

其中 DIR参数指定二进制文件存储路径filename参数指定二级制文件文件名，形式为filename.number形式为000001. 000002等 每次重启mysql服务或运行flush logs；都会生成一个新的二进制文件

查看二进制文件信息

查看二进制日志信息的命令：

语法格式：SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

#查看所有的二进制信息

mysql> show binlog events\G;

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

mysql> show binlog events in 'mysql-bin.000013'\G;

 删除二进制日志信息

长时间不清理二进制文件，影响磁盘空间，删除之后可能导致数据库奔溃无法恢复，所以删除二进制首先和数据库备份

语法形式：

mysql> PURGE { BINARY | MASTER } LOGS {TO 'log_name' | BEFORE datetime_expr }

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

也可以指定时间来删除二进制日志

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

由于无法使用cat等方式直接打开并查看二进制日志；所以必须使用mysqlbinlog命令。但是当正在执行mysql读写操作时建议不要使用此打开正在使用的二进制日志文件

删除所以二进制日志

3 事物日志

可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。mysql会默认提供多种存储引擎,你可以通过下面的查看:
查看你的mysql现在已提供什么存储引擎:
mysql> show engines;
create table库名.表名 engine = innodb;
这样就可以将表的引擎变更为innodb引擎了。
也可以在创建表之后通过下面语句来变更：
alter table库名.表名engine =innodb;

在事务提交时innodb是否同步日志从缓冲区到文件中，当这个值为1（默认值）之时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新，性能会很差造成大量的磁盘I/O但这种方式最安全

刷写的概念

设为1当然是最安全的，但性能页是最差的（相对其他两个参数而言，但不是不能接受）。如果对数据一致性和完整性要求不高，完全可以设为2,如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能

慢查询日志

记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。

慢查询日志的作用：

慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。

启动和设置查询日志

slow_query_log：off关闭状态  on开启状态
slow_query_log_file  慢查询日志存放地点

long_query_time选项来设置一个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询。

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

修改my.cnf文件：

方法2：通过登录mysql服务器直接定义，方式如下：

mysql>set global slow_query_log=1;  #开启慢查询日志

Query OK, 0 rowsaffected (0.35 sec)

mysql>set session long_query_time=0.0001; #更改时间（当前session中，退出则重置）

Query OK, 0 rowsaffected (0.00 sec)

mysql>set global long_query_time=0.0001; #更改时间（全局中，重启服务则重置）

mysql> SHOW VARIABLES LIKE 'long%';  #查询定义时间

查看慢查询日志

mysql> use mysql

mysql> selectuser,host from user where user="root";

3)数据文据

在MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。

mysql> show create table 表名;

create table库名.表名 engine = innodb;  这样就可以将表的引擎变更为innodb引擎了。

登录mysql，创建一个数据库如testdb，并在数据库中创建一个表

以myisam存储引擎创建一个测试表tb2

.frm”文件

与表相关的元数据（meta）信息都存放在“.frm”文件中，包括表结构的定义信息等。不论是什么存储引擎（MySQL常用的两个存储引擎是MyISAM和InnoDB），每一个表都会有一个以表名命名的“.frm”文件。

MyISAM数据库表文件：.MYD文件：表数据文件；.MYI文件：索引文件

MYD”文件

“.MYD”文件是MyISAM存储引擎专用，存放MyISAM表的数据。每一个MyISAM表都会有一个“.MYD”文件与之对应，同样存放于所属数据库的文件夹下，和“.frm”文件在一起。

MYI”文件

“.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。

InnoDB采用表空间（tablespace）来管理数据，存储表数据和索引。

.ibd文件：单表表空间文件，每个表使用一个表空间文件（file per table），存放用户数据库表数据和索引。

.ibd”文件和ibdata文件

这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。

共享表空间以及独占表空间都是针对数据的存储方式而言的。

共享表空间:  某一个数据库的所有的表数据，索引文件全部放在一个文件中。

独占表空间:  每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容。

所有的数据和索引存放到一个文件中，多个表及索引在表空间中混合存储，这样对于一个表做了大量删除操作后表空间中将会有大量的空隙，特别是对于统计分析，日值系统这类应用最不适合用共享表空间。

独立表空间：

优点：
1．每个表都有自已独立的表空间。
2．每个表的数据和索引都会存在自已的表空间中。
3．可以实现单表在不同的数据库中移动。
4．空间可以回收
查看当前数据库的表空间管理类型

ON代表独立表空间管理，OFF代表共享表空间管理

Innodb共享表空间配置：

768/64=12

innodb_data_home_dir = "/path/" 数据库文件所存放的目录

innodb_log_group_home_dir = "/path/" 日志存放目录

innodb_data_file_path=ibdata1:10M:autoextend 设置一个可扩展大小的尺寸为10MB的数据文件（共享数据文件），名为ibdata1。没有给出文件的位置，所以默认的是在MySQL的数据目录内。

innodb_file_per_table=1｜0   //1为使用独占表空间，0 为使用共享表空间

在/etc/my.cnf文件中设置6400页而当前ibdata1为768页

需要计算768/64=12
启动mysql，成功！

计算公式：64pages相当于1M，1page是16KB

调用存储过程

查看表中行数：

对INNODB，直接用命令show table status查看某个表的表空间占用情况

MySQL数据库中每个表占用的空间、表记录的行数的话，可以打开MySQL的information_schema数据库。在该库中有一个TABLES表，这个表主要字段分别是：

TABLE_SCHEMA : 数据库名
TABLE_NAME：表名
ENGINE：所使用的存储引擎
TABLE_ROWS：记录数
DATA_LENGTH：数据大小
INDEX_LENGTH：索引大小

master.info文件

包括Master的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。

 elay 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线程以及某些管理操作随时能够获取当前复制信息

 socket文件也是在Unix/Linux环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接使用Unix Socket来连接MySQL。

mysql有两种连接方式，常用的一般是tcp
mysql –h  mysql主机ip -uroot  -pxxx
mysql -S  /path /mysql.sock