MySQL string to date function

Ref. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y');
        -> '2013-05-01'

MySQL String Replace

Ref. http://stackoverflow.com/questions/1806949/mysql-query-to-replace-spaces-in-a-column-with-underscores

update photos set filename = replace(filename, ' ', '_');

ie, you search for ' ' in the column filename and use '_' instead ; and put the result back intofilename.

Linux Search Disk Format using df

root# df -hT

See also:

• cat /etc/fstab
• cat /etc/mtab

Linux ssh login fail counter using pam_tally2.so

Configuration:

Ref. http://secureos.wordpress.com/2011/07/26/rhel6-ssh-lockouts/
Ref. http://fvue.nl/wiki/Bash:_Piped_%60while-read'_loop_starts_subshell
Choice 1:

1.  Configure /etc/pam.d/sshd ONLY

Add the following line to the auth section:

auth       required    pam_sepermit.so

auth       required     pam_tally2.so even_deny_root (deny=5 unlock_time=60 even_deny_root)

※must be exactly under pam_sepermit.so(only 1 line after)

Choice 2:

2. Configure /etc/pam.d/password-auth ONLY

auth        required      pam_env.so
auth        required      pam_tally2.so even_deny_root

※must be exactly under pam_sepermit.so(only 1 line after)

--------------------------------------------------------

Action:Someone Failed Attemp times reach 3. Do something:

fail_log_watch:

#!/bin/bash
pam_tally2|grep -v Login | while read line
do
        test -z "$line" && exit
        FAIL_ARRAY=($line)
        FAIL_ACCOUNT=${FAIL_ARRAY[0]}
        FAIL_COUNT="${FAIL_ARRAY[1]}"
        FAIL_DATE="${FAIL_ARRAY[2]} ${FAIL_ARRAY[3]}"
        FAIL_IP=${FAIL_ARRAY[4]}
        if (($FAIL_COUNT>=3))
        then
        #==========DO SOMETHING HERE==========
                Message="Account:\"${FAIL_ACCOUNT}\", Failed Login OVER 3 times at ${FAIL_DATE} from ${FAIL_IP}"
                echo ${Message}
        #==========DO SOMETHING HERE==========
        #After DO something reset it
        pam_tally2 -u ${FAIL_ACCOUNT} --reset
        fi
done

------------------------------------------------------------
Useful Reference Usage:
Ref. http://www.unix.com/aix/107866-how-send-alert-email-whenever-failed-login.html

# vi logwatch.sh

LOG=/logs/userauth.log
echo "\n\n" >> ${LOG}
tail -1 -f ${LOG} |
while read LINE
do
case "${LINE}" in
failed)
echo ${LINE} | mailx -s "Failed login" me@mail.com ;;
esac
done

--------------------------------------------------
/etc/pam.d/sshd

#%PAM-1.0

auth       required     pam_tally2.so deny=5 unlock_time=60 even_deny_root

auth       required     pam_sepermit.so

auth       include      password-auth

account    required     pam_nologin.so

account    include      password-auth

password   include      password-auth

# pam_selinux.so close should be the first session rule

session    required     pam_selinux.so close

session    required     pam_loginuid.so

# pam_selinux.so open should only be followed by sessions to be executed in the user context

session    required     pam_selinux.so open env_params

session    optional     pam_keyinit.so force revoke

session    include      password-auth

Linux mail command using sendmail

mail.sh:
-------------------------------------------------------------
while read line
do
        if [ -n "`echo $line | grep \"#SUBJECT#\"`" ]
        then
                echo "Subject: message here" >> mail.txt
        elif [ -n "`echo $line | grep \"#CONTENT#\"`" ]
        then
                echo "content message here" >> mail.txt
        else
                echo $line >> mail.txt
        fi
done < mail_sample
sendmail -t -oi -f "sender@sender.com" < mail.txt
-------------------------------------------------------------

mail_sample:
----------------------------------------

From: sender@sender.com

To: receiver@receiver.com

Cc:  receiver@receiver.com

Content-type: text/html; charset=UTF-8

#SUBJECT#

#CONTENT#

----------------------------------------

MySQL - Math function

