MySQL
—–

* MySQL (pronounced “my ess cue el”) is an open source database management system.

* It uses Structured Query Language (SQL), the most popular language for adding, accessing, and processing data in a database.

* MySQL is noted mainly for its speed, reliability,and flexibility.

Advantages of MySQL
——————-

* It is multithreaded allowing multiple connections at the same time without slowing down the system.
* You can use MySQL with many different application-programming interfaces. These range
from Perl to C/C++ to Java.
* The feature that makes MySQL so very popular is,it costs you nothing to download.

Installing MySQL
—————-

* Using .rpm - RPM Package Manager

– yum search mysql-server – lists the compatible packages.
– yum install pkg-name – installs the package.

* Using .deb - a medium-level package manager for Debian

– apt-cache search mysql-server
– apt-get install pkg-name

* Using Source

– Download the required source (.tar.gz) file using wget
– Extract the tar file using tar –zxvf mysql.tar.gz
– Run ./configure

Start/Stop MySQL Server
———————–

• mysql.server script is used to start/stop the service

• After Installation the mysql.server script will be installed in the /etc/init.d directory with the name mysql or mysqld depending on the downloaded package.

• The mysqld daemon starts the server by invoking mysqld_safe binary
(/usr/bin/mysqld_safe)

• Finally a chkconfig command helps to start the service at system
startup :
– chkconfig –level 35 mysql/mysqld on : starts mysql service in runlevels 3 & 5

• service mysqld start/stop , /etc/init.d/mysqld start/stop , /scripts/restartsrv_mysql are various other means by which we can start/stop the service manually.

Storage Engines
—————

* ISAM - managed non-transactional tables. Each ISAM table is stored on disk in three files. A .frm – table format, .ISD – data file & .ISM extension – index files

* MyISAM – same as ISAM, but is more flexible in its properties when compared to the other. Each table is stored on disk in three files. A .frm – table format, .MYD – data file & .MYI extension –index files

* InnoDB - provides MySQL with a transaction-safe storage engine that has commit, rollback, and crash recovery capabilities. InnoDB has been designed for maximum performance when processing large data volumes

Basic MySQL commands
——————–
• shell> mysql –u root –p

• Creating a database
– mysql> CREATE database 134a;

• Deleting a database
– mysql> DROP database 134a;

• Change the Current Database
– mysql> USE 134a;

• Creating a table
– mysql> CREATE TABLE president ( last_name varchar(15) not null,
first_name varchar(15) not null );

• To see what tables are present
– mysql> SHOW tables;

• To view the structure of a table
– mysql> DESCRIBE president;

• Refer : http://www.pantz.org/database/mysql/mysqlcommands.shtml
for more commands

MySQL Permissions & Grant Tables
———————————

• In order to add a new user or update user’s privileges in mysql grant tables login to mysql as a root user

• GRANT priv_type ON {db_name.tb_name} TO user [IDENTIFIED BY [PASSWORD] ] ;

• Ex: Grant ALL on *.* to db_user identified by db_passwd
– Grants all privileges for the user db_user to all databases

• Execute Flush privileges to reset all the privileges

Backing Up Databases With MySQLDump
———————————–

• The mysqldump utility allows us to backup a database to an external resource such as a file, or even a completely different MySQL server running on the other side of the world!
Syntax :
– shell>mysqldump –user [user name] –password=[password]
[database name] > [dump file]

• Backing up of database from one MySQL server to another with
just one command :
– shell>mysqldump –opt mydatabase | mysql –host=remote_ip
newdatabase
– You must have already created the database on the remote server
( create database newdatabase; )

• Restoring a dump file :
– shell>mysql mydatabase < [dump file]

MySQLAdmin
———–

• mysqladmin is a client for performing administrative operations

• shell> mysqladmin [options] command [command-arg]

Examples :
– create db_name (ex : mysqladmin create data;)
– password new-password
– processlist : Show a list of active server threads
– Status : Display a short server status message

Refer : http://dev.mysql.com/doc/refman/5.0/en/mysqladmin.html for more commands

MySQL & PHP
————

Copyright 2006 MySQL AB The World’s Most Popular Open Source Database 11 MySQL & PHP

• To allow php to use MySQL this has to be configured first :
– shell> yum search php-mysql

