Archive for the ‘MySQL Server’ Category

Mysql Errors and solutions

Tuesday, May 14th, 2013

(a)Last_Errno: 1008

mysql>STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;

Reason :
Error: 1008 SQLSTATE: HY000 (ER_DB_DROP_EXISTS)
Message: Can’t drop database ‘%s’; database doesn’t exist

3 Cant Access mysql Sever

Monday, November 29th, 2010

hi
When I am trying to run plesk , it does not open and and mysql server does not start.
the error i am seeing is :
# service psa start
Starting psa… done
Starting xinetd service… done
Starting named service… done
Starting mysqld service… failed
Starting postgresql service… failed
Starting psa-spamassassin service… done
Plesk: Starting Mail Server… already started
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
Starting mail handlers tmpfs storage
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
Starting psa… done
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

cahit

Linux:InnoDB: Unable to lock ./ibdata1, error: 37

Tuesday, September 14th, 2010

Ref: http://bugs.mysql.com/bug.php?id=47769
Database is located on the remote storage that mount via NFS. During mysql startup the
following entries appear in the .err file:
InnoDB: Unable to lock ./ibdata1, error: 37

Solution: mount -t nfs -o nolock IP:/data /data

Mysql Server processlist shows negative value(-) in connect column for system user

Wednesday, June 9th, 2010

Some times process list out put show negative value like bellow :
Command :

watch /usr/local/mysql/bin/mysqladmin -ppass processlist

8 | system user | | Connect | -1247 | Has read all relay log; waiting for the slave I/O thread to update it |

One of the reason :
make sure both Server has same time zone.
if there is any time difference between 2 server the replicate client show negative values

How To Set Up MySQL Database Replication With SSL Encryption

Wednesday, June 9th, 2010

SSl Replication between 2 Active Active Mysql Server


Step1 :
Set up normal replication first and find out if mysql server is compiled with ssl supports
Ref:http://www.fosiul.com/index.php/2009/11/mysql-server-master-master-active-active-replication/

Bellow commands will verify if mysql server is compiled with ssl supports

SHOW VARIABLES LIKE ‘have_openssl’;

output :

(Yes mean)Mysql Server is compiled with ssl


Step2 :
in Server1 :
(a)Create Self signed certificate .
Note : While Creating self signed certificate use different common name for each certificate,other wise it will through ssl certificate error.

Creating Self signed certificate :
ref :http://dev.mysql.com/doc/refman/5.1/en/secure-create-certs.html

mkdir /usr/local/mysql/ssl ( I am assuming ,mysql has been compiled at /usr/local/mysql directory)

cd /usr/local/mysql/ssl

# Create CA certificate (Use different common name)

shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 1000 \
         -key ca-key.pem > ca-cert.pem

# Create server certificate (use different common name)

shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

# Create client certificate

shell> openssl req -newkey rsa:2048 -days 1000 \
         -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 1000 \
         -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

So it will be like bellow picture

Step 2 :

Copy all these files into Server 2 .Location : /usr/local/mysql/ssl

Reason : we will make Master Master Active Active Replication. There would be ssl encryption between Server1 to server 2 and server2 to server1.
Picture :SSl Replication between 2 Active Active Mysql Server

scp * root@ns2.server2co.uk:/usr/local/mysql/ssl/
(Assume, we are in /usr/local/mysql/ssl directory of Server1)

Step 3:

For Server1 :

Edit my.cnf file add bellow lines in [ Mysqld] sections
 
ssl-key=/usr/local/mysql/ssl/server-key.pem
ssl-cert=/usr/local/mysql/ssl/server-cert.pem
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
 
[client]
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
ssl-key=/usr/local/mysql/ssl/client-key.pem
ssl-cert=/usr/local/mysql/ssl/client-cert.pem
 
 
For Server2 :
<pre lang="GNU">
Edit my.cnf file add bellow lines in [ Mysqld] sections
 
