manopj1

my.cnf

Aug 16th, 2020
31
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. # MariaDB database server configuration file.
  2. #
  3. # You can copy this file to one of:
  4. # - "/etc/mysql/my.cnf" to set global options,
  5. # - "~/.my.cnf" to set user-specific options.
  6. #
  7. # One can use all long options that the program supports.
  8. # Run program with --help to get a list of available options and with
  9. # --print-defaults to see which it would actually understand and use.
  10. #
  11. # For explanations see
  12. # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
  13.  
  14. # This will be passed to all mysql clients
  15. # It has been reported that passwords should be enclosed with ticks/quotes
  16. # escpecially if they contain "#" chars...
  17. # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
  18. [client]
  19. port = 3306
  20. socket = /var/run/mysqld/mysqld.sock
  21. default-character-set = utf8mb4
  22.  
  23. # Here is entries for some specific programs
  24. # The following values assume you have at least 32M ram
  25.  
  26. # This was formally known as [safe_mysqld]. Both versions are currently parsed.
  27. [mysqld_safe]
  28. socket = /var/run/mysqld/mysqld.sock
  29. nice = 0
  30.  
  31. [mysqld]
  32. #
  33. # * Basic Settings
  34. #
  35. user = mysql
  36. pid-file = /var/run/mysqld/mysqld.pid
  37. socket = /var/run/mysqld/mysqld.sock
  38. port = 3306
  39. basedir = /usr
  40. datadir = /var/lib/mysql
  41. tmpdir = /tmp
  42. lc_messages_dir = /usr/share/mysql
  43. lc_messages = en_US
  44. skip-external-locking
  45.  
  46. skip-grant-tables
  47. # Instead of skip-networking the default is now to listen only on
  48. # localhost which is more compatible and is not less secure.
  49. bind-address = 172.31.28.210
  50. #
  51. # * Fine Tuning
  52. #
  53. max_connections = 1024
  54. connect_timeout = 5
  55. wait_timeout = 60
  56. interactive_timeout = 5000
  57. max_allowed_packet = 16M
  58. thread_cache_size = 128
  59. join_buffer_size = 4M
  60. sort_buffer_size = 4M
  61. bulk_insert_buffer_size = 16M
  62. tmp_table_size = 256M
  63. max_heap_table_size = 256M
  64. #
  65. # * MyISAM
  66. #
  67. # This replaces the startup script and checks MyISAM tables if needed
  68. # the first time they are touched. On error, make copy and try a repair.
  69. myisam_recover_options = BACKUP
  70. key_buffer_size = 512M
  71. open-files-limit = 60000
  72. table_open_cache = 48000
  73. myisam_sort_buffer_size = 512M
  74. concurrent_insert = 2
  75. read_buffer_size = 2M
  76. read_rnd_buffer_size = 1M
  77. back_log = 9500
  78. table_definition_cache = 2000
  79. table_open_cache_instances = 16
  80. #
  81. # * Query Cache Configuration
  82. #
  83. # Cache only tiny result sets, so we can fit more in the query cache.
  84. query_cache_limit = 128K
  85. query_cache_size = 64M
  86. query_cache_min_res_unit = 2K
  87. # for more write intensive setups, set to DEMAND or OFF
  88. query_cache_type = DEMAND
  89. #
  90. # * Logging and Replication
  91. #
  92. # Both location gets rotated by the cronjob.
  93. # Be aware that this log type is a performance killer.
  94. # As of 5.1 you can enable the log at runtime!
  95. #general_log_file = /var/log/mysql/mysql.log
  96. #general_log = 1
  97. #
  98. # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
  99. #
  100. # we do want to know about network errors and such
  101. log_warnings = 2
  102. #
  103. # Enable the slow query log to see queries with especially long duration
  104. #slow_query_log[={0|1}]
  105. slow_query_log_file = /var/log/mysql/mariadb-slow.log
  106. long_query_time = 5
  107. #log_slow_rate_limit = 1000
  108. log_slow_verbosity = query_plan
  109. log-queries-not-using-indexes = 1
  110. #log-queries-not-using-indexes
  111. #log_slow_admin_statements
  112. #
  113. # The following can be used as easy to replay backup logs or for replication.
  114. # note: if you are setting up a replication slave, see README.Debian about
  115. # other settings you may need to change.
  116. #server-id = 1
  117. #report_host = master1
  118. #auto_increment_increment = 2
  119. #auto_increment_offset = 1
  120. log_bin = /var/log/mysql/mariadb-bin
  121. log_bin_index = /var/log/mysql/mariadb-bin.index
  122. # not fab for performance, but safer
  123. sync_binlog = 1
  124. expire_logs_days = 10
  125. max_binlog_size = 100M
  126. # slaves
  127. #relay_log = /var/log/mysql/relay-bin
  128. #relay_log_index = /var/log/mysql/relay-bin.index
  129. #relay_log_info_file = /var/log/mysql/relay-bin.info
  130. #log_slave_updates
  131. #read_only
  132. #
  133. # If applications support it, this stricter sql_mode prevents some
  134. # mistakes like inserting invalid dates etc.
  135. #sql_mode = NO_ENGINE_SUBSTITUTION,TRADITIONAL
  136. #
  137. # * InnoDB
  138. #
  139. # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
  140. # Read the manual for more InnoDB related options. There are many!
  141. default_storage_engine = InnoDB
  142. innodb_file_format = Barracuda
  143. innodb_large_prefix = 'on'
  144.  
  145. innodb_buffer_pool_size = 16G
  146. innodb_buffer_pool_chunk_size=2G
  147. innodb_log_buffer_size = 64M
  148. innodb_buffer_pool_instances=16
  149. innodb_file_per_table = 1
  150. innodb_open_files = 48000
  151. innodb_io_capacity = 30000
  152. innodb_flush_method = O_DIRECT
  153. innodb_log_file_size = 2G
  154. innodb_log_files_in_group=2
  155. innodb_undo_log_truncate=off
  156.  
  157. innodb_doublewrite= 1
  158. innodb_thread_concurrency=6
  159. innodb_flush_log_at_trx_commit= 1
  160. innodb_max_dirty_pages_pct=90
  161. innodb_max_dirty_pages_pct_lwm=10
  162. innodb_use_native_aio=1
  163. innodb_stats_persistent = 1
  164. innodb_page_cleaners=4
  165. innodb_change_buffer_max_size=15
  166. innodb_flushing_avg_loops=5
  167.  
  168. innodb_adaptive_flushing = 1
  169. innodb_flush_neighbors = 2
  170. innodb_read_io_threads = 16
  171. innodb_write_io_threads = 16
  172.  
  173. innodb_purge_threads=4
  174. innodb_adaptive_hash_index=0
  175. max_prepared_stmt_count=100000
  176. innodb_monitor_enable = '%'
  177. performance_schema=ON
  178. skip-name-resolve=1
  179. key_cache_age_threshold=7200
  180. key_cache_division_limit=50
  181. key_cache_block_size=16K
  182.  
  183.  
  184.  
  185. character-set-server = utf8mb4
  186. collation-server = utf8mb4_unicode_ci
  187. skip-character-set-client-handshake
  188. #
  189. # * Security Features
  190. #
  191. # Read the manual, too, if you want chroot!
  192. # chroot = /var/lib/mysql/
  193. #
  194. # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
  195. #
  196. #
  197. # ssl-ca=/etc/mysql/cacert.pem
  198. # ssl-cert=/etc/mysql/server-cert.pem
  199. # ssl-key=/etc/mysql/server-key.pem
  200.  
  201. #
  202. # * Galera-related settings
  203. #
  204. [galera]
  205. # Mandatory settings
  206. #wsrep_on=ON
  207. #wsrep_provider=
  208. #wsrep_cluster_address=
  209. #binlog_format=row
  210. #default_storage_engine=InnoDB
  211. #innodb_autoinc_lock_mode=2
  212. #
  213. # Allow server to accept connections on all interfaces.
  214. #
  215. #bind-address=0.0.0.0
  216. #
  217. # Optional setting
  218. #wsrep_slave_threads=1
  219. #innodb_flush_log_at_trx_commit=0
  220.  
  221. [mysqldump]
  222. quick
  223. quote-names
  224. max_allowed_packet = 1024M
  225. #net_read_timeout=3600
  226. #net_write_timeout=3600
  227. [mysql]
  228. [mysql]
  229. #no-auto-rehash # faster start of mysql but no tab completion
  230. default-character-set = utf8mb4
  231. [isamchk]
  232. key_buffer = 32M
  233.  
  234. #
  235. # * IMPORTANT: Additional settings that can override those from this file!
  236. # The files must end with '.cnf', otherwise they'll be ignored.
  237. #
  238. !include /etc/mysql/mariadb.cnf
  239. !includedir /etc/mysql/conf.d/
  240.  
  241.  
  242.  
  243.  
  244.  
  245.  
  246.  
RAW Paste Data