生活在別處

Super Smack 是一个强大的压力测试工具，支持 MySQL, PostgreSQL, Oracle。最开始的版本是由Sasha Pachev写成，由Jeremy Zawodny在维护，而现在，是
Tony Bourke在维护，根据 Tony Bourke 的 开发 log 来看，2005-08-30 后，super smack 就已经停止发布新的版本，但这并不妨碍我们现在继续使用它(这个工具的开发者和维护者很伟大)。

安装

安装有点点麻烦，主要是编译时会出现一些问题。

[root@localhost tmp]# wget http://vegan.net/tony/supersmack/super-smack-1.3.tar.gz
[root@localhost tmp]# tar xvzf super-smack-1.3.tar.gz
[root@localhost super-smack-1.3]# ./configure  --prefix=/usr/local/super-smack --with-mysql --with-mysql-lib=/usr/local/mysql/lib/mysql --with-mysql-include=/usr/local/mysql/include/mysql
[root@localhost super-smack-1.3]# make
[root@localhost super-smack-1.3]# make install

说明

1 编译时，必须指定它所支持的数据库管理系统，否则会报如下错误。

......
configure: error:

You should include support for at least one database!

Reconfigure with one or more of:
  --with-mysql
  --with-pgsql
  --with-oracle

2 选择支持 MySQL 后, 在 MySQL 编译安装的情况下时, 也需要在编译参数中指定 MySQL 的 lib。
3 编译时，dictionary.h 和 super-smack.cc 报错：

......
dictionary.h:93: error: ‘strlen’ was not declared in this scope
super-smack.cc:126: error: ‘strlen’ was not declared in this scope

在 super smack 源代码的 src 目录，找到 dictionary.h, super-smack.cc，分别加上#include <string.h>。
4 编译时，query.cc 报错

query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:200: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision
query.cc:219: error: cast from ‘char*’ to ‘unsigned int’ loses precision

在 super smack 源代码的 src 目录， 找到 query.cc文件，将上面指定的 200, 219 行中的unsigned int改为unsigned long。

用其测试 MySQL 之前奏

1 看 Makefile 才知道， smack 文件是放在 /usr/share/smacks 这个目录下(开始并不知道源代码中有一份), 产生的数据文件是在/var/smack-data目录下

...
SMACKS_DIR = /usr/share/smacks
DATADIR = /var/smack-data
...

2 根据 MySQL 的参数对应修改 /usr/share/smacks 目录下的 select-key.smack 和 update-select.smack 文件。包括 user, host, db, pass, 还有，最重要的 socket 路径。

3 未将 super smack 的 bin 目录加入 PATH 时，则还需：

gen_data_file "gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";
#改为
gen_data_file "./gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";
#或者直接
gen_data_file "/usr/local/super-smack/bin/gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d";

4 因为在 select-key.smack 和 update-select.smack 文件中指定的数据文件是 words.dat, 而安装默认情况下 words.dat 为空文件:

[root@localhost smack-data]# ls -al
total 5320
drwxr-xr-x  2 root root    4096 May 26 18:12 .
drwxr-xr-x 24 root root    4096 May 26 17:25 ..
-rw-r--r--  1 root root 5421337 May 26 17:25 ..  http_auth.dat
-rw-r--r--  1 root root       0 May 26 17:25 ..  words.dat

所以，需将 select-key.smack 和 update-select.smack 文件中指定的数据 words.dat 改为 http_auth.dat(其实为了统一，最好也是 http_auth.dat，因为文件中指定测试的表名是 http_atuh)。

当然，为了不修改，也可以在开始先执行如下命令：

[root@localhost bin]# /usr/local/super-smack/bin/gen-data -n 90000 -f %12-12s%n,%25-25s,%n,%d > /var/smack-data/words.dat

5 将 /usr/share/smacks 下文件 copy 到 /usr/local/super-smack/bin/ 目录下：

[root@localhost super-smack]# cp /usr/share/smacks/*  bin/

运行

[root@localhost bin]# ./super-smack -d mysql select-key.smack 20 1000
Query Barrel Report for client smacker1
connect: max=2204ms  min=1ms avg= 221ms from 20 clients
Query_type      num_queries     max_time        min_time        q_per_s
select_index    40000   0       0       5017.26

参数：

• -d 指定测试的数据库管理系统的类型。
• 20 20 个线程
• 1000 每个线程 1000 个查询

实际上，还有-D参数来指定数据文件，默认路径如前面提到是 /var/smack-data， 这个路径需跟 select-key.smack 和 update-select.smack 指定的一致。

返回结果：

• max=2204ms min=1ms avg= 221ms from 20 clients 连接的最大、最小及平均花费时间。
• q_per_s|5017.26 QPS，每秒请求处理数
• 40000, 脚本中，对查询次数做了翻倍处理, 所以， 20 × 1000 x 2 = 40,000.

FROM: Using MySQL as a NoSQL – A story for exceeding 750,000 qps on a commodity server

由于 MySQL 的局限性，很多站点都采用了 MySQL+Memcached 的架构。另外一些站点放弃 MySQL 而采用 NoSQL，比如 TokyoCabinet/Tyrant 等。不可否认，在做一些简单查询 (尤其 PK 查询) 的时候，NoSQL 比 MySQL 要快很多很多。而且网站上的绝大多数查询都是这样的简单查询。

像其他大规模的公司一样，DeNA 也面临过类似的问题。但最后我们采用的是一完全不同的方法, 仅使用了 MySQL。我们仍然使用 Memcached 做前端缓存(例如，预处理 HTML， 数量/摘要 信息)，但在后端，既没有使用 Memcached 缓存任何记录，也没有使用 NoSQL，这是为什么呢？因为与其他的 NoSQL 产品相比，我们的 MySQL 能得到更好的性能。 在我们的基准测试中，一普通的 MySQL/InnoDB 5.1 服务器达到了 750,000+ QPS，生产环境中的性能当然更不列外。或许，你们很难相信这个数字，但这是事实。我将在以下分享我们的经验。

(作者经历)2010-08，我离开了 Oracle, 现在任职于日本最大社交游戏平台供应商之一的 DeNA。

SQL 真的适合做 PK 查询？

