mysql 运维小TIPS

很多mysql运维过程中的小tip能大大提高运维效率，做了点总结，之后会持续更新

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;

升级版

统计某个库下前20大表的情况

select  TABLE_SCHEMA,TABLE_NAME ,
concat(truncate(DATA_LENGTH/1024/1024,2),'MB') as table_size,
concat(truncate(INDEX_LENGTH/1024/1024,2),'MB') as index_size ,
concat(truncate(DATA_LENGTH/1024/1024+INDEX_LENGTH/1024/1024,2),'MB') as total_size 
from information_schema.tables 
where TABLE_TYPE='BASE TABLE'  
and TABLE_SCHEMA='XXX'
order by truncate(DATA_LENGTH/1024/1024+INDEX_LENGTH/1024/1024,2)  desc 
limit 20;

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 -n| 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

使用：

perl mysql_convert_table_format  --user=root --password=xxoo --socket=/tmp/mysql9999.sock  --engine=tokuDB   test (test表示库名)

需要注意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;  

11，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中的。

升级版[已经验证适用于5.6]，加入了时间显示和分段显示更友好

mysqladmin -uroot  -h 127.0.0.1 -P 4005 extended-status -i1| gawk  'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count%15 == 1){print "      Time              QPS    Com_commit   Com_rollback   Threads_con      Threads_run      TPS\n -------------------------------------------------------------------------------------------------\n";} else if($2 ~ /Aborted_clients/){now=strftime("%Y-%m-%d %T",systime());} else if ($2 ~ /Queries/){q=$4-qp;qp=$4;} else if($2 ~ /Com_commit/){com=$4-qc;qc=$4;} else if( $2 ~ /Com_rollback /){rol=$4-cr;cr=$4;} else if($2 ~ /Threads_connected/){tc=$4;} else if($2 ~ /Threads_running/  && count >= 2) {printf(" %1s %8d %8d %10d %18d %15d %10d\n" ,now,q,com,rol,tc,$4,com + rol);}}'

另外一种：

mysqladmin -uroot -pxxx -r -i 2 extended-status |awk -F "|" 'BEGIN { count=0; } { if($2 ~ /Variable_name/ && ++count%15 == 1){print "----------|---------|--- MySQL Command Status --|----- Innodb row operation -----|-- Buffer Pool Read --";    print "---Time---|---QPS---|select insert update delete|   read inserted updated deleted|   logical    physical";} else if ($2 ~ /Queries/){queries=$3;} else if ($2 ~ /Com_select /){com_select=$3;} else if ($2 ~ /Com_insert /){com_insert=$3;} else if ($2 ~ /Com_update /){com_update=$3;} else if ($2 ~ /Com_delete /){com_delete=$3;} else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;} else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;} else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;} else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;} else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;} else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;} else if ($2 ~ /Uptime / && count >= 2){  printf(" %s |%9d",strftime("%H:%M:%S"),queries);printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete);printf("|%8d %7d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted); printf("|%10d %11d\n",innodb_lor,innodb_phr);}}'

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()，dense_rank() over()

1),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 |
+----+---------------------+------+---------------------+

2), dense_rank() over()

select id  ,if(@n=name,@rank:=@rank,@rank:=@rank+1) rank , @n:=name  from t2 ,(select @n:='',@rank:=0) as a  order by name;       
+------+------+----------+
| id   | rank | @n:=name |
+------+------+----------+
|   11 |     1 |   11       |
|    1  |     1 |   11       |
|   11 |     1 |  11       |
|   12 |     2 |  222     |
|  125 |    2 | 222      |
|   13 |     3 | 333      |
|  135 |    3 | 333      |
+------+------+----------+
7 rows in set (0.00 sec)

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

重复则进行更新，但是如果是扩展插入好像不常见

1. INSERT INTO TABLE (a,b,c) VALUES
2. (1,2,3),
3. (4,5,6),
4. ON DUPLICATE KEY UPDATE b=VALUES(b);
5. 测试数据：
6. 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 |
+------+-----+------+

或 id4=valuse(id4),id5=values(id5);

31，快速查询views 导出并重建

select concat('create OR REPLACE SQL SECURITY INVOKER VIEW ',TABLE_NAME,' as ',VIEW_DEFINITION,';') AS def from information_schema.views \G

注意这个的 SQL SECURITY INVOKER （不是definer)主要是为了防止删除definer账号后导致视图不可访问的问题

32，对少量数据如何快速去重复

create table  uni(id int ,name varchar(20));
 insert into uni values(1,'1'),(1,'1');

对id 和name创建唯一索引注意ignore 即如果遇到重复就删除并忽略该错误

alter ignore table tt add unique index ind_id (id,name);
 或

对id，或name单独创建唯一索引