• yum install pkg-name
– Whm servers : whm -> Apache Update -> Php Module -> Mysql Module (check & Start Build)

• A simple PHP & MySQL Code :
– <?php mysql_connect(”localhost”, “admin”, ”passwd”) or die
(mysql_error()); echo “Connected to MySQL<br />”; ?>
– Gives : “Connected to MySQL” result if everything is working fine

MySQL & WHM
————

• Every cpanel/whm server by default comes with MySQL
(a database server) set up.

• [Up/Down]Grading MySQL
– Whm -> Tweak Settings -> MySQL -> Check the version and Save
the settings
– To downgrade, the required version has to be specified in /
var/cpanel/cpanel.config file
– Now run /scripts/upcp –force to fix up errors (if any)

• Restart Service :
– Whm : whm -> Restart Services -> MySQL
– shell> /scripts/restartsrv_mysql

Databases through CPanel
————————

• Login to your cpanel at http://domain/cpanel

• From the main page, click MySQL databases

• Enter a name for the database and click Add Db

• Enter a username and password for the database and click Add User

• Select the appropriate user and their privileges for the database

• Click Add User to DB

• Database is now created with a user assigned to it

Tuning/Optimizing my.cnf file for MySQL
————————————–
• Mysql provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options

• Below is a sample my.cnf file :
– [mysqld]
– datadir=/var/lib/mysql
– socket=/var/lib/mysql/mysql.sock
– [mysql.server]
– user=mysql
– basedir=/var/lib
– [mysqld_safe]
– err-log=/var/log/mysqld.log
– pid-file=/var/run/mysqld/mysqld.pid

MySQL Monitoring Tools
———————-

