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

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