binlog row模式delete误删除恢复

MySQLgaoquan 发表了文章 • 2 个评论 • 401 次浏览 • 2016-01-18 10:50 • 来自相关话题

场景:某DBA在数据操作过程中,误将db.t1执行了一次delete from db.t1;,此数据库配置的格式为row,现在需要利用binlog将该误操作进行恢复。

1、获取删除语句在binlog中的大概位置

#切换下二进制日志

mysql >flush logs;

#将二进制binlog文件转换为文本文件

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS ./mysql-bin.000005>temp.log

#找到该操作所对应的事务的开始和结束,然后将日志重定向到另一个文件delete.log

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=120 --stop-position=401 ./mysql-bin.000005>delete.log

#使用Python脚本提取恢复insert语句:

python delete.py "./delete.log" 6

#提取出的结果如下:


 INSERT INTO `test`.`t1`   SELECT    ,1    ,'gao'    ,'quan'    ,31;

 INSERT INTO `test`.`t1`   SELECT    ,2    ,'wang'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,3    ,'wan'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,4    ,'qqq'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,5    ,'qqq'    ,'ssss'    ,32;

#脚本如下




# -*- coding: utf-8 -*-

import re

#列表拆分

def div_list(ls,n):

    n = int(n)

    ls_len = len(ls)

    j = ls_len/n

    ls_return = []

    for i in range(0,j):

        ls_return.append(ls[i*n:(n*(i+1))])

    return ls_return

#col_count为表的列数+2,binlog为mysqlbinlog -v处理过后的文本

def exc_binlog(binlog,col_count):

    with open(binlog) as f:

        lines=[]

        for line in f:

            if re.search('###',line):

                line=re.sub("\(\d+\)","",re.sub("\@\d+\=",",",line.strip().replace("### "," ").replace("DELETE FROM ","INSERT INTO ").replace("WHERE"," SELECT")))

                lines.append(line)




        list=div_list(lines,col_count)




    with open(r"./copy.log","w") as f:

        for i in range(0,len(list)):

            list1=list[i]

            line=" ".join(list1).replace("SELECT     ,","SELECT ")+";"+"\n"

            f.writelines(line)




if __name__ == '__main__':

    import sys

    exc_binlog(sys.argv[1],sys.argv[2]) 查看全部
场景:某DBA在数据操作过程中,误将db.t1执行了一次delete from db.t1;,此数据库配置的格式为row,现在需要利用binlog将该误操作进行恢复。

1、获取删除语句在binlog中的大概位置

#切换下二进制日志

mysql >flush logs;

#将二进制binlog文件转换为文本文件

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS ./mysql-bin.000005>temp.log

#找到该操作所对应的事务的开始和结束,然后将日志重定向到另一个文件delete.log

/usr/local/mysql/bin/mysqlbinlog -v --base64-output=DECODE-ROWS --start-position=120 --stop-position=401 ./mysql-bin.000005>delete.log

#使用Python脚本提取恢复insert语句:

python delete.py "./delete.log" 6

#提取出的结果如下:


 INSERT INTO `test`.`t1`   SELECT    ,1    ,'gao'    ,'quan'    ,31;

 INSERT INTO `test`.`t1`   SELECT    ,2    ,'wang'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,3    ,'wan'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,4    ,'qqq'    ,'qua1'    ,32;

 INSERT INTO `test`.`t1`   SELECT    ,5    ,'qqq'    ,'ssss'    ,32;

#脚本如下




# -*- coding: utf-8 -*-

import re

#列表拆分

def div_list(ls,n):

    n = int(n)

    ls_len = len(ls)

    j = ls_len/n

    ls_return = []

    for i in range(0,j):

        ls_return.append(ls[i*n:(n*(i+1))])

    return ls_return

#col_count为表的列数+2,binlog为mysqlbinlog -v处理过后的文本

def exc_binlog(binlog,col_count):

    with open(binlog) as f:

        lines=[]

        for line in f:

            if re.search('###',line):

                line=re.sub("\(\d+\)","",re.sub("\@\d+\=",",",line.strip().replace("### "," ").replace("DELETE FROM ","INSERT INTO ").replace("WHERE"," SELECT")))

                lines.append(line)




        list=div_list(lines,col_count)




    with open(r"./copy.log","w") as f:

        for i in range(0,len(list)):

            list1=list[i]

            line=" ".join(list1).replace("SELECT     ,","SELECT ")+";"+"\n"

            f.writelines(line)




if __name__ == '__main__':

    import sys

    exc_binlog(sys.argv[1],sys.argv[2])

MySQL优化索引-----定位需要优化的索引( 我的SQL支持5.6,5.7,mariadb。另下面附了姜总原文中的SQL支持5.6外的环境)

MySQLhuzilin 发表了文章 • 17 个评论 • 1602 次浏览 • 2016-01-18 10:33 • 来自相关话题

前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。 查看全部
前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。

监控磁盘I/O

Linuxggqqmmxxpp 发表了文章 • 2 个评论 • 475 次浏览 • 2016-01-15 16:32 • 来自相关话题

在很多服务器上,磁盘I/O往往会成为性能的瓶颈,监控磁盘的I/O成为了非常关键的工作。
监控I/O的命令iostat,iostat -dmx:
     -d是显示磁盘的使用状态,
     -m统计数据显示,以每秒兆字节,而不是块或千字节每秒。显示的数据只与内核2.4及更高版本有效。 
     -x显示扩展信息。
     
     Linux 2.6.32-358.el6.x86_64 (ck)    01/15/2016      _x86_64_        (48 CPU)
    Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
    sdc               0.24        36.95     1.35    2.18     0.13     0.15   163.59         0.01        3.48   0.43   0.15
    dm-0             5.18       832.51    412.64  122.02    16.36     5.10    82.22     0.04    0.08   0.12   6.58
    dm-1              0.00      0.00        417.82  954.54    16.36     5.10    32.03     0.03    0.08   0.05   6.59
    dm-2              0.00     0.00         417.82  948.78    16.36     5.10    32.17     0.03    0.00   0.05   6.60
    sda               0.00     0.00          58.53   12.62     2.26     0.62    82.92     0.03    0.41   0.63   4.51
    sdd               0.00     0.00          58.53   12.62     2.26     0.62    82.90     0.03    0.40   0.63   4.51
    sdb               0.00     0.00          58.54   12.61     2.26     0.62    82.95     0.02    0.21   0.60   4.28
    sde               0.00     0.00          58.53   12.62     2.26     0.62    82.91     0.02    0.24   0.61   4.32 

    rrqm/s     是每秒读请求的数量。
    wrqm/s    是每秒钟写请求的数量。
    r/s            每秒钟读请求完成的数量。
    w/s           每秒钟写请求完成的数量。
    rMB/s       每秒从设备读取的兆字节数。
    wMB/s      每秒从设备写入的兆字节数。
    avgrq-sz    平均请求扇区的大小。
    avgqu-sz   平均请求队列的长度。
    await         平均每次请求等待的时间。 
    svctm        平均每次请求的服务时间,本次完整IO从开始到结束响应的时间。
    %util          设备利用率。 
    
    关键是这几个参数: r/s,w/s,await,svctm,%util。
    await这列通常不超过10,svctm这列通常不超过5,util这列越低越好,最好低于50%。

    sar -d
    Linux 2.6.32-358.el6.x86_64 (ck)    01/15/2016      _x86_64_        (48 CPU)