• MTOP (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. In addition, server performance statistics, configuration information, and tuning tips are provided
– Download it from : http://mtop.sourceforge.net/

• MKILL (MySQL kill) monitors a MySQL server for long running queries and kills them after a specified time interval.

• Refer : http://www.linuxweblog.com/node/231/ for more info

Article Authored  by Jeeva Abraham

Author, Jeeva, is a Systems Engineer with SupportPRO. Jeeva specializes in Cpanel and Linux servers. SupportPRO offers 24X7 technical support services to Web hosting companies and service providers.

AJAX AND ATLAS
—————

AJAX stands for Asynchronous JavaScript And XML. AJAX is a type of programming made popular in 2005 by Google (with Google Suggest). AJAX is not a new programming language, but a new way to use existing standards. With AJAX you can create better, faster, and more user-friendly web applications. AJAX is based on JavaScript and HTTP requests.

What You Should Already Know:
—————————–
Before you continue you should have a basic understanding of the following:
HTML / XHTML JavaScript.

AJAX is not a new programming language, but a technique for creating better, faster, and more interactive web applications. With AJAX, your JavaScript can communicate directly with the server, using the JavaScript XMLHttpRequest object. With this object, your JavaScript can trade data with a web server, without reloading the page. AJAX uses asynchronous data transfer (HTTP requests) between the browser and the web server, allowing web pages to request small bits of information from the server instead of whole pages. The AJAX technique makes Internet applications smaller, faster and more user-friendly.

AJAX is a browser technology independent of web server software.

AJAX is based on Web Standards:
* JavaScript
* XML
* HTML
* CSS
The web standards used in AJAX are well defined, and supported by all major browsers. AJAX applications are browser and platform independent.

AJAX is About Better Internet Applications:

Web applications have many benefits over desktop applications; they can reach a larger audience, they are easier to install and support, and easier to develop. However, Internet applications are not always as “rich” and user-friendly as traditional desktop applications. With AJAX, Internet applications can be made richer and more user-friendly.

AJAX Uses HTTP Requests

In traditional JavaScript coding, if you want to get any information from a database or a file on the server, or send user information to a server, you will have to make an HTML form and GET or POST data to the server. The user will have to click the “Submit” button to send/get the information, wait for the server to respond, then a new page will load with the results. Because the server returns a new page each time the user submits input, traditional web applications can run slowly and tend to be less user-friendly.

With AJAX, your JavaScript communicates directly with the server, through the JavaScript XMLHttpRequest object With an HTTP request, a web page can make a request to, and get a response from a web server - without reloading the page. The user will stay on the same page, and he or she will not notice that scripts request pages, or send data to a server in the background.

The XMLHttpRequest Object:

By using the XMLHttpRequest object, a web developer can update a page with data from the server after the page has loaded! Google Suggest is using the XMLHttpRequest object to create a very dynamic web interface: When you start typing in Google’s search box, a JavaScript sends the letters off to a server and the server returns a list of suggestions. The XMLHttpRequest object is supported in Internet Explorer 5.0+, Safari 1.2, Mozilla 1.0 / Firefox, Opera 8+, and Netscape 7.

Ajax Example

Your First AJAX Application :

To understand how AJAX works, we will create a small AJAX application.

First, we are going to create a standard HTML form with two text fields: username and time. The username field will be filled in by the user and the time field will be filled in using AJAX.

The HTML file will be named “testAjax.htm”, and it looks like this (notice that the HTML form below has no submit button!):

<html>
<body>
<form name=”myForm”>
Name: <input type=”text” name=”username” />
Time: <input type=”text” name=”time” />
</form>
</body>
</html>

The next chapters will explain the keystones of AJAX.

AJAX - Browser Support
———————-

The keystone of AJAX is the XMLHttpRequest object. Different browsers use different methods to create the XMLHttpRequest object. Internet Explorer uses an ActiveXObject, while other browsers uses the built-in JavaScript object called XMLHttpRequest. To create this object, and deal with different browsers, we are going to use a “try and catch” statement. You can read more about the try and catch statement in our JavaScript tutorial.Let’s update our “testAjax.htm” file with the JavaScript that creates the XMLHttpRequest object:

<html>
<body>
<script type=”text/javascript”>
function ajaxFunction()
{
var xmlHttp;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
xmlHttp=new ActiveXObject(”Msxml2.XMLHTTP”);
}
catch (e)
{
try
{
xmlHttp=new ActiveXObject(”Microsoft.XMLHTTP”);
}
catch (e)
{
alert(”Your browser does not support AJAX!”); return false;
}
}

Example explained: First create a variable xmlHttp to hold the XMLHttpRequest object.

Then try to create the object with XMLHttp=new XMLHttpRequest(). This is for the Firefox, Opera, and Safari browsers. If that fails, try xmlHttp=new ActiveXObject(”Msxml2.XMLHTTP”) which is for Internet Explorer 6.0+, if that also fails, try xmlHttp=new ActiveXObject(”Microsoft.XMLHTTP”) which is for Internet Explorer 5.5+

If none of the three methods work, the user has a very outdated browser, and he or she will get an alert stating that the browser doesn’t support AJAX.

Note: The browser-specific code above is long and quite complex. However, this is the code you can use every time you need to create an XMLHttpRequest object, so you can just copy and paste it whenever you need it. The code above is compatible with all the popular browsers: Internet Explorer, Opera, Firefox, and Safari.

The next chapter shows how to use the XMLHttpRequest object to communicate with the server.

AJAX XMLHttpRequest
——————-

Before sending data to the server, we have to explain three important properties of the XMLHttpRequest object.

The onreadystatechange Property :::
After a request to the server, we need a function that can receive the data that is returned by the server.

The onreadystatechange property stores the function that will process the response from a server. The following code defines an empty function and sets the onreadystatechange property at the same time:

xmlHttp.onreadystatechange=function()
{
// We are going to write some code here
}

The readyState Property :::

The readyState property holds the status of the server’s response. Each time the readyState changes, the onreadystatechange function will be executed. Here are the possible values for the readyState propery:

State Description

0 The request is not initialized

1 The request has been set up

2 The request has been sent

3 The request is in process

4 The request is complete

We are going to add an If statement to the onreadystatechange function to test if our response is complete (this means that we can get our data):

xmlHttp.onreadystatechange=function()
{
if(xmlHttp.readyState==4)
{
// Get the data from the server’s response
}
}
The responseText Property :::

The data sent back from the server can be retrieved with the responseText property. In our code, we will set the value of our “time” input field equal to responseText:

xmlHttp.onreadystatechange=function()
{
if(xmlHttp.readyState==4)
{
document.myForm.time.value=xmlHttp.responseText;
}
}

AJAX - Sending a Request to the Server
————————————–

To send off a request to the server, we use the open() method and the send() method.

The open() method takes three arguments. The first argument defines which method to use when sending the request (GET or POST). The second argument specifies the URL of the server-side script. The third argument specifies that the request should be handled asynchronously. The send() method sends the request off to the server. If we assume that the HTML and ASP file are in the same directory, the code would be:

xmlHttp.open(”GET”,”time.asp”,true);
xmlHttp.send(null);

Now we must decide when the AJAX function should be executed. We will let the function run “behind the scenes” when the user types something in the username text field:

<form name=”myForm”>
Name: <input type=”text”
onkeydown=”ajaxFunction();” name=”username” />
Time: <input type=”text” name=”time” />
</form>

Our updated AJAX-ready “testAjax.htm” file now looks like this:

<html>
<body>
<script type=”text/javascript”>
function ajaxFunction()
{
var xmlHttp;
try
{
// Firefox, Opera 8.0+, Safari
xmlHttp=new XMLHttpRequest();
}
catch (e)
{
// Internet Explorer
try
{
xmlHttp=new ActiveXObject(”Msxml2.XMLHTTP”);
}
catch (e)
{
try
{
xmlHttp=new ActiveXObject(”Microsoft.XMLHTTP”);
}
catch (e)
{ alert(”Your browser does not support AJAX!”); return false;
}
}
}
xmlHttp.on

AJAX - The Server-Side ASP Script
———————————-

Now we are going to create the script that displays the current server time.

The responseText property (explained in the previous chapter) will store the data returned from the server. Here we want to send back the current time. The code in “time.asp” looks like this:

<%
response.write(time)
%>

Article Authored by ShivaKiran.

Author, ShivaKiran, was a Systems Engineer with SupportPRO. Shivakiran  was specialized in Cpanel and Linux servers. SupportPRO offers 24X7 technical support services to Web hosting companies and service providers.

HISTORY
——-

* Exim was written by Philip Hazel at the University of Cambridge in 1995.
* Written using the basic philosophy of Smail
* The name was derived from “Experimental Internet Mailer” as the outcome of the
project at start was unknown.

EXIM
—-

* Exim is Open Source and distributed under the GNU General Public License (GPL)
* Exim is a Mail Transfer Agent (MTA)

INTERNET MAIL TRANSPORT AGENTS
——————————

* MTA – Mail Transfer Agent
MTA is a program used for transporting mail from one host to another. Messages sent from one host to another use the Simple Mail Transfer Protocol (SMTP).

* MUA – Mail User Agent
The program people most often use to send/receive mail are referred to as Mail UserAgents (MUAs). They exist to provide convenient mail interfaces for users.
Examples are Outlook, Thunderbird, iMail, etc

The MUA simply sends the mail to the MTA which then transports the mail from host to host until it’s delivered to mailbox and retrieved using an MTA

MDA – Mail Delivery Agent
A Mail Delivery Agent (MDA) is used by the MTA to deliver email to a particular user’s mailbox.

EXIM PORT
———
* SMTP listens on TCP port 25. Because of this, Exim must be run as a priviledged
program.

* Port 465 is reserved for SMTP over SSL. However, Exim employs Transport Layer
Security (TLS) in order to not need a seperate port for secure communications.

HEADERS
——-

* RFC 2822 is standard used for the message format.
* RFC 2822 - Internet Message Format. It is an international standard that defines the format of SMTP email
* A message consists of a header and a body. The header contains a number of lines as defined by RFC 2822.
* RFC 2822 allows many variations for addresses, such as :
To: example@example.com
John Doe <example@example.com>
example@example.com (John Doe)

* Many header lines are added by the MTA. In addition to the header and the body,an envelope is transmitted immediately before the headers using SMTP commands MAIL and RCPT.
* Envelope contains sender address and at least one recipient address. Always in form of <user@domain>.
* Delivery is based completely on the envelope, not the To: or Cc: lines. Any message failures are always sent to the envelope from address, not the From: or Reply-to: address.
* Additional header lines are also added by the MUA and MTA. Before transmitting message, Date: and Message-ID: line always added by the MUA. Many other headers can also be added by MUA and MTA.
* SpamAssassin on cPanel servers add headers which are prefixed with X-Spam. Other headers can be added based on the Exim configuration.

HOW SMTP WORKS
—————

* SMTP is a Simple command-reply protocol. Client host sends command and awaits a reply.

• MUA =requests=> MTA
• MTA =220 ESMTP Ready=> MUA
• MUA =EHLO=> MTA
• MTA =Accept=> MUA
• MUA =From=> MTA
• MUA =To=> MTA
• MTA =Accepted=> MUA
• MUA =Data=> MTA
• MTA =354=> MUA
• MUA =send=> MTA
• QUIT

EXIM IN CPANEL
————-

• On cPanel servers, both /usr/lib/sendmail and /usr/sbin/sendmail are symlinks to the Exim binary.

• Most exim processes act seperately and are short lived. The exception to this is:

1) Process to listen on SMTP port for incoming TCP/IP connections
2) Process to start up queue runner processes.

