Some of Mysql's functional Linux shell script sharing _mysql

memcached startup script

# vim/etc/init.d/memcached

#!/bin/bash #======================================================================================= # Chkconfig: -# description:distributed Memory Caching Daemon # processname:memcached #======================================= ================================================ ipaddr= '/sbin/ifconfig eth1 | Awk-f ': '/inet addr/{print $} ' | 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 Me
  Mcached ... "$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 star T
} reload () {echo-n $ "reloading memcached ..."/sbin/killproc $BINFILE-hup retval=$?
        
echo return $RETVAL} case "in Start" start;;
        
stop) stop;;
        
restart) restart;;
  Condrestart) [e $LOCKFILE] && restart retval=$?
        
;;
        
reload) reload;;
  Status $prog retval=$?
        
;;
 *) echo "Usage: $ {start|stop|restart|condrestart|status}" retval=1 Esac

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 remove MySQL Binlog, which has been synchronized at the master end, "Run on the master" to reduce disk space # run every 5:30 a day # Note: To add the account number on the Slave side that allows Master access: check_binlog, Password: Bin log_2356 "# running on the MySQL Master End" is currently used only for one main from the sync mode, for the more from the situation is not considered for the moment "#=======================================================
================================ Path=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin # # Slave End 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 view command sqlcmd=" show slave Status "#============================= ========================================================== # # Check if MySQL has run if [[' PS aux | grep mysql[d] | wc-l '-eq 0] ]; Then echo the MySQL isn't running at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 1 fi # test slave-end connectivity Nohup M YSQLADMIN-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 is connected at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 1 fi # Check if legal S Lave master_addr= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-E "${sqlcmd}\g;" | awk ' $1== ' master_host: ' {print $} ' local_addr= '/sbin/ifconfig eth1 | Awk-f ': '/inet addr/{print $} ' | Sed ' s/[a-za-z]//g ' if [[' ${master_addr} '!= ' ${local_addr} ']]; Then echo the MySQL Slave is not lawful at: ' Date +%f ' "%h-%m-%s ' >> ${logfile} exit 1 fi # to obtain Slave information to confirm Io_status= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-e ' ${sqlcmd}\g if the condition is in normal sync awk ' $1== ' slave_io_running: ' {print $} ' ' sql_status= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-e ' ${SQLCMD }\g; "| awk ' $1== ' slave_sql_running: ' {print $} ' if [[' ${io_status} '!= ' Yes ' | | ' ${sql_status} "!=" yes "]; Then echo the MySQL Replication is not syNchronous at: ' Date +%f ' "%h-%m-%s ' >> ${logfile} exit 1 fi # again make a judgment to ensure that data synchronization is absolutely normal" create test Data "MYSQL-UROOT-E" cre Ate database if not exists MYTESTDB: "Sleep 3 retval= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-e" show Da tabases; "| grep mytestdb ' mysql-uroot-e ' drop database if exists mytestdb; "If [[" ${retval}x "=" X "]]; Then echo the MySQL Replication is not synchronous at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 1 fi # in already with Step, you will also need to determine which Binlog is currently synchronized to determine which are already expired Binlog slave_binlog1= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-e ${ sqlcmd}\g; "| awk ' $1== ' master_log_file: ' {print $} ' ' slave_binlog2= ' mysql-h${slave_addr}-u${slave_user}-p${slave_pwd}-e ' ${ sqlcmd}\g; "| awk ' $1== ' relay_master_log_file: ' {print $} ' # gets the Master end, the current Binlog file and Binlog path master_binlog= ' mysql-uroot-e ' show Master status; "| Grep-v ' ^+ ' | Tail-1 | awk ' {print} ' # # master/slave end has synchronized to the same binlog if [[' ${slave_binlog1} ' = ' ${slave_binlog2} ' && ' ${SLAVE_BINLOG1} "=" ${master_binlog} "]]; Then curr_binlog= "${master_binlog}" # # The Master and slave side has been synchronized, but the binlog from the end has not been catching up to the main side of the latest Binlog elif [[' ${slave_binlog1} ' = ' ${slave_b INLOG2} "&&" ${slave_binlog1} "!=" ${master_binlog} "]; Then curr_binlog= "${slave_binlog1}" # # The Master and slave end has been synchronized, the binlog of the master-slave end is consistent, but Relaylog also inconsistent elif [[' ${slave_binlog1} '!= ' ${slave_b INLOG2} "&&" ${slave_binlog1} "=" ${master_binlog} "]; Then curr_binlog= "${slave_binlog2}" else echo has noknown error at: ' Date +%f '%h-%m-%s ' >> ${logfile} E Xit 1 fi mysql-uroot-e "purge binary logs to ' ${curr_binlog} ';" If [[$?-eq 0]];

 Then echo clear MySQL Binlog are OK at: ' Date +%f '%h-%m-%s ' >> ${logfile} fi

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