12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
12:10:01 AM   dev8-32      4.17     51.66     41.07     22.25      0.00      1.13      0.72      0.30
12:10:01 AM  dev253-0    268.69 131485.97   1043.90    493.25      0.74      2.76      0.82     22.15
12:10:01 AM  dev253-1    389.75 131483.37   1043.90    340.03      2.05      5.24      0.57     22.17
12:10:01 AM  dev253-2    388.59 131484.24   1043.90    341.05      2.04      5.26      0.57     22.18
12:10:01 AM    dev8-0     67.46  32881.41    258.19    491.22      0.18      2.74      2.66     17.92
12:10:01 AM   dev8-48     67.46  32868.36    262.30    491.09      0.18      2.73      2.63     17.75
12:10:01 AM   dev8-16     67.46  32867.25    256.15    490.98      0.17      2.49      2.40     16.20
12:10:01 AM   dev8-64     67.46  32868.96    267.26    491.18      0.17      2.47      2.39     16.09
12:20:01 AM   dev8-32     68.08     11.61  30696.92    451.04      1.09     16.04      0.30      2.03
12:20:01 AM  dev253-0    270.34 136112.01   1075.79    507.47      0.62      2.31      0.73     19.61
12:20:01 AM  dev253-1    402.33 136112.88   1075.79    340.99      1.09      2.71      0.49     19.63
12:20:01 AM  dev253-2    401.26 136112.01   1075.79    341.89      1.09      2.72      0.49     19.64
12:20:01 AM    dev8-0     67.85  34023.85    268.54    505.41      0.15      2.25      2.22     15.07
12:20:01 AM   dev8-48     67.85  34041.28    273.41    505.73      0.15      2.23      2.20     14.96
12:20:01 AM   dev8-16     67.85  34023.80    281.65    505.61      0.14      2.13      2.10     14.28
12:20:01 AM   dev8-64     67.85  34023.08    252.18    505.17      0.14      2.13      2.10     14.26
12:30:01 AM   dev8-32      2.21      0.00     38.06     17.25      0.00      0.58      0.31      0.07
12:30:01 AM  dev253-0    129.14  83063.41    116.79    644.11      0.41      3.17      1.37     17.72
12:30:01 AM  dev253-1    178.60  83061.68    116.79    465.73      0.59      3.29      0.99     17.74
12:30:01 AM  dev253-2    177.58  83061.68    116.79    468.40      0.59      3.31      1.00     17.75
12:30:01 AM    dev8-0     32.54  20821.80     30.72    640.87      0.10      3.05      3.05      9.92
12:30:01 AM   dev8-48     32.54  20744.40     29.76    638.46      0.10      3.08      3.08     10.02
12:30:01 AM   dev8-16     32.54  20793.47     28.81    639.91      0.10      2.93      2.92      9.51
12:30:01 AM   dev8-64     32.54  20703.74     27.51    637.08      0.09      2.89      2.89      9.39
tps也就是iops,当然是越高越好,不过当iops从4000-5000,一下子到10000了,就要考虑是不是因为索引导致的,iops就是每秒读写多少个4KB。

rd_sec/s:每秒读取扇区的数量,每个扇区是512字节。



bi=block in to mem from disk从磁盘读取字节放到内存,一个block4KB ,I/O读


bo=block out from mem to disk 从内存读取写到磁盘的字节数,I/O写  查看全部

在很多服务器上,磁盘I/O往往会成为性能的瓶颈,监控磁盘的I/O成为了非常关键的工作。
监控I/O的命令iostat,iostat -dmx:
     -d是显示磁盘的使用状态,
     -m统计数据显示,以每秒兆字节,而不是块或千字节每秒。显示的数据只与内核2.4及更高版本有效。 
     -x显示扩展信息。
     
     Linux 2.6.32-358.el6.x86_64 (ck)    01/15/2016      _x86_64_        (48 CPU)
    Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
    sdc               0.24        36.95     1.35    2.18     0.13     0.15   163.59         0.01        3.48   0.43   0.15
    dm-0             5.18       832.51    412.64  122.02    16.36     5.10    82.22     0.04    0.08   0.12   6.58
    dm-1              0.00      0.00        417.82  954.54    16.36     5.10    32.03     0.03    0.08   0.05   6.59
    dm-2              0.00     0.00         417.82  948.78    16.36     5.10    32.17     0.03    0.00   0.05   6.60
    sda               0.00     0.00          58.53   12.62     2.26     0.62    82.92     0.03    0.41   0.63   4.51
    sdd               0.00     0.00          58.53   12.62     2.26     0.62    82.90     0.03    0.40   0.63   4.51
    sdb               0.00     0.00          58.54   12.61     2.26     0.62    82.95     0.02    0.21   0.60   4.28
    sde               0.00     0.00          58.53   12.62     2.26     0.62    82.91     0.02    0.24   0.61   4.32 

    rrqm/s     是每秒读请求的数量。
    wrqm/s    是每秒钟写请求的数量。
    r/s            每秒钟读请求完成的数量。
    w/s           每秒钟写请求完成的数量。
    rMB/s       每秒从设备读取的兆字节数。
    wMB/s      每秒从设备写入的兆字节数。
    avgrq-sz    平均请求扇区的大小。
    avgqu-sz   平均请求队列的长度。
    await         平均每次请求等待的时间。 
    svctm        平均每次请求的服务时间,本次完整IO从开始到结束响应的时间。
    %util          设备利用率。 
    
    关键是这几个参数: r/s,w/s,await,svctm,%util。
    await这列通常不超过10,svctm这列通常不超过5,util这列越低越好,最好低于50%。

    sar -d
    Linux 2.6.32-358.el6.x86_64 (ck)    01/15/2016      _x86_64_        (48 CPU)
12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
12:10:01 AM   dev8-32      4.17     51.66     41.07     22.25      0.00      1.13      0.72      0.30
12:10:01 AM  dev253-0    268.69 131485.97   1043.90    493.25      0.74      2.76      0.82     22.15
12:10:01 AM  dev253-1    389.75 131483.37   1043.90    340.03      2.05      5.24      0.57     22.17
12:10:01 AM  dev253-2    388.59 131484.24   1043.90    341.05      2.04      5.26      0.57     22.18
12:10:01 AM    dev8-0     67.46  32881.41    258.19    491.22      0.18      2.74      2.66     17.92
12:10:01 AM   dev8-48     67.46  32868.36    262.30    491.09      0.18      2.73      2.63     17.75
12:10:01 AM   dev8-16     67.46  32867.25    256.15    490.98      0.17      2.49      2.40     16.20
12:10:01 AM   dev8-64     67.46  32868.96    267.26    491.18      0.17      2.47      2.39     16.09
12:20:01 AM   dev8-32     68.08     11.61  30696.92    451.04      1.09     16.04      0.30      2.03
12:20:01 AM  dev253-0    270.34 136112.01   1075.79    507.47      0.62      2.31      0.73     19.61
12:20:01 AM  dev253-1    402.33 136112.88   1075.79    340.99      1.09      2.71      0.49     19.63
12:20:01 AM  dev253-2    401.26 136112.01   1075.79    341.89      1.09      2.72      0.49     19.64
12:20:01 AM    dev8-0     67.85  34023.85    268.54    505.41      0.15      2.25      2.22     15.07
12:20:01 AM   dev8-48     67.85  34041.28    273.41    505.73      0.15      2.23      2.20     14.96
12:20:01 AM   dev8-16     67.85  34023.80    281.65    505.61      0.14      2.13      2.10     14.28
12:20:01 AM   dev8-64     67.85  34023.08    252.18    505.17      0.14      2.13      2.10     14.26
12:30:01 AM   dev8-32      2.21      0.00     38.06     17.25      0.00      0.58      0.31      0.07
12:30:01 AM  dev253-0    129.14  83063.41    116.79    644.11      0.41      3.17      1.37     17.72
12:30:01 AM  dev253-1    178.60  83061.68    116.79    465.73      0.59      3.29      0.99     17.74
12:30:01 AM  dev253-2    177.58  83061.68    116.79    468.40      0.59      3.31      1.00     17.75
12:30:01 AM    dev8-0     32.54  20821.80     30.72    640.87      0.10      3.05      3.05      9.92
12:30:01 AM   dev8-48     32.54  20744.40     29.76    638.46      0.10      3.08      3.08     10.02
12:30:01 AM   dev8-16     32.54  20793.47     28.81    639.91      0.10      2.93      2.92      9.51
12:30:01 AM   dev8-64     32.54  20703.74     27.51    637.08      0.09      2.89      2.89      9.39
tps也就是iops,当然是越高越好,不过当iops从4000-5000,一下子到10000了,就要考虑是不是因为索引导致的,iops就是每秒读写多少个4KB。

