mysql日志，线程，慢查询，锁表;

 

http://pangge.blog.51cto.com/6013757/1319304

MySQL日志：

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

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

错误日志

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

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

下面我们来定义mysql错误日志的功能：

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

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

mysql> SHOW  GLOBAL VARIABLES LIKE '%log%'; +-----------------------------------------+-----------------------------------+ |  Variable_name                            | Value                              | +-----------------------------------------+-----------------------------------+ | back_log                                | 50                                | |  binlog_cache_size                        | 32768                              | |  binlog_direct_non_transactional_updates | OFF                               | |  binlog_format                           | MIXED                             | |  binlog_stmt_cache_size                   | 32768                              | |  expire_logs_days                         | 0                                  | | general_log                             | OFF                               | |  general_log_file                         | /mydata/data/stu18.log             | |  innodb_flush_log_at_trx_commit           | 1                                  | |  innodb_locks_unsafe_for_binlog           | OFF                               | |  innodb_log_buffer_size                   | 8388608                            | |  innodb_log_file_size                     | 5242880                            | |  innodb_log_files_in_group                | 2                                  | |  innodb_log_group_home_dir                | ./                                 | |  innodb_mirrored_log_groups               | 1                                  | | log                                     |  OFF                               | | log_bin                                 | ON                                | |  log_bin_trust_function_creators          | OFF                                | | log_error                               |  /mydata/data/stu18.magedu.com.err |定义错误日志 | log_output                              | FILE                              | |  log_queries_not_using_indexes            | OFF                                | |  log_slave_updates                        | OFF                                | |  log_slow_queries                        | OFF                               | |  log_warnings                             | 1               是否把警告信息写入错误日志中 |  max_binlog_cache_size                    | 18446744073709547520               | |  max_binlog_size                          | 1073741824                         | |  max_binlog_stmt_cache_size               | 18446744073709547520               | |  max_relay_log_size                       | 0                                  | | relay_log                               |                                   | |  relay_log_index                          |                                    | |  relay_log_info_file                      | relay-log.info                     | |  relay_log_purge                          | ON                                 | |  relay_log_recovery                       | OFF                                | |  relay_log_space_limit                    | 0                                  | |  slow_query_log                           | OFF                                | | slow_query_log_file                     |  /mydata/data/stu18-slow.log       | | sql_log_bin                             | ON                                | | sql_log_off                             | OFF                               | | sync_binlog                             | 0                                 | |  sync_relay_log                           | 0                                  | |  sync_relay_log_info                      | 0                                  | +-----------------------------------------+-----------------------------------+

其中，log_error可以直接定义为文件路径，也可以为ON|OFF；log_warings只能使用1|0来定义开关启动。

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

 

1 2 3

[root@stu18 data]#  vim /etc/my.cnf [mysqld] Log_error=DIR/[filename]

解析：其中，DIR参数指定错误日志的路径filename参数是错误日志的名称，没有指定该参数时默认为主机名。重启mysql服务器即可生效。

查看mysql错误日志：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

[root@stu18 data]#  tail -20 stu18.magedu.com.err 130813  15:30:50  InnoDB: Starting shutdown... 130813  15:30:51  InnoDB: Shutdown completed;  log sequence number 1630920 130813 15:30:51  [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 130813 15:30:52  mysqld_safe mysqld from pid file /mydata/data/stu18.magedu.com.pid ended 130813 15:30:53  mysqld_safe Starting mysqld daemon with databases from /mydata/data 130813 15:30:54  InnoDB: The InnoDB memory heap is disabled     #禁用了InnoDB memory的堆功能。 130813 15:30:54  InnoDB: Mutexes and rw_locks use GCC atomic builtins #Mutexes（互斥量）和rw_locks（行级锁）是GCC编译的是InnoDB内置的。 130813 15:30:54  InnoDB: Compressed tables use zlib 1.2.3     #默认压缩工具是zlib 130813 15:30:55  InnoDB: Initializing buffer pool, size = 128.0M    #InnoDB引擎的缓冲池（buffer pool）的值大小 130813 15:30:55  InnoDB: Completed initialization of buffer pool 130813 15:30:55  InnoDB: highest supported file format is Barracuda. 130813  15:30:57  InnoDB: Waiting for the  background threads to start 130813 15:30:58  InnoDB: 5.5.33 started; log sequence number 1630920 130813 15:30:58  [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 130813 15:30:58  [Note]   - '0.0.0.0' resolves to  '0.0.0.0';  #0.0.0.0会反解主机名，这里反解失败 130813 15:30:58  [Note] Server socket created on IP: '0.0.0.0'. 130813 15:30:58  [Note] Event Scheduler: Loaded 0 events    #事件调度器没有任何事件，因为没有装载。 130813 15:30:58  [Note] /usr/local/mysql/bin/mysqld: ready for connections. #mysql启动完成等待客户端的请求。 Version:  '5.5.33-log'  socket:  '/tmp/mysql.sock'  port: 3306  Source distribution  #创建一个本地sock用于本地连接。

删除错误日志：

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

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

 

1 2 3 4

[root@stu18 data]# mv stu18.magedu.com.err  stu18.magedu.com.err.old  [root@stu18 data]#  mysqladmin flush-logs [root@stu18 data]# ls hellodb  myclass  mysql-bin.000003  mysql-bin.index           stu18.magedu.com.pid     ibdata1      mysql     mysql-bin.000004  performance_schema    ib_logfile0  mysql-bin.000001  stu18.magedu.com.err           test   ib_logfile1  mysql-bin.000002  stu18.magedu.com.err.old

