mysql运维过程中的小tip [转载]

mysql运维过程中的小 tips

1.查询数据库不同引擎下表和索引的大小
select engine,count(*) table_num, 
concat(truncate(sum(DATA_LENGTH/1024/1024),2),'MB') table_size,
concat(truncate(sum(INDEX_LENGTH/1024/1024),2),'MB') index_size ,
concat(truncate(sum(DATA_LENGTH/1024/1024)+sum(INDEX_LENGTH/1024/1024),2),'MB') total_size 
from information_schema.tables 
where TABLE_TYPE='BASE TABLE'  
group by engine;

2.查询数据库用户的状态

mysql -uroot -p  -e 'show processlist\G'|grep State|sort|uniq -c|sort -n


mysql -uroot -p -e ‘show processlist\G’|grep State|sort|uniq -c|sort -n

3.mysql>=5.5 查询锁阻塞的情况

select   
r.trx_id,  
r.trx_mysql_thread_id waiting_thread,  
r.trx_query id,  
b.trx_mysql_thread_id blocking_thread,  
b.trx_query blocking_query 
from  
information_schema.innodb_lock_waits w 
inner join  
information_schema.innodb_trx b  
on b.trx_id=w.blocking_trx_id 
inner join 
information_schema.innodb_trx r  
on r.trx_id=w.requesting_trx_id;
+--------+----------------+------------------------------+-----------------+----------------+
| trx_id | waiting_thread | id                 | blocking_thread | blocking_query |
+--------+----------------+------------------------------+-----------------+----------------+
| 527    |             17 | insert into lidan values (9) |              14 | NULL           |
| 526    |             16 | insert into lidan values (6) |              14 | NULL           |
=======================================================

4.通过抓包获取mysql协议包中的sql语句

/usr/sbin/tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | egrep -i 'SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL'
  • 表示输入的文件名(这里表示先输入文件,然后又输出到stdout)

5.批量修改存储引擎

/usr/local/mysql/bin/mysql_convert_table_format

将 28 行 $opt_type —-> $opt_engine
需要注意innodb 的外键和myisam的fulltext index

6.mysql 快速创建空表

在MYSQL中有两种方法。

1、create table select ...
2、create table like ...

第一种很多人都知道,第二种却很少人用。
第一种有个缺点
1、第一种会取消掉原来表的有些定义。
手册上是这么讲的:

Some conversion of data types might occur. For example, the AUTO_INCREMENT attribute is not preserved,
and VARCHAR columns can become CHAR columns. 

7.观察mysql status 变量的变化情况

方法一,(推荐)
每三秒打印出innodb相关状态的变化情况,并且使用白色标示

watch -d -n 3 "mysqladmin -uroot -pxxx ext|egrep Innodb" 

同样也是没三秒打印相关变化但是并没有白色标示

 mysqladmin -uroot -p1111 -i 3 ext|egrep Innodb

8.left join 条件的区别

SELECT * FROM a LEFT JOIN b ON a.uid=b.uid_a WHERE b.password='abcd';
+-----+---------+----------+----------+-------+
| uid | userfen | username | password | uid_a |
+-----+---------+----------+----------+-------+
|   1 |    1000 | admin    | abcd     |     1 | 
+-----+---------+----------+----------+-------+
1 ROW IN SET (0.00 sec)

mysql> SELECT * FROM a LEFT JOIN b ON a.uid=b.uid_a AND b.password='abcd';     
+-------+---------+----------+----------+-------+
| uid   | userfen | username | password | uid_a |
+-------+---------+----------+----------+-------+
|     1 |    1000 | admin    | abcd     |     1 | 
| 22222 | 2392032 | NULL     | NULL     |  NULL | 
+-------+---------+----------+----------+-------+

2 ROWS IN SET (0.00 sec)
WHERE 语句是在left join完成之后才执行的,所以它匹配不到NULL的行,ON语句是在left join之前执行。

9.多表关联删除

删除表aaa中id 与表t中id相同的数据

delete from aaa using t ,aaa  where aaa.id=t.id ; 

delete  aaa ,t from aaa ,t  where aaa.id=t.id ;

delete tt1 from  t1  as tt1  , t2 as  tt2 where  tt1.id=tt2.id;

10.多表更新

只更新表bo

update bo , t_disc td  SET  bo.name='你' where bo.name=td.name;

同时更新两个表(线上环境不推荐同时更新多表)

update bo , t_disc td  SET  bo.name='你' ,td.name='你' where bo.name=td.name;  

mysql对ip的存储

INET_ATON(expr)

给出一个作为字符串的网络地址的点地址表示,返回一个代表该地址数值的整数。地址可以是4或8比特地址。