rd_sec/s:每秒读取扇区的数量,每个扇区是512字节。



bi=block in to mem from disk从磁盘读取字节放到内存,一个block4KB ,I/O读


bo=block out from mem to disk 从内存读取写到磁盘的字节数,I/O写 

监控CPU、內存

Linuxggqqmmxxpp 发表了文章 • 1 个评论 • 452 次浏览 • 2016-01-15 16:30 • 来自相关话题

监控CPU是日常主要工作的一项,因为CPU利用率很高,有可能是服务器上可能有很多计算的SQL语句,也可能是索引没有建好或者没有建立索引导致。
        
         用top命令可以查出当前CPU使用情况。
         top - 14:29:42 up 454 days, 19:36, 33 users,  load average: 2.02, 1.70, 1.58
        Tasks: 1421 total,   1 running, 1419 sleeping,   1 stopped,   0 zombie
        Cpu(s):  1.2%us,  0.1%sy,  0.0%ni, 97.5%id,  1.1%wa,  0.0%hi,  0.0%si,  0.0%st
        Mem:  132103620k total, 117597200k used, 14506420k free,   234836k buffers
        Swap: 37887992k total,  1111756k used, 36776236k free, 11139408k cached

        load average:負載情況,如果說性能是服務器能夠跑多快,那麼,負載就是能夠跑多久。
        load average: 2.02, 1.70, 1.58 這個是1分鐘、5分鐘、15分鐘的負載情況。
        該數據是根據是每間隔5秒鐘計算一次,根據活躍進程數計算出來的,如果這個值除以CPU的數目超過5說明超負荷了。
        
        CPU使用率很高原因可能是因為索引,IO使用率很高也可能是索引問題,可以考慮把訪問頻繁的數據放到redis中進行緩存,可以降低CPU、IO的負載。
        CPU一些衡量指標: 用户user%<10% 、wait%<5%、idle%<80%、IOWAIT<5%。
    
sar -p 也可以查看CPU使用情況
    
01:30:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:40:01 PM     all          0.56      0.00      0.17      1.73      0.00     97.55
01:50:01 PM     all          0.37      0.00      0.16      1.89      0.00     97.57
02:00:01 PM     all          1.02      0.00      0.18      1.29      0.00     97.52
02:10:01 PM     all          1.47      0.00      0.22      0.83      0.00     97.47
02:20:01 PM     all          2.13      0.00      0.26      0.56      0.00     97.04
02:30:01 PM     all          1.93      0.00      0.23      0.70      0.00     97.14
02:40:01 PM     all          1.75      0.00      0.17      0.83      0.00     97.25
02:50:01 PM     all          1.57      0.00      0.15      0.80      0.00     97.49
03:00:01 PM     all          1.55      0.00      0.14      0.76      0.00     97.55
03:10:01 PM     all          3.37      0.00      0.29      0.86      0.00     95.48
03:20:01 PM     all          2.96      0.00      0.26      0.82      0.00     95.96
Average:           all          2.17      0.00      0.30      0.42      0.00     97.12 

   %user是用戶CPU使用率,%system是系統CPU使用率。
   如果%user很高說明,用戶進程負載很高,可能是SQL語句的問題,可以在MySQL中用show full processlist\G來查看SQL語句執行情況,從而選擇語句進行explain查看,需要改寫的改寫,需要優化索引的優化索引。
   如果%system很高可能會導致中斷。IO比较差或者发生频繁SWAP还有网络IO很高,除了IDLE任何一列超过10说明很高,需要关注。

  內存:free -m即可監控。     
  主要關注cached、used,如果cached低,used高要注意了,可能是內存洩漏,就是沒有及時釋放使用完畢的內存。

    sar -r也可以查看內存使用情況:
    01:30:01 PM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
    01:40:01 PM  15608224 116495396     88.18    228912  10124336 107304284     63.12
    01:50:01 PM  15028680 117074940     88.62    230884  10681796 107303308     63.12
    02:00:01 PM  15007156 117096464     88.64    231896  10703256 107321572     63.13
    02:10:01 PM  14515268 117588352     89.01    232040  11143528 107349252     63.15
    02:20:01 PM  14505052 117598568     89.02    233420  11146696 107360896     63.16
    02:30:01 PM  14507368 117596252     89.02    234868  11139748 107366216     63.16
    02:40:01 PM  14487060 117616560     89.03    236304  11110168 107405800     63.18
    02:50:01 PM  14572496 117531124     88.97    237668  11050164 107371496     63.16
    03:00:01 PM  14587436 117516184     88.96    239220  11033556 107360344     63.16
    03:10:01 PM  15703372 116400248     88.11    236808   9932424 107357368     63.15
    03:20:01 PM  16501268 115602352     87.51    236868   9172400 107355456     63.15
    03:30:01 PM  16367920 115735700     87.61    239096   9285328 107386628     63.17
        Average:     17878881 114224739     86.47    179138   8503225 107470051    63.22
  查看全部
监控CPU是日常主要工作的一项,因为CPU利用率很高,有可能是服务器上可能有很多计算的SQL语句,也可能是索引没有建好或者没有建立索引导致。
        
         用top命令可以查出当前CPU使用情况。
         top - 14:29:42 up 454 days, 19:36, 33 users,  load average: 2.02, 1.70, 1.58
        Tasks: 1421 total,   1 running, 1419 sleeping,   1 stopped,   0 zombie
        Cpu(s):  1.2%us,  0.1%sy,  0.0%ni, 97.5%id,  1.1%wa,  0.0%hi,  0.0%si,  0.0%st
        Mem:  132103620k total, 117597200k used, 14506420k free,   234836k buffers
        Swap: 37887992k total,  1111756k used, 36776236k free, 11139408k cached

        load average:負載情況,如果說性能是服務器能夠跑多快,那麼,負載就是能夠跑多久。
        load average: 2.02, 1.70, 1.58 這個是1分鐘、5分鐘、15分鐘的負載情況。
        該數據是根據是每間隔5秒鐘計算一次,根據活躍進程數計算出來的,如果這個值除以CPU的數目超過5說明超負荷了。
        
        CPU使用率很高原因可能是因為索引,IO使用率很高也可能是索引問題,可以考慮把訪問頻繁的數據放到redis中進行緩存,可以降低CPU、IO的負載。
        CPU一些衡量指標: 用户user%<10% 、wait%<5%、idle%<80%、IOWAIT<5%。
    