ssl-key=/usr/local/mysql/ssl/server-key.pem
ssl-cert=/usr/local/mysql/ssl/server-cert.pem
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
 
 
[client]
ssl-ca=/usr/local/mysql/ssl/ca-cert.pem
ssl-key=/usr/local/mysql/ssl/client-key.pem
ssl-cert=/usr/local/mysql/ssl/client-cert.pem

Restart the both server, using the –skip-slave-start
ref :href=”http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_skip-slave-start

/usr/local/mysql/bin/mysqld_safe --skip-slave-start --user=mysql &

Now check if both server has ssl linked to accurate directory

Execute bellow command in mysql server console in both server.

mysql> show variables like '%ssl%';

it will give output like bellow picture

Ssl Enabled and its looking to right directory

Step 4 :
Create replication user
For server 1

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'ip.of.your.server2' IDENTIFIED BY 'strong-password' require SSL;

For server 2

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'ip.of.your.server1' IDENTIFIED BY 'strong-password' require SSL;

Step 5 :
Server 1:
Open firewall rules ,and allow traffic to 3306 port from only ip of server2

Server 2:

Open Firewall rules ,and allow traffic to 3306 port from only ip of server1

Step 5 :

Test if both server accepting ssl connection from each other and its going via Secure ssl encryption
From Server 1/Server 2 :

mysql --ssl -hip-of-server1 -uSSL_CLIENT -ppassword

if everything goes ok then you should see mysql prompt. at the mysql prompt , type
\s to verify that its going through via ssl encryption.

Look at the ssl column for :
SSL:Cipher in use is DHE-RSA-AES256-SHA or similar
Same as bellow picture:

Ssl is enabled

Step 6 :
Connect Serve1 with SErver 2 and SErver 2 with Server 1

Server1 to Server2 :

CHANGE MASTER TO MASTER_HOST='ip.of.your.server2', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=53678, MASTER_SSL=1,MASTER_SSL_CA = '/usr/local/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/usr/local/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/usr/local/mysql/ssl/client-key.pem';

Server2 to Server1

CHANGE MASTER TO MASTER_HOST='ip.of.your.server1', MASTER_USER='slave', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=53488, MASTER_SSL=1,MASTER_SSL_CA = '/usr/local/mysql/ssl/ca-cert.pem', MASTER_SSL_CERT = '/usr/local/mysql/ssl/client-cert.pem', MASTER_SSL_KEY = '/usr/local/mysql/ssl/client-key.pem';

NOte : make sure you lock al the tables before taking log file positions and also check the log file position from both server.

Step 6 :
Now start slave server on both server.

 
slave start

Step 7:
Verify if both server looking to each other.

Server1/Server2

show slave status\G;

check if the output is similar with the bellow picture

Check if all slaves looking to each other

Look for bellow options :

Master_Host: xx.xx.xx.xx
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 128108
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /usr/local/mysql/ssl/ca-cert.pem
Master_SSL_Cert: /usr/local/mysql/ssl/client-cert.pem
Master_SSL_Key: /usr/local/mysql/ssl/client-key.pem

Please let me know if there is any problem you face while implementing this.
Thanks

Mysql server master master active active replication

Tuesday, November 24th, 2009

Ref: http://www.howtoforge.com/mysql_master_master_replication

a) Create user name and password for replication on both servers by using this command

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO noslave@’host-name’ IDENTIFIED BY ’some-pass’;

b) Configuration for Server 1 To make it primary Server for Server2

vi /etc/my.cnf

log-bin=mysql-bin
binlog-do-db=fosiul # which Database to replicate
binlog-do-db=hesk # Which Database to replicate
binlog-ignore-db=mysql # Which Database to ignore
binlog-ignore-db=test # Which Database to ignore
server-id = 1 # Primary Server id
auto_increment_increment= 2 # to solved the issue for auto indexing problem
auto_increment_offset = 1 # to solved the issue for auto indexing problem

