Analyzing DDoS Attack with Nginx log

In this small post I would like to show a few useful commands to use if someone is experiencing a DDoS attack. In my case, there is an nginx as a front-end server. The access log format looks like this:

log_format main '$remote_addr — $remote_user [$time_local] "$host" "$request" '
'$status $body_bytes_sent "$http_referer" '
'"$http_user_agent" "$http_x_forwarded_for" -> $upstream_response_time';

In the log file we’ll see something like this:

188.142.8.61 — - [14/Sep/2014:22:51:03 +0400] «www.mysite.com» «GET / HTTP/1.1» 200 519 «kiloccnp.com.vn/» «Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.191602; .NET CLR 3.5.191602; .NET CLR 3.0.191602» "-" -> 0.003

Sử dụng log Apache để phân tích DDos

Nguồn : http://pastebin.com/raw/MLHtJ7fQ

[root@kiloccnp~]# cat kilo.txt  | cut -d ' ' -f 9 | sort | uniq -c | sort -nr

    698 404
    691 HTTP/1.1"
    168 HTTP/1.0"
     27 403

[root@kiloccnp~]# grep " 404 " kilo.txt  | cut -d ' ' -f 7 | sort | uniq -c | sort -nr

    674 /
    672 "POST
     23 //
      1 /balancer?&data=

[root@kiloccnp~]# grep " 404 " kilo.txt   | cut -d '"' -f 6 | sort | uniq -c | sort -nr

    136 Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; SV1; .NET CLR 2.0.50727; InfoPath.2)
    132 Mozilla/5.0 (Windows; U; Windows NT 6.1; en; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729)
    131 Mozilla/4.0 (compatible; MSIE 6.1; Windows XP)
    118 Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US) AppleWebKit/532.1 (KHTML, like Gecko) Chrome/4.0.219.6 Safari/532.1
    117 Mozilla/5.0 (Windows; U; MSIE 7.0; Windows NT 6.0; en-US)
    113 Opera/9.80 (Windows NT 5.2; U; ru) Presto/2.5.22 Version/10.51
    109 Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; InfoPath.2)
    106 Mozilla/5.0 (Windows; U; Windows NT 5.2; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729)
    102 Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.3) Gecko/20090913 Firefox/3.5.3
     98 Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.2; Win64; x64; Trident/4.0)
     94 Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; .NET CLR 1.1.4322; .NET CLR 3.5.30729; .NET CLR 3.0.30729)
     92 Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.1.1) Gecko/20090718 Firefox/3.5.1
     21 Mozilla/5.0 (KHTML, like Gecko) Safari/537.36
      1 MyClient/1.0

Install MySQL Enterprise Monitor

1 . Install MySQL Monitor center

[root@Kilo-MySQL-Monitor ~]# unzip V790192-01-MySQL_Enterprise_Monitor_Service_Manager_3.3.1.zip

Archive:  V790192-01-MySQL_Enterprise_Monitor_Service_Manager_3.3.1.zip

 inflating: mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin  

 inflating: mysqlmonitor-3.3.1.1112-linux-x86_64-update-installer.bin  

 inflating: README_en.txt           

 inflating: READ_ME_ja.txt         

[root@Kilo-MySQL-Monitor ~]# chmod +x mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin

[root@Kilo-MySQL-Monitor ~]# ./mysqlmonitor-3.3.1.1112-linux-x86_64-installer.bin --mode text

Language Selection

MySQL Enterprise Backup & Restore - Full and Incrimental

1. Create bash script 

#!/bin/bash

#creates and maintains MySQL Enterprise Backup (MEB) backups

#prints usage
usage()
{
    echo "$VERSION"
    echo "
Usage: `basename $0` [command] [MEB options]

Commands:
    full                                make full backup
    incremental                         make incremental backup
    incremental-with-redo-log-only      make incremental backup with redo log only
    verify-to-tape                      verify backup images, then copy to tape
    prepare                             prepare backups
    remove-old                          remove old backups
    "
}

Full Backup using MySQL Enterprise Backup

Install the software

Before we can configure backups and the like, you’ll need to install the MySQL Enterprise Backup software:

$ tar xvzf meb-3.9.0-linux2.6-x86-64bit.tar.gz 
meb-3.9.0-linux2.6-x86-64bit/
meb-3.9.0-linux2.6-x86-64bit/bin/
meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup
meb-3.9.0-linux2.6-x86-64bit/README.txt
meb-3.9.0-linux2.6-x86-64bit/LICENSE.mysql
meb-3.9.0-linux2.6-x86-64bit/manual.html
meb-3.9.0-linux2.6-x86-64bit/mysql-html.css

I then placed the mysqlbackup binary in my MySQL “bin” directory :

$ cp meb-3.9.0-linux2.6-x86-64bit/bin/mysqlbackup /usr/local/mysql/bin/
$ which mysqlbackup 
/usr/local/mysql/bin/mysqlbackup

Now we’ve installed the software, we can go on and prepare our database for backup.

How to Back Up MySQL Server using MySQL Enterprise Backup (MEB)

 In order to be able to use MySQL Enterprise Backup to perform a backup of a MySQL Server instance, the following MySQL options must be specified in the configuration file of the server instance, under the [mysqld] section (substitute ... with any valid values):

datadir=...
innodb_data_home_dir=...
innodb_data_file_path=...
innodb_log_group_home_dir=...
innodb_log_files_in_group=...
innodb_log_file_size=...

Alternately you can use a new configuration file, that contains just the options above, and pass it to MySQL Enterprise Backup.

My.cnf examples

[root@kiloccnp ~]# cat /etc/my.cnf
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.

[mysqld]
datadir=/usr/local/mysql/data
#tmpdir=/var/log/mysqld/
tmpdir=/db/mytmp
log-error=/var/log/mysqld/mysqld.err
# as of MySQL 5.1.29, log-slow-queries is deprecated, use the 2 options below
#log-slow-queries=/var/log/mysqld/mysqld-slow.log
slow-query-log=1
slow-query-log-file=/var/log/mysqld/mysqld-slow.log
performance_schema_consumer_events_statements_history_long = ON