Posts Tagged ‘ MySQL ’

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

OR

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

Forceful

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

OR

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

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
[root@]#

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

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

MySQL not reading Russian

For some reason i cant read anything written in Russian language in my database thru Phpmyadmin. I guess it has something to do with connection/database collation char sets but which one exactly ?

solution I found to work was to add the following line to /etc/my.cnf :

init-connect=SET NAMES binary

I think I’m missing something else here… you might

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.

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

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