Configuration For server2 to make as Slave for Server1

server-id = 2

master-host = IP_Of_Server1
master-user =noslave
master-password = SomeStrongPassword
master-port = 3306
auto_increment_increment= 2 # Avoid Auto Indexing problem
auto_increment_offset = 2

Now restart both Server and look for bellow report:

For Server 1 (Master Report):

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000008 | 565444 | fosiul,hesk | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

For Server2:( Slave report)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: IP-Of-Server1
Master_User: noslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 565444
Relay_Log_File: web-relay-bin.000092
Relay_Log_Pos: 153971
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 565444
Relay_Log_Space: 154124
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

ERROR:
No query specified

note :

a) Make sure Master_Log_File: mysql-bin.000008 From Slave Report matches with the Master_Log_file name with Master Reports.

b)Make sure Read_Master_Log_Pos: 565444 at Slave Report matches with Position field at Master Reports

c) Make sure Seconds_Behind_Master is always 0(Zero)

Click on the picture to view

Verify Log File

Master(Server1) Slave (Server2)

c)Configuration for server 2 as Master for Server1

#Bellow section for acting as Master for server1

log-bin=mysql-bin

binlog-do-db=fosiul # Which Database to repliacate
binlog-do-db=hesk # Which Database to replicate
binlog-ignore-db=mysql # Which Database to ignore
binlog-ignore-db=test # Which Database to ignore

#Configuration for Server1 to make as slave for Server 2

master-host = IP-Of-Server2
master-user = noslave
master-password = SomeSTrongPassowrd
master-port = 3306
log-slave-updates # To make this Master Server act as slave

d) Now Restart both mysql server and look for bellow report:

Slave Status report for Server1

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: IP_OF_Sever2
Master_User: noslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 106
Relay_Log_File: mail-relay-bin.000025
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 550
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)

Master Report for Server2 :

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000006 | 106 | fosiul,hesk | mysql,test |
+——————+———-+————–+——————+
1 row in set (0.01 sec)

Note :

a) Make sure Master_Log_File: mysql-bin.000006 From Slave Report matches with the Master_Log_file name with Master Report.

b)Make sure Read_Master_Log_Pos: 106 at Slave Report matches with Position field at Master Report.

c) Make sure Seconds_Behind_Master is always 0(Zero)

Click on the picture to view

Slave(Server1) and Master (Server2)

Slave(Server1) and Master (Server2)

How to install innotop

Thursday, November 19th, 2009

a)Download innotop from http://code.google.com/p/innotop/

b)cd /tmp

c) tar -xvzf innotop-1.7.2.tar.gz

d) cd innotop-1.7.2

e) perl per Makefile.PL

f) Make install

NOte : if you see error like this :

Looks good
Warning: prerequisite DBD::mysql 1 not found.
Warning: prerequisite DBI 1.13 not found.
Warning: prerequisite Term::ReadKey 2.1 not found.
Writing Makefile for innotop

Solution :

yum install perl-DBD-MySQL

yum install perl-TermReadKey

Run innotop : perl /usr/bin/innotop –password “your password”

How to install apache2-php-mysql from source

Thursday, September 10th, 2009

Prerequisite : yum install gcc-c++ gcc make ncurses-devel openssl-devel glibc* libc-*

Packages required for php: yum install libjpeg-devel libpng-devel curl-devel libmcrypt-devel krb5-devel

Apache Server Installation from Source:
Apache installation directory is : /usr/local/apache
a) Download the apache source file from : http://httpd.apache.org/download.cgi
b) Download the source file in to /tmp directory.
c) I am guessing the source file is httpd-2.2.13.tar.gz
d) Cd /tmp
e) tar –xvzf httpd-2.2.13.tar.gz
f) cd httpd-2.2.13

