Difference between revisions of "Centos Oneliners"

From Brian Nelson Ramblings
Jump to: navigation, search
(→‎Qmail Oneliners)
(→‎Wordpress Mysql Hacks)
 
(35 intermediate revisions by the same user not shown)
Line 6: Line 6:
  
 
  for x in $(ps aux | grep pts| awk '{print $2}'); do kill $x; done
 
  for x in $(ps aux | grep pts| awk '{print $2}'); do kill $x; done
 +
Strace all php-fpm process by a single user for debugging
 +
echo; read -p "User Name: " _user_ ;strace -ffTttvs20000 -o ~/strace.$_user_.log $(ps aux | grep php-fpm | grep $_user_ | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')
 +
Strace for apache
 +
strace -ffTttvs20000 -o ~/strace.apache.log $(ps aux | grep apache |grep -v grep | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')
 +
What process is using up the most memory
 +
ps aux | awk '{if ($5 != 0 ) print $2,$5,$6,$11}' | sort -k2n
 +
Find the top files in MB for the Current GROUP user
 +
find $PWD -type f -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \;  | sort -rn | uniq | head
 +
Find the larges directories in MB for the current GROUP user
 +
find $PWD -type d -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \;  | sort -rn | uniq | head
  
 
===Apache Oneliners===
 
===Apache Oneliners===
 +
Bandwidth per IP/Bot
 +
grep -i google transfer.log | awk '{ sum+=$10} END {size= sum / 1048576; printf "total size %.2f MB\n", size}'
 +
 +
Max_Clients - Check which account is getting the most traffic
 +
for z in $(find /home/*/var/*/logs/transfer.log); do echo $z; echo '----------'; for x in $(seq -w 0 24); do echo -n "$x  "; grep -c "$(date +%d/%b/%Y:)$x" $z; done;done;
 +
 
Check the Number of IP's Connecting over Port 80
 
Check the Number of IP's Connecting over Port 80
−
  netstat -tn | grep :80 | awk '{print $5}'| cut -d: -f1 | uniq -c | sort -rn | head
+
  netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f1 | sort | uniq -c | sort -rn | head
 +
 
 +
netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f4 | sort | uniq -c | sort -rn | head
  
 
Check what IP's are getting the most traffic
 
Check what IP's are getting the most traffic
−
  netstat -tn | grep EST | grep :80 | awk '{print $4}' |cut -d: -f1 | uniq -c | sort -rn
+
  netstat -tn | grep EST | grep :80 | awk '{print $4}' |cut -d: -f1 | sort |uniq -c | sort -rn | head
 +
 
 +
netstat -tn |grep EST| grep :80 | awk '{print $4}'| cut -d: -f4 | sort | uniq -c | sort -rn | head
 +
 
 +
size of logs
 +
ls -lahrS /home/*/var/*/logs/*log
 +
 
 +
what are the php-cgi processes doing? (sleeps for 5 seconds, command takes a while to run, will open up less when it's done)
 +
sleep 5; for i in $(ps aux |grep php-cgi |grep -v defunct |grep -v grep |awk '{print $2}'); do strace -p $i -o $i.trace ; done ; cat *.trace | less &&  rm -rf *.trace
 +
 
 +
top 20 URLs from the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
 +
 
 +
top 20 URLS excluding POST data from the last 5000 hits
 +
tail -5000 ./transfer.log | awk -F"[ ?]" '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
 +
 
 +
top 20 IPs from the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{freq[$1]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
 +
 
 +
top 20 URLs requested from a certain ip from the last 5000 hits
 +
IP=1.2.3.4; tail -5000 ./transfer.log | awk -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
 +
 
 +
top 20 URLS requested from a certain ip excluding, excluding POST data, from the last 5000 hits
 +
IP=1.2.3.4; tail -5000 ./transfer.log | awk -F"[ ?]" -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20
 +
 
 +
top 20 referrers from the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{freq[$11]++} END {for (x in freq) {print freq[x], x}}' | tr -d '"' | sort -rn | head -20
 +
 
 +
top 20 user agents from the last 5000 hits
 +
tail -5000 ./transfer.log | cut -d\  -f12- | sort | uniq -c | sort -rn | head -20
 +
 
 +
sum of data (in MB) transferred in the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{sum+=$10} END {print sum/1048576}'
 +
 
 +
IPs using the most bandwidth (in MB) from the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{tx[$1]+=$10} END {for (x in tx) {print x, "\t", tx[x]/1048576, "M"}}' | sort -k 2n | tail -n 20 | tac
 +
 
 +
Which website Asset is using the most bandwidth (in MB) from the last 5000 hits
 +
tail -5000 ./transfer.log | awk '{tx[$7]+=$10} END {for (x in tx) {print x, "\t", tx[x]/1048576, "M"}}' | sort -k 2n | tail -20 | tac
 +
 
 +
Check which pages are using an Asset
 +
cat transfer.log | grep 'webimiage.jpg' | awk '{print $11}' | sort | uniq -c | sort -rn
 +
 
 +
 
 +
hits per hour
 +
for x in $(seq -w 0 23); do echo -n "$x  "; grep -c "$(date +%d/%b/%Y:)$x" ./transfer.log; done;
  
 
===Mysql Oneliners===
 
===Mysql Oneliners===
Line 18: Line 81:
 
Show locked tables
 
Show locked tables
 
  show open tables WHERE In_use > 0;
 
  show open tables WHERE In_use > 0;
 +
Check what size the Innodb Buffer Pool should be
 +
SELECT CONCAT(ROUND(KBS/POWER(1024,  IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), 
 +
SUBSTR(' KMG',IF(PowerOf1024<0,0,  IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size  FROM (SELECT SUM(data_length+index_length) KBS
 +
FROM information_schema.tables  WHERE engine='InnoDB') A,  (SELECT 3 PowerOf1024) B;
 +
Show all Definers
 +
show procedure status;
 +
Change Procedure Definer
 +
UPDATE `mysql`.`proc` p SET definer = 'use1r@%' WHERE definer='user2t@%'
 +
Check uptime
 +
mysql> \s;
 +
Adjust innodb_lock_wait_timeout, might need a restart but most systems are not read only
 +
m -e'set global innodb_lock_wait_timeout = 150;' && m -e'set session innodb_lock_wait_timeout = 150;'
 +
 +
* note I have setup an alias for m to connect to mysql in my bashrc file
 +
alias m='mysql -u <user> -p<longuglypasswod>'
 +
 +
Check database size for all databases
 +
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP  BY table_schema;
 +
Check Free space in the Database -  Free space in the tablespace not allocated to any segment
 +
SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 /1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLES GROUP BY table_schema ;
 +
Check when the databases where last updated
 +
use information_schema; SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA FROM TABLES GROUP BY TABLE_SCHEMA ORDER BY 1, 2;
  
 
===Qmail Oneliners===
 
===Qmail Oneliners===
 +
Check what domains are getting the most mail sent to them
 +
grep 'to remote' /var/log/send/* | awk '{print $9}'| tr [:upper:] [:lower:] |cut -d@ -f2|sort | uniq -c | sort -rn | head -20
 +
 +
Check what domain are getting the most unique mail sent to them
 +
grep 'to remote' /var/log/send/* | awk '{print $9}'| tr [:upper:] [:lower:] | sort | uniq |cut -d@ -f2|sort | uniq -c | sort -rn | head -20
  
 
who are the top senders for the outgoing (remote) queue
 
who are the top senders for the outgoing (remote) queue
Line 66: Line 156:
 
  awk '/tcpserver: status/ {print $4}' /var/log/imap4/* | sort | uniq -c | sort -k2 -n
 
  awk '/tcpserver: status/ {print $4}' /var/log/imap4/* | sort | uniq -c | sort -k2 -n
 
  awk '/tcpserver: status/ {print $4}' /var/log/pop3/* | sort | uniq -c | sort -k2 -n
 
  awk '/tcpserver: status/ {print $4}' /var/log/pop3/* | sort | uniq -c | sort -k2 -n
 +
 +
===Wordpress Mysql Hacks===
 +
 +
Delete all unapproved comments
 +
DELETE from wp_comments WHERE comment_approved = '0';
 +
 +
Delete all revisions
 +
DELETE FROM wp_posts WHERE post_type = "revision";
 +
 +
Update admin users password
 +
UPDATE `wp_users` SET `user_pass` = MD5('PASSWORD') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;
 +
 +
Change Wordpress Url
 +
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
 +
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');
 +
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
 +
 +
Check what Crons are setup in Wordpress
 +
SELECT * FROM `wp_options` WHERE `option_name` LIKE '%cron%';
 +
 +
Disable Comment Notifications Spam
 +
UPDATE wp_options SET option_value="" WHERE option_name LIKE "%notify";
 +
 +
Verify Disabled
 +
SELECT * FROM wp_options WHERE option_name LIKE '%notify%';
 +
 +
How to remove spam user that do not input a username
 +
 +
DELETE u FROM wp_users u
 +
  LEFT JOIN wp_usermeta um1 ON u.id = um1.user_id AND um1.meta_key = 'first_name'
 +
  LEFT JOIN wp_usermeta um2 ON u.id = um2.user_id AND um2.meta_key = 'last_name'
 +
WHERE (um1.meta_value IS NULL OR CHAR_LENGTH(um1.meta_value) = 0)
 +
  AND (um2.meta_value IS NULL OR CHAR_LENGTH(um2.meta_value) = 0);
 +
 +
DELETE um FROM wp_usermeta um
 +
  LEFT JOIN wp_users u ON u.id = um.user_id
 +
WHERE u.id IS NULL;

Latest revision as of 22:26, 30 May 2019

Centos Oneliners

This will be a place to save my oneliners

Centos Oneliners

kill all pts 

for x in $(ps aux | grep pts| awk '{print $2}'); do kill $x; done

Strace all php-fpm process by a single user for debugging 

echo; read -p "User Name: " _user_ ;strace -ffTttvs20000 -o ~/strace.$_user_.log $(ps aux | grep php-fpm | grep $_user_ | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')

Strace for apache 

strace -ffTttvs20000 -o ~/strace.apache.log $(ps aux | grep apache |grep -v grep | awk '{print $2}'| sed 's/\([0-9]*\)/\-p \1/g')

What process is using up the most memory 

ps aux | awk '{if ($5 != 0 ) print $2,$5,$6,$11}' | sort -k2n

Find the top files in MB for the Current GROUP user 

find $PWD -type f -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \;  | sort -rn | uniq | head

Find the larges directories in MB for the current GROUP user 

find $PWD -type d -group $(stat -c "%G" $PWD) -exec du --block-size=MB {} \;  | sort -rn | uniq | head

Apache Oneliners

Bandwidth per IP/Bot 

grep -i google transfer.log | awk '{ sum+=$10} END {size= sum / 1048576; printf "total size %.2f MB\n", size}'

Max_Clients - Check which account is getting the most traffic 

for z in $(find /home/*/var/*/logs/transfer.log); do echo $z; echo '----------'; for x in $(seq -w 0 24); do echo -n "$x  "; grep -c "$(date +%d/%b/%Y:)$x" $z; done;done;

Check the Number of IP's Connecting over Port 80 

netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f1 | sort | uniq -c | sort -rn | head

netstat -tn |grep EST| grep :80 | awk '{print $5}'| cut -d: -f4 | sort | uniq -c | sort -rn | head

Check what IP's are getting the most traffic 

netstat -tn | grep EST | grep :80 | awk '{print $4}' |cut -d: -f1 | sort |uniq -c | sort -rn | head

netstat -tn |grep EST| grep :80 | awk '{print $4}'| cut -d: -f4 | sort | uniq -c | sort -rn | head

size of logs 

ls -lahrS /home/*/var/*/logs/*log

what are the php-cgi processes doing? (sleeps for 5 seconds, command takes a while to run, will open up less when it's done) 

sleep 5; for i in $(ps aux |grep php-cgi |grep -v defunct |grep -v grep |awk '{print $2}'); do strace -p $i -o $i.trace ; done ; cat *.trace | less &&  rm -rf *.trace

top 20 URLs from the last 5000 hits 

tail -5000 ./transfer.log | awk '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20

top 20 URLS excluding POST data from the last 5000 hits 

tail -5000 ./transfer.log | awk -F"[ ?]" '{freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20

top 20 IPs from the last 5000 hits 

tail -5000 ./transfer.log | awk '{freq[$1]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20

top 20 URLs requested from a certain ip from the last 5000 hits 

IP=1.2.3.4; tail -5000 ./transfer.log | awk -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20

top 20 URLS requested from a certain ip excluding, excluding POST data, from the last 5000 hits 

IP=1.2.3.4; tail -5000 ./transfer.log | awk -F"[ ?]" -v ip=$IP ' $1 ~ ip {freq[$7]++} END {for (x in freq) {print freq[x], x}}' | sort -rn | head -20

top 20 referrers from the last 5000 hits 

tail -5000 ./transfer.log | awk '{freq[$11]++} END {for (x in freq) {print freq[x], x}}' | tr -d '"' | sort -rn | head -20

top 20 user agents from the last 5000 hits 

tail -5000 ./transfer.log | cut -d\  -f12- | sort | uniq -c | sort -rn | head -20

sum of data (in MB) transferred in the last 5000 hits 

tail -5000 ./transfer.log | awk '{sum+=$10} END {print sum/1048576}'

IPs using the most bandwidth (in MB) from the last 5000 hits 

tail -5000 ./transfer.log | awk '{tx[$1]+=$10} END {for (x in tx) {print x, "\t", tx[x]/1048576, "M"}}' | sort -k 2n | tail -n 20 | tac

Which website Asset is using the most bandwidth (in MB) from the last 5000 hits 

tail -5000 ./transfer.log | awk '{tx[$7]+=$10} END {for (x in tx) {print x, "\t", tx[x]/1048576, "M"}}' | sort -k 2n | tail -20 | tac

Check which pages are using an Asset 

cat transfer.log | grep 'webimiage.jpg' | awk '{print $11}' | sort | uniq -c | sort -rn


hits per hour 

for x in $(seq -w 0 23); do echo -n "$x  "; grep -c "$(date +%d/%b/%Y:)$x" ./transfer.log; done;

Mysql Oneliners

Show locked tables 

show open tables WHERE In_use > 0;

Check what size the Innodb Buffer Pool should be 

SELECT CONCAT(ROUND(KBS/POWER(1024,  IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),  
SUBSTR(' KMG',IF(PowerOf1024<0,0,  IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size  FROM (SELECT SUM(data_length+index_length) KBS 
FROM information_schema.tables  WHERE engine='InnoDB') A,  (SELECT 3 PowerOf1024) B;

Show all Definers 

show procedure status;

Change Procedure Definer 

UPDATE `mysql`.`proc` p SET definer = 'use1r@%' WHERE definer='user2t@%'

Check uptime 

mysql> \s;

Adjust innodb_lock_wait_timeout, might need a restart but most systems are not read only 

m -e'set global innodb_lock_wait_timeout = 150;' && m -e'set session innodb_lock_wait_timeout = 150;'
  • note I have setup an alias for m to connect to mysql in my bashrc file
alias m='mysql -u <user> -p<longuglypasswod>'

Check database size for all databases 

SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP  BY table_schema;

Check Free space in the Database - Free space in the tablespace not allocated to any segment 

SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 /1024 "Data Base Size in MB",sum( data_free )/ 1024 / 1024 "Free Space in MB"FROM information_schema.TABLES GROUP BY table_schema ;

Check when the databases where last updated 

use information_schema; SELECT MAX(UPDATE_TIME), MAX(CREATE_TIME), TABLE_SCHEMA FROM TABLES GROUP BY TABLE_SCHEMA ORDER BY 1, 2;

Qmail Oneliners

Check what domains are getting the most mail sent to them 

grep 'to remote' /var/log/send/* | awk '{print $9}'| tr [:upper:] [:lower:] |cut -d@ -f2|sort | uniq -c | sort -rn | head -20

Check what domain are getting the most unique mail sent to them 

grep 'to remote' /var/log/send/* | awk '{print $9}'| tr [:upper:] [:lower:] | sort | uniq |cut -d@ -f2|sort | uniq -c | sort -rn | head -20

who are the top senders for the outgoing (remote) queue 

/var/qmail/bin/qmqtool -R | awk '/  From:/ {h[$0]++} END {for (x in h) {print h[x], x}}' | sort -rn | head -20

who are the top recipients of the outgoing (remote) queue 

/var/qmail/bin/qmqtool -R | awk '/  To:/ {h[$0]++} END {for (x in h) {print h[x], x}}' | sort -rn | head -20

what are the top subjects of the outgoing (remote) queue 

/var/qmail/bin/qmqtool -R | awk '/  Subject:/ {h[$0]++} END {for (x in h) {print h[x], x}}' | sort -rn | head -20

what domains are have the most mail in the outgoing (remote) queue 

/var/qmail/bin/qmqtool -R | grep "To: " | cut -d @ -f2 | sort  | uniq -c | sort -rn  | head -20

who are the top receivers for the incoming (local) queue 

/var/qmail/bin/qmqtool -L | grep "To: " | sort  | uniq -c | sort -rn | head -20

what domains are have the most mail in the incoming (local) queue 

/var/qmail/bin/qmqtool -L | grep "To: " | cut -d @ -f2 | sort  | uniq -c | sort -rn  | head -20

how many messages are queued up in the incoming (local) queue for a specific domain 

DOMAIN="masdox.com"; /var/qmail/bin/qmqtool -L | grep "To: " | grep $DOMAIN |sort  | wc -l

view the messages in the incoming (local) queue for a specific domain 

DOMAIN="masdox.com"; /var/qmail/bin/qmqtool -L | grep "To: " | grep $DOMAIN |sort  | uniq -c | sort -rn | head -20

list all domains with catch-alls 

for x in /home/*/var/*/mail/catchall; do echo "$(echo $x | cut -d/ -f5) - $(cat $x)"; done;
for x in /home/*/var/*/mail/.catchall; do echo "$(echo $x | cut -d/ -f5) - $(cat $x)"; done;

all domains with bounce turned off (and don't have a catchall which requires bounce to be turned off) 

(for x in /home/*/var/*/mail; do if [ ! -e $x/.bounceon -a ! -e $x/.catchall ]; then echo "$x"; fi; done;) | cut -d/ -f5 | sort

which domains with bounce off or catchalls are getting the most messages 

grep 'accepted any recipient' /var/log/smtp/* | awk '{print $11}' | sed 's/[<>]//g' | cut -d@ -f2 | sort | uniq -c | sort -rn | head -20

list everyone with a vacation message 

find /home/*/var/*/mail/*/vacation -type f ! -size 0

print out every vacation message 

for x in $(find /home/*/var/*/mail/*/vacation -type f ! -size 0); do echo $x; cat $x; echo -e "\n\n-------------------"; done;

how man imap/pop3 connections are usually open. also look for it approaching the limit 

awk '/tcpserver: status/ {print $4}' /var/log/imap4-ssl/* | sort | uniq -c | sort -k2 -n
awk '/tcpserver: status/ {print $4}' /var/log/pop3-ssl/* | sort | uniq -c | sort -k2 -n
awk '/tcpserver: status/ {print $4}' /var/log/imap4/* | sort | uniq -c | sort -k2 -n
awk '/tcpserver: status/ {print $4}' /var/log/pop3/* | sort | uniq -c | sort -k2 -n

Wordpress Mysql Hacks

Delete all unapproved comments 

DELETE from wp_comments WHERE comment_approved = '0';

Delete all revisions 

DELETE FROM wp_posts WHERE post_type = "revision";

Update admin users password 

UPDATE `wp_users` SET `user_pass` = MD5('PASSWORD') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;

Change Wordpress Url 

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');

Check what Crons are setup in Wordpress 

SELECT * FROM `wp_options` WHERE `option_name` LIKE '%cron%';

Disable Comment Notifications Spam 

UPDATE wp_options SET option_value="" WHERE option_name LIKE "%notify";

Verify Disabled 

SELECT * FROM wp_options WHERE option_name LIKE '%notify%';

How to remove spam user that do not input a username 

DELETE u FROM wp_users u
 LEFT JOIN wp_usermeta um1 ON u.id = um1.user_id AND um1.meta_key = 'first_name'
 LEFT JOIN wp_usermeta um2 ON u.id = um2.user_id AND um2.meta_key = 'last_name'
WHERE (um1.meta_value IS NULL OR CHAR_LENGTH(um1.meta_value) = 0)
 AND (um2.meta_value IS NULL OR CHAR_LENGTH(um2.meta_value) = 0);

DELETE um FROM wp_usermeta um
 LEFT JOIN wp_users u ON u.id = um.user_id 
WHERE u.id IS NULL;
Retrieved from "https://briansnelson.com/index.php?title=Centos_Oneliners&oldid=975"