Unix
Windows
 Shell
Scripting 		Mysql Web
Hosting
 Forum Home | About us | FAQ
UNix System Adminitrator Page
 
Home -> Mysql -> Troubleshooting Errors
 
MySQL troublehsooting links
http://souptonuts.sourceforge.net/readme_mysql.htm
 
Checking MyISAM tables for errors
Before you repair a table, it is a good idea that you first find out which table is currupted or have errors and then repair only that particular table

myisamchk tbl_name

This finds 99.99% of all errors. What it cannot find is corruption that involves only the data file (which is very unusual). If you want to check a table, you should normally run myisamchk without options or with the -s (silent) option.

myisamchk -m tbl_name

This finds 99.999% of all errors. It first checks all index entries for errors and then reads through all rows. It calculates a checksum for all key values in the rows and verifies that the checksum matches the checksum for the keys in the index tree.

myisamchk -e tbl_name

This does a complete and thorough check of all data (-e means “extended check”). It does a check-read of every key for each row to verify that they indeed point to the correct row. This may take a long time for a large table that has many indexes. Normally, myisamchk stops after the first error it finds. If you want to obtain more information, you can add the -v (verbose) option. This causes myisamchk to keep going, up through a maximum of 20 errors.

myisamchk -e -i tbl_name

This is like the previous command, but the -i option tells myisamchk to print additional statistical information
 
 
Repairing a mysql table
From time to time mysql databases can get corrupt, whether it is due to lack of disk space, power failure, or just an error.

Repairing MyISAM myql tables

cd /var/lib/mysql/DBNAME

/etc/init.d/mysql stop

myisamchk -r -q *.MYI
Or
myisamchk -r -f --safe-recover *.MYI                     # -r -f means `recovery in force mode'

/etc/init.d/mysql start

This will attempt to repair the index file without touching the datafile. If the datafile contains everything that it should and the delete links point at the correct locations within the datafile, this should work, and the table is fixed. Start repairing the next table. Otherwise, use the following procedure:

1. Make a backup of the datafile before continuing.

2. Use myisamchk -r tbl_name (-r means ``recovery mode''). This will remove incorrect records and deleted records from the datafile and reconstruct the index file.

3. If the preceding step fails, use myisamchk --safe-recover tbl_name. Safe recovery mode uses an old recovery method that handles a few cases that regular recovery mode doesn't (but is slower).

Note: Please note that we assume your mysql data directory is /var/lib/mysql
 
Recovering all tables in a database
myisamchk /var/lib/mysql/database_dir/*.MYI
 
Repairing all tables in all databse in a single command
myisamchk -r -q /var/lib/mysql/*/*.MYI                      # -r -q means `quick recovery mode', use -s for to quite mode

Note: Please note that we assume your mysql data directory is /var/lib/mysql
 
Mysql databse and its components
File Name Function of the file
`tbl_name.frm' Table definition (form) file
`tbl_name.MYD' Datafile
`tbl_name.MYI' Index file
 
Check mysql to see which user and which database is using resources on your linux server with this command:
watch -n 1 "uptime; free -m; mysqladmin processlist"
 
How to resolve the error messages "user **** has already more than 'max_user_connections' active connections"
service mysql stop
vi /etc/my.cnf
increase the value of variable "max_user_connections=???"
service mysql start
 
Untitled Document
Free Link Exchange  Cheap Hosting  Domain Registration  Affiliate Program  
Terms & Conditions | Copyright © 2004-2007