Shell: 执行Mysql查询,并将查询结果导出到文件
直接使用Mysql执行查询
mysql> use xxx_dbName;
mysql> select * from log_06 where nat_ip=1234315965 limit 3;
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
| receive_time | host | host_vsys | severity | log_time | src_ip | src_port | nat_ip | nat_port | dst_ip | dst_port | protocol | server_name | user_name | mac | category | rule_id |
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
| 06:00:03 | 8 | NULL | 6 | 2017-03-03 05:42:47 | 1232283696 | 51828 | 1234315965 | 35429 | 1233279515 | 53934 | 49 | NULL | NULL | NULL | 1 | 58 |
| 06:00:57 | 8 | NULL | 6 | 2017-03-03 05:43:41 | 1232307391 | 36441 | 1234315965 | 37272 | 1233238803 | 31747 | 35 | 10.180.45.100 | sjyang | 111122223333 | 1 | 97 |
| 06:03:54 | 8 | NULL | 6 | 2017-03-03 05:46:38 | 1232279540 | 29735 | 1234315965 | 33755 | 1233252778 | 30256 | 14 | NULL | NULL | NULL | 1 | 2 |
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
3 rows in set (0.02 sec)
通过Shell执行Mysql查询,导出结果到文件
文件logQuery.sh
-bash-3.2# cat logQuery.sh
#!/bin/bash
tmpQueryResultFile=/tmp/test2/tmp.result
finalQueryResultFile=/tmp/test2/final.result
dbName=hlog_123456_nat_bin_20170303;
tableName=log_06;
# 判断数据库文件是否存在
if [[ -d "/xxx/$dbName" ]] && [[ -f "/xxx/$dbName/$tableName.frm" ]];then
echo -e "\\033[32m Begin to export log ,please wait for moment ... \\033[0m"
/xxx/bin/mysql -uroot -pxxx << EOF
use $dbName;
select * into outfile "$tmpQueryResultFile" fields terminated by ',' lines terminated by '\\n' from $tableName where nat_ip=1234315965 limit 3;
EOF
cat $tmpQueryResultFile >> $finalQueryResultFile
else
echo -e "Not exit:[ dbName: $dbName, tableName=$tableName]"
fi
echo -e "\\033[32m============= All log query Finish====== \\033[0m"
echo -e "\\033[32m============= Query result file : $finalQueryResultFile ====== \\033[0m"
执行结果:
-bash-3.2# ./logQuery.sh
Begin to export log ,please wait for moment ...
============= All log query Finish======
============= Query result file : /tmp/test2/final.result ======
-bash-3.2#
-bash-3.2# cat final.result
06:00:03,8,,6,2017-03-03 05:42:47,1232283696,51828,1234315965,35429,1233279515,53934,49,,,,1,58
06:00:57,8,,6,2017-03-03 05:43:41,1232307391,36441,1234315965,37272,1233238803,31747,35,"10.180.45.100 ","sjyang ","111122223333 ",1,97
06:03:54,8,,6,2017-03-03 05:46:38,1232279540,29735,1234315965,33755,1233252778,30256,14,,,,1,2
-bash-3.2#
Shell: 创建数据库/数据表,导入数据文件
文件: createTable.sh
-bash-3.2# cat createTable.sh
#!/bin/bash
/system/infobright-4.0.7-x86_64/bin/mysql -uroot -phillstone < createTable.sql
echo "create table and load data finish"
-bash-3.2#
文件:createTable.sql
-bash-3.2# cat createTable.sql
create database if not exists testDb2;
use testDb2;
flush tables;
CREATE TABLE natTable (
`receive_time` time DEFAULT NULL,
`host` int(11) DEFAULT NULL,
`host_vsys` char(32) DEFAULT NULL,
`severity` tinyint(4) DEFAULT NULL,
`log_time` datetime DEFAULT NULL,
`src_ip` bigint(11) DEFAULT NULL,
`src_port` int(11) DEFAULT NULL,
`nat_ip` bigint(11) DEFAULT NULL,
`nat_port` int(11) DEFAULT NULL,
`dst_ip` bigint(11) DEFAULT NULL,
`dst_port` int(11) DEFAULT NULL,
`protocol` int(5) DEFAULT NULL,
`server_name` char(64) DEFAULT NULL,
`user_name` char(64) DEFAULT NULL,
`mac` char(20) DEFAULT NULL,
`category` int(2) DEFAULT NULL,
`rule_id` int(5) DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=utf8;
#
load data infile '/tmp/test2/final.result' into table natTable fields terminated by ',' lines terminated by '\n';
查看结果
mysql> use testDb2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from natTable;
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
| receive_time | host | host_vsys | severity | log_time | src_ip | src_port | nat_ip | nat_port | dst_ip | dst_port | protocol | server_name | user_name | mac | category | rule_id |
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
| 06:00:03 | 8 | NULL | 6 | 2017-03-03 05:42:47 | 1232283696 | 51828 | 1234315965 | 35429 | 1233279515 | 53934 | 49 | NULL | NULL | NULL | 1 | 58 |
| 06:00:57 | 8 | NULL | 6 | 2017-03-03 05:43:41 | 1232307391 | 36441 | 1234315965 | 37272 | 1233238803 | 31747 | 35 | 10.180.45.100 | sjyang | 111122223333 | 1 | 97 |
| 06:03:54 | 8 | NULL | 6 | 2017-03-03 05:46:38 | 1232279540 | 29735 | 1234315965 | 33755 | 1233252778 | 30256 | 14 | NULL | NULL | NULL | 1 | 2 |
+--------------+------+-----------+----------+---------------------+------------+----------+------------+----------+------------+----------+----------+---------------+-----------+--------------+----------+---------+
3 rows in set (0.01 sec)
mysql>
转载请标明出处:http://www.cnblogs.com/ssslinppp/