更多信息请查阅官方文档：http://dev.mysql.com/doc/refman/5.5/en/error-log.html

查询日志：

默认情况下查询日志是关闭的。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

查看查询日志是否开启：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

mysql> SHOW  GLOBAL VARIABLES LIKE '%log%'; +-----------------------------------------+-----------------------------------+ |  Variable_name                            | Value                              | +-----------------------------------------+-----------------------------------+ | back_log                                | 50                                | |  binlog_cache_size                        | 32768                              | |  binlog_direct_non_transactional_updates | OFF                               | |  binlog_format                           | MIXED                             | |  binlog_stmt_cache_size                   | 32768                              | |  expire_logs_days                         | 0                                  | | general_log                             | OFF        #定义查询日志是否开启  |                   |  general_log_file                         | /mydata/data/stu18.log   #定义查询日志的文件地址名称                          | |  innodb_flush_log_at_trx_commit           | 1                                  | |  innodb_locks_unsafe_for_binlog           | OFF                                | |  innodb_log_buffer_size                   | 8388608                            | |  innodb_log_file_size                     | 5242880                            | |  innodb_log_files_in_group               | 2                                 | |  innodb_log_group_home_dir                | ./                                 | |  innodb_mirrored_log_groups               | 1                                  | | log                                     |  OFF          #是否开启日志  （若开启则表示开启所有的日志功能）                 | | log_bin                                 | ON                                | |  log_bin_trust_function_creators          | OFF                                | | log_error                               | /mydata/data/stu18.magedu.com.err  | | log_output                              | FILE   #日志的输出的位置          |       |  log_queries_not_using_indexes            | OFF                                | |  log_slave_updates                        | OFF                               | |  log_slow_queries                         | OFF                                | |  log_warnings                             | 1                                  | |  max_binlog_cache_size                    | 18446744073709547520               | |  max_binlog_size                          | 1073741824                         | |  max_binlog_stmt_cache_size               | 18446744073709547520               | |  max_relay_log_size                       | 0                                  | | relay_log                               |                                   | |  relay_log_index                          |                                    | |  relay_log_info_file                      | relay-log.info                     | |  relay_log_purge                         | ON                                | |  relay_log_recovery                       | OFF                                | |  relay_log_space_limit                    | 0                                  | |  slow_query_log                           | OFF                                | |  slow_query_log_file                      | /mydata/data/stu18-slow.log        | | sql_log_bin                             | ON                                | | sql_log_off                             | OFF                               | | sync_binlog                             | 0                                 | |  sync_relay_log                           | 0                                  | |  sync_relay_log_info                      | 0                                  | +-----------------------------------------+-----------------------------------+ 41 rows in set  (0.00 sec)

拓展解析：日志的输出位置一般有三种方式：file(文件)，table(表)，none(不保存)；其中前两个输出位置可以同时定义，none表示是开启日志功能但是记录日志信息。file就是通过general_log_file |/mydata/data/stu18.log 等方式定义的，而输出位置定义为表时查看日志的内容方式为：

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

mysql> use  mysql;                    #在此数据库中 Database changed mysql> show  tables; +---------------------------+ |  Tables_in_mysql           | +---------------------------+ |  columns_priv              | | db                        | | event                     | | func                      | | general_log               |      #这个就是查询日志的表输出位置 |  help_category             | |  help_keyword              | |  help_relation             | | help_topic ………………                | +---------------------------+

慢查询日志：

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

查看慢查询日志的定义：

 

1 2 3

mysql> SHOW  GLOBAL VARIABLES LIKE '%log%'; |  slow_query_log         | OFF    #定义慢查询日志的 |  slow_query_log_file    |/mydata/data/stu18-slow.log    #输出方式为file（文件）时定义慢查询日志的位置

启动和设置慢查询日志：

1、通过配置文件my.cnf中的log-slow-queries选项可以开启慢查询日志；形式如下：

 

1 2 3 4

[root@stu18 data]#  vim /etc/my.cnf [mysqld] slow_query_log=1 log-slow-queries [=  DIR/[filename] ]

其中，DIR参数指定慢查询日志的存储路径；filename参数指定日志的文件名，生成日志文件的完成名称为filename-slow.log。如果不指定存储路径，慢查询日志默认存储到mysql数据库的数据文件下，如果不指定文件名，默认文件名为hostname-slow.log。

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

首先要有全局权限；然后执行mysql>set global slow_query_log=1;

时间默认超过多少的称为慢查询日志？

一般都是通过long_query_time选项来设置这个时间值，时间以秒为单位，可以精确到微秒。如果查询时间超过了这个时间值（默认为10秒），这个查询语句将被记录到慢查询日志中。查看服务器默认时间值方式如下：

 

1 2 3 4 5 6 7

mysql> SHOW  GLOBAL VARIABLES LIKE 'long%'; +-----------------+-----------+ |  Variable_name   | Value     | +-----------------+-----------+ | long_query_time  | 10.000000 | +-----------------+-----------+ 1 row in set (0.04  sec)

注释：其中这个慢查询时间并不是只表示语句自身执行超过10秒还包含由于其他资源被征用造成阻塞的查询执行时间或其他原因等都被记录到慢查询中。所以这个慢查的时长表示从查询开始到查询结束中间包含可能的任何原因所经历的所有时间。

