Some functional and practical Linuxshell scripts of MySQL _ MySQL

This article mainly introduces the shell script sharing of some practical functions of MySQL in Linux, including starting Memcached, binlog automatic cleanup, and repairing master-slave synchronization, for more information, see Memcached startup script

# vim /etc/init.d/memcached

#!/bin/bash#=======================================================================================# chkconfig: - 80 12# description: Distributed memory caching daemon# processname: memcached#=======================================================================================IPADDR=`/sbin/ifconfig eth1 | awk -F ':' '/inet addr/{print $2}' | sed 's/[a-zA-Z ]//g'`PORT="11211"USER="root"SIZE="2048"CONNNUM="51200"PIDFILE="/var/run/memcached.pid"BINFILE="/usr/local/memcached/bin/memcached"LOCKFILE="/var/lock/subsys/memcached"RETVAL=0      start() {  echo -n $"Starting memcached......"  $BINFILE -d -l $IPADDR -p $PORT -u $USER -m $SIZE -c $CONNNUM -P $PIDFILE  RETVAL=$?  echo  [ $RETVAL -eq 0 ] && touch $LOCKFILE          return $RETVAL}      stop() {  echo -n $"Shutting down memcached......"  /sbin/killproc $BINFILE  RETVAL=$?  echo  [ $RETVAL -eq 0 ] && rm -f $LOCKFILE          return $RETVAL}      restart() {  stop  sleep 1  start}      reload() {  echo -n $"Reloading memcached......"  /sbin/killproc $BINFILE -HUP  RETVAL=$?  echo          return $RETVAL}      case "$1" instart)  start  ;;        stop)  stop  ;;        restart)  restart  ;;        condrestart)  [ -e $LOCKFILE ] && restart  RETVAL=$?  ;;        reload)  reload  ;;        status)  status $prog  RETVAL=$?  ;;        *)  echo "Usage: $0 {start|stop|restart|condrestart|status}"  RETVAL=1esac

exit $RETVAL# chmod +x /etc/init.d/memcached# chkconfig --add memcached# chkconfig --level 235 memcached on# service memcached start

Binlog automatic cleanup script

# vim /data/scripts/delete_mysql_binlog.sh

