Optimal my.cnf settings for large sites

log-slow-queries = /var/log/mysql_slow.log
open_files_limit =24000
thread_concurrency = 8
query_cache_size = 32M
thread_cache_size = 8
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 512
max_allowed_packet = 32M
key_buffer = 384M
max_heap_table_size = 64M
query_cache_limit = 2M

innodb_file_per_table = 1
innodb_additional_mem_pool_size = 16M
innodb_flush_log_at_trx_commit = 0


InnoDB: Error: log file ./ib_logfile0 is of different size

InnoDB is insanely picky about it's config; if something's not right, it'll just give up and go home. To make a change to the log file size without data loss:

  1. Revert any config changes you've made to the log file size and start MySQL again.
  2. In your running MySQL: SET GLOBAL innodb_fast_shutdown=0;
  3. Stop MySQL
  4. Make the configuration change to the log file size.
  5. Delete both log files.
  6. Start MySQL. It will complain about the lack of log files, but it'll create them and all will be well.


How to properly close MySQL port 3306 from outer networks

To close port #3306 from outside networks add this to /etc/my.cnf' under the [mysqld] section:


then run 'service mysqld restart' and then 'netstat -tln' to see if the port comes up in the list of open ports:

[email protected]:/var/named#
netstat -tln
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0* LISTEN

Enable log files

MySQL provides many different log files to capture various information that you can use for performance tuning or during problem determination.

To enable the log files, do the following:
Create the /etc/my.cnf options file with the following definitions that enable the log files automatically:


By default, the logs are placed in the data directory /pathname/mysql/data. The log names default to the name of the host system followed by a log identifier such as -bin.


Subscribe to RSS - mysql