Tuesday, September 29, 2009

LVM Single Drive to LVM RAID 1 Mirror MigrationLVM Single Drive to LVM RAID 1 Mirror Migration

SkyHi @ Tuesday, September 29, 2009
Steps to migrate a running machine using LVM on a single drive to mirrored drives on Linux RAID 1 (mirror) and LVM. Keep the machine online while data is migrated across the LVM too!

This document was written based on a How-to article for Debian Etch (see references for original article). This version was tested using CentOS 5.3. It may work for other versions of CentOS and Linux using LVM on a single drive.

In my example the main drive is /dev/sda where /dev/sda1 is a ext3 /boot partition and /dev/sda2 is a our LVM PV. Our new hard drive is /dev/sdb and is the same model and size as /dev/sda. LVM naming may be different and I have outlined steps to get PV, VG, and LV names of your specific system setup.

Remember to BACKUP YOUR DATA! I make no guarantees this will work and you should have all data backed up off of BOTH drives used in this tutorial to an external source that is disconnected from the machine when starting this tutorial.




Insert a second drive

This step will vary between systems. In short, you need to put the new drive into the machine that will be part of the RAID mirror. The drive should be identical in size as the current one and if possible, the same model.

Throughout this document the new drive will be referenced as /dev/sdb.

2.
Print current partition layout for reference

We will need the current partition layout of /dev/sda (our original drive with LVM partition). Our example has only 2 partitions. /dev/sda1 (the boot partition) and /dev/sda2 (the LVM partition).

Print the current layout with the following command
# fdisk -l /dev/sda

3.
Partition the second drive

We now need to partition the second drive identical to the first drive. Use the starting cylinder and last cylinder of the first drive partition layout to ensure the second drive is the same.

Partition the new drive with the following command:
# fdisk /dev/sdb

a. Press 'n' to create a new partition
b. Press 'p' for primary partition
c. Press '1' to create the first primary partition (this step might be automatically completed as there are no partitioins yet)
d. Press '1' to start it at the first cylinder
e. Type in the number of the last cylinder for the original /dev/sda1 partition, in our example it is '13'
f. Type in 't' to set the partition type
g. Type in the partition number
h. Type in 'fd' for Linux RAID type
i. Perform sub-steps a-h for the second primary partition
j. Type in 'w' to write the changes

4.
Compare the partition layout between the two drives

We should now have /dev/sdb partitioned with 2 Linux RAID partitions the same size as the /dev/sda partitions (which are likely not Linux RAID type, this is OK). Verify the partition sizes line up:

# fdisk -l

Our example system now outputs:

Disk /dev/sda: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 13 104391 83 Linux
/dev/sda2 14 1044 8281507+ 8e Linux LVM

Disk /dev/sdb: 8589 MB, 8589934592 bytes
255 heads, 63 sectors/track, 1044 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdb1 1 13 104391 fd Linux raid autodetect
/dev/sdb2 14 1044 8281507+ fd Linux raid autodetect

5.
Create our new RAID mirror devices

Zero the superblock in case the new drive happened to be part of a linux software RAID before:

# mdadm --zero-superblock /dev/sdb1
# mdadm --zero-superblock /dev/sdb2

Create the new RAID devices:

# mdadm --create /dev/md0 --verbose --level=1 --raid-devices=2 missing /dev/sdb1
# mdadm --create /dev/md1 --verbose --level=1 --raid-devices=2 missing /dev/sdb2

We use the word 'missing' in place of the first drive as we will add the drive to the array after we confirm the machine can boot and all the data is on array (which will currently only have 1 drive, the newly added /dev/sdb).

6.
Build the /etc/mdadm.conf

There may already be a mdadm.conf in which case you may only need to add the arrays with the mdadm --examine line. In our example we did not have any /etc/mdadm.conf and needed to build one by hand.

The following commands will build a simple /etc/mdadm.conf that sends notifications to root (our example machine also has root aliased to an external email address).

# echo "DEVICE partitions" >> /etc/mdadm.conf
# echo "MAILADDR root" >> /etc/mdadm.conf
# mdadm --examine --scan >> /etc/mdadm.conf

7.
Format the new RAID boot partition

We can now format the RAID partition that will be used in place of our boot partition on /dev/sda1. In our example system the original partition is ext3 mounted as /boot.

Setup using command:
# mkfs.ext3 /dev/md0

8.
Mount and build the new boot partition

We need to copy over the existing boot partition to the new RAID device:

# mkdir /mnt/boot
# mount /dev/md0 /mnt/boot
# cp -dpRx /boot/* /mnt/boot/
# umount /mnt/boot

9.
Mount new boot partition in place of old

We will now unmount the current /boot partition and mount our new /dev/md0 device in place of it to prepare for making a new initrd image.

# umount /boot
# mount /dev/md0 /boot

10.
Build new initrd image

Now build a new initrd image that contains the dm-mirror and other raid modules to be safe (without these the boot will fail not recognizing the new /boot).

# mv /boot/initrd-$(uname -r).img /boot/initrd-$(uname -r).img.bak
# mkinitrd -v /boot/initrd-$(uname -r).img $(uname -r)

11.
Install grub on both drives

Install grub on both physical drives MBR and edit /etc/fstab to reflect our new boot partition /dev/md0

# grub-install /dev/sda
# grub-install /dev/sdb

If grub-install complains about not able to find /dev/sdb or that it is not part of the BIOS drives then you may need to use the --recheck switch

# grub-install --recheck /dev/sdb
# grub-install --recheck /dev/sda

12.
Edit /etc/fstab to reflect new /boot location

Edit the /etc/fstab file to replace LABEL=/boot with /dev/md0. Your new /boot partition line should look something like:

/dev/md0 /boot ext3 defaults 1 2

13.
Time for the first reboot!

Now, reboot the system to get it up and running on the new boot partition and off of /dev/sda1. This will confirm that we can safely change the /dev/sda1 partition ID to Linux RAID type and then add the partition to the md0 mirror.

# reboot

14.
Take a breather... Verify /boot is mounted using the RAID device

You are about half way done. Hopefully everything is up and running after your first reboot.

Check the mount command to verify that /boot is using the new /dev/md0 device:

# mount

If it is then you can are set to continue... after a short break of course.

15.
Change first drive /dev/sda1 to Linux RAID type

Modify the old /boot device (/dev/sda1) to be Linux RAID type. This will prepare it so it can be added to our RAID device /dev/md0 (which our new /boot is using).

# fdisk /dev/sda

a. Type in 't' to set the partition type
b. Type in the partition number (1)
c. Type in 'fd' for Linux RAID type
d. Type in 'w' to write the changes and exit fdisk

16.
Add /dev/sda1 to /dev/md0 mirror

Add the /dev/sda1 partition to the /dev/md0 mirror and remove the old label

# e2label /dev/sda1 ""
# mdadm --add /dev/md0 /dev/sda1

17.
Wait for the /dev/md0 mirror to rebuild

Watch the /proc/mdstat output to make sure the syncing of the newly completed /dev/md0 device finishes before continuing.

# watch cat /proc/mdstat

Once it is complete you may continue. Since the /boot partition is usually only a couple hundred megabytes this usually completes very quickly.

18.
Create new LVM physical volume

Create a new LVM PV on the second raid device. We will use this new PV to extend the current LVM volume group to.

# pvcreate -v /dev/md1

19.
Extend volume group to new PV

Use command `vgs` to show the name of the LVM VG. In our example it is VolGroup00.

Run the following command replacing VolGroup00 with the actual name of your volume group. You may have to do this step for each VG if you have multiple:

# vgextend -v VolGroup00 /dev/md1

20.
Move logical volume to new PV

Use `pvdisplay` to verify the VG is now using both drives:

# pvdisplay

Now we can move the LV to the new PV:

Use`lvs` to get the LV names (you will need to move all) then run the following command for both (replacing LogVolXX with your LV names):

# pvmove -v /dev/sda2 /dev/md1

Wait a while.... It will output % done every 15 seconds.

Depending on the size of your original drive this may take an hour or more. It is now copying everything from the first drive to the second drive while keeping the machine online.

21.
Remove the VG from the old PV

Once all the data is finished moving we want to 'reduce' the volume groups to only use the new physical volume and then remove the old physical volume so it cna no longer be used for LVM.

# vgreduce -v VolGroup00 /dev/sda2
# pvremove /dev/sda2

22.
Build new initrd image (again)

Built new initrd image again. This was needed on our example system to keep from throwing a kernel panic at boot. We are being safe by doing this step again.

# mv /boot/initrd-$(uname -r).img /boot/initrd-$(uname -r).img.bak
# mkinitrd -v /boot/initrd-$(uname -r).img $(uname -r)

23.
Install grub on both drives (again)

Double check that grub is installed correctly.

Somewhere between the Step 10 reboot and Step 24 reboot grub became corrupted (froze with only 'GRUB' on screen and no indicator) so I had to reinstall grub again on each drive to be safe. The fix was to install grub the traditional way using the grub commands root (hd0,1) and setup (hd0) and then the same for hd1.

# grub-install --recheck /dev/sdb
# grub-install --recheck /dev/sda

24.
Reboot again!

Reboot the machine again:

# reboot

Everything come back up OK? Great, we are almost done!

25.
Change first drive /dev/sda2 to Linux RAID type

Change the old LVM partiton to Linux RAID type:

# fdisk /dev/sda

a. t
b. 2
c. fd
d. w

Verify the partition type is now Linux RAID:

# fdisk -l

