博主QQ：819594300

博客地址：http://zpf666.blog.51cto.com/

有什么疑问的朋友可以联系博主，博主会帮你们解答，谢谢支持！

一、MySQL Server 简介

什么是MySQL？

答：是目前IT行业最流行的开放源代码的数据库管理系统之一，它同时也是一个支持多线程高并发多用户的关系型数据库管理系统。

特点：

简单：安装简单

高效：读写性能高，仅次于oracle

可靠：运行稳定

MySQL 与其他数据库的简单比较：

1）       功能比较

2）       易用性比较

3）       性能比较

4）       可靠性

MySQL 的主要适用场景：

1）       Web 网站系统

2）       日志记录系统

3）       数据仓库系统

二、MySQL 架构组成

Mysql物理文件组成：

日志文件，主要包含：错误日志、查询日志、慢查询日志、事务日志、二进制日志

日志文件作用：记录着mysql数据库运行期间发生的变化；也就是说用来记录mysql数据库的客户端连接状况、SQL语句的执行情况和错误信息等。当数据库遭到意外的损坏时，可以通过日志查看文件出错的原因，并且可以通过日志文件进行数据恢复。

1、错误日志：Error Log

是否开启：默认已开启。

存储路径：默认在mysql数据库所在的路径。

名称：默认为hostname.err。其中，hostname表示服务器主机名。

配置：所记录的信息是可以通过log-error和log-warnings来定义。其中logwarnings是定义是否将警告信息也定义至错误日志中。

记录哪方面信息：

①   服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB（lnnoDB是插件式存储引擎）的表空间文件的、如何初始化自己的存储引擎的等等）

②   服务器运行过程中的错误信息

③   事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息。

注1：MySQL有很多系统变量可以设置，系统变量设置不同，会导致系统运行状态的不同。因此mysql提供两组命令，分别查看系统设置和运行状态。

①  查看系统设置

②运行状态

如何修改系统配置

方法1：配置文件设置my.cnf。例如：binlog_cache_size = 1M（永久设置）

方法2：例如：set  global binlog_cache_size = 1048576（临时设置）

注2：查看mysql的版本

或者

或者

说明：一般而言，日志级别的定义没有会话变量都只是在全局级别下进行定义。

查看错误日志的状态：

更改错误日志位置可以使用log-error来设置形式如下：

查看mysql错误日志：

说明：为了方便维护需要，有时候会希望将错误日志中的内容做备份并重新开始记录，这时候

就可以利用MySQL 的FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。

mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。

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

备份错误日志并生成新的错误日志文件

或者

更多信息请查阅官方文档：

http://dev.mysql.com/doc/refman/5.5/en/error-log.html

http://dev.mysql.com/doc/refman/5.6/en/error-log.html

http://dev.mysql.com/doc/refman/5.7/en/error-log.html

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

是否开启：默认不开启

记录哪方面的信息：记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。

容量：一般大小体积上限为1G

开启方法：在/etc/my.cnf文件中，通过“log-bin=file路径/file_name”

记录过程：开启了记录二进制文件之后，MySQL 会将所有修改数据库数据的query （即sql语句）以二进制形式记录到日志文件中。

名称：如果未手动指定file_name名称，则会在数据目录下记录为mysql-bin.******（*代表0～9 之间的某一个数字，来表示该日志的序号）。

查看二进制开启状态：

my.cnf配置文件中，关于binlog的参数及其含义

binlog-format={ROW|STATEMENT|MIXED}#指定二进制日志的类型，默认为MIXED。

说明：每次重启mysql服务或运行mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成filename.number文件外还会自动生成filename.index的文件。

查看二进制日志：

说明：二进制日志的定义方式为二进制格式；使用此格式可以存储更多的信息，并且可以使写入二进制日志的效率更高。但是不能直接使用查看命令打开并查看二进制日志。

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

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

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

查看所有的二进制信息：

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

从指定的事件位置开始

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

指定偏移量(不是语句，是事件)

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

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

mysqlbinlog命令的使用方式：

删除二进制日志信息：

说明：若要删除二进制日志首先将其和数据库备份一份，其中也只能删除备份前的二进制日志，新产生的日志信息不可删。也不可在关闭mysql服务器之后直接删除因为这样可能会给数据库带来错误的。若非要删除二进制日志需要做如下操作：导出备份数据库和二进制日志文件进行压缩归档存储。删除二进制文件的方法如下：

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

语法形式：

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

或者删除时间点之前的二进制日志文件

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

使用RESET MASTER语句可以删除所有的二进制日志。该语句的形式如下：

3、事务日志(或称redo日志)

说明：（InnoDB特有的日志）可以帮助提高事务的效率。

为什么会减少磁盘I/O资源：事务日志采用追加方式写入，因此此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。

名称：，在mysql中默认以ib_logfile0,ib_logfile1名称存在。