sar -p 也可以查看CPU使用情況
    
01:30:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle
01:40:01 PM     all          0.56      0.00      0.17      1.73      0.00     97.55
01:50:01 PM     all          0.37      0.00      0.16      1.89      0.00     97.57
02:00:01 PM     all          1.02      0.00      0.18      1.29      0.00     97.52
02:10:01 PM     all          1.47      0.00      0.22      0.83      0.00     97.47
02:20:01 PM     all          2.13      0.00      0.26      0.56      0.00     97.04
02:30:01 PM     all          1.93      0.00      0.23      0.70      0.00     97.14
02:40:01 PM     all          1.75      0.00      0.17      0.83      0.00     97.25
02:50:01 PM     all          1.57      0.00      0.15      0.80      0.00     97.49
03:00:01 PM     all          1.55      0.00      0.14      0.76      0.00     97.55
03:10:01 PM     all          3.37      0.00      0.29      0.86      0.00     95.48
03:20:01 PM     all          2.96      0.00      0.26      0.82      0.00     95.96
Average:           all          2.17      0.00      0.30      0.42      0.00     97.12 

   %user是用戶CPU使用率,%system是系統CPU使用率。
   如果%user很高說明,用戶進程負載很高,可能是SQL語句的問題,可以在MySQL中用show full processlist\G來查看SQL語句執行情況,從而選擇語句進行explain查看,需要改寫的改寫,需要優化索引的優化索引。
   如果%system很高可能會導致中斷。IO比较差或者发生频繁SWAP还有网络IO很高,除了IDLE任何一列超过10说明很高,需要关注。

  內存:free -m即可監控。     
  主要關注cached、used,如果cached低,used高要注意了,可能是內存洩漏,就是沒有及時釋放使用完畢的內存。

    sar -r也可以查看內存使用情況:
    01:30:01 PM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit
    01:40:01 PM  15608224 116495396     88.18    228912  10124336 107304284     63.12
    01:50:01 PM  15028680 117074940     88.62    230884  10681796 107303308     63.12
    02:00:01 PM  15007156 117096464     88.64    231896  10703256 107321572     63.13
    02:10:01 PM  14515268 117588352     89.01    232040  11143528 107349252     63.15
    02:20:01 PM  14505052 117598568     89.02    233420  11146696 107360896     63.16
    02:30:01 PM  14507368 117596252     89.02    234868  11139748 107366216     63.16
    02:40:01 PM  14487060 117616560     89.03    236304  11110168 107405800     63.18
    02:50:01 PM  14572496 117531124     88.97    237668  11050164 107371496     63.16
    03:00:01 PM  14587436 117516184     88.96    239220  11033556 107360344     63.16
    03:10:01 PM  15703372 116400248     88.11    236808   9932424 107357368     63.15
    03:20:01 PM  16501268 115602352     87.51    236868   9172400 107355456     63.15
    03:30:01 PM  16367920 115735700     87.61    239096   9285328 107386628     63.17
        Average:     17878881 114224739     86.47    179138   8503225 107470051    63.22
 

MySQL 插入数据的几种方法

MySQLgaoguilong 发表了文章 • 2 个评论 • 323 次浏览 • 2016-01-14 20:24 • 来自相关话题

 环境:5.6.20-enterprise-commercial-advanced MySQL Enterprise Server
CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `IX_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1)插入数据
INSERT INTO
INSERT INTO test(b, c) VALUES (1, 1), (2, 2),(3, 3),(4, 4);
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
此时如果我们继续执行下的语句,就会出现重复的插入唯一索引的错误提示
INSERT INTO test(b, c) VALUES(1, 2);
Result: Error Code: 1062. Duplicate entry '1' for key 'IX_b'
因此,我们可以利用
INSERT IGNORE INTO
REPLACE INTO
 INSERT INTO ON DUPLICATE KEY UPDATE
来避免出现
1)INSERT IGNORE INTO
mysql> INSERT IGNORE INTO test(b, c) VALUES(1, 2);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
mysql> INSERT IGNORE INTO test(a, b) VALUES(1, 5);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键或者唯一是否存在,如果存在直接忽略,如果不存在,则直接插入数据。
2)REPLACE INTO
mysql> REPLACE INTO test(b,c) VALUES (1, 2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
| 5 | 1 |    2 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查唯一索引是否存在,检查存在b=1的记录,删除a=1,b=1,c=1这条记录,然后添加a=5,b=1, c=2的记录。如果不存在,则直接插入数据。
上面运行语句相当于:
DELETE FROM test
WHERE b = 1
;
INSERT INTO test(b,c) VALUES (1, 2)
;
如果执行
mysql> REPLACE INTO test(a,b) VALUES (1, 5);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 5 | NULL |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键是否存在,检查存在a=1的记录,删除a=1,b=1,c=1这条记录,然后添加a=1,b=b, c取默认值的记录。如果不存在,则直接插入数据。
上面的语句相当于
DELETE FROM test
WHERE b = 1
;
INSERT INTO test(a, b) VALUES (1, 5)
;
3) INSERT INTO ON DUPLICATE KEY UPDATE
mysql> INSERT INTO test(b, c) VALUES(1, 2) ON DUPLICATE KEY UPDATE c = 7;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    7 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键或者唯一是否存在,检查存在b=1的记录,将c的值更新为7。如果不存在,则直接插入数据。关于影响行数为什么是2呢?MySQL手册: the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.
上面执行的语句相当于
UPDATE test
SET c = 7
WHERE b = 1
;
再来看下
mysql> INSERT INTO test(a, b) VALUES(1, 5) ON DUPLICATE KEY UPDATE c = 7;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    7 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
上面执行的语句相当于
UPDATE test
SET c = 7
WHERE a = 1
;
结论:INSERT IGNORE INTO只要主键或者唯一索引存在,插入记录被忽略,否者插入。
REPLACE INTO如果是唯一索引记录存在,就会删除记录,添加一条新的记录,主键如果是自增,那么会加1
如果是主键记录存在,就会删除记录,添加一条新的记录,主键不变(因为已经指定主键值)
 INSERT INTO ON DUPLICATE KEY UPDATE,相当于如果主键或者唯一索引存在,更新指定的字段 查看全部
 环境:5.6.20-enterprise-commercial-advanced MySQL Enterprise Server
CREATE TABLE `test` (
  `a` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `b` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `IX_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8
1)插入数据
INSERT INTO
INSERT INTO test(b, c) VALUES (1, 1), (2, 2),(3, 3),(4, 4);
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
此时如果我们继续执行下的语句,就会出现重复的插入唯一索引的错误提示
INSERT INTO test(b, c) VALUES(1, 2);
Result: Error Code: 1062. Duplicate entry '1' for key 'IX_b'
因此,我们可以利用
INSERT IGNORE INTO
REPLACE INTO
 INSERT INTO ON DUPLICATE KEY UPDATE
