Differences between innodb and myisam mysql storage engines

Data in MySQL is stored in files (or memory) using a variety of different techniques. Each of these techniques employ different storage mechanisms, indexing facilities, locking levels and ultimately provide a range of different functions and capabilities. By choosing a different technique you can gain additional speed or functionality benefits that will improve the overall functionality of your application.

Comparison between the MyISAM and InnoDB storage engines of MySQL.

innodb myisam

1

row-level locking table-level locking

2

supports transaction does not support transactions

3

foreign key constraints no foreign key constraints

4

row count is not stored internally and so slow COUNT(*)s row count is stored internally and so fast COUNT(*)s

5

automatic crash recovery no automatic crash recovery, but it does offer repair table functionality

6

stores both data and indexes in one file stores indexes in one file and data in another

7

uses a buffer pool (innodb_buffer_pool_size) to cache both data and indexes uses key buffers (key_buffer) for caching indexes and leaves the data caching management to the operating system

8

ACID(Atomicity, Consistency, Isolation and Durability) compliant not ACID compliant

Import csv file to a mysql table

Using the LOAD DATA INFILE SQL statement we can import data to a mysql table. Suppose I have a table named from_csv in the database test_csv.

Using the LOAD DATA INFILE SQL statement we can import data to a mysql table.

Suppose I have a table named from_csv in the database test_csv.

And I have csv file with following content

The following query will import this csv file to mysql table from_csv

After the execution of the query the contents of the table: is:

Compile apache with suphp on Ubuntu

One day my client asked me to setup a webserver on a control panel less Ubuntu server. He needs as php as suphp. Following are the steps which I followed during the web server setup.

One day my client asked me to setup a webserver on a control panel less Ubuntu server. He needs as php as suphp.

Following are the steps which I followed during the web server setup.

Downloaded all neccessary sources:

1. First compile apache from the source with following configure options
Continue reading “Compile apache with suphp on Ubuntu”

Generating and installing SSL certificate

Once the private key is generated a Certificate Signing Request can be generated. The CSR is then used in one of two ways. Ideally, the CSR will be sent to a Certificate Authority, such as Thawte or Verisign who will verify the identity of the requester and issue a signed certificate. The second option is to self sign the CSR.

Before proceeding with installation, make sure that Apache is compiled with mod_ssl. You can check the same by executing the following command in the server.

Continue reading “Generating and installing SSL certificate”

Checking SSL cert’s validity and other details

OpenSSL is a cryptography toolkit implementing the Secure Sockets Layer (SSL v2/v3) and Transport Layer Security (TLS v1) network protocols and related cryptography standards required by them. The openssl program is a command line tool for using the various cryptography functions of OpenSSL’s crypto library from the shell

1. Get complete available details of an SSL certificate

2. Who issued the certificate?
Continue reading “Checking SSL cert’s validity and other details”

Apache detailed status from URL

Hello every one. I wrote the last article around 30 days before. I was little busy. Today is Dec 24 and I wish you all a happy Merry Christmas.

In this article I am explainig the way to enable apache status which can be view from a URL by browsers. This feature of apache is by the “status_module” module. The Status module allows a server administrator to find out how well their server is performing. A HTML page is presented that gives the current server statistics in an easily readable form. If required this page can be made to automatically refresh (given a compatible browser).

For enabling apache status you need to edit your apache configuration file [httpd.conf].

Add following lines to your httpd.conf file.
Continue reading “Apache detailed status from URL”

mysql queries on command line

In this article I like to share some tips which I am using while dealing with mysql databases.

‘-e’ is the switch which enables command line mode for ‘mysql’ client which is installed default on every Linux distros.

Some examples:

1. For listing all mysql users on the server.

2. Show values assisned to each mysql variable

Continue reading “mysql queries on command line”

Making mod_sec rule exceptions – part II

In the previous article I explained how to disable complete mod_sec rules for a particular URL. But from the security point of view it is very unsafe to doing so. This article will explain how to disable a particular rule for a particular URL.

For doing this we use mod_sec Configuration Directive named SecRuleRemoveByID

Configuration Directive SecRuleRemoveByID:

Description: Removes matching rules from the parent contexts.

Syntax: SecRuleRemoveById RULEID
Continue reading “Making mod_sec rule exceptions – part II”

Making an exception for a particular URL in mod_sec rules – part I

What Is ModSecurity?
ModSecurity is a web application firewall that can work either embedded or as a reverse proxy. It provides protection from a range of attacks against web applications and allows for HTTP traffic monitoring, logging and real-time analysis.
It is also an open source project that aims to make the web application firewall technology available to everyone.

Suppose you want to make an exception in mod_sec rules for some URLs say all URLs starting with “phpmyadmin”.

For that do the following:

Step: 1

Check mod_sec rule conf path from main httpd.conf file.

Step: 2
Continue reading “Making an exception for a particular URL in mod_sec rules – part I”

Password protecting a folder of a website

Authentication is any process by which you verify that someone is who they claim they are. Authorization is any process by which someone is allowed to be where they want to go, or to have information that they want to have.

If you have information on your web site that is sensitive or intended for only a small group of people, the techniques in this article will help you make sure that the people that see those pages are the people that you wanted to see them.

This article covers the “standard” way of protecting parts of your web site that most of you are going to use.
Continue reading “Password protecting a folder of a website”