alter ignore table uni add unique index ind_name (name);    
Query OK, 2 rows affected (0.06 sec)               
Records: 2  Duplicates: 1  Warnings: 0

查询结果：

select * from uni;
+------+------+
| id   | name |
+------+------+
|    1 | 1    |
+------+------+

总结：alter ignore table 创建唯一索引的情况可以对小数据量快速去重复

33，有可能是replace into

replace into 在真存在冲突的时候对基于row的复制备库可能出现自增属性和主库不一致的情况

34，mysql时间转秒数以及秒数转时间的方法

MariaDB [test]> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
|            1395369570 |
+-----------------------+
1 row in set (0.00 sec)

MariaDB [test]> select from_unixtime(1395369176) ;
+---------------------------+
| from_unixtime(1395369176) |
+---------------------------+
| 2014-03-21 10:32:56       |
+---------------------------+
1 row in set (0.00 sec)

35 ，如何获取mysql server 当前的调试信息或lock信息

mysqladmin  -uroot -pxxx  debug 

执行完后查看错误日志即可

tail -250 /var/log/mysqld.log 

36，查看外键情况 

select  CONSTRAINT_SCHEMA,CONSTRAINT_NAME 外键,TABLE_NAME 子表,REFERENCED_TABLE_NAME 父表 
    from  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ;

37，binlog row 格式下，如何查看执行的语句

 mysqlbinlog   --base64-output='DECODE-ROWS' --verbose mysql-bin.000004 

38，使用pt工具修改表结构

 pt-online-schema-change --user=root --ask-pass --port=3306  --host=127.0.0.1 \
       --charset=utf8 --critical-load Threads_running=60  --check-interval=3 --max-lag=10 --chunk-time=0.5 --statistics \
       --set-vars="wait_timeout=10000 innodb_lock_wait_timeout=300 lock_wait_timeout=300" \
       --nocheck-replication-filters --alter "add pt varchar(20) not null default '1234'" D=test,t=vb   --dry-run
        or
        --execute

39，如果因为连接数设置太小而无法连接可尝试如下方式暴力设置

 gdb -p `pidstat |grep -w mysqld |awk '{print $3}'` -ex "set max_connections=9999" -batch

40，从某个库或表里构造出索引创建的语句

(user:root  time: 13:03 port:3306)[db: lidan] desc e2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| fname | varchar(30) | YES  |     | NULL    |       |
| lname | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

(user:root  time: 13:03 port:3306)[db: lidan]create index ind_name on e2(fname,lname);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

构造索引创建语句的sql如下：
(user:monitor  time: 13:23 port: 1234)[db: information_schema]select concat('alter table  ',TABLE_SCHEMA,'.',TABLE_NAME,' add index ',INDEX_NAME,' on (',group_concat(COLUMN_NAME order by null),');') as create_index from information_schema.STATISTICS where table_schema='lidan' and INDEX_NAME<>'PRIMARY' group by INDEX_NAME order by SEQ_IN_INDEX ;
+---------------------------------------------------------------------------------+
| create_index                                                                                     |
+---------------------------------------------------------------------------------+
| alter table  lidan.e2 add index ind_name on (fname,lname); |
+---------------------------------------------------------------------------------+

上述构造语句适用于schema基本的，如果要构造某个表的，则在where 后面加table_name='xxx' 即可xxx表示表名

更方便的方式还可以将结果重定向到文件：

select concat('alter table  ',TABLE_SCHEMA,'.',TABLE_NAME,' add index ',INDEX_NAME,' on (',group_concat(COLUMN_NAME order by null),');') as create_indexinto outfile '/tmp/create.sql'from information_schema.STATISTICS where table_schema='lidan' and INDEX_NAME<>'PRIMARY' group by INDEX_NAME order by SEQ_IN_INDEX ; 

然后将文件拷贝到要创建索引的机器导入即可。

41，使用load data加载数据的时候指定字段

LOAD DATA   INFILE '/tmp/xxx.csv'      INTO TABLE HAHA     (col1,col2,col3)   ;   #这后面不用指定fileds的分隔符否则会报语法错误

42，如果分割字符串

reverse(substring_index(reverse(substring_index("www.mmm.nnn.kkk",".",@position)),".",1));

其中@position表示你要取第几个字符串，要算一共几个字符串可以使用

select (length("www.mmm.nnn.kkk")-length(replace("www.mmm.nnn.kkk",".","")))+1 as num;
+------+
| num  |
+------+
|    4 |
+------+

然后做一个while 循环 从1~4 带入@position即可

43，mysql中insert 函数的用法

select insert("3332222",2,2,2) 

表示 将“3332222” 这个字符 从第二个字符开始替换两个 用什么替换呢，用2替换

那么结果就是 322222 

未完待续...