怎么能让主库上的用户管理不复制到从库

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

Case:
主库上设置了:replicate-do-db=mysql
目的:希望保持主库上用户和从库上的用户独立性
出现的问题: 在主库上创建的一个用户,或是修改了密码都会同步到从库上.

解决办法:

1. 把replicate-do-db=mysql改成: replicate-wild-igore-table=mysql.%
2. 临时办法:在使用grant ,remove等涉及用户相关时,前面先使用use mysql; 然后进行相关操作.

原因:
replicate-do-db只工作于指定的DB下面所有的写操作不会同步,当发生串插写操作时,将会用步到从
库上.
所以如果明确想不会将某个库复制到从库请使用: replicate-wild-igore-table=dbname.% 做替代

验证:

比较简单,就不发图了.

从MySQL源码学习运维Innodb buffer命中率计算

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.
按官方手册推荐Innodb buffer Hit Ratios的计算是:

100-((iReads / iReadRequests)*100)
iReads : mysql->status->Innodb_buffer_pool_reads
iReadRequests: mysql->status->Innodb_buffer_pool_read_requests

出处: http://dev.mysql.com/doc/mysql-monitor/2.0/en/mem_graphref.html
搜”Hit Ratios”
推荐有兴趣的同学把这个页面都看一下应该也会有很大收获.
另外在hackmysql: www.hackmysql.com网站上的: mysqlsqlreport中关于buffer命中计算是:

$ib_bp_read_ratio = sprintf "%.2f",
($stats{'Innodb_buffer_pool_read_requests'} ?
100 - ($stats{'Innodb_buffer_pool_reads'} /
$stats{'Innodb_buffer_pool_read_requests'}) * 100 :0);

即:

