Correct MySQL Folder Permissions


Quary
Correct MySQL Folder Permissions

Setting Permissions
chmod -R 660 /var/lib/mysql/[DBNAME]
chmod 700 /var/lib/mysql/[DBNAME]

Advertisements

Changing TimeZone


Quary
Changing TimeZone

Zone Information File is Located at /usr/share/zoneinfo
cd /etc/
ln -sf /usr/share/zoneinfo/EST localtime

Setting Up IST Time Zone (Asia/Calcutta)
ln -sf /usr/share/zoneinfo/Asia/Calcutta localtime

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