Best mysql configurations

In this post i will describe what are the best configurations for mysql server, you can edit your my.cnf file to reflect the changes. To do so follow the below steps:

  • Login to your server via putty or shell/terminal with root access.
  • Execute following commands:
# cd /etc/mysql
# nano my.cnf
  • Now you are in text editor with opened file my.cnf here you can copy and paste the below code into it and then save it.
#skip-innodb
[mysqld]
#socket=/path/to/mysql.sock
#datadir=/var/lib/mysql
skip-locking
local-infile=0

# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
max_connections=60
interactive_timeout=100
wait_timeout=100
connect_timeout=100
table_cache=512
thread_cache=32

[mysqldump]
quick
max_allowed_packet = 2M
[mysql]
no-auto-rehash

[isamchk]
key_buffer=50M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 4M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 4M
  • Now press CTRL+O and the press [Enter] settings will be saved and then execute following below command:
/etc/init.d/mysql restart
OR
service mysql restart
OR
restart mysql
  • You can find your my.cnf file in mysql directory it might be different from the location that i mentioned above.
You are welcome to ask any question regarding above tutorial…