Sunday, 17 February 2013

Mysql Database Configuration, Access settings, Innodb configuration, Log slow query

To bind the server Access point
By default it is binded to localhost or
Open /etc/mysql/my.cnf fine

So lets say you have 4-5 machines from which you want to access mysql DB from any of the machine, but you do not want anyone from outside to access this,
bind-address will be Local LAN Ip Address.
bind-address = Local LAN IP Address

If you want only local machine to access mysql DB
bind-address            =

If you want to make it public, remove the bind-address line.

Logging the slow queries
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

Logging the queries which are not using index

Changing the InnoDB configuration
Buffer Pool Size is the memory which you provide to mysql server program.


max_allowed_packet : 
The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get that much worth of data in a single query. In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high. In mysqld max_allowed_packet could be 16M (to be safe, because it doesn't uses memory until required), in mysqldump, max_allowed_packet  could be 128M or may be 512M, depends on your machine and requirement.

If you want mysqldump to work fast

max_allowed_packet  =  64M (Increase this value, default is 16M)

* You can also take take dump faster by passing as a command argument
$ mysqldump -u root -p --max_allowed_packet=512M dbname > dbname.sql

More Ideas on MySQL performance tuning

Post a Comment