A ton of data to import with a shell

Standard

schema_import.sh

#!/bin/bash
for f in *schema*.gz; do
  DBName=$(echo $f | cut -d - -f 5)
  echo "Create "${DBName}
  echo "create database if not exists ${DBName}" | /usr/bin/mysql -u root -pXXXXXX
  zcat ${f} | /usr/bin/mysql -u root -pXXXXXX ${DBName}
  echo "Created"
done

data_import.sh

#!/bin/bash
for f in *data*.gz; do
  DBName=$(echo $f | cut -d - -f 5)
  echo "Import "${DBName}
  zcat ${f} | /usr/bin/mysql -u root -pXXXXXX ${DBName}
  echo "Imported"
done

一大坨文件

/nfs/dbs/31/mysql-slave-31.lololololol.com-schema-user_cluster_1-11-27-16.sql.gz
/nfs/dbs/31/mysql-slave-31.lololololol.com-schema-user_cluster_2-11-27-16.sql.gz
...
 
/nfs/dbs/31/mysql-slave-31.lololololol.com-data-user_cluster_1-11-27-16.sql.gz
/nfs/dbs/31/mysql-slave-31.lololololol.com-data-user_cluster_2-11-27-16.sql.gz
...

ps:
公司收了一家美国的论坛服务商,那边留了个人交接.
放了个NFS,上去一看,好家伙.每台机器目录下有几千个小的备份文件.
问其why,答long story…
好吧…好在文件命名还算规律。

dump.sh

MYSQL_PORT=$1
FILE_NAME=$2
MYSQL_IP=x.x.x.x
MYSQL_USER=uid
MYSQL_PASS=pwd
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_IP} -P${MYSQL_PORT}"
#
# Collect all database names except for
# mysql, information_schema, and performance_schema
#
SQL="SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN"
SQL="${SQL} ('mysql','information_schema','performance_schema')"
 
DBLISTFILE=/tmp/DatabasesToDump-${FILE_NAME}.txt
mysql ${MYSQL_CONN} -ANe"${SQL}" > ${DBLISTFILE}
 
DBLIST=""
for DB in `cat ${DBLISTFILE}` ; do DBLIST="${DBLIST} ${DB}" ; done
 
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} --databases ${DBLIST} | pv | gzip > db-${FILE_NAME}.sql.gz
wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb && dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb && apt-get update && apt-get -y upgrade && apt-get -y install lvm2 xfsprogs percona-server-server-5.7
 
# add 2 x 128 ssd and 1 x 256 hdd
# ssd n enter enter enter enter t enter 8e enter w enter
fdisk /dev/sdc
fdisk /dev/sdd
 
# hdd
fdisk /dev/sde
 
pvcreate /dev/sdc1 /dev/sdd1 /dev/sde1 && vgcreate mysql /dev/sdc1 /dev/sdd1 && vgcreate data /dev/sde1 && lvcreate --name data --size 240G mysql && lvcreate --name backups --size 240G data
 
mkfs.xfs /dev/mapper/mysql-data && mkfs.xfs /dev/mapper/data-backups
 
edit /etc/fstab
/dev/mapper/mysql-data /var/lib/mysql auto  defaults,nobarrier 0 2
/dev/mapper/data-backups /data auto  defaults,nobarrier 0 2
 