#! /Bin/bash #=============================================== ========================================================== ============## used to delete the binlog that has been synchronized on the MySQL Master end [run on the Master end ], to reduce disk space # run every morning at # Note: you must add an account on the Slave to allow access from the Master. [account: check_binlog, password: binlog_2356] # run on the MySQL Master end [currently only used in Master-slave synchronization mode, not considered for the moment when multiple slaves exist. ========================================================== ==================== PATH =/sbin: /bin:/usr/sbin:/u Sr/bin:/usr/local/sbin:/usr/local/bin # Slave terminal connection information SLAVE_ADDR = "XXX. XXX. XXX. XXX "SLAVE_USER =" check_binlog "SLAVE_PWD =" binlog_2356 "LOGFILE ="/data/logs/db_sync_info.log "PINGFILE ="/tmp/mysqlping. log "# MySQL status information viewing command SQLCMD =" show slave status "#====================== ========================================================== ====================================## check whether MySQL is running. if [['ps aux | grep mysql [d] | wc-l '-eq 0]; then echo The MySQL is not running: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # Test the connectivity of the Slave end nohup mysqladmin-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD} ping >$ {PINGFILE} retval = 'grep "^ error" $ {PINGFILE} 'rm-f $ {PINGFILE} if [[" $ {retval} X "! = "X"]; then echo The MySQL Slave can not be connected: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # check whether the SlaveMASTER_ADDR is valid = 'MySQL-h $ {SLAVE_ADDR} -u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e "$ {SQLCMD} \ G; "| awk '$1 =" Master_Host: "{print $2}'' LOCAL _ ADDR ='/sbin/ifconfig eth1 | awk-f ': ''/inet addr/{print $2} '| sed's/[a-zA-Z] // g'' if [[" $ {MASTER_ADDR }"! = "$ {LOCAL_ADDR}"]; then echo The MySQL Slave is not lawful: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # obtain the Slave terminal information, IO_STATUS = 'MySQL-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e "$ {SQLCMD} \ G; "| awk '$1 =" Slave_IO_Running: "{print $2}'' SQL _ STATUS = 'MySQL-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e "$ {SQLCMD }\ g; "| awk '$1 =" Slave_ SQL _Running: "{print $2}'' if [["$ {IO_STATUS }"! = "Yes" | "$ {SQL _STATUS }"! = "Yes"]; then echo The MySQL Replication is not synchronous: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # make another judgment, to ensure that data synchronization is absolutely normal [create test data] mysql-uroot-e "create database if not exists mytestdb; "sleep 3 retval = 'MySQL-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e" show databases; "| grep mytestdb 'MySQL-uroot-e" drop database if exists mytestdb; "if [[" $ {retval} X "=" X "]; then echo The MyS QL Replication is not synchronous at: 'date + % F "" % H-% M-% s' >>$ {LOGFILE} exit 1fi # When synchronization has been performed, you also need to determine the currently synchronized binlog, to determine which binlogSLAVE_BINLOG1 = 'MySQL-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e "$ {SQLCMD} \ G has expired; "| awk '$1 =" Master_Log_File: "{print $2}'' SLAVE _ BINLOG2 = 'MySQL-h $ {SLAVE_ADDR}-u $ {SLAVE_USER}-p $ {SLAVE_PWD}-e "$ {SQLCMD }\ g; "| awk '$1 =" Relay_Master_Log_File: "{print $2}'' # get Ma The current binlog file and binlog path MASTER_BINLOG = 'MySQL-uroot-e "show master status; "| grep-v '^ +' | tail-1 | awk '{print $1}'' # the master/slave has been synchronized to the same binlogif [["$ {SLAVE_BINLOG1} "=" $ {SLAVE_BINLOG2} "&" $ {SLAVE_BINLOG1} "=" $ {MASTER_BINLOG} "]; then CURR_BINLOG = "$ {MASTER_BINLOG}" # the master and slave databases have been synchronized, however, the slave binlog has not caught up with the latest binlogelif [["$ {SLAVE_BINLOG1}" = "$ {SLAVE_BINLOG2}" & "$ {SLAVE_BINLOG1 }"! = "$ {MASTER_BINLOG}"]; then CURR_BINLOG = "$ {SLAVE_BINLOG1}" # the master and slave nodes have been synchronized, and the binlogs of the master and slave nodes are consistent, however, relaylog is inconsistent. elif ["$ {SLAVE_BINLOG1 }"! = "$ {SLAVE_BINLOG2}" & "$ {SLAVE_BINLOG1}" = "$ {MASTER_BINLOG}"]; then CURR_BINLOG = "$ {SLAVE_BINLOG2}" else echo Has noknown error: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi mysql-uroot-e "purge binary logs to '$ {CURR_BINLOG }'; "if [[$? -Eq 0]; then echo Clear MySQL binlog is OK at: 'date + % F "" % H-% M-% s' >>$ {LOGFILE} fi

# crontab -e 30 05 * * * /data/scripts/delete_mysql_binlog.sh >/dev/null 2>&1

Fix MySQL master-slave synchronization