SELECT INET_ATON('209.207.224.40');
-> 3520061480

产生的数字总是按照网络字节顺序。如上面的例子,数字按照 209×2563 + 207×2562 + 224×256 + 40 进行计算。
INET_ATON() 也能理解短格式 IP 地址:

mysql> SELECT INET_ATON('127.0.0.1'), INET_ATON('127.1');
        -> 2130706433, 2130706433

注释: 在存储由INET_ATON() 产生的值时,推荐你使用 INT UNSIGNED 列。假如你使用 (带符号) INT列,
则相应的第一个八位组大于127的IP 地址值会被截至 2147483647 (即, INET_ATON(‘127.255.255.255’) 所返回的值)。

INET_NTOA(expr)
给定一个数字网络地址 (4 或 8 比特),返回作为字符串的该地址的电地址表示。

mysql> SELECT INET_NTOA(3520061480);
        -> '209.207.224.40'

11.myisam表count的优化

count查询
对于MYISAM中的SQL:

SELECT count(*) from city WHERE id > 2;

改写为:

SELECT (SELECT COUNT(*) FROM city) - count(*) FROM city where id <=2

数据量大的情况下,并且id最好为顺序增长的或是主键(或是索引)
因为misam表count(*)会作为一个常量存储所以会很快

12.group_concat 函数的使用

select id,group_concat(name) name from tb group by id;
1,a1
1,a2
2,b1
2,b2

结果是

id  name
1    a1,a2
2    b1,b2

上边的函数可以实现查询结果
另外用group_concat的时候请注意,连接起来的字段如果是int型,一定要转换成char再拼起来,
否则在你执行后返回的将不是一个逗号隔开的串,而是byte。需要注意的是如果不带group by
那么函数就是对列的所有值进行连接

13.下面的命令,可以杀死当前用户bbs正在运行的连接)

已验证

mysqladmin -uroot -p  processlist|awk -F "|" '{if($3~/bbs/)print $2}'|xargs -n 1 mysqladmin -uroot -p kill

升级版:
kill 用户名为haha 执行时间超过50秒的select 语句

mysqladmin -uroot -pxxx  processlist|awk -F "|" '/select/{if($3~/haha/ && $7 > 50)print $2}'|xargs -n 1 mysqladmin -uroot -pxxx kill

14.脚本实现qps,tps的查看 (适合5.1~5.5)

mysqladmin -uroot -pxxxx extended-status -i1 |\
awk 'BEGIN{print " QPS   Com_commit  Com_rollback   Threads_con   Threads_run   TPS\n \
 ---------------------------------------------------------------------------\n"} \
/Queries/{q=$4-qp;qp=$4}\
/Com_commit/{com=$4-qc;qc=$4}\
/Com_rollback/{rol=$4-cr;cr=$4}\
/Threads_connected/{tc=$4}\
/Threads_running/{printf "%5d %5d %10d %15d %15d %10d\n",q,com,rol,tc,$4,com + rol}'

上面的是5.1的 5.5的参数增加了很多需要修改

mysqladmin -uroot -pxxx extended-status -i1|\
awk 'BEGIN{print " QPS   Com_commit  Com_rollback   Threads_con   Threads_run   TPS\n \
 ---------------------------------------------------------------------------\n"} \
/Queries/{q=$4-qp;qp=$4}\
/Com_commit/{com=$4-qc;qc=$4}\
/Com_rollback /{rol=$4-cr;cr=$4}\
/Threads_connected/{tc=$4}\
/Threads_running/{printf "%5d %5d %10d %15d %15d %10d\n",q,com,rol,tc,$4,com + rol}'

注意rollback后面的空格,另外你可能一直看到TPS对应的值为0,原因是:
计算TPS公式(com_commit+com_rollback)/time 这种计算方法必须有一个前提条件是:所有的事务必须是显示提交的,
如果存在隐式的提交和会滚(默认autocommit=1,或者value为on)可以看到Com_commit 始终为0 ,这就是隐式提交的时候,
不计入com_commit和com_rollback中的。

15.not in 查询的一个改写方式

mysql> select d.* from d where name<>'1' and id not in (select id from e);
+----+------+
| id | name |
+----+------+
|  9 | 9    |
| 19 | 19   |
+----+------+

2 rows in set (0.00 sec)
改装后
mysql> select d.id ,d.name,e.id,e.name from d left join e on d.id=e.id where d.name<>’1’ and e.id is null;
+——+———+———+———+
| id | name | id | name |
+——+———+———+———+
| 9 | 9 | NULL | NULL |
| 19 | 19 | NULL | NULL |
+——+———+———+———+
2 rows in set (0.00 sec)

16.一条语句删除重复记录的方式

