MySql入门--基础知识

一、MySQL物理文件组成

成都创新互联自2013年起，先为祁东等服务建站，祁东等地企业，进行企业商务咨询服务。为祁东企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。

（一）日志文件

1、错误日志：Error Log

错误日志记录了MyQL Server运行过程中所有较为严重的警告和错误信息，以及MySQLServer每次启动和关闭的详细信息。在默认情况下，系统记录错误日志的功能是关闭的，错误信息被输出到标准错误输出（stderr），如果要开启系统记录错误日志的功能，需要在启动时开启-log-error选项。错误日志的默认存放位置在数据目录下，以hostname.err命名。但是可以使用命令：--log-error[=file_name]，修改其存放目录和文件名。

为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候就可以利用MySQL的FLUSH LOGS命令来告诉MySQL备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。

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

二进制日志，也就是我们常说的binlog，也是MySQL Server中最为重要的日志之一。当我们通过“--log-bin[=file_name]”打开了记录的功能之后，MySQL会将所有修改数据库数据的query以二进制形式记录到日志文件中。当然，日志中并不仅限于query语句这么简单，还包括每一条query所执行的时间，所消耗的资源，以及相关的事务信息，所以binlog是事务安全的。

和错误日志一样，binlog记录功能同样需要“--log-bin[=file_name]”参数的显式指定才能开启，如果未指定file_name，则会在数据目录下记录为mysql-bin.******（*代表0～9之间的某一个数字，来表示该日志的序号）。

binlog还有其他一些附加选项参数：

“--max_binlog_size”设置binlog的最大存储上限，当日志达到该上限时，MySQL会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL不会将同一个事务分开记录到两个binlog中。

“--binlog-do-db=db_name”参数明确告诉MySQL，需要对某个（db_name）数据库记录binlog，如果有了“--binlog-do-db=db_name”参数的显式指定，MySQL会忽略针对其他数据库执行的query，而仅仅记录针对指定数据库执行的query。

“--binlog-ignore-db=db_name”与“--binlog-do-db=db_name”完全相反，它显式指定忽略某个（db_name）数据库的binlog记录，当指定了这个参数之后，MySQL会记录指定数据库以外所有的数据库的binlog。

“--binlog-ignore-db=db_name”与“--binlog-do-db=db_name”两个参数有一个共同的概念需要大家理解清楚，参数中的db_name不是指query语句更新的数据所在的数据库，而是执行query的时候当前所处的数据库。不论更新哪个数据库的数据，MySQL仅仅比较当前连接所处的数据库（通过use db_name切换后所在的数据库）与参数设置的数据库名，而不会分析query语句所更新数据所在的数据库。

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

3、更新日志：update log

更新日志是MySQL在较老的版本上使用的，其功能和binlog基本类似，只不过不是以二进制格式来记录而是以简单的文本格式记录内容。自从MySQL增加了binlog功能之后，就很少使用更新日志了。从版本5.0开始，MySQL已经不再支持更新日志了。

4、查询日志：query log

查询日志记录MySQL中所有的query，通过“--log[=fina_name]”来打开该功能。由于记录了所有的query，包括所有的select，体积比较大，开启后对性能也有较大的影响，所以请大家慎用该功能。一般只用于跟踪某些特殊的sql性能问题才会短暂打开该功能。默认的查询日志文件名为hostname.log。

5、慢查询日志：slow query log

顾名思义，慢查询日志中记录的是执行时间较长的query，也就是我们常说的slowquery，通过设--log-slow-queries[=file_name]来打开该功能并设置记录位置和文件名，默认文件名为hostname-slow.log，默认目录也是数据目录。

慢查询日志采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。其中记录了语句执行的时刻，执行所消耗的时间，执行用户，连接主机等相关信息。MySQL还提供了专门用来分析满查询日志的工具程序mysqlslowdump，用来帮助数据库管理人员解决可能存在的性能问题。

6、Innodb的在线redo日志：innodb redo log

Innodb是一个事务安全的存储引擎，其事务安全性主要就是通过在线redo日志和记录在表空间中的undo信息来保证的。redo日志中记录了Innodb所做的所有物理变更和事务信息，通过redo日志和undo信息，Innodb保证了在任何情况下的事务安全性。Innodb的redo日志同样默认存放在数据目录下，可以通过innodb_log_group_home_dir来更改设置日志的存放位置，通过innodb_log_files_in_group设置日志的数量。

（二）数据文件

在MySQL中每一个数据库都会在定义好（或者默认）的数据目录下存在一个以数据库名字命名的文件夹，用来存放该数据库中各种表数据文件。不同的MySQL存储引擎有各自不同的数据文件，存放位置也有区别。多数存储引擎的数据文件都存放在和MyISAM数据文件位置相同的目录下，但是每个数据文件的扩展名却各不一样。如MyISAM用“.MYD”作为扩展名，Innodb用“.ibd”，Archive用“.arc”，CSV用“.csv”，等等。