g)

 
./configure  --prefix=/usr/local/apache --with-included-apr --with-php --with-mysql --with-susexec --disable-info --with-mpm=prefork --enable-so --enable-cgi --enable-rewrite --enable-ssl --enable-mime-magic --enable-unique-id --enable-mods-shared="proxy cache ssl all"

h) make
i) make install
j)To restart apache : /usr/local/apache/bin/apachectl start

MySql Server Install from source:

Ref:http://dev.mysql.com/doc/refman/5.1/en/quick-install.html

a)Download my.version.tar.gz from
http://dev.mysql.com/downloads/mysql/5.1.html#source

b)shell> groupadd mysql
c)shell> useradd -g mysql mysql
d)shell> gunzip < mysql-VERSION.tar.gz | tar -xvf – e)shell> cd mysql-VERSION
f)

   ./configure --prefix=/usr/local/mysql --with-ssl --with-plugins=innobase

note:: for mysql 5.1 : to add innodb support its ” –with-plugins=innobase” but for 5.0 its “./configure –with-innodb”
g)shell> make
h)shell> make install
i)shell> cp support-files/my-medium.cnf /etc/my.cnf
j)shell> cd /usr/local/mysql
k)shell> chown -R mysql .
l)shell> chgrp -R mysql .
m)shell> bin/mysql_install_db -–user=mysql
n)shell> chown -R root .
o)shell> chown -R mysql var
p)shell> bin/mysqld_safe -–user=mysql &

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
./bin/mysqladmin -u root password 'new-password'

Php installation from source with GD library Support

http://www.php.net/manual/en/install.unix.apache2.php

a) Download the php source from here : http://www.php.net/downloads.php
b) Download the source file in to /tmp directory
c) Here I am gussing the php version is php-5.3.0.tar.gz
d) Tar –xvzf php-5.3.0.tar.gz
e) Cd php-5.3.0
f)

./configure --with-apxs2=/usr/local/apache/bin/apxs --with-mysql=/usr/local/mysql --enable-mbstring --with-gd --with-zlib --with-jpeg-dir --with-png-dir --with-openssl --with-curl --with-mcrypt --with-imap --with-imap-ssl --with-kerberos --with-mysqli=/usr/local/mysql/bin/mysql_config

g) make
h) make install
i) setup your php.ini : cp php.ini-dist /usr/local/lib/php.ini

j) In httpd.conf file.. check for bellow lines

LoadModule php5_module modules/libphp5.so

j) Add the bellow lines in httpd.conf file to allow .php extension.
add bellow lines under directive

Add php extension

 
<FilesMatch "\.phps$">
          SetHandler application/x-httpd-php-source
      </FilesMatch>
 
 <FilesMatch "\.ph(p[2-6]?|tml)$">
          SetHandler application/x-httpd-php
      </FilesMatch>

j) Stop apache /usr/local/apache/bin/apachectl1 stop
k) Restart apache /usr/local/apache/bin/apachectl1 start

Note :

(a) configure: error: xml2-config not found. Please check your libxml2 installation. : yum install libxml2-devel

(b) configure: error: libpng.(a|so) not found.
configure: error: libjpeg.(a|so) not found.
(c) Error : configure: error: utf8_mime2text() has new signature, but U8T_CANONICAL is missing
yum install libc-client-devel*
So it will try to find accurate rpm for your kernel(32/64)

(d) If you have older httpd daemon running , please stop that daemon,Other wise when you will start apache daemon, it will through an error .You can check by bellow command to make sure you don’t have any other httpd is running in background.

ps aux | grep -v grep | grep httpd

If this returns value that means another httpd daemon is running and you can stop it by executing

service httpd stop

Last Update : 14-09-2010

How to optimize Thread Cache variables for MySQL server

Monday, August 17th, 2009

If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. Your CPU will thank you.

Ref : http://jeremy.zawodny.com/blog/archives/000173.html

“As soon as I optimized the thread cache, MySQL’s server load dropped over 50%!”