Repair MySQL master-slave sync

#!/bin/sh Path=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin logfile=/data/repair_mysql_sync_ ' Date +%f '. Log sqlcmd1= "show slave status" # see if MySQL has started if [[' PS aux | grep mysqld | grep-v grep ' ' x ' = ' x ']]; Then echo the MySQL is not running at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 1 fi # get MySQL from end relay Binlog The path retval= ' grep ' ^relay-log '/etc/my.cnf | Grep-v relay-log-| grep '/' if [[' ${retval} ' = ' X ']]; Then relay_binlog_path= ' ps aux | Grep-w mysqld | Grep-v grep | awk ' {print $} ' | Awk-f ' = ' {print $} ' else Relay_binlog_path= ' dirname $ (echo ${retval} | awk-f ' = ' {print $} ') ' Fi # Find Master. Info file, for locating binlog information master_file= ' PS aux | Grep-w mysqld | Grep-v grep | awk ' {print $} ' | Awk-f ' = ' {print $} '/master.info if [[!-e ${master_file}]]; Then echo this Server isn't MySQL Slave at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 1 fi # get current sync status Io_st atus= ' mysql-uroot-e ' ${sqlcmd1}\g; "| awk ' $1== ' SlAve_io_running: "{print $} ' sql_status= ' Mysql-uroot-e" ${SQLCMD1}\G; "| awk ' $1== ' slave_sql_running: ' {print $} ' if [[' ${io_status} ' = ' yes ' && ' ${sql_status} ' = ' yes ']]; Then echo now, the MySQL Replication are synchronous at: ' Date +%f '%h-%m-%s ' >> ${logfile} exit 0 fi # from MAS Ter.info file, obtain the synchronization information of the MySQL main end repli_info= ' sed '/^$/d ' ${master_file} | Tail +2 | Head-5 ' repli_binlog_file= ' echo ${repli_info} | awk ' {print} ' repli_ipaddr= ' echo ${repli_info} | awk ' {print $} ' repli_user= ' echo ${repli_info} | awk ' {print $} ' repli_pwd= ' echo ${repli_info} | awk ' {print $} ' # # Remove the unwanted RELAY binlog rm-rf ${relay_binlog_path}/*-relay-bin.* # # directly from 0 locations Sync sqlcmd2= ' Change maste R 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" ${SQLCM
  
D2}; "Mysql-uroot-e" Start slave; # # If there is a duplicate record during synchronizationCauses the synchronization to fail, skip the while True does sleep 2 io_status= ' mysql-uroot-e ' ${sqlcmd1}\g | awk ' $1== ' slave_io_running: "{print $} ' sql_status= ' Mysql-uroot-e" ${SQLCMD1}\G; "| awk ' $1== ' slave_sql_running: "{print $} ' behind_status= ' Mysql-uroot-e" ${SQLCMD1}\G; "| awk ' $1== ' seconds_behind_master: "{print $} ' slave_binlog1= ' Mysql-uroot-e" ${SQLCMD1}\G; "| awk ' $1== ' master_log_file: "{print $} ' slave_binlog2= ' Mysql-uroot-e" ${SQLCMD1}\G; "| awk ' $1== ' relay_master_log_file: ' {print $} ' # # An error occurred, the error message is logged to the log file and the error continues to sync if [[' ${io_status} '!= ' Yes ' | | "${sql_status}"!= "Yes"]]; Then errorinfo= ' mysql-uroot-e ' ${sqlcmd1}\g; "| Awk-f ': ' $1== ' Last_error ' {print $} ' echo ' The MySQL synchronous Error information: ${errorinfo} ' >> ${log
    FILE} mysql-uroot-e "Stop slave;"
    Mysql-uroot-e "Set GLOBAL sql_slave_skip_counter=1;"
      
  Mysql-uroot-e "Start slave;" # # Completed sync, normal 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 fi do