测试是否可以记录日志：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> set globalslow_query_log=1;       #开启慢查询日志 Query OK, 0 rowsaffected (0.35 sec) mysql> setsession long_query_time=0.001;     #更改时间（当前session中，退出则重置） Query OK, 0 rowsaffected (0.00 sec) mysql> set globallong_query_time=0.001;      #更改时间（全局中，重启服务则重置） mysql> SHOWVARIABLES LIKE 'long%';          #查询定义时间 +-----------------+----------+ | Variable_name   | Value   | +-----------------+----------+ | long_query_time |0.001000 | +-----------------+----------+ 1 row in set (0.00sec) mysql> showglobal variables like "%slow%";  #查看慢查询日志开启状态 +---------------------+-----------------------------+ | Variable_name       | Value                       | +---------------------+-----------------------------+ |log_slow_queries    | ON                          | |slow_launch_time    | 2                           | |slow_query_log      | ON                          | |slow_query_log_file | /mydata/data/stu18-slow.log | +---------------------+-----------------------------+ 4 rows in set (0.03sec)

查看慢查询日志：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> use mysql mysql> selectuser,host,password from user where user="root"; +------+------------------+----------+ | user | host             | password | +------+------------------+----------+ | root |localhost        |          | | root |stu18.magedu.com |          | | root |127.0.0.1        |          | | root | ::1              |          | +------+------------------+----------+ 4 rows in set (0.08sec)       #查询时间为0.08 mysql> systemmore /mydata/data/stu18_slow.log          #查询慢查询日志记录信息 /usr/local/mysql/bin/mysqld,Version: 5.5.33-log (Source distribution). started with: Tcp port: 3306  Unix socket: /tmp/mysql.sock Time                 Id Command    Argument >>>>>>>>>>>>>>>>部分已省略>>>>>>>>>>>>>> # Time: 13100723:46:33 # User@Host:root[root] @ localhost [] # Query_time:0.108459  Lock_time: 0.000216 Rows_sent:4  Rows_examined: 6 SETtimestamp=1381160793; selectuser,host,password from user where user="root";

事务日志：

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

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

查看事务日志的定义：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

mysql> SHOWGLOBAL VARIABLES LIKE '%log%'; +-----------------------------------------+-----------------------------------+ | Variable_name                           | Value                             | +-----------------------------------------+-----------------------------------+ | innodb_flush_log_at_trx_commit          | 1        #在事务提交时innodb是否同步日志从缓冲到文件中1表示事务以提交就同步不提交每隔一秒同步一次，性能会很差造成大量的磁盘I/O；定义为2表示只有在事务提交时才会同步但是可能会丢失整个事务      | |innodb_locks_unsafe_for_binlog          |OFF                               | | innodb_log_buffer_size                  | 8388608                           | |innodb_log_file_size                    |5242880                           | | innodb_log_files_in_group               | 2      #至少有两个                | |innodb_log_group_home_dir               |./      #定义innodb事务日志组的位置       | |innodb_mirrored_log_groups              |1    #表示对日志组做镜像          | 每个事务日志都是大小为5兆的文件： [root@stu18 data]#ls -lh -rw-rw---- 1 mysqlmysql 5.0M Oct  7 23:36 ib_logfile0 -rw-rw---- 1 mysqlmysql 5.0M Aug 12 01:06 ib_logfile1

二进制日志：

二进制日志也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等。所以说通过二进制日志可以查询mysql数据库中进行了哪些变化。一般大小体积上限为1G。

二进制开启状态：

1 2 3 4 5 6 7 8

mysql> showglobal variables like "%log_bin%"; +---------------------------------+-------+ | Variable_name                   | Value | +---------------------------------+-------+ | log_bin                         | ON    |  #已开启 |log_bin_trust_function_creators | OFF   | | sql_log_bin                     | ON    | +---------------------------------+-------+

二进制日志相关的参数：

1 2 3 4 5 6 7 8 9 10

mysql> showglobal variables like "%log%"; sql_log_bin ={ON|OFF}     #用于控制会话级别二进制日志功能的开启或关闭。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。 binlog_cache_size =32768   #默认值32768 Binlog Cache用于在打开了二进制日志（binlog）记录功能的环境，是MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了。 binlog_stmt_cache_size= 32768       #当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句。 log_bin = mysql-bin#指定binlog的位置，默认在数据目录下。 binlog-format= {ROW|STATEMENT|MIXED}     #指定二进制日志的类型，默认为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。 sync_binlog = 10#设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步 max_binlog_cache_size= {4096 .. 18446744073709547520}      #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。 max_binlog_stmt_cache_size= {4096 .. 18446744073709547520}    #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存 expire_log_days ={0..99}    #设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。

二进制日志定义方式：

其一、log_bin可以直接定义为文件路径，也可以为ON|OFF。

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

1 2 3

[root@stu18 ~]#my.cnf [mysqld] log-bin [=DIR \ [filename]]

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

[root@stu18 ~]# cd /mydata/data/ [root@stu18 data]#ls -lh -rw-rw---- 1 mysqlmysql  14K Aug 13 15:30 mysql-bin.000001 -rw-rw---- 1 mysqlmysql  150 Aug 13 17:05 mysql-bin.000002 -rw-rw---- 1 mysqlmysql  150 Aug 13 17:06 mysql-bin.000003 -rw-rw---- 1 mysqlmysql  150 Aug 13 17:07 mysql-bin.000004 -rw-rw---- 1 mysqlmysql  150 Aug 13 17:39 mysql-bin.000005 -rw-rw---- 1 mysqlmysql  126 Aug 13 19:03 mysql-bin.000006 -rw-rw---- 1 mysqlmysql  126 Aug 13 19:03 mysql-bin.000007 -rw-rw---- 1 mysqlmysql  126 Aug 13 19:05 mysql-bin.000008 -rw-rw---- 1 mysqlmysql  107 Aug 13 19:05 mysql-bin.000009 -rw-rw---- 1 mysqlmysql  353 Oct  7 23:40 mysql-bin.000010 -rw-rw---- 1 mysqlmysql  190 Oct  7 20:43 mysql-bin.index [root@stu18 data]#cat mysql-bin.index ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006 ./mysql-bin.000007 ./mysql-bin.000008 ./mysql-bin.000009 ./mysql-bin.000010

