数据库类
Jan
18
一、RPM包下载地址：
http://dev.mysql.com/downloads/repo/yum/
请根据自己的操作系统，选择要下载的rpm包：
Red Hat Enterprise Linux 7/CentOS 7
mysql57-community-release-el7-9.noarch.rpm
Red Hat Enterprise Linux 6/CentOS 6
mysql57-community-release-el6-9.noarch.rpm
Red Hat Enterprise Linux 5/CentOS 5
mysql57-community-release-el5-7.noarch.rpm
以CentOS 6为例：
二、安装：
2.1、选择要安装的MySQL版本：
2.2、安装MySQL，默认安装的版本是5.7，所以不需要做任何的修改。
2.3、安装其他版本，假如你想安装MySQL5.6或者其他版本,需要修改 /etc/yum.repos.d/mysql-community.repo文件。
三、启动MySQL服务
3.1、查看MySQL状态
对于MySQL5.7版本，系统初始化之后会生成root账户的密码，保存在日志文件里，使用grep 'temporary password' /var/log/mysqld.log 查看初始密码，MySQL5.7的密码要求至少包含一个大写字母，一个小写字母，一个数字和一个特殊字符，并且总密码长度至少为8个字符。
3.2、修改密码
3.3、安装其他MySQL产品和组件
官方文档：
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
http://dev.mysql.com/downloads/repo/yum/
请根据自己的操作系统，选择要下载的rpm包：
Red Hat Enterprise Linux 7/CentOS 7
mysql57-community-release-el7-9.noarch.rpm
Red Hat Enterprise Linux 6/CentOS 6
mysql57-community-release-el6-9.noarch.rpm
Red Hat Enterprise Linux 5/CentOS 5
mysql57-community-release-el5-7.noarch.rpm
以CentOS 6为例：
wget https://repo.mysql.com//mysql57-community-release-el6-9.noarch.rpm
二、安装：
2.1、选择要安装的MySQL版本：
2.2、安装MySQL，默认安装的版本是5.7，所以不需要做任何的修改。
2.3、安装其他版本，假如你想安装MySQL5.6或者其他版本,需要修改 /etc/yum.repos.d/mysql-community.repo文件。
三、启动MySQL服务
3.1、查看MySQL状态
对于MySQL5.7版本，系统初始化之后会生成root账户的密码，保存在日志文件里，使用grep 'temporary password' /var/log/mysqld.log 查看初始密码，MySQL5.7的密码要求至少包含一个大写字母，一个小写字母，一个数字和一个特殊字符，并且总密码长度至少为8个字符。
3.2、修改密码
3.3、安装其他MySQL产品和组件
官方文档：
https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
Jul
27
一、今天编译安装了MySQL5.6.31，安装启动之后错误日志报以下Warning信息：
160727 12:52:32 mysqld_safe Starting mysqld daemon with databases from /home/mysql/3307/data
2016-07-27 12:52:33 0 [Note] /usr/local/webserver/mysql56/bin/mysqld (mysqld 5.6.31-log) starting as process 26248 ...
2016-07-27 12:52:33 26248 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-07-27 12:52:33 26248 [Note] InnoDB: The InnoDB memory heap is disabled
2016-07-27 12:52:33 26248 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-07-27 12:52:33 26248 [Note] InnoDB: Memory barrier is not used
2016-07-27 12:52:33 26248 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-07-27 12:52:33 26248 [Note] InnoDB: Using CPU crc32 instructions
2016-07-27 12:52:33 26248 [Note] InnoDB: Initializing buffer pool, size = 512.0M
2016-07-27 12:52:33 26248 [Note] InnoDB: Completed initialization of buffer pool
2016-07-27 12:52:33 26248 [Note] InnoDB: Highest supported file format is Barracuda.
2016-07-27 12:52:33 26248 [Note] InnoDB: 128 rollback segment(s) are active.
2016-07-27 12:52:33 26248 [Note] InnoDB: Waiting for purge to start
2016-07-27 12:52:33 26248 [Note] InnoDB: 5.6.31 started; log sequence number 1601096
2016-07-27 12:52:33 26248 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3307
2016-07-27 12:52:33 26248 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2016-07-27 12:52:33 26248 [Note] Server socket created on IP: '0.0.0.0'.
2016-07-27 12:52:33 26248 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] 'proxies_priv' entry '@ [email protected]' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for th
e table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for th
e table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for
the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2016-07-27 12:52:33 26248 [Note] Event Scheduler: Loaded 0 events
2016-07-27 12:52:33 26248 [Note] /usr/local/webserver/mysql56/bin/mysqld: ready for connections.
BUG产生具体原因不详，官方bugs.mysql.com上有提供修复这5张表的SQL文件，下载链接：http://bugs.mysql.com/file.php?id=19725
下面我们就按照文件的说明操作
1. drop these tables from mysql:
innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info
2. delete all .frm & .ibd of the tables above.
3. run this file to recreate the tables above (source five-tables.sql).
4. restart mysqld.
二、具体操作步骤
2.1、删除这5张表
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
2.2、删除这5张表对应的.frm和.ibd文件
rm -f /home/mysql/3307/data/mysql/*.ibd
2.3、导入下载的five-tables.sql文件
source /home/sql/five-tables.sql
2.4、导入完成之后，重新启动MYSQL服务
三、检验结果：
已经没有报错信息了。
160727 12:52:32 mysqld_safe Starting mysqld daemon with databases from /home/mysql/3307/data
2016-07-27 12:52:33 0 [Note] /usr/local/webserver/mysql56/bin/mysqld (mysqld 5.6.31-log) starting as process 26248 ...
2016-07-27 12:52:33 26248 [Note] InnoDB: Using atomics to ref count buffer pool pages
2016-07-27 12:52:33 26248 [Note] InnoDB: The InnoDB memory heap is disabled
2016-07-27 12:52:33 26248 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-07-27 12:52:33 26248 [Note] InnoDB: Memory barrier is not used
2016-07-27 12:52:33 26248 [Note] InnoDB: Compressed tables use zlib 1.2.3
2016-07-27 12:52:33 26248 [Note] InnoDB: Using CPU crc32 instructions
2016-07-27 12:52:33 26248 [Note] InnoDB: Initializing buffer pool, size = 512.0M
2016-07-27 12:52:33 26248 [Note] InnoDB: Completed initialization of buffer pool
2016-07-27 12:52:33 26248 [Note] InnoDB: Highest supported file format is Barracuda.
2016-07-27 12:52:33 26248 [Note] InnoDB: 128 rollback segment(s) are active.
2016-07-27 12:52:33 26248 [Note] InnoDB: Waiting for purge to start
2016-07-27 12:52:33 26248 [Note] InnoDB: 5.6.31 started; log sequence number 1601096
2016-07-27 12:52:33 26248 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3307
2016-07-27 12:52:33 26248 [Note] - '0.0.0.0' resolves to '0.0.0.0';
2016-07-27 12:52:33 26248 [Note] Server socket created on IP: '0.0.0.0'.
2016-07-27 12:52:33 26248 [Warning] 'user' entry '[email protected]' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] 'user' entry '@localhost.localdomain' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] 'proxies_priv' entry '@ [email protected]' ignored in --skip-name-resolve mode.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for th
e table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for th
e table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for
the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem.
2016-07-27 12:52:33 26248 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
2016-07-27 12:52:33 26248 [Note] Event Scheduler: Loaded 0 events
2016-07-27 12:52:33 26248 [Note] /usr/local/webserver/mysql56/bin/mysqld: ready for connections.
BUG产生具体原因不详，官方bugs.mysql.com上有提供修复这5张表的SQL文件，下载链接：http://bugs.mysql.com/file.php?id=19725
下面我们就按照文件的说明操作
1. drop these tables from mysql:
innodb_index_stats
innodb_table_stats
slave_master_info
slave_relay_log_info
slave_worker_info
2. delete all .frm & .ibd of the tables above.
3. run this file to recreate the tables above (source five-tables.sql).
4. restart mysqld.
二、具体操作步骤
2.1、删除这5张表
drop table if exists innodb_index_stats;
drop table if exists innodb_table_stats;
drop table if exists slave_master_info;
drop table if exists slave_relay_log_info;
drop table if exists slave_worker_info;
2.2、删除这5张表对应的.frm和.ibd文件
rm -f /home/mysql/3307/data/mysql/*.ibd
2.3、导入下载的five-tables.sql文件
source /home/sql/five-tables.sql
2.4、导入完成之后，重新启动MYSQL服务
三、检验结果：
已经没有报错信息了。
Mar
13
1、
mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表
2、
通过连接它并执行这个命令，可以看到实际上使用的变量的值：
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标：
mysql>SHOW STATUS；
使用mysqladmin还可以获得系统变量和状态信息：
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table 命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭，这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。
swap -s检查可用交换区
mysql内存计算公式
mysql used mem = key_buffer_size + query_cache_size + tmp_table_size
+ innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections * (
read_buffer_size + read_rnd_buffer_size
+ sort_buffer_size+ join_buffer_size
+ binlog_cache_size + thread_stack
)
在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
mysql关键参数设置
Mysqld 数据库的参数设置有两种类型，
一种是全局参数，影响服务器的全局操作；
另一种是会话级参数，只影响当前的客户端连接的相关操作。
服务器启动时，所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行 SET GLOBAL var_name 语句可以更改动态全局参数。要想更改全局参数，必须具有 SUPER 权限。全局参数的修改只对新的连接生效，已有的客户端连接并不会生效。
服务器还可以为每个客户端连接维护会话级参数，客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过 SET SESSION var_name 语句来更改动态会话参数。设置会话级参数不需要特殊权限，但每个客户端可以只更改自己的会话级参数，不能更改其它客户的会话级参数。
不指定设置的参数类型时，默认设置的是会话级参数。
(1)、max_connections：
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。 默认数值是100，我把它改为1024 。
(2)、record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120 (16m)
(3)、key_buffer_size：
为了最小化磁盘的 I/O ， MyISAM 存储引擎的表使用键高速缓存来缓存索引，这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主，则应该适当增加该参数的值，以便尽可能的缓存索引，提高访问的速度。
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为 402649088(400mb)。
默认情况下，所有的索引都使用相同的键高速缓存，当访问的索引不在缓存中时，使用 LRU （ Least Recently Used 最近最少使用）算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用，从 MySQL5.1 开始，可以设置多个键高速缓存，并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值，如何设置多个键高速缓存，以及如何为不同的索引指定不同的缓存：
显示当前的参数大小，为16M：
mysql> show variables like 'key_buffer_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
修改参数值到200M：
mysql> set global key_buffer_size=204800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
上面介绍的是默认的键缓存，下面介绍如何设置多个键缓存：
设置 hot_cache 的键缓存 100M ， cold_cache 的键缓存 100M ，另外还有 200M 的默认的键缓存。如果索引不指定键缓存，则会放在默认的键缓存中。
mysql> set global hot_cache.key_buffer_size=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> set global cold_cache.key_buffer_size= 1024 00;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
如果要显示设置的多键缓存的值，可以使用：
mysql> SELECT @@global.hot_cache.key_buffer_size;
+------------------------------------+
| @@global.hot_cache.key_buffer_size |
+------------------------------------+
| 102400 |
+------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT @@global.cold_cache.key_buffer_size;
+-------------------------------------+
| @@global.cold_cache.key_buffer_size |
+-------------------------------------+
| 102400 |
+-------------------------------------+
1 row in set (0.00 sec)
指定不同的索引使用不同的键缓存：
mysql> CACHE INDEX test1 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test1 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
mysql> CACHE INDEX test2 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test2 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
通常在数据库刚刚启动的时候，需要等待数据库热起来，也就是等待数据被缓存到缓存区中，这段时间数据库会因为 buffer 的命中率低而导致应用的访问效率不高。使用键高速缓存的时候，可以通过命令将索引预加载到缓存区中，大大缩短了数据库预热的时间。具体的操作方式是：
mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;
+------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------+----------+----------+
| test .test1 | preload_keys | status | OK |
| test .test2 | preload_keys | status | OK |
+------------+--------------+----------+----------+
2 rows in set (3.89 sec)
如果已经使用 CACHE INDEX 语句为索引分配了一个键高速缓冲，预加载可以将索引块放入该缓存，否则，索引块将被加载到默认的键高速缓冲。
4)、back_log：
要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
(5)、interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800，我把它改为7200。
(6)、sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m)，我把它改为 16777208 (16m)。
(7)、table_cache：
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
(8)、thread_cache_size：
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(9)mysql的搜索功能
用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312
(10)、wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。
(11)、innodb_thread_concurrency：
你的服务器CPU有几个就设置为几,默认为8。
(12)、query_cache_size 与 query_cache_limit
QueryCache 之后所带来的负面影响：
a) Query 语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache 之后，每条SELECT
类型的Query 在到达MySQL 之后，都需要进行一个hash 运算然后查找是否存在该Query 的
Cache，虽然这个hash 运算的算法可能已经非常高效了，hash 查找的过程也已经足够的优化
了，对于一条Query 来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千
条Query 的时候，我们就不能对产生的CPU 的消耗完全忽视了。
b) Query Cache 的失效问题。如果我们的表变更比较频繁，则会造成Query Cache 的失效率非常
高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我
们每次缓存到Query Cache 中的Cache 数据可能在刚存入后很快就会因为表中的数据被改变而被
清除，然后新的相同Query 进来之后无法使用到之前的Cache。
c) Query Cache 中缓存的是Result Set ，而不是数据页，也就是说，存在同一条记录被Cache 多
次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定Query
Cache 的大小啊。是的，我们确实可以限定Query Cache 的大小，但是这样，Query Cache 就很
容易造成因为内存不足而被换出，造成命中率的下降。
QueryCache 的正确使用：
虽然Query Cache 的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我
们完全不用因为Query Cache 的上面三个负面影响就完全失去对Query Cache 的信心。只要我们理解了
Query Cache 的实现原理，那么我们就完全可以通过一定的手段在使用Query Cache 的时候扬长避短，重
发发挥其优势，并有效的避开其劣势。
首先，我们需要根据Query Cache 失效机制来判断哪些表适合使用Query 哪些表不适合。由于Query
Cache 的失效主要是因为Query 所依赖的Table 的数据发生了变化，造成Query 的Result Set 可能已经
有所改变而造成相关的Query Cache 全部失效，那么我们就应该避免在查询变化频繁的Table 的Query 上
使用，而应该在那些查询变化频率较小的Table 的Query 上面使用。MySQL 中针对Query Cache 有两个专
用的SQL Hint（提示）：SQL_NO_CACHE 和SQL_CACHE，分别代表强制不使用Query Cache 和强制使用
Query Cache。我们完全可以利用这两个SQL Hint，让MySQL 知道我们希望哪些SQL 使用Query Cache 而
哪些SQL 就不要使用了。这样不仅可以让变化频繁Table 的Query 浪费Query Cache 的内存，同时还可以
减少Query Cache 的检测量。
其次，对于那些变化非常小，大部分时候都是静态的数据，我们可以添加SQL_CACHE 的SQL Hint，
强制MySQL 使用Query Cache，从而提高该表的查询性能。
最后，有些SQL 的Result Set 很大，如果使用Query Cache 很容易造成Cache 内存的不足，或者将
之前一些老的Cache 冲刷出去。对于这一类Query 我们有两种方法可以解决，一是使用SQL_NO_CACHE 参
数来强制他不使用Query Cache 而每次都直接从实际数据中去查找， 另一种方法是通过设定
“query_cache_limit”参数值来控制Query Cache 中所Cache 的最大Result Set ，系统默认为
1M（1048576）。当某个Query 的Result Set 大于“query_cache_limit”所设定的值的时候，Query
Cache 是不会Cache 这个Query 的。
(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的 60%-80%
InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。
这样，12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占用到14.5G(12G X 21%)的内存，再加上操作系统用的几百M，近千个线程堆栈，就差不多16G了。
MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
（db用户在dbname的数据库上控制用户每小时打开新连接的数量为5个）
MAX_USER_CONNECTIONS 限制有多少用户连接MYSQL服务器：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
（db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个）
MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
（db用户在dbname的数据库上控制用户每小时修改更新数据库的次数为5次）
MAX_USER_CONNECTIONS 用来限制用户每小时的修改数据库数据的数量：
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql单个用户的最大连接数
（db用户在dbname的数据库上控制用户每小时的连接数为20个）
调优举例
针对my.cnf文件进行优化：
[mysqld]
skip-locking（取消文件系统的外部锁）
skip-name-resolve（不进行域名反解析,注意由此带来的权限/授权问题）
key_buffer_size = 256M（分配给MyISAM索引缓存的内存总数）对于内存在4GB左右的服务器该参数可设置为256M或384M。
注意：该参数值设置的过大反而会是服务器整体效率降低！
max_allowed_packet = 4M（允许最大的包大小）
thread_stack = 256K（每个线程的大小）
table_cache = 128K（缓存可重用的线程数）
back_log = 384（临时停止响应新请求前在短时间内可以堆起多少请求，如果你需要在短时间内允许大量连接，可以增加该数值）
sort_buffer_size = 2M(分配给每个线程中处理排序)
read_buffer_size = 2M（读取的索引缓冲区大小）
join_buffer_size = 2M（分配给每个线程中处理扫描表连接及索引的内存）
myisam_sort_buffer_size = 64M（myisam引擎排序缓冲区的大小）
table_cache = 512（缓存数据表的数量，避免重复打开表的开销）
thread_cache_size = 64（缓存可重用线程数，见笑创建新线程的开销）
query_cache_size = 64M（控制分配给查询缓存的内存总量）
tmp_table_size = 256M(指定mysql缓存的内存大小)
max_connections = 768（最大连接数）指mysql整个的最大连接数
max_connect_errors = 10000(最大连接错误数据)
wait_timeout = 10（超时时间，可以避免攻击）
thread_concurrency = 8（根据cpu数量来设置）
skip-bdb 禁用不必要的引擎
skip-networking（关闭mysql tcp/ip连接方式）
Log-slow-queries = /var/log/mysqlslowqueries.log
long_query_time = 4（设定慢查询的时间）
skip-host-cache(提高mysql速度的)
open_files_limit = 4096(打开文件数）
interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
max_user_connections = 500（最大用户连接数）
key_buffer_size 默认为218 调到128最佳
query_cache_size
tmp_table_size 默认为16M 调到64-256最挂
innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8
table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=8M 默认为2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=4M 默认为1M
read_buffer_size=4M 默认为64K
read_rnd_buffer_size 随机读 缓存区 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210
thread_cache_size=120 默认为60
性能测试
1、mysql 自带测试工具
shell> perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
server_name是一个支持的服务器。要获得所有选项和支持的服务器，调用命令：
shell> perl run-all-tests --help
2、mysqlreport
http://hackmysql.com/mysqlreport
参考文档：
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
http://hackmysql.com/tools
http://www.imysql.cn/
mysqld --verbose --help
这个命令生成所有mysqld选项和可配置变量的列表
2、
通过连接它并执行这个命令，可以看到实际上使用的变量的值：
mysql> SHOW VARIABLES;
还可以通过下面的语句看到运行服务器的统计和状态指标：
mysql>SHOW STATUS；
使用mysqladmin还可以获得系统变量和状态信息：
shell> mysqladmin variables
shell> mysqladmin extended-status
shell> mysqladmin flush-table 命令可以立即关闭所有不使用的表并将所有使用中的表标记为已经关闭，这样可以有效释放大多数使用中的内存。FLUSH TABLE在关闭所有表之前不返回结果。
swap -s检查可用交换区
mysql内存计算公式
mysql used mem = key_buffer_size + query_cache_size + tmp_table_size
+ innodb_buffer_pool_size + innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections * (
read_buffer_size + read_rnd_buffer_size
+ sort_buffer_size+ join_buffer_size
+ binlog_cache_size + thread_stack
)
在mysql 中输入如下命令，可自动计算自己的当前配置最大的内存消耗
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_additional_mem_pool_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'thread_stack';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = 16 * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
( @@key_buffer_size + @@query_cache_size + @@tmp_table_size
+ @innodb_buffer_pool_size + @innodb_additional_mem_pool_size
+ @innodb_log_buffer_size
+ @@max_connections * (
@@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @thread_stack
) ) / @giga_bytes AS MAX_MEMORY_GB;
mysql关键参数设置
Mysqld 数据库的参数设置有两种类型，
一种是全局参数，影响服务器的全局操作；
另一种是会话级参数，只影响当前的客户端连接的相关操作。
服务器启动时，所有全局参数都初始化为默认值。可以在初始化文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行 SET GLOBAL var_name 语句可以更改动态全局参数。要想更改全局参数，必须具有 SUPER 权限。全局参数的修改只对新的连接生效，已有的客户端连接并不会生效。
服务器还可以为每个客户端连接维护会话级参数，客户端连接时使用相应全局参数的当前值对客户端会话参数进行初始化。客户可以通过 SET SESSION var_name 语句来更改动态会话参数。设置会话级参数不需要特殊权限，但每个客户端可以只更改自己的会话级参数，不能更改其它客户的会话级参数。
不指定设置的参数类型时，默认设置的是会话级参数。
(1)、max_connections：
允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 too many connections 错误。 默认数值是100，我把它改为1024 。
(2)、record_buffer：
每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128k)，我把它改为16773120 (16m)
(3)、key_buffer_size：
为了最小化磁盘的 I/O ， MyISAM 存储引擎的表使用键高速缓存来缓存索引，这个键高速缓存的大小则通过 key-buffer-size 参数来设置。如果应用系统中使用的表以 MyISAM 存储引擎为主，则应该适当增加该参数的值，以便尽可能的缓存索引，提高访问的速度。
索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8m)，我的mysql主机有2gb内存，所以我把它改为 402649088(400mb)。
默认情况下，所有的索引都使用相同的键高速缓存，当访问的索引不在缓存中时，使用 LRU （ Least Recently Used 最近最少使用）算法来替换缓存中最近最少使用的索引块。为了进一步避免对键高速缓存的争用，从 MySQL5.1 开始，可以设置多个键高速缓存，并为不同的索引键指定使用的键高速缓存。下面的例子演示如何修改高速键缓存的值，如何设置多个键高速缓存，以及如何为不同的索引指定不同的缓存：
显示当前的参数大小，为16M：
mysql> show variables like 'key_buffer_size';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| key_buffer_size | 16384 |
+-----------------+-------+
1 row in set (0.00 sec)
修改参数值到200M：
mysql> set global key_buffer_size=204800;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
上面介绍的是默认的键缓存，下面介绍如何设置多个键缓存：
设置 hot_cache 的键缓存 100M ， cold_cache 的键缓存 100M ，另外还有 200M 的默认的键缓存。如果索引不指定键缓存，则会放在默认的键缓存中。
mysql> set global hot_cache.key_buffer_size=102400;
Query OK, 0 rows affected (0.00 sec)
mysql> set global cold_cache.key_buffer_size= 1024 00;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'key_buffer_size';
+-----------------+--------+
| Variable_name | Value |
+-----------------+--------+
| key_buffer_size | 204800 |
+-----------------+--------+
1 row in set (0.00 sec)
如果要显示设置的多键缓存的值，可以使用：
mysql> SELECT @@global.hot_cache.key_buffer_size;
+------------------------------------+
| @@global.hot_cache.key_buffer_size |
+------------------------------------+
| 102400 |
+------------------------------------+
1 row in set (0.03 sec)
mysql> SELECT @@global.cold_cache.key_buffer_size;
+-------------------------------------+
| @@global.cold_cache.key_buffer_size |
+-------------------------------------+
| 102400 |
+-------------------------------------+
1 row in set (0.00 sec)
指定不同的索引使用不同的键缓存：
mysql> CACHE INDEX test1 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test1 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
mysql> CACHE INDEX test2 in hot_cache;
+------------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------------+----------+----------+
| test .test2 | assign_to_keycache | status | OK |
+------------+--------------------+----------+----------+
1 row in set (0.00 sec)
通常在数据库刚刚启动的时候，需要等待数据库热起来，也就是等待数据被缓存到缓存区中，这段时间数据库会因为 buffer 的命中率低而导致应用的访问效率不高。使用键高速缓存的时候，可以通过命令将索引预加载到缓存区中，大大缩短了数据库预热的时间。具体的操作方式是：
mysql> LOAD INDEX INTO CACHE test1,test2 IGNORE LEAVES;
+------------+--------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------+--------------+----------+----------+
| test .test1 | preload_keys | status | OK |
| test .test2 | preload_keys | status | OK |
+------------+--------------+----------+----------+
2 rows in set (3.89 sec)
如果已经使用 CACHE INDEX 语句为索引分配了一个键高速缓冲，预加载可以将索引块放入该缓存，否则，索引块将被加载到默认的键高速缓冲。
4)、back_log：
要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。
back_log 值指出在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的tcp/ip连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。
当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | null | connect | null | login | null 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。
(5)、interactive_timeout：
服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 client_interactive 选项的客户。 默认数值是28800，我把它改为7200。
(6)、sort_buffer：
每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速order by或group by操作。默认数值是2097144(2m)，我把它改为 16777208 (16m)。
(7)、table_cache：
为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。mysql对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。
(8)、thread_cache_size：
可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 connections 和 threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。
(9)mysql的搜索功能
用mysql进行搜索，目的是能不分大小写，又能用中文进行搜索
只需起动mysqld时指定 --default-character-set=gb2312
(10)、wait_timeout：
服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。
(11)、innodb_thread_concurrency：
你的服务器CPU有几个就设置为几,默认为8。
(12)、query_cache_size 与 query_cache_limit
QueryCache 之后所带来的负面影响：
a) Query 语句的hash 运算以及hash 查找资源消耗。当我们使用Query Cache 之后，每条SELECT
类型的Query 在到达MySQL 之后，都需要进行一个hash 运算然后查找是否存在该Query 的
Cache，虽然这个hash 运算的算法可能已经非常高效了，hash 查找的过程也已经足够的优化
了，对于一条Query 来说消耗的资源确实是非常非常的少，但是当我们每秒都有上千甚至几千
条Query 的时候，我们就不能对产生的CPU 的消耗完全忽视了。
b) Query Cache 的失效问题。如果我们的表变更比较频繁，则会造成Query Cache 的失效率非常
高。这里的表变更不仅仅指表中数据的变更，还包括结构或者索引等的任何变更。也就是说我
们每次缓存到Query Cache 中的Cache 数据可能在刚存入后很快就会因为表中的数据被改变而被
清除，然后新的相同Query 进来之后无法使用到之前的Cache。
c) Query Cache 中缓存的是Result Set ，而不是数据页，也就是说，存在同一条记录被Cache 多
次的可能性存在。从而造成内存资源的过渡消耗。当然，可能有人会说我们可以限定Query
Cache 的大小啊。是的，我们确实可以限定Query Cache 的大小，但是这样，Query Cache 就很
容易造成因为内存不足而被换出，造成命中率的下降。
QueryCache 的正确使用：
虽然Query Cache 的使用会存在一些负面影响，但是我们也应该相信其存在是必定有一定价值。我
们完全不用因为Query Cache 的上面三个负面影响就完全失去对Query Cache 的信心。只要我们理解了
Query Cache 的实现原理，那么我们就完全可以通过一定的手段在使用Query Cache 的时候扬长避短，重
发发挥其优势，并有效的避开其劣势。
首先，我们需要根据Query Cache 失效机制来判断哪些表适合使用Query 哪些表不适合。由于Query
Cache 的失效主要是因为Query 所依赖的Table 的数据发生了变化，造成Query 的Result Set 可能已经
有所改变而造成相关的Query Cache 全部失效，那么我们就应该避免在查询变化频繁的Table 的Query 上
使用，而应该在那些查询变化频率较小的Table 的Query 上面使用。MySQL 中针对Query Cache 有两个专
用的SQL Hint（提示）：SQL_NO_CACHE 和SQL_CACHE，分别代表强制不使用Query Cache 和强制使用
Query Cache。我们完全可以利用这两个SQL Hint，让MySQL 知道我们希望哪些SQL 使用Query Cache 而
哪些SQL 就不要使用了。这样不仅可以让变化频繁Table 的Query 浪费Query Cache 的内存，同时还可以
减少Query Cache 的检测量。
其次，对于那些变化非常小，大部分时候都是静态的数据，我们可以添加SQL_CACHE 的SQL Hint，
强制MySQL 使用Query Cache，从而提高该表的查询性能。
最后，有些SQL 的Result Set 很大，如果使用Query Cache 很容易造成Cache 内存的不足，或者将
之前一些老的Cache 冲刷出去。对于这一类Query 我们有两种方法可以解决，一是使用SQL_NO_CACHE 参
数来强制他不使用Query Cache 而每次都直接从实际数据中去查找， 另一种方法是通过设定
“query_cache_limit”参数值来控制Query Cache 中所Cache 的最大Result Set ，系统默认为
1M（1048576）。当某个Query 的Result Set 大于“query_cache_limit”所设定的值的时候，Query
Cache 是不会Cache 这个Query 的。
(13)、innodb_buffer_pool_size
innodb_buffer_pool_size 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而 innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。在一个以 InnoDB 为主的专用数据库服务器上，可以考虑把该参数设置为物理内存大小的 60%-80%
InnoDB占用的内存，除innodb_buffer_pool_size用于存储页面缓存数据外，另外正常情况下还有大约8%的开销，主要用在每个缓存页帧的描述、adaptive hash等数据结构，如果不是安全关闭，启动时还要恢复的话，还要另开大约12%的内存用于恢复，两者相加就有差不多21%的开销。
这样，12G的innodb_buffer_pool_size，最多的时候InnoDB就可能占用到14.5G(12G X 21%)的内存，再加上操作系统用的几百M，近千个线程堆栈，就差不多16G了。
MAX_QUERIES_PER_HOUR 用来限制用户每小时运行的查询数量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_connections_per_hour 5;
（db用户在dbname的数据库上控制用户每小时打开新连接的数量为5个）
MAX_USER_CONNECTIONS 限制有多少用户连接MYSQL服务器：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_user_connections 2;
（db用户在dbname的数据库账户一次可以同时连接的最大连接数为2个）
MAX_UPDATES_PER_HOUR 用来限制用户每小时的修改数据库数据的数量：
mysql> grant all on dbname。* to db@localhost identified by “123456” with max_updates_per_hour 5;
（db用户在dbname的数据库上控制用户每小时修改更新数据库的次数为5次）
MAX_USER_CONNECTIONS 用来限制用户每小时的修改数据库数据的数量：
mysql> grant all on dbname。* to db@localhost identified by “123456”
With MAX_QUERIES_PER_HOUR 20 ;指mysql单个用户的最大连接数
（db用户在dbname的数据库上控制用户每小时的连接数为20个）
调优举例
针对my.cnf文件进行优化：
[mysqld]
skip-locking（取消文件系统的外部锁）
skip-name-resolve（不进行域名反解析,注意由此带来的权限/授权问题）
key_buffer_size = 256M（分配给MyISAM索引缓存的内存总数）对于内存在4GB左右的服务器该参数可设置为256M或384M。
注意：该参数值设置的过大反而会是服务器整体效率降低！
max_allowed_packet = 4M（允许最大的包大小）
thread_stack = 256K（每个线程的大小）
table_cache = 128K（缓存可重用的线程数）
back_log = 384（临时停止响应新请求前在短时间内可以堆起多少请求，如果你需要在短时间内允许大量连接，可以增加该数值）
sort_buffer_size = 2M(分配给每个线程中处理排序)
read_buffer_size = 2M（读取的索引缓冲区大小）
join_buffer_size = 2M（分配给每个线程中处理扫描表连接及索引的内存）
myisam_sort_buffer_size = 64M（myisam引擎排序缓冲区的大小）
table_cache = 512（缓存数据表的数量，避免重复打开表的开销）
thread_cache_size = 64（缓存可重用线程数，见笑创建新线程的开销）
query_cache_size = 64M（控制分配给查询缓存的内存总量）
tmp_table_size = 256M(指定mysql缓存的内存大小)
max_connections = 768（最大连接数）指mysql整个的最大连接数
max_connect_errors = 10000(最大连接错误数据)
wait_timeout = 10（超时时间，可以避免攻击）
thread_concurrency = 8（根据cpu数量来设置）
skip-bdb 禁用不必要的引擎
skip-networking（关闭mysql tcp/ip连接方式）
Log-slow-queries = /var/log/mysqlslowqueries.log
long_query_time = 4（设定慢查询的时间）
skip-host-cache(提高mysql速度的)
open_files_limit = 4096(打开文件数）
interactive_timeout = 10(服务器在关闭它前在一个交互连接上等待行动的秒数)
max_user_connections = 500（最大用户连接数）
key_buffer_size 默认为218 调到128最佳
query_cache_size
tmp_table_size 默认为16M 调到64-256最挂
innodb_thread_concurrency=8 你的服务器CPU有几个就设置为几,默认为8
table_cache=1024 物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=8M 默认为2M
innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=4M 默认为1M
read_buffer_size=4M 默认为64K
read_rnd_buffer_size 随机读 缓存区 默认为256K
sort_buffer_size=32M 默认为256K
max_connections=1024 默认为1210
thread_cache_size=120 默认为60
性能测试
1、mysql 自带测试工具
shell> perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
shell> cd sql-bench
shell> perl run-all-tests --server=server_name
server_name是一个支持的服务器。要获得所有选项和支持的服务器，调用命令：
shell> perl run-all-tests --help
2、mysqlreport
http://hackmysql.com/mysqlreport
参考文档：
http://dev.mysql.com/doc/refman/5.1/zh/optimization.html
http://hackmysql.com/tools
http://www.imysql.cn/
Jan
25
事情的起因是本月某日程序员一时操作失误删除了某表id大于或等于216内容，需要恢复到当天的数据，以免影响用户使用，这种恢复也很简单，通过mysql的二进制文件就可以，前提是你的mysql配置已经开启了二进制日志功能。
开始提取二进制日志，首先把某库22日00时开始的SQL语句提取出来，首先提取binlog.002895的某库SQL语句，mysql的二进制日志不是按天创建的，在my.cnf的配置文件有设置max_binlog_size最大值，日志的大小达到你设置的值时，mysql才会创建一个新的日志文件，如果大小达到设置的值了，最后的执行语句很长时会等到语句执行完毕，才会新建一个日志文件。
-rw-rw---- 1 mysql mysql 1073764199 Jan 20 09:55 binlog.002892
-rw-rw---- 1 mysql mysql 1073769146 Jan 20 20:43 binlog.002893
-rw-rw---- 1 mysql mysql 1073760451 Jan 21 10:39 binlog.002894
-rw-rw---- 1 mysql mysql 1073745436 Jan 21 17:31 binlog.002895
-rw-rw---- 1 mysql mysql 1073742546 Jan 22 05:28 binlog.002896
-rw-rw---- 1 mysql mysql 1073758697 Jan 22 13:44 binlog.002897
-rw-rw---- 1 mysql mysql 1073742021 Jan 22 21:27 binlog.002898
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb --start-datetime='2013-01-22 00:00:00' /home/mysql/binlog/binlog.002895 > log2895.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002896 > log2896.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002897 > log2897.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002898 > log2898.txt
现在已经把那个时间段的某库（这里使用testdb）的sql语句全部提取出来了，我们现在只需要恢复被误删的那个库那段时间的数据，并不需要恢复整个数据库，所以接下来进行第二次提取sql内容。
#grep userlist log2895.txt> log2895-userlist.txt
#grep userlist log2896.txt> log2896-userlist.txt
#grep userlist log2897.txt> log2897-userlist.txt
#grep userlist log2898.txt> log2898-userlist.txt
现在提取出来的文件就只有对userlist表操作的SQL语句了，这里有两个需要注意的地方，一个是你需要把最后一个log2898-userlist.txt文件中程序员误操作的那个删除的SQL语句删除掉，一个是提取出来的SQL语句是每行一条，行尾没有“;”号，要在每个行SQL语句行尾加“;”使用vi编辑器打开SQL文件:%s/$/;即可在每行行尾加上“;”符号。
登陆MYSQL命令行，执行
mysql > source /home/dbbak/log2895-userlist.txt;
mysql > source /home/dbbak/log2896-userlist.txt;
mysql > source /home/dbbak/log2897-userlist.txt;
mysql > source /home/dbbak/log2898-userlist.txt;
执行完毕，该表的数据就恢复到当天误删除之前的状态了。
开始提取二进制日志，首先把某库22日00时开始的SQL语句提取出来，首先提取binlog.002895的某库SQL语句，mysql的二进制日志不是按天创建的，在my.cnf的配置文件有设置max_binlog_size最大值，日志的大小达到你设置的值时，mysql才会创建一个新的日志文件，如果大小达到设置的值了，最后的执行语句很长时会等到语句执行完毕，才会新建一个日志文件。
-rw-rw---- 1 mysql mysql 1073764199 Jan 20 09:55 binlog.002892
-rw-rw---- 1 mysql mysql 1073769146 Jan 20 20:43 binlog.002893
-rw-rw---- 1 mysql mysql 1073760451 Jan 21 10:39 binlog.002894
-rw-rw---- 1 mysql mysql 1073745436 Jan 21 17:31 binlog.002895
-rw-rw---- 1 mysql mysql 1073742546 Jan 22 05:28 binlog.002896
-rw-rw---- 1 mysql mysql 1073758697 Jan 22 13:44 binlog.002897
-rw-rw---- 1 mysql mysql 1073742021 Jan 22 21:27 binlog.002898
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb --start-datetime='2013-01-22 00:00:00' /home/mysql/binlog/binlog.002895 > log2895.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002896 > log2896.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002897 > log2897.txt
#/usr/local/webserver/mysql/bin/mysqlbinlog --no-defaults --database=testdb /home/mysql/binlog/binlog.002898 > log2898.txt
现在已经把那个时间段的某库（这里使用testdb）的sql语句全部提取出来了，我们现在只需要恢复被误删的那个库那段时间的数据，并不需要恢复整个数据库，所以接下来进行第二次提取sql内容。
#grep userlist log2895.txt> log2895-userlist.txt
#grep userlist log2896.txt> log2896-userlist.txt
#grep userlist log2897.txt> log2897-userlist.txt
#grep userlist log2898.txt> log2898-userlist.txt
现在提取出来的文件就只有对userlist表操作的SQL语句了，这里有两个需要注意的地方，一个是你需要把最后一个log2898-userlist.txt文件中程序员误操作的那个删除的SQL语句删除掉，一个是提取出来的SQL语句是每行一条，行尾没有“;”号，要在每个行SQL语句行尾加“;”使用vi编辑器打开SQL文件:%s/$/;即可在每行行尾加上“;”符号。
登陆MYSQL命令行，执行
mysql > source /home/dbbak/log2895-userlist.txt;
mysql > source /home/dbbak/log2896-userlist.txt;
mysql > source /home/dbbak/log2897-userlist.txt;
mysql > source /home/dbbak/log2898-userlist.txt;
执行完毕，该表的数据就恢复到当天误删除之前的状态了。
Jul
5
因为最近遇到一些超时的问题，正好就把所有的timeout参数都理一遍，首先数据库里查一下看有哪些超时：
root@localhost : test 12:55:50> SHOW global VARIABLES LIKE “%timeout%”;
+—————————-+——–+
| Variable_name | VALUE |
+—————————-+——–+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | ON |
| interactive_timeout | 172800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 | # 这个参数已经没用了
| wait_timeout | 172800 |
+—————————-+——–+
我们一个个来看
connect_timeout
手册描述:
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.
解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
delayed_insert_timeout
手册描述：
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解释：这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间。
innodb_lock_wait_timeout
手册描述：
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:
ERROR 1205 (HY000): LOCK wait timeout exceeded; try restarting TRANSACTION
When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)
innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.
For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.
解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。
innodb_rollback_on_timeout
手册描述：
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。
interactive_timeout/wait_timeout
手册描述：
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also
解释：一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态，执行完Query后成为interactive状态，重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程，意义是一样的。
net_read_timeout / net_write_timeout
手册描述：
The number of seconds to wait for more data from a connection before aborting the read. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.
解释：这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数
slave_net_timeout
手册描述：
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or –master-connect-retry option, and the number of reconnection attempts is limited by the –master-retry-count option. The default is 3600 seconds (one hour).
解释：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了
root@localhost : test 12:55:50> SHOW global VARIABLES LIKE “%timeout%”;
+—————————-+——–+
| Variable_name | VALUE |
+—————————-+——–+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 120 |
| innodb_rollback_on_timeout | ON |
| interactive_timeout | 172800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 | # 这个参数已经没用了
| wait_timeout | 172800 |
+—————————-+——–+
我们一个个来看
connect_timeout
手册描述:
The number of seconds that the mysqld server waits for a connect packet before responding with Bad handshake. The default value is 10 seconds as of MySQL 5.1.23 and 5 seconds before that.
Increasing the connect_timeout value might help if clients frequently encounter errors of the form Lost connection to MySQL server at ‘XXX’, system error: errno.
解释：在获取链接时，等待握手的超时时间，只在登录时有效，登录成功这个参数就不管事了。主要是为了防止网络不佳时应用重连导致连接数涨太快，一般默认即可。
delayed_insert_timeout
手册描述：
How many seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating.
解释：这是为MyISAM INSERT DELAY设计的超时参数，在INSERT DELAY中止前等待INSERT语句的时间。
innodb_lock_wait_timeout
手册描述：
The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by another InnoDB transaction will hang for at most this many seconds before issuing the following error:
ERROR 1205 (HY000): LOCK wait timeout exceeded; try restarting TRANSACTION
When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (To have the entire transaction roll back, start the server with the –innodb_rollback_on_timeout option, available as of MySQL 5.1.15. See also Section 13.6.12, “InnoDB Error Handling”.)
innodb_lock_wait_timeout applies to InnoDB row locks only. A MySQL table lock does not happen inside InnoDB and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in its own lock table immediately and rolls back one transaction. The lock wait timeout value does not apply to such a wait.
For the built-in InnoDB, this variable can be set only at server startup. For InnoDB Plugin, it can be set at startup or changed at runtime, and has both global and session values.
解释：描述很长，简而言之，就是事务遇到锁等待时的Query超时时间。跟死锁不一样，InnoDB一旦检测到死锁立刻就会回滚代价小的那个事务，锁等待是没有死锁的情况下一个事务持有另一个事务需要的锁资源，被回滚的肯定是请求锁的那个Query。
innodb_rollback_on_timeout
手册描述：
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解释：这个参数关闭或不存在的话遇到超时只回滚事务最后一个Query，打开的话事务遇到超时就回滚整个事务。
interactive_timeout/wait_timeout
手册描述：
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also
解释：一个持续SLEEP状态的线程多久被关闭。线程每次被使用都会被唤醒为acrivity状态，执行完Query后成为interactive状态，重新开始计时。wait_timeout不同在于只作用于TCP/IP和Socket链接的线程，意义是一样的。
net_read_timeout / net_write_timeout
手册描述：
The number of seconds to wait for more data from a connection before aborting the read. Before MySQL 5.1.41, this timeout applies only to TCP/IP connections, not to connections made through Unix socket files, named pipes, or shared memory. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
On Linux, the NO_ALARM build flag affects timeout behavior as indicated in the description of the net_retry_count system variable.
解释：这个参数只对TCP/IP链接有效，分别是数据库等待接收客户端发送网络包和发送网络包给客户端的超时时间，这是在Activity状态下的线程才有效的参数
slave_net_timeout
手册描述：
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement or –master-connect-retry option, and the number of reconnection attempts is limited by the –master-retry-count option. The default is 3600 seconds (one hour).
解释：这是Slave判断主机是否挂掉的超时设置，在设定时间内依然没有获取到Master的回应就人为Master挂掉了