在每秒中，需要做多少次的 PK 查询了？在 DeNA 公司的应用中，经常要进行 PK 查询。比如根据 user id 取出 userinfo，根据 diary id 取出日志内容， 对于这样的需求，不用说，Memcached 和 NoSQL 都相当适合。在简单的多线程 “Memcached GET”基准测试中，很可能每秒进行 400,000 次 get 操作,即使 Memcached client 在不同的服务器。在一台 Nehalem box 2.5GHz x 8 核 CPU, Broadcom 四端口千兆网卡的服务器上，最新的 libMemcached 和 Memcached 每秒可达到 420,000 次 get 操作。

在 MySQL 下， 每秒可作多少次的 PK 查询呢, 我们可用 sysbench, super-smack or mysqlslap 等来进行基准测试

[matsunobu@host ~]$ mysqlslap --query="select user_name,..  from test.user where user_id=1" \
--number-of-queries=10000000 --concurrency=30 --host=xxx -uroot

通过如下命令，很快就得知 InnoDB 的 QPS 大概为 100,000, 几乎只有 Memcached 的 1/4.

[matsunobu@host ~]$ mysqladmin extended-status -i 1 -r -uroot \
| grep -e "Com_select"

...
| Com_select                            | 107069     |
| Com_select                            | 108873     |
| Com_select                            | 108921     |
| Com_select                            | 109511     |
| Com_select                            | 108084     |
| Com_select                            | 108483     |
| Com_select                            | 108115     |
...

看上去， 100, 000+ QPS 也不是太差，但为什么 MySQL 比 Memcached 差这么多呢，MySQL 到底在做什么呢。从 vmstat 的统计信息得知, %user 和 %system 的数据都非常高.

[matsunobu@host ~]$ vmstat 1

r   b  swpd   free   buff  cache      in     cs us sy id wa st
23  0     0 963004 224216 29937708 58242 163470 59 28 12  0  0
24  0     0 963312 224216 29937708 57725 164855 59 28 13  0  0
19  0     0 963232 224216 29937708 58127 164196 60 28 12  0  0
16  0     0 963260 224216 29937708 58021 165275 60 28 12  0  0
20  0     0 963308 224216 29937708 57865 165041 60 28 12  0  0

再看 Oprofile 输出，可知 CPU 消耗的出去:

samples  %        app name                 symbol name
259130    4.5199  mysqld                   MYSQLparse(void*)
196841    3.4334  mysqld                   my_pthread_fastmutex_lock
106439    1.8566  libc-2.5.so              _int_malloc
94583     1.6498  bnx2                     /bnx
284550    1.4748  ha_innodb_plugin.so.0.0.0 ut_delay
67945     1.1851  mysqld                   _ZL20make_join_statisticsP4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
63435     1.1065  mysqld                   JOIN::optimize()
55825     0.9737  vmlinux                  wakeup_stack_begin
55054     0.9603  mysqld                   MYSQLlex(void*, void*)
50833     0.8867  libpthread-2.5.so        pthread_mutex_trylock
49602     0.8652  ha_innodb_plugin.so.0.0.0 row_search_for_mysql
47518     0.8288  libc-2.5.so              memcpy
46957     0.8190  vmlinux                  .text.elf_core_dump
46499     0.8111  libc-2.5.so              malloc

MySQL 的 SQL 解析阶段，有调用 MYSQLparse() 和 MYSQLlex(); 查询优化阶段，调用 make_join_statistics() 和 JOIN::optimize()。很明显，主要耗资源的是SQL 层，而不是 InnoDB 存储层。与 Memcached/NoSQL 比起来，MySQL 还要额外做一些工作：

• Parsing SQL statements 解析 SQL.
• Opening, locking tables 打开并锁定表.
• Making SQL execution plans SQL 执行计划.
• Unlocking, closing tables 解锁并关闭表.

另外，MySQL 还必须要做大量的并发控制，比如在发送/接收网络数据包的时候，fcntl() 就要被调用很多次; Global mutexes 比如 LOCK_open，LOCK_thread_count 也被频繁地取得/释放。所以, 在 Oprofile 的输出中，排在第二位的是 my_pthread_fastmutex_lock()。并且 %system 占用的 CPU 相当高(28%)。

其实 MySQL 开发团队和外围的开发团体已意识到大量并发控制对性能的影响，MySQL5.5 中已经解决了一些问题。未来的 MySQL 版本中，应该会越来越好。

还有一个大的问题是，%user 达到了60%。互斥量的争夺导致 %system 上增，而不是 %user，即使 MySQL 内部关于互斥量的的问题都得到修复，还是很难达到我们所期望的 300,000 QPS.也许，会有人提到使用 HANDLER ，但是因为在解析 SQL时，opening/closing table 还是必须的，所以对于提高吞吐量，它还是只能爱莫能助。

在完全内存操作的情况时, CPU的效率非常重要

如果只有一小部分数据进入内存，那么 SQL 带来的消耗可以忽略不计。很简单，因为磁盘的 I/0 操作所带来的消耗会要大，这种情况下时，就不需要太过的去考虑 SQL 所带来的消耗。

但是，在大多数的 hot MySQL 服务器中, 大部分的数据都是因为全部载入至内存中而变的只受 CPU 的限制。Profiling 的结果就类似上所述的那样: SQL 层消耗了大量的资源。假设，需要做大量的 PK 查询(i.e. SELECT x FROM t WHERE id=?)或者是做 LIMIT 的范围查询, 即使有 70-80% 都是在同一张表中做 PK 查询(仅仅只是查询条件中给定的值不同，即 value 不同而已)， MySQL 还是每次需要去做 parse/open/lock/unlock/close, 这对我们来说，是非常影响效率的。

NDBAPI

到底有没有好的方法来减少 MySQL SQL 层的 CPU 资源/争夺呢? 如果使用 MySQL Cluster， NDBAPI 不失为一个很好的解决办法。 在我还是 MySQL/Sun/Oracle 的顾问时，就见到过很多客户对SQL Node + NDB performance 感到非常不爽，但当他们用了 NDBAPI 客户端后，发现性能调提高了 N 倍。当然，在 MySQL Cluster 中是可以同时使用 NDBAPI 和 SQL 的，但在做频繁的访问模式时还是推荐使用 NDBAPI，而在 ad-hoc 或者 查询不频繁的情况下使用 SQL + MySQL + NDB。