如果说我们向某个表的某个字段插入一个数据而这个数据为当前时间(日期时间型)；过段时间将此二进制文件应用到另一台服务器上数据就会变动从而导致数据的不一致性所以说对于这种非确定性的数据使用默认的语句定义并不是可靠的；

二进制日志中常用的定义格式：

1、语句(statement)：默认的记录格式；

2、行(row)：定义的并非数据本身而是这一行的数据是什么；

3、混合模式(mixed)：交替使用行和语句、由mysql服务器自行判断。

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

查看二进制日志：

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

mysql> showbinary logs;     #显示当前服务器使用的二进制文件及大小 +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001|     13814 | | mysql-bin.000002|       150 | | mysql-bin.000003|       150 | | mysql-bin.000004|       150 | | mysql-bin.000005|       150 | | mysql-bin.000006|       126 | | mysql-bin.000007|       126 | | mysql-bin.000008|       126 | | mysql-bin.000009|       107 | | mysql-bin.000010|       353 | +------------------+-----------+ 10 rows in set (0.07sec) mysql> showmaster logs;      #显示主服务器使用的二进制文件及大小 +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001|     13814 | | mysql-bin.000002|       150 | | mysql-bin.000003|       150 | | mysql-bin.000004|       150 | | mysql-bin.000005|       150 | | mysql-bin.000006|       126 | | mysql-bin.000007|       126 | | mysql-bin.000008|       126 | | mysql-bin.000009|       107 | | mysql-bin.000010|       353 | +------------------+-----------+ 10 rows in set (0.02sec) mysql> showmaster status;   #当前使用的二进制文件及所处位置 +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000010|      353 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00sec)

小扩展：二进制日志的记录位置：通常为上一个事件执行结束时间的位置，每一个日志文件本身也有自己的元数据所以说对于当前版本的mysql来说二进制的开始位置通常为107；

1 2 3 4 5 6 7 8 9 10

mysql> flushlogs; Query OK, 0 rowsaffected (0.23 sec) 注意：flush logs一般只会滚动中继日志和二进制日志。 mysql> showmaster status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000011|      107 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00sec)

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

1 2 3 4 5 6 7 8 9 10 11 12 13

mysql> createdatabase yong; Query OK, 1 rowaffected (0.12 sec) mysql> createtable yong.tb1 (id int,name char(20)); Query OK, 0 rowsaffected (0.44 sec) mysql> insertinto yong.tb1 values(1,'tom'); Query OK, 1 rowaffected (0.14 sec) mysql> showmaster status; +------------------+----------+--------------+------------------+ | File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000011|      479 |              |                  | +------------------+----------+--------------+------------------+ 1 row in set (0.00sec)

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

SHOW BINLOG EVENTS[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] mysql> showbinlog events\G         #查看所有的二进制信息 ***************************87. row ***************************    Log_name: mysql-bin.000001         Pos: 13580  Event_type: Query   Server_id: 1 End_log_pos: 13688        Info: use `hellodb`; /*!40000 ALTERTABLE `toc` DISABLE KEYS */ ***************************88. row ***************************    Log_name: mysql-bin.000001         Pos: 13688  Event_type: Query   Server_id: 1 End_log_pos: 13795        Info: use `hellodb`; /*!40000 ALTERTABLE `toc` ENABLE KEYS */ ***************************89. row ***************************    Log_name: mysql-bin.000001         Pos: 13795  Event_type: Stop   Server_id: 1 End_log_pos: 13814        Info: 89 rows in set (0.00sec) mysql> showbinlog events in 'mysql-bin.000011';    #查看指定日志的二进制信息 +------------------+-----+-------------+-----------+-------------+----------------------------------------------+ | Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                         | +------------------+-----+-------------+-----------+-------------+----------------------------------------------+ | mysql-bin.000011|   4 | Format_desc |         1 |         107 | Server ver: 5.5.33-log, Binlogver: 4        | | mysql-bin.000011 |107 | Query       |         1 |         190 | create database yong                         | | mysql-bin.000011 |190 | Query       |         1 |         293 | create table yong.tb1 (idint,name char(20)) | | mysql-bin.000011 |293 | Query       |         1 |         357 | BEGIN                                        | | mysql-bin.000011 |357 | Query       |         1 |         452 | insert into yong.tb1values(1,'tom')         | | mysql-bin.000011 |452 | Xid         |         1 |         479 | COMMIT /* xid=103 */                         | +------------------+-----+-------------+-----------+-------------+----------------------------------------------+ 6 rows in set (0.00sec) mysql> showbinlog events in 'mysql-bin.000011' from 190; #从指定的事件位置开始 +------------------+-----+------------+-----------+-------------+----------------------------------------------+ | Log_name         | Pos | Event_type | Server_id |End_log_pos | Info                                         | +------------------+-----+------------+-----------+-------------+----------------------------------------------+ | mysql-bin.000011 |190 | Query      |         1 |         293 | create table yong.tb1 (idint,name char(20)) | | mysql-bin.000011 |293 | Query      |         1 |         357 | BEGIN                                        | | mysql-bin.000011 |357 | Query      |         1 |         452 | insert into yong.tb1values(1,'tom')         | | mysql-bin.000011 |452 | Xid        |         1 |         479 | COMMIT /* xid=103 */                         | +------------------+-----+------------+-----------+-------------+----------------------------------------------+ 4 rows in set (0.00sec) mysql> showbinlog events in 'mysql-bin.000011' from 190 limit 3;  #指定偏移量(不是语句，是事件) +------------------+-----+------------+-----------+-------------+----------------------------------------------+ | Log_name         | Pos | Event_type | Server_id |End_log_pos | Info                                         | +------------------+-----+------------+-----------+-------------+----------------------------------------------+ | mysql-bin.000011 |190 | Query      |         1 |         293 | create table yong.tb1 (idint,name char(20)) | | mysql-bin.000011 |293 | Query      |         1 |         357 | BEGIN                                        | | mysql-bin.000011 |357 | Query      |         1 |         452 | insert into yong.tb1values(1,'tom')         | +------------------+-----+------------+-----------+-------------+----------------------------------------------+ 3 rows in set (0.00sec)

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