number_id 和number_Pstn_Backup 相同就表示重复而id表示主键
==嵌套子查询写法

delete   k from imeet_test as k  ,
(select a.id from imeet_test a where a.id <(select max(b.id) from imeet_test b where b.number_id=a.number_id and b.number_Pstn_Backup=a.number_Pstn_Backup)
) as  m where k.id=m.id;

==相关子查询写法

explain  select a.id from imeet_test a ,(select max(b.id) id ,b.number_id,b.number_Pstn_Backup from imeet_test b group by  b.number_id, 
b.number_Pstn_Backup having count(1)>1) as c where  a.number_id= c.number_id and a.number_Pstn_Backup= c.number_Pstn_Backup
and a.id<c.id; 

17.时间取值

前一天

select now()-interval 1 day 

取上一个月的第一天

select date_sub(date_format(now(),'%y-%m-01'),interval 1 month);
select date_add(date_add(last_day(now()),interval 1 day),interval -2 month);
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day from now())-1 day),interval 1 month);

上个月的最后一天

select last_day(now()-interval 1 month);
select date_add(last_day(now()),interval -1 month);
select date_sub(date_sub(date_format(now(),'%y-%m-01'),interval 0 month),interval 1 day); 
select date_add(date_sub(date_format(now(),'%y-%m-01'),interval 0 month),interval -1 day);
select date_sub(date_sub(date_format(now(),'%y-%m-%d'),interval extract(day     from now()) day),interval 0 month) as dt

本周第一天:

select date_add(curdate(),interval (WEEKDAY(curdate()) + 1)*-1day) ;
select date_sub(curdate(),interval WEEKDAY(curdate()) + 1day) ;

本周最后一天

select date_sub(curdate(),interval WEEKDAY(curdate()) - 5 day);
select date_add(curdate(),INTERVAL (WEEKDAY(curdate()) - 5)*-1 day);

前一周的最后一天

select date_sub(curdate(),interval WEEKDAY(curdate()) + 2 day) 

其他的可以类推

18.查看分区表的执行计划

查看分区的执行计划 EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G
但是记得住不能让两个关键字PARTITIONS 和 EXTENDED 同时出现在explain里

19.备库临时表问题

slave重启的时候要检查是否有临时表show status like ‘%Slav_open_temp_tables%’ 必须为0才能够正常重启
否则可能导致报错或数据不一致

20.mysqldump注意事项

 1,mysqldump  默认不会导出 INFORMATION_SCHEMA 库,但是在5.5之后可以在db
 列表显示指定该库名并且加上 --skip-lock-tables 参数
 2,5.5.25之前mysqldump 是不导出mysql系统库的general_log 和 slow_query_log 表,而在此之后mysqldump会生成
 重建语句因此在reload 的时候这两个表不会丢失,但是表内容为空。
 3,mysqldump 可以导出为cvs 和xml格式,对于导出为xml格式对配置文件的管理是一个不错的应用

20.mysql需要的最大内存

服务器允许的最大连接数,尽量不要设置太大,因为设置太大的话容易导致内存溢出,需要通过如下公式来确定:

SET @k_bytes = 1024; SET @m_bytes = @k_bytes * 1024; SET @g_bytes = @m_bytes * 1024; 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 ) ) / @g_bytes AS MAX_MEMORY_USED_GB;

21.mysql实现oracle的count() over()

select a.*,b.c_s from c_over a ,(select d ,d2 ,count(*) c_s from c_over group by d,d2  ) as b where a.d=b.d and a.d2=b.d2 order by a.name desc
+---------------------+---------------------+------+-----+
| d                   | d2                  | name | c_s |
+---------------------+---------------------+------+-----+
| 2013-12-30 18:27:55 | 2013-12-30 18:27:55 | ee   |   2 |
| 2013-12-30 18:27:55 | 2013-12-30 18:27:55 | dd   |   2 |
| 2013-12-30 18:27:48 | 2013-12-30 18:27:44 | bb   |   2 |
| 2013-12-30 18:27:30 | 2013-12-30 18:27:30 | aa   |   1 |
| 2013-12-30 18:27:48 | 2013-12-30 18:27:44 | aa   |   2 |
+---------------------+---------------------+------+-----+

22.mysql实现oracle 的row_number() over()

