5. MySQL 服务管理

5. MySQL 服务管理... 1

5.1 The Mysql Server1

5.2 Mysql 服务日志... 1

5.2.1 选择General query log和slow query log 的输出方式... 1

5.2.2 Error Log. 1

5.2.3 General Query Log. 1

5.2.4 Binary Log. 1

5.2.4.1 binary log日志记录方式... 1

5.2.4.2设置binary log格式... 1

5.2.4.3混合binary log模式... 1

5.2.4.4 mysql数据库的log format1

5.2.5 Slow Query Log. 1

5.2.6 服务日志维护... 1

5.3 一个设备运行多个实例... 1

5.4 使用DTrace跟踪mysqld.. 1

5.4.1 mysqld DTrace Probe 说明... 1

5.1 The Mysql Server

略

5.2 Mysql 服务日志

Mysql有5类日志：

error log:mysql执行时的一些信息（和mssql的错误日志类似）

General query log：从客户端上过来的语句和连接

Binary log：修改数据库的日志（类似mssql的事务日志）

Relay log：从复制的master服务器传过来的日志

Slow query log：查询超过long_query_time的查询

默认是不启动什么日志的。并且默认当日志启动的时候，都是放在数据文件夹下。日志的刷新使用命令FLUSH LOGS当然还有别的比如通过mysqladmin，mysqldump等。当binary log超过max_bing_size的时候，也会自动刷新日志。刷新日志是关闭现在的日志并重新打开。

可以在运行时设置general query log 和slow query log，可以在服务启动的状态下，启动关闭，配置日志路径。

5.2.1 选择General query log和slow query log 的输出方式

general query log 和 slow query log的输出方式为文件或者表(slow_log,general_log)，写入表比写入文件负荷要高，可以使用—log-output命令行参数或者log-output变量。

log-output取值有3个：.FILE，存到文件中，.TABLE，存到表中，.NONE，不存，默认为FILE。

general_log:设置是否启动general log和slow_query_log 类似

general_log_file指定输出文件和slow_query_log_file类似

任意日志启动后，都会在日志文件上写入服务启动信息，但是后续的数据是否写入到日志文件中由log-output决定。

另外sql_log_off控制所在session生成的sql是否写入到general log。

日志数据写入到表的好处：

1.一个标准的格式（格式固定）

2.可以通过sql 访问日志数据

3.可以远程访问

日志表实现有一下几个特点：

1.日志表是为了看如何运行，而不是为了干涉运行

2.create table，alter table，drop table 可以应用在日志表上，alter table和drop table 会堵塞日志表。

3.默认日志表示CSV的，如果要换成myisam要先停止日志。

4.停止日志之后就可以alter table 或者drop table

5.可以truncate table 日志表

6.可以rename table 日志表

7.可以check table 日志表

8.lock table不能使用

9.insert，delete,update不能操作

10.flush tables with read lock或者read_only变量无效。

11.日志表上的更改不会写入binary log，不能用于复制

12.刷新日志表或日志文件分别使用 FLUSH TABLES或者FLUSH LOGS

13.不能使用表分区

14.在5.6.6之前不能mysqldump导出日志表，As of 5.6.6, the dump includes statements to recreate those tables so that they are not missing after reloading the dump file. Log table contents are not dumped.(测试之后 5.6.19可以用mysqldump，需要加--skip-lock-tables，10.0.10-MariaDB-log也一样。)

5.2.2 Error Log

error log中包含了从mysqld从启动到结束所有主要错误。一些平台下面，当mysqld挂了的时候，会把mysqld的堆栈dump到error log中。error log的位置由—log-error指定。若没有，那么默认使用host_name.err。可以使用flush logs来刷新error log。mysqld会关闭日志并重新打开。

如果要rename，先mv掉，然后flush logs。

shell> mv host_name.err host_name.err-old

shell> mysqladmin flush-logs

shell> mv host_name.err-old backup-directory

如果是使用mysqld_safe来启动的mysqld。异常退出，会写入通知到error log需要重启mysqld。

