LinuxHelps.com

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 http://mysqltuner.com/mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

Result

>> MySQLTuner 0.9.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> 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:

skip-innodb

and comment it out:

#skip-innodb

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.

query_cache_size=SIZE


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'; 

Result:

 +------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| query_cache_size | 16777216 |
+------------------+----------+

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

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

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/session.inc.php 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/session.inc.php 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

Replace:
——————————-
session.save_handler = sqlite
session.save_path =/var/cpanel/userhomes/cpanelphpmyadmin/sessions/phpsess.sdb
——————————-
To:
——————————-

session.save_handler=files

session.save_path=/tmp
——————————-

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