select a.id ,a.d ,if(@od=a.d,@rank:=@rank+1,@rank:=1) num ,@od:=a.d from (select id ,d from number c order by c.id) as a ,(select @od:=null,@rank:=0 ) as b;                                    
+----+---------------------+------+---------------------+
| id | d                   | num  | @od:=a.d            |
+----+---------------------+------+---------------------+
|  1 | 2013-12-30 17:14:11 |    1 | 2013-12-30 17:14:11 |
|  2 | 2013-12-30 17:14:18 |    1 | 2013-12-30 17:14:18 |
|  3 | 2013-12-30 17:14:28 |    1 | 2013-12-30 17:14:28 |
|  4 | 2013-12-30 17:14:28 |    2 | 2013-12-30 17:14:28 |
|  5 | 2013-12-30 17:15:33 |    1 | 2013-12-30 17:15:33 |
|  6 | 2013-12-30 17:15:33 |    2 | 2013-12-30 17:15:33 |
|  7 | 2013-12-30 17:15:33 |    3 | 2013-12-30 17:15:33 |
+----+---------------------+------+---------------------+

23.mysqldump 的时候忽略某些表

mysqldump -uroot -pxxx  --ignore-table=db_name.table_name1 --ignore-table=db_name.table_name2 ... ... 

即每一个表需要完整的写一遍,而不是 —ignore-table=db_name.table_name1 ,db_name.table_name2 挺土鳖的

24.修改mysql默认值无需copy表,是不是终于可以在oracle dba面前嘚瑟一下了

alter table tt modify t timestamp default '0000-00-00 00:00:00';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

用modify 修改默认值 1572864 记录瞬间完成
如果你喜欢使用change那也行,多打几个字

25.查询会话时间超过10秒的top 10 SQL

select* from  PROCESSLIST where command<>"Sleep"  and time >10 AND DB is not null order by TIME desc limit 10;   

26.explain 的时候,对于子查询,优化器会先去执行子查询,得到结果集后,再将结果与其他部分进行计算。

参考: explain select sleep(2) 和 explain select * from (select sleep(2)) as a ;

27.mysql 实现时间计算迭代

select to_char(add_months(to_date('201301','yyyymm'),level-1),'yyyymm') yyyymm  from dual connect by level <13;

改写如下:

select date_format(date_add(now() ,interval f.seq month),'%Y%m')  as m from (select ifnull(@rank,0),@rank:=@rank+1 as seq from (select 1 from dual union all select 0 from dual) as a ,(select 1 from dual union all select 0 from dual) as b,(select 1 from dual union all select 0 from dual) as c,(select 1 from dual union all select 0 from dual) as d,(select @rank:=0) as e ) as f where f.seq<=12; 

结果集:

+--------+
| m      |
+--------+
| 201403 |
| 201404 |
| 201405 |
| 201406 |
| 201407 |
| 201408 |
| 201409 |
| 201410 |
| 201411 |
| 201412 |
| 201501 |
| 201502 |
+--------+

28.mysql复制延迟参数的真正含义

from : http://www.dbunix.com/?p=3348
源码:

long time_diff= ((long)(time(0) - mi->rli.last_master_timestamp)- mi->clock_diff_with_master);
mi->clock_diff_with_master=(long) (time((time_t*) 0) - strtoul(master_row[0], 0, 10));

结论:
seconds_behind_master=(slave系统时间-master执行最新event的timestamp)-(slave系统时间-master系统时间)
(slave系统时间-master执行最新event的timestamp):得到最新event到slave执行还要多久。
(slave系统时间-master系统时间):可能存在主备系统时间差别,所以计算seconds_behind_master要减去,但实际情况,slave和master系统时间基本一致,得到结果应该接近0
文档中这句误导很多人计算seconds_behind_master

If the network is slow, this is not a good approximation; the slave SQL thread may quite often be caught up with the slow-reading slave I/O thread, so Seconds_Behind_Master often shows a value of 0

29.关于INSERT INTO ON DUPLICATE KEY UPDATE的问题

很多小伙伴都知道该语句的用法是如果插入的数据和UNIQUE索引或PRIMARY KEY
重复则进行更新,但是如果是扩展插入好像不常见

INSERT INTO TABLE (a,b,c) VALUES
(1,2,3),
(4,5,6),

ON DUPLICATE KEY UPDATE b=VALUES(b);
测试数据:

select * from group_t;
+------+-----+------+
| id   | id2 | id3  |
+------+-----+------+
|    2 |   1 |   10 |
|    1 |   2 |    2 |
|    1 |   3 |    2 |
|    3 |   4 |    4 |
|    2 |   5 |   10 |
+------+-----+------+
insert into group_t values (1,1,1),(1,2,1) ON DUPLICATE KEY update id3=values(id3);
select * from group_t;
+------+-----+------+
| id   | id2 | id3  |
+------+-----+------+
|    2 |   1 |    1 |
|    1 |   2 |    1 |
|    1 |   3 |    2 |
|    3 |   4 |    4 |
|    2 |   5 |   10 |
+------+-----+------+

总结

posted on 2014-09-24 10:19  博弈悠然  阅读(319)  评论(0编辑  收藏  举报