log-warnings等于1启动写入warning到error log，如果大于1，会把非法种植的连接和拒绝的访问写入到log-warnings。

5.2.3 General Query Log

general query log记录了mysqld需要做些什么。当想要知道客户端发送什么到mysqld 的时候这个日志非常有用。mysqld根据收到语句的顺序写入到日志中。和bin log不同，bin log是在语句执行完成，但是锁释放前（提交前）写入bin log的。

默认general log是关闭的，可以设置general-log变量或者—general-log命令行配置。

使用—general_log_file指定日志路径，使用—log-output指定日志输出方式。

如果的日志名为host_name.log。设计到general log的日志都有对应的变量可以设置。

服务重启或者flush logs并不会引起创建一个新的general log，如果要创建一个新的，应该先mv，然后flush logs。

shell> mv host_name.log host_name-old.log

shell> mysqladmin flush-logs

shell> mv host_name-old.log backup-directory

变量sql_log_off可以设置session级别的是否写入general log。

5.2.4 Binary Log

Binary Log其实是数据修改的“事件”的日志。binary log主要有2个目的：

1.用于复制，master上的binary log发送给slave服务。

2.数据恢复操作需要用到binary log。

binary log不记录select或者show语句。

开启binary log会让性能稍微降低。

启动binary log可以设置命令行配置—log-bin[=base_name]默认名称为，pid-file选项的名称。pid-file选项默认为设备的host，然后加上-bin。如果在指定log-bin的时候加了后缀，那么会被自动忽略。

每次flush logs的时候，会重新创建一个新的日志文件。binlog文件超过了max_binlog_size的时候，也会重新创建一个新的日志文件。当出现大事务的时候，因为一个事务要放在一个binlog上，所以也可能出现大于max_binlog_size。

所有的binary log被记录在binary log index文件里面，这个文件可以由—log-bin-index修改。默认为bin_log.index。最好不要手动去修改，会让mysqld产生混乱。

如果客户端有SUPER权限，可以通过set sql_log_bin=0来禁止该session的语句写入到binary log。

binary log默认在写入日志的时候，服务会写入“事件”长度。然后在读取binary log的时候会根据长度来验证binary log。

可以使用binlog-checksum变量来指定bin log 写入checksum，在读的时候使用master_verify_checksum来指定使用使用checksum验证。slave i/o线程也可以使用chunksum来验证relay log过来的日志，slave_sql_verify_checksum。

“事件”被记录在binary log的时候有2中模式：1.行模式，2.语句模式。

--binlog-do-db和--binlog-ignore-db来忽略对某些db的binary log和—replicate-do-db，--replicate-ignore-db类似。

slave服务一般不把数据修改写入到binary log中，只有启动了—log-slave-update和—log-bin之后才会写入，一般用来做复制链

可以使用RESET MASTER或者PURGE BINARY LOGS来清空binary log。如果有复制那么句不应该在没有恢复完之前删除binary log。

binary log 的内容可以通过mysqlbinlog工具查看里面的内容。

binary log的写入是语句执行完之后，在释放锁之前（提交前）写入。没事务性表，会在执行完之后马上插入到binary log。

若是未提交事务，所有的写入事务表都会被缓存，直到接收commit，这个时候写入到binary log，然后再执行commit。

非事务表是不能回滚的，所以当一个事务如果包含了非事务性表的操作，回滚的时候，会在binary log上 ROLLBACK，这样非事务性表的修改就能够被复制。

binlog_cache_szie用来缓存binlog，如果操作会使用临时文件保存。

binlog_cache_use状态变量，显示了缓存的事务个数。binlog_cache_disk_use状态变量显示了缓存的事务存在临时文件的个数。可以使用上面2个状态变量来调整binlog_cache_size。

max_binlog_cache_size系统变量(默认4gb)可以用来限制所有缓存事务的大小。如果一个事务超过了这个值，就会报错回滚。最小值为4096。

