5 December 2014

MySQL configuration that I am using for developing Drupal in localhost. The config file can be found from /etc/mysql/my.cnf under Linux. I would use it for any apache instance tough. You might get errors if you are not using ArchLinux, but this is simple to tweak to work on any distribution.

Source code viewer
  1. # This is for a MySQL like InnoDB system with 8G of RAM.
  2.  
  3. # In this file, you can use all long options that a program supports.
  4. # If you want to know which options a program supports, run the program
  5. # with the "--help" option.
  6.  
  7. # The following options will be passed to all MariaDB clients
  8. [client]
  9. #password	= your_password
  10. port		= 3306
  11. socket		= /run/mysqld/mysqld.sock
  12.  
  13. # Here follows entries for some specific programs
  14.  
  15. # The database server
  16. [mysqld]
  17. port		= 3306
  18. socket		= /run/mysqld/mysqld.sock
  19. skip-external-locking
  20.  
  21. key_buffer_size = 32M
  22. max_allowed_packet = 16M
  23. table_open_cache = 16384
  24. table_definition_cache = 4096
  25. table_cache = 4096
  26. optimizer_search_depth = 4
  27. sort_buffer_size = 2M
  28. join_buffer_size = 2M
  29. net_buffer_length = 8K
  30. read_buffer_size = 2M
  31. read_rnd_buffer_size = 64M
  32. myisam_sort_buffer_size = 8M
  33.  
  34. #collation-server = utf8_general_ci
  35. # Point the following paths to different dedicated disks
  36. #tmpdir		= /tmp/
  37.  
  38. interactive_timeout = 400
  39. connect_timeout = 10
  40.  
  41. tmp_table_size = 64M
  42. max_heap_table_size = 64M
  43. query_cache_type = 2
  44. query_cache_size = 64M
  45. query_cache_limit = 16M
  46.  
  47. max_connections = 64
  48. open_files_limit = 65535
  49.  
  50. # Replication Master Server (default)
  51. # binary logging is required for replication
  52. log-bin=mysql-bin
  53.  
  54. # binary logging format - mixed recommended
  55. binlog_format=mixed
  56.  
  57. # required unique id between 1 and 2^32 - 1
  58. # defaults to 1 if master-host is not set
  59. # but will not function as a master if omitted
  60. server-id	= 1
  61.  
  62. # Force recover databases.
  63. #innodb_force_recovery = 6
  64.  
  65. # InnoDB tables
  66. innodb_data_home_dir = /var/lib/mysql
  67. innodb_data_file_path = ibdata1:10M:autoextend
  68. innodb_log_group_home_dir = /var/lib/mysql
  69. # You can set .._buffer_pool_size up to 50 - 80 %
  70. # of RAM but beware of setting memory usage too high
  71. # Use mysqltuner to calculate maximum possible memory usage and adjust accordingly.
  72. innodb_buffer_pool_size = 1G
  73. innodb_additional_mem_pool_size = 20M
  74. # Set .._log_file_size to 25 % of buffer pool size
  75. innodb_log_file_size = 256M
  76. innodb_log_buffer_size = 12M
  77. innodb_flush_log_at_trx_commit = 2
  78. innodb_lock_wait_timeout = 180
  79.  
  80. innodb_log_files_in_group = 2
  81. innodb_support_xa = 0
  82. innodb_thread_concurrency = 8
  83. innodb_doublewrite = 0
  84. innodb_file_per_table = 1
  85. innodb_file_format = barracuda
  86. innodb_strict_mode = 1
  87. innodb_fast_shutdown = 0
  88. innodb_flush_method=O_DIRECT 
  89.  
  90. bind-address = 127.0.0.1
  91. skip-name-resolve
  92.  
  93. max_user_connections = 150
  94.  
  95. key_buffer = 16M
  96. key_cache_block_size = 4K
  97.  
  98. bulk_insert_buffer_size  = 256M
  99. myisam_sort_buffer_size  = 64M
  100.  
  101. back_log = 2048
  102.  
  103. open-files = 10000
  104. query_prealloc_size = 65536
  105. query_alloc_block_size = 131072
  106.  
  107. binlog_cache_size = 1M
  108.  
  109. thread_cache_size = 100
  110. thread_concurrency = 4
  111. thread_stack = 192K
  112. transaction-isolation=READ-COMMITTED
  113.  
  114. [mysqldump]
  115. quick
  116. quote-names
  117. max_allowed_packet = 16M
  118.  
  119. [mysql]
  120. no-auto-rehash
  121. # Remove the next comment character if you are not familiar with SQL
  122. #safe-updates
  123.  
  124. [myisamchk]
  125. key_buffer_size = 20M
  126. sort_buffer_size = 20M
  127. read_buffer = 2M
  128. write_buffer = 2M
  129.  
  130. [mysqlhotcopy]
  131. interactive-timeout
Programming Language: MySQL