[root@stu18 data]#mysqlbinlog mysql-bin.000017        #必须在数据目录下 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!40019 SET@@session.max_insert_delayed_threads=0*/; /*!50003 SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4       #事件开始处 #131009  0:25:59 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.33-log created 131009  0:25:59 # Warning: thisbinlog is either in use or was not closed properly. BINLOG ' FzJUUg8BAAAAZwAAAGsAAAABAAQANS41LjMzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA== '/*!*/; # at 107 #131009  0:26:36 server id 1  end_log_pos 192   Query   thread_id=12    exec_time=0 error_code=0     #131009 0:26:36年月日的简写方式；end_log_pos事件结束处；thread_id=12 哪个会话线程创建的此语句；exec_time=0 执行时长单位为秒；error_code=0 错误代码0表示没有 SET TIMESTAMP=1381249596/*!*/;      #预设信息(环境设定)

导出此数据库的信息：

1	[root@stu18 data]#mysqlbinlog mysql-bin.000017 > /tmp/a.sql

导入此数据库的信息：

1	[root@stu18 data]#mysql < a.sql

删除二进制日志信息：

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

1、删除所有的二进制日志（不可效仿）：

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

1 2 3 4 5 6 7 8 9

mysql> resetmaster;           Query OK, 0 rowsaffected (0.17 sec) mysql> showbinary logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000001|       107 | +------------------+-----------+ 1 row in set (0.04sec)

解析：首先不建议在生产环境下使用此操作；删除所有的二进制日志后，Mysql将会重新创建新的二进制日志。新二进制日志的编号从000001开始。

2、根据文件或时间点来删除二进制日志：

语法形式：

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> PURGEBINARY LOGS TO 'mysql-bin.000007'; Query OK, 0 rowsaffected (0.11 sec) mysql> showbinary logs; +------------------+-----------+ | Log_name         | File_size | +------------------+-----------+ | mysql-bin.000007|       150 | | mysql-bin.000008|       150 | | mysql-bin.000009|       150 | | mysql-bin.000010|       150 | | mysql-bin.000011|       150 | | mysql-bin.000012|       150 | | mysql-bin.000013|       150 | | mysql-bin.000014|       150 | | mysql-bin.000015|       150 | | mysql-bin.000016|       150 | | mysql-bin.000017|       483 | +------------------+-----------+ 11 rows in set (0.04sec) [root@stu18 data]#cat mysql-bin.index ./mysql-bin.000007 ./mysql-bin.000008 ./mysql-bin.000009 ./mysql-bin.000010 ./mysql-bin.000011 ./mysql-bin.000012 ./mysql-bin.000013 ./mysql-bin.000014 ./mysql-bin.000015 ./mysql-bin.000016 ./mysql-bin.000017

由此可以看出这种清理二进制日志文件的方式是非常合理的，不会导致数据库的错误发生。

1 2	mysql> PURGEBINARY LOGS BEFORE '13-10-19 10:26:36'; #使用时间来删除二进制日志 Query OK, 0 rowsaffected (0.05 sec)

 

到此关于二进制的知识就解析完了，其中若有错误不足之处请指出！谢谢！注意二进制日志和错误日志很重要重点学之！！

http://hone033.iteye.com/blog/451100

mysql有以下几种日志： 
   错误日志：     -log-err 
   查询日志：     -log 
   慢查询日志:   -log-slow-queries 
   更新日志:     -log-update 
   二进制日志： -log-bin 


是否启用了日志 
mysql>show variables like 'log_%'; 

怎样知道当前的日志 
mysql> show master status; 

顯示二進制日志數目 
mysql> show master logs; 

看二进制日志文件用mysqlbinlog 
shell>mysqlbinlog mail-bin.000001 
或者shell>mysqlbinlog mail-bin.000001 | tail 

在配置文件中指定log的輸出位置. 
Windows：Windows 的配置文件为 my.ini，一般在 MySQL 的安装目录下或者 c:\Windows 下。 
Linux：Linux 的配置文件为 my.cnf ，一般在 /etc 下。 

在linux下： 
Sql代码 

   1. # 在[mysqld] 中輸入  
   2. #log  
   3. log-error=/usr/local/mysql/log/error.log  
   4. log=/usr/local/mysql/log/mysql.log  
   5. long_query_time=2  
   6. log-slow-queries= /usr/local/mysql/log/slowquery.log  