来避免出现
1)INSERT IGNORE INTO
mysql> INSERT IGNORE INTO test(b, c) VALUES(1, 2);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
mysql> INSERT IGNORE INTO test(a, b) VALUES(1, 5);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    1 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键或者唯一是否存在,如果存在直接忽略,如果不存在,则直接插入数据。
2)REPLACE INTO
mysql> REPLACE INTO test(b,c) VALUES (1, 2);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
| 5 | 1 |    2 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查唯一索引是否存在,检查存在b=1的记录,删除a=1,b=1,c=1这条记录,然后添加a=5,b=1, c=2的记录。如果不存在,则直接插入数据。
上面运行语句相当于:
DELETE FROM test
WHERE b = 1
;
INSERT INTO test(b,c) VALUES (1, 2)
;
如果执行
mysql> REPLACE INTO test(a,b) VALUES (1, 5);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 5 | NULL |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键是否存在,检查存在a=1的记录,删除a=1,b=1,c=1这条记录,然后添加a=1,b=b, c取默认值的记录。如果不存在,则直接插入数据。
上面的语句相当于
DELETE FROM test
WHERE b = 1
;
INSERT INTO test(a, b) VALUES (1, 5)
;
3) INSERT INTO ON DUPLICATE KEY UPDATE
mysql> INSERT INTO test(b, c) VALUES(1, 2) ON DUPLICATE KEY UPDATE c = 7;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    7 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
这条语句的含义是插入时检查主键或者唯一是否存在,检查存在b=1的记录,将c的值更新为7。如果不存在,则直接插入数据。关于影响行数为什么是2呢?MySQL手册: the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.
上面执行的语句相当于
UPDATE test
SET c = 7
WHERE b = 1
;
再来看下
mysql> INSERT INTO test(a, b) VALUES(1, 5) ON DUPLICATE KEY UPDATE c = 7;
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM test;
+---+---+------+
| a | b | c    |
+---+---+------+
| 1 | 1 |    7 |
| 2 | 2 |    2 |
| 3 | 3 |    3 |
| 4 | 4 |    4 |
+---+---+------+
4 rows in set (0.00 sec)
上面执行的语句相当于
UPDATE test
SET c = 7
WHERE a = 1
;
结论:INSERT IGNORE INTO只要主键或者唯一索引存在,插入记录被忽略,否者插入。
REPLACE INTO如果是唯一索引记录存在,就会删除记录,添加一条新的记录,主键如果是自增,那么会加1
如果是主键记录存在,就会删除记录,添加一条新的记录,主键不变(因为已经指定主键值)
 INSERT INTO ON DUPLICATE KEY UPDATE,相当于如果主键或者唯一索引存在,更新指定的字段

MySQL 5.7的binlog的gtid一个相关改动和gtid在线修改的功能分享

MySQLhuzilin 发表了文章 • 4 个评论 • 354 次浏览 • 2016-01-13 23:23 • 来自相关话题

Binlog改动现在MySQL的binlog在gtid off的模式下也记录binlog




gtid的值是anonymous_gtid
在线修改GTID模式
  按照线面这个顺序,渐进改变。
OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
  当gtid_mode= OFF_PERMISSIVE那么新的交易是匿名的,同时允许复制事务是要么GTID或匿名交易。
  匿名交易就是我最上面提到的binlog的新改动。
  当gtid_mode= ON_PERMISSIVE那么新事务使用GTIDs同时允许复制事务是要么GTID或匿名交易。这意味着可以有一个复制拓扑具有使用匿名和GTID交易服务器。
  中间两个过度状态表示可以和开启gtid的MySQL进行复制,也可以和非gtid的MySQL进行复制。这样整个业务进行开启gtid的时候不需要短暂的停业务了。当然5.6同样也可以修改源码来实现这个淘宝的内核周报里面有教程。~不过我还没尝试,有谁尝试可以告诉告诉我哈,让我也学习下。
 
开启
mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.47 sec)

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.37 sec)

mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.48 sec)
关闭

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.39 sec)

mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.39 sec)

mysql> set global gtid_mode=OFF;
Query OK, 0 rows affected (0.41 sec)
 
这里面我踩了个小坑
mysql> set global gtid_mode=OFF_PERMISSIVE;
ERROR 1766 (HY000): The system variable gtid_mode cannot be set when there is an ongoing transaction.

关掉所有session(包括当前),重进然后再进行设置。
  查看全部
  • Binlog改动现在MySQL的binlog在gtid off的模式下也记录binlog

a.jpg

gtid的值是anonymous_gtid
  • 在线修改GTID模式

  按照线面这个顺序,渐进改变。
OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON
  当gtid_mode= OFF_PERMISSIVE那么新的交易是匿名的,同时允许复制事务是要么GTID或匿名交易。
  匿名交易就是我最上面提到的binlog的新改动。
  当gtid_mode= ON_PERMISSIVE那么新事务使用GTIDs同时允许复制事务是要么GTID或匿名交易。这意味着可以有一个复制拓扑具有使用匿名和GTID交易服务器。
  中间两个过度状态表示可以和开启gtid的MySQL进行复制,也可以和非gtid的MySQL进行复制。这样整个业务进行开启gtid的时候不需要短暂的停业务了。当然5.6同样也可以修改源码来实现这个淘宝的内核周报里面有教程。~不过我还没尝试,有谁尝试可以告诉告诉我哈,让我也学习下。
 
开启
mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.47 sec)

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.37 sec)

mysql> set global gtid_mode=ON;
Query OK, 0 rows affected (0.48 sec)
关闭

mysql> set global gtid_mode=ON_PERMISSIVE;
Query OK, 0 rows affected (0.39 sec)

mysql> set global gtid_mode=OFF_PERMISSIVE;
Query OK, 0 rows affected (0.39 sec)

mysql> set global gtid_mode=OFF;
Query OK, 0 rows affected (0.41 sec)
 
这里面我踩了个小坑
mysql> set global gtid_mode=OFF_PERMISSIVE;
ERROR 1766 (HY000): The system variable gtid_mode cannot be set when there is an ongoing transaction.

关掉所有session(包括当前),重进然后再进行设置。
 

最近碰到的MySQL5.6.12上的bug处理

MySQLhuzilin 发表了文章 • 2 个评论 • 347 次浏览 • 2016-01-13 22:39 • 来自相关话题

在客户老环境碰到的坑。
 报错
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error reading header of binary log while looking for the oldest binary log that contains any GTID that is not in the given gtid set'
在内核月报上查到了:
上网查阅是MySQL GTID的一个BUG
    这个时候 mysqld 重启的话,会发现再也起不来了,error log 里有这样的错

    The binary log file 'mysql/mysql-bin.000020' is logically corrupted: The first global transaction identifier was read, but no other information regarding identifiers existing on the previous log files was found.
    这个时候主库继续更新,然后从库来拉取 binlog 的时候,io 线程会停下来

    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error reading header of binary log while looking for the oldest binary log that contains any GTID that is not in the given gtid set'
 
  我都恢复方法和上面有些差异,我并没有去重做主从。
我的解决办法如下:
1.从上做:
stop slave;
change master to master_log_file='xxxx',master_log_file=xxxx, master_auto_position=0;
这样gtid可以有断点
 
2.主上做:
flush logs;
 
以下从上做:
3.等从开始应用主库新flush的binlog的时候
stop slave;
3.show master status;
记录当前gtid值
4.reset slave;reset master;
5.set global gtid_purged='当前同步到的gtid值';
6.change master to xxxx master_auto_position=1;
 
这样就能正常同步了。
 
这个版本的坑实在是太多了,经常出现gtid的bug,最近他们才开始考虑版本升级。 查看全部
在客户老环境碰到的坑。
 报错
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error reading header of binary log while looking for the oldest binary log that contains any GTID that is not in the given gtid set'
在内核月报上查到了:
上网查阅是MySQL GTID的一个BUG
    这个时候 mysqld 重启的话,会发现再也起不来了,error log 里有这样的错

    The binary log file 'mysql/mysql-bin.000020' is logically corrupted: The first global transaction identifier was read, but no other information regarding identifiers existing on the previous log files was found.
    这个时候主库继续更新,然后从库来拉取 binlog 的时候,io 线程会停下来

    Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error reading header of binary log while looking for the oldest binary log that contains any GTID that is not in the given gtid set'
 
  我都恢复方法和上面有些差异,我并没有去重做主从。