以快捷的速度访问 API, 这正是我们需要的，但同时我们也想在 ad_hoc 或者复杂的查询的情况时还是使用 SQL. 像其他的 web service， DeNA 使用的是 InnoDB, 转为 NDB，这并不是一件容易的事情，因为内置InnoDB 即不支持 SQL 也不支持网络层的服务。

HandlerSocket —— 一个采用 NoSQL 网络协议的MySQL插件

最好的办法可以是在 MySQL 的内部，实现一以 MySQL plugin 的形式存在的 NoSQL 的网络服务。它侦听在某端口来接收采用 NoSQL 协议/API 的通讯， 然后通过 MySQL 内部的存储引擎 API 来直接访问 InnoDB。这种方法的理念类似于 NDBAPI， 但是它可以做到与 InnoDB 通讯。

这个理念最初是去年由 Kazuho Oku 在 Cybozu Labs 上提出的，他曾写过采用 Memcached protocols 通讯的MyCached UDF。而我的大学同学实现了另外一个插件 — HandlerSocket, 下面图描述了 HandlerSocket 具体做了哪些工作:

因为 HandlerSocket 是以 MySQL daemaon plugin 形式存在，所以在应用中可把 MySQL 当 NoSQL 使用. 它最大的功能是实现了与存储引擎交互，比如 InnoDB，而这不需要任何的 SQL 方面的开销. 访问 MySQL 的 table 时，当然她也是需要 open/close table 的，但是 它并不是每次都去 open/close table, 因为它会将以前访问过的 table 保存下来以供来是使用，而 opening/closing tables 是最耗资源的，而且很容易引起互斥量的争夺，这样一来，对于提高性能，非常有效。在流量变小时， HandlerSocket 会 close tables, 所以，它不会阻塞 administrative commands (DDL).

它与MySQL + Memcached 的区别在哪呢？ 对比图1 和图2 ，可从中看出其不同点。图2 展示了典型的 MySQL + Memecached 的使用. 因为 Memcached 的 get 操作比 MySQL 的内存中/磁盘上的主键查询要快很多，所以 Memcached 用于缓存数据库记录。如果 HandlerSocket 的查询速度能和 Memcached 媲美，我们就不需要使用 Memcached 来缓存记录。

使用 HandlerSocket

举一个例子，假设有一 user 表，通过 user_id 来获取用户信息:

CREATE TABLE user
(
user_id INT UNSIGNED PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(255),
created DATETIME
)
ENGINE=InnoDB;

用 SELECT 语句获取用户信息

mysql> SELECT user_name, user_email, created FROM user WHERE user_id=101;
+---------------+-----------------------+---------------------+
| user_name     | user_email            | created
|  +---------------+-----------------------+---------------------+
| Yukari Takeba | yukari.takeba@dena.jp | 2010-02-03 11:22:33
|  +---------------+-----------------------+---------------------+
1 row in set (0.00 sec)

下面我们来看看如何使用 HandlerSocket 完成同样的事情.

安装 HandlerSocket

HandlerSocket具体安装步骤请参考这里，基本步骤如下：

• 1 下载HandlerSocket
• 2 编译 HandlerSocket(客户端和服务端)

[root@localhost handlersocket]# ./autogen.sh
[root@localhost handlersocket]# ./configure --with-mysql-source=mysql-source-dir --with-mysql-bindir=mysql-server-bin-dir
[root@localhost handlersocket]# make
[root@localhost handlersocket]# make install

• 3 安装 HandlerSocket
  将如下内容添加至 MySQL 配置文件 my.cnf

  [mysqld]
  loose_handlersocket_port = 9998
    # the port number to bind to (for read requests)
  loose_handlersocket_port_wr = 9999
    # the port number to bind to (for write requests)
  loose_handlersocket_threads = 16
    # the number of worker threads (for read requests)
  loose_handlersocket_threads_wr = 1
    # the number of worker threads (for write requests)
  open_files_limit = 65535
    # to allow handlersocket accept many concurrent
    # connections, make open_files_limit as large as
    # possible.
  

  以 root 身份登录 MySQL

  mysql> INSTALL PLUGIN 'handlersocket' soname 'handlersocket.so';
  

  重启 MySQL 服务。

因为 HandlerSocket是 MySQL 插件，所以可以象使用其它插件，如 InnoDB, Q4M 和 Spider 那样使用它，即不需要修改 MySQL 源代码，MySQL 最好是 5.1 或更高版本，编译 HandlerSocket 时需要 MySQL 源码和 MySQL bin 库。

书写 HandlerSocket 客户端代码

目前已提供 C++ 和 perl 调用的客户端库，下面是使用 perl 调用的实例代码:

#!/usr/bin/perl  

use strict;
use warnings;
use Net::HandlerSocket;  

#1. establishing a connection
my $args = { host => 'ip_to_remote_host', port => 9998 };
my $hs = new Net::HandlerSocket($args);  

#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
    'user_name,user_email,created');
die $hs->get_error() if $res != 0;  

#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_name\t$user_email\t$created\n";
}  

#4. closing the connection
$hs->close()

#!/usr/bin/perl  

use strict;
use warnings;
use Net::HandlerSocket;  

#1. establishing a connection
my $args = { host => 'ip_to_remote_host', port => 9998 };
my $hs = new Net::HandlerSocket($args);  

#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
    'user_name,user_email,created');
die $hs->get_error() if $res != 0;  

#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
  my $user_name= $res->[$row + 0];
  my $user_email= $res->[$row + 1];
  my $created= $res->[$row + 2];
  print "$user_name\t$user_email\t$created\n";
}  

#4. closing the connection
$hs->close();

上面代码是通过 user_id=101 条件在 user 表获取用户 user_name, user_email和 created 信息，得到的结果应该和之前在 MySQL client 查询出来的结果一样。

[matsunobu@host ~]$ perl sample.pl
Yukari Takeba   yukari.takeba@dena.jp   2010-02-03 11:22:33

对于大多数Web应用程序而言，保持轻量级的 HandlerSocket 连接是一个很好的做法(持续连接)，让大量的请求可以集中于主要逻辑(上面代码中的#3部分)。
HandlerSocket 协议是一个小尺寸的基于文本的协议，和 Memcached 文本协议类似，可以使用 telnet 通过 HandlerSocket 获取数据。

[matsunobu@host ~]$ telnet 192.168.1.2 9998
Trying 192.168.1.2...
Connected to xxx.dena.jp (192.168.1.2).
Escape character is '^]'.
P       0       test    user    PRIMARY user_name,user_email,created
0       1
0       =       1       101
0       3       Yukari Takeba   yukari.takeba@dena.jp   2010-02-03 11:22:33

基准测试

现在是时候展示基准测试结果，使用上面的 user 表，从多线程远程客户端测试了执行主键查询操作的次数，所有用户数据都装入到内存中(我测试了 100 万行)，也用类似的数据测试了 Memcached(我使用 libMemcached 和 Memcached_get() 获取用户数据)，在 MySQL SQL 测试中，我使用了的是传统的 SELECT 语句: “SELECT user_name, user_email, created FROM user WHERE user_id=?”, Memcached 和 HandlerSocket 客户端代码均使用 C/C++ 编写，所有客户端程序都位于远程主机上，通过 TCP/IP 连接到 MySQL/Memcached。最高的吞吐量情况如下：

                            approx qps     server CPU util
MySQL via SQL               105,000      %us 60%  %sy 28%
Memcached                   420,000      %us  8%  %sy 88%
MySQL via HandlerSocket     750,000      %us 45%  %sy 53%

HandlerSocket的吞吐量比使用传统 SQL 时高出 7.5, 而且 %us 也只有使用传统 SQL 时的3/4, 这说明 MySQL 的 SQL 层是非常耗资源的，如果能跳过这一层，性能肯定会大大提升。有趣的是，MySQL 使用 HandlerSocket 时的速度比使用 Memcached 也要快 178%，并且 Memcached 消耗的 %sy 资源也更多。所以虽然 Memcached 是一个很好的产品，但仍然有优化的空间。

下面是oprofile输出内容，是在 MySQL HandlerSocket 测试期间收集到的，在核心操作，如网络数据包处理，获取数据等的 CPU 资源消耗(bnx2是一个网络设备驱动程序)。

samples  %        app name                 symbol name
984785    5.9118  bnx2                     /bnx2
847486    5.0876  ha_innodb_plugin.so.0.0.0 ut_delay
545303    3.2735  ha_innodb_plugin.so.0.0.0 btr_search_guess_on_hash
317570    1.9064  ha_innodb_plugin.so.0.0.0 row_search_for_mysql
298271    1.7906  vmlinux                  tcp_ack
291739    1.7513  libc-2.5.so              vfprintf
264704    1.5891  vmlinux                  .text.super_90_sync 

248546    1.4921  vmlinux                  blk_recount_segments
244474    1.4676  libc-2.5.so              _int_malloc
226738    1.3611  ha_innodb_plugin.so.0.0.0 _ZL14build_template  P19row_prebuilt_structP3THDP8st_tablej
206057    1.2370  HandlerSocket.so         dena::hstcpsvr_worker::run_one_ep()
183330    1.1006  ha_innodb_plugin.so.0.0.0 mutex_spin_wait
175738    1.0550  HandlerSocket.so         dena::dbcontext::  cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&,   ha_rkey_function, dena::cmd_exec_args const&)
169967    1.0203  ha_innodb_plugin.so.0.0.0 buf_page_get_known_nowait
165337    0.9925  libc-2.5.so              memcpy
149611    0.8981  ha_innodb_plugin.so.0.0.0 row_sel_store_mysql_rec
148967    0.8943  vmlinux                  generic_make_request

因为 HandlerSocket 是运行于 MySQL 内部，直接与 InnoDB 交互，所以，可以使用常见的 SQL 命令，如 SHOW GLOBAL STATUS 获得统计信息，Innodb_rows_read 达到了 750000+ 是值得一看的。

$ mysqladmin extended-status -uroot -i 1 -r | grep "InnoDB_rows_read"
...
| Innodb_rows_read                      | 750192     |
| Innodb_rows_read                      | 751510     |
| Innodb_rows_read                      | 757558     |
| Innodb_rows_read                      | 747060     |
| Innodb_rows_read                      | 748474     |
| Innodb_rows_read                      | 759344     |
| Innodb_rows_read                      | 753081     |
| Innodb_rows_read                      | 754375     |
...

测试用机的详细信息如下：

型号	戴尔PowerEdge R710
CPU	Nehalem 8核，E5540@2.53GHz
内存	32GB(所有数据都装入缓冲池)
MySQL	5.1.50 InnoDB
Memcached/libMemcached	1.4.5(Memcached)，0.44(libMemcached)
Network	Boradcom NetXtreme II BCM5709 1000Base-T(内建四端口，使用了其中三个)

Memcached 和 HandlerSocket 都做了网络 I/O 限制，当我测试单个端口时，HandlerSocket 的 QPS 为 260000，而 Memcached 为 220000。　

HandlerSocket 的特点和优势

如下所述，HandlerSocket 有其自己的特点和优势，而其中一些对我们来说, 是真的很给力.

支持多种查询模式

HandlerSocket 目前支持 主键/唯一性查询，非唯一性索引查询，范围扫描，LIMIT 和 INSERT/UPDATE/DELETE，但还不支持未使用任何索引的操作。另外，multi_get()(类似于in(1,2,3), 只需一次网络往返)还可获取多行数据。到这里可查询详细信息。

处理大量并发连接

HandlerSocket 连接是轻量级的，因为 HandlerSocket 采用epoll()和 worker-thread/thread-pooling 架构，而 MySQL 内部线程的数量是有限的(可以由 my.cnf中的 handlersocket_threads参数控制)，所以即使建立上千万的网络连接到 HandlerSocket，它的稳定性也不会受到任何影响(消耗太多的内存，会造成巨大的互斥竞争等其他问题，如bug#26590，bug#33948，bug#49169)。

及其优秀的性能

HandlerSocket,如上所描述, 相对于其它 NoSQL 阵容,性能表现一点也不逊色。事实上，我还未曾见过哪个 NoSQL 产品在一台普通服务器上可达到 750000+ 次查询。它不仅没有调用与 SQL 相关的函数，还优化了网络/并发相关的问题。

• 更小的网络数据包
  和传统 MySQL 协议相比，HandlerSocket 协议更简短，因此整个网络的流量更小。
• 运行有限的 MySQL 内部线程数
  参考上面的内容。
• 将客户端请求分组
  当大量的并发请求抵达 HandlerSocket 时，每个工作线程尽可能多地聚集请求，然后同时执行聚集起来的请求和返回结果。这样，通过牺牲一点响应时间，而大大地提高性能。例如，你可以得到以下好处，如果有人感兴趣，我会在今后的文章中对它们加以深入的解释。
  减少fsync()调用的次数.
  减少复制延迟.

无重复缓存

当使用 Memcached 缓存 MySQL/InnoDB 记录时，在 Memcached 和 InnoD B缓冲池中均缓存了这些记录，因此效率非常低(内存仍然很贵). 而采用 HandlerSocket插件, 由于它访问 InnoDB 存储引擎，记录缓存在 InnoDB 缓冲池中，这样，其它 SQL 语句就可以重复使用它。

无数据不一致的现象

由于数据只存储在一个地方(InnoDB 内)，不像使用 Memcached 时，需要在 Memcached 和 MySQL 之间检查数据一致性。

崩溃安全

后端存储是 InnoDB，它是事务性和崩溃安全的，即使有设置innodb-flush-log-at-trx-commit!=1，在服务器崩溃时也只会丢掉 < 1s 内的数据。

可从 MySQL 客户端使用 SQL

在许多情况下，人们仍然希望使用 SQL(如生产摘要报告)，这就是为什么我们不能使用嵌入式 InnoDB 的原因，大多数 NoSQL 产品都不支持 SQL 接口，HandlerSocket 仅仅是一个 MySQL 插件，可以从 MySQL 客户端发送 SQL 语句，但当需要高吞吐量时，最好使用 HandlerSocket。

从 MySQL获益

因为 HandlerSocket 运行于 MySQL 内部，因此所有 MySQL 操作，如 SQL，在线备份，复制，通过 Nagios/EnterpriseMonitor 监控等都是支持的，HandlerSocket 获得可以通过普通的 MySQL 命令监控，如SHOW GLOBAL STAUTS，SHOW ENGINE INNODB STATUS和SHOW PROCESSLIST等.

不需要修改/重建 MySQL

因为 HandlerSocket 是一个插件，所以它支持 MySQL 社区版和企业服务器版，而无需对 MySQL 做出任何修改就可以使用。

独立于存储引擎

虽然我们只测试了5.1和5.5 InnoDB 插件，但 HandlerSocket 可以和任何存储引擎交互。

注意事项和限制

需要学习HandlerSocket API

尽管它很容易使用，但仍然需要学习如何与 HandlerSocket 交互，我们提供了C++ API、Perl绑定。

没有安全功能

和其它NoSQL数据库类似，HandlerSocket不支持安全功能，HandlerSocket的工作线程以系统用户权限运行，因此应用程序可以访问通过 HandlerSocket 协议的所有表，当然，你可以象其它 NoSQL 产品一样使用防火墙过滤数据包。

对于 HDD 绑定工作负载没有优势

对于 HDD I/O 绑定工作负载，数据库每秒无法执行数千次查询，通常只有 1-10% 的 CPU 利用率，在这种情况下，SQL 执行层不会成为瓶颈，因此使用HandlerSocket没有什么优势，我们只在数据完全装载到内存的服务器上使用 HandlerSocket。

DeNA 在生产环境中使用 HandlerSocket

我们已经在生产环境中使用了 HandlerSocket 插件，效果很明显，因为我们减少了许多 Memcached 和 MySQL 从属服务器，而且整个网络流量也在减少。目前还没有发现任何性能问题(如响应时间慢，延迟等)。
我认为, NoSQL/Database 社区完全低估了 MySQL, 相对于其他产品来说，它历史悠久，而且到目前为止，我优秀的前同事们也做了许多独特的、伟大的改进。从 NDBAPI 可以看出 MySQL 有成为 NoSQL 的潜力，因为存储引擎 API 和守护进程接口的完全独立，使得 Akira 和 DeNA 开发 HandlerSocket 成为可能。作为 MySQL 一名前员工和对 MySQL 长期的了解，我想看到 MySQL 变得更好，更受欢迎，而不仅仅只作为一个 RDBMS，也应该成为 NoSQL 阵营中的一员。

采用如下参数编译 MySQL 5.1 版本

[root@localhost mysql-5.1.56]# ./configure --prefix=/usr/local/mysql/ \
--without-debug \
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--enable-assembler  \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=latin1,gb2312 \
--with-pthread --enable-thread-safe-client   \
--with-plugins=myisam,innodb_plugin,innobase

在 client 使用命令

mysql> INSERT PLUGIN plugin_name SONAME 'plugin_name.so';

提示如下错误

ERROR 1289 (HY000): The 'plugin' feature is disabled; you need MySQL built with 'HAVE_DLOPEN' to have it working

Bug #45605 是类似问题, 中间回复有一段说明产生如上错误是因为编译时有使用参数 --with-mysqld-ldflags=-all-static

- Why does the compilation process not recognize that Ubuntu default install supports HAVE_DLOPEN ?
Server was built with –with-mysqld-ldflags=-all-static

查资料时, 有说到在编译之前, 先设置如下两个环境变量就可以解决问题

# export CFLAGS="-O2 -DHAVE_DLOPEN=1"
# export CXXFLAGS="-O2 -DHAVE_DLOPEN=1"

但我测试后, 编译倒完全不能通过. 不知道有木有人采用这种方法有解决了问题的？

checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for gcc... gcc
checking for C compiler default output file name...
configure: error: C compiler cannot create executables
See `config.log' for more details.

之前編譯 MySQL 5.1.54, 使用如下參數

[root@localhost mysql-5.1.54]# ./configure --prefix=/usr/local/mysql/ \
--without-debug \
--with-unix-socket-path=/usr/local/mysql/tmp/mysql.sock \
--with-client-ldflags=-all-static \
--with-mysqld-ldflags=-all-static \
--enable-assembler  \
--with-charset=utf8 \
--with-collation=utf8_general_ci \
--with-extra-charsets=latin1,gb2312 \
--with-pthread --enable-thread-safe-client   \
--with-plugins=myisam,innodb_plugin

編譯後, stoRe engine 可使用 InnoDB. 但這次因為測試 HandlerSocket, 到官方下載的是 MySQL 5.1.56 的源代碼, 同樣使用如上同樣的參數, 但不能使用 InnoDB, 後面做多番嘗試沒成功後, 將後面參數修改為 --with-plugins=myisam,innodb_plugin,innobase後, 再編譯安裝, OK.

安裝 MySQL 5.1.56，編譯時報如下錯誤

/bin/rm: cannot remove `libtoolT’: No such file or directory

忽略錯誤，強制make，出錯.網上查了下，有很多提到採用

# aclocal
# autoconf
# automake
# libtoolize –force

OR

# autoreconf –force –install
# libtoolize –automake –force
# automake –force –add-missing

但安裝無法成功.

Debian Bug report logs – #523750 autoconf false error這裡是類似的錯誤，臨時解決方法是刪除 /usr/share/aclocal/libtool.m4 文件中的 680 行$RM -f "$cfgfile", 遂按此方法重新編譯安裝，還是失敗.

Package: libtool
Version: 2.2.6a-2
Normal run of libtool, produces no other warnings until the end of the master configure script.
At which point out pops:
config.status: executing libtool commands
/bin/rm: cannot remove `libtoolT’: No such file or directory
This is resolved by placing:
$RM -f “$cfgfile”
at line 680 of /usr/share/aclocal/libtool.m4
One thing to note, is that the package producing this error has been bootstrapped to install libLtdl and related stuff under lib/libLtdl instead of the defaults.
The actual error may be elsewhere with a failure of the macro to detect and handle that case.

因為在上段引用中，有看到提到 libLtdl，於是，搜索了下，在 11.6 How to distribute libltdl with your package，看到聲明 libLtdl 的路徑libtoolize --ltdl, 用這種方法再重新編譯，雖然編譯還是報如上同樣的錯誤，但安裝已能順利進行下去.

— Macro: LT_CONFIG_LTDL_DIR (directory)
Declare directory to be the location of the libltdl source files, for libtoolize –ltdl to place them.

昨日, 因為從庫 down 掉, 再次啟動時, Relay_log_file 已差很遠. 只好手動同步下, 先記錄下當前的binlog_file, binlog_pos, 然後按正常步驟,

mysql> STOP SLAVE;
mysql> RESET SLAVE;
mysql> CHANGE MASTER TO ...;
mysql> START SLAVE

但 SHOW SLAVE STATUS 提示：

Got fatal error 1236 from master when reading data from binary log: ‘Could not find first log file name in binary log index file

報找不到日誌錯誤, 但查看日誌列表時, 指定的日誌是存在着的. 死活查不到問題出在那, 滾動鼠標滑輪時, 不小心將編輯器的字體改小, 發現 master_log_file=” mysql_bin.000011″, 問題竟出這裡. 這就是不小心及偷懶的代價…

使用 MySQL自带命令 mysqldumpslow 查看

OPTIONS

• -s ORDER ORDER, 主要有 c, t, l, r 和 ac, at, al, ar, 分别是按照 query次数, 时间, lock的时间和返回的记录数来排序, 前面加了a时倒序.
• -t NUM top NUM, 即为返回前面多少条的数据.
• -g PATTERN grep: 后边可以写一个正则匹配模式, 大小写不敏感

#查看访问次数最多的 20 个 sql 语句
[root@localhost ~]# mysqldumpslow -s c -t 20 /usr/local/mysql/var/mysql_slow_query.log
#查看返回记录集最多的 20 个 sql
[root@localhost ~]# mysqldumpslow -s r -t 20 /usr/local/mysql/var/mysql_slow_query.log
#按照时间返回前 10 条里面含有左连接的 sql 语句
[root@localhost ~]# mysqldumpslow -t 10 -s t -g "LEFT JOIN" /usr/local/mysql/var/mysql_slow_query.log

mysqlsla 分析 MySQL 慢查询日志

• mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log slow log
• mysqlsla -lt general /usr/local/mysql/var/mysql_query.log general log
• mysqlbinlog /usr/local/mysql/var/mysql-bin.000001 | mysqlsla -lt binary - binary log

mysqlsla(现已不在维护)是第三方提供的perl脚本, 不过它功能强悍, 可以分析包括慢查询在内的多种格式的日志.

[root@localhost tmp]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
[root@localhost tmp]# tar zxf mysqlsla-2.03.tar.gz
[root@localhost tmp]# cd mysqlsla-2.03
[root@localhost mysqlsla-2.03]# perl Makefile.PL
[root@localhost mysqlsla-2.03]# make
[root@localhost mysqlsla-2.03]# make install

安装完成后, mysqlsla 会加入到 /usr/bin 目录.

mysqlsla 会自动判断日志类型, 缺省会打印出前十条结果, 可以通过类似--top 100的参数来修改, 如果觉得每次输入麻烦, 还可以建立一个配置文件 ~/.mysqlsla, 在文件里写上:

top=100

这样就不用每次都手动输入参数了.

[root@localhost mysqlsla-2.03]# mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log

返回结果:

Report for slow logs: /usr/local/mysql/var/mysql_slow_query.log
4 queries total, 2 unique
Sorted by 't_sum'
Grand Totals: Time 11 s, Lock 0 s, Rows sent 712.40k, Rows Examined 712.40k

______________________________________________________________________ 001 ___
Count         : 3  (75.00%)
Time          : 8 s total, 2.666667 s avg, 2 s to 4 s max  (72.73%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 178.10k avg, 178.10k to 178.10k max  (75.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max  (75.00%)
Database      : test
Users         :
        root@localhost  : 66.67% (2) of query, 75.00% (3) of all users
        root1@localhost  : 33.33% (1) of query, 25.00% (1) of all users

Query abstract:
SELECT * FROM test_1;

Query sample:
select * from test_1;

______________________________________________________________________ 002 ___
Count         : 1  (25.00%)
Time          : 3 s total, 3 s avg, 3 s to 3 s max  (27.27%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 178.10k avg, 178.10k to 178.10k max  (25.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max  (25.00%)
Database      :
Users         :
        root@localhost  : 100.00% (1) of query, 75.00% (3) of all users

Query abstract:
SELECT * FROM test_2;

Query sample:
select * from test_2;

釋意:

• queries total 总查询次数
• unique 去除重复后的 sql 数量.
• Sorted by 输出报表的内容排序.
• Grand Totals slow sql统计信息包括: 总执行时间, 等待锁时间, 结果行总数, 扫描行总数.
• Count slow sql 的执行次数及占总的 slow log 数量的百分比.
• Time 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总 slow sql 时间的百分比.
• Lock Time 等待锁的时间.
• Rows sent 结果行统计数量, 包括平均, 最小, 最大数量.
• Rows examined 扫描的行数量.
• Database 属于哪个数据库
• Users username@hostname 占到所有用户执行该句 sql 百分比及占到所有用户执行的所有 SQL 的百分比.
• Query abstract 精简后的sql语句

几个常用OPTION, 其他可查看官网文档

• --log-type=TYPE LOGS or -lt TYPE LOGS TYPE LOGS 可为 slow, general, binary, msl or udl. 官方文档说是没有指定时, 会根据给定的日志文件自动检测, 但测试时失败, so, 最好指定该参数.
  msl 是指 microslow patched 的慢日志.
  udl 用户自定义的日志.
  binary 因为 mysqlsla 不能直接解析 MySQL 的 binary log, 所以需先用 mysqlbinlog命令将其解析为文本,. mysqlbinlog 带有 --short-form参数时, 则 LOG TYPE 需指定为 udl. 命令可类似于如下:

  mysqlbinlog /usr/local/mysql/var/mysql-bin.000001 | mysqlsla -lt binary -
  mysqlbinlog --short-form /usr/local/mysql/var/mysql-bin.000001 | mysqlsla -lt udl -
  

• --explain or -ex explain 每句 SQL, 默认没有启用
• --databases=dbname1,dbnam2[,...] or -db dbname1,dbnam2[,...] or -D dbname1,dbnam2[,...]用于 --explain
• --flush-qc 强制刷新查询缓存, 默认没有启用
• --grep="PATTERN" 只解析满足条件的 SQL
• --meta-filter="CONDTIONS" or -mf "CONDTIONS" CONDITIONS 格式为[meta][op][value],多个条件时, 中间以,分割.
  [meta] 查看这里
  [op] >, < or =. 當 [meta] 是基於字符串時 , [op] 只能是 =.
  [value] 數字 or 字符串.
• --reports=REPORTS or -R REPORTS, 默認是 standard, REPORTS 可以是以,為分割的列表. 可選選項：standard, time-all, print-unique, print-all, dump

NOTE

• MySQL 5.1.21 及以后版本可通过 set [session|global] long_query_time=0.01 等来设置记录执行时间超过 0.01秒 以上的 sql 语句.v5.1.21 之前的版本最小值只能到达 1s, 这时候可用 microslow patch(msl patch ) 补丁来完成这一工作.

Windows 下命令端启动，停止 MySQL 服务.

net start mysql

启动 MySQL 服务.

net stop mysql

停止 MySQL 服务.

昨日, 在 Windows 下做修改某配置的测试，启动服务时，条件反射的使用 mysqld_safe，失败才记起得用如上命令来启动。记录下以备来时又昏头.

删除

PURGE {MASTER | BINARY} LOGS TO 'binlog-name';
删除 binlog-name 编号之前所有的日志.

PURGE {MASTER | BINARY} LOGS BEFORE 'date';
除当前使用日志外, 如果其他日志最后更新时间是小于 date, 则会被删除.

mysql> PURGE MASTER LOGS TO 'mysql-bin.000003';
Query OK, 0 rows affected (0.01 sec)

删除 000003 之前所有的日志(000003 不会被删除).

mysql> PURGE MASTER LOGS BEFORE '2011-03-25 23:59:59';
Query OK, 0 rows affected (0.05 sec)

expire_logs_days
MySQL 可以根據 expire_logs_days 的設置自動清除日誌。該值默認為 0， 即不會自動清除。可能的值為 0-99.
配置文件中設置

[mysqld]
expire_logs_days=1

命令端設置

mysql> SET GLOBAL expire_logs_days=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'expire%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 1     |
+------------------+-------+
1 row in set (0.00 sec)

The number of days for automatic binary log file removal. The default is 0, which means “no automatic removal.” Possible removals happen at startup and when the binary log is flushed. Log flushing occurs as indicated in

清空二进制日志

mysql> RESET MASTER

该命令会删除之前所有的 binlog, 并重新生成新的 binlog, 后缀又会从 000001 开始. 但如果该库为主库, 且有连接从库, 而从库正在读取试图删除的日志之一,
则本语句不会起作用, 而是会失败, 并伴随一个错误. 不过, 如果从库没有连接主库, 而又删除了从库读取的日志, 则从库启动后不能复制. 当从库正在复制时, 本语句可以安全运行, 不需要停止它们.

查看当前二进制日志

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000009 |      180 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

更新二进制

mysql> FLUSH LOGS;

查看二进制日志数目

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       117 |
| mysql-bin.000002 |       117 |
| mysql-bin.000003 |      1181 |
| mysql-bin.000004 |    491718 |
| mysql-bin.000005 |   2543824 |
| mysql-bin.000006 |   8032840 |
+------------------+-----------+
16 rows in set (0.01 sec)

列出所有的日志及其大小

查看 events

mysql> SHOW BINLOG EVENTS;

MySQL 命令终端查看当前日志中记录的日志.

mysqlbinlog

[root@localhost ~]# mysqlbinlog  /usr/local/mysql/var/mysql-bin.000001

查看二进制文件 mysql-bin.000001 中记录的 DML 和 DLL.

mysqlbinlog 有用参数

• -d, --database=dbname 列出查看的数据库
• --start-datetime='2011-03-25 00:00:00' 查看发生在该指定时间之后的 events.
• --stop-datetime='2011-03-25 23:59:59' 查看发生在该指定时间之前的 events.
• --start-position=10 查看该指定偏移点之后的 events.
• --stop-position=100 查看该指定偏移点之前的 events.
• --set-charset=utf8 在输出的 events 之前添加上 “SET NAMES utf8″.

基于时间

[root@localhost ~]# mysqlbinlog --start-datetime='2011-03-25 00:00:0' --stop-datetime='2011-03-25 23:59:59' --database=test  /usr/local/mysql/var/mysql-bin.000001 > /tmp/1.log

> /tmp/1.log 将其重定向到 1.log, 更便于查看.

基于偏移量

[root@localhost ~]# mysqlbinlog --start-position=98 --stop-position=344 --database=test  /usr/local/mysql/var/mysql-bin.000001 > /tmp/1.log

在 MySQL 中有 4 种不同的日志, 分别为错误日志, 查询日志和慢查询日志, 二进制日志. 默认情况下, 为尽量减少 IO 损耗, 系统只打开错误日志. 若需要复制, 就必须要打开二进制日志.

错误日志

错误日志在 MySQL 数据库中很重要, 它记录着 MySQL 启动和停止, 以及服务器在运行过程中发生的任何错误的相关信息.

配置
如果配置文件 my.cnf 没有指定 log_eror, 则错问日志默认文件名为 hostname.err, 存放于 datadir 目录中.

mysql> SHOW VARIABLES LIKE 'log_error';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

将 log-error 配置到 my.cnf 文件中

[mysqld]
log_error=/usr/local/mysql/var/mysql-error.err

查询日志

查询日志记录了所有操作的语句. 由于它记录数据库所有操作, 对于访问频繁的系统, 此种日志会造成性能影响, 所以一般不会将其打开.

配置
如果配置文件 my.cnf 有打开log选项, 但未指定具体文件名和路径, 则其默认文件名为 hostname.log, 存放于 datadir 目录中.

默认时查询日志变量值

mysql> SHOW VARIABLES LIKE 'log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log           | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

如果有需要使用到查询日志, 将 log 配置到 my.cnf 文件中

[mysqld]
log=/usr/local/mysql/var/mysql_query.log

查询日志是纯文本格式, 可使用文本读取工具直接打开查看.

慢查询日志

慢查询日志是记录执行时间超过参数 slow_launch_time(unit: s, v5.1 默认 2s v5.0.* 是 long_query_time)所设定值的 SQL 语句日志. 它有助于发现性能有问题的 SQL.

打开慢日志对系统性能的整体影响没有 binlog 那么大, 但系统需要计算每一条查询的执行时间, 所有, 在 CPU 方面还是有损耗的. 如果 CPU 资源不够, 可在大部分时候关闭这个, 只需间断性的打开来定位可能存在的慢查询.

配置
如果配置文件 my.cnf 有打开log_slow_queries选项, 但未指定具体文件名和路径, 则其默认文件名为 hostname-slow.log, 存放于 datadir 目录中.

默认情况下慢日志查询变量(v5.1)：

mysql> SHOW VARIABLES LIKE '%slow%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| log_slow_queries    | OFF                              |
| slow_launch_time    | 2                                |
| slow_query_log      | OFF                              |
| slow_query_log_file | /usr/local/mysql/var/db-slow.log |
+---------------------+----------------------------------+
4 rows in set (0.00 sec)

如果有需要使用到慢查询日志, 将 log_slow_queries 配置到 my.cnf 文件中

[mysqld]
log_slow_queries=/usr/local/mysql/var/mysql_slow_query.log

也可以在启动 MySQL 服务时, 加上 --log_slow_queries=filename.log

若需要进一步缩短慢查询时间限制, 可使用 Percona 提供的 microslow-patch(msl Patch). 它不仅能将时间减小到毫秒级别, 还能通过一些特定的规则来过滤记录的SQL, 如只记录某个表的慢查询.

查看慢日志

二进制日志

二进制日志记录了所有的 DDL 和 DML 的语句, 但不包括查询语句, 语句以事件方式保存, 此日志对发生灾难时数据恢复极为重要. MySQL 复制时, 必须将其打开.

启用配置

[mysqld]
log-bin=/usr/local/mysql/var/mysql-bin

mysql-bin 为日志文件名，MySQL 在文件名后添加数字索引，所以该文件最后的形式类似于 mysql-bin.000001.如果在指定文件名时类似于 myql-bin.log, .log 会自动忽略. 该日志没有指定相对路径时，默认存放于 datadir 目录中.

如下情况时，二进制日志会更换到新的文件:

• 服务器重启
• 服务器被更新
• 日志达到最大日志长度 max_binlog_size
• 在 MySQL 命令终端 FLUSH LOGS

打开配置时各变量值(v5.1)

mysql> SHOW VARIABLES LIKE '%bin%';
+-----------------------------------------+----------------------+
| Variable_name                           | Value                |
+-----------------------------------------+----------------------+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | STATEMENT            |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| log_bin                                 | ON                   |
| log_bin_trust_function_creators         | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sql_log_bin                             | ON                   |
| sync_binlog                             | 0                    |
+-----------------------------------------+----------------------+
12 rows in set (0.00 sec)

部分变量值说明

• binlog_cache_size 事务过程中容纳二进制日志 SQL 语句的缓存大小. 二进制日志缓存是服务器支持事务存储引擎, 且服务器启用了二进制日志(log_bin)的前提下为每个客户端分配的内存, 注意是给每个客户端可以分配设置大小的 binlog cache 空间. 如果系统中会出现多语句的事务, 增加该值的大小, 以得到更好的性能.
• max_binlog_cache_size 和binlog_cache_size对应, 它代表的是 binlog 能使用的最大 cache 值大小. 当不够大时, 系统可能会报“Multi_statement transaction required more than ‘max_binlog_cache_size’ bytes of storage”.
• max_binlog_size binlog 最大值, 一般设置为 512KB 或 1GB, 但不能超过 1GB.
• sync_binlog影响到 binlog 对 MySQL 所带来的性能消耗, 还影响到数据的完整性, 参数说明如下:
  sync_binlog=0 事务提交后, 仅仅是将 binlog_cache 中的数据写入到 binlog 文件中, 但不执行 fsync 之类的磁盘操作指令通知文件系统将缓存刷新到磁盘, 而让文件系统自行决定什么时候来同步. sync_binlog=N 进行 N 次事务提交后, 系统将执行一次 fsync 之类的磁盘同步指令, 通知文件系统将 binlog 文件的缓存刷新到磁盘. 默认是 sync_binlog=0, 性能是最好, 但是风险是最大, 因为一旦系统 crash, 文件系统缓存中的 binlog 将都会丢失.设置为 1 时, 最安全但性能损耗最大, 当系统 crash 时, 最多只会丢失 binlog_cache 中未完成的一个事务, 对实际数据没有任何的影响.sync_binlog=0 性能 有可能是 sync_binlog=1 时的5倍.

记录内容配置
binlog_do_db=example1,example2 设定那些 db 需要记录binlog
binlog_ignore_db=test1,test2 设定那些 db不要记录 binlog

注:
如果在操作数据库之前，不使用use $dbname , 那么所有的SQL都不会记录, 如果使用了use $dbname,那么判断规则取决于这里的$dbname，而不是 SQL 中操作的库.

如果配置文件中没有配置这两个选项，则该主机上所有库的 DML 和 DLL 语句都会被记录。
删除查看二进制日志