作用：修改数据时，数据存放在数据缓冲区，当提交事务时，事务缓冲区的数据将写入事务日志中，而数据缓冲区的数据将在某个时间后才写入数据文件，这样减少的磁盘I/O资源。

查看你的mysql现在已提供什么存储引擎:

看你的mysql当前默认的存储引擎:

你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

查看事务日志的定义：

总结：先有刷，后才有写。012三者的比较，最终性能最优的是0，但是默认是1

每个事物日志文件默认大小事50M（不同版本的mysql有差异）

4、   慢查询日志：slow query log

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

格式：采用的是简单的文本格式，可以通过各种文本编辑器查看其中的内容。

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

作用：通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化

是否开启：默认没开启

工具：MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow

查看慢查询日志的定义：

启动和设置慢查询日志：

方法1：通过配置文件my.cnf开启慢查询日志：

注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like "%slow%" 和showvariables like "%long%"查看出来。

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

修改my.cnf文件：

重启mysqld服务

再次查询慢查询日志定义：

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

或用系统查看文件内容命令如cat直接查看慢日志文件

第一行表示记录日志时的时间。其格式是 YYYY-MM-DDHH:MM:SS。我们可以看出上面的查询记录于 2016 年8 月 29 日下午 15:47：24 - 注意：这个是服务器时间.

MySql 用户、服务器以及主机名第三行表示总的查询时间、锁定时间、"发送"或者返回的行数

Query_time: 0.000304  表示用了0.000304秒

Lock_time: 0.000128  表示锁了0.000128秒

Rows_sent: 4 表示返回4行

Rows_examined: 4  表示一共查了4行

SET timestamp=UNIXTIME; 这是查询实际发生的时间

何将其变成一个有用的时间，将 Unix 时间转成一个可读的时间，可以使用 date –d@日志中的时间戳

慢查询分析mysqldumpslow

们可以通过打开log文件查看得知哪些SQL执行效率低下。从日志中，可以发现查询时间超过long_query_time时间的query为慢查询，而小于long_query_time时间的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果

进入log的存放目录，运行

数据文据

作用：用来存放改数据库中各种表数据文件。

数据库引擎：

MyISAM:扩展名是“.MYD”

Innodb：扩展名是“.idb”

Archive：扩展名是“.arc”

CSV   : 扩展名是“.csv”

如何查看你的mysql现在已提供什么存储引擎:

看你的mysql当前默认的存储引擎:

查要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):

注：create table 库名.表名 engine = 引擎名;  这样就可以将要建的表的引擎变更为其他引擎了（默认是innodb）

刚才在上面建了一个数据库bdqn，还在bdqn库里建了一个表it。

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

查看bdqn目录的文件列表

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

再次查看数据库bdqn目录

修改mysql的默认存储引擎的方法

1、      先用mysql> show engines;命令查看字段 Support为:Default的引擎是哪种

2、      在配置文件my.cnf中的 [mysqld] 下面加入“default-storage-engine=引擎名称”这一句话

3、      重启mysql服务器：①mysqladmin -u root -p shutdown

或者

②systemctl  restart  mysqld

“.frm”文件

作用：与表相关的元数据（meta）（元数据含义：描述数据的信息或属性的数据）信息都存放在“.frm”文件中，包括表结构的定义信息等。

所属哪个引擎：不论是什么存储引擎（MySQL常用的两个存储引擎是MyISAM和InnoDB），每一个表都会有一个以表名命名的“.frm”文件。

存放位置：所有的“.frm”文件都存放在所属数据库的目录下。

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

“.MYD”文件

所属哪个引擎：MyISAM 存储引擎专用

作用：存放MyISAM 表的数据，一个表一个”.MYD”文件

存放位置：和“.frm”文件在一起，，同样存放于所属数据库的文件夹下

“.MYI”文件

所属哪个引擎：也是专属于MyISAM 存储引擎

作用：主要存放MyISAM 表的索引相关信息，一个表一个”.MYI”文件

特别之处：于MyISAM 存储来说，可以被缓存的内容主要就是来源于“.MYI”文件中

存放位置：存放于位置和“.frm”以及“.MYD”一样

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

.ibd文件

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

ibdata1、ibdata2等（即ibdata文件）

说明以及作用：InnoDB共享表空间（即InnoDB文件集，ib-file set），存储InnoDB系统信息和用户数据库表数据和索引，所有表共用。

“.ibd”文件和ibdata 文件

相同点：这两种文件都是存放Innodb 数据的文件

既然作用一样，为啥要分两种文件：因为Innodb 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空间存放存储数据。

不同点：

①独享表空间存储方式使用“.ibd”文件来存放数据，且每个表一个“.ibd”文件，文件存放在和MyISAM 数据相同的位置。

②如果选用共享存储表空间来存放数据，则会使用ibdata 文件来存放，所有表共同使用一个（或者多个，可自行配置）ibdata 文件。

ibdata文件：innodb_data_home_dir(配置数据存放的总目录，即存放的路径)

             和

             innodb_data_file_path（配置每一个文件的名称，即文件名）

            两个参数共同配置组成。

