1(生产实战案例):监控MySQL主从同步是否异常,如果异常,则发送短信或者邮件给管理员。提示:如果没主从同步环境,可以用下面文本放到文件里读取来模拟:
阶段1:开发一个守护进程脚本每30秒实现检测一次。
阶段2:如果同步出现如下错误号(1158,1159,1008,1007,1062),则跳过错误。
阶段3:请使用数组技术实现上述脚本(获取主从判断及错误号部分)
[root@oldboy~]# mysql -uroot -p'oldboy' -S /data/3307/mysql.sock -e "show slave status\G;"
1. row
Slave_IO_State:Waiting for master to send event
Master_Host:10.0.0.179 #当前的mysql master服务器主机
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:mysql-bin.000013
Read_Master_Log_Pos: 502547
Relay_Log_File:relay-bin.000013
Relay_Log_Pos:251
Relay_Master_Log_File:mysql-bin.000013
Slave_IO_Running:Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 502547
Relay_Log_Space:502986
Until_Condition:None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0 #和主库比同步延迟的秒数,这个参数很重要
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
实例答案如下:
[root@aming01 oldboy]# cat sqlmy.sh
#!/bin/bash
Mysql = " mysql -uroot -poldboy123 -S /tmp/mysql.sock "
L_aaa(){
Status=$Mysql -e "show slave status\G" |grep -E "_Running: yes|seconds_Behind_Master: [0-2]" |wc -l
Code=$Mysql -e "show slave status\G" |awk '/Last_SQL_Errno:/{print $2}'
}
L_Status(){
[ $Status -ne 3 ] && { return 1 } ||{ return 0}
}
S_Code=(
1158
1159
1008
1007
1062
)
L_Skip(){
[ $Code -eq 0 ] && return 0
for i in ${S_Code[*]}
do
[ $Code -eq $i ] && {
$Mysql -e "stop slave; set global sql_slave_skip_counter = 1; start slave;" &&
\
return 0
}
done
return 1
}
main(){
while true
do
L_aaa
L_Skip
Error1=$?
L_Status
Error2=$?
[ $Error1 -eq 1 -o $Error2 -eq 1 ] && echo "Error"
done
}
main
[root@aming01 oldboy]#
[root@aming01 oldboy]# vi sqlmy.sh
#!/bin/bash
Mysql = " mysql -uroot -p123456 -S /data/mysql/mysql.sock "
L_aaa(){
Status=$Mysql -e "show slave status\G" |grep -E "_Running: Yes|Seconds_Behind_Master: [0-2]" |wc -l
Code=$Mysql -e "show slave status\G" |awk '/Last_SQL_Errno:/{print $2}'
}
L_Status(){
[ $Status -ne 3 ] && { return 1 } ||{ return 0}
}
S_Code=(
1158
1159
1008
1007
1062
)
L_Skip(){
[ $Code -eq 0 ] && return 0
for i in ${S_Code[*]}
do
[ $Code -eq $i ] && {
$Mysql -e "stop slave; set global sql_slave_skip_counter = 1; start slave;" &&\
return 0
}
done
return 1
}
main(){
while true
do
L_aaa
L_Skip
Error1=$?
L_Status
Error2=$?
[ $Error1 -eq 1 -o $Error2 -eq 1 ] && echo "Error"
done
}
main
2.使用for循环在/oldboy目录下通过随机小写10个字母加固定字符串oldboy批量创建10个html文件
#!/bin/bash
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
path=/tmp/test/
[ ! -d $path ] && mkdir -p $path
for i in seq -w 01 10
do
#touch $path$(echo $RANDOM|md5sum|tr "." “A-Za-z”|cut -c 2-10)_oldboy.html
touch $path$(echo $RANDOM|openssl rand -hex 40|sed -r 's#[.]#[0-9a-zA-Z]#g'|cut -c 2-10)_oldboy.html
#touch $Path$(echo $RANDOM|md5sum|tr "[0-9]" "a-zA-Z"|cut -c2-10)oldboy$i.html
#touch $Path$(cat /proc/sys/kernel/random/uuid|md5sum|cut -c 2-10)oldboy$i.html
done
3.批量创建10个系统帐号oldboy01-oldboy10并设置密码(密码为随机8位字符串)。
方法一:
[root@aming01 oldboy]# for i in oldboy_{01..10};do useradd $i && echo $RANDOM|md5sum|cut -c 2-10|passwd --stdin $i;done
方法二:
[root@aming01 oldboy]# cat user.sh
#!/bin/bash
path=/tmp/user.conf
[ ! -f $path ]&& touch $path
[ -f /etc/init.d/functions ]&& . /etc/init.d/functions
for i in oldboy{01..10}
do
pass=$(echo $RANDOM|md5sum|cut -c 2-10)
useradd $i &>/dev/null &&\
echo $pass|passwd --stdin $i;
echo -e $i "\t" $pass >>$path // echo -e 对后面的 \t 转义
done
方法三:
[root@aming01 oldboy]# echo oldboy{01..10}|xargs -n1|sed -r 's#(.*)#useradd \1;pass=$(echo $RANDOM|md5sum|cut -c 2-10);echo $pass|passwd --stdin \1;echo -e "\1 \t $pass" >>/tmp/user.conf#g'|bash
4. 写一个脚本,实现判断10.0.0.0/24网络里,当前在线用户的IP有哪些(方法有很多)
[root@aming01 oldboy]#
[root@aming01 oldboy]# cat ping.sh
#!/bin/bash
ip=10.0.0.
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
for i in {1..254}
do
ping -w 3 -c 2 $ip$i &>/dev/null
if [ $? -eq 0 ];then
action " $ip$i is ok " /bin/true
else
action "$ip$i is false " /bin/false
fi
done
.7.开发mysql多实例启动脚本:
已知mysql多实例启动命令为:mysqld_safe--defaults-file=/data/3306/my.cnf &
停止命令为:mysqladmin -u root -p123456 -S /data/3306/mysql.sockshutdown
请完成mysql多实例启动启动脚本的编写
首先利用下面这条命令查下MySQL的pid文件
show variables like 'pid_file'\G;
得到pid文件路径是 Value: /data/mysql/aming01.pid
mysql.sock 的配置位置配置到/data/mysql/mysql.sock
答案如下:
[root@aming01 oldboy]# cat 7sql.sh
[ -f /etc/init.d/functions ] && . /etc/init.d/functions
port=3306
mysql_user=root
mysql_paswd=123456
Path=/data/mysql/
mysql_start(){
if [ -f ${Path}aming01.pid ];then
echo "Mysql is running"
exit 1
else
mysqld_safe --defaults-file=${Path}my.cnf &>/dev/null &
if [ $? -eq 0 ];then
action "Mysql is running" /bin/true
else
action "Mysql no start" /bin/false
fi
fi
}
mysql_stop(){
if [ ! -f ${Path}aming01.pid ];then
echo "Mysql is not running"
else
/usr/local/mysql/bin/mysqladmin -u${mysql_user} -p${mysql_paswd} -S ${Path}mysql.sock shutdown
if [ $? -eq 0 ];then
action "mysql is stop " /bin/true
else
action " mysql is already stopping" /bin/false
fi
fi
}
case "$1" in
start)
mysql_start
;;
stop)
mysql_stop
;;
restart)
mysql_stop
sleep 3
mysql_start
;;
*)
echo "Please input {start|stop|restart}"
;;
esac
未完待续
转载于:https://blog.51cto.com/13227377/2348599