"Shell"-"batch remote MySQL, execute command"-"summary"

Yesterday before work, the boss gave a batch of log database IP address, need to count the Log table message field Top 10 results, and output to an Excel file.
Spot checks, there are two types of table names that end in the date of the day. Due to the number of too many, time is tight, had to write batch script to solve the problem.
And with this extension, parsing several of the common shell programs, the main script written in the second half of the article.
Learning the shell is a flexible use of commands, form their own way of thinking, and writing habits, script reference can be.

Problem solving Process steps:
1. Combing IP address and corresponding table name
2. Determine query sql
3. Batch query data

Complete script attached at the end of the article

Analysis of the commonly used knowledge points:

1. Date: Table field with date time in the table on the end of the date
Note: The habit of defining variables for dates in the shell.
<1> Current date Time and format
date_mark= ' date + '%y%m%d ' results: 20161214
date_mark= ' date + '%y-%m-%d%h:%m:%s ' results: 2016-12-14 18:20:11
　　　　
Past date Time and format
date_mark= ' date-d ' 1 days ago ' + '%y%m%d ' results: One day ago: 20161213
date_mark= ' date-d ' 1 hour ago ' + '%y-%m-%d%h:%m:%s ' results: 2016-12-14 17:20:11
　　　　
Modified-d ': 7 days ago: 7 day ago 7 hours ago: 7 hours ago
　　　　
Table name: Tb_name= "Log_${date_mark}"