1、“.frm”文件

与表相关的元数据（meta）信息都存放在“.frm”文件中，包括表结构的定义信息等。不论是什么存储引擎，每一个表都会有一个以表名命名的“.frm”文件。所有的“.frm”文件都存放在所属数据库的文件夹下面。

2、“.MYD”文件

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

3、“.MYI”文件

“.MYI”文件也是专属于MyISAM存储引擎的，主要存放MyISAM表的索引相关信息。对于MyISAM存储来说，可以被cache的内容主要就是来源于“.MYI”文件中。每一个MyISAM表对应一个“.MYI”文件，存放于位置和“.frm”以及“.MYD”一样。

4、“.ibd”文件和ibdata文件

这两种文件都是存放Innodb数据的文件，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM数据相同的位置。如果选用共享存储表空间来存放数据，则会使用ibdata文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata文件。ibdata文件可以通过innodb_data_home_dir和innodb_data_file_path两个参数共同配置组成， innodb_data_home_dir配置数据存放的总目录， 而innodb_data_file_path配置每一个文件的名称。当然， 也可以不配置innodb_data_home_dir而直接在innodb_data_file_path参数配置的时候使用绝对路径来完成配置。innodb_data_file_path中可以一次配置多个ibdata文件。文件可以是指定大小，也可以是自动扩展的，但是Innodb限制了仅仅只有最后一个ibdata文件能够配置成自动扩展类型。当我们需要添加新的ibdata文件的时候，只能添加在innodb_data_file_path配置的最后，而且必须重启MySQL才能完成ibdata的添加工作。不过如果我们使用独享表空间存储方式的话，就不会有这样的问题，但是如果要使用裸设备的话，每个表一个裸设备，可能造成裸设备数量非常大，而且不太容易控制大小，实现比较困难，而共享表空间却不会有这个问题，容易控制裸设备数量。我个人还是更倾向于使用独享表空间存储方式。当然，两种方式各有利弊，看大家各自应用环境的侧重点在那里了。

上面仅仅介绍了两种最常用存储引擎的数据文件，此外其他各种存储引擎都有各自的数据文件，读者朋友可以自行创建某个存储引擎的表做一个简单的测试，做更多的了解。

（三）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线程以及某些管理操作随时能够获取当前复制的相关信息。

2.1.4其他文件：

1、system config file

MySQL的系统配置文件一般都是“my.cnf”，Unix/Linux下默认存放在"/etc"目录下，Windows环境一般存放在“c:/windows”目录下面。“my.cnf”文件中包含多种参数选项组（group），每一种参数组都通过中括号给定了固定的组名，如“[mysqld]”组中包括了mysqld服务启动时候的初始化参数，“[client]”组中包含着客户端工具程序可以读取的参数，此外还有其他针对于各个客户端软件的特定参数组，如mysql程序使用的“[mysql]”，mysqlchk使用的“[mysqlchk]”，等等。如果读者朋友自己编写了某个客户端程序，也可以自己设定一个参数组名，将相关参数配置在里面，然后调用mysql客户端api程序中的参数读取api读取相关参数。

2、pid file

pid file是mysqld应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，存放着自己的进程id。

3、socket file

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

二、User、schema、database概念

In MySQL:

·         server instance == not identified with catalog, just a set of databases

·         database == schema == catalog == a namespace within the server.

·         user == named account, who is can connect to server and use (but can not own - no concept of ownership) objects in one or more databases

·         to identify any object you need (database name + object name)

In Oracle:

·         server instance == database == catalog == all data managed by same execution engine

·         schema == namespace within database, identical to user account

·         user == schema owner == named account, identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas

·         to identify any object you need (schema name + object name)

三、MySQL默认数据库mysql、information_schema等

l  mysql

数据库mysql：这个是mysql的核心数据库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息。不可以删除，如果对mysql不是很了解，也不要轻易修改这个数据库里面的表信息。

l  information_schema

information_schema数据库是MySQL自带的，它提供了访问数据库元数据的方式。什么是元数据呢？元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

在 MySQL中，把 information_schema看作是一个数据库，确切说是信息数据库。其中保存着关于MySQL服务器所维护的所有其他数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。在INFORMATION_SCHEMA中，有数个只读表。它们实际上是视图，而不是基本表，因此，你将无法看到与之相关的任何文件。

l  performance_schema

MySQL 5.5新增一个存储引擎：命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表

performance_schema提供以下功能：

1.提供进程等待的详细信息，包括锁、互斥变量、文件信息；

2.保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；

3.对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

通过以上得到的信息，DBA能够较明细得了解性能降低可能是由于哪些瓶颈？

Performance的开启很简单，在my.cnf中[mysqld]加入performanc_schema，检查性能数据库是否启动的命令:

SHOW VARIABLES LIKE ‘performance_schema’;

若是返回的 值为ON，则说明性能数据库正常开启状态。