Ref: http://www.epigroove.com/posts/63/optimize_mysql_the_thread_cache

How to set thread cache :

You should keep your thread cache ( variables name ‘thread_cache_size’) large enough that Threads_created doesn’t increase very often.

To check whether the thread cache is large enough, watch the “Threads_created” status variable and ‘thread_cache_size’ system variables.

mysql> show status like ‘threads_created’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| Threads_created | 2 |
+—————–+——-+
1 row in set (0.00 sec)

Bellow commands will show the size of threads cache size :

mysql> show variables like ‘thread_cache_size’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| thread_cache_size | 12 |
+——————-+——-+
1 row in set (0.00 sec)

A good approach is to watch the Threads_connected variable and try to set thread_cache_size large enough to handle the typical fluctuation in your workload.

Bellow commands will show all threads related status:

mysql> show status like ‘threads_%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 2 |
| Threads_connected | 1 |
| Threads_created | 3 |
| Threads_running | 1 |
+——————-+——-+
4 rows in set (0.00 sec)

How to monitor Threads by cacti : Working on it…

How to optimized MySql server

Monday, August 17th, 2009

Ref: MySQL® 5 Certification Study Guide

Ref: High performace MySQL

  1. The MyISAM Key Cache
  2. The MyISAM key block size
  3. The Thread Cache
  4. The Table Cache

Master to slave and slave to master replication

Wednesday, August 12th, 2009

Master :
1. Create user and give the privileges:
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO reply@’host-name’ IDENTIFIED BY ‘some-pass’;
2. Define the log setting in my.cnf
log-bin = mysql-bin
server_id = 2
3. Restart the server /etc/init.d/mysqld restart
4. check the status of the server by : show master status\G;

Slave :
1. Enable necessary log:
log-bin = mysql-bin
server_id = 3
relay-log = mysql-relay-bin
log_slave_updates =1
read_only =1
2. Create replication user in Slave server: GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO reply@’host-name’ IDENTIFIED BY ‘some-pass’;
3. Change the informaiton in master.info by inserting bellow command in command line
CHANGE MASTER TO MASTER_HOST=’MASTER-HOST-NAME’,
-> MASTER_USER=’reply’,
->MASTER_PASSWORD=’some-pass’,
->MASTER_LOG_FILE=’mysql-bin.000001′ , { you can check log file status from SHOW MASTER STATUS\G output form Master server }
->MASTER_LOG_POS=0;

4. Restart the slave : slave start ;
5. Look for 2 things in SHOW SLAVE STATUS\G; report :
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0

Note : if Slave_IO_Running: = NO , then check the log /var/log/mysqld.log file for further information such as

090428 7:51:18 [ERROR] Slave I/O thread: error connecting to master ‘reply@Master-host-name:3306’: Error: ‘Host ‘your-host-name.’ is not allowed to connect to this MySQL server’ errno: 1130 retry-time: 60 retries : 86400
090428 8:07:18 [Note] Slave I/O thread killed while connecting to master
090428 8:07:18 [Note] Slave I/O thread exiting, read up to log ‘mysql-bin.000001’, position 106

Query Cache in mysqlserver

Wednesday, August 12th, 2009

Ref:MySQL® 5 Certification Study Guide
Ref:Hight Performance Mysql Server(2nd Edition)
Ref:http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
Ref:http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/

How to find out if Query Cache is enabled:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

How to enable Query Cache:

Edit my.cnf file and Add as bellow:
[mysqld]

query_cache_type = 1

 query_cache_size = 10M

query_cache_limit = 2M

Bellow Command will show the variables setting for your mysql server.

mysql> SHOW VARIABLES LIKE 'query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 8388608 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+

query_cache_size is the size of the query cache in bytes. If the size is 0,the cache is disabled
even if query_cache_type is not OFF.

How to setup query cache in runtime :

Ref : http://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html