我的解决办法如下:
1.从上做:
stop slave;
change master to master_log_file='xxxx',master_log_file=xxxx, master_auto_position=0;
这样gtid可以有断点
 
2.主上做:
flush logs;
 
以下从上做:
3.等从开始应用主库新flush的binlog的时候
stop slave;
3.show master status;
记录当前gtid值
4.reset slave;reset master;
5.set global gtid_purged='当前同步到的gtid值';
6.change master to xxxx master_auto_position=1;
 
这样就能正常同步了。
 
这个版本的坑实在是太多了,经常出现gtid的bug,最近他们才开始考虑版本升级。

SHELL脚本提取binlog delete语句

MySQLhcx8850 回复了问题 • 8 人关注 • 2 个回复 • 719 次浏览 • 2016-01-13 20:18 • 来自相关话题

萤火虫俱乐部20160110次活动PPT下载地址

activityzhangdh 发表了文章 • 0 个评论 • 323 次浏览 • 2016-01-11 12:21 • 来自相关话题

链接: http://pan.baidu.com/s/1pKp74U3 密码: sbek
链接: http://pan.baidu.com/s/1pKp74U3 密码: sbek

MYSQL的字符串函数,留个大家备用

MySQLFred 发表了文章 • 3 个评论 • 289 次浏览 • 2016-01-10 11:21 • 来自相关话题

对于针对字符串位置的操作,第一个位置被标记为1。 
1.ASCII(str) 
返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。 
mysql> select ASCII('2'); 
        -> 50 
mysql> select ASCII(2); 
        -> 50 
mysql> select ASCII('dx'); 
        -> 100 
也可参见ORD()函数。 
2.ORD(str) 
如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。 
mysql> select ORD('2'); 
        -> 50 

3.CONV(N,from_base,to_base) 
在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。CONV以64位点精度工作。 
mysql> select CONV("a",16,2); 
        -> '1010' 
mysql> select CONV("6E",18,8); 
        -> '172' 
mysql> select CONV(-17,10,-18); 
        -> '-H' 
mysql> select CONV(10+"10"+'10'+0xa,10,10); 
        -> '40' 

4.BIN(N) 
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果N是NULL,返回NULL。 
mysql> select BIN(12); 
        -> '1100' 
5.OCT(N) 
返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL。 
mysql> select OCT(12); 
        -> '14' 

6.HEX(N) 
返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果N是NULL,返回NULL。 
mysql> select HEX(255); 
        -> 'FF' 

7.CHAR(N,...) 
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。 
mysql> select CHAR(77,121,83,81,'76'); 
        -> 'MySQL' 
mysql> select CHAR(77,77.3,'77.3'); 
        -> 'MMM' 

8.CONCAT(str1,str2,...) 
返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 
mysql> select CONCAT('My', 'S', 'QL'); 
        -> 'MySQL' 
mysql> select CONCAT('My', NULL, 'QL'); 
        -> NULL 
mysql> select CONCAT(14.3); 
        -> '14.3' 
9.LENGTH(str) 
 
10.OCTET_LENGTH(str) 
 
11.CHAR_LENGTH(str) 
 
12.CHARACTER_LENGTH(str) 
返回字符串str的长度。 
mysql> select LENGTH('text'); 
        -> 4 
mysql> select OCTET_LENGTH('text'); 
        -> 4 
注意,对于多字节字符,其CHAR_LENGTH()仅计算一次。 
13.LOCATE(substr,str) 
 
14.POSITION(substr IN str) 
返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0. 
mysql> select LOCATE('bar', 'foobarbar'); 
        -> 4 
mysql> select LOCATE('xbar', 'foobar'); 
        -> 0 
该函数是多字节可靠的。  
15.LOCATE(substr,str,pos) 
返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。 
mysql> select LOCATE('bar', 'foobarbar',5); 
        -> 7 
这函数是多字节可靠的。 
16.INSTR(str,substr) 
返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同,除了参数被颠倒。 
mysql> select INSTR('foobarbar', 'bar'); 
        -> 4 
mysql> select INSTR('xbar', 'foobar'); 
        -> 0 
这函数是多字节可靠的。 
17.LPAD(str,len,padstr) 
返回字符串str,左面用字符串padstr填补直到str是len个字符长。 
mysql> select LPAD('hi',4,'??'); 
        -> '??hi' 

18.RPAD(str,len,padstr) 
返回字符串str,右面用字符串padstr填补直到str是len个字符长。  
mysql> select RPAD('hi',5,'?'); 
        -> 'hi???' 
19.LEFT(str,len) 
返回字符串str的最左面len个字符。 
mysql> select LEFT('foobarbar', 5); 
        -> 'fooba' 
该函数是多字节可靠的。 
20.RIGHT(str,len) 
返回字符串str的最右面len个字符。 
mysql> select RIGHT('foobarbar', 4); 
        -> 'rbar' 
该函数是多字节可靠的。 
21.SUBSTRING(str,pos,len) 
 
22.SUBSTRING(str FROM pos FOR len) 
 
23.MID(str,pos,len) 
从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。 
mysql> select SUBSTRING('Quadratically',5,6); 
        -> 'ratica' 
该函数是多字节可靠的。 
24.SUBSTRING(str,pos) 
 
25.SUBSTRING(str FROM pos) 
从字符串str的起始位置pos返回一个子串。 
mysql> select SUBSTRING('Quadratically',5); 
        -> 'ratically' 
mysql> select SUBSTRING('foobarbar' FROM 4); 
        -> 'barbar' 
该函数是多字节可靠的。 
26.SUBSTRING_INDEX(str,delim,count) 
返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。 
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2); 
        -> 'www.mysql' 
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2); 
        -> 'mysql.com' 
该函数对多字节是可靠的。 
27.LTRIM(str) 
返回删除了其前置空格字符的字符串str。 
mysql> select LTRIM('  barbar'); 
        -> 'barbar' 
28.RTRIM(str) 
返回删除了其拖后空格字符的字符串str。 
mysql> select RTRIM('barbar   '); 
        -> 'barbar' 
该函数对多字节是可靠的。  
29.TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) 
返回字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。 
mysql> select TRIM('  bar   '); 
        -> 'bar' 
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx'); 
        -> 'barxxx' 
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx'); 
        -> 'bar' 
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz'); 
        -> 'barx' 
该函数对多字节是可靠的。 
21.SOUNDEX(str) 
返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。 
mysql> select SOUNDEX('Hello'); 
        -> 'H400' 
mysql> select SOUNDEX('Quadratically'); 
        -> 'Q36324' 

22.SPACE(N) 
返回由N个空格字符组成的一个字符串。 
mysql> select SPACE(6); 
        -> '      ' 

23.REPLACE(str,from_str,to_str) 
返回字符串str,其字符串from_str的所有出现由字符串to_str代替。 
mysql> select REPLACE('www.mysql.com', 'w', 'Ww'); 
        -> 'WwWwWw.mysql.com' 
该函数对多字节是可靠的。 
24.REPEAT(str,count) 
返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。 
mysql> select REPEAT('MySQL', 3); 
        -> 'MySQLMySQLMySQL' 

25.REVERSE(str) 
返回颠倒字符顺序的字符串str。 
mysql> select REVERSE('abc'); 
        -> 'cba' 