service mysql stop && cd /var/lib && mv mysql mysql.old && mkdir mysql && mkdir /data && mount -a && mv mysql.old/* mysql/  && chown mysql:mysql mysql
 
vi /etc/security/limits.d/91-mysql.conf
mysql   soft    nofile 400000
mysql   hard    nofile 400000 
 
/etc/sysctl.conf
fs.file-max = 20000000
net.ipv4.tcp_fin_timeout = 10
kernel.pid_max = 65535
kernel.randomize_va_space = 1
net.core.netdev_max_backlog=32768
net.core.rmem_max = 8388608
net.core.somaxconn = 16384
net.core.wmem_max = 8388608
net.ipv4.conf.all.accept_redirects = 0
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.all.log_martians = 1
net.ipv4.conf.all.rp_filter = 1
net.ipv4.conf.all.secure_redirects = 0
net.ipv4.conf.default.accept_redirects = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.conf.default.log_martians = 1
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.secure_redirects = 0
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.ip_forward=0
net.ipv4.ip_local_port_range = 2000 65000
net.ipv4.tcp_max_syn_backlog=8192
net.ipv4.tcp_rmem = 4096 87380 8388608
net.ipv4.tcp_syncookies=0
net.ipv4.tcp_syncookies = 0
net.ipv4.tcp_tw_recycle = 0
net.ipv4.tcp_tw_reuse = 0
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_wmem = 4096 87380 8388608
vm.overcommit_memory = 1
vm.swappiness = 0
fs.aio-max-nr = 500000
 
sysctl -p
 
echo session required pam_limits.so >> /etc/pam.d/common-session
 
vi /etc/mysql/percona-server.conf.d/mysqld.cnf 
[mysqld]
user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
innodb_use_native_aio=1
max_connections=4096
wait_timeout=5
interactive_timeout=120
myisam_sort_buffer_size=1024M
sort_buffer_size=1024M
innodb_file_per_table=ON
skip-name-resolve
default-storage-engine=InnoDB
max_allowed_packet=64M
# 48 hours
expire_logs_days = 3
server-id              = 108
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_buffer_pool_size = 28000M
innodb_flush_method = O_DIRECT
innodb_file_per_table
bind-address = 0.0.0.0
log-error    = /var/log/mysql/error.log
#log-erorr = /dev/null
log_error_verbosity=3
# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
sql_mode=""
symbolic-links=0
 
# Forum requires this
lower_case_table_names  = 1
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate-do-table              = account.identity_data
replicate-do-table              = account.signature_data
replicate-do-table              = account.account_usertypes
replicate-do-table              = account.banned_words
replicate-wild-do-table         = forum\_skeleton.%
replicate-wild-do-table = domain\_%.%
replicate-wild-do-table = user\_cluster\_%.%
slave-skip-errors               = 1062
open-files-limit=400000
myisam-recover-options=FORCE,BACKUP

Using Custom Amazon CloudWatch Metrics

Standard

日志样式如下:

...
[2015-07-16 09:01:29] production.ERROR: Aws ...
...xxx...
[2015-07-16 09:07:23] production.ERROR: [Notification - getDetails] ...
...xxx...
[2015-07-16 09:16:04] production.ERROR: URL: ...
...xxx...
...

放crontab里跑的shell

#!/bin/bash
if  [ ! -n "$1" ] ;then
  echo -e "no argument\nusage: ./SendPHPErrorNumber2Cloudwatch.sh 5"
  exit
fi
periodMinute=$1
 
dateNow=`date "+%Y-%m-%d"`
 
logFile="/var/log/yemaosheng_com_php-log-"${dateNow}".txt"
if [ ! -f "$logFile" ]; then
  echo "No File"
  exit
fi
 
minuteAgo=`date "+%Y-%m-%d %H:%M:%S" -d "${periodMinute} minute ago"`
 
instanceId=`GET http://169.254.169.254/latest/meta-data/instance-id`
 
#tail -n 500 ${logFile} | awk '/^\[/{print $1" "$2}'
#tail -n 500 ${logFile} | grep ERROR
#    sed 's/^.//' | sed 's/.$//'
#    tr -d "[" | tr -d "]"
#    cut -c 2-20
 
errorNum=`tail -n 500 ${logFile} | grep ERROR | cut -c 2-20 | awk '{if ($0>minuteAgo){print minuteAgo} }' minuteAgo="$minuteAgo" | wc -l`
 
/usr/local/bin/aws cloudwatch put-metric-data --metric-name PHPError --namespace LogError --dimensions Name=InstanceId,Value=${instanceId} --value ${errorNum} --timestamp `date "+%Y-%m-%dT%H:%M:%S.000Z"`

详细记录用户操作

Standard

vi /etc/profile

# Logs for user operation
USER=`who -u am i | awk '{print $1}'`
TIME=`date +%Y%m%d.%H:%M.%S`
LOGS="/tmp/.history/$TIME.$USER.history"
 
if [ ! -d /tmp/.history ] 
then 
   mkdir /tmp/.history
   chmod 777 /tmp/.history
fi 
 
script -q $LOGS

封超过100个连接的IP防DDOS

Standard
#!/bin/sh
/bin/netstat -an|grep ESTABLISHED|awk{print $5}|awk -F: ‘{print $1}|sort|uniq -c|sort -rn|grep -v -E172.10|127.0|awk{if ($2!=null && $1>100) {print $2}}>/tmp/ddosip
for i in $(cat /tmp/ddosip)
do
/sbin/iptables -I INPUT  -p tcp -m tcp -s $i –dport 80 –syn -j REJECT
done

用enca检查文件编码的shell

Standard
#!/bin/bash
go()
{
 tmp=`find $1 -name "*.$2"`
 path=( ${tmp} )
 for((i=0;i<${#path[@]};i++));do
  encode=`enca -L none ${path[${i}]} -e`
  if [[ $encode == ASCII* ]]
  then
   echo $encode : ${path[${i}]}
  fi
 done
}
go `pwd` php
go `pwd` phtml
go `pwd` js
go `pwd` css
go `pwd` yemaosheng.com

转载请注明出处 http://yemaosheng.com

Converting captured rm stream to mp3 using mplayer

Standard

# emerge win32codecs
# USE=”mp3 real” emerge mplayer
安装后执行命令报错如下:
Error: /opt/RealPlayer/codecs/sipr.so.6.0: cannot open shared object file: No such file or directory
解决方法:emerge realcodecs

附将同目录下所有*.rm转换为*.mp3的shell

#!/bin/sh
for file in *.rm
do
  if [ -f $file ]; then
    mplayer $file -ao pcm
    echo "CONVERTING $file FORMAT TO MP3"
    lame -h -b 256 audiodump.wav `basename $file .rm`".mp3"
    rm -f audiodump.wav
  fi
done

转载请注明出处: http://yemaosheng.com

mysql不停服修复主从同步的shell

Standard
#!/bin/sh
#yemaosheng.com上传到从DB修改后执行!
#主DB信息
MDB_IP=192.168.3.30
MDB_USERNAME=op
MDB_PASSWORD=123456
#从DB信息
SDB_IP=127.0.0.1
SDB_USERNAME=root
SDB_PASSWORD=132456
#相互同步的库名
DB=("db_1" "db_2")
 
/etc/init.d/mysqld restart
 
for((i=0;i<${#DB[@]};i++));do
	echo ${DB[${i}]}".sql Dump..."
	mysqldump -h${MDB_IP} -u${MDB_USERNAME} -p${MDB_PASSWORD} -R --triggers  --single-transaction --flush-logs --master-data --database ${DB[${i}]} > ${DB[${i}]}.sql 
	echo "Ok"
done
 
mysql -u${SDB_USERNAME} -p${SDB_PASSWORD} -h${SDB_IP} -e 'slave stop;'
 
for((i=0;i<${#DB[@]};i++));do
	echo ${DB[${i}]}".sql Input..."
	mysql -u${SDB_USERNAME} -p${SDB_PASSWORD} -h${SDB_IP} < ${DB[${i}]}.sql
	echo "Ok"
done
 
mysql -u${SDB_USERNAME} -p${SDB_PASSWORD} -h${SDB_IP} -e 'slave start;'
 
mysql -u${SDB_USERNAME} -p${SDB_PASSWORD} -h${SDB_IP} -e 'show slave status\G'
 
for((i=0;i<${#DB[@]};i++));do
	echo ${DB[${i}]}".sql Del..."
	rm -f ${DB[${i}]}.sql
	echo "Ok"
done

Mysql同步跳错shell

Standard

估计程序里面还是有点问题,经常造成大面积的同步出错,出错信息基本上是那种主键冲突之类的.
经常要几十台几十台的这样跳错太烦了,写了个shell.

#!/bin/sh
while [ "0" != $(mysql -h 127.0.0.1 -u yemaosheng_com -ppasswd -e "show slave status\G" | grep "Seconds_Behind_Master: " | awk -F ': ' '{print $2}') ]; do
  if [ 0 -lt $(mysql -h 127.0.0.1 -u yemaosheng_com -ppasswd -e "show slave status\G" | grep "Last_Error: Error" | wc -l) ]; then
    mysql -h 127.0.0.1 -u yemaosheng_com -ppasswd -e "SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;slave start;";
  fi
done

Mysql多机备份shell

Standard
#!/bin/sh
 
##
## 功能:备份Mysql数据库
## 说明:该版本将备分所有数据库,指定不备份的数据库除外
##
 
## 服务器配置段
## 说明:1、以下配置信息全为数组各数组元数用空格分开,
##       2、以SERVER为准,PORT、USER、PASS、NO_BAK_DB一一对应,若是后三个参数都相应可以不填,
##          将以默认的GLOBALS参数为准,若其中一台SEVER的参数不同,其前面的的参数需补全方可
 
## 设置保留多少天的备份文件
DAY=15
 
SERVER=("192.168.0.11" "192.168.0.12")
PORT=()
USER=()
PASS=()
## 不用备份的数据库列表,用 "|" 分开,与上面的参数一一对应
NO_BAK_DB=()
 
GLOBALS_PORT="3306" 
GLOBALS_USER=""
GLOBALS_PASS=""
GLOBALS_NO_BAK_DB="test|mysql|information_schema"
 
DATE_PATH="/data/database"
MYSQLSHOW="/usr/local/mysql/bin/mysqlshow"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump"
LOGFILE="/var/log/bak_mysql.log"
 
_SERVER_=""
_PORT_="${GLOBALS_PORT}"
_USER_="${GLOBALS_USER}"
_PASS_=""
_NO_BAK_DB_="${GLOBALS_NO_BAK_DB}"
_DATE_=`date +%Y%m%d%H`
 
check_dir ()
{
	# process: check_dir function
	# Syntax:  check_dir
	# Returns:
	#       N/A
 
	if [ -n "$1" ]; then
		_PATH_="$1"
	else
		echo "脚本内部发生错误"
		exit 1
	fi
 
	if [ ! -d "${_PATH_}" ]; then
	   mkdir -p "${_PATH_}"
	   make_log "Creating ${_PATH_}"
	fi
}
 
make_log ()
{
	# process: make_log function
	# Syntax:  make_log
	# Returns:
	#       N/A
 
	check_dir "${LOGFILE%/*}"
	echo -e "[$(date +%Y-%m-%d' '%H:%M:%S)] ${1}" >> "${LOGFILE}"
}
 
tar_sql ()
{
	# process: tar_sql function
	# Syntax:  tar_sql <path>
	# Returns:
	#       N/A
 
	cd "${1%/*}"
	make_log "PWD: ${1%/*}"
 
	make_log "tar zcf ${1##*/}.tgz ${1##*/} >> "${LOGFILE}" 2>&1"
	tar zcf ${1##*/}.tgz ${1##*/} >> "${LOGFILE}" 2>&1
 
	make_log "rm -rf ${1##*/} >> "${LOGFILE}" 2>&1"
	rm -rf ${1##*/} >> "${LOGFILE}" 2>&1
}
 
rm_tar ()
{
	# process: rm_tar function
	# Syntax:  rm_tar <path>
	# Returns:
	#       N/A
 
	for _sql_file_ in `find "${1}" -name "*[0-9].tgz" -type f -mtime +${DAY}`; do
		make_log "rm -f ${_sql_file_}"
		rm -f ${_sql_file_}
	done 
}
 
for((i=0;i<${#SERVER[@]};i++));do
	if ((${#PORT[@]} > 0 & i < ${#PORT[@]})); then
		if [ "${PORT[${i}]}" = "" ]; then
			_PORT_="--port=${GLOBALS_PORT}"
		else
			_PORT_="--port=${PORT[${i}]}"
		fi
	else
		_PORT_="--port=${GLOBALS_PORT}"
	fi
 
	if ((${#USER[@]} > 0 & i < ${#USER[@]})); then
		if [ "${USER[${i}]}" = "" ]; then
			_USER_="--user=${GLOBALS_USER}"
		else
			_USER_="--user=${USER[${i}]}"
		fi
	else
		_USER_="--user=${GLOBALS_USER}"
	fi
 
	if ((${#PASS[@]} > 0 & i < ${#PASS[@]})); then
		if [ "${PASS[${i}]}" = "" ]; then
			_PASS_=""
		else
			_PASS_="--password=${PASS[${i}]}"
		fi
	else
		_PASS_="--password=${GLOBALS_PASS}"
	fi
 
	if [ "${_PASS_}" != "" ]; then
		_LOG_PASS_="--password=xxxxxxx"
	else
		_LOG_PASS_=""
	fi
 
	if ((${#NO_BAK_DB[@]} > 0 & i < ${#NO_BAK_DB[@]})); then
		_NO_BAK_DB_="${NO_BAK_DB[${i}]}"
	else
		_NO_BAK_DB_="${GLOBALS_NO_BAK_DB}"
	fi
 
	_DATE_PATH_="${DATE_PATH}"
	DATE_PATH="${DATE_PATH}/${SERVER[${i}]}"
 
	check_dir "${DATE_PATH}/${SERVER[${i}]}_${_DATE_}"
 
	for db in `${MYSQLSHOW} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_PASS_} | awk '{++n;if(n>3&&NF>=3&&$2!~"^('${_NO_BAK_DB_}')$")print$2}'`; do
 
		make_log "${MYSQLDUMP} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_LOG_PASS_} -R --triggers --database ${db} > ${DATE_PATH}/${SERVER[${i}]}_${_DATE_}/${db}.sql"
 
		${MYSQLDUMP} --host=${SERVER[${i}]} ${_PORT_} ${_USER_} ${_PASS_} -R --triggers --database ${db} > ${DATE_PATH}/${SERVER[${i}]}_${_DATE_}/${db}.sql
 
	done
 
	tar_sql "${DATE_PATH}/${SERVER[${i}]}_${_DATE_}"
	rm_tar "${DATE_PATH}"
	DATE_PATH="${_DATE_PATH_}"
done

Mysql定时备份shell

Standard
#!/bin/sh
dbname=yemaosheng_com #数据库名称
backupDir=/tmp/mysqlbackup/ #备份存放路径
cd $backupDir
if [ -f $dbname".tar.gz" ]
then
        rm -rf $dbname
        rm -rf $dbname".tar.gz"
fi
mysqlhotcopy $dbname $backupDir
tar zcvf $dbname".tar.gz" "./"$dbname
#! /bin/bash
 
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/$TIMESTAMP"
MYSQL_USER="backup"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="password"
MYSQLDUMP=/usr/bin/mysqldump
 
mkdir -p "$BACKUP_DIR/mysql"
 
databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
 
for db in $databases; do
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/mysql/$db.gz"
done

保存到 /etc/cron.daily/ 目录下,加上777就行