MySQL技巧集锦之一

1）5.1.21后可以进行毫秒级的慢查询记录,设置long_query_time=0.01

2）mysqldump 不支持regex ，对于分表比较多的库，导出可以采用先 mysql -N information_schema -e “select table_name from tables where table_name like ‘prefix_%’” > tbs.txt 然后 mysqldump db `cat tbs.txt` > dump.sql的方式曲线救国

3）mysql的row格式binlog，insert,delete等都是多行，如果想合并为一行，其实一行简单的sed就可以搞定 sed ‘:a;1!N;s/n###//;s/### //;ta;’ test.txt

4）mysql计算两个datetime的时间差时，如果直接用减法，cast(endtime-begintime as time)，当endtime的分或者秒小于begintime的分或者秒时，结果为null，所以必须使用timediff(endtime,begintime)

5) mysql5.0 ：show variables like ’sql_log_bin’;显示Empty set，但实际上set sql_log_bin=0;有效

6) #tcpdump# #mk-query-digest#抓包的时候如果#mysql#QPS太高，可能分析不出结果，其实分两步做就行了，tcpdump -i eth1 port 4512 -s 65535 -x -n -q -tttt > tcpdump.log cat tcpdump.log |mk-query-digest –type tcpdump –watch-server 192.168.0.2:4512，注意对于非3306端口要加–watch-server

7) MySQL 的show authors 命令可以看到代码贡献人的名单,不过名单很旧了，所以在5.6中移除了

8）python使用MySQLdb连接MySQL数据库，因为较长时间没有操作再次操作时出现_mysql_exceptions.OperationalError: (2006, ‘MySQL server has gone away’)怎么办？方法很多，不过最简单的应该是再次执行操作之前执行 mydb.ping(True)，注意True不能少

9）MySQL如果要修改字段的默认值，有几种方法：一是mysql> ALTER TABLE sakila.film -> MODIFY COLUMN rental_duration TINYINT(3) NOT NULL DEFAULT 5; 7. 二是mysql> ALTER TABLE sakila.film -> ALTER COLUMN rental_duration SET DEFAULT 5; 第二种非常快，因为只修改了.frm文件。第一种？copy 表！

10）MySQL的状态值很多都是累计值，想快速看到变化值，可用此命令： mysqladmin -hxxxx -umysqlha -pxxxx -Pxxxx ext -i1 | awk ‘ /Queries/{q=$4-qp;qp=$4} /Com_insert /{insert=$4-insertp;insertp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf “%5d %5d %5d %5dn”, q, insert, tc, $4}’，还可以用/usr/local/my51/bin/mysqladmin -hhostname -uuser -p -P4697 -i1 extended-status -r |egrep “Com_insert |Com_select”

11）MySQL性能测试时，对于搜集MySQL本身的数据，可以在测试前执行”show global variables;”>>log，测试中间隔执行”show global status;”,”show engine innodb statusG;”,”show full processlistG;”>>log

12）mysql中输入pager more或者pager less可以分页显示结果,也可以在连接时使用–pager=more

13）mysql授权时可以对库名使用通配符，表名不行，注意“_”需要转义！如：
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON `meta%`.* TO user1@’172.16.215.%’ IDENTIFIED BY ‘12345′;
grant all privileges on `dp_p4p%`.* to dp_admin2 identified by ‘mypasswd’;

14）SELECT SUM(id = 2), SUM(name = ‘a’) FROM t3 可以统计出每个字段符合条件的数量

15）CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB; 在show engine innodb status中可以显示更详细的死锁信息，同时每隔16秒分输出show engine innodb status结果到error log

16）awk ‘/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}’ slow-query.log 可以显示慢查询时间分布

17) mysql -hhostname -uuser -ppwd-P4901 -e “show processlistG”|grep State: |sort|uniq -c|sort -rn 可以对processlist状态进行分类统计

18) gdb -p $(ps -ef|grep “mysqld “|grep 3306|awk ‘{print $2}’) -ex “set opt_log_slave_updates=0″ -batch 在5.5中可以不重启开启log_slave_updates

19) select id,group_concat(distinct name order by name separator ‘:’) from t4 group by id; 可以进行字符串分组拼接

20）同时查看多个status
show status where variable_name like ‘Uptime’ or variable_name like ‘Threads_cached’;

原文 http://www.iamcjd.com/?p=1269