该函数对多字节可靠的。 
26.INSERT(str,pos,len,newstr) 
返回字符串str,在位置pos起始的子串且len个字符长得子串由字符串newstr代替。 
mysql> select INSERT('Quadratic', 3, 4, 'What'); 
        -> 'QuWhattic' 
该函数对多字节是可靠的。 
27.ELT(N,str1,str2,str3,...) 
如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL。ELT()是FIELD()反运算。 
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo'); 
        -> 'ej' 
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo'); 
        -> 'foo' 
28.FIELD(str,str1,str2,str3,...) 
返回str在str1, str2, str3, ...清单的索引。如果str没找到,返回0。FIELD()是ELT()反运算。 
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); 
        -> 2 
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); 
        -> 0 
29.FIND_IN_SET(str,strlist) 
如果字符串str在由N子串组成的表strlist之中,返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列,FIND_IN_SET()函数被优化而使用位运算!如果str不是在strlist里面或如果strlist是空字符串,返回0。如果任何一个参数是NULL,返回NULL。如果第一个参数包含一个“,”,该函数将工作不正常。 
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); 
        -> 2 

30.MAKE_SET(bits,str1,str2,...) 
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串),由相应的位在bits集合中的的字符串组成。str1对应于位0,str2对应位1,等等。在str1, str2, ...中的NULL串不添加到结果中。 
mysql> SELECT MAKE_SET(1,'a','b','c'); 
        -> 'a' 
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); 
        -> 'hello,world' 
mysql> SELECT MAKE_SET(0,'a','b','c'); 
        -> '' 
31.EXPORT_SET(bits,on,off,[separator,[number_of_bits]]) 
返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用。 
mysql> select EXPORT_SET(5,'Y','N',',',4) 
        -> Y,N,Y,N 
32.LCASE(str) 
 
33.LOWER(str) 
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。 
mysql> select LCASE('QUADRATICALLY'); 
        -> 'quadratically' 

34.UCASE(str) 
 
35.UPPER(str) 
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。 
mysql> select UCASE('Hej'); 
        -> 'HEJ' 
该函数对多字节是可靠的。 
36.LOAD_FILE(file_name) 
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL。 
mysql> UPDATE table_name 
           SET blob_column=LOAD_FILE("/tmp/picture") 
           WHERE id=1; 

MySQL必要时自动变换数字为字符串,并且反过来也如此: 
mysql> SELECT 1+"1"; 
        -> 2 
mysql> SELECT CONCAT(2,' test'); 
        -> '2 test' 
如果你想要明确地变换一个数字到一个字符串,把它作为参数传递到CONCAT()。 
如果字符串函数提供一个二进制字符串作为参数,结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。 查看全部
对于针对字符串位置的操作,第一个位置被标记为1。 
1.ASCII(str) 
返回字符串str的最左面字符的ASCII代码值。如果str是空字符串,返回0。如果str是NULL,返回NULL。 
mysql> select ASCII('2'); 
        -> 50 
mysql> select ASCII(2); 
        -> 50 
mysql> select ASCII('dx'); 
        -> 100 
也可参见ORD()函数。 
2.ORD(str) 
如果字符串str最左面字符是一个多字节字符,通过以格式((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]返回字符的ASCII代码值来返回多字节字符代码。如果最左面的字符不是一个多字节字符。返回与ASCII()函数返回的相同值。 
mysql> select ORD('2'); 
        -> 50 

3.CONV(N,from_base,to_base) 
在不同的数字基之间变换数字。返回数字N的字符串数字,从from_base基变换为to_base基,如果任何参数是NULL,返回NULL。参数N解释为一个整数,但是可以指定为一个整数或一个字符串。最小基是2且最大的基是36。如果to_base是一个负数,N被认为是一个有符号数,否则,N被当作无符号数。CONV以64位点精度工作。 
mysql> select CONV("a",16,2); 
        -> '1010' 
mysql> select CONV("6E",18,8); 
        -> '172' 
mysql> select CONV(-17,10,-18); 
        -> '-H' 
mysql> select CONV(10+"10"+'10'+0xa,10,10); 
        -> '40' 

4.BIN(N) 
返回二进制值N的一个字符串表示,在此N是一个长整数(BIGINT)数字,这等价于CONV(N,10,2)。如果N是NULL,返回NULL。 
mysql> select BIN(12); 
        -> '1100' 
5.OCT(N) 
返回八进制值N的一个字符串的表示,在此N是一个长整型数字,这等价于CONV(N,10,8)。如果N是NULL,返回NULL。 
mysql> select OCT(12); 
        -> '14' 

6.HEX(N) 
返回十六进制值N一个字符串的表示,在此N是一个长整型(BIGINT)数字,这等价于CONV(N,10,16)。如果N是NULL,返回NULL。 
mysql> select HEX(255); 
        -> 'FF' 

7.CHAR(N,...) 
CHAR()将参数解释为整数并且返回由这些整数的ASCII代码字符组成的一个字符串。NULL值被跳过。 
mysql> select CHAR(77,121,83,81,'76'); 
        -> 'MySQL' 
mysql> select CHAR(77,77.3,'77.3'); 
        -> 'MMM' 

8.CONCAT(str1,str2,...) 
返回来自于参数连结的字符串。如果任何参数是NULL,返回NULL。可以有超过2个的参数。一个数字参数被变换为等价的字符串形式。 
mysql> select CONCAT('My', 'S', 'QL'); 
        -> 'MySQL' 
mysql> select CONCAT('My', NULL, 'QL'); 
        -> NULL 
mysql> select CONCAT(14.3); 
        -> '14.3' 
9.LENGTH(str) 
 
10.OCTET_LENGTH(str) 
 
11.CHAR_LENGTH(str) 
 
12.CHARACTER_LENGTH(str) 
返回字符串str的长度。 
mysql> select LENGTH('text'); 
        -> 4 
mysql> select OCTET_LENGTH('text'); 
        -> 4 
注意,对于多字节字符,其CHAR_LENGTH()仅计算一次。 
13.LOCATE(substr,str) 
 
14.POSITION(substr IN str) 
返回子串substr在字符串str第一个出现的位置,如果substr不是在str里面,返回0. 
mysql> select LOCATE('bar', 'foobarbar'); 
        -> 4 
mysql> select LOCATE('xbar', 'foobar'); 
        -> 0 
该函数是多字节可靠的。  
15.LOCATE(substr,str,pos) 
返回子串substr在字符串str第一个出现的位置,从位置pos开始。如果substr不是在str里面,返回0。 
mysql> select LOCATE('bar', 'foobarbar',5); 
        -> 7 
这函数是多字节可靠的。 
16.INSTR(str,substr) 
返回子串substr在字符串str中的第一个出现的位置。这与有2个参数形式的LOCATE()相同,除了参数被颠倒。 
mysql> select INSTR('foobarbar', 'bar'); 
        -> 4 
mysql> select INSTR('xbar', 'foobar'); 
        -> 0 
这函数是多字节可靠的。 
17.LPAD(str,len,padstr) 
返回字符串str,左面用字符串padstr填补直到str是len个字符长。 
mysql> select LPAD('hi',4,'??'); 
        -> '??hi' 

18.RPAD(str,len,padstr) 
返回字符串str,右面用字符串padstr填补直到str是len个字符长。  
mysql> select RPAD('hi',5,'?'); 
        -> 'hi???' 
19.LEFT(str,len) 
返回字符串str的最左面len个字符。 
mysql> select LEFT('foobarbar', 5); 
        -> 'fooba' 
该函数是多字节可靠的。 
20.RIGHT(str,len) 
返回字符串str的最右面len个字符。 
mysql> select RIGHT('foobarbar', 4); 
        -> 'rbar' 
该函数是多字节可靠的。 
21.SUBSTRING(str,pos,len) 
 
22.SUBSTRING(str FROM pos FOR len) 
 
23.MID(str,pos,len) 
从字符串str返回一个len个字符的子串,从位置pos开始。使用FROM的变种形式是ANSI SQL92语法。 
mysql> select SUBSTRING('Quadratically',5,6); 
        -> 'ratica' 
该函数是多字节可靠的。 
24.SUBSTRING(str,pos) 
 
25.SUBSTRING(str FROM pos) 
从字符串str的起始位置pos返回一个子串。 
mysql> select SUBSTRING('Quadratically',5); 
        -> 'ratically' 
mysql> select SUBSTRING('foobarbar' FROM 4); 
        -> 'barbar' 
该函数是多字节可靠的。 
26.SUBSTRING_INDEX(str,delim,count) 
返回从字符串str的第count个出现的分隔符delim之后的子串。如果count是正数,返回最后的分隔符到左边(从左边数) 的所有字符。如果count是负数,返回最后的分隔符到右边的所有字符(从右边数)。 
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', 2); 
        -> 'www.mysql
mysql> select SUBSTRING_INDEX('www.mysql.com', '.', -2); 
        -> 'mysql.com' 
该函数对多字节是可靠的。 
27.LTRIM(str) 
返回删除了其前置空格字符的字符串str。 
mysql> select LTRIM('  barbar'); 
        -> 'barbar' 
28.RTRIM(str) 
返回删除了其拖后空格字符的字符串str。 
mysql> select RTRIM('barbar   '); 
        -> 'barbar' 
该函数对多字节是可靠的。  
29.TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str) 
返回字符串str,其所有remstr前缀或后缀被删除了。如果没有修饰符BOTH、LEADING或TRAILING给出,BOTH被假定。如果remstr没被指定,空格被删除。 
mysql> select TRIM('  bar   '); 
        -> 'bar' 