Exim configuration file is stored at /etc/exim.conf on cPanel servers. However, all modifications to the exim.conf can be made through the Advanced Exim Configuration Editor in WHM.

MESSAGE ID
———-

• Every message handled by Exim is issued a unique Message-ID.
• The ID is 16 characters and seperated into 3 parts by hyphens. Each part is actually a number, encoded in base 62.
• The first part is the unix time the message started to be received. The second part is the PID of the process that received it. The third part is are used to distinguish
between processes received by the same process at the very same moment.

EXIM QUEUE
———-

* The exim queue on cPanel servers is stored at /var/spool/exim/input
* On cPanel servers, we opt to split the queue into 62 subdirectories. ([a-z], [A-Z], and [0-9]). This causes all messages to be distributed to the seperate subdirectories based on the 6th character in the message ID.
* This requires Exim to do more work when scanning the queue, but vastly improves the disk writing performance.

EXIM COMMANDS
————-

* You can manage the Exim queue from command line very easily.
* To list the contents of the queue, you would simply use the command:
exim -bp
* To only give you the number of emails in the spool you simply add a ‘c’ to the end of the command:
exim -bpc
* You can examine the contents of a message in queue using the ‘exim -Mvl command followed by the Message ID

EXIM STATS
———

* We use the eximstats daemon to monitor your exim queue.
* Eximstats is a perl script which parses logs and creates a mysql database with many different statistics about Exim.
* Most of this information can be accessed through WHM using the “View Mail Statistics” option.
* To restart eximstats you would use /scripts/restartsrv_eximstats.

