MySQL MYI Table Errors


Quary
MySQL ‘MYI’ Table Errors

Error Messages From MySQL Database
Database failed to execute query (query) 1016: Can’t open file: ‘conntrack.MYI’.(errno: 145)
Error Msg: 1034: Incorrect key file for table: ‘sometable’.

Check & Repair Table From MySQL
check table conntrack;
+——————+——-+———-+—————————-+
| Table | Op | Msg_type | Msg_text |
+——————+——-+———-+—————————-+
| yourdb.conntrack | check | warning | Table is marked as crashed |
| yourdb.conntrack | check | status | OK |
+——————+——-+———-+—————————-+

Repair Conntrack Table
repair table conntrack;
+——————+——–+———-+———-+
| Table | Op | Msg_type | Msg_text |
+——————+——–+———-+———-+
| yourdb.conntrack | repair | status | OK |
+——————+——–+———-+———-+

MySQL Daemon Cannot Start


Quary
MySQL Daemon Cannot Start

Check Permissions of Files in /var/lib/mysql & /var/lib/mysql/mysql
chown mysql:mysql /var/lib/mysql -R

Configuration File my.cnf Should Copied to /etc/
cp /etc/mysql/my.cnf /etc/my.cnf

Start MySQL Daemon If You are Getting Error
[ERROR] Can’t start server : Bind on unix socket: Permission denied
[ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?
[ERROR] Aborting

Permissions Of /var/run/mysqld
chown mysql:mysql /var/run/mysqld -R

Start MySQL Service If you Getting Errors
Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist

Install Default Tables From /usr
cd /usr
mysql_install_db –user=mysql –ldata=/var/lib/mysql/

MySQL Binary Logs are Taking Up Huge Disk Space


Query
MySQL binary logs are taking up huge disk space

Open MySQL Configuration File /etc/mysql/my.cnf & Comment Following Lines
#log-bin=mysql-bin
#binlog_format=mixed

Limit Size Log Files in MySQL Configuration File /etc/mysql/my.cnf
expire_logs_days = 10
max_binlog_size  = 100M

Disk Space Use Below Command
mysql -u root -p”PASSWORD” -e “PURGE BINARY LOGS TO ‘mysql-bin.0000xx’;”

Enable MySQL Slow Query Log


Query
Enable MySQL Slow Query Log

Edit Configure File /etc/my.cnf & Add Following Lines
#Slow Query Log
#Log file
log-slow-queries = /var/log/slow-queries-log

#Logs queries longer than 2 sec
long_query_time = 2

#Logs queries not using indexes
log-queries-not-using-indexes TRUE

Change Owner Permissions
chown mysql.mysql

Re-Start MySQL Service
service mysql restart
(or)
service mysqld restart