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
) 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
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:[email protected]+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:[email protected]+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複制延遲參數的真正含義
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 |
+------+-----+------+
總結