默认binary log不是同步写入到磁盘的，如果系统或者设备crash，可能会照成binary log数据丢失。可以通过sync_binlog来控制经过N个写入之后，然后写入到磁盘。sync_binlog为1的时候最安全，但是也是最慢的。而且不能保证不丢失数据，当在发出commit命令后，写入binary log然后提交事务，如果出现在 写入binary log 和提交事务之间。那么事务会被回滚，但是还是会存在在binary log内。可以使用—innodb_support_xa来保证事务一致性。但是发布于支持XA事务的innodb。

该选项提供了更高的安全性，mysql服务也要同步的写入binlog，并且在innodb上提交事务前要写入日志到磁盘。innodb日志默认是同步的，sync_binlog=1也可以用来同步binary log。该选项主要是在crash restart的时候，如果回滚会剪切binary log中的innodb事务，这样就保证了slave和master的一致性。

如果mysql服务crash restart，发现binary log比预期的要短，就会出错：

<strong>The binary log</strong> <strong><em>file_name</em></strong> <strong>is shorter than its expected size</strong>.

以下session value会被写入到binary log，被应用在slave解析binary log阶段：

·  <strong>sql_mode</strong> (except that the <strong>NO_DIR_IN_CREATE</strong> mode is not replicated; see Section 17.4.1.34, “Replication and Variables”)

·  <strong>foreign_key_checks</strong>

·  <strong>unique_checks</strong>

·  <strong>character_set_client</strong>

·  <strong>collation_connection</strong>

·  <strong>collation_database</strong>

·  <strong>collation_server</strong>

·  <strong>sql_auto_is_null</strong>

5.2.4.1 binary log日志记录方式

binary log格式体现在变量binlog_format中有3方式：

1.语句模式（--binlog-format=STATEMENT），记录发送的语句。（默认）

2.行模式(--binlog-format=ROW)，记录发生修改的行。

3.混合模式(--binlog-format=MIXED)，即混合了语句模式和行模式

如果复制是基于语句模式的binary log会发送非确定性的语句，导致master和slave出现不一致性。mysql会保证语句是否可以复制到slave的一致性，如果保证不了会有一个警告：

 Statement may not be safe to log in statement format.

5.2.4.2设置binary log格式

可以使用命令行参数—binlog-format=type设置，type有3个值：STATEMENT(默认)，ROW，MIXED。适用范围是：global，session。

修改binlog_format的值必须要有 <strong>SUPER</strong> 权限。

当发生服务是运行在STATEMENT或者MIXED模式下，但是写入日志使用了ROW的日志方式。这个事件在slave中会被临时切换到ROW模式。

session变量设置原因：

1.session，修改只影响了很少的行，可以使用ROW

2.session，修改影响了很多行，使用STATEMENT

3.session，在master上执行时间很差，但是只修了一些行，可以使用ROW

一下情况下不能切换复制模式(切换会报错)：

1.在存储过程或者触发器内

2.使用了NDBCLUSTER存储引擎

3.session在ROW复制模式(replication format，我认为是和binary log模式一样的东西)下，并且已经打开了临时表

当存在临时表的时候建议不要修改复制方式，因为临时表只有在语句模式下才会被记录。

在binary log模式为ROW情况下很多修改都是以行方式记录，但是DDL都是以语句方式记录。

--binlog-row-event-max-size选项，应用在有基于行的复制下，行保存的大小不能超过这个选项的大小。这个值必须是256的倍数，默认是1024。

5.2.4.3混合binary log模式

如果使用混合模式，在以下条件下，会把 statement-base切换为row-base：

Ÿ   当一个包含函数UUID()

Ÿ   当一个或者多个表有auto-increment列被更新，并且触发器或者存储方法被调用。

Ÿ   调用了视图，视图体的语句需要row-base。

Ÿ   调用了UDF，用户自定义函数。

Ÿ   非事务表上执行了INSERT DELAYED。

Ÿ   如果会话使用了临时表，那么之后都会以row-base保存。

Ÿ   当FOUND_ROWS()或者ROW_COUNT()函数被使用的时候。

Ÿ   当USER(),CURRENT_USER(),CURRENT_USER被使用的时候。