ib_bp_hit=100-(Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100

另外我们知道查看Innodb Buffer Hit Ratios的地方是:
show engine innodb status\G;
Buffer pool hit rate : XXXX/1000;
那个XXX/1000即是buffer pool hit ratios的命中.
这样也可以从代码里看一下这个bp命中计算:

storage/innobase/buf/buf0buf.c # void buf_print_io
storage/innodbase/include/buf0buf.h #struct buf_block_struct

在buf0buf.c 中的buf_print_io函数中可以看到:

void
buf_print_io(
…

if (buf_pool->n_page_gets > buf_pool->n_page_gets_old) {
fprintf(file, "Buffer pool hit rate %lu / 1000\n",
(ulong)
(1000 - ((1000 * (buf_pool->n_pages_read
- buf_pool->n_pages_read_old))
/ (buf_pool->n_page_gets
- buf_pool->n_page_gets_old))));
} else {
fputs("No buffer pool page gets since the last printout\n",
file);
}

buf_pool->n_page_gets_old = buf_pool->n_page_gets;
buf_pool->n_pages_read_old = buf_pool->n_pages_read;
…
}

结合:
storage\innobase\include\buf0buf.h中

struct buf_block_struct{
…
ulint n_pages_read; /* number read operations */
…
ulint n_page_gets; /* number of page gets performed;
also successful searches through
the adaptive hash index are
counted as page gets; this field
is NOT protected by the buffer
pool mutex */
…
ulint n_page_gets_old;/* n_page_gets when buf_print was
last time called: used to calculate
hit rate */
…
ulint n_pages_read_old;/* n_pages_read when buf_print was
last time called */
…
}

从这个来看innodb buffer hit Ratios的命中计算需要本次取的值和上次值做一个减法公式应该为

ib_bp_hit=1000 – (t2.iReads – t1.iReads)/(t2.iReadRequest – t1.iReadRequest)*1000

t(n): 时间点 两个时间间隔最少是30秒以上,在小意义不大.
iReads: Innodb_buffer_pool_reads
iReadRequest: Innodb_buffer_pool_read_requests

对innodb的输出参数有兴趣的可以关注: storage/innobase/buf/Srv0srv.c 中的:
void srv_export_innodb_status()

思考:
对于innodb_buffer_pool_read_requests, innodb_buffer_pool_reads这种累加值,当很大时进行: innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 相来讲只能得到从开始到现在的命中率的表现了. 如果想得到现在近五分钟,近一分钟或是8点到9点每分钟的命中率情况,如果还是按着innodb_buffer_pool_reads/innodb_buffer_pool_read_requests 进行计算,只能得到mysqld开起累计在8点-9点的每分钟的累计平均命中情况.
所以如果想到每(五)分钟的命中情况,就需要本次取得的值和一(五)分钟前的值进行相减,然后进行运算.这样才能得到一个当下的bp命中情况.
两种方法没实质的对错的问题,但相对于源码中的那种计算方式更容让发现数据库的抖动问题.

能解决的问题:
偶而的数据库性能抖动能直观的反应出来.

MySQL版本和SSIS不兼容问题分析及解决办法

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.
线上替换Percona版本和SSIS不兼容问题分析及解决办法

现象:
利用SSIS(SQL Server Intelligence Services)访问Percona-Server 5.1 原来的SQL取不到数据。
如果加上limit后就能取到数据。但原来的MySQL版本(MySQL-5.1.43sp1)就可以取到数据。
MySQL版本:Percona-Server-5.1.55 or MySQL-5.1.55后
SSIS 从MySQL中取不到数据。

分析:
从WireShark抓包分SSIS执行SQL的流程:
1. PING 包
2. INIT_DB连接上数据库
3. 发送:set sql_select_limit=0;
4. 执行SSIS中定义的SQL;
5. 发送: set sql_select_limit=-1;
6. 再执行SSIS中定义的SQL;

问题发生在哪呢?

第一次set sql_select_limit=0 是因为在早期的数据库交互中,因为mysql没有SQL编译的及SQL正确否的校验,
所以很多程序员会用set sql_select_limit=0这个然后再执行SQL看有返回的错误不。
从协义上看第一次set sql_select_limit=0在Percona-Server-5.1.55及后面的SQL执行都是正确执行的。
然后第二次调用:set sql_select_limit=-1; 目的是把环境变量sql_select_limit还原成default值。
然后再执行SSIS中的SQL.读取读取不到信息。

猜测:

  在执行set sql_select_limit=-1没能执行成功造成后面SSIS再次执行SQL没返回。

有了以上的信息去验证一下:

mysql> select @@version_comment, @@version;
+--------------------------------------------------------------+--------------------+
| @@version_comment | @@version |
+--------------------------------------------------------------+--------------------+
| Percona Server with XtraDB (GPL), Release 12.6, Revision 200 | 5.1.55-rel12.6-log |
+--------------------------------------------------------------+--------------------+
1 row in set (0.00 sec)
mysql>  select user from mysql.user where user="root";
+------+
| user |
+------+
| root |
| root |
+------+
2 rows in set (0.00 sec)
mysql>  set sql_select_limit=0;
Query OK, 0 rows affected (0.00 sec)
mysql>  select user from mysql.user where user="root";
Empty set (0.00 sec)
mysql> set sql_select_limit=-1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>  select user from mysql.user where user="root";
Empty set (0.00 sec)
mysql> set sql_select_limit=default;
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user where user="root";
+------+
| user |
+------+
| root |
| root |
+------+
2 rows in set (0.00 sec)
mysql>  set sql_select_limit=-1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect sql_select_limit value: '-1' |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

可见在执行:set sql_select_limit=-1; 这个负1是不支持的。从手册上来看,也只是说了一个可以给的最大值,也没说最少值。该参数还是挺鬼疑的。
有兴趣的可以试一下mysql-5.1.54及以下版本或是MySQL-5.1.55以后的版本。
我试了MySQL-5.1.56一样对:set sql_select_limit=-1是不支持。
现在在来看为什么加上limit都可以显示数据呢?
手册里描述:If a SELECT has a LIMIT clause, the LIMIT takes precedence over the value of sql_select_limit.
可见SELECT中的limit运算优先于sql_select_limit。

验证:

mysql>  select user from mysql.user where user="root" limit 10;
+------+
| user |
+------+
| root |
| root |
+------+
2 rows in set (0.00 sec)
mysql>  set sql_select_limit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select user from mysql.user where user="root";
Empty set (0.00 sec)

如何解决这个问题:
原因是set sql_select_limit=-1没正确执行。
可以在SSIS中的SQL前添加set sql_selct_limit=default;该解决方法存在性能问题,但能正确执行了。
最彻底的解决办法:反溃给那MS支持方,SQL调用中的set sql_select_limit=-1改成 set sql_select_limit=default。

影响:
如果该问题不修正,目前这个SSIS只能使用MySQL-5.1.54前的版本了。
参考:
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_sql_select_limit

感想:
不是开源的东西,你知道为什么,知道怎么改,也无语啊。

如何查看mysqld进程的Profiler

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.

在mysqld中运行中有时偶而出有点看不出来原因的问题,想看看MySQLD中在执行什么,可以用下来的脚本查一下profiler

#PMP 
#http://poormansprofiler.org/
#!/bin/bash
nsamples=1
sleeptime=0
pid=$(pidof mysqld)

for x in $(seq 1 $nsamples)
  do
    gdb -ex "set pagination 0" -ex "thread apply all bt" -batch -p $pid
    sleep $sleeptime
  done | \
awk '
  BEGIN { s = ""; } 
  /Thread/ { print s; s = ""; } 
  /^\#/ { if (s != "" ) { s = s "," $4} else { s = $4 } } 
  END { print s }' | \
sort | uniq -c | sort -r -n -k 1,1

Perl DBI操作MySQL的Tips

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.

 使用perl连接mysql,这个网上有很多案例了,一般大家都是DBI下的DBD::MySQL这个模块进行.这里做一个mask弄一个TIPS:
 Perl DBI MySQL的字符集为UTF8
 Perl DBI 特殊字符写入时报错
 Perl DBI 连接自动重连或是连接超时

 
1. 当MySQL的字符集是UTF8时需要引入:

use utf8;
binmode(STDOUT, ':encoding(utf8)');
binmode(STDIN, ':encoding(utf8)');
binmode(STDERR, ':encoding(utf8)');

目的: 
解决perl连接mysql到数据后读取显示结果为乱码的问题.
 
2.对于特殊字符的写入,最好使用:

 my $sth=$dbh->prepare("insert into wubx.WeekEvent values(?,?,?,?,?,?,?)");
 $sth->execute($OId,$CId,qq/$Time/,$EventType,qq/$CDesc/,$PId,$RFlag);

对于字符串有可能是用户提交的用qq//包裹,减少特殊字符造成SQL不能执行的情况
3. 如果连接两个数据库有交换的操作或是迁数据,要考虑连连超时的情况.
 报错: MySQL server has gone away
  处理办法:
  在DBD::mysql 4.012以后支持DBI连接的自动重连.需要设置:

  $dbh->{mysql_auto_reconnect} = 1;

  在早期的模块中不支持,简单的方法:

   $dbh->do('set SESSION wait_timeout=72000');
   $dbh->do('set SESSION interactive_timeout=72000');

  此方法适用别的语言连MySQL连接短期丢失或是Server的timeout时间设置太短.

Innodb Log写入方式分析

作者/译者:王佳隆 来源: http://www.mysqlsupport.cn/ 联系方式:enjoylonely#live.cn  转载请注:译者和出处,并且不能用于商业用途,违者必究.

原文URL:http://www.mysqlperformanceblog.com/2010/07/16/analyzing-the-distribution-of-innodb-log-file-writes/

    最近我分析了一下Innodb是如何写多个日志的。我这里有个流量比较高的MySQL系统,使用的是Percona XtraDB存储引擎,

我使用strace命令分别跟踪了innodb如何去日志文件的。通常来说,innodb是以512bytes的大小来写入日志的。

关于这个可以参考:Mark Callaghan explained this and some of its performance implications 。那么innodb什么时候情况下会以大于512bytes者小于512bytes的请求写到日志里呢?

首先,我通过lsof命令找出日志的文件描述符(handle).

# lsof -p $(pidof mysqld) | grep ib_log
mysqld  29772 mysql    8uW  REG                8,2   268435456   7143989 /var/lib/mysql/ib_logfile0
mysqld  29772 mysql    9uW  REG                8,2   268435456   7143993 /var/lib/mysql/ib_logfile1

我们可以看到2个日志的handle为8和9,现在我们需要捕获innodb是如何写这2个日志的相关信息。Innodb轮循写日志就是通过这个文件描述符.

The following grabs the write sizes out of 100k calls to pwrite() and aggregates them:

# strace -f -p $(pidof mysqld) -e pwrite -s1 -xx 2>&1 \

   | grep ‘pwrite([89],’ |head -n 100000 \

   | awk ‘{writes[$5]++}END{for(w in writes){print w, ” “, writes[w]}}’

本来我可以写一个更好的脚本来捕获信息,但是下面的信息对我来说已经足够了。

bytes        count
512           44067
1024         30740
1536         15221
2048         7094
2560         1810
3072         570
3584         219
4096         112
4608         39
5120         23
5632         16
6144         15
6656         5
7168         3
7680         8
8192         2
8704         2
9216         1
9728         2
10240       1
10752       2
11264       1
11776       1
14848       1
15360       1
15872       2
16384       4
16896       4
17408       2
17920       2
18432       2
18944       8
19456       7
19968       4
20480       4
21504       1
22016       2
24064       1
40960       1

总的来说,大致有3/4是以512和1024字节写入的。那么这到底什么意思呢?这里有很多有趣的而且复杂的东西需要我们去研究。

 1. 我们可以看到,大部分的写入都小于4K。但是我们知道操作系统的page大小是4K的。如果要写入的page不在cache中,那么这个page最开始需要读出,然后再修改,最后再写回到磁盘。vadim曾经过做做一些测试,如果要性能最好,那么日志要在OS的cache中。

 2.这台MySQL的innodb_flush_log_at_trx_commit设置是2.这就说明每个事务都会有一个日志写入请求,这个和设置为1是一样的。但是如果设置成0,那么写入的方法就大不相同—这个时候写入请求会累计到一定程度,然后一起写入。

 3.那么如果说log buffer小于一个事务要写的日志大小怎么办?这是另外一个需要研究的主题了。目前我还不清楚。

4.从上面的信息来看,是否可以很容易知道log buffer大小应该给多少呢?最大的写入小于40K,这好像可以说明分配64K给log buffer就已经足够了。这是真的吗?我们需要去测试才可以知道。peter以前和我谈过这个问题,log buffer背后的机制其实是很复杂的,到底log buffer需要保留多少空间给写操作。这些都需要更深入的研究。但是有一点可以确认,即使最大的写入操作不是很大的情况下,如果log buffer设置太小,性能肯定是不好的,而且会造成而外的锁。这个问题或许我们同样需要去研究。

最后,研究innodb是如何写日志的很容易,但是事实上innodb redo log机制是很复杂的,有时候我们很难说去猜想应该是什么样的,而应该去更深入的研究才可以知道的更多。也许我们可以按照上面这种步骤去研究不同LOG buufer大小,不同的日志参数设置,以及不同的服务器负载的情况下innodb到底是如何来写入日志的。

参考地址:http://mysqlha.blogspot.com/2009/06/buffered-versus-direct-io-for-innodb.html

mysqldump 的Tips

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.

只导出表结构:
 mysqldump   -d –trigger=false
只导出存储过程:
mysqldump -f  -Rtdn –triggers=false
只导出触发器:
 mysqldump -f  -tdn –triggers
只导出事件:
 mysqldump -f  -Etdn –triggers=false
只导出数据:
mysqldump -f  –single-transaction –triggers=false  -t

案例:一个引号带来的查询性能提升

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.
今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记,来纪念一下随便的字段定义的问题。

回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例.
查询语句:

	SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;

该表(client_id,channel)是一个组合索引.
利用explain,看一下执行计划,对于索引使用上看上非常完美

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
| id | select_type | table       | type  | possible_keys      | key                | key_len | ref  | rows     | Extra                    |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | xxx_sources | index | idx_client_channel | idx_client_channel | 1032    | NULL | 20207319 | Using where; Using index |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)

看一下实际执行:

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
+---------+----------+
| channel | visitors |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (11.69 sec)

实际执行的情况非常的糟糕.传通的想法,这个执行从索引上执行计划上看非常完美了,好象和MySQL没什么关系了. 在去看一下表的设计会发现client_id也是设计成了
varchar(255).看到这里不防可以使用下面的方法试一下:

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys      | key                | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | xxx_sources | ref  | idx_client_channel | idx_client_channel | 258     | const | 457184 | Using where; Using index |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)

