Archive for the ‘ MySQL ’ Category

MySQL repair

When you get the following error when repairing database —

error    : Incorrect key file for table './DB/table'; try to repair it
Error    : Incorrect key file for table 'DB_table'; try to repair it

Try the following steps

1. Stop mysql

2. rename the .myi file to something else, like “DISK.BAD”.

  mv /var/lib/mysql/database/*.MYI /root/database_MYI/

3. restart mysql

4. REPAIR TABLE — MySQL will see that the .MYI file is missing and rebuild it. But this time, it will be in a different spot on disk.

  for i in `cat /root/tbs`;do mysql -e "REPAIR TABLE database.$i USE_FRM;";done

when you get the following error

error    : Can't create new tempfile: './DB/table.TMD'
status   : Operation failed

  myisamchk -r -v -f  tables.MYI

Repair MySQL Database

Following command will repair your all databases on server.

  • myisamchk -r /var/lib/mysql/*/*.MYI
  • /etc/rc.d/init.d/mysql restart

Following command will show if you need to repair your database or not

  • myisamchk –check /var/lib/mysql/*/*.MYI

Then try ‘safe-recover’ first:

  • myisamchk –safe-recover /var/lib/mysql/*/*.MYI


  • myisamchk –recover /var/lib/mysql/*/*.MYI


  • myisamchk –safe-recover –extend-check –force /var/lib/mysql/*/*.MYI


  • myisamchk –recover –extend-check –force /var/lib/mysql/*/*.MYI

Check mysql syntax error in /etc/my.cnf

To check for any syntax error in /etc/my.cnf. I added a non-existent option to –log-warnings9 to /etc/my.cnf.

# /usr/sbin/mysqld --help --verbose 1>/dev/null
120413 23:18:36 [ERROR] /usr/sbin/mysqld: unknown option '--log-warnings9'

I corrected it to –log-warnings and ran again. It showed no warning. So we are safe to restart mysql.

[root@]# /usr/sbin/mysqld --help --verbose 1>/dev/null

MySQL Performance Tuning Primer Script

MySQL Performance Tuning Primer Script

This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
to produce sane recomendations for tuning server variables.
It is compatable with all versions of MySQL 3.23 and higher (including 5.1).

Currently it handles recomendations for the following:

  • Slow Query Log
  • Max Connections
  • Worker Threads
  • Key Buffer
  • Query Cache
  • Sort Buffer
  • Joins
  • Temp Tables
  • Table (Open & Definition) Cache
  • Table Locking
  • Table Scans (read_buffer)
  • Innodb Status

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


row-level locking table-level locking


supports transaction does not support transactions


foreign key constraints no foreign key constraints


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


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


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


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


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

MySQL: Analyze slow query log using mysqldumpslow

First enable slow query logging, then generate a slow query and finally look at the slow query log.

This program parses and summarizes a ‘slow query log’.
-v verbose
-d debug
what to sort by (t, at, l, al, r, ar etc)
-r reverse the sort order (largest last instead of first)
just show the top n queries
-a don’t abstract all numbers to N and strings to ‘S’
abstract numbers with at least n digits within names
grep: only consider stmts that include this string
hostname of db server for *-slow.log filename (can be wildcard)
name of server instance (if using mysql.server startup script)
-l don’t subtract lock time from total time

1. mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_query.txt
It will put top ten slow query in file /tmp/top_ten_slow_query.txt
2. mysqldumpslow -a -s c -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_repeat_slow_query.txt
It will put top ten repeat slow query in file top_ten_repeat_slow_query.txt

DB not Listed in Cpanel after Mysql upgrade

When mysql is upgraded, the DB cache and dbusermap in cpanel may be lost. We need to recreate that

/usr/local/cpanel/bin/setupdbmap –> To recreate the dbusermap

/scripts/update_db_cache –> To recreate DB cache

If these do not work with the existing DB cache, move the existing db cache and run these script, it will create the cache automatically

mv /var/cpanel/databases /var/cpanel/databases.old and then run these in order /usr/local/cpanel/bin/setupdbmap /scripts/update_db_cache