MySQL- basic commands in Mysql

To login from unix shell.
[mysql dir]/bin/mysql -h hostname -u root -p

*Note: use -h only if needed.
Create, List, Use and Delete Databases Commands

create database
This command is used to create a Database on the sql server..
Syntax: create [db name];

Eg:
create employees;

show databases
This command id used to list all databases on the sql server.
Syntax: show databases;

use database
This command is used to switch to a database.
Syntax: use [db name];

drop database
This command is used to delete a database.
Syntax: drop [db name];
Creating tables and working with MySQL

create tabe
This command is used to create tables in a database.

Eg:
CREATE TABLE [table name] (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups
VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

show tables
This command is see all the tables in the database.

describe table
To see database's field formats.
describe [table name];

drop table
To delete a table.
drop table [table name];

Show all data in a table.
SELECT * FROM [table name];

Returns the columns and column information pertaining to the designated table.
show columns from [table name];

Show certain selected rows with the value "something".
SELECT * FROM [table name] WHERE [field name] = "something";

Show all records containing the name "Telson" AND the phone number '2255'.
SELECT * FROM [table name] WHERE name = "Telson" AND phone_number = '2255';

Show all records not containing the name "Telson" AND the phone number '2255' order by the phone_number field.
SELECT * FROM [table name] WHERE name != "Telson" AND phone_number = '2255' order by phone_number;

Show all records starting with the letters 'Tel' AND the phone number '2255'.
SELECT * FROM [table name] WHERE name like "Tel%" AND phone_number = '2255';

Use a regular expression to find records. Use "REGEXP BINARY" to force case-sensitivity. This finds any record beginning with a.
SELECT * FROM [table name] WHERE rec RLIKE "^a$";

Show unique records.
SELECT DISTINCT [column name] FROM [table name];

Show selected records sorted in an ascending (asc) or descending (desc).
SELECT [column1],[column2] FROM [table name] ORDER BY [column2] DESC;

Return number of rows.
SELECT COUNT(*) FROM [table name];

Sum column.
SELECT SUM(*) FROM [table name];

Join tables on common columns.
select lookup.illustrationid, lookup.personid,person.birthday from lookup left join person on lookup.personid=person.personid=statement to join birthday in person table with primary illustration id;
Creating Mysql databse Users and changing Password

Switch to the mysql db.

Create a new user.
INSERT INTO [table name] (Host,User,Password) VALUES('%','user',PASSWORD('password'));

Change a users password.(from unix shell).
[mysql dir] mysqladmin -u root -h hostname.blah.org -p password 'new-password'

Change a users password.(from MySQL prompt).
SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');

Allow the user "telson" to connect to the server from localhost using the password "passwd"
grant usage on *.* to telson@localhost identified by 'passwd';

Switch to mysql db.
Give user privilages for a db.
INSERT INTO [table name] (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');

or

grant all privileges on databasename.* to username@localhost;
Modifying and Updating tables in MySQL

To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user'; Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'something';

Update database permissions/privilages.
FLUSH PRIVILEGES;

Delete a column.
alter table [table name] drop column [column name];

Add a new column to db.
alter table [table name] add column [new column name] varchar (20);

Change column name.
alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.
alter table [table name] add unique ([column name]);

Make a column bigger.
alter table [table name] modify [column name] VARCHAR(4);

Delete unique from table.
alter table [table name] drop index [colmn name];

Load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);
Creating MySQL Database backup's and Restoring Databases

Dump all databases for backup. Backup file is sql commands to recreate all db's.
[mysql dir] mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.
[mysql dir] mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.
[mysql dir] mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.
[mysql dir] mysql -u username -ppassword databasename < /tmp/databasename.sql

EXIM -- MTA

Exim
=====
Conf : /etc/exim.conf - exim main configuration file
/etc/localdomains - list of domains allowed to relay mail
Log : /var/log/exim_mainlog - incoming/outgoing mails are logged here
/var/log/exim_rejectlog - exim rejected mails are reported here
/var/log/exim_paniclog - exim errors are logged here
Mail queue: /var/spool/exim/input
Cpanel script to restart exim - /scripts/restartsrv_exim
Email forwarders and catchall address file - /etc/valiases/domainname.com
Email filters file - /etc/vfilters/domainname.com
POP user authentication file - /home/username/etc/domainname/passwd
catchall inbox - /home/username/mail/inbox
POP user inbox - /home/username/mail/domainname/popusername/inbox
POP user spambox - /home/username/mail/domainname/popusername/spam
Program : /usr/sbin/exim (suid - -rwsr-xr-x 1 root root )
Init Script: /etc/rc.d/init.d/exim
force exim up : /scripts/eximup --force
Log file is located at /var/log/exim4/mainlog
Count the number of messages in the queue.

root@localhost# exim -bpc
Listing the messages in the queue (time queued, size, message-id, sender, recipient).

root@localhost# exim -bp
Search the queue for messages from a specific sender.

root@localhost# exiqgrep -f [luser]@domain
Search the queue for messages for a specific recipient/domain.

root@localhost# exiqgrep -r [luser]@domain
Print messages older than the specified number of seconds.

Eg: messages older than 1 hour.
root@localhost# exiqgrep -o 3600 [...]
Print messages younger than the specified number of seconds.

Eg: messages less than an hour old.
root@localhost# exiqgrep -y 3600 [...]
Match the size of a message with a regex. Eg: Messages between 500-599 bytes.

root@localhost# exiqgrep -s '^5..$' [...]
Print just the message-id of the entire queue.

root@localhost# exiqgrep -i
Remove a message from the queue.

root@localhost# exim -Mrm <message-id> [ <message-id> ... ]
Freeze a message.

root@localhost# exim -Mf <message-id> [ <message-id> ... ]
View a message's logs.

root@localhost# exim -Mvl <message-id>

========================
Remove all frozen messages.

root@localhost# exiqgrep -z -i | xargs exim -Mrm

there can be lot of frozen messages in the queue which are not delivered most probabalt spammed messages the script deletes them
exim -bp | awk '$6~"frozen" {print $3 }' | xargs exim -Mrm
Remove all messages older than five days (86400 * 2 = 172800 seconds).

root@localhost# exiqgrep -o 172800 -i | xargs exim -Mrm
Remove all messages latest five days (86400 * 2 = 172800 seconds).

root@localhost# exiqgrep -y 172800 -i | xargs exim -Mrm
Freeze all queued mail from a given sender.

root@localhost# exiqgrep -i -f user@example.com | xargs exim -Mf

To remove all messages from the queue, enter:
===================================================================
grep -R -l [SPAM] /var/spool/exim/msglog/*|cut -b26-|xargs exim -Mrm

exim -bp | awk '/^ *[0-9]+[mhd]/{print "exim -Mrm " $3}' | bash

Command to send all the emails in the mail queue
===================================================================
exim -bpru |awk '{print $3}' | xargs -n 1 -P 40 exim -v -M

=========================

Send a test message send "content" | mail -s "subject" user@example.com

Send a message without "send": echo "body" | mail -s "subject" user@example.com

==========================

##############################################################
Troubleshoot Spamming#########################################
##############################################################

Get details of scripts that are used to send out spam emails :
grep “cwd=” /var/log/exim_mainlog|awk ‘{for(i=1;i teststats

Script to know the mail count by various accounts
grep “cwd=” /var/log/exim_mainlog|awk ‘{for(i=1;i<=10;i++){print $i}}’|sort|uniq -c|grep cwd|sort -n

The number of mails by a domain
exigrep @domain.com /var/log/exim_mainlog|grep 2009-04-17|grep Completed|wc -l

1)Issue this command: ps -C exim -fH ewww |grep home, it shows the mails going from the server.
It shows from which user’s home the mail is going, so that you can easily trace it and block it if needed.

2)Issue this command: eximstats -ne -nr /var/log/exim_mainlog
It shows top 50 domains using mail server with options.

3)Issue this command: exim -bp | exiqsumm
It shows the main domains receiving and sending mails on the server.

4)Issue this command: netstat -plan|grep :25|awk {'print $5'}|cut -d: -f 1|sort|uniq -c|sort -nk 1
It shows the IPs which are connected to server through port number 25. It one particular Ip is using more than 10 connection you can block it in the server firewall.

5)In order to find “nobody” spamming, issue the following command
ps -C exim -fH ewww|awk ‘{for(i=1;i<=40;i++){print $i}}'|sort|uniq -c|grep PWD|sort -n

It will give some result like:
Example :
6 PWD=/
347 PWD=/home/sample/public_html/test
Count the PWD and if it is a large value check the files in the directory listed in PWD
(Ignore if it is / or /var/spool/mail /var/spool/exim)

The above command is valid only if the spamming is currently in progress. If the spamming has happened some hours before, use the following command.

grep “cwd=” /var/log/exim_mainlog|awk ‘{for(i=1;i<=10;i++){print $i}}'|sort|uniq -c|grep cwd|sort -n

This will result in something like :
47 cwd=/root
8393 cwd=/home/sample/public_html/test

Count the cwd and if it is a large value check the files in the directory listed in cwd
(Ignore if it is / or /var/spool/mail /var/spool/exim)

Pass the below mentioned command at your command prompt to find the domain which is being used by spammers.
exim -bp
exim -bpr | exiqsumm -c | head
Then,
exiqgrep -ir | xargs -n1 exim -Mrm

================================

Config-Server-Firewall

Installing CSF---config-server-firewall


Downloading the Packages

--------------->wget http://www.configserver.com/free/csf.tgz
--------------->tar zxvf csf.tgz

--------------->cd csf

This is where the paths diverge: cPanel server, or non-cPanel server.

--------------->./install.cpanel.sh

If you are running a non-cpanel redhat server:

--------------->./install.sh

---------------> /etc/init.d/csf restart


First run following command that you have all the required iptables modules available for running CSF full. Don’t worry if you cannot run all the features, so long as the script doesn’t report any FATAL errors
[root@desk csf]# perl /etc/csf/csftest.pl
Testing ip_tables/iptable_filter…OK
Testing ipt_LOG…OK
Testing ipt_multiport/xt_multiport…OK
Testing ipt_REJECT…OK
Testing ipt_state/xt_state…OK
Testing ipt_limit/xt_limit…OK
Testing ipt_recent…OK
Testing ipt_owner…OK
Testing iptable_nat/ipt_REDIRECT…OK
RESULT: csf should function on this server
Looks 100% OK.


Here are the most common commands you will be using:

csf -d IPADDRESS will deny an IP.
csf -a IPADDRESS will allow an IP.
csf -r will reload all rules.
-dr, --denyrm ip    Remove and unblock an IP address in /etc/csf.deny
-t, --temp          Displays the current list of temporary IP bans and their TTL
-tr, --temprm ip    Remove an IP address from the temporary IP ban list


---------------------------
# Testing flag - enables a CRON job that clears iptables incase of
# configuration problems when you start csf. This should be enabled until you
# are sure that the firewall works - i.e. incase you get locked out of your
# server! Then do remember to set it to 0 and restart csf when you're sure
# everything is OK. Stopping csf will remove the line from /etc/crontab
TESTING = "1"

Edit the last line of that block of text so that it reflects testing being disabled:
TESTING = "0"

Finally, restart CSF:
---------------------------


More about csf
##############################
Now edit the /etc/csf/csf.conf
Put your all ports which you want to be open on your server for incoming traffic seperated by comma.
TCP_IN = “20,21,22,25,53,80,110,143,443,465,587,993,995″
Also open any port you want for outgoing traffic
TCP_OUT = “20,21,22,25,53,80,110,113,443″
Same goes for UDP_IN and UDP_OUT, be remember if you are running DNS service, so you have to open port 53 in UDP_IN as DNS port 53 runs on udp rather than tcp
UDP_IN = “20,21,53″
To allow outgoing traceroute add 33434:33523 to this list
UDP_OUT = “20,21,53,113,123,33434:33523″
#############################
SYNFLOOD protection is already enabled and if you want to change the RATE or BURST value you can use following lines to match your traffic.
SYNFLOOD = “0″
SYNFLOOD_RATE = “100/s”
SYNFLOOD_BURST = “150″
currently the RATE is 100/s and BURST can upto 150. This can be varry from server to server.
i.e. if 100 connections are received from an IP/sec for 150 times, block it. Make sure don’t keep it too strict if you are not receiving an attack else it will generate false positives and will block legit connections.
############################
Search for “PORTFLOOD”
PORTFLOOD = “80;tcp;20;300″
This rule will block IPs that connects to port 80 via TCP more than 20 times within 300 seconds.  Once the attack is normal then remove this rule from the csf firewall.
############################
# To disable this feature, set this to 0
CT_LIMIT = Default: 50 (means 50 connections per ip address)
# Connection Tracking interval. Set this to the the number of seconds between
# connection tracking scans
CT_INTERVAL = Default: 30
# Send an email alert if an IP address is blocked due to connection tracking
CT_EMAIL_ALERT = Default: 1
# If you want to make IP blocks permanent then set this to 1, otherwise blocks
# will be temporary and will be cleared after CT_BLOCK_TIME seconds
CT_PERMANENT = Default: 0
# If you opt for temporary IP blocks for CT, then the following is the interval
# in seconds that the IP will remained blocked for (e.g. 1800 = 30 mins)
CT_BLOCK_TIME = Default: 1800
# If you don’t want to count the TIME_WAIT state against the connection count
# then set the following to “1″
CT_SKIP_TIME_WAIT = Default: 0
# If you only want to count specific states (e.g. SYN_RECV) then add the states
# to the following as a comma separated list. E.g. “SYN_RECV,TIME_WAIT”
#
# Leave this option empty to count all states against CT_LIMIT
CT_STATES =
# If you only want to count specific ports (e.g. 80,443) then add the ports
# to the following as a comma separated list. E.g. “80,443″
#
# Leave this option empty to count all ports against CT_LIMIT
CT_PORTS = 80,443
############################
CONNLIMIT is a comma separated list of:
port;limit
So, a setting of CONNLIMIT = "22;5,80;20" means:
1. Only allow up to 5 concurrent new connections to port 22 per IP address
2. Only allow up to 20 concurrent new connections to port 80 per IP address
Note: Existing connections are not included in the count, only new SYN packets,
i.e. new connections
############################
If you want to add some spam protection, CSF can help. Look in the configuration for the following:
LF_SCRIPT_ALERT = 0 change this to 1. This will send an email alert to the system administrator when the limit configured below is reached within an hour.
LF_SCRIPT_LIMIT = 100 change this to 250. This will alert you when any scripts sends out 250 email messages in an hour.
Define email address to which you need to get alerts and define email address to which you want to get.
LF_ALERT_TO = “snipped@google.com”
LF_ALERT_FROM = “csf@google.com”
###########################