round(digit)  --->  <4 drop, >5 add
ceil(digit) ---> all add
floor(digit) ---> all drop

---------------------
Ref. https://dev.mysql.com/doc/refman/5.0/en/mathematical-functions.html

Auto login with interaction - spawn(expect)

Require Package: expect-5.44.1.15-2.el6.x86_64

-----------------------
login.sh:
-----------------------
#!/usr/bin/expect -f
log_file expect.log #log the process
set timeout 3
spawn ssh "root@8.8.8.8"
expect "assword"
send "password\r"
#spawn ls
interact #Do not leave after spawn login

MySQL top query - limit usage

select * from book
limit 2,2;

means start from the third record, and goes two.
same as :

select * from book
limit 2 offset 2;

We can use page size , 2 records/per page

select * from book
limit 2,2;

MySQL Convert varchar to int with function CAST

select cast(`varchar_string`as SIGNED int)
from phone_table

MySQL - Search if there is data duplicated !

SELECT username,count(username)
FROM `member`
group by username
having count(username) >1

Linux Check tcp udp port alive

Check UDP :
nmap -p 694 -sU -P0 8.8.8.8


Check TCP :
telnet www.google.com 80

MySQL - Regular Expressions

 (Ref.

http://www.go4expert.com/forums/showthread.php?t=2337)
-----------------------------------------------------------------------------------------

Introduction

A very interesting and useful capability of MySQL is to incorporate Regular Expressions (regex) in SQL queries. The regular expression support in MySQL is extensive. Let's take a look at using Regular Expressions in queries and the supported metacharacters.

Using Regular Expressions in queries

A simple example of using Regular Expressions in a SQL query would be to select all names from a table that start with 'A'.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^A'

A slight modification in the above example to look for names starting with 'A' or 'D' or 'F' will look like this.

Code: SQL

SELECT name FROM employees WHERE name REGEXP '^(A|D|F)'

If we want to select all names ending with 'P', then the SQL query goes like this

Code: SQL

SELECT name FROM employees WHERE name REGEXP 'P$'

We can use much complex patterns in our SQL queries, but first let's have a look at various MySQL Regular Expression metacharacters.

Regular Expression Metacharacters

*

Matches zero or more instances of the string preceding it

+

Matches one or more instances of the string preceding it

?

Matches zero or one instances of the string preceding it

.

Matches any single character, except a newline

[xyz]

Matches any of x, y, or z (match one of enclosed characters)

[^xyz]

Matches any character not enclosed

[A-Z]

Matches any uppercase letter

[a-z]

Matches any lowercase letter

[0-9]

Matches any digit

^

Anchors the match from the beginning

$

Anchors the match to the end

|

Separates alternatives

{n,m}

String must occur at least n times, but not more than m times

{n}

String must occur exactly n times

{n,}

String must occur at least n times

[[:<:]]

Matches beginning of words

[[:>:]]

Matches ending of words

[:class:]

match a character class i.e.,

[:alpha:] for letters
[:space:] for whitespace
[:punct:] for punctuation
[:upper:] for upper case letters 

Extras

MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).

Whether the Regular Expression match is case sensitive or otherwise is decided by the collation method of the table. If your collation method name ends with ci then the comparison/match is case-insensitive, else if it end in cs then the match is case sensitive.

Examples

Checking only for numbers

Code: SQL

SELECT age FROM employees WHERE age REGEXP '^[0-9]+$'

/* starts, ends and contains numbers */

Contains a specific word, for example the skill PHP in skill sets

Code: SQL

SELECT name FROM employees WHERE skill_sets REGEXP '[[:<:]]php[[:>:]]'

Fetching records where employees have entered their 10-digit mobile number as the contact number.

Code: SQL

SELECT name FROM employees WHERE contact_no REGEXP '^[0-9]{10}$'

Tell the World You Just Read: Regular Expressions in MySQL ...

MySQL ： Insert from another DB with condition

Ref. http://stackoverflow.com/questions/10438464/how-do-i-copy-data-from-one-table-to-another-with-where-clause

DB1.TABLE1: id value_name number

DB2.TABLE2: id name value rid

insert into `DB1`.`TABLE1` (value_name)
SELECT name
FROM `DB2`.`TABLE2`

