linux shell 整理收集(不断更新)

1)主从复制延时判断 （转 http://www.cnblogs.com/gomysql/p/3862018.html)

   说明：

不要通过Seconds_Behind_Master去判断，该值表示slave上SQL线程和IO线程之间的延迟
1、首先看 Relay_Master_Log_File 和 Master_Log_File 是否有差异
2、如果Relay_Master_Log_File 和 Master_Log_File 有差异的话，那说明延迟很大
3、如果Relay_Master_Log_File 和 Master_Log_File 没有差异，再来看Exec_Master_Log_Pos 和 Read_Master_Log_Pos 的差异，那么更加严谨的做法是同时在主库执行show master status和在从库上面执行show slave status 的输出进行比较。MHA就是这样保证数据一致性的。MMM都没有做到。这也算MHA比MMM更加优秀的地方。

#!/bin/bash
# 判断主从复制是否延迟
# write by yayun 2014-07-23
# http://www.cnblogs.com/gomysql/

# slave
s_psswd=123456
s_user=root
s_port=3306
s_host=localhost

# master
m_psswd=123456
m_user=root
m_port=3306
m_host=192.168.0.102


slave_wan_ip=`ifconfig | sed -n '/inet /{s/.*addr://;s/ .*//;p}' | head -n1`

while true
do
    sleep 1
    echo -e "\e[1;33m###################################\e[0m"
    Master_Log_File=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave status\G" | grep -w Master_Log_File | awk -F": " '{print $2}')
    Relay_Master_Log_File=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave status\G" | grep -w Relay_Master_Log_File | awk -F": " '{print $2}')
    Read_Master_Log_Pos=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave status\G" | grep -w Read_Master_Log_Pos | awk -F": " '{print $2}')
    Exec_Master_Log_Pos=$(mysql -u$s_user -p$s_psswd -h$s_host -P$s_port -e "show slave status\G" | grep -w Exec_Master_Log_Pos | awk -F": " '{print $2}'|sed 's/[ \t]*$//g')
    Master_Log_File_Num=`echo $Master_Log_File | awk -F '.' '{print $2}' | sed 's/^0\+//'`
    Master_File=$(mysql -u$m_user -p$m_psswd -h$m_host -P$m_port -Nse "show master status" | awk '{print $1}')
    Master_Pos=$(mysql -u$m_user -p$m_psswd -h$m_host -P$m_port -Nse "show master status" | awk '{print $2}'|sed 's/[ \t]*$//g')
    Master_File_Num=`echo $Master_File | awk -F '.' '{print $2}' | sed 's/^0\+//'`

    if [ -z $Master_Log_File ] && [ -z $Relay_Master_Log_File ] && [ -z $Read_Master_Log_Pos ] && [ -z $Exec_Master_Log_Pos ]
    then
        echo -e "\e[1;31mSLAVE 没有取到值，请检查参数设置!\e[0m"
        exit 1
    fi

    if [ $Master_Log_File = $Relay_Master_Log_File ] && [ $Read_Master_Log_Pos = $Exec_Master_Log_Pos ]
    then
        if [ $Master_Log_File = $Master_File ] && [ $Exec_Master_Log_Pos = $Master_Pos ]
        then
            echo -e "\e[1;32mMaster-slave 复制无延迟 ^_^\e[0m"
        else
            if [ $Master_Log_File_Num -gt $Master_File_Num ] || [ $Master_Pos -gt $Exec_Master_Log_Pos ]
            then
                log_count=$(expr $Master_Log_File_Num - $Master_File_Num)
                pos_count=$(expr $Master_Pos - $Exec_Master_Log_Pos)
                echo -e "\e[1;31mMaster-slave 复制延迟 !!!\e[0m"
                echo -e "\e[1;31mMaster:$m_host Slave:$slave_wan_ip\e[0m"
                echo -e "\e[1;31mMaster当前binlog: $Master_File"
                echo -e "\e[1;31mSlave当前binlog:  $Master_Log_File"
                echo -e "\e[1;31mbinlog相差文件数: $log_count\e[0m"
                echo -e "\e[1;31mPos点相差:        $pos_count\e[0m"
            fi
        fi
    fi
done

 主从复删除主键冲突的记录

#!/bin/bash
#Delete duplicate records primary key conflict
#Write by yayun 2014-05-17

mysql=/usr/local/mysql-5.1.66/bin/mysql
sock=/data/mysql-slave-3311/mysql.sock
passwd=123456

while true
do
    SQL_THREAD=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep 'Slave_SQL_Running' | awk '{print $2}'`
    LAST_ERROR=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep Last_Errno | awk '{print $2}'`
    duplicate=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk '/Duplicate entry/{print $5}' | awk -F "'" '{print $2}'`
    DATABASE=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk '{print $13}' | awk -F "'" '{print $2}'`
    TABLE=`$mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep Last_Error | awk -F ":" '{print $4}' | awk -F "(" '{print $1}' | awk '{print $NF}'`

    $mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | grep HA_ERR_FOUND_DUPP_KEY
    if [ $? -eq 1 ]
    then
        if [ "$SQL_THREAD" == No ] && [ "$LAST_ERROR" == 1062 ]
        then
            FILED=`$mysql -uroot -p$passwd -S $sock -Nse "desc $DATABASE.$TABLE" | grep PRI | awk '{print $1}'`
            $mysql -uroot -p$passwd -S $sock -e "delete from $DATABASE.$TABLE where $FILED=$duplicate"
            $mysql -uroot -p$passwd -S $sock -e "start slave sql_thread"
        else
            echo "====================== ok ========================"
            $mysql -uroot -p$passwd -S $sock -e 'show slave status\G' | egrep 'Slave_.*_Running'
            echo "====================== ok ========================"
            break
        fi
    fi
done

 写了一个脚本启动 mysqladmin.sh

    #!/bin/sh  
      
    mysql_port=3306  
    mysql_username="root"  
    mysql_password=""  
      
    function_start_mysql()  
    {  
        printf "Starting MySQL...\n"  
        /bin/sh /usr/local/webserver/mysql/bin/mysqld_safe --defaults-file=/data1/mysql/${mysql_port}/my.cnf 2>&1 > /dev/null &  
    }  
      
    function_stop_mysql()  
    {  
        printf "Stoping MySQL...\n"  
        /usr/local/webserver/mysql/bin/mysqladmin -u ${mysql_username} -p${mysql_password} -h 127.0.0.1 -S /tmp/mysql.sock shutdown  
    }  
      
    function_restart_mysql()  
    {  
        printf "Restarting MySQL...\n"  
        function_stop_mysql  
        sleep 5  
        function_start_mysql  
    }  
      
    function_kill_mysql()  
    {  
        kill -9 $(ps -ef | grep 'bin/mysqld_safe' | grep ${mysql_port} | awk '{printf $2}')  
        kill -9 $(ps -ef | grep 'libexec/mysqld' | grep ${mysql_port} | awk '{printf $2}')  
    }  
      
    if [ "$1" = "start" ]; then  
        function_start_mysql  
    elif [ "$1" = "stop" ]; then  
        function_stop_mysql  
    elif [ "$1" = "restart" ]; then  
    function_restart_mysql  
    elif [ "$1" = "kill" ]; then  
    function_kill_mysql  
    else  
        printf "Usage: /data1/mysql/${mysql_port}/mysql {star|stop|restart|kill}\n"  
    fi