Some MySQL commands are given below to manage your MySQL databases 

Step 1 

Create database 

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

    [create_specification] ...

create_specification:

    [DEFAULT] CHARACTER SET [=] charset_name

  | [DEFAULT] COLLATE [=] collation_name

Create database db_name;

Step 2

Create user
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']
    [, user [IDENTIFIED BY [PASSWORD] 'password']] ...
Create user db_user identified by ‘db_passswd’;

Step 3

Grant privileges
GRANT priv_type [(column_list)]
[, priv_type [(column_list)]] …
ON [object_type]
{
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name
}
TO user [IDENTIFIED BY [PASSWORD] ‘password’]
GRANT ALL ON *.* TO ‘someuser’@'somehost’;
GRANT SELECT, INSERT ON *.* TO ‘someuser’@'somehost’;

Step 4
Remove database user
DROP USER user;

Step 5

Set password for database users
SET PASSWORD FOR ‘alan’@'%.loc.gov’ = PASSWORD(‘newpass’);

That is equivalent to the following statements:
UPDATE mysql.user SET Password=PASSWORD(‘newpass’)
WHERE User=’alan’ AND Host=’%.loc.gov’;
FLUSH PRIVILEGES;

Optimization is an integral part of database administration. When database is heavily used having too many records and hence there are too many inserts/deletes or retrievals from the database in that case MySQL uses most of system resources.

There are two many options you have to optimize the query or MySQL databases.

Those options are illustrated in this article.

If querying is taking lot of time, and database connections and backlogs are growing, then site performance as well as server performance goes down causing high load sometimes or database crashed which indicate that it is time to optimize your database.

mysqladminpr command is used to check which mysql queries are responsible for heavy system resources.

You can modify MySQL configuration file (my.cnf) to log slow queries. It will check the slow running queries and the possible causes.

This could be related to indexes like no index or improper index for database columns. Here is an attempt to describe which commands and concepts we can use to optimize MySQL so that it will improve the performance.

Step 1:

Use of indexes

Using indexes makes the query faster in the same way as an index in a book helps in looking for a particular word.

To know how and which indexes are used in a given query, use EXPLAIN command as a prefix to select query.

EXPLAIN SELECT employee_code FROM emp WHERE dept = ‘IT’;

This command returns following information:

Table – Which table(s) are used in the query
Type – JOIN type. Values can be system, const, eq_ref, ref, range, index, all
Possible Keys – All keys which can be used for indexes
Key – The key actually used for index
Key Length – Shorter keys are better
Ref – Other Columns used with key to get the results
Rows – Number of rows from where data will come
Extra – Extra information. Some possible values can be using index, using where, using temporary, using filesort

You should reconsider your table structure if Possible Keys contain NULL value.
A NULL value in Key field indicates no index is being used. Using no indexes will deter the performance if there are too many records in the table because, for any query, all the records will be searched for the given condition.

Use ALTER command to add index as given below:

ALTER TABLE table_name ADD INDEX (column_name);

You can do indexing on multiple columns using:

ALTER TABLE table_name ADD INDEX (column_name1,..,column_nameN);

MySQL uses leftmost prefixing, so automatically indexes will be added for column_name1, (column_name1,column_name2),…(column_name1,..,column_NameN-1). This is quite helpful in situations like searching based on surname so an index on (surname, firstname, middlename) will automatically add multiple indexes used in faster querying.

Points to remember:

• Choose the column(s) for indexing very carefully.

• Indexed fields should be used for searching and query should be re-framed if any calculation is performed on indexed fields.

• Index itself takes storage space.

• Each operation on database requires updating index as well.

• A smaller Key Length reported by EXPLAIN command is better. Smaller key length means lesser storage space is required by index file and also that time to search in index is less. For this, indexes can be set on part of columns. For example, INDEX(col1(10),col2(20)). Again, you have to judiciously decide what length of columns to be used for indexing.

Step 2:

Using ANALYZE command

ANALYZE command is used to generate key distribution for the table to be used by MySQL query optimizer to decide which indexes are best to use in a query.

ANALYZE TABLE table_name;

Step 3:

Using OPTIMIZE command

If there are too many inserts/deletes in a table then OPTIMIZE command must be frequently used to optimize disk head movement during retrieval. This is particularly useful if variable size columns are used like varchar, text, blob etc. OPTIMIZE command does defragmentation of the fragmentation caused by variable length fields like VARCHAR.

OPTIMIZE TABLE table_name;

Step 4:

Using special functions for loading data

For loading data from a file into a database table, using LOAD DATA INFILE is much faster than reading data from file iteratively and using INSERT command. The syntax for this command is:

LOAD DATA INFILE ‘impdata.dat’ INTO TABLE table_name (col1,col2,…,colM) FIELDS TERMINATED BY ‘|’”);

Step 5:

Setting PRIORITY of commands

If there are more queries than data insertions, you may lower priority of INSERT command using:

INSERT LOW_PRIORITY or SELECT HIGH_PRIORITY

If client is not interested in results of INSERT, then it can be immediately freed using following command:

INSERT DELAYED.

This makes the system faster by batching insertions.

Step 6:

DELETE vs. TRUNCATE

DELETE command use to deletes row by row, while TRUNCATE use to deletes all rows at once. So, if you are not interested in number of rows deleted from a table as result of DELETE command, then use TRUNCATE with following syntax:

TRUNCATE TABLE table_name;

Step 7:

Lowering permission checking overhead

Simpler permissions reduce permission checking overhead. Use GRANT command to set the permissions. General syntax for GRANT is:

GRANT priv_type [(column_list)]

[, priv_type [(column_list)]] …

ON [object_type]

{

*

| *.*

| db_name.*

| db_name.tbl_name

| tbl_name

| db_name.routine_name

}

TO user [IDENTIFIED BY [PASSWORD] ‘password’]

For example:

GRANT SELECT,UPDATE ON emp_sal TO hr@localhost IDENTIFIED BY ’password’;

If user account ‘hr’ is not there, then it will be created on localhost (although user can access data from any workstation) and password ‘password’ will be assigned to it.

Step 8:

Use BENCHMARK() function

To know how much a given MySQL function or expression is taking time, use MySQL built-in function:

BENCHMARK(loop_count, expression)

It always returns 0 but also prints time taken by the expression.

Step 9:

Synchrozing data types

If columns in tables contain identical information, then make their data types also same so that joins can be performed faster.

Step 10:

Database optimization is not one time job. You have to optimize it every after 2-3 months.

Installing php-pear

First we will have to install the php-pear module on the server. This can be done using yum.

yum install php-pear*

We must also configure pear in php config at /etc/php.ini.

include_path=”.:/usr/share/pear:/local/PEAR/”

Restart apache on the server.

/etc/init.d/httpd stop
/etc/init.d/httpd start

Enable PEAR for a domain.

Create a file called vhost.conf in /var/www/vhosts/domain.com/conf with the following contents:

<Directory /var/www/vhosts/domain.com/httpdocs>
php_admin_value include_path “/var/www/vhosts/domain.com/httpdocs/:/usr/share/pear/”
php_admin_value open_basedir “none”
</Directory>

Reconfigure your webserver so it will look for your new vhost.conf file by doing this:

/usr/local/psa/admin/sbin/websrvmng –reconfigure-vhost –vhost-name=domain.com

Finally  restart apache service:

/etc/init.d/httpd stop
/etc/init.d/httpd start

PROBLEM:
While Login into Webmail it returns the error: “Login failed”

SOLUTION:
Make sure that it is possible to connect to localhost:143 from the command line using telnet. If you get the error as below:
-=-=-
# telnet localhost 143
localhost/143: Name or service not known
-=-=-

then check /etc/hosts file. It must have permissions -rw-r–r– and contain the record for localhost in the following format:
-=-=-
127.0.0.1       localhost localhost.localdomain
-=-=-
Above is from From Parallels Knowledgebase

If /var/log/psa-horde/psa-horde.log shows something like:

FAILED LOGIN xx.xx.xx.xx to localhost:143[imap/notls] as me@mydomain.co.uk
FAILED LOGIN xx.xx.xx.xx to localhost:143[imap/notls] as me@mydomain.co.uk

I have searched regarding this but unable to find any fix so, I just re-installed the IMAP Service on the server. Following are the steps using which I have re-installed IMAP Server:
-=-=-
1) You can find the RPMS used by Plesk in /root/psa/PSA_version directory OR you can download the Tar (Not Autoinstaller) containing RPMS from Parallels website http://www.parallels.com/en/download/ (need registration it’s free)

2) go to the RPM directory which contains courier Imap RPM’s
#cd [path to the PSA RPM Directory]/dist-rpm-CentOS-4.2-i386/base/

3) Re-install courier-imap-3.0.8-cos4.build81070322.16.i586.rpm and psa-courier-imap-add-8.1.1-cos4.build81070322.16.i586.rpm which will automatically restart the services too
-=-=-
[root@vps base]# pwd
/root/plesk/dist-rpm-CentOS-4.2-i386/base
[root@vps base]# rpm -Uvh courier-imap-3.0.8-cos4.build81070322.16.i586.rpm psa-courier-imap-add-8.1.1-cos4.build81070322.16.i586.rpm –force
Preparing… ########################################### [100%]
Reloading configuration: [ OK ]
1:courier-imap ########################################### [ 50%]
Stopping Courier-IMAP server:
Stopping imap [ OK ]
Stopping imap-ssl [ OK ]
Stopping pop3 [ OK ]
Stopping pop3-ssl [ OK ]

Starting Courier-IMAP server:
Starting imapd [ OK ]
Starting imap-ssl [ OK ]
Starting pop3 [ OK ]
Starting pop3-ssl [ OK ]

2:psa-courier-imap-add ########################################### [100%]
Stopping Courier-IMAP server:
Stopping imap [ OK ]
Stopping imap-ssl [ OK ]
Stopping pop3 [ OK ]
Stopping pop3-ssl [ OK ]

Starting Courier-IMAP server:
Starting imapd [ OK ]
Starting imap-ssl [ OK ]
Starting pop3 [ OK ]
Starting pop3-ssl [ OK ]

[root@vps base]# netstat -nap | grep :143
tcp 0 0 0.0.0.0:143 0.0.0.0:* LISTEN 1657/couriertcpd
[root@vps base]# telnet localhost 143
Trying 127.0.0.1…
Connected to localhost.
Escape character is ‘^]’.
-=-=-

After this I was able to login into Webmail without any problem. I hope this will help other to fix the issue too