2. Traversal thinking and processing of loops and rows
<1> Bulk Remote execute command, bulk telnet port, bulk ping host
<2>: Iterate through each line of the file, adding, intercepting, and outputting the operation.
main idea: Read IP or line first, then process IP or line, then use IP or line
Example:
1. Sequentially output each line in the file
command line while loop traversal: Cat Ip.txt | While read line, do echo $line;d One
command line for loop traversal: For lines in ' Cat ip.txt ';d o echo $line;d One
called in the script: Ip.txt, and then loops through the. ip file as the execution parameter of the script
#!/bin/sh
ipfile=$1
Cat ${ipfile}|while Read line
Do
Echo ${line}
Done
script execution:./xx.sh ip.txt
2. Handling of rows, one row with multiple split fields is required to process
format: 172.21.1.1,3306
intercept the first column of IP on one line:
ip= ' echo ${line}|awk-f ', ' {print '} '
port= ' echo ${line}|awk-f ', ' {print $} '
awk-f ', ' {print '} ' equals cut-f1-d ', '
Add Content:
echo "${ip}_${port}:ok" Result: 172.21.1.1_3306:ok

3. About custom Functions
<1>: Non-pass parameter function
Fun_name ()
{
name= ' Kata '
echo "My name is ${name}."
}
Fun_name #调用方式, directly: My name is Kata. Output to screen
<2>. pass parameter function
Fun_name ()
{
name=$1
echo "My name is ${name}."
}
　　
Fun_name "Alias" #调用方式, directly: My name is Alias. Output to screen
<3>. Multi-parameter function

Fun_name ()
{
Name=$1
Age=$2
echo "My name is ${name} and my age is ${age}."
}
　　
Fun_name "Sunny" #调用方式, directly: My name is Sunny and my age is 18. Output to Screen

<4> Assign a function result to a variable
status= ' fun_name ' Alias ' #变量status的内容就是: My name is alias. The status variable is then applied to other program calls
<5> custom Function Case: What is the corresponding table format in the statistics IP database?
Idea: Choose a format table as a judgment, there is a mark, does not exist is another. Prerequisites: Only one table exists on each IP to avoid accidental two kinds of tables try

Fun_check ()
{
ip=$1 #自定义函数传参
tb_like=$2
mysql_cmd= "/usr/local/mysql/bin/mysql-uadmin-padmin-h${ip}-p3306" #变量只有IP, ports can also define variables
sql= "Select COUNT (*) from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' logdb ' and table_name
= ' ${tb_like}_${date_mark} '; "
status= ' ${mysql_cmd}-nbe "${sql}" ' #查询数据库表结果赋值给变量
if [${status} = = ' 1 '];then
Echo ${ip}
Else
The echo #此处输出空行或者去掉else makes it easy to copy the screen IP list. Not even if judged, direct: echo "${ip}:${status}" again filter.
fi
}
to call a function in a while loop:
#!/bin/sh
.
.
.
cat $IP _file1|while Read line
Do
ip= ' echo $line |cur-f1-d '
fun_check "${ip}" "Ha_logging" #手动修改下第二个参数, another table is also tested, do not omit the IP.
Done
4. About output Files
append query data for the table on n IP to an Excel. SQL query results do not take a table header. Otherwise, multiple header rows will appear in the file.
Specify the Nbe parameter with the MySQL command, output only data results, and do not display field names.
mysql-u-p-h-p-nbe "${sql}"

5. Script-Pass and function-parameter
The beginning of the script is defined: xxx=$1 xxx=$2
The beginning of the function is defined: xxx=$1 xxx=$2
Script execution:./x.sh parameter 1 parameter 2
Function call: fun_name "parameter 1" "Parameter 2"

Annex See below:

Attachment Script 1: Comb the IP list and what table format to match
Description
Script execution: Execute twice
./check_ip.sh ip.txt ' ha_logging '
./check_ip.sh ip.txt ' LOG '
The second parameter of the script: variable parameter for table-type fuzzy query

Cat check_ip.sh
#!/bin/sh
ipfile=$1
tb_like=$2
date_mark= ' date-d ' 1 day ago ' + '%y%m%d '
Fun_check ()
{
ip=$1 #自定义函数传参: IP
tb_like=$2 #自定义函数传参: ha_logging or log
mysql_cmd= "/usr/local/mysql/bin/mysql-uadmin-padmin-h${ip}-p3306" #变量只有IP, ports can also define variables
sql= "Select COUNT (*) from INFORMATION_SCHEMA. TABLES WHERE table_schema= ' logdb ' and table_name
= ' ${tb_like}_${date_mark} '; "
status= ' ${mysql_cmd}-nbe "${sql}" ' #查询数据库表结果赋值给变量
if [${status} = = ' 1 '];then
Echo ${ip}
fi #此处定义 If there is a direct output IP, it does not exist, regardless.
}
Cat ${ipfile}|while Read line
Do
ip= ' echo ${line}|cut-f1-d '
fun_check "${ip}" "${tb_like}"
Done

Attachment Script 3: Querying table data into Excel
Description: For one-time execution, use two IP list files as script parameters
Script execution:./check_loginfo.sh ha_ip.txt Log_ip.txt
Cat check_loginfo.sh
#!/bin/sh
Ip_file1=$1
Ip_file2=$2

# define Date flag: 20161214 (here is yesterday's date)
time_mark= ' date-d ' 1 day ago ' + '%y%m%d '

# defines the name of the table: the name of the table ending with a date <db_name. Tb_name_20161214>
ha_tb= "Logdb.ha_logging_${time_mark}"
lo_tb= "Logdb.log_${time_mark}"

# output to the Excel file for table query results: "Out file
out_file="/ Data/${time_mark}_log.xls "

# define common functions, query data results from table
get_info ()
{
ipfile=$1
tbname=$2

cat $ipfile |while Read line
do
ip= ' echo $line |awk-f ' ' {print '} '
mysql_cmd= "/usr/local/mysql/bin/mysql-uadmin-padmin-h${ip}-P9306"
sql= "Select Servicename,loggername,count (loggername) as Count_num from ${tbname} GROUP by Loggername ORDER by Count_num DESC LIMIT; "
$MYSQL _cmd-nbe "${sql}" >>${out_file}
echo "$IP: OK"
done
}

# Execute Call
#
Get_info "${ip_file1}" "${ha_tb}"
Get_info "${ip_file2}" "${lo_tb}"

End!
　

"Shell"-"batch remote MySQL, execute command"-"summary"