从执行计划上来看,差不多,但实际差多了.具体上来看key_len从1032降到了258,执行计划变成了const基于等于的查找,行数从原来千万级到了十万级了.不算也能明白IO
节省了很多.
再来看实际执行:

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;
+---------+----------+
| channel | visitors |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (0.25 sec)

哇,从11.69秒变成了0.25秒,这是什么概念,优化了多少倍,算一下吧.

看到这里在想什么呢,记住这个案例,嗯,不错,以后还可以加引号优化一下.那为什么不问一下,能不能在优化了,为什么会这样呢?
我们先来看一下第一个问题:
能不能在优化了?
答案是当然可以了.从索引的长度上来看258还是一个非常大的数据,对于client_id这个字段从名字上来看,也只会存数据型的值,那为什么不用的一个int unsigned去存呢,
索引的长度马上会从258降到4。这样不是又节省了很多吗?
接下来看一下第二个问题,为什么会这样呢?
原因有两点,同时基于一个原则,基于成本的优化器。对于client_id在表的定义时定义成了字符型的值,在查询时传入了数值型的值,需要经过一个数值转换,悲剧的开始,最终
导致MySQL选择了一个完成的索引去扫描。

从这个案例上,我们需要注意什么呢?
合理的选择数据类型,基本工太重要了,就这叫赢在起跑线,一切都不能随便了,别把一个表定义成了降了主建外其它全是Varchar(255)。对数据库的double/float这种字
段做索引时一定要小心。