Ÿ   当语句使用了系统变量的时候。

一下系统变量，在session级别使用，不会影响日志模式的切换：

Ÿ   auto_increment_increment

Ÿ   auto_increment_offset

Ÿ   <strong>character_set_client</strong>

Ÿ   character_set_connection

Ÿ   character_set_database

Ÿ   character_set_server

Ÿ   <strong>collation_connection</strong>

Ÿ   <strong>collation_database</strong>

Ÿ   <strong>collation_server</strong>

Ÿ   <strong>foreign_key_checks</strong>

Ÿ   identity

Ÿ   last_insert_id

Ÿ   lc_time_names

Ÿ   pseudo_thread_id

Ÿ   <strong>sql_auto_is_null</strong>

Ÿ   time_zone

Ÿ   timestamp

Ÿ   <strong>unique_checks</strong>

存储引擎，binary log模式支持情况。

一个语句会被以什么模式记录，取决于：1.语句本身，2.log_format，3.存储引擎。

1.binlog-format=mixed，语句本身unsafe，就要求ROW方式写入，如果语句本row injection要求ROW方式写入。

2.当语句，binlog-format，引擎支持，任意之间出现冲突这无法写入error。

具体表格查看：

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

5.2.4.4 mysql数据库的log format

mysql上面的数据可能会通过DML语句直接修改，也可能通过DDL语句修改。

DML：DML语句对mysql数据库的修改，会依据binlog_format来。

DDL：DDL语句对mysql数据库修改，会以语句方式，不会理睬binlog_format。

5.2.5 Slow Query Log

slow log包括，执行时间超过 long_query_time并且至少请求min_examined_row_limit条记录。

执行时间并不包含初始化表锁的时间，mysqld会在执行完并且释放锁之后写入到日志。所以日志上的顺序和执行顺序不同。

默认slow query log 是不启动的，可以通过—slow_query_log设置，通过—slow_query_log_file来设置日志文件所在位置。--log_output来设置输出方式。如果没有指定shlow_query_log_file默认为host_name-slow.log。

若日志启动会写入到log。若启动设置了文件方向，才会把后续的内容写到日志文件中，否则写入到表中。

如果设置了—log-short-format会尽量减少写入的日志信息。若要把管理性的语句写入到日志文件，需要启动—log-slow-admin-statements。

log_queries_not_using_indexes控制把没有使用索引的查询写入到日志。

log_throttle_queries_not_using_indexes是为了减少slow query log增长太快，每60s为一个区间，在区间内发送了log_throttle_queries_not_using_indexes个没有使用索引的语句写入到日志文件。

1.如果是非管理性语句或者log-slow-admin-statements=1

2.long_query_time或者log_queries_not_using_indexes符合

3.至少要达到请求min_examined_row_limit行

4.不在log_throttle_queries_not_using_indexes限制返回内的sql

slave不写slow query log，除非启动了—log-slow-slave-statement。

注：

log_throttle_queries_not_using_indexes，只会对没有使用索引的sql处理。

5.2.6 服务日志维护

对于binlog可以设置expire_logs_days自动过期binary log。如果有复制存在，设置的值不应该小余slave的延迟。也可以通过purge binary logs语句来手动清理日志。

通过flush logs手动让binary log启动一个新的日志文件。

日志刷新做一下动作：

1.如果启动了general log 或者show query log，服务会关闭当前文件并且重新打开。

2.如果bianry log启动了，服务会关闭binary log 文件并且打开一个新的binary log 文件。

3.如果启动了error log，那么会关闭文件并且重新打开。

若要新建文件可以先使用mv命令重命名，然后flush logs。

5.3 一个设备运行多个实例

略

5.4 使用DTrace跟踪mysqld

DTrace在mysql中被用来提供mysql执行查询的信息，在执行过程中不同区域系统的使用。

DTrace可以跟着从连接到查询执行然后返回的全部过程。

5.4.1 mysqld DTrace Probe 说明

mysql DTrace 提供了一下事件(probe):