TroubleShooting EXIM
——————–
* There are many troubleshooting techniques you can use on the command line for Exim.
* To test how Exim would route a message you simply use the ‘exim -bt’ command, for instance:
exim -bt nick@example.com
* This will tell you which routers Exim would use to deliver a message to this address.

EXIM LOG FILES
————–

* There are three log files available for you to monitor the activities of Exim.
- The main exim log file (exim_mainlog) records the arrival of each message as well as the
delivery in a single logical line.
- The reject log file (exim_rejectlog) records information about messages and addresses
that are rejected based on policy.
– The exim panic log (exim_paniclog) is only used when Exim suffers a disastrous error. (most often related to syntax errors in the log files).

EXIM.CONF
———

* The exim configuration editor can be accessed through WHM for modifying any aspect of the exim.conf.
* This editor also allows you some simple configuration options such as:
– Always set the Sender: header when the sender is changed from the actual sender. (Unchecking this will stop “On behalf of” data in Microsoft(R) Outlook, but may
limit your ability to track abuse of the mail system.)
* Verify the existance of email senders.
* Use callouts to verify the existance of email senders.
* Discard emails for users who have exceeded their quota instead of keeping them in the queue.

MAILDIR VS MBOX
—————-

* The best advantage of Maildir over other mailbox formats is maildir does not require locks as it’s all seperate files.
* All mail is written to a tmp directory first, and then written to the ‘new’ directory.
* Once the MUA finds mail in the ‘new’ directory, it will move it to the ‘cur’ directory.
* All cPanel servers now come default using Maildir instead of mbox. However, for any old systems which have not yet been converted to maildir, you can simply use the script:
/scripts/convert2maildir
* Maildir is especially beneficial for servers that pass large volumes of mail.

EXIM FACTS FOR THE SUPPORT TEAM
——————————-

• RELAYING
• RBL
• Finding A Mail From Log
• SPAMD
• CLAMD
Article Authored by Nobin Joseph

Author, Nobin, is a  Sr.Systems Engineer with SupportPRO. Nobin specializes in Exim,Cpanel and Linux servers. SupportPRO offers 24X7 technical support services to Web hosting companies and service providers.