Super SUBquery

select count(a.book_id)
from books a,
(select book_id,status,min(book_date) book_date
from books_in
where status='out'
group by book_id) b
where a.book_id=b.book_id
and a.place='positionA'
and a.buy_date like '2012-07-%'
and TIMESTAMPDIFF(minute,a.buy_date, b.book_date) >= 1440 and TIMESTAMPDIFF(minute,a.buy_date, b.book_date) < 2880;
-- This is equal to
select count(books.book_id)
from books,
(select book_id,status,min(book_date) book_date
from books_in
where status='out'
group by book_id
) books_in_sub
where books.book_id=books_in_sub.book_id
and books.place='positionA'
and books_in_sub.status = 'out'
and buy_date like '2012-07-%'
and TIMESTAMPDIFF(minute,buy_date,books_in_sub.book_date) >= 1440 and TIMESTAMPDIFF(minute,buy_date,books_in_sub.book_date) < 2880;
---------------------------------------------------------------------
select book_id,status,min(book_date) book_date
from books_in
where status='out'
group by book_id

MySQL LIMIT Usage

select * from table_a
LIMIT 3,5

Result :

3 record3

4 record4

5 record5

6 record6

7 record7

Shows start from record 3

and shows only 5 records

Easy awk usage

The Same Result:

ü   cat /etc/passwd |awk -F ':' '{print $1}'

ü   cat /etc/passwd |cut -d':' -f1

Extra Command:

※ transfer tab to ,

※ cat xxxx | sed s/'\t'/','/g

§ Show only the # column
§ cat xxx | awk '{print $1 $2}'

MySQL time calculate - Super Method

Most Usefull:

TIMESTAMPDIFF(hour,'2008-08-08 00:01:00','2008-08-09 00:00:00')

TIMESTAMPDIFF(minute,'2008-08-08 00:01:00','2008-08-09 00:00:00')

TIMESTAMPDIFF(second,'2008-08-08 00:01:00','2008-08-09 00:00:00')

Result:

23.98333333

1439.0000

86340

Less Usefull:

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60/60

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60

time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))

Result:

The same

------------------------------------------------------------------------------------

※Timediff output: HH:MM:SS

※Time_to_sec Format: time_to_sec(HH:MM:SS)

------------------------------------------------------------------------------------

Ref. http://wen198599.pixnet.net/blog/post/22450019-mysql%E6%97%A5%E6%9C%9F%E5%92%8C%E6%99%82%E9%96%93%E5%87%BD%E6%95%B8%E4%B8%8D%E6%B1%82%E4%BA%BA

Ref. http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

Ref. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_time-to-sec

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

mysql> SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');

        -> 3

mysql> SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');

        -> -1

mysql> SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');

        -> 128885

 TIME_TO_SEC(time)

Returns the time argument, converted to seconds.

mysql> SELECT TIME_TO_SEC('22:23:00');

        -> 80580

mysql> SELECT TIME_TO_SEC('00:39:38');

        -> 2378

 -------------------------------------------------------------------------

select
timediff('2008-08-09 00:00:00','2008-08-08 00:01:00') timediff,
hour(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_hr,
minute(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_minute,
second(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) as timediff_second,
time_to_sec('2008-08-08 00:01:00') To_Sec,
time_to_sec('2008-08-09 00:00:00') To_Sec,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00')) Tdiff_To_Sec,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60 Tdiff_To_Sec_min,
time_to_sec(timediff('2008-08-09 00:00:00','2008-08-08 00:01:00'))/60/60 Tdiff_To_Sec_hr,
TIMESTAMPDIFF(minute,'2008-08-08 00:01:00','2008-08-09 00:00:00') stampdiff_min,
TIMESTAMPDIFF(second,'2008-08-08 00:01:00','2008-08-09 00:00:00') stampdiff_sec

timediff	timediff_hr	timediff_minute	timediff_second	To_Sec	To_Sec	Tdiff_To_Sec	Tdiff_To_Sec_min	Tdiff_To_Sec_hr	stampdiff_min	stampdiff_sec
23:59:00	23	59	0	60	0	86340	1439.0000	23.98333333	1439	86340