MySQL Storage Engines – an overview, their limitations and an attempt for comparison

Most of us are using Mysql database and majority don’t know how to choose the data base engines, what are the different types of storage engines available in mysql and how they differ from each other. In this article let me give you a brief idea about the Storage Engines and what are the limitations and where to use these various storage engines.

One of the greatest things about MySQL, other than being free, widely supported and fast, is the flexibility of choosing different storage engines for different tables. These storage engines act as handlers for different table types. Thus MySQL storage engines include both those that handle transaction-safe tables and those that handle non-transaction-safe tables along with many others. MySQL does this through their Pluggable Storage Engine Architecture.

MySQL Storage Engines Overview

To determine which storage engines your server supports, we use the SHOW ENGINES statement. The value in the Support column indicates whether an engine can be used. A value of YES, NO, or DEFAULT indicates that an engine is available, not available, or available and currently set as the default storage engine. (Read the rest of this entry…)

Tweet This Post

Issues that are encountered in web hosting

When tried to change passwd from WHM, the following error is displayed.

Error
The password for “user” could not be changed because:

Check the error in log file: /usr/local/cpanel/logs/error_log
You will find similar error as shown below.

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

Can’t locate Crypt/PasswdMD5/XS.pm in @INC (@INC contains:
/scripts /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl
/usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl
/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi
/usr/lib/perl5/5.8.8 .) at /scripts/cPScript/CheckPass/UNIX.pm line 6.
BEGIN failed–compilation aborted at /scripts/cPScript/CheckPass/UNIX.pm line 6.
Compilation failed in require at /scripts/cPScript/CheckPass.pm line 6.
BEGIN failed–compilation aborted at /scripts/cPScript/CheckPass.pm line 6.
Compilation failed in require at /scripts/realchpass line 16.
BEGIN failed–compilation aborted at /scripts/realchpass line 16.

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

Fix:

ssh into the server with root login credentials.
# /scripts/perlinstaller Crypt::PasswdMD5
Check if the issue still persists.

Then, let’s try fixing the problem without rebuilding Perl or changing any system configuration files. Run:

# /scripts/checkperlmodules
# service cpanel restart

Try changing the password again. If you’re still getting the error, it’s likely that the cPanel Perl installation was overwritten by an updated Perl RPM either manually or by yum. Let’s make sure Perl is left up to cPanel and check if perl automatic perl update is disabled in cPanel and excluded from yum list.

# grep allowperlupdates /var/cpanel/cpanel.config

If you see allowperlupdates=1 you’ve enabled “Allow Perl updates from RPM-based linux vendors” in WHM under Tweak Settings -> System. Return to WHM and uncheck this option then click the “Save” button.

In /etc/yum.conf, check if perl is present in exclude and intall the pearl module.
exclude=apache* bind-chroot courier* dovecot* exim* httpd* mod_ssl* mysql* nsd* perl* php*

Steps to install the pearl module.
First check the current version of Perl
# perl -v
If the older version of Perl is running on server, you get an output something like,
This is perl, v5.8.7 built for i686-linux
Upgrade perl on the Cpanel server
1) Download the latest perl installer from cPanel site.
# wget http://layer1.cpanel.net/perl588installer.tar.gz

2) Untar the file.
# tar -zxvf perl588installer.tar.gz

3) Change the directory to perl588installer.
# cd perl588installer

4) Now run the installer file.
# ./install

5) Check perl modules.
# /scripts/checkperlmodules

6) Force a cPanel update.
# /scripts/upcp –force

****************************************************************************************************************

How to locate mysql log file in the server.

* Check whether the mysql log file is present in /var/log such as /var/log/mysqld.log

* Usually, the mysql log file is present in /var/lib/mysql for cPanel servers. Kindly follow the steps to find the log file

======================================================================================================
root@server1 [/var/lib/mysql]# top -c | grep mysql
17545 mysql 15 0 427m 209m 5176 S 3.9 2.6 266:16.79 /usr/sbin/mysqld –basedir=/ –datadir=/var/lib/mysql –user=mysql –pid-file=/var/lib/mysql/server1.abc.com
======================================================================================================