26.
Add /dev/sda2 to /dev/md1 mirror

We are now ready to add the old /dev/sda2 LVM partition to the /dev/md1 RAID device:

# mdadm --add /dev/md1 /dev/sda2

27.
Wait for the /dev/md1 mirror to rebuild

Watch the /proc/mdstat output to make sure the syncing of the newly completed /dev/md1 device finishes before rebooting.

# watch cat /proc/mdstat

28.
Last reboot!

Reboot the machine once more to verify that everything is working correctly:

# reboot

Is the machine back up and running? No data loss?

Wonderful! You have just completed migrating your single drive machine to a two drive Linux RAID mirror.

Conclusion

While it is always best to create RAID setups and the time of install there may be a desire to have RAID after the fact when the original installer was not required, did not have the hardware (only 1 drive available), or didn't have the know how.

In a pinch, this may help to bring a production system some drive redundancy. I hope you found this tutorial helpful!


Reference: http://community.spiceworks.com/how_to/show/340

Malecious Random JavaScript Rootkit

SkyHi @ Tuesday, September 29, 2009
Malecious Random JavaScript Rootkit
(Adapted from http://www.cpanel.net/security/notes/random_js_toolkit.html )

BugTargeted Systems
A Linux server virus has recently been reported targeting multiple platforms such as: RedHat Enterprise, Centos v4.x/v5.x, and Fedora Core v5/v6. This Rootkit is not believed to be specific to any one control panel and/or Php application(s). Unfortunately, there are still many unknown details. We are actively researching the entry point to this exploit to pinpoint the methods used to gain access in order to stop this Rootkit from infecting servers in the future. It's a very time and energy consuming process as the primary Rootkit is designed to be hidden within the system which proves to be challenging even to Level I engineers.

Affected Binary Packages
It has been established that this Rootkit requires super user privileges. The initial entry point has not been confirmed yet. When a hacker gains shell access, the Rootkit is installed. Frequently-compromised binaries include:

/sbin/ifconfig
/sbin/fsck
/sbin/route
/bin/basename
/bin/cat
/bin/mount
/bin/touch

The Rootkit renames these system binary packages by adding a random set of characters at the end of the file name. Additionally, a 0 byte file with a different set of random characters is created based upon the target binary package's name.

For example:

/sbin/routewWmVTnBL6szkobbNZ9Iz
/sbin/routeGnAxnt168fMJAxHiru22

These files are hidden on the live filesystem of an affected system. In order to view these files, the system must be rebooted to a safe environment such as a system rescue CD.

How a Rootkit works

1. Once the Rootkit is successfully installed, the server will sit idle until rebooted. During a server reboot, the system initialization scripts will call the infected binaries.
2. When executed, the infected binary packages use /dev/mem as a pathway to the Kernel , and then attach to several system calls within the running Kernel. This results in hidden files, broken binary packages, and random JavaScript code being seen by web visitors.
3. When the system is fully online in an infected state, the Kernel will begin serving a JavaScript payload to random web requests/visits. This occurs outside of Apache and will not be seen in any of the Apache logs. The JavaScript injection will look like:


The JavaScript file is dynamically created and will have a random five character filename. This JavaScript will begin exploiting several known vulnerabilities within Windows, QuickTime and Yahoo Messenger on the web visitor's PC. Keep in mind that the JavaScript is not served on every request. It is injected into a small percentage of requests at random.

How do I know my server has been infected with this JS Rootkit ?

If you feel your server is being compromised, you can run the tests below to confirm.

The easiest test is to attempt to create a directory with a numerical name such as:

mkdir 123test

If your system returns the following error:

[root\@server ~]# mkdir 1
mkdir: cannot create directory `1': No such file or directory

Your system has been compromised.

This is not always true in older variants of a Rootkit . To make sure that your server is not compromised, run the following command for 3-5 minutes in order to sniff packets:

tcpdump -nAs 2048 src port 80 | grep "[a-zA-Z]\{5\}\.js'"

If your server is infected, the system will return the following result:

root@server log]# tcpdump -nAs 2048 src port 80 | grep "[a-zA-Z]\{5\}\.js'"
tcpdump: verbose output suppressed, use -v or -vv for full protocol

decode
listening on eth0, link-type EN10MB (Ethernet), capture size 2048 bytes


1311 packets captured
2627 packets received by filter
0 packets dropped by kernel

Additional detection methods require an in-depth knowledge of Kernel debugging.

Solution
It is possible to remove this Rootkit by booting the server into a safe environment and moving the previous binary back in place. In this case, /sbin/routewWmVTnBL6szkobbNZ9Iz is the original binary. You can confirm this by looking at the size of the file. One will be 0 bytes and the other will be relatively significant in size. We suggest copying these binaries from a trusted and clean server.

Keep in mind that this is a root compromise. Until the point of entry is determined and closed, the server can continue to be compromised without proper security practices in place. We highly recommended that you do the following:

1. Contact your datacenter, NOC, or a qualified administrator to have the system fully checked and recovered.
2. Secure and harden your server.
3. Change any root passwords that have been previously used.
4. DO NOT give shell or jailshell access to ANYONE.
5. Update your server’s applications and services to the latest release, especially security patches and monitor the server closely for any unsuspected activity.

June 26th, 2008: Read the Latest findings about the Random JavaScript Rootkit

Click here if you wish to protect your server against IFrame JS code with "ServerTune IFrame Shield"

More information about Kernel Rootkits (To view this page, your browser must support in-line frames.)

Reference: http://servertune.com/kbase/entry/258/

What Causes High Server Load?

SkyHi @ Tuesday, September 29, 2009
What is Server Load Average?
The Server Load Average gives the sum of the average number of jobs or active processes in the queue over the last 1, 5, and 15 minutes. It is intended to provide you with a simplistic indication on how much work has been done on your server .

Server Load Average is measured in the x.xx format, with values ranging from 0.00 to potentially unlimited.

Calculated in the UNIX kernel using CALC_LOAD, server load averages are based on exponentially dampened moving averages. New server load average values are in part based on the last server load recordings.

What is an acceptable Server Load Average?
There are a few factors involved to determine the server average load. If your server (s) use dual processors, the acceptable Server Load Average is 2.00. This load is considered "optimal".

There is no clear-cut Server Load Average number considered to be a 'warning' level for performance degradation. However if the server load gets up to 5.00 or greater for a consistent period of time, then it's worth finding out the exact cause of the higher server load.

Server Load Spikes
It's perfectly normal to have 'spikes' in your Server Load. Peak visitor times, log processing, database backup scripts, automated cron jobs - all can cause 'spikes' in your server load. It's when the server load is constantly above 5.00 - that's the time to start asking questions.

What Causes High Server Load?
There are lots of potential causes of high server loads. Server load levels very much depend on what is being run on your server . The following table gives a good overview of some potential areas which could cause your server to be placed under higher loads.

Possible Causes of High Server Loads

* High server loads could be caused by just one or several resource-intensive application(s). Examples include very high-traffic Web sites, database-driven Web sites, forums, gaming sites, file download sites and so on.
* A high server load can also be caused by a malicious script or a "runaway script" which can continuously loop, dragging down the server 's resources.
* Too many websites on the one server - with the cumulative resources resulting in high server load.
* Running out of memory and swapping to the swap file.
* Server backups or server updates are taking place.
* Server comes under intermittent or continuous Internet attack. Click here to read about different Internet attacks.
* Mis-configured software causing errors.
* Users sending large mailing lists.
* Users trying to bounce spam.
* Users/spammers sending spam email.
* Hardware issues including memory leak, bad hard drive, and network card.

*** NOTE ***
If you experience high CPU load problem of any kind, we suggest you sign up for the ServerTune Plan to put your server in perfect working condition. Our Linux certified engineers are ready to fix any problem you might have with your server. If you have any questions, please don't hesitate to send a message to customerservice@servertune.com. We'll be more than happy to work with you.

Referece: http://servertune.com/kbase/entry/67/

MySQL HowTo :: commands, statements, and clauses

SkyHi @ Tuesday, September 29, 2009
Description Command
To login (from unix shell) use -h only if needed. [mysql dir]/bin/mysql -h hostname -u root -p
Create a database on the sql server . create database [databasename];
List all databases on the sql server. show databases;
Switch to a database. use [db name];
To see all the tables in the db. show tables;
To see database's field formats. describe [table name];
To delete a db. drop database [database name];
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 "whatever". SELECT * FROM [table name] WHERE [field name] = "whatever";
Show all records containing the name "Bob" AND the phone number '3444444'. SELECT * FROM [table name] WHERE name = "Bob" AND phone_number = '3444444';
Show all records not containing the name "Bob" AND the phone number '3444444' order by the phone_number field. SELECT * FROM [table name] WHERE name != "Bob" AND phone_number = '3444444' order by phone_number;
Show all records starting with the letters 'bob' AND the phone number '3444444'. SELECT * FROM [table name] WHERE name like "Bob%" AND phone_number = '3444444';
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 [col1],[col2] FROM [table name] ORDER BY [col2] 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;
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]/bin/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 "bob" to connect to the server from localhost using the password "passwd" grant usage on *.* to bob@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;
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] = 'whatever';
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(3);
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);
Dump all databases for backup. Backup file is sql commands to recreate all db's. [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup. [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database. [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup. [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1. 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));
Create Table Example 2. create table [table name] (personid int(50) not null auto_increment primary key,firstname varchar(35),middlename varchar(50),lastname varchar(50) default 'bato');

MYSQL Statements and clauses

ALTER DATABASE
ALTER TABLE
ALTER VIEW
ANALYZE TABLE
BACKUP TABLE
CACHE INDEX
CHANGE MASTER TO
CHECK TABLE
CHECKSUM TABLE
COMMIT
CREATE DATABASE
CREATE INDEX
CREATE TABLE
CREATE VIEW
DELETE
DESCRIBE
DO
DROP DATABASE
DROP INDEX
DROP TABLE
DROP USER
DROP VIEW
EXPLAIN
FLUSH
GRANT
HANDLER
INSERT
JOIN
KILL
LOAD DATA FROM MASTER
LOAD DATA INFILE
LOAD INDEX INTO CACHE
LOAD TABLE...FROM MASTER
LOCK TABLES
OPTIMIZE TABLE
PURGE MASTER LOGS
RENAME TABLE
REPAIR TABLE
REPLACE
RESET
RESET MASTER
RESET SLAVE
RESTORE TABLE
REVOKE
ROLLBACK
ROLLBACK TO SAVEPOINT
SAVEPOINT
SELECT
SET
SET PASSWORD
SET SQL_LOG_BIN
SET TRANSACTION
SHOW BINLOG EVENTS
SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW CREATE DATABASE
SHOW CREATE TABLE
SHOW CREATE VIEW
SHOW DATABASES
SHOW ENGINES
SHOW ERRORS
SHOW GRANTS
SHOW INDEX
SHOW INNODB STATUS
SHOW LOGS
SHOW MASTER LOGS
SHOW MASTER STATUS
SHOW PRIVILEGES
SHOW PROCESSLIST
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES
SHOW WARNINGS
START SLAVE
START TRANSACTION
STOP SLAVE
TRUNCATE TABLE
UNION
UNLOCK TABLES
USE

String Functions

AES_DECRYPT
AES_ENCRYPT
ASCII
BIN
BINARY
BIT_LENGTH
CHAR
CHAR_LENGTH
CHARACTER_LENGTH
COMPRESS
CONCAT
CONCAT_WS
CONV
DECODE
DES_DECRYPT
DES_ENCRYPT
ELT
ENCODE
ENCRYPT
EXPORT_SET
FIELD
FIND_IN_SET
HEX
INET_ATON
INET_NTOA
INSERT
INSTR
LCASE
LEFT
LENGTH
LOAD_FILE
LOCATE
LOWER
LPAD
LTRIM
MAKE_SET
MATCH
AGAINST
MD5
MID
OCT
OCTET_LENGTH
OLD_PASSWORD
ORD
PASSWORD
POSITION
QUOTE
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SHA
SHA1
SOUNDEX
SPACE
STRCMP
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UNCOMPRESS
UNCOMPRESSED_LENGTH
UNHEX
UPPER

Date and Time Functions

ADDDATE
ADDTIME
CONVERT_TZ
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATE_ADD
DATE_FORMAT
DATE_SUB
DATEDIFF
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
FROM_UNIXTIME
GET_FORMAT
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SEC_TO_TIME
SECOND
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIMEDIFF
TIMESTAMP
TIMESTAMPDIFF
TIMESTAMPADD
TIME_FORMAT
TIME_TO_SEC
TO_DAYS
UNIX_TIMESTAMP
UTC_DATE
UTC_TIME
UTC_TIMESTAMP
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Mathematical and Aggregate Functions

ABS
ACOS
ASIN
ATAN
ATAN2
AVG
BIT_AND
BIT_OR
BIT_XOR
CEIL
CEILING
COS
COT
COUNT
CRC32
DEGREES
EXP
FLOOR
FORMAT
GREATEST
GROUP_CONCAT
LEAST
LN
LOG
LOG2
LOG10
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
STD
STDDEV
SUM
TAN
TRUNCATE
VARIANCE

Flow Control Functions

CASE
IF
IFNULL
NULLIF

Command-Line Utilities

comp_err
isamchk
make_binary_distribution
msql2mysql
my_print_defaults
myisamchk
myisamlog
myisampack
access
admin
binlog
bug
check
dump
dumpslow
hotcopy
import
show
perror

Perl API - using functions and methods built into the Perl DBI with MySQL

available_drivers
begin_work
bind_col
bind_columns
bind_param
bind_param_array
bind_param_inout
can
clone
column_info
commit
connect
connect_cached
data_sources
disconnect
do
dump_results
err
errstr
execute
execute_array
execute_for_fetch
fetch
fetchall_arrayref
fetchall_hashref
fetchrow_array
fetchrow_arrayref
fetchrow_hashref
finish
foreign_key_info
func
get_info
installed_versions
last_insert_id
looks_like_number
neat
neat_list
parse_dsn
parse_trace_flag
parse_trace_flags
ping


prepare
prepare_cached
primary_key
primary_key_info
quote
quote_identifier
rollback
rows
selectall_arrayref
selectall_hashref
selectcol_arrayref
selectrow_array
selectrow_arrayref
selectrow_hashref
set_err
state
table_info
table_info_all
tables
trace
trace_msg
type_info
type_info_all
Attributes for Handles

PHP API - using functions built into PHP with MySQL

mysql_affected_rows
mysql_change_user
mysql_client_encoding
mysql_close
mysql_connect
mysql_create_db
mysql_data_seek
mysql_db_name
mysql_db_query
mysql_drop_db
mysql_errno
mysql_error
mysql_escape_string
mysql_fetch_array
mysql_fetch_assoc
mysql_fetch_field
mysql_fetch_lengths
mysql_fetch_object
mysql_fetch_row
mysql_field_flags
mysql_field_len
mysql_field_name
mysql_field_seek
mysql_field_table
mysql_field_type
mysql_free_result
mysql_get_client_info
mysql_get_host_info
mysql_get_proto_info
mysql_get_server_info
mysql_info
mysql_insert_id
mysql_list_dbs
mysql_list_fields
mysql_list_processes
mysql_list_tables
mysql_num_fields
mysql_num_rows
mysql_pconnect
mysql_ping
mysql_query
mysql_real_escape_string
mysql_result
mysql_select_db
mysql_stat
mysql_tablename
mysql_thread_id
mysql_unbuffered_query


Reference: http://servertune.com/kbase/entry/158/

5.4.7. Causes of Access-Denied Errors

SkyHi @ Tuesday, September 29, 2009
If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

*

Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:

shell> mysql
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)
shell> mysql
ERROR 2002: Can't connect to local MySQL server through socket
'/tmp/mysql.sock' (111)

*

It might be that the server is running, but you are trying to connect using a TCP/IP port, named pipe, or Unix socket file different from the one on which the server is listening. To correct this when you invoke a client program, specify a --port option to indicate the proper port number, or a --socket option to indicate the proper named pipe or Unix socket file. To find out where the socket file is, you can use this command:

shell> netstat -ln | grep mysql

*

Make sure that the server has not been configured to ignore network connections or (if you are attempting to connect remotely) that it has not been configured to listen only locally on its network interfaces. If the server was started with --skip-networking, it will not accept TCP/IP connections at all. If the server was started with --bind-address=127.0.0.1, it will listen for TCP/IP connections only locally on the loopback interface and will not accept remote connections.
*

Check to make sure that there is no firewall blocking access to MySQL. Your firewall may be configured on the basis of the application being executed, or the port number used by MySQL for communication (3306 by default). Under Linux or Unix, check your IP tables (or similar) configuration to ensure that the port has not been blocked. Under Windows, applications such as ZoneAlarm or the Windows XP personal firewall may need to be configured not to block the MySQL port.
*

The grant tables must be properly set up so that the server can use them for access control. For some distribution types (such as binary distributions on Windows, or RPM distributions on Linux), the installation process initializes the mysql database containing the grant tables. For distributions that do not do this, you must initialize the grant tables manually by running the mysql_install_db script. For details, see Section 2.11.2, “Unix Post-Installation Procedures”.

To determine whether you need to initialize the grant tables, look for a mysql directory under the data directory. (The data directory normally is named data or var and is located under your MySQL installation directory.) Make sure that you have a file named user.MYD in the mysql database directory. If not, execute the mysql_install_db script. After running this script and starting the server, test the initial privileges by executing this command:

shell> mysql -u root test

The server should let you connect without error.
*

After a fresh installation, you should connect to the server and set up your users and their access permissions:

shell> mysql -u root mysql

The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the password for the root accounts is something you should do while you're setting up your other MySQL accounts. For instructions on setting the initial passwords, see Section 2.11.3, “Securing the Initial MySQL Accounts”.

MySQL Enterprise. The MySQL Enterprise Monitor enforces security-related best practices. For example, subscribers are alerted whenever there is any account without a password. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
*

If you have updated an existing MySQL installation to a newer version, did you run the mysql_upgrade script? If not, do so. The structure of the grant tables changes occasionally when new capabilities are added, so after an upgrade you should always make sure that your tables have the current structure. For instructions, see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.
*

If a client program receives the following error message when it tries to connect, it means that the server expects passwords in a newer format than the client is capable of generating:

shell> mysql
Client does not support authentication protocol requested
by server; consider upgrading MySQL client

For information on how to deal with this, see Section 5.5.6.3, “Password Hashing in MySQL”, and Section B.1.2.4, “Client does not support authentication protocol”.
*

Remember that client programs use connection parameters specified in option files or environment variables. If a client program seems to be sending incorrect default connection parameters when you have not specified them on the command line, check any applicable option files and your environment. For example, if you get Access denied when you run a client without any options, make sure that you have not specified an old password in any of your option files!

You can suppress the use of option files by a client program by invoking it with the --no-defaults option. For example:

shell> mysqladmin --no-defaults -u root version

The option files that clients use are listed in Section 4.2.3.3, “Using Option Files”. Environment variables are listed in Section 2.14, “Environment Variables”.
*

If you get the following error, it means that you are using an incorrect root password:

shell> mysqladmin -u root -pxxxx ver
Access denied for user 'root'@'localhost' (using password: YES)

If the preceding error occurs even when you have not specified a password, it means that you have an incorrect password listed in some option file. Try the --no-defaults option as described in the previous item.

For information on changing passwords, see Section 5.5.5, “Assigning Account Passwords”.

If you have lost or forgotten the root password, see Section B.1.4.1, “How to Reset the Root Password”.
*

If you change a password by using SET PASSWORD, INSERT, or UPDATE, you must encrypt the password using the PASSWORD() function. If you do not use PASSWORD() for these statements, the password will not work. For example, the following statement assigns a password, but fails to encrypt it, so the user is not able to connect afterward:

SET PASSWORD FOR 'abe'@'host_name' = 'eagle';

Instead, set the password like this:

SET PASSWORD FOR 'abe'@'host_name' = PASSWORD('eagle');

The PASSWORD() function is unnecessary when you specify a password using the CREATE USER or GRANT statements or the mysqladmin password command. Each of those automatically uses PASSWORD() to encrypt the password. See Section 5.5.5, “Assigning Account Passwords”, and Section 12.5.1.1, “CREATE USER Syntax”.
*

localhost is a synonym for your local host name, and is also the default host to which clients try to connect if you specify no host explicitly.

To avoid this problem on such systems, you can use a --host=127.0.0.1 option to name the server host explicitly. This will make a TCP/IP connection to the local mysqld server. You can also use TCP/IP by specifying a --host option that uses the actual host name of the local host. In this case, the host name must be specified in a user table row on the server host, even though you are running the client program on the same host as the server.
*

The Access denied error message tells you who you are trying to log in as, the client host from which you are trying to connect, and whether you were using a password. Normally, you should have one row in the user table that exactly matches the host name and user name that were given in the error message. For example, if you get an error message that contains using password: NO, it means that you tried to log in without a password.
*

If you get an Access denied error when trying to connect to the database with mysql -u user_name, you may have a problem with the user table. Check this by executing mysql -u root mysql and issuing this SQL statement:

SELECT * FROM user;

The result should include a row with the Host and User columns matching your client's host name and your MySQL user name.
*

If the following error occurs when you try to connect from a host other than the one on which the MySQL server is running, it means that there is no row in the user table with a Host value that matches the client host:

Host ... is not allowed to connect to this MySQL server

You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.

If you do not know the IP number or host name of the machine from which you are connecting, you should put a row with '%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. Then change the '%' in the user table row to the actual host name that shows up in the log. Otherwise, your system is left insecure because it allows connections from any host for the given user name.

On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc, or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.
*

If you specify a host name when trying to connect, but get an error message where the host name is not shown or is an IP number, it means that the MySQL server got an error when trying to resolve the IP number of the client host to a name:

shell> mysqladmin -u root -pxxxx -h some_hostname ver
Access denied for user 'root'@'' (using password: YES)

If you try to connect as root and get the following error, it means that you do not have a row in the user table with a User column value of 'root' and that mysqld cannot resolve the host name for your client:

Access denied for user ''@'unknown'

These errors indicate a DNS problem. To fix it, execute mysqladmin flush-hosts to reset the internal DNS host name cache. See Section 7.5.11, “How MySQL Uses DNS”.

Some permanent solutions are:
o

Determine what is wrong with your DNS server and fix it.
o

Specify IP numbers rather than host names in the MySQL grant tables.
o

Put an entry for the client machine name in /etc/hosts on Unix or \windows\hosts on Windows.
o

Start mysqld with the --skip-name-resolve option.
o

Start mysqld with the --skip-host-cache option.
o

On Unix, if you are running the server and the client on the same machine, connect to localhost. Unix connections to localhost use a Unix socket file rather than TCP/IP.
o

On Windows, if you are running the server and the client on the same machine and the server supports named pipe connections, connect to the host name . (period). Connections to . use a named pipe rather than TCP/IP.
*

If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied (where your_hostname is the actual host name of the local host), you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table row specifies an unqualified host name, but your system's name resolution routines return a fully qualified domain name (or vice versa). For example, if you have an entry with host 'pluto' in the user table, but your DNS tells MySQL that your host name is 'pluto.example.com', the entry does not work. Try adding an entry to the user table that contains the IP number of your host as the Host column value. (Alternatively, you could add an entry to the user table with a Host value that contains a wildcard; for example, 'pluto.%'. However, use of Host values ending with “%” is insecure and is not recommended!)
*

If mysql -u user_name test works but mysql -u user_name other_db does not, you have not granted access to the given user for the database named other_db.
*

If mysql -u user_name works when executed on the server host, but mysql -h host_name -u user_name does not work when executed on a remote client host, you have not enabled access to the server for the given user name from the remote host.
*

If you cannot figure out why you get Access denied, remove from the user table all entries that have Host values containing wildcards (entries that contain '%' or '_' characters). A very common error is to insert a new entry with Host='%' and User='some_user', thinking that this allows you to specify localhost to connect from the same machine. The reason that this does not work is that the default privileges include an entry with Host='localhost' and User=''. Because that entry has a Host value 'localhost' that is more specific than '%', it is used in preference to the new entry when connecting from localhost! The correct procedure is to insert a second entry with Host='localhost' and User='some_user', or to delete the entry with Host='localhost' and User=''. After deleting the entry, remember to issue a FLUSH PRIVILEGES statement to reload the grant tables. See also Section 5.4.4, “Access Control, Stage 1: Connection Verification”.
*

If you are able to connect to the MySQL server, but get an Access denied message whenever you issue a SELECT ... INTO OUTFILE or LOAD DATA INFILE statement, your entry in the user table does not have the FILE privilege enabled.
*

If you change the grant tables directly (for example, by using INSERT, UPDATE, or DELETE statements) and your changes seem to be ignored, remember that you must execute a FLUSH PRIVILEGES statement or a mysqladmin flush-privileges command to cause the server to reload the privilege tables. Otherwise, your changes have no effect until the next time the server is restarted. Remember that after you change the root password with an UPDATE command, you will not need to specify the new password until after you flush the privileges, because the server will not know you've changed the password yet!
*

If your privileges seem to have changed in the middle of a session, it may be that a MySQL administrator has changed them. Reloading the grant tables affects new client connections, but it also affects existing connections as indicated in Section 5.4.6, “When Privilege Changes Take Effect”.
*

If you have access problems with a Perl, PHP, Python, or ODBC program, try to connect to the server with mysql -u user_name db_name or mysql -u user_name -pyour_pass db_name. If you are able to connect using the mysql client, the problem lies with your program, not with the access privileges. (There is no space between -p and the password; you can also use the --password=your_pass syntax to specify the password. If you use the -p or --password option with no password value, MySQL prompts you for the password.)
*

For testing purposes, start the mysqld server with the --skip-grant-tables option. Then you can change the MySQL grant tables and use the mysqlaccess script to check whether your modifications have the desired effect. When you are satisfied with your changes, execute mysqladmin flush-privileges to tell the mysqld server to reload the privileges. This enables you to begin using the new grant table contents without stopping and restarting the server.
*

If you get the following error, you may have a problem with the db or host table:

Access to database denied

If the entry selected from the db table has an empty value in the Host column, make sure that there are one or more corresponding entries in the host table specifying which hosts the db table entry applies to. This problem occurs infrequently because the host table is rarely used.
*

If everything else fails, start the mysqld server with a debugging option (for example, --debug=d,general,query). This prints host and user information about attempted connections, as well as information about each command issued. See MySQL Internals: Porting.
*

If you have any other problems with the MySQL grant tables and feel you must post the problem to the mailing list, always provide a dump of the MySQL grant tables. You can dump the tables with the mysqldump mysql command. To file a bug report, see the instructions at Section 1.6, “How to Report Bugs or Problems”. In some cases, you may need to restart mysqld with --skip-grant-tables to run mysqldump.



Reference: http://dev.mysql.com/doc/refman/5.1/en/access-denied.html

Warning: MySQL server has gone away

SkyHi @ Tuesday, September 29, 2009
If you get the following error message:

*Warning*: MySQL server has gone away

that means your Php or Perl /cgi powered application has not passed a query for a while. The connection to MySQL times out. To start up a new and simple query session, select a table within the database of that application via PhpMyAdmin.

If that does not help, you need to tweak MySQL conf file. Using your favorite Linux text editor such as: vi or pico,

* vi /etc/my.cnf

Set the following values for these two variables:

connect_timeout=120
wait_timeout=1800

If these values do not help, you can increase them.

Then restart MySQL. This command will restart MySQL daemon on most generic Linux servers:

* /sbin/service mysqld restart

If you do not have a my.cnf file, create a new one. The local path on most generic Linux servers is: /etc/my.cnf

To view MySQL variables and their current values, run the following command at the prompt:

* /usr/bin/mysql -uroot -pYOUR_ROOT_PASSWD

Then, run the following command (one single line) at MySQL prompt:

* show global variables like '%timeout%';

Which will show a result like this:

+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+-------------------------------+-------+
9 rows in set (0.00 sec)

As mentioned earlier, you can change the values of the connect_timeout and net_write_timeout. The higher the values are, the longer MySQL stays active and can process longer queries.

For more information about this error, go to: MySQL 5.0 Reference Manual :: B.1.2.9 MySQL

Reference: http://servertune.com/kbase/entry/366/

How can I find out if my IP Address(s) have been blocked by an anti spam company?

SkyHi @ Tuesday, September 29, 2009
You can use any of the following services to see if your IP address (s) have been blocked,

* The Abusive Hosts Blocking List
http://www.ahbl.org/lktool
* SpamCop Blocking List
http://www.spamcop.net/bl.shtml
* Spamhaus Block List (SBL)
http://www.spamhaus.org/sbl/
* Composite Blocking List (CBL)
http://cbl.abuseat.org/
* Weighted Private Block List (WPBL)
http://www.wpbl.info/

Reference: http://servertune.com/kbase/entry/358/



I am wondering if my server is being used to send out or relay spam. How can I check?

You can test your Mail server to see if it's running an open relay by using Netowrk ABUSE clearinghouse system. Point your browser at: http://www.abuse.net/relay.html


My Mail server was reported as an open relay. Is this true?
Pertaining to the Exim mail server for the cPanel/WHM and DirectAdmin

By default, the Exim mail server shipped with the cPanel/WHM and DirectAdmin is NOT open relay.

Now, if you are using a default address to "catchall" all the invalid and un-routed email messages to your domain, some of the "open relay" testing services will report that your domain is an open relay. But this is NOT true and your Mail server is NOT an open relay. The "open relay" report of these services is flawed in that context.

We highly recommend that you DISABLE the default address and do NOT use catchall.

If you are not sure that your server is secure, we suggest you sign up for the ServerTune Plan.

Tips to avoid getting your server blocked by Hotmail/Yahoo/Gmail

SkyHi @ Tuesday, September 29, 2009
---------- Symptom ----------

* Clients send messages from your server , but do not either show up, or go to the "SPAM box", in the Internet Service Provider (ISP): MSN/hotmail/AOL/Yahoo inbox
* There are no error messages in the log file.
* Your main server IP address and/or hostname is not blocked or blacklisted on any of the popular DNSBLs.





If you believe that the feature "Sender verify callout", is causing problems, read on to find out more. Click here to read about "Send verify callout" in this Knowledgebase.

If you're experiencing problems sending email messages from your server to any of the ISP including: Hotmail / Yahoo / AOL, Gmail, Comcast we suggest the following:

1. Assign an IP address to every client sending out large number of email messages. Yahoo! Mail, for example, keeps reputation data about each IP address sending out messages to their system. By doing that, you make it easier for Yahoo! Mail to determine the IP address' reputation.

ISPs look at the reputation of the domain and the reputation of the IP address used to send email messages from.

2. Manage your and/or your client's lists by paying attention to bounces and rejections. Clean lists get higher-priority delivery than others.

3. Don't act and/or any of your clients act like a phisher:
* Don't use an IP address as part of your email address. Use a valid domain name hosted on your server.
* Don't include HTML forms in email messages. Submitting and/or including forms in email messages is considered security threat for many ISPs.
* Don't include JavaScript code in email messages.
* Don't include embedded objects in email messages such as: Flash or Active-X.
* Don't re-send rejected messages. If a message gets rejected, it will not be accepted anytime soon. Re-sending rejected messages is a waste of resources and makes you appear to have a dirty list.
4. Use standard POP and SMTP Mail Ports, only.

5. Use a consistent From: header address. Your domain name is an important element of your brand. Using it consistently helps in distinguishing email sent out from your server from SPAM.

6. Be CAN-SPAM compliant.

7. Make sure all domains sending mail via your server have an appropriate email authentication such as SPF record ( http://spf.pobox.com) and DomainKeys.
Add SPF entry in your DNS zones using the following:
""v=spf1 a mx ip4:SERVER_MAIN_IP_ADDRESS -all" In some situations, some of your customers might have dedicated IPs for their sites and their MX and A records point to those IPs. Those IP address' are NOT the main IP address' for the server. By default, on a cPanel and/or a DirectAdmin powered server(s), the main IP address is the one which sends out mail. So if the main IP of the server is 60.70.80.90, then you need the following as a minimal SPF record for all domains on that server:
"v=spf1 a mx ip4:60.70.80.90 -all" MSN/Hotmail recommends using -all terminators in the SPF record. Click here to read about HowTo create a SPF record for a domain.

8. Make sure the IP address that your server sends mail from (which would be the main IP address), has:
1. Valid reverse DNS (rDNS); and
2. That rDNS is reflective of your hostname, rather than some generic record that your upstream IP provider has put in place.

9. Run the following two commands to see if your hostname is associated with the main IP of your server:
nslookup your.hostname
and
nslookup YOUR_SERVER_MAIN_IP
(where YOUR_SERVER_MAIN_IP is the main IP address of your server). The system should return your_hostname

10. Go to: http://www.openrbl.org and make sure your main server IP address and/or hostname is not blocked or blacklisted on any of the popular DNSBLs. We also suggest you read this article "How can I find out if my IP Address(s) have been blocked by an anti spam company?"
Although your main IP or hostname might not be listed there, it doesn't mean that it is not blacklisted or negatively scored by AOL or Yahoo or Hotmail or www.rr.com. It is likely that AOL or Yahoo or Hotmail or www.rr.com negatively scored your server due to past traffic from that IP address before your server was using it.

11. Disable the feature Autoresponder on your server. Click here to read why are Autoresponders bad?

12. Make sure your users aren't forwarding all of their email, which might include spam, from their accounts hosted on your server to an @aol.com or @yahoo.com or @hotmail.com email address. That will surely cause future emails from your server's IP address to be considered un-safe or spam by those entities.

Stopping customers from forwarding their email is very difficult. Since it is not possible to guarantee that all email messages forwarded from your server to AOL / Hotmail / Yahoo are not spam or don't contain spam, you need to let your customers know that their email might be rejected by those entities. As a host , you need to enable SpamAssassin, or any good spam filter, on all accounts hosted on your server. Make sure spam rules are up-to-date all the time.

13. Secure your mail servers. Ensure your mail servers are not open proxies or relays. Keep your Mail server up-to-date with the latest security patches to help prevent spammers from using your resources and tarnishing your reputation.

14. Secure and harden your Web servers.

15. Make sure all scripts either Php and/or Cgi/Perl such as: formmail/cgimail are secure and can not be used by spammers to send out email.

Even after you've followed all of the above instructions, you and your users may find that their email does not arrive, or ends up in the Spam folders of Hotmail, Yahoo, AOL, and so on. In this case, read and follow the instructions of the ISP:

* AOL
o Conditions To Bulk Sender Status
o Sender Best Practices
o Whitelist Information
* Comcast
o Mail to Comcast is rejected and is returned with an error message containing the code BL004. What does this mean?
* Gmail
o Bulk Senders Guidelines
o Bulk Sender Contact Form
* Hotmail
o Microsoft Guidelines
o MSN/Hotmail Technical Support
* Yahoo
o What are some best practices when sending to Yahoo! Mail?
o My email is being blocked by Yahoo! Mail. What can I do?
o Why was my SMTP connection rejected?

NOTE
We'll be more than happy to apply the best possible spam rules, secure, harden, and optimize your Mail server. Please contact us at: customerservice@servertune.com


Reference: http://servertune.com/kbase/entry/270/

Tips to solve email problem(s)

SkyHi @ Tuesday, September 29, 2009
For end-users:

* Check with your registrar and make sure that the domain name hasn't expired.
* Make sure that your email inbox and/or domain didn't exceed its quota.
* Use your full email address Code by which the Internet identifies you. The format is username@hostname, where username is your username, login name, or account number, and hostname is the name of the computer or Internet provider you use. The hostname may be a few words strung together with periods. as username (i.e., username@domain.com).
* Switch to a different port such as: 26 or 1000 for SMTP Outgoing Server .
* Login to your control panel and remove any mail filter you have.

If you can't send and/or receive using MS Outlook/Outlook Express do the following:

1. Make sure that "My Server Requires Authentication" is checked in your Outlook/Outlook Express.
2. Make sure your Anti-Virus and/or Windows firewall are not blocking you from sending/receiving email
3. Go to your DOS prompt and issue the following command: ipconfig /flushdns
4. Run Windows Disk Defragmenter and wait until it is completed.
5. Power off, wait for a few seconds, and then power on your PC.
6. Turn off any firewall you might have on your own PC, including Norton and McAfee.

For System Administrators:

* SSH to your server and check Exim main log file for errors that might help in solving the problem:
For the cPanel/WHM
tail -f /var/log/exim_mainlog
For DirectAdmin
tail -f /var/log/exim/mainlog

* Go to either www.dnsreport.com or www.intodns.com and get a DNS report about the domain with the problem. Look carefully for any errors in the report and correct them.
* Make sure your server resolves, and the entries in these two files are correct: /stc/resolv.conf and /etc/hosts
* Make sure the domain name in question is not due for the annual domain name registration
* Ask your host to set rDNS PTR Records on your main IP address
* Make sure client's DNS zone and nameservers are correct
* Make sure that the email filter and/or SpamAssassin are configured and working properly
* Remove any mail filter created by the client.
* Verify that the email and/or the domain exist where they should exist
For a cPanel/WHM powered server
/etc/localdomains
* Make sure /etc/exim.pl exists

For the cPanel/WHM v11.x
Login to the WHM >> Main >> Service Configuration >> Exim Configuration Editor
Scroll down the page and click on the button "Reset All Configs to Default" then SSH to the server and run these commands at the prompt:

/scripts/buildeximconf
/scripts/mailperm
/scripts/eximup --force

Reference: http://servertune.com/kbase/entry/34/

MS Outlook/Express Error Codes

SkyHi @ Tuesday, September 29, 2009
The following chart contains a list of possible error codes that may be returned by the Microsoft mail clients listed at the beginning of this article. This list of error codes may be returned when a failure occurs sending and receiving mail.

Click here to read about the differences between MS Outlook and MS Outlook Express.


MS Outlook/Express Error Codes
Error Code Error Type
0x800CCC00 LOAD_SICILY_FAILED Authentication did not load.
0x800CCC01 INVALID_CERT_CN Invalid certificate content
0x800CCC02 INVALID_CERT_DATE Invalid certificate date.
0x800CCC03 ALREADY_CONNECTED User already connected.
0x800CCC04 CONN
0x800CCC05 NOT_CONNECTED Not connected to server.
0x800CCC06 CONN_SEND
0x800CCC07 WOULD_BLOCK
0x800CCC08 INVALID_STATE
0x800CCC09 CONN_RECV
0x800CCC0A INCOMPLETE Message download incomplete.
0x800CCC0B BUSY Server or maildrop is busy.
0x800CCC0C NOT_INIT
0x800CCC0D CANT_FIND_HOST Cannot locate server.** Server is down**
0x800CCC0E FAILED_TO_CONNECT Cannot connect to server.
0x800CCC0F CONNECTION_DROPPED Connection closed.
0x800CCC10 INVALID_ADDRESS Address not known on server.
0x800CCC11 INVALID_ADDRESS_LIST Mailing list not known on server.
0x800CCC12 SOCKET_READ_ERROR Unable to send Winsock request.
0x800CCC13 SOCKET_WRITE_ERROR Unable to read Winsock reply.
0x800CCC14 SOCKET_INIT_ERROR Unable to initialize Winsock.
0x800CCC15 SOCKET_CONNECT_ERROR Unable to open Windows Socket.
0x800CCC16 INVALID_ACCOUNT User account not recognized.
0x800CCC17 USER_CANCEL User canceled operation.
0x800CCC18 SICILY_LOGON_FAILED Logon attempt failed.
0x800CCC19 TIMEOUT
0x800CCC1A SECURE_CONNECT_FAILED Unable to connect using SSL.
0x800CCC40 WINSOCK_WSASYSNOTREADY Network subsystem is unusable.
0x800CCC41 WINSOCK_WSAVERNOTSUPPORTED Windows Sockets cannot support this application.
0x800CCC42 WINSOCK_WSAEPROCLIM
0x800CCC43 WINSOCK_WSAEFAULT Bad address.
0x800CCC44 WINSOCK_FAILED_WSASTARTUP Unable to load Windows Sockets.
0x800CCC45 WINSOCK_WSAEINPROGRESS Operation now in progress. This error appears if a Windows Sockets API is called while a blocking function is in progress.
0x800CCC60 SMTP_RESPONSE_ERROR Invalid response.
0x800CCC61 SMTP_UNKNOWN_RESPONSE_CODE Unknown error code.
0x800CCC62 SMTP_500_SYNTAX_ERROR Syntax error returned.
0x800CCC63 SMTP_501_PARAM_SYNTAX Parameter syntax incorrect.
0x800CCC64 SMTP_502_COMMAND_NOTIMPL Command not implemented.
0x800CCC65 SMTP_503_COMMAND_SEQ Improper command sequence.
0x800CCC66 SMTP_504_COMMAND_PARAM_NOTIMPL Command not implemented.
0x800CCC67 SMTP_421_NOT_AVAILABLE Command not available.
0x800CCC68 SMTP_450_MAILBOX_BUSY Mailbox is locked and busy.
0x800CCC69 SMTP_550_MAILBOX_NOT_FOUND Mailbox not found.
0x800CCC6A SMTP_451_ERROR_PROCESSING Error processing request.
0x800CCC6B SMTP_551_USER_NOT_LOCAL User mailbox is known, but mailbox not on this server.
0x800CCC6C SMTP_452_NO_SYSTEM_STORAGE No space to store messages.
0x800CCC6D SMTP_552_STORAGE_OVERFLOW Storage limit exceeded.
0x800CCC6E SMTP_553_MAILBOX_NAME_SYNTAX Invalid mailbox name syntax.
0x800CCC6F SMTP_554_TRANSACT_FAILED Transaction failed.
0x800CCC78 SMTP_REJECTED_SENDER This is caused by having the incorrect e-mail address in the Reply-To field.
0x800CCC79 SMTP_REJECTED_RECIPIENTS Server rejected recipients.
0x800CCC7A SMTP_NO_SENDER No sender address specified.
0x800CCC7B SMTP_NO_RECIPIENTS No recipients specified.
0x800420CB POP3_NO_STORE Mail cannot be stored on server
0x800CCC90 POP3_RESPONSE_ERROR Client response invalid.
0x800CCC91 POP3_INVALID_USER_NAME Invalid user name or user not found.
0x800CCC92 POP3_INVALID_PASSWORD Password not valid for account.
0x800CCC93 POP3_PARSE_FAILURE Unable to interpret response.
0x800CCC94 POP3_NEED_STAT STAT Command required.
0x800CCC95 POP3_NO_MESSAGES No messages on server.
0x800CCC96 POP3_NO_MARKED_MESSAGES No messages marked for retrieval.
0x800CCC97 POP3_POPID_OUT_OF_RANGE Message ID out of range.
0x800CCCA0 NNTP_RESPONSE_ERROR News server response error.
0x800CCCA1 NNTP_NEWGROUPS_FAILED Newsgroup access failed.
0x800CCCA2 NNTP_LIST_FAILED LIST command to server failed.
0x800CCCA3 NNTP_LISTGROUP_FAILED Unable to display list.
0x800CCCA4 NNTP_GROUP_FAILED Unable to open group.
0x800CCCA5 NNTP_GROUP_NOTFOUND Group not on server.
0x800CCCA6 NNTP_ARTICLE_FAILED Message not on server.
0x800CCCA7 NNTP_HEAD_FAILED Message header not found.
0x800CCCA8 NNTP_BODY_FAILED Message body not found.
0x800CCCA9 NNTP_POST_FAILED Unable to post to server.
0x800CCCAA NNTP_NEXT_FAILED Unable to open next message.
0x800CCCAB NNTP_DATE_FAILED Unable to display date.
0x800CCCAC NNTP_HEADERS_FAILED Unable to display headers.
0x800CCCAD NNTP_XHDR_FAILED Unable to display MIME headers.
0x800CCCAE NNTP_INVALID_USERPASS Invalid user or password.
0x800CCCC2 RAS_NOT_INSTALLED RAS/DUN not installed.
0x800CCCC3 RAS_PROCS_NOT_FOUND RAS/DUN process not found.
0x800CCCC4 RAS_ERROR RAS/DUN error returned.
0x800CCCC5 RAS_INVALID_CONNECTOID Connectoid damaged or missing.
0x800CCCC6 RAS_GET_DIAL_PARAMS Error getting dial settings
0x800CCCD1 IMAP_LOGINFAILURE Login failed.
0x800CCCD2 IMAP_TAGGED_NO_RESPONSE Message tagged.
0x800CCCD3 IMAP_BAD_RESPONSE Invalid response to request.
0x800CCCD4 IMAP_SVR_SYNTAXERR Syntax error.
0x800CCCD5 IMAP_NOTIMAPSERVER Not an IMAP server.
0x800CCCD6 IMAP_BUFFER_OVERFLOW Buffer limit exceeded.
0x800CCCD7 IMAP_RECVR_ERROR Recovery error.
0x800CCCD8 IMAP_INCOMPLETE_LINE Incomplete data.
0x800CCCD9 IMAP_CONNECTION_REFUSED Connection not allowed.
0x800CCCDA IMAP_UNRECOGNIZED_RESP Unknown response.
0x800CCCDB IMAP_CHANGEDUID User ID has changed.
0x800CCCDC IMAP_UIDORDER User ID command failed.
0x800CCCDD IMAP_UNSOLICITED_BYE Unexpected disconnect.
0x800CCCDE IMAP_IMPROPER_SVRSTATE Invalid server state.
0x800CCCDF IMAP_AUTH_NOT_POSSIBLE Unable to authorize client.
0x800CCCE0 IMAP_OUT_OF_AUTH_METHODS No more authorization types.
0x80042108 OL: Error Message: 0x80042108 Outlook Is Unable to Connect to Your Incoming POP3 E-mail Server


Reference: http://servertune.com/kbase/entry/42/

How can I block IP addresses?

SkyHi @ Tuesday, September 29, 2009
[home]$ /bin/netstat -ntu | awk '{print $5}' | cut -d: -f1 | sort |uniq -c | sort -n |more
1 142.32.208.232
1 142.58.185.157
1 154.5.60.241
1 164.77.228.99
1 192.28.0.22
1 192.168.100.235
1 208.80.193.29
1 213.250.36.94
1 66.249.67.34
1 72.30.142.170
1 98.188.147.225
1 Address
1 servers)
2 41.4.138.112
2 67.195.115.54
3 192.168.100.236
3 209.216.175.95
3 213.39.187.103
4 192.168.100.18
6 192.168.100.16
6 74.13.129.145
6 79.65.242.231
8 199.60.221.132
13 65.2.116.47
31 192.168.100.46


Block un-wanted IPs using IPTables

/sbin/iptables -I INPUT -s 12.34.56.78 -j DROP
/sbin/service iptables save
/sbin/service iptables restart

How To Hide BIND DNS Sever Version

SkyHi @ Tuesday, September 29, 2009
Q. How do I hide my dns server version number from command such as:
dig @ns1.example.com -c CH -t txt version.bind

How do I hide version under BIND9 Linux / UNIX systems?

A. This is nothing but security through obscurity. You can hide version but one can always fingerprint your name server to find out exact version details using fpdns tool.

Open your named.conf file, find out options { ... }; section,

options
{
query-source port 53;
query-source-v6 port 53;
listen-on { 174.ttt.xx.yy; };
directory "/var/named"; // the default
dump-file "data/cache_dump.db";
statistics-file "data/named_stats.txt";
memstatistics-file "data/named_mem_stats.txt";
dnssec-enable yes;
recursion no;
allow-notify { 174.zzz.yy.zz; 172.xx.yy.zz; };
version "BIND";
};

To hide your bind version:
version "YOUR Message";
OR
version "use fpdns to get version number ;)";
Save and close the file. Restart named, enter:
# service bind9 restart
OR
# service named restart
How do I see bind version?

Use dig command, enter
$ dig @ns1.softlayer.com -c CH -t txt version.bind
As usual, you can use fpdns to find out version number.


Reference: http://www.cyberciti.biz/faq/hide-bind9-dns-sever-version/

Bind Error: lame server resolving

SkyHi @ Tuesday, September 29, 2009
I see this error message in my log file:

Dec 12 11:30:21 server1 named[xxxxx]: lame server resolving '62.114.34.74.in-addr.arpa' (in '114.34.74.in-addr.arpa'?)

What does that mean and how can I get rid of it?

Lame server is one that is advertised to have authoritative information about a domain, but doesn't. This can happen in a few different ways, but it's usually not worth the trouble to track down the administrator of the other domain. TO solve this issue, add this directive to /etc/named.conf in the "options" section:

version "not currently available";
allow-recursion { trusted; };
allow-notify { trusted; };
allow-transfer { trusted; };

This will allow you to run your own zones as a master, and stop anyone but your server from using your DNS to look up hosts you aren't authoratative for (recursive lookups). That should eliminate the possibility that someone else's queries causing this issue.

If you wish to clean up the logs, add this to /etc/named.conf, below the options section:

logging {
category lame-servers { null; };


Reference: http://servertune.com/kbase/entry/227/

Monday, September 28, 2009

How to verify whether DNS is working correctly ?

SkyHi @ Monday, September 28, 2009
After you have setup your DNS Server, it's very important to check that the entries which are populated to the Internet are correct. You can use the following checklist using nslookup.

Start nslookup for the desired DNS Server

nslookup
> server 193.247.121.196
Default Server: rabbit.akadia.ch
Address: 193.247.121.196

Check Start of Authority (SOA)

> set q=SOA
> akadia.com
Server: rabbit.akadia.ch
Address: 193.247.121.196

akadia.com
origin = rabbit.akadia.com
mail addr = postmaster.akadia.com
serial = 2000061501
refresh = 10800 (3H)
retry = 3600 (1H)
expire = 604800 (1W)
minimum ttl = 86400 (1D)
akadia.com nameserver = rabbit.akadia.com
akadia.com nameserver = lila.thenet.ch
rabbit.akadia.com internet address = 193.247.121.196
lila.thenet.ch internet address = 193.135.252.2

Check the Nameservers (NS)

> set q=NS
> akadia.com
Server: rabbit.akadia.ch
Address: 193.247.121.196

akadia.com nameserver = lila.thenet.ch
akadia.com nameserver = rabbit.akadia.com
lila.thenet.ch internet address = 193.135.252.2
rabbit.akadia.com internet address = 193.247.121.196

Check E-Mail MX-Records (MX)

> set q=MX
> akadia.com
Server: rabbit.akadia.ch
Address: 193.247.121.196

akadia.com preference = 20, mail exchanger = opal.akadia.com
akadia.com preference = 10, mail exchanger = rabbit.akadia.com
akadia.com nameserver = rabbit.akadia.com
akadia.com nameserver = lila.thenet.ch
opal.akadia.com internet address = 193.247.121.197
rabbit.akadia.com internet address = 193.247.121.196
lila.thenet.ch internet address = 193.135.252.2

Check everything (ANY)

> set q=any
> akadia.com
Server: rabbit.akadia.ch
Address: 193.247.121.196

akadia.com preference = 10, mail exchanger = rabbit.akadia.com
akadia.com preference = 20, mail exchanger = opal.akadia.com
akadia.com nameserver = rabbit.akadia.com
akadia.com nameserver = lila.thenet.ch
akadia.com
origin = rabbit.akadia.com
mail addr = postmaster.akadia.com
serial = 2000061501
refresh = 10800 (3H)
retry = 3600 (1H)
expire = 604800 (1W)
minimum ttl = 86400 (1D)
akadia.com nameserver = rabbit.akadia.com
akadia.com nameserver = lila.thenet.ch
rabbit.akadia.com internet address = 193.247.121.196
opal.akadia.com internet address = 193.247.121.197
lila.thenet.ch internet address = 193.135.252.2

Lookup all hosts within a domain

> ls -d akadia.com


Reference: http://www.akadia.com/services/check_dns.html



$dig @localhost example.com
$dig@localhost MX example.com
$dig @localhost ANY example.com

bash prompt

SkyHi @ Monday, September 28, 2009
.bashrc
echo 'ALERT - Root Shell Access (NS2) on:' `date` `who` | mail -s "Alert: Root Access from NS2 `who | cut -d"(" -f2 | cut -d")" -f1`" sysadmin@example.com

What is SPF and how to prevent spoofing by setup SPF record?

SkyHi @ Monday, September 28, 2009
SPF record tells the mail receiving server information about validity of sending servers. It specific which mail servers will send out email on behalf of your domain.

To setup or modify SPF, login Control Panel -> DNS -> chooose your domain and click edit Zone.

Here is some sample SPF:

allows domain's MX recordes to send mail for this domain, prohibit all others:
"v=spf1 mx ptr -all"

allows only the specified ips to send mail for the domain, prohibit all others:
"v=spf1 ip4:216.10.244.151 ip4:66.226.27.224 ip4:216.10.240.224 mx ptr -all"


You can find a SPF generate Wizard and details of SPF here:
http://www.openspf.org/



Reference: http://www.webhost4life.com/hostingKB/KnowledgebaseArticle50221.aspx




Set SPF records
Print
Sender Policy Framework (SPF) records allow domain owners to specify which hosts are permitted to send email on behalf of their domains. Normal SMTP allows any computer to send an email claiming to be from anyone. Thus, it's easy for spammers to send emails with forged From: addresses. SPF allows a domain owner to use a special format of DNS TXT records to specify which machines or hosts are authorized to transmit email for their domain; this makes it difficult to forge From: addresses.

For example, if you own the domain example.com, you can designate which hosts are authorized to send email originating from user@example.com. Your recipient's servers will then identify the origin of your message by checking the SPF record.

If you're using Google Apps services, we strongly encourage you to publish SPF records for your domain. Having these records in place will ensure that messages sent from users in your domain are not rejected by the recipient's domain.

To set your domain's SPF record, you should have access to your domain's DNS settings. On your DNS resource, publish the following TXT record: v=spf1 include:aspmx.googlemail.com ~all

Note: Publishing an SPF record that lacks include:aspmx.googlemail.com or specifying -all instead of ~all may result in delivery problems.

For more information on SPF records, please contact your domain host.

If you choose to activate the Postini features in Google Apps Premier Edition and configure Google Apps to route email to the internet via Postini's servers, we suggest that you use this configuration: yourdomain.com. IN TXT "v=spf1 ip4:207.126.144.0/20 ip4:64.18.0.0/20 ip4:74.125.148.0/22 include:_spf.google.com ~all"

Reference: http://www.google.com/support/a/bin/answer.py?answer=33786


SPF Record Syntax
Note: This page serves as an introduction and quick overview of SPF mechanism syntax. For the complete and definitive picture, please see the specification.

Domains define zero or more mechanisms. Mechanisms can be used to describe the set of hosts which are designated outbound mailers for the domain.

all | ip4 | ip6 | a | mx | ptr | exists | include

Domains may also define modifiers. Each modifier can appear only once.

redirect | exp
Mechanisms

Mechanisms can be prefixed with one of four qualifiers:
"+" Pass
"-" Fail
"~" SoftFail
"?" Neutral

If a mechanism results in a hit, its qualifier value is used. The default qualifier is "+", i.e. "Pass". For example:
"v=spf1 -all"
"v=spf1 a -all"
"v=spf1 a mx -all"
"v=spf1 +a +mx -all"

Mechanisms are evaluated in order. If no mechanism or modifier matches, the default result is "Neutral".

If a domain has no SPF record at all, the result is "None". If a domain has a temporary error during DNS processing, you get the result "TempError" (called "error" in earlier drafts). If some kind of syntax or evaluation error occurs (eg. the domain specifies an unrecognized mechanism) the result is "PermError" (formerly "unknown").

Evaluation of an SPF record can return any of these results:
Result Explanation Intended action
Pass The SPF record designates the host to be allowed to send accept
Fail The SPF record has designated the host as NOT being allowed to send reject
SoftFail The SPF record has designated the host as NOT being allowed to send but is in transition accept but mark
Neutral The SPF record specifies explicitly that nothing can be said about validity accept
None The domain does not have an SPF record or the SPF record does not evaluate to a result accept
PermError A permanent error has occured (eg. badly formatted SPF record) unspecified
TempError A transient error has occured accept or reject

The "all" mechanism (edit)

all

This mechanism always matches. It usually goes at the end of the SPF record.

Examples:

"v=spf1 mx -all"
Allow domain's MXes to send mail for the domain, prohibit all others.

"v=spf1 -all"
The domain sends no mail at all.

"v=spf1 +all"
The domain owner thinks that SPF is useless and/or doesn't care.

The "ip4" mechanism (edit)

ip4:
ip4:/

The argument to the "ip4:" mechanism is an IPv4 network range. If no prefix-length is given, /32 is assumed (singling out an individual host address).

Examples:

"v=spf1 ip4:192.168.0.1/16 -all"
Allow any IP address between 192.168.0.1 and 192.168.255.255.

The "ip6" mechanism (edit)

ip6:
ip6:/

The argument to the "ip6:" mechanism is an IPv6 network range. If no prefix-length is given, /128 is assumed (singling out an individual host address).

Examples:

"v=spf1 ip6:1080::8:800:200C:417A/96 -all"
Allow any IPv6 address between 1080::8:800:0000:0000 and 1080::8:800:FFFF:FFFF.

"v=spf1 ip6:1080::8:800:68.0.3.1/96 -all"
Allow any IPv6 address between 1080::8:800:0000:0000 and 1080::8:800:FFFF:FFFF.

The "a" mechanism (edit)

a
a/
a:
a:/

All the A records for domain are tested. If the client IP is found among them, this mechanism matches.

If domain is not specified, the current-domain is used.

The A records have to match the client IP exactly, unless a prefix-length is provided, in which case each IP address returned by the A lookup will be expanded to its corresponding CIDR prefix, and the client IP will be sought within that subnet.

"v=spf1 a -all"
The current-domain is used.

"v=spf1 a:example.com -all"
Equivalent if the current-domain is example.com.

"v=spf1 a:mailers.example.com -all"
Perhaps example.com has chosen to explicitly list all the outbound mailers in a special A record under mailers.example.com.

"v=spf1 a/24 a:offsite.example.com/24 -all"
If example.com resolves to 192.0.2.1, the entire class C of 192.0.2.0/24 would be searched for the client IP. Similarly for offsite.example.com. If more than one A record were returned, each one would be expanded to a CIDR subnet.

The "mx" mechanism (edit)

mx
mx/
mx:
mx:/

All the A records for all the MX records for domain are tested in order of MX priority. If the client IP is found among them, this mechanism matches.

If domain is not specified, the current-domain is used.

The A records have to match the client IP exactly, unless a prefix-length is provided, in which case each IP address returned by the A lookup will be expanded to its corresponding CIDR prefix, and the client IP will be sought within that subnet.

Examples:

"v=spf1 mx mx:deferrals.domain.com -all"
Perhaps a domain sends mail through its MX servers plus another set of servers whose job is to retry mail for deferring domains.

"v=spf1 mx/24 mx:offsite.domain.com/24 -all"
Perhaps a domain's MX servers receive mail on one IP address, but send mail on a different but nearby IP address.

The "ptr" mechanism (edit)

ptr
ptr:

The hostname or hostnames for the client IP are looked up using PTR queries. The hostnames are then validated: at least one of the A records for a PTR hostname must match the original client IP. Invalid hostnames are discarded. If a valid hostname ends in domain, this mechanism matches.

If domain is not specified, the current-domain is used.

If at all possible, you should avoid using this mechanism in your SPF record, because it will result in a larger number of expensive DNS lookups.

Examples:

"v=spf1 ptr -all"
A domain which directly controls all its machines (unlike a dialup or broadband ISP) allows all its servers to send mail. For example, hotmail.com or paypal.com might do this.

"v=spf1 ptr:otherdomain.com -all"
Any server whose hostname ends in otherdomain.com is designated.

The "exists" mechanism (edit)

exists:

Perform an A query on the provided domain. If a result is found, this constitutes a match. It doesn't matter what the lookup result is – it could be 127.0.0.2.

When you use macros with this mechanism, you can perform RBL-style reversed-IP lookups, or set up per-user exceptions.

Examples:

In the following example, the client IP is 1.2.3.4 and the current-domain is example.com.

"v=spf1 exists:example.com -all"
If example.com does not resolve, the result is fail. If it does resolve, this mechanism results in a match.

The "include" mechanism (edit)

include:

The specified domain is searched for a match. If the lookup does not return a match or an error, processing proceeds to the next directive. Warning: If the domain does not have a valid SPF record, the result is a permanent error. Some mail receivers will reject based on a PermError.

Examples:

In the following example, the client IP is 1.2.3.4 and the current-domain is example.com.

"v=spf1 include:example.com -all"
If example.com has no SPF record, the result is PermError.
Suppose example.com's SPF record were "v=spf1 a -all".
Look up the A record for example.com. If it matches 1.2.3.4, return Pass.
If there is no match, other than the included domain's "-all", the include as a whole fails to match; the eventual result is still Fail from the outer directive set in this example.

Trust relationships — The "include:" mechanism is meant to cross administrative boundaries. Great care is needed to ensure that "include:" mechanisms do not place domains at risk for giving SPF Pass results to messages that result from cross user forgery. Unless technical mechanisms are in place at the specified otherdomain to prevent cross user forgery, "include:" mechanisms should give a Neutral rather than Pass result. This is done by adding "?" in front of "include:". The example above would be:

"v=spf1 ?include:example.com -all"

In hindsight, the name "include" was poorly chosen. Only the evaluated result of the referenced SPF record is used, rather than acting as if the referenced SPF record was literally included in the first. For example, evaluating a "-all" directive in the referenced record does not terminate the overall processing and does not necessarily result in an overall Fail. (Better names for this mechanism would have been "if-pass", "on-pass", etc.)
Modifiers

Modifiers are optional. A modifier may appear only once per record. Unknown modifiers are ignored.

The "redirect" modifier (edit)

redirect=

The SPF record for domain replace the current record. The macro-expanded domain is also substituted for the current-domain in those look-ups.

Examples:

In the following example, the client IP is 1.2.3.4 and the current-domain is example.com.

"v=spf1 redirect=example.com"
If example.com has no SPF record, that is an error; the result is unknown.
Suppose example.com's SPF record was "v=spf1 a -all".
Look up the A record for example.com. If it matches 1.2.3.4, return Pass.
If there is no match, the exec fails to match, and the -all value is used.

The "exp" modifier (edit)

exp=

If an SMTP receiver rejects a message, it can include an explanation. An SPF publisher can specify the explanation string that senders see. This way, an ISP can direct nonconforming users to a web page that provides further instructions about how to configure SASL.

The domain is expanded; a TXT lookup is performed. The result of the TXT query is then macro-expanded and shown to the sender. Other macros can be used to provide an customized explanation.
Edit text of this page | View other revisions
Last edited 2008-06-29 12:49 (UTC) by Julian Mehnle (diff)
http://www.openspf.org/SPF_Record_Syntax

SPF website

SkyHi @ Monday, September 28, 2009
Note: Please get on the mailing list to stay abreast of updates.

Let's pretend that my domain name is:
of my users still "forge" their own address in outbound mail, through an SMTP server I do not control.
Pick a default.

SPF domains have to publish at least two directives: a version identifier and a default mechanism.

example.net. TXT "v=spf1 -all"

This is the simplest possible SPF record: it means your domain example.net never sends mail.

It makes sense to do this when a domain is only used for web services and doesn't do email.

But most domains will want to designate permitted hosts using one or more mechanisms.
If your MX servers send mail, designate them.

example.net. TXT "v=spf1 mx -all"

If example.net had an MX record, its MX servers would be designated. But it doesn't have any MX servers.
If other machines in the domain also send mail, designate them.

example.net. TXT "v=spf1 mx ptr -all"

This designates all the hosts whose PTR hostname match example.net.
If any other machines not in the domain also send mail from that domain, designate them.

example.net. TXT "v=spf1 a:example.net mx ptr -all"

example.net's IP address doesn't show up in its list of MX servers. So we add an "a" mechanism to the directive set to match it.

example.net. TXT "v=spf1 a mx ptr -all"

This is shorthand for the same thing.
Each of your mail servers should have an SPF record also.

When your mail servers create a bounce message, they will send it using a blank envelope sender: <>. When an SPF MTA sees a blank envelope sender, it will perform the lookup using the HELO domain name instead. These records take care of that scenario.

Consider creating an SPF record for every other machine in your domain.

Spammers can forge hostnames as well as domain names: to SMTP there is no difference between the two. If they start forging the hostnames of web servers, unix servers, even workstations, you'll want to create SPF records for those machines also.
Notes

If you send mail through another organization's servers, you should use an Include directive to point to their servers. If they do not have SPF records, maybe they don't know about SPF. Tell them about it!
(optional) use Include records to share these hosts

If other domains use exactly the same set of hosts, you can set up redirects for them. "Redirect" aliases point to other domains which themselves publish SPF records. This aliasing mechanism makes it possible to easily consolidate multiple domains that share the same set of designated hosts.

That's it. You're done.

Note: The above examples are good for a simple case, but do read the Mechanisms page or the SPF draft RFC to see how to configure complex cases. You can have multiple includes for a given domain.

Once you've set up records, try them out.


Reference: http://old.openspf.org/dns.html?mydomain=example.net&deny=deny