文章目录
  1. 1. MySQL运维过程中的小 tips
    1. 1.1. 查询数据库不同引擎下表和索引的大小
    2. 1.2. 查询数据库用户的状态
    3. 1.3. mysql>=5.5 查询锁阻塞的情况
    4. 1.4. 抓包获取协议包中的sql语句
    5. 1.5. 批量修改存储引擎
    6. 1.6. mysql 快速创建空表
    7. 1.7. 观察mysql status 变量的变化情况
    8. 1.8. left join 条件的区别
    9. 1.9. 多表关联删除
    10. 1.10. 多表更新
    11. 1.11. myisam表count的优化
    12. 1.12. 12.group_concat 函数的使用
    13. 1.13. 杀死当前用户bbs正在运行的连接)
    14. 1.14. 脚本实现qps,tps的查看
    15. 1.15. not in 改写
    16. 1.16. 删除重复记录
    17. 1.17. 时间取值
    18. 1.18. 查看分区表的执行计划
    19. 1.19. 备库临时表问题
    20. 1.20. mysqldump注意事项
    21. 1.21. MySQL需要的最大内存
    22. 1.22. 实现oracle的count() over()
    23. 1.23. 实现oracle 的row_number() over()
    24. 1.24. mysqldump 的时候忽略某些表
    25. 1.25. 修改mysql默认值无需copy表
    26. 1.26. 查询会话时间超过10秒SQL
    27. 1.27. 实现时间计算迭代
    28. 1.28. 复制延迟参数的含义
    29. 1.29. INSERT INTO ON DUPLICATE KEY UPDATE的问题
  2. 2. 总结

MySQL运维过程中的小 tips

查询数据库不同引擎下表和索引的大小

1
2
3
4
5
6
7
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;

查询数据库用户的状态

1
2
3
    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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 |
=======================================================

抓包获取协议包中的sql语句

1
2
/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)

批量修改存储引擎

1
2
3
/usr/local/mysql/bin/mysql_convert_table_format
-- 将 28 行 $opt_type ---> $opt_engine
-- 需要注意innodb 的外键和myisam的fulltext index

mysql 快速创建空表

在MYSQL中有两种方法。

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

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

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

观察mysql status 变量的变化情况

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

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

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

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

left join 条件的区别

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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之前执行。

多表关联删除

删除表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;

多表更新

只更新表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'

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
那么函数就是对列的所有值进行连接

杀死当前用户bbs正在运行的连接)

已验证

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

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

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

脚本实现qps,tps的查看

1
2
3
4
5
6
7
8
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的参数增加了很多需要修改

1
2
3
4
5
6
7
8
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中的。

not in 改写

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    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)

删除重复记录

number_id 和number_Pstn_Backup 相同就表示重复而id表示主键

==嵌套子查询写法

1
2
3
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;

==相关子查询写法

1
2
3
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;

时间取值

前一天

select now()-interval 1 day 

取上一个月的第一天

1
2
3
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);

上个月的最后一天

1
2
3
4
5
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

本周第一天:

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

本周最后一天

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

前一周的最后一天

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

其他的可以类推

查看分区表的执行计划

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

备库临时表问题

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

mysqldump注意事项

1
2
3
4
5
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格式对配置文件的管理是一个不错的应用

MySQL需要的最大内存

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

1
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;

实现oracle的count() over()

1
2
3
4
5
6
7
8
9
10
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 |
+---------------------+---------------------+------+-----+

实现oracle 的row_number() over()

1
2
3
4
5
6
7
8
9
10
11
12
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 |
+----+---------------------+------+---------------------+

mysqldump 的时候忽略某些表

1
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 挺土鳖的

修改mysql默认值无需copy表

1
2
3
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那也行,多打几个字

查询会话时间超过10秒SQL

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

实现时间计算迭代

1
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;

结果集:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
+--------+
| m |
+--------+
| 201403 |
| 201404 |
| 201405 |
| 201406 |
| 201407 |
| 201408 |
| 201409 |
| 201410 |
| 201411 |
| 201412 |
| 201501 |
| 201502 |
+--------+

复制延迟参数的含义

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

1
2
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

1
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

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);
测试数据:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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 |
+------+-----+------+

总结

依旧在路上 ...
文章目录
  1. 1. MySQL运维过程中的小 tips
    1. 1.1. 查询数据库不同引擎下表和索引的大小
    2. 1.2. 查询数据库用户的状态
    3. 1.3. mysql>=5.5 查询锁阻塞的情况
    4. 1.4. 抓包获取协议包中的sql语句
    5. 1.5. 批量修改存储引擎
    6. 1.6. mysql 快速创建空表
    7. 1.7. 观察mysql status 变量的变化情况
    8. 1.8. left join 条件的区别
    9. 1.9. 多表关联删除
    10. 1.10. 多表更新
    11. 1.11. myisam表count的优化
    12. 1.12. 12.group_concat 函数的使用
    13. 1.13. 杀死当前用户bbs正在运行的连接)
    14. 1.14. 脚本实现qps,tps的查看
    15. 1.15. not in 改写
    16. 1.16. 删除重复记录
    17. 1.17. 时间取值
    18. 1.18. 查看分区表的执行计划
    19. 1.19. 备库临时表问题
    20. 1.20. mysqldump注意事项
    21. 1.21. MySQL需要的最大内存
    22. 1.22. 实现oracle的count() over()
    23. 1.23. 实现oracle 的row_number() over()
    24. 1.24. mysqldump 的时候忽略某些表
    25. 1.25. 修改mysql默认值无需copy表
    26. 1.26. 查询会话时间超过10秒SQL
    27. 1.27. 实现时间计算迭代
    28. 1.28. 复制延迟参数的含义
    29. 1.29. INSERT INTO ON DUPLICATE KEY UPDATE的问题
  2. 2. 总结