Find the hostname of the server.
=======================
root@server1 [~]# hostname
server1.abc.com
=======================

We need to find the presently running mysql log file in the server. Usually, mysql log file will be associated with the hostname.

=======================================================
root@server1 [/var/lib/mysql]# ls -l | grep -i server1.abc.com
-rw-rw—-   1 mysql mysql  48495912 Aug 19 16:46 server1.abc.com.err
-rw-rw—-   1 mysql mysql     41720 May 18  2009 SERVER1.ABC.COM.err
-rw-rw—-   1 mysql mysql         6 Aug 19 16:46 server1.abc.com.pid
-rw-rw—-   1 mysql mysql         6 May 18  2009 SERVER1.ABC.COM.pid
=======================================================

From the above code, we find that server1.abc.com.err is used recently (checking with date). Therefore, server1.abc.com.err is the log file. To confirm it further, kindly check the process ID of server1.abc.com.pid with that of mysql process ID.

==========================================
root@server1 [/var/lib/mysql]# cat server1.abc.com.pid
17545
==========================================

The mysql log file displayed below.

==============================================================================
root@server1 [/var/lib/mysql]# vi server1.abc.com.err

090503 04:31:43 mysqld started
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
090503 4:31:43 InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait…
090503 4:31:43 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait…
090503 4:31:43 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait…
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090503 4:31:43 InnoDB: Started; log sequence number 0 0
090503 4:31:43 [Note] /usr/sbin/mysqld: ready for connections.
Version: ‘5.0.67-community’ socket: ‘/var/lib/mysql/mysql.sock’ port: 3306 MySQL Community Edition (GPL)
090503 4:31:46 [Note] /usr/sbin/mysqld: Normal shutdown

090503 4:31:46 InnoDB: Starting shutdown…
090503 4:31:47 InnoDB: Shutdown completed; log sequence number 0 43655
090503 4:31:47 [Note] /usr/sbin/mysqld: Shutdown complete

090503 04:31:47 mysqld ended
==============================================================================

***********************************************************************************

The “latest visitor” log in the stats of the cpanel is weird and not getting updated.

Error: Raw Access log contain only term “combined”, liked combined combined combined……………………

Fix:
Check if the format provided below is correct in the apache configuration file where version is Apache/2.2.15.
=======================================================================
<IfModule log_config_module>
LogFormat “%h %l %u %t \”%r\” %>s %b \”%{Referer}i\” \”%{User-Agent}i\”" combined
LogFormat “%h %l %u %t \”%r\” %>s %b” common

CustomLog logs/access_log common

<IfModule logio_module>
LogFormat “%h %l %u %t \”%r\” %>s %b \”%{Referer}i\” \”%{User-Agent}i\” %I %O” combinedio

</IfModule>

</IfModule>
=====================================================================

Tweet This Post

mod_php explained

Preface

Like in the case of all apache modules, you can either compile PHP as a static module or compile it as a dynamic module.  In the case of static module , you  can’t perform any modification for the module without recompiling the binary to which it is attached.  For eg.  you can’t add ssl support for the mod_php without re-compiling apache as a whole. And any failure in the compilation may cause downtime for the entire webserver also, including plain html support.

The advantage is that it provides a faster  performance, because the module is initialized  whenever the apache binary is started.

In the case of a dynamic mod_php installation, the necessary modifications or module additions can be done by recompiling the module alone. There is no need to recompile the Apache as the mod_php is not linked with the binary of apache.  But since the webserver loads the module on the fly, it needs to load, initialize and then execute the module.  So it can create some level of slowness while processing php pages.

How does mod_php work?

When PHP  is loaded into Apache as a module (using mod_php), each Apache process will contain an instance of mod_php or PHP interpreter also.   The interpreter comes with a bundle of libraries we enabled during compilation and each  process can make use of these libraries to process the requests. This means that the Apache process that just started  to load a simple HTML page  too will contain a PHP interpreter with all assigned libraries which inturn means resource consumption.

When the webserver gets an HTTP request. The  request header  contains the path to the requested document

