A blog for Linux Lovers.

Archive for the ‘Mysql’ Category

Posted by sibu on October 10, 2009

MySQL information_schema error when using mysqldump utility

While executing the mysqldump command  you may have experienced following errors.

mysqldump: Got error: 1044: Access denied for user ‘root’@'localhost’ to database ‘information_schema’ when using LOCK TABLES

To quickly fix this problem use the following switch when running mysqldump for MySQL database backups.

mysqldump -u root -p -all-databases -single-transaction > all.sql

Posted by sibu on June 14, 2009

Optimize MySQL Performance With MySQLTuner

MySQLTuner is a high-performance MySQL tuning script written in perl that will provide you with a snapshot of a MySQL server health. Based on the statistics gathered, specific recommendations will be provided that will increase a MySQL servers efficiency and performance. The script gives you automated MySQL tuning that is on the level of what you would receive from a MySQL DBA.

# wget
# chmod +x
# ./


>> MySQLTuner 0.9.0 - Major Hayden
>> Bug reports, feature requests, and downloads at
>> Run with ‘–help’ for additional options and output filtering
Please enter your MySQL administrative login: secret
Please enter your MySQL administrative password: more secret

——– General Statistics ————————————————–
[OK] You have the latest version of MySQLTuner
[OK] Currently running supported MySQL version 5.0.32-Debian_7etch4-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

——– Storage Engine Statistics ——————————————-
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 71M (Tables: 548)

——– Performance Metrics ————————————————-
[--] Up for: 109d 22h 8m 1s (69M q [7.283 qps], 1M conn, TX: 3B, RX: 3B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 2.6M per thread and 58.0M global
[OK] Maximum possible memory usage: 320.5M (62% of installed RAM)
[OK] Slow queries: 0% (39/69M)
[OK] Highest usage of available connections: 63% (63/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/65.0K
[OK] Key buffer hit rate: 100.0%
[OK] Query cache efficiency: 80.6%
[!!] Query cache prunes per day: 30287
[OK] Sorts requiring temporary tables: 0%
[!!] Temporary tables created on disk: 59%
[OK] Thread cache hit rate: 99%
[!!] Table cache hit rate: 0%
[OK] Open file limit used: 12%
[OK] Table locks acquired immediately: 99%

——– Recommendations —————————————————–
General recommendations:

* Enable the slow query log to troubleshoot bad queries
* When making adjustments, make tmp_table_size/max_heap_table_size equal
* Reduce your SELECT DISTINCT queries without LIMIT clauses
* Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

* query_cache_size (> 16M)
* tmp_table_size (> 32M)
* max_heap_table_size (> 16M)
* table_cache (> 64)

Posted by sibu on May 24, 2009

How to change the storage engine to InnoDB?

Edit the /etc/my.cnf file and look for this line:


and comment it out:


Add the following entry in the /etc/my.cnf file

default-storage_engine = InnoDB

Save the file, and restart mysql

/etc/rc.d/init.d/mysqld restart

You can use the following command to check the “storage engine” used in the server.

mysqladmin variables | grep storage_engine

You will see the storage engine as “InnoDB”.

# mysqladmin variables | grep storage_engine
| storage_engine | InnoDB

Posted by sibu on March 22, 2009

Enable query cache in MySQL.

This query cache in mysql will help to improve my mysql performance.


The amount of memory (SIZE) allocated for caching query results.

Options are:

0 : Don’t cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

How to Enable.

Login to mysql as root user.

mysql -u root –p

Setup cache size 16Mb:

mysql> SET GLOBAL query_cache_size = 16777216;

To check this

mysql> SHOW VARIABLES LIKE 'query_cache_size'; 


| Variable_name    | Value    |
| query_cache_size | 16777216 |

Then open my.cnf file, Append config directives as follows:

query_cache_size = 268435456

In this example maximum size of individual query results that can be cached
set to 1048576 using query_cache_limit system variable
Posted by sibu on March 10, 2009

Mysql root password reset.

To reset MySQL root Password

1. Kill/Stop the MySQL Service
# pkill mysql / /etc/init.d/mysqld stop

2. Start mysql in safe mode
# safe_mysqld –skip-grant-tables & [OR]
# mysqld_safe  –skip-grant-tables &

3. Connect to mysql
# mysql mysql

4. Update the root user Password
mysql> UPDATE user SET Password=PASSWORD(’New_Password’) WHERE Host=’localhost’ AND User=’root’;

5. Exit and kill mysqld_safe / safe_mysqld
# pkill mysqld_safe / safe_mysqld

6. Start the MySQL Service
# /etc/init.d/mysql start

7. Connect to your Mysql
# mysql -u root -p
Enter Password: Specify your New_Password

Posted by sibu on March 10, 2009

PhpMyAdmin :: SQLite failed to open/create session database.

This is usually a bug in the PhpMyAdmin configuration, whenever you receive the following error when accessing PhpMyAdmin either from WHM or cPanel,

Error :

Warning: session_start() [function.session-start]: SQLite: failed to open/create session database `/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb’ - unable to open database: /var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/ on line 86

Fatal error: session_start() [<a href='function.session-start'>function.session-start</a>]: Failed to initialize storage module: sqlite (path: /var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/ on line 86

Manually edit /usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini file as follow:

1)Login to shell of the server using root login details and open the file “/usr/local/cpanel/3rdparty/etc/phpmyadmin/php.ini” using vi or any other editor.

2) Search for the [Session] variable & underneath it, change the following

session.save_handler = sqlite
session.save_path =/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb



By default, PhpMyAdmin uses sqlite as as the file handler, but it needs to be changed to files & the location of the session files to be stored needs to be changed to /tmp which is the standard/default place to store temporary files

Once done, save & exit the php.ini file & restart the webserver using “/scripts/restartsrv_httpd”.

You can also ‘chattr’ the file such that it doesn’t get reverted back to original whenever cPanel updates it in the future..

Posted by sibu on March 3, 2009

Repairing a mySQL Database/Table…

Repairing MyISAM mySQL Tables/Databases:

cd /var/lib/mysql/DBNAME
#^ Please note that we assume your mySQL data directory is /var/lib/mysql

myisamchk *.MYI

Repairing ISAM mySQL Tables/Databases:

cd /var/lib/mysql/DBNAME

isamchk *.MYI