#! /Bin/shPATH =/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin: /usr/local/bin LOGFILE =/data/repair_mysql_sync _ 'date + % f '. logSQLCMD1 = "show slave status" # check whether MySQL has been started if [['ps aux | grep mysqld | grep-v grep '"X" = "X"]; then echo The MySQL is not running: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # obtain the MySQL slave Relay binlog path retval = 'grep "^ relay-log "/etc/my. cnf | grep-v relay-log-| grep '/''I F [["$ {retval}" = "X"]; then RELAY_BINLOG_PATH = 'PS aux | grep-w mysqld | grep-v grep | awk '{print $13}' | awk-f' = ''{print $2 }'' else RELAY_BINLOG_PATH = 'dirname $ (echo $ {retval} | awk-f' = ''{print $2 }') 'Fi # Find the master.info file, used to locate Binlog information MASTER_FILE = 'PS aux | grep-w mysqld | grep-v grep | awk '{print $13}' | awk-f' = ''{print $2} ''/master. infoif [[! -E $ {MASTER_FILE}]; then echo This Server is not MySQL Slave: 'date + % F "" % H-% M-% s' >$ {LOGFILE} exit 1fi # obtain the current synchronization status IO_STATUS = 'MySQL-uroot-e" $ {SQLCMD1} \ G; "| awk '$1 =" Slave_IO_Running: "{print $2}'' SQL _ STATUS = 'MySQL-uroot-e "$ {SQLCMD1} \ G; "| awk '$1 =" Slave_ SQL _Running: "{print $2}'' if ["$ {IO_STATUS}" = "Yes" & "$ {SQL _STATUS}" = "Yes"]; then echo Now, the MySQL Replication is sy Nchronous at: 'date + % F "" % H-% M-% s' >>$ {LOGFILE} exit 0fi # from the master.info file, obtain the MySQL master synchronization information REPLI_INFO = 'SED '/^ $/D' $ {MASTER_FILE} | tail + 2 | head-5 'repli _ BINLOG_FILE = 'echo $ {REPLI_INFO} | awk '{print $1} ''REPLI _ IPADDR = 'echo $ {REPLI_INFO} | awk' {print $3}'' REPLI _ USER = 'echo $ {REPLI_INFO} | awk '{print $4} ''REPLI _ PWD = 'echo $ {REPLI_INFO} | awk' {print $5}'' # Delete useless Relay binlogrm-rf $ {RELAY_BINLO G_PATH}/*-relay-bin. * ## synchronize SQLCMD2 = "change master to master_host = '$ {REPLI_IPADDR}', master_user = '$ {REPLI_USER }', master_password = '$ {REPLI_PWD}', "SQLCMD2 =" $ {SQLCMD2} master_log_file = '$ {REPLI_BINLOG_FILE}', master_log_pos = 0 "mysql-uroot-e" stop slave; "mysql-uroot-e" $ {SQLCMD2}; "mysql-uroot-e" start slave; "# if a duplicate record occurs during synchronization, synchronization fails, skip while truedo sleep 2 IO_STATUS = 'MySQL-uroot-e "$ {SQ LCMD1} \ G; "| awk '$1 =" Slave_IO_Running: "{print $2}'' SQL _STATUS = 'MySQL-uroot-e "$ {SQLCMD1} \ G; "| awk '$1 =" Slave_ SQL _Running: "{print $2}'' BEHIND_STATUS = 'MySQL-uroot-e "$ {SQLCMD1} \ G; "| awk '$1 =" Seconds_Behind_Master: "{print $2}'' SLAVE_BINLOG1 = 'MySQL-uroot-e "$ {SQLCMD1} \ G; "| awk '$1 =" Master_Log_File: "{print $2}'' SLAVE_BINLOG2 = 'MySQL-uroot-e "$ {SQLCMD1} \ G; "| awk '$1 =" Relay_Maste R_Log_File: "{print $2}'' # when an error occurs, record the error information to the log file and skip the error to continue synchronization if [["$ {IO_STATUS }"! = "Yes" | "$ {SQL _STATUS }"! = "Yes"]; then ERRORINFO = 'MySQL-uroot-e "$ {SQLCMD1} \ G;" | awk-f ': ''$ 1 = "Last_Error" {print $2} ''echo "The MySQL synchronous error information: $ {ERRORINFO} ">> {LOGFILE} mysql-uroot-e" stop slave; "mysql-uroot-e" set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1; "mysql-uroot-e" start slave; "# synchronization has been completed, exit elif ["$ {IO_STATUS}" = "Yes" & "$ {SQL _STATUS}" = "Yes" & "$ {SLAVE_BINLOG1}" = "$ {SLAVE_BINLOG2} "& $ {BEHIND_STATUS}-eq 0]; then echo The MySQL synchronous is OK at: 'date + % F "" % H-% M-% s' >$ {LOGFILE} break fidone