e.g. access.log:    xx.xx.xx.xx – - [22/June/2010:21:14:53 -0700] “GET /info.php?=PHPE9568F35-D428-11d2-A769-00AA001ACF42 HTTP/1.1″ 200 2146 “http://domain.com/info.php” “Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.9.1.7) Gecko/20100106 Ubuntu/9.10 (karmic) Firefox/3.5.7″

1. The request will be redirected to the document root of the domain and then to the file “info.php” , if it fails then the corresponding error message will be given.

2. The info.php file is to be processed. It follows the following steps

Normally every httpd.conf file will have an entry like this

AddType application/x-httpd-php .php5 .php4 .php

It instructs the webserver that the files with extension .php ,.php4,.ph5 are of PHP mime type . The TypesConfig directive sets the location of the MIME types configuration file. This file controls what Internet media types are sent to the client for  given file extension(s).  Sending the correct media type to the client  is important so they know how to handle the content of the file.

root@new [/usr/local/apache/conf]# cat mime.types | grep x-httpd-php
application/x-httpd-php-source          phps
application/x-httpd-php          php php3 php4 php5 php6
root@new [/usr/local/apache/conf]#

Here we can see that the Mime type to be used for files with extension .php  is application/x-httpd-php , while the file with .phps is to be mapped to the php mime type application/x-httpd-php-source .

The webserver identifies that the requested file is of Mime type x-httpd-php.

To handle or process it , the apache has to load the corresponding module. Since it is a php type , the module mod_php will be loaded and it will execute the file.

Since apache is a HTTP server. It gets the HTTP requests and answers with the HTML code.  So the mod_php will execute the commands within php flag and creates the HTML page dynamically and send it back to the client – internet browser which sent HTTP request.

Security concerns / Implications

You can see that  every request or execution of a php file through web is initiated by the webserver. So the webserver acts as the parent of every php execution through web. It imposes a great security threat. Since apache is being executed as an apache  user, all process will be owned by that user. By default  it is “nobody” or “apache”.  Let me try to explain.

If your  web application performs some operations in the db, unless that database (eg: a flat text DB) has built-in access control, you will have to make the database accessible to the “nobody” user. This means a malicious script could access and modify the database, even without a username and password.  Such can be the case with various configuration files too.  Unless you protect these directories or applications with necessary authorization techniques like .htaccess, session control etc. There is a high possibility of attack through webapplication.

Another dangerous issue is of root escalation. If the webserver has a bug, by exploiting that bug, a malicious user can gain some root privileges or escalated to root. Its quite alarming situation as an escalated apache user can do any sort of  actions without any level of authentication.

Also it is difficult to identify the script which performs the malicious activity as all php scripts will be executed as “nobody”

Since PHP applications are executed as web server user, you need to give access and write permissions for the directories wherever the application  is supposed to be working. Sometimes you may be forced to give 777 permissions and it invites lot of attacks.

The files created by php applications will be owned by user “nobody” . So the user will not be able to delete the files unless it is done through another php application. Otherwise he needs to contact the server admin to get the same.

As a security measure, we may be forced to block mails from “nobody” users . But it can create mails generated from php applications being blocked in the server. Various php applications widely used for spamming . So some servers are configured to block mails from nobody users. This creates inconvenience to users.

Tweet This Post

How do I reset the root mysql pass, lxadmin created ?

Oh, do it from the GUI lxadmin gives you OR else, do it console based way.

# cd /usr/local/lxlabs/lxadmin/httpdocs/
# lphp.exe ../bin/common/misc/reset-mysql-root-password.phps

Tweet This Post

How to upgrade MySQL 5.0 to MySQL 5.1 in a cPanel server

A similar request came to us two years back when we had to upgrade MySQL version 4.0 to MySQL 5.0. We are listing here the same approach for the current needs of upgrading MySQL 5.0 to 5.1

File responsible for the MySQL version is /var/cpanel/cpanel.config

Open it, find the variable mysql-version and edit the value to read mysql-version=5.1

And finally run

/scripts/mysqlup

This should upgrade your MySQL version to MySQL 5.1. For better understanding of the process behind, /scripts/mysqlup, go through the code and browse the URL http://httpupdate.cpanel.net/mysqlinstall/

SupportSages rocks!!

Tweet This Post