Command Center

Removing a passphrase from an SSL Key
Friday, October 12th, 2007

The typical process for creating an SSL certificate is as follows:

# openssl genrsa -des3 -out www.key 1024

At this point it is asking for a PASS PHRASE (which I will describe how to remove):

Enter pass phrase for www.key:

# openssl req -new -key www.key -out www.csr

Next, you will typically send the www.csr file to your registrar. In turn, you should receive a key.

From a security standpoint utilizing a passphrase, is a good thing, but from a practical standpoint not very useful.

For instance, what happens when your server reboots/crashes at 3am? Or better, what happens in 6 months when you reboot your machine, and you don’t remember the password? Well, one thing is for sure, your web server will not be online.

I suggest removal of the passphrase, you can follow the process below:

Always backup the original key before first to make sure no mistakes occur:

# cp www.key www.key.orig

Then unencrypt the key with openssl. You’ll need the passphrase for the decryption process:

# openssl rsa -in www.key -out new.key

Now copy the new.key to the www.key file and you’re done. Next time you restart the web server, it should not prompt you for the passphrase.

Reference: http://www.mnxsolutions.com/blog/apache/removing-a-passphrase-from-an-ssl-key.html

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
(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 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/

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/

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

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/