mysql数据库的参数以及性能的调配

 mysql数据库的特性以及参数性能

一：mysql与其他数据库的比较

MySQL是一个关系型数据库管理系统，开发者为瑞典MySQL AB公司，现在已经被Sun公司收购，支持FreeBSD、Linux、MAC、Windows等多种操作系统与其他的大型数据库例如Oracle、DB2、SQL Server等相比功能稍弱一些

1、可以处理拥有上千万条记录的大型数据

2、支持常见的SQL语句规范

3、可移植行高，安装简单小巧

4、良好的运行效率，有丰富信息的网络支持

5、调试、管理，优化简单(相对其他大型数据库)

易用性比较

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

性能比较

   MySQL一直以来奉行一个原则，那就是在保证足够稳定性的前提下，尽可能地提高自身的处理能力。也就是说，在性能和功能方面，MySQL第一考虑的要素主要还是性能，MySQL希望能够在满足客户99%的需求的前提下，将剩余的所有精力都用来努力提高系统性能，而不希望自己是一个比其他任何数据库的功能都要强大的产品。

总体来说，MySQL数据库在发展过程中一直追求三项原则：简单、高效、可靠。

 

二：mysql架构组成

 mysql物理文件组成：

1）日志文件：主要包含{错误日志、查询日志、慢查询日志、事物日志、二进制日志}

日志是mysql数据库的重要组成部分。记录mysql数据库运行期间发生的变化，如mysql数据库的客户端连接状况、sql语句执行情况和错误信息。当数据库遭到损坏时，可以通过日志查看文件记录的出错的原因，并且可以通过日志文件进行数据恢复。

首先挨个介绍日志的功能：

错误日志：Error Log

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

错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-error是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中。记录的内容信息包括：服务器启动和关闭过程中的信息（未必是错误信息，如mysql如何启动InnoDB的表空间文件的、如何初始化自己的存储引擎的等等）、服务器运行过程中的错误信息、事件调度器运行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息

mysql有很多的系统变量可以设置，系统的变量设置不同，会导致系统运行状态不同

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.

那么接下来修改系统配置：在主配置文件中

vi /etc/my.cnf

    例如：log_bin=/usr/local/mysql/data/

查看mysql的版本

 

或

 

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

错误日志的状态：

 

log-error定义为错误日志文件路径

log-error-verbosity

The MySQL error log has received some attention in MySQL 5.7, with a new setting called log_error_verbosity.

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

在主配置文件中： vi /etc/my.cnf

log-error = /usr/local/mysql/data/mysqld.err

查看mysql的错误日志内容：

 

在工作中有时候希望将错误日志做备份，并且重新记录，这时候可以使用mysql的flush logs刷新日志进行生成新的日志文件。备份文件为.beifen结尾

删除错误日志：

在mysql5.5.7之前：数据库管理员可以删除很长时间之前的错误日志，以保证mysql服务器上的硬盘空间。mysql数据库中，可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下：mysqladmin –u root –pflush-logs也可以登录mysql数据库中使用FLUSHLOGS语句来开启新的错误日志。

在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

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

二进制日志，俗称Binary Log，主要用于记录修改数据或有可能引起数据改变的mysql语句；并且记录语句的发生时间、执行时长、操作数据。。。。一般情况下大小体积上限为1G

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

主：如果log-bin日志不开启的话那么将无法做mysql主从复制

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

二进制的开启：

当前是关闭状态

 

可以通过直配置文件开启：

 

之后重启mysql服务

再次查看mysql服务已经启动：

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

“max_binlog_size”设置binlog的最大存储上限，一般设置为512M或1G,一般不能超过1G当日志达到该上限时，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。

 

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

 

binlog_cache_size =32768   #默认值32768 binlog_cache_size：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。

binlog_cache_size ：一个事务，在没有提交（uncommitted）的时候，产生的日志，记录到Cache中；等到事务提交（committed）需要提交的时候，则把日志持久化到磁盘。 

接着，binlog_cache_size设置多大呢？答案是：根据公司生产的实际情况而定 
设置太大的话，会比较消耗内存资源（Cache本质就是内存），更加需要注意的是：binlog_cache不是全局的，是按SESSION为单位独享分配的，也就是说当一个线程开始一个事务的时候，Mysql就会为这个SESSION分配一个binlog_cache （备注：我想之所以以SESSION为单位分配binlog_cache是有道理的，因为不同的mysql请求，产生的binlog数量不一样的，批量插入数据必然产生大量的binlog，而简单注册一个用户，产生的binlog有限，那么JDBC连接上能否设置binlog_cache_size呢？）。 
设置太小的话，如果用户提交一个“长事务（long_transaction）”，比如：批量导入数据。那么该事务必然会产生很多binlog，这样cache可能不够用（默认binlog_cache_size是32K），不够用的时候mysql会把uncommitted的部分写入临时文件（临时文件cache的效率必然没有内存cache高），等到committed的时候才会写入正式的持久化日志文件。 

 

概念解释：

事务表支持将批处理当做一个完整的任务统一提交或回滚，即对包含在事务中的多条语句要么全执行，要么全部不执行
非事务表则不支持此种操作，批处理中的语句如果遇到错误，在错误前的语句执行成功，之后的则不执行。

 

log-bin = mysql-bin#指定binlog的位置，默认在数据目录下。

 

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

概念解释：mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

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模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。即交替使用行和语句、由mysql服务器自行判断。

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

注：在生产环境下多使用MBR模式，虽然I/O使用增大，但对数据安全性比较高

 

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

 

通过编辑my.cnf中的log-bin选项可以开启二进制日志；形式如下：

log-bin = /usr路径

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

 

 

 

查看二进制日志：

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

 

 

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

 

 

 

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

 

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

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

#查看所有的二进制信息

mysql> show binlog events\G;

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

#从指定的事件位置开始

mysql> show binlog events in ‘log.000002‘ from 1215\G;

 

 

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

删除二进制日志信息：

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

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

语法形式：

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

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

 

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

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

 

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

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

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

一般情况下，mysql会默认提供多种存储引擎,你可以通过下面的查看:
查看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%‘;
你要看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎):
mysql> show create table 表名;

注：

create table 库名.表名 engine = innodb;
这样就可以将表的引擎变更为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但这种方式最安全