# 在[mysqld] 中輸入 
#log 
log-error=/usr/local/mysql/log/error.log 
log=/usr/local/mysql/log/mysql.log 
long_query_time=2 
log-slow-queries= /usr/local/mysql/log/slowquery.log 



windows下: 
Sql代码 

   1. # 在[mysqld] 中輸入  
   2. #log  
   3. log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log"  
   4. log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log"  
   5. long_query_time=2  
   6. log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log"  

# 在[mysqld] 中輸入 
#log 
log-error="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/error.log" 
log="E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/mysql.log" 
long_query_time=2 
log-slow-queries= "E:/PROGRA~1/EASYPH~1.0B1/mysql/logs/slowquery.log" 



开启慢查询 
long_query_time =2  --是指执行超过多久的sql会被log下来，这里是2秒 
log-slow-queries= /usr/local/mysql/log/slowquery.log  --将查询返回较慢的语句进行记录 

log-queries-not-using-indexes = nouseindex.log  --就是字面意思，log下来没有使用索引的query 

log=mylog.log  --对所有执行语句进行记录 

http://ryyt1231.blog.163.com/blog/static/20708281201121142754561/

mysqlbinlog 查看mysql bin 日志  

2011-03-11 16:31:55|  分类： 学习笔记 |  标签：mysql  mysqlbinlog  |举报|字号 订阅

 

 

mysqlbinlog  mysql-bin.000036 | less

查询包含几个字段的语句：

mysqlbinlog mysql-bin.000036| egrep '(2011030610002460|2011030310001730|2011030410011680|2011030410006280)'

 

mysqlbinlog查看二进制日志的问题

在使用mysqlbinlog查看日志的时候碰到了一个问题， 
错误提示如下：
/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8' 

产生这个问题的原因是因为我在my.cnf中的client选项组中添加了
default-character-set=utf8

要解决这个bug的方法还是有的，  
一种方法是使用：--no-defaults

./mysqlbinlog --no-defaults mysql-bin.000063 

另一种方法：使用mysqlbinlog工具查看二进制日志时会重新读取的mysql的配置文件my.cnf(windows下是my.ini),而不是服务器已经加载进内存的配置文件。也就是说只要修改并保存了my.cnf文件，而不需要重起mysql服务器，则使用mysqlbinlog查看时修改后的my.cnf配置文件对mysqlbinlog而言已经生效。所以这里可以使用此方法：把client选项组中default-character-set=utf8选项屏蔽掉，如 
#default-character-set=utf8
然后运行mysqlbinlog工具，则不会产生任何问题了。当然记得在不在使用mysqlbinlog工具时，把#default-character-set=utf8
选项恢复为default-character-set=utf8

 

 

 

 

 

 

 

 

 

 

==================================================

http://lucifer119.blog.51cto.com/2914308/1264843

mysql 查看binlog，slowlog，查看锁表情况

shell> mysqlbinlog log-file
使用mysqldumpslow命令获得日志中显示的查询摘要来处理慢查询日志，    例如：[zzx@bj37 data]$ mysqldumpslow bj37-slow.log

一.1 获 取锁等待情况可以通过检查    table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺：

mysql> show status like 'Table%';

+----------------------------+----------+

| Variable_name | Value |

+----------------------------+----------+

| Table_locks_immediate | 105 |

| Table_locks_waited | 3 |

+----------------------------+----------+

2 rows in set (0.00 sec)

可以通过检查 Innodb_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';

+----------------------------------------+----------+

| Variable_name | Value |

+----------------------------------------+----------+|    

Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 2001 |

| Innodb_row_lock_time_avg | 667 |

| Innodb_row_lock_time_max | 845 |

| Innodb_row_lock_waits | 3 |

+----------------------------------------+----------+

5 rows in set (0.00 sec)另外，针对Innodb类型的表，如果 需要察看当前的锁等待情况，可以设置InnoDB Monitors，然后通过Show innodb status察看，设置的方式是：CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;监视器可以通过发出下列语句来被停止：DROP TABLE innodb_monitor;设置监视器后，在show    innodb status的显示内容中，会有详细的当前锁等待的信息，包括表名、锁类型、锁定记录的情况等等，便于进行进一步的分析和问题的确定。打开监视器以后，默    认情况下每15秒会向日志中记录监控的内容，如果长时间打开会导致.err文件变得非常的巨大，所以我们在确认问题原因之后，要记得删除监控表以关闭监视    器。或者通过使用--console选项来启动服务器以关闭写日志文件。

如果是root帐号，你能看到所有用户的当前连接。如果是其它普通帐号，只能看到自己占用的连接。
show processlist;只 列出前100条，如果想全列出请使用show full processlist;
   mysql> show processlist;（非常管用哦）

 

1. 进入mysql/bin目录下输入mysqladmin processlist; 

2. 启动mysql，输入show processlist; 

如果有SUPER权限，则可以看到全部的线程，否则，只能看到自己发起的线程（这是指，当前对应的MySQL帐户运行的线程）。

得到数据形式如下（只截取了三条）： 

mysql> show processlist; 

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

| Id | User | Host | db | Command | Time| State | Info 

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

|207|root |192.168.0.20:51718 |mytest | Sleep | 5 | | NULL 

|208|root |192.168.0.20:51719 |mytest | Sleep | 5 | | NULL 

|220|root |192.168.0.20:51731 |mytest |Query | 84 | Locked | 

select bookname,culture,value,type from book where id=001 