待思考:
为什么加一个引号后索引长度执行计划变成了258,为什么是258呢,不是别的呢。

Linux运维Tips(-)

        作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.

       对于一个Linux运维团队,在管理一些机器时,难免会出现一些尴嘎的事情,A同事在操作的时间,B同事也上去操作了;本来工作时间上去时,如果看一下磁盘使用状态,也许就必免了假日收到了短信报警。对于这些问题怎么处理呢?

      聪明的朋友说,登录到系统后运行一下w 和df -h不就行了。是的,这是一个很好的解决方法。但人总是懒惰的。要不,也不会出那么事了。

        这里提供一个自动化每一个用户登录上去,自动把df  -h 和w的结果输出到终端。实现方法: 编辑~/.profile 或是 ~/.bashrc (提倡改.profile)在最后添加:

echo "============================="

df -h

echo "============================="

w

       然后再登录到系统中时就会自动显示:

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

Filesystem            Size  Used Avail Use% Mounted on

/dev/sda2             3.9G  2.4G  1.4G  65% /

/dev/sda1             122M   12M  104M  11% /boot

tmpfs                 250M     0  250M   0% /dev/shm

/dev/sdb1           11G  1.9G  9.1G  17% /data

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

 09:15:00 up 28 days, 20 min,  1 user,  load average: 0.00, 0.00, 0.00

USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
wubx     pts/0    10.10.15.72      09:15    0.00s  0.02s  0.02s -bash

利用tcpflow抓取SQL

作者:吴炳锡 来源:http://www.mysqlsupport.cn/ 联系方式: wubingxi#gmail.com 转载请注:译者和出处,并且不能用于商业用途,违者必究.

以前介绍过利用tcpdump抓取相关的SQL,但是在识别方面并不友好,只是能看到相关的SQL。今天推荐一个强劲的工具:tcpflow加一些牛人们开发的工具从而实现友好的显示相关的SQL。
相关工具下载,功先欲其事,必先利其器:
Tcpflow 下载:http://www.circlemud.org/~jelson/software/tcpflow/
extract_queries.: http://mysqldump.azundris.com/uploads/extract_queries.c
使用方法:
#mkdir flow
#cd flow
#tcpflow –i eth0 dst MasterIP and port 3306
等待一会 Ctrl+c

#cd ..
# find flow –print0 |xargs -0 extract_queries –u >slow
#mysqldumpslow -s c slow >stats

不足之处:
不能真正把SQL的执行时间记录下来,因为这个只是网络IO的流量抓取,同时这个也不能把真正的连接数据库的用户抓下来。
原文地址:http://mysqldump.azundris.com/archives/85-Getting-SQL-from-a-SPAN-port.html