innodb_data_file_path：一次可以配几个：可以一次配置多个ibdata 文件。

                       大小固定与否：文件可以是指定大小，也可以是自动扩展的。

                      特殊之处：Innodb限制了仅仅只有最后一个ibdata 文件能够配置成自动扩展类型。

                      新增新的ibdata文件怎么添加：只能添加在innodb_data_file_path配置的最后，而且必须重启MySQL才能完成ibdata 的添加工作。

                      额外说明：如果我们使用独享表空间存储方式的话，就不会有这样的问题。

总结：

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

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

3、独立表空间：每一个表都将会生成以独立的文件方式来进行存储，每一个表都有一个“.frm”（myisam引擎所有）表描述文件，描述表的表结构定义信息；还有一个“.ibd”（innodb引擎所有）文件，其中这个文件包含了独立一个表的数据内容以及索引内容。

即：使用innodb引擎的独立表空间占用大小=“.idb”文件的大小

使用myisam引擎的独立表空间占用大小=“.MYD”文件大小+“.MYI”文件大小

使用共享表空间的表不管什么引擎，其中的单独的一个表的表空间所占大小=Data_length+Index_legth

两者之间的优缺点：

共享表空间：

优点：

可以把表空间分成多个文件存放在各个磁盘上，数据和文件放在一起方便管理。

缺点：

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

独立表空间：

优点：

1、每个表都有自己独立的表空间。

2、每个表的数据和索引都会存在自己的表空间中。

3、可以实现单表在不同的数据库中移动。

4、空间可以回收。

a)drop table 操作自动回收表空间，如果对于统计分析或者日值表，删除大量数据后

可以通过”alter  table  表名 engine=innodb”回缩不用的空间。

b)对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点：

单表增加过大，例如超过100个G，相比较之下，使用独立表空间的效率以及性能会更高一点。

总结一句话：对于独立表空间，其中的数据删除后，空出来的空间可以回收。

            对于共享表空间，其中的数据删除后，会产生空隙（即碎片），无法回收。

查看当前数据库的表空间管理类型

Innodb共享表空间配置：

重启mysqld服务

mysqld启动失败,查看错误日志

修改配置为12M

再次尝试重启mysqld服务

如果不清楚默认文件page大小，可以先 du -h ibdata1 查看下，再去设置；

登录mysql执行mysql> show variables like‘%innodb_file_per_table%‘;

这时新建的表就会使用共享表空间了。

创建一个数据库testdb并新建一个表，并定义一个存储过程向表中插入1万个数据

调用存储过程

如何查看表在表空间占用情况：

方法1：对INNODB，你可以直接用命令showtable status查看某个表的表空间占用情况。

方法2：

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

Replication相关文件：

说明：与主从复制有关系

1）master.info 文件：

存放位置：存在于slave端的/usr/local/mysql/data目录下

存放了什么：存放了该Slave 的Master 端的相关信息，包括Master 的主机地址，连接用户，连接密码，连接端口，当前日志位置，已经读取到的日志位置等信息。

即包含的就是slave要连接master时。所输入的信息，如下例子所为；

2）relay log 和relay log index：

mysql-relay-bin.xxxxxn文件用于存放Slave 端的I/O 线程从Master 端所读取到的Binary Log 信息，然后由Slave 端的SQL 线程从该relaylog 中读取并解析相应的日志信息，转化成Master 所执行的SQL 语句，然后在Slave 端应用。

mysql-relay-bin.index文件的功能类似于mysql-bin.index ，同样是记录日志的存放位置的绝对路径，只不过他所记录的不是Binary Log，而是Relay Log。

3）relay-log.info 文件：

类似于master.info，它存放通过Slave 的I/O 线程写入到本地的relaylog 的相关信

息。供Slave 端的SQL 线程以及某些管理操作随时能够获取当前复制的相关信息。

其他文件：

1）system config file

MySQL 的系统配置文件一般都是my.cnf，默认存放在"/etc"目录下，my.cnf文件中包含多种参数选项组（group），每一种参数组都通过中括号给定了固定的组名，如“[mysqld]”组中包括了mysqld服务启动时候的初始化参数，“[client]”组中包含着客户端工具程序可以读取的参数。

2）pid file

pid file 是mysqld 应用程序在Unix/Linux 环境下的一个进程文件，和许多其他

Unix/Linux 服务端程序一样，存放着自己的进程id。

3）socket file

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

mysql有两种连接方式，常用的一般是tcp

mysql –h  mysql主机ip  -uroot  -p密码

mysql -S  /path /mysql.sock

“lsof   -c   进程名称”     //查看某个进程所关联额文件

如果Linux服务器中了病毒或者木马了该证明解决：

1）找出可以的进程

2）使用“lsof  -c  进程名”来查找出可以文件

本文出自 “IT技术助手” 博客，请务必保留此出处http://zpf666.blog.51cto.com/11248677/1910497

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