CREATE TABLE wpdb.wp_posts_deduped like wpdb.wp_posts;
INSERT wpdb.wp_posts_deduped SELECT * FROM wpdb.wp_posts GROUP BY post_content, post_title;
RENAME TABLE wpdb.wp_posts TO wpdb.wp_posts_with_dupes;
RENAME TABLE wpdb.wp_posts_deduped TO wpdb.wp_posts;
#OPTIONAL
ALTER TABLE wpdb.wp_posts ADD UNIQUE `unique_index` (`post_content`, `post_title`);
#OPTIONAL
DROP TABLE wpdb.wp_posts_with_dupes;
Saturday, 26 April 2014
Find mysql database and tabe size
These queries are very useful to find bloated databases/tables:
Show size of all databases (in MB)
SELECT s.schema_name,
SUM(t.data_length + t.index_length)/1024/1024 total_size,
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used, SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC;
Show size of all tables (in MB)
SELECT s.schema_name, table_name,
SUM(t.data_length + t.index_length)/1024/1024 total_size,
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used,
SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
ORDER BY total_size DESC;
Source
Show size of all databases (in MB)
SELECT s.schema_name,
SUM(t.data_length + t.index_length)/1024/1024 total_size,
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used, SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.schema_name = t.table_schema
GROUP BY s.schema_name
ORDER BY total_size DESC;
Show size of all tables (in MB)
SELECT s.schema_name, table_name,
SUM(t.data_length + t.index_length)/1024/1024 total_size,
(SUM(t.data_length + t.index_length)-SUM(t.data_free))/1024/1024 data_used,
SUM(data_free)/1024/1024 data_free
FROM INFORMATION_SCHEMA.SCHEMATA s INNER JOIN INFORMATION_SCHEMA.TABLES t
ON s.schema_name = t.table_schema
GROUP BY s.schema_name, table_name
ORDER BY total_size DESC;
Source
Sunday, 16 February 2014
Burn srt to avi in Linux
To split files use avisplit like:avisplit -s 300 -i video.avi Then to burn subtitle to the video.avi:
mencoder in.mp4 -sub mysub.srt -subcp utf8 -subfont-text-scale 2 -sub-bg-color 0 -sub-bg-alpha 100 -subfont-outline 1 -ovc xvid -oac mp3lame -xvidencopts pass=1 -o out.mp4
Note: You need to either import a proper ttf file to your ~/.mplayer OR symlink the folder to a ttf file in /usr/share/fonts. (Doc)
Thursday, 6 February 2014
Install tahoma fonts on Ubuntu
wget -c http://hezardastan.sourceforge.net/persianfonts/tahoma.tar.gz
wget -c http://hezardastan.sourceforge.net/persianfonts/bfonts.tar.gz
sudo mkdir /usr/share/fonts/truetype/ttf-persian-fonts
sudo tar zxvf tahoma.tar.gz -C /usr/share/fonts/truetype/ttf-persian-fonts
sudo tar zxvf bfonts.tar.gz -C /usr/share/fonts/truetype/ttf-persian-fonts
sudo fc-cache -f -vTuesday, 4 February 2014
How to counter botnets?
First, what are the indications that you are under attack by botnets?
Usually a very high load on web server without much spike in bandwidth usage or high load on database is a good indication that botnest are involved.
Also, if you use software firewalls like CSF (You should!) you will see on top command that ldf process eats up a lot of your cpu time. This means a lot of requests are coming through and the firewall needs to deal with them, hence its high cpu usage.
To make sure check your web server access log. Botnets can be recognized by the fact that they send the most number of request usually to get the the website's root. So you see many requests like:
GET / HTTP/1.1" 200 7288 "http://yourdomain.com/" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.2.3.4) Gecko/20090922 Firefox/3.5.2 (.NET CLR 3.4.5678)"
in your access.log
How to counter botnest?
Don't worry! Botnets are very stupid as they all use the same user agent (at least the one that I've encountered). So it is easy to tame them if the botnet is not very large.
Once you could pinpoint the botnet 'user agent', it is dead simple to counter them: Just add a rule to nginx to deny any request from that particular user agent:
if ($http_user_agent = "200 7288 "http://yourdomain.com/" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.2.3.4) Gecko/20090922 Firefox/3.5.2 (.NET CLR 3.4.5678)") {
return 444;
}
That's it. Now the 444 means that nginx returns no information to the client and closes the connection so the botnet effort to bombard your webserver are simply ignored.
However sometimes botnets are so vast that even the above trick does not suffice, as the botnet consumes all the worker connections of nginx. In this case you need to pinpoint the attacking IPs and block them before they reach nginx.
These are scripts that I've written for this purpose:
findbadips.py
#!/usr/bin/env python
import os
from os import system
distilips.py
After adding to bad ips into csf.deny, and reloading the deny list 'deny -r' you should see that the server load quickly drops to normal.
In case you use cloudflare (you should!) you want to block all the bad IPs at cloulflare level instead of csf.
Thanks to the Cloudflare API, you can do this at one shot:
You may not see but you can be sure that your adversaries are now pitiful of the money that they spared on petite botnet Sheppard.
Usually a very high load on web server without much spike in bandwidth usage or high load on database is a good indication that botnest are involved.
Also, if you use software firewalls like CSF (You should!) you will see on top command that ldf process eats up a lot of your cpu time. This means a lot of requests are coming through and the firewall needs to deal with them, hence its high cpu usage.
To make sure check your web server access log. Botnets can be recognized by the fact that they send the most number of request usually to get the the website's root. So you see many requests like:
GET / HTTP/1.1" 200 7288 "http://yourdomain.com/" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.2.3.4) Gecko/20090922 Firefox/3.5.2 (.NET CLR 3.4.5678)"
in your access.log
How to counter botnest?
Don't worry! Botnets are very stupid as they all use the same user agent (at least the one that I've encountered). So it is easy to tame them if the botnet is not very large.
Once you could pinpoint the botnet 'user agent', it is dead simple to counter them: Just add a rule to nginx to deny any request from that particular user agent:
if ($http_user_agent = "200 7288 "http://yourdomain.com/" "Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.2.3.4) Gecko/20090922 Firefox/3.5.2 (.NET CLR 3.4.5678)") {
return 444;
}
And of course start nginx.
However sometimes botnets are so vast that even the above trick does not suffice, as the botnet consumes all the worker connections of nginx. In this case you need to pinpoint the attacking IPs and block them before they reach nginx.
These are scripts that I've written for this purpose:
findbadips.py
#!/usr/bin/env python
import os
from os import system
os.system("netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort | uniq -c | sort -n > data.txt")
#trusted IPs
goodips=['1.2.3.4, '127.0.0.1']
#IPs already blocked in csf.deny
csfdenyips=['1.3.4.2', '5.4.3.2]
#http 444 that should flag bad IPs
flagstrings=['444']
with open('access.log', "r") as f,open('badips.txt', "w") as f2:
for l in f:
if (not any(ip in l for ip in goodips) and not any(ip in l for ip in csfdenyips) and any(ip in l for ip in flagstrings)):
f2.write(l.strip()+'\n')
Then you need to distill the badips.txt to remove repetitions.
distilips.py
#!/usr/bin/env python
import os
from os import system
import re
import time
#Use awk to distil access log to $IP $TIME and $URL columns
bashcom = "awk '{print $1}' <badips.txt > iprequests.txt"
os.system(bashcom)
#just in case any good ip has sneaked into the list
goodips=['1.2.3.4','127.0.0.1']
distips = []
with open('iprequests.txt', "r") as src,open('distiled-badips.txt', "w") as dest:
for l in src:
if (not any(ip in l for ip in goodips)):
if l not in distips:
distips.append(str(l))
for ip in distips:
dest.write(ip)
After adding to bad ips into csf.deny, and reloading the deny list 'deny -r' you should see that the server load quickly drops to normal.
In case you use cloudflare (you should!) you want to block all the bad IPs at cloulflare level instead of csf.
Thanks to the Cloudflare API, you can do this at one shot:
#!/bin/bash
badIPArray=( 1.2.3.4 4.2.4.2 )
for i in "${badIPArray[@]}"
do
curl -s https://www.cloudflare.com/api_json.html -d 'a=ban' -d 'tkn=YourAPIKey' -d 'email=you@example.com' -d 'key=$i';
echo "posted - $i";
done
You may not see but you can be sure that your adversaries are now pitiful of the money that they spared on petite botnet Sheppard.
Friday, 24 January 2014
mezzanine notes
Error loading MySQLdb module: No module named MySQLdb
On Ubuntu 12.04, to be able to use mysql as backend database in mezzanine, do these Before creating virtualenv do install:
apt-get install libmysqlclient-dev
apt-get install python-dev
pip install mysql-python
Then on vritualenv :
pip install MySQL-python
-----------------------------
decoder jpeg not available
On Ubuntu 12.04 64 bit:
On Ubuntu 12.04, to be able to use mysql as backend database in mezzanine, do these Before creating virtualenv do install:
apt-get install libmysqlclient-dev
apt-get install python-dev
pip install mysql-python
Then on vritualenv :
pip install MySQL-python
-----------------------------
decoder jpeg not available
On Ubuntu 12.04 64 bit:
sudo ln -s /usr/lib/x86_64-linux-gnu/libjpeg.so /usr/lib
sudo ln -s /usr/lib/x86_64-linux-gnu/libfreetype.so /usr/lib
sudo ln -s /usr/lib/x86_64-linux-gnu/libz.so /usr/libpip install Wednesday, 22 January 2014
Fix drupal 6 file permissioins
If you need to fix permissions repeatedly then the following script will help you, it is based on the guidelines given above and performs some checks before any modification to ensure it is not applied on files/directories outside your drupal installation.
#!/bin/bash
if [ $(id -u) != 0 ]; then
printf "This script must be run as root.\n"
exit 1
fi
drupal_path=${1%/}
drupal_user=${2}
httpd_group="${3:-www-data}"
# Help menu
print_help() {
cat <<-HELP
This script is used to fix permissions of a Drupal installation
you need to provide the following arguments:
1) Path to your Drupal installation.
2) Username of the user that you want to give files/directories ownership.
3) HTTPD group name (defaults to www-data for Apache).
Usage: (sudo) bash ${0##*/} --drupal_path=PATH --drupal_user=USER --httpd_group=GROUP
Example: (sudo) bash ${0##*/} --drupal_path=/usr/local/apache2/htdocs --drupal_user=john --httpd_group=www-data
HELP
exit 0
}
# Parse Command Line Arguments
while [ $# -gt 0 ]; do
case "$1" in
--drupal_path=*)
drupal_path="${1#*=}"
;;
--drupal_user=*)
drupal_user="${1#*=}"
;;
--httpd_group=*)
httpd_group="${1#*=}"
;;
--help) print_help;;
*)
printf "Invalid argument, run --help for valid arguments.\n";
exit 1
esac
shift
done
if [ -z "${drupal_path}" ] || [ ! -d "${drupal_path}/sites" ] || [ ! -f "${drupal_path}/core/modules/system/system.module" ] && [ ! -f "${drupal_path}/modules/system/system.module" ]; then
printf "Please provide a valid Drupal path.\n"
print_help
exit 1
fi
if [ -z "${drupal_user}" ] || [ $(id -un ${drupal_user} 2> /dev/null) != "${drupal_user}" ]; then
printf "Please provide a valid user.\n"
print_help
exit 1
fi
cd $drupal_path
printf "Changing ownership of all contents of "${drupal_path}":\n user => "${drupal_user}" \t group => "${httpd_group}"\n"
chown -R ${drupal_user}:${httpd_group} .
printf "Changing permissions of all directories inside "${drupal_path}" to "rwxr-x---"...\n"
find . -type d -exec chmod u=rwx,g=rx,o= '{}' \;
printf "Changing permissions of all files inside "${drupal_path}" to "rw-r-----"...\n"
find . -type f -exec chmod u=rw,g=r,o= '{}' \;
printf "Changing permissions of "files" directories in "${drupal_path}/sites" to "rwxrwx---"...\n"
cd sites
find . -type d -name files -exec chmod ug=rwx,o= '{}' \;
printf "Changing permissions of all files inside all "files" directories in "${drupal_path}/sites" to "rw-rw----"...\n"
printf "Changing permissions of all directories inside all "files" directories in "${drupal_path}/sites" to "rwxrwx---"...\n"
for x in ./*/files; do
find ${x} -type d -exec chmod ug=rwx,o= '{}' \;
find ${x} -type f -exec chmod ug=rw,o= '{}' \;
done
echo "Done settings proper permissions on files and directories"
Copy the code above to a file, name it "fix-permissions.sh" and run it as follows:
sudo bash fix-permissions.sh --drupal_path=your/drupal/path --drupal_user=your_user_name
Note: The server group name is assumed "www-data", if it differs use the --httpd_group=GROUP argument.
Tuesday, 21 January 2014
Sample text searches for suspicious PHP code
1
| grep -Rn "mkdir *(" public_html/ |
Or
1
| grep -RPn "(passthru|shell_exec|system|phpinfo|base64_decode|chmod|mkdir|fopen|fclose|readfile) *\(" public_html/ |
Tuesday, 14 January 2014
How to move mysql to backend server
This is a great solution to off-load struggling server under load:
nano /etc/network/interfaces
#add following
auto eth1
iface eth1 inet static
address 192.168.1.1
netmask 255.255.255.0
mtu 9000
Don't forget to assign permissions to the front-end user:
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON mydb.* TO 'myuser'@'192.168.1.2' IDENTIFIED BY 'supersecretpassword';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON mydb.* TO 'myuser'@'192.168.1.2.localdomain' IDENTIFIED BY 'supersecretpassword';
FLUSH PRIVILEGES;
quit;
To allow packet comming through:
iptables -A INPUT -i eth1 -s 192.168.1.2 -p tcp --destination-port 3306 -j ACCEPT
service networking restart
On frontend server:
nano /etc/network/interfaces
#add follwingCheck whether you can connect to the backend:
auto eth1
iface eth1 inet static
address 192.168.1.2
netmask 255.255.255.0
mtu 9000
mysql -u myuser -h 192.168.1.1 -p
service networking restart
thanks:
http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html
Saturday, 5 October 2013
Passwordless ssh login
You can login to a remote Linux server without entering password in 3 simple steps using ssky-keygen and ssh-copy-id as explained in this article.
ssh-keygen creates the public and private keys. ssh-copy-id copies the local-host’s public key to the remote-host’s authorized_keys file. ssh-copy-id also assigns proper permission to the remote-host’s home, ~/.ssh, and ~/.ssh/authorized_keys.
This article also explains 3 minor annoyances of using ssh-copy-id and how to use ssh-copy-id along with ssh-agent.
ssh-keygen creates the public and private keys. ssh-copy-id copies the local-host’s public key to the remote-host’s authorized_keys file. ssh-copy-id also assigns proper permission to the remote-host’s home, ~/.ssh, and ~/.ssh/authorized_keys.
This article also explains 3 minor annoyances of using ssh-copy-id and how to use ssh-copy-id along with ssh-agent.
Step 1: Create public and private keys using ssh-key-gen on local-host
jsmith@local-host$ [Note: You are on local-host here] jsmith@local-host$ ssh-keygen Generating public/private rsa key pair. Enter file in which to save the key (/home/jsmith/.ssh/id_rsa):[Enter key] Enter passphrase (empty for no passphrase): [Press enter key] Enter same passphrase again: [Pess enter key] Your identification has been saved in /home/jsmith/.ssh/id_rsa. Your public key has been saved in /home/jsmith/.ssh/id_rsa.pub. The key fingerprint is: 33:b3:fe:af:95:95:18:11:31:d5:de:96:2f:f2:35:f9 jsmith@local-host
Step 2: Copy the public key to remote-host using ssh-copy-id
jsmith@local-host$ ssh-copy-id -i ~/.ssh/id_rsa.pub remote-host jsmith@remote-host's password: Now try logging into the machine, with "ssh 'remote-host'", and check in: .ssh/authorized_keys to make sure we haven't added extra keys that you weren't expecting.
Note: ssh-copy-id appends the keys to the remote-host’s .ssh/authorized_key.
Step 3: Login to remote-host without entering the password
jsmith@local-host$ ssh remote-host Last login: Sun Nov 16 17:22:33 2008 from 192.168.1.2 [Note: SSH did not ask for password.] jsmith@remote-host$ [Note: You are on remote-host here]
The above 3 simple steps should get the job done in most cases.
Note:
Recently I started to get Agent admitted failure to sign using the key on ubuntu 12.04, but this solved the problem:
# start the ssh-agent in the background eval "$(ssh-agent -s)
After setting that up refer to "Disable root login" to disable root login for extra security.
Subscribe to:
Comments (Atom)