mysql> select TRIM(LEADING 'x' FROM 'xxxbarxxx'); 
        -> 'barxxx' 
mysql> select TRIM(BOTH 'x' FROM 'xxxbarxxx'); 
        -> 'bar' 
mysql> select TRIM(TRAILING 'xyz' FROM 'barxxyz'); 
        -> 'barx' 
该函数对多字节是可靠的。 
21.SOUNDEX(str) 
返回str的一个同音字符串。听起来“大致相同”的2个字符串应该有相同的同音字符串。一个“标准”的同音字符串长是4个字符,但是SOUNDEX()函数返回一个任意长的字符串。你可以在结果上使用SUBSTRING()得到一个“标准”的 同音串。所有非数字字母字符在给定的字符串中被忽略。所有在A-Z之外的字符国际字母被当作元音。 
mysql> select SOUNDEX('Hello'); 
        -> 'H400' 
mysql> select SOUNDEX('Quadratically'); 
        -> 'Q36324' 

22.SPACE(N) 
返回由N个空格字符组成的一个字符串。 
mysql> select SPACE(6); 
        -> '      ' 

23.REPLACE(str,from_str,to_str) 
返回字符串str,其字符串from_str的所有出现由字符串to_str代替。 
mysql> select REPLACE('www.mysql.com', 'w', 'Ww'); 
        -> 'WwWwWw.mysql.com
该函数对多字节是可靠的。 
24.REPEAT(str,count) 
返回由重复countTimes次的字符串str组成的一个字符串。如果count <= 0,返回一个空字符串。如果str或count是NULL,返回NULL。 
mysql> select REPEAT('MySQL', 3); 
        -> 'MySQLMySQLMySQL' 

25.REVERSE(str) 
返回颠倒字符顺序的字符串str。 
mysql> select REVERSE('abc'); 
        -> 'cba' 
该函数对多字节可靠的。 
26.INSERT(str,pos,len,newstr) 
返回字符串str,在位置pos起始的子串且len个字符长得子串由字符串newstr代替。 
mysql> select INSERT('Quadratic', 3, 4, 'What'); 
        -> 'QuWhattic' 
该函数对多字节是可靠的。 
27.ELT(N,str1,str2,str3,...) 
如果N= 1,返回str1,如果N= 2,返回str2,等等。如果N小于1或大于参数个数,返回NULL。ELT()是FIELD()反运算。 
mysql> select ELT(1, 'ej', 'Heja', 'hej', 'foo'); 
        -> 'ej' 
mysql> select ELT(4, 'ej', 'Heja', 'hej', 'foo'); 
        -> 'foo' 
28.FIELD(str,str1,str2,str3,...) 
返回str在str1, str2, str3, ...清单的索引。如果str没找到,返回0。FIELD()是ELT()反运算。 
mysql> select FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); 
        -> 2 
mysql> select FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo'); 
        -> 0 
29.FIND_IN_SET(str,strlist) 
如果字符串str在由N子串组成的表strlist之中,返回一个1到N的值。一个字符串表是被“,”分隔的子串组成的一个字符串。如果第一个参数是一个常数字符串并且第二个参数是一种类型为SET的列,FIND_IN_SET()函数被优化而使用位运算!如果str不是在strlist里面或如果strlist是空字符串,返回0。如果任何一个参数是NULL,返回NULL。如果第一个参数包含一个“,”,该函数将工作不正常。 
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); 
        -> 2 

30.MAKE_SET(bits,str1,str2,...) 
返回一个集合 (包含由“,”字符分隔的子串组成的一个字符串),由相应的位在bits集合中的的字符串组成。str1对应于位0,str2对应位1,等等。在str1, str2, ...中的NULL串不添加到结果中。 
mysql> SELECT MAKE_SET(1,'a','b','c'); 
        -> 'a' 
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world'); 
        -> 'hello,world' 
mysql> SELECT MAKE_SET(0,'a','b','c'); 
        -> '' 
31.EXPORT_SET(bits,on,off,[separator,[number_of_bits]]) 
返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个“off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用。 
mysql> select EXPORT_SET(5,'Y','N',',',4) 
        -> Y,N,Y,N 
32.LCASE(str) 
 
33.LOWER(str) 
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成小写。该函数对多字节是可靠的。 
mysql> select LCASE('QUADRATICALLY'); 
        -> 'quadratically' 

34.UCASE(str) 
 
35.UPPER(str) 
返回字符串str,根据当前字符集映射(缺省是ISO-8859-1 Latin1)把所有的字符改变成大写。该函数对多字节是可靠的。 
mysql> select UCASE('Hej'); 
        -> 'HEJ' 
该函数对多字节是可靠的。 
36.LOAD_FILE(file_name) 
读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权限。文件必须所有内容都是可读的并且小于max_allowed_packet。如果文件不存在或由于上面原因之一不能被读出,函数返回NULL。 
mysql> UPDATE table_name 
           SET blob_column=LOAD_FILE("/tmp/picture") 
           WHERE id=1; 

MySQL必要时自动变换数字为字符串,并且反过来也如此: 
mysql> SELECT 1+"1"; 
        -> 2 
mysql> SELECT CONCAT(2,' test'); 
        -> '2 test' 
如果你想要明确地变换一个数字到一个字符串,把它作为参数传递到CONCAT()。 
如果字符串函数提供一个二进制字符串作为参数,结果字符串也是一个二进制字符串。被变换到一个字符串的数字被当作是一个二进制字符串。这仅影响比较。