先简单说一下各列的含义和用途，第一列，id，不用说了吧，一个标识，你要kill一个语句的时候很有用。user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。host列，显示这个语句是从哪个ip的哪个端口上发出的。呵呵，可以用来追踪出问题语句的用户。db列，显示这个进程目前连接的是哪个数据库。command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。time列，此这个状态持续的时间，单位是秒。state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个sql语句，已查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table 
　正在检查数据表（这是自动的）。
Closing tables 
　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out 
　复制从服务器正在连接主服务器。
Copying to tmp table on disk 
　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table 
　正在创建临时表以存放部分查询结果。
deleting from main table 
　服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables 
　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables 
　正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed 
　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked 
　被其他查询锁住了。
Sending data 
　正在处理Select查询的记录，同时正在把结果发送给客户端。
Sorting for group 
　正在为GROUP BY做排序。
Sorting for order 
　正在为ORDER BY做排序。
Opening tables 
　这个过程应该会很快，除非受到其他因素的干扰。例如，在执Alter TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates 
　正在执行一个Select DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table 
　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting 
　修复指令正在排序以创建索引。
Repair with keycache 
　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update 
　正在讲符合条件的记录找出来以备更新。它必须在Update要修改相关的记录之前就完成了。
Sleeping 
　正在等待客户端发送新请求. 
System lock 
　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock 
Insert DELAYED正在尝试取得一个锁表以插入新记录。
Updating 
　正在搜索匹配的记录，并且修改它们。
User Lock 
　正在等待GET_LOCK()。
Waiting for tables 
　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, Alter TABLE, RENAME TABLE, REPAIR TABLE,ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert 
Insert DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着
详细出处参考：http://www.jb51.net/article/22409.htm

http://ask.chinaunix.net/question/1923

innodb引擎可以这样看：
mysql> desc information_schema.INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id     | varchar(81)         | NO   |     |         |       |
| lock_trx_id | varchar(18)         | NO   |     |         |       |
| lock_mode   | varchar(32)         | NO   |     |         |       |
| lock_type   | varchar(32)         | NO   |     |         |       |
| lock_table  | varchar(1024)       | NO   |     |         |       |
| lock_index  | varchar(1024)       | YES  |     | NULL    |       |
| lock_space  | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_page   | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_rec    | bigint(21) unsigned | YES  |     | NULL    |       |
| lock_data   | varchar(8192)       | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
10 rows in set (0.10 sec)


另外show engine innodb status\G
里面也可以看到线程持有的锁相关信息

 

http://blog.csdn.net/dc_726/article/details/8576151

1 show processlist;

 

SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程（也就是，与您正在使用的MySQL账户相关的线程）。如果有线程在update或者insert 某个表，此时进程的status为updating 或者 sending data。

 

如果您得到“too many connections”错误信息，并且想要了解正在发生的情况，本语句是非常有用的。MySQL保留一个额外的连接，让拥有SUPER权限的账户使用，以确保管理员能够随时连接和检查系统（假设您没有把此权限给予所有的用户）。

 

Status	含义
Checking table	正在检查数据表（这是自动的）。
Closing tables	正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out	复制从服务器正在连接主服务器。
Copying to tmp table on disk	由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table	正在创建临时表以存放部分查询结果。
deleting from main table	服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables	服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables	正在执行FLUSH TABLES，等待其他线程关闭数据表。
Killed	发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked	被其他查询锁住了。
Sending data	正在处理SELECT查询的记录，同时正在把结果发送给客户端。
Sorting for group	正在为GROUP BY做排序。
Sorting for order	正在为ORDER BY做排序。
Opening tables	这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
Removing duplicates	正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table	获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting	修复指令正在排序以创建索引。
Repair with keycache	修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
Searching rows for update	正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
Sleeping	正在等待客户端发送新请求。
System lock	正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
Upgrading lock	INSERT DELAYED正在尝试取得一个锁表以插入新记录。
Updating	正在搜索匹配的记录，并且修改它们。
User Lock	正在等待GET_LOCK()。
Waiting for tables	该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
waiting for handler insert	INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。

 

大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

 

2 show full processlist;

 

show processlist;只列出前100条，如果想全列出请使用show full processlist;

 

3 show open tables;

 

这条命令能够查看当前有那些表是打开的。In_use列表示有多少线程正在使用某张表，Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。所以这条命令不能帮助解答我们常见的问题：当前某张表是否有死锁，谁拥有表上的这个锁等。

 

show open tables from database;

 

 

4 show status like ‘%lock%’

 

查看服务器状态。

 

 

5 show engine innodb status\G;

 

MySQL 5.1之前的命令是：show innodbstatus\G;，MySQL 5.5使用上面命令即可查看innodb引擎的运行时信息。

 

 

6 show variables like ‘%timeout%’;

 

查看服务器配置参数。

 

 

 

参考资料

 

mysql5.0经常出现 err=1205 - Lockwait timeout exceeded; try restarting transaction

http://www.imysql.cn/node/165

 

mysql show processlist命令详解

http://www.cnblogs.com/JulyZhang/archive/2011/01/28/1947165.html

 

MySQL锁

http://blog.csdn.net/c__ilikeyouma/article/details/8541195

 

SHOW INNODB STATUS提示语法错误？

http://www.itpub.net/thread-1454597-1-1.html

 

SHOW OPEN TABLES – what is in your tablecache

http://blog.sina.com.cn/s/blog_4d1f40c00100rsse.html

 

http://bbs.sciencenet.cn/thread-1225117-1-1.html

启动Mysql时加参数--log-slow-queries来记录执行时间超过long_query_time秒的sql
MySQL 自带 slow log 的分析工具 mysqldumpslow。
slow log 是 MySQL 根据 SQL 语句的执行时间设定，写入的一个文件，用于分析执行较慢的语句。

只要在 my.cnf 文件中配置好： 
log-slow-queries = [slow_query_log_filename] 
即可记录超过默认的 10s 执行时间的 SQL 语句。
如果要修改默认设置，可以添加：
long_query_time = 5 
设定为 5s 。

explain来了解SQL执行的状态。
explain select * from wp_posts\G;

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

如：explain select surname,first_name form a,b where a.id=b.id

EXPLAIN列的解释：

• table：显示这一行的数据是关于哪张表的
• type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
• possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
• key：实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
• rows：MYSQL认为必须检查的用来返回请求数据的行数
• Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

extra列返回的描述的意义

• Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了
• Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了
• Range checked for each Record（index map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一
• Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
• Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候
• Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上
• Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）
• system 表只有一行：system表。这是const连接类型的特殊情况
• const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待
• eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用
• ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好
• range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况
• index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）
• ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

使用show status like "Handler_read%"; 来了解索引的效果。
Handler_read_key 值高表示索引效果好，Handler_read_rnd_next值高表示索引低效。

用show processlist 查看当前运行状态。

mysql> show processlist;

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

| Id | User | Host | db | Command | Time| State | Info 

+-----+-------------+--------------------+-------+---------+-------+----------------------------------+----------

|207|root |192.168.0.20:51718 |mytest | Sleep | 5 | | NULL 

|208|root |192.168.0.20:51719 |mytest | Sleep | 5 | | NULL 

|220|root |192.168.0.20:51731 |mytest |Query | 84 | Locked |

select bookname,culture,value,type from book where id=001

先简单说一下各列的含义和用途，
ID列，一个标识，你要kill一个语句的时候很有用，用命令杀掉此查询 /*/mysqladmin kill 进程号。
user列，显示单前用户，如果不是root，这个命令就只显示你权限范围内的sql语句。
host列，显示这个语句是从哪个ip的哪个端口上发出的。用于追踪出问题语句的用户。
db列，显示这个进程目前连接的是哪个数据库。
command列，显示当前连接的执行的命令，一般就是休眠（sleep），查询（query），连接（connect）。
time列，此这个状态持续的时间，单位是秒。
state列，显示使用当前连接的sql语句的状态，很重要的列，后续会有所有的状态的描述，请注意，state只是语句执行中的某一个状态，一个 sql语句，以查询为例，可能需要经过copying to tmp table，Sorting result，Sending data等状态才可以完成，
info列，显示这个sql语句，因为长度有限，所以长的sql语句就显示不全，但是一个判断问题语句的重要依据。

这个命令中最关键的就是state列，mysql列出的状态主要有以下几种：

Checking table
正在检查数据表（这是自动的）。
Closing tables
正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
Connect Out
复制从服务器正在连接主服务器。
Copying to tmp table on disk
由于临时结果集大于 tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
Creating tmp table
正在创建临时表以存放部分查询结果。
deleting from main table
服务器正在执行多表删除中的第一部分，刚删除第一个表。
deleting from reference tables
服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
Flushing tables
正在执行 FLUSH TABLES，等待其他线程关闭数据表。
Killed
发 送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位， 不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
Locked
被其他查询锁住了。
Sending data
正在处理 SELECT 查询的记录，同时正在把结果发送给客户端。
Sorting for group
正在为 GROUP BY 做排序。
Sorting for order
正在为 ORDER BY 做排序。
Opening tables
这个过程应该会很快，除非受到其他因素的干扰。例如，在执 ALTER TABLE 或 LOCK TABLE 语句行完以前，数据表无法被其他线程打开。 正尝试打开一个表。
Removing duplicates
正在执行一个 SELECT DISTINCT 方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
Reopen table
获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
Repair by sorting
修复指令正在排序以创建索引。
Repair with keycache
修复指令正在利用索引缓存一个一个地创建新索引。它会比 Repair by sorting 慢些。
Searching rows for update
正在讲符合条件的记录找出来以备更新。它必须在 UPDATE 要修改相关的记录之前就完成了。
Sleeping
正在等待客户端发送新请求.
System lock
正在等待取得一个外部的系统锁。如果当前没有运行多个 mysqld 服务器同时请求同一个表，那么可以通过增加 --skip-external-locking参数来禁止外部系统锁。
Upgrading lock
INSERT DELAYED 正在尝试取得一个锁表以插入新记录。
Updating
正在搜索匹配的记录，并且修改它们。
User Lock
正在等待 GET_LOCK()。
Waiting for tables
该 线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情 况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, 或 OPTIMIZE TABLE。
waiting for handler insert
INSERT DELAYED 已经处理完了所有待处理的插入操作，正在等待新的请求。
大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。

mysql手册里有所有状态的说明，链接如下：http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

http://m.studyofnet.com/news/494.html

3、explain来了解SQL执行的状态

explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了：

例如：

explain select surname,first_name form a,b where a.id=b.id

结果如图

 

EXPLAIN列的解释

table

显示这一行的数据是关于哪张表的

type

这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句 中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows

MYSQL认为必须检查的用来返回请求数据的行数

Extra

关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

 

extra列返回的描述的意义

Distinct

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record（index map:#）

没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort

看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary

看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Where used

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）

const

表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待

eq_ref

在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用

ref

这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

range

这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

index

这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）

ALL

这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

http://www.jb51.net/article/38466.htm

通过status命令，查看Slow queries这一项，如果值长时间>0,说明有查询执行时间过长