<![CDATA[静怡家园]]> http://www.zhanghaijun.com/index.php zh-cn http://www.zhanghaijun.com/post/973/ <![CDATA[CentOS/RedHat Enterprise使用官方源安装MySQL]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Wed, 18 Jan 2017 03:54:10 +0000 http://www.zhanghaijun.com/post/973/ 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/
Tags - , , , , , , ]]> http://www.zhanghaijun.com/post/965/ <![CDATA[[Warning] InnoDB: Cannot open table mysql/slave_master_info]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Wed, 27 Jul 2016 06:09:48 +0000 http://www.zhanghaijun.com/post/965/ 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 'root@localhost.localdomain' 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 '@ root@localhost.localdomain' 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服务

三、检验结果：
点击在新窗口中浏览此图片
已经没有报错信息了。
Tags - , , , ]]> http://www.zhanghaijun.com/post/917/ <![CDATA[MySQL内存及虚拟内存优化设置]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Wed, 13 Mar 2013 01:55:54 +0000 http://www.zhanghaijun.com/post/917/
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/
Tags - , ]]> http://www.zhanghaijun.com/post/914/ <![CDATA[通过mysql二进制binlog恢复数据]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Fri, 25 Jan 2013 03:05:15 +0000 http://www.zhanghaijun.com/post/914/
开始提取二进制日志，首先把某库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;
执行完毕，该表的数据就恢复到当天误删除之前的状态了。
Tags - , ]]> http://www.zhanghaijun.com/post/898/ <![CDATA[MySQL的timeout那点事]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Thu, 05 Jul 2012 02:43:07 +0000 http://www.zhanghaijun.com/post/898/
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挂掉了
Tags - , ]]> http://www.zhanghaijun.com/post/871/ <![CDATA[shell脚本监控mysql服务器状态]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Mon, 27 Feb 2012 02:35:51 +0000 http://www.zhanghaijun.com/post/871/ 1.监控mysql服务器的状态
2.当发现mysql down机就自动重启mysql服务
3.重启mysql不成功，发邮件给管理员警告mysql down机

#vi /usr/local/sbin/check_mysql.sh
#!/bin/sh
cat << EOF
+--------------------------------------------------------------------------+
|                      === Welcome to LinuxTone===                         |
|-------------------------http://www.linuxtone.org------------------------ |
+--------------------------------------------------------------------------+
EOF

PORT=`netstat -na|grep "LISTEN"|grep "3306"|awk -F[:" "]+ '{print $5}'`
MYSQLIP=`ifconfig eth0|awk '/inet/{print $2}'|cut -c 6-`
while [ `whoami` == "root" ]
do
  if [ "$PORT" == "3306" ];then
    echo "mysql is running......"
  else
    echo "restart mysql"
    /etc/init.d/mysqld restart
       if [ "$PORT" == "3306" ];then
         echo "mysql restart successful......"
       else
         echo "mysql restart failure......"
         echo "server: $MYSQLIP mysql is down,please try to restart mysql!" > /var/log/mymsg
         mail -s "warn!server: $MYSQLIP  mysql is down" hamgua\@gmail.com < /var/log/mymsg
       fi
  fi
break
done

赋予check_mysql.sh可执行的权限
#chmod u+x /usr/local/sbin/check_mysql.sh

加入crontab，让系统五分钟检测一次mysql状态
#crontab -l
*/5 * * * * /usr/local/sbin/check_mysql.sh > /dev/null 2>&1
Tags - , ]]> http://www.zhanghaijun.com/post/868/ <![CDATA[mongodb常用管理命令]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Mon, 09 Jan 2012 11:49:38 +0000 http://www.zhanghaijun.com/post/868/ 1.要尝试使用MongoDB首先必须要在命令行运行mongod服务
a. cd MONGO_HOME/bin
b. ./mongod --dbpath /data/mongodb/db/  --logpath /data/mongodb/logs/mongolog  --logappend --fork

参数说明
--dbpath            #指定db文件存放的目录
--port              #指定mongod服务使用的端口
--fork              #设置mongo服务为后台运行
--logpath           #指定log文件的目录和文件名
--logappend         #设置每次log添加在文件最后
--rest              #关闭rest api功能
--nohttpinterface   #关闭web管理功能
--auth              #指定mongo使用身份验证机制
--bindip            #用逗号分隔ip地址，用来指定
--f                 #将所有前面介绍的参数都可以存放到一个配置文件中，然后用这个参数调用配置文件来启动mongod服务

2.关闭MongoDB有几种方法
a. db.shutdownServer()  #推荐优先使用
b. ctrl + c             #在不使用 --fork参数的时候可以使用，可能会造成数据文件损坏
c. kill / kill -2       #在无法使用 a和b的情况下使用，可能会造成数据文件损坏
d. kill -9              #不在万不得已的情况下，不要使用这个方法

3. 查看MongoDB状态
a.db.runCommand({"serverStatus":1})
b.MONGO_HOME/bin/mongostat

4. 添加用户，切换用户 使 --auth参数起效
db.addUser("root","123")
db.addUser("read_only","123",true);  #第3个参数表示设置readonly的状态

db.auth("read_only","123")

5. 数据库备份
有4种方法备份数据库
a. 关闭mongod服务后，复制--dbpath参数指定的数据文件。优点速度快，缺点需要停止mongo服务。
b. 使用mongodump 导出数据，并用mongorestore 导入数据。优点不需要停止mongo服务，缺点在mongodump操作时用户插入的数据可能无法备份出来。
c. fsync and lock锁定数据库的让用户只能使用read功能，再使用方法b导出并导入数据。优点不需要停止mongo服务，缺点在数据库lock期间用户无法执行insert操作。
d. 使用slaveDB并且 使用方法c锁定slaveDB，再使用方法b导出并导入数据。优点不需要停止mongo服务，不会影响用户insert操作（推荐使用此方法）。

6. 修复数据库
当数据库文件遭到损坏的时候有3种方法修复数据文件
a. MONGO_HOME/bin/mongod --repair
b. use test
   db.repairDatabase()
c. db.runCommand({"repairDatabase":1});


Tags - , , ]]> http://www.zhanghaijun.com/post/765/ <![CDATA[SQL常用命令使用方法]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Mon, 02 May 2011 14:48:41 +0000 http://www.zhanghaijun.com/post/765/
sql=”select * from 数据表 where 字段名=字段值 order by 字段名 ”

sql=”select * from 数据表 where 字段名 like ‘%字段值%‘ order by 字段名 ”

sql=”select top 10 * from 数据表 where 字段名 order by 字段名 ”

sql=”select * from 数据表 where 字段名 in (‘值1‘,‘值2‘,‘值3‘)”

sql=”select * from 数据表 where 字段名 between 值1 and 值2″

(2) 更新数据记录：

sql=”update 数据表 set 字段名=字段值 where 条件表达式”

sql=”update 数据表 set 字段1=值1,字段2=值2 …… 字段n=值n where 条件表达式”

(3) 删除数据记录：

sql=”delete from 数据表 where 条件表达式”

sql=”delete from 数据表” (将数据表所有记录删除)

(4) 添加数据记录：

sql=”insert into 数据表 (字段1,字段2,字段3 …) valuess (值1,值2,值3 …)”

sql=”insert into 目标数据表 select * from 源数据表” (把源数据表的记录添加到目标数据表)

(5) 数据记录统计函数：

AVG(字段名) 得出一个表格栏平均值
COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计
MAX(字段名) 取得一个表格栏最大的值
MIN(字段名) 取得一个表格栏最小的值
SUM(字段名) 把数据栏的值相加

引用以上函数的方法：

sql=”select sum(字段名) as 别名 from 数据表 where 条件表达式”
set rs=conn.excute(sql)

用 rs(“别名”) 获取统的计值，其它函数运用同上。

(6) 数据表的建立和删除：

CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) …… )

例：CREATE TABLE tab01(name varchar(50),datetime default now())

DROP TABLE 数据表名称 (永久性删除一个数据表)

(7)记录集对象的方法：

rs.movenext 将记录指针从当前的位置向下移一行
rs.moveprevious 将记录指针从当前的位置向上移一行
rs.movefirst 将记录指针移到数据表第一行
rs.movelast 将记录指针移到数据表最后一行
rs.absoluteposition=N 将记录指针移到数据表第N行
rs.absolutepage=N 将记录指针移到第N页的第一行
rs.pagesize=N 设置每页为N条记录
rs.pagecount 根据 pagesize 的设置返回总页数
rs.recordcount 返回记录总数
rs.bof 返回记录指针是否超出数据表首端，true表示是，false为否
rs.eof 返回记录指针是否超出数据表末端，true表示是，false为否
rs.delete 删除当前记录，但记录指针不会向下移动
rs.addnew 添加记录到数据表末端
rs.update 更新数据表记录

(8)判断所填数据是数字型

if not isNumeric(request(“字段名称”)) then
response.write “不是数字”
else
response.write “数字”
end if
Tags - ]]> http://www.zhanghaijun.com/post/762/ <![CDATA[MySQL安全设置问题]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Wed, 27 Apr 2011 15:16:10 +0000 http://www.zhanghaijun.com/post/762/
1.如果客户端和服务器端的连接需要跨越并通过不可信任的网络，那么就需要使用SSH隧道来加密该连接的通信。

2.用set password语句来修改用户的密码，三个步骤，先“mysql -u root”登陆数据库系统，然后“mysql> update mysql.user set password=password(‘newpwd’)”，最后执行“flush privileges”就可以了。

3.需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于ACL即访问控制列表的安全措施来完成。也有一些对SSL连接的支持。

4.除了root用户外的其他任何用户不允许访问mysql主数据库中的user表；

加密后存放在user表中的加密后的用户密码一旦泄露，其他人可以随意用该用户名/密码相应的数据库；

5.用grant和revoke语句来进行用户访问控制的工作；

6.不使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码；

7.不选用字典中的字来做密码；

8.采用防火墙来去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在DMZ区域中；

9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不能允许从非信任网络中访问数据库服务器的3306号TCP端口，因此需要在防火墙或路由器上做设定；

10.为了防止被恶意传入非法参数，例如where ID=234，别人却输入where ID=234 or 1=1导致全部显示，所以在web的表单中使用”或”"来用字符串，在动态URL中加入%22代表双引号、%23代表井号、%27代表单引号；传递未检查过的值给mysql数据库是非常危险的；

11.在传递数据给mysql时检查一下大小；

12.应用程序需要连接到数据库应该使用一般的用户帐号，只开放少数必要的权限给该用户；

13.在各编程接口(C C++ PHP Perl Java JDBC等)中使用特定‘逃脱字符’函数；

在因特网上使用mysql数据库时一定少用传输明文的数据，而用SSL和SSH的加密方式数据来传输；

14.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 | strings。以普通用户来启动mysql数据库服务；

15.不使用到表的联结符号，选用的参数 –skip-symbolic-links；

16.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限；

17.不许将process或super权限付给非管理用户，该mysqladmin processlist可以列举出当前执行的查询文本；super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器；

18.file权限不付给管理员以外的用户，防止出现load data ‘/etc/passwd’到表中再用select 显示出来的问题；

19.如果不相信DNS服务公司的服务，可以在主机名称允许表中只设置IP数字地址；

20.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数；

21.grant语句也支持资源控制选项；

22.启动mysqld服务进程的安全选项开关，–local-infile=0或1 若是0则客户端程序就无法使用local load data了，赋权的一个例子grant insert(user) on mysql.user to ‘user_name’@'host_name’;若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用mysqladmin flush-privileges或mysqladmin reload来开启访问控制；默认情况是show databases语句对所有用户开放，可以用–skip-show-databases来关闭掉。

23.碰到Error 1045(28000) Access Denied for user ‘root’@'localhost’ (Using password:NO)错误时，你需要重新设置密码，具体方法是：先用–skip-grant-tables参数启动mysqld，然后执行mysql -u root mysql,mysql>update user set password=password(‘newpassword’) where user=’root’;mysql>Flush privileges;，最后重新启动mysql就可以了。
Tags - ]]> http://www.zhanghaijun.com/post/485/ <![CDATA[MySql 新建用户,新建数据库,用户授权,删除用户,修改密码]]> 碟舞飞扬 <webmaster@zhanghaijun.com> Wed, 05 Nov 2008 06:32:32 +0000 http://www.zhanghaijun.com/post/485/ //登录MYSQL
@>mysql -u root -p
@>密码
//创建用户
mysql> mysql> insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub
ject) values("localhost","pppadmin",password("passwd"),'','','');

这样就创建了一个名为：phplamp   密码为：1234   的用户。

然后登录一下。

mysql>exit;
@>mysql -u phplamp -p
@>输入密码
mysql>登录成功

2.为用户授权。

//登录MYSQL（有ROOT权限）。我里我以ROOT身份登录.
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(phplampDB)
mysql>create database phplampDB;
//授权phplamp用户拥有phplamp数据库的所有权限。
>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作

/*
如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表。
mysql>flush privileges;
*/

3.删除用户。
@>mysql -u root -p
@>密码
mysql>Delete FROM user Where User="phplamp" and Host="localhost";
mysql>flush privileges;
//删除用户的数据库
mysql>drop database phplampDB;

4.修改指定用户密码。
@>mysql -u root -p
@>密码
mysql>update mysql.user set password=password('新密码') where User="phplamp" and Host="localhost";
mysql>flush privileges;

5.列出所有数据库

mysql>show database;

6.切换数据库

mysql>use '数据库名';

7.列出所有表

mysql>show tables;

8.显示数据表结构

mysql>describe 表名;

9.删除数据库和数据表

mysql>drop database 数据库名;
mysql>drop table 数据表名;
Tags - ]]>