mysql best configs

From Lawrence Amer, 4 Months ago, written in Plain Text, viewed 70 times.
URL https://secploit.com/view/4e3ed2c6 Embed
Download Paste or View Raw
  1. steps to do it after putting content
  2.  
  3.  
  4.  
  5. Login via SSH as root user and execute the following commands, line by line after you made
  6. configs done
  7.  
  8. $ rm -rvf /var/lib/mysql/ib_logfile*
  9. $ touch /var/lib/mysql/mysql.sock
  10. $ touch /var/lib/mysql/mysql.pid
  11. $ chown -R mysql:mysql /var/lib/mysql/*
  12. $ mkdir -p /var/run/mysqld
  13. $ touch /var/run/mysqld/mysqld.pid
  14. $ /scripts/restartsrv_mysql
  15.  
  16. ######mysql configs for best performance
  17.  
  18. [client]
  19. port                            = 3306
  20. socket                          = /var/lib/mysql/mysql.sock
  21.  
  22. [mysqld-safe]
  23. socket                          = /var/lib/mysql/mysql.sock
  24. nice                            = 0
  25.  
  26. [mysqld]
  27. # Basic
  28. basedir                         = /usr
  29. bind-address                    = 127.0.0.1 # Comment out if you want remote servers to connect to this server's MySQL instance
  30. datadir                         = /var/lib/mysql
  31. lc-messages-dir                 = /usr/share/mysql
  32. max-allowed-packet              = 32M
  33. max-connect-errors              = 1000000
  34. pid-file                        = /var/lib/mysql/mysql.pid
  35. port                            = 3306
  36. skip-external-locking
  37. skip-name-resolve
  38. socket                          = /var/lib/mysql/mysql.sock
  39. tmpdir                          = /tmp
  40. user                            = mysql
  41.  
  42. # InnoDB Settings
  43. default-storage-engine          = InnoDB
  44. innodb-buffer-pool-instances    = 2     # Use 1 instance per 1GB of InnoDB pool size
  45. innodb-buffer-pool-size         = 2G    # Use up to 70-80% of RAM & optionally check if /proc/sys/vm/swappiness is set to 0
  46. innodb-file-per-table           = 1
  47. innodb-flush-log-at-trx-commit  = 0
  48. innodb-flush-method             = O_DIRECT
  49. innodb-log-buffer-size          = 16M
  50. innodb-log-file-size            = 128M
  51. #innodb-thread-concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better contain CPU usage
  52.                                         # E.g. if your system has 8 CPUs, try 6 or 7 and check the overall load from MySQL.
  53.  
  54. # MyISAM Query Cache Settings
  55. query-cache-limit               = 4M    # UPD
  56. query-cache-size                = 48M   # UPD
  57. query-cache-type                = 1
  58.  
  59. key-buffer-size                 = 48M   # UPD
  60.  
  61. low-priority-updates            = 1
  62. concurrent-insert               = 2
  63.  
  64. # Common
  65. max-connections                 = 100   # UPD
  66. back-log                        = 512
  67.  
  68. wait-timeout                    = 300
  69. interactive-timeout             = 300
  70.  
  71. join-buffer-size                = 2M    # UPD
  72. read-buffer-size                = 2M    # UPD
  73. read-rnd-buffer-size            = 4M    # UPD
  74. sort-buffer-size                = 4M    # UPD
  75.  
  76. thread-cache-size               = 100   # UPD (most of the times you probably won't need to change this)
  77. thread-stack                    = 192K
  78.  
  79. max-heap-table-size             = 128M
  80. tmp-table-size                  = 128M
  81.  
  82. table-definition-cache          = 6000  # UPD
  83. table-open-cache                = 6000  # UPD
  84. open-files-limit                = 18000 # UPD
  85.  
  86. ft-min-word-len                 = 3     # Minimum length of words to be indexed for search results
  87.  
  88. expire-logs-days                = 7
  89. log-error                       = /var/lib/mysql/mysql_error.log
  90. log-queries-not-using-indexes   = 1
  91. long-query-time                 = 5
  92. max-binlog-size                 = 100M
  93. slow-query-log                  = 1
  94. slow-query-log-file             = /var/lib/mysql/mysql_slow.log
  95.  
  96. [mysqldump]
  97. quick
  98. quote-names
  99. max-allowed-packet              = 16M
  100.  
  101. [mysql]
  102.  
  103. [isamchk]
  104. key-buffer-size                 = 16M

Reply to "mysql best configs"

Here you can reply to the paste above