The following is a sample my.cnf configuration file. It includes some common and uncommon parameters, along with occasional comments.<\/p>\n
You can use this file, and tune as appropriate for your machine, operating system, and application needs.<\/p>\n
In particular, note the high values set for the InnoDB related parameters, below, and change them to match your system and application’s requirements. You have been warned.<\/p>\n
[client]
\n #password = [your_password]
\nport = 3306<\/strong>
\n# The following location is not the default location for many
\n# pre-packaged utilities. For example, mytop or mysqlreport
\n# (or any Perl or Python based client, for that matter), will
\n# NOT look in the \/tmp path for the mysql.sock file.
\n# The value is set to common location by your RPM or DEB
\n# package on GNU\/Linux, yet defaults to the following in the
\n# .tgz mysql-server downlaod files.
\n socket = \/tmp\/mysql.sock<\/strong><\/p>\ndefault-character-set=utf8<\/strong><\/p>\n
[mysqld]<\/strong><\/p>\n
# For a new installation, best to use a strict sql_mode:<\/p>\n
sql_mode=TRADITIONAL
\ndatadir= [path to mysql data]
\n<\/strong># I prefer working case-sensitive. However, some 3rd party tools
\n# demand case insensitivity. If you’re working with such a tool,
\n# uncomment the following line.
\n#lower_case_table_names=1<\/strong><\/p>\n# 5-10 minutes timeout is usually more than required for idle
\n# connections. If a connection can be idle for so long, then
\n# creating a new connection is usually very cheap. This applies
\n# for many common web based applications. However, depending
\n# on your application, you may wish to set this parameter to a
\n# higher value.
\n wait_timeout=300<\/strong><\/p>\n# The following is unfortunately still used on many instances. If
\n# you need backwards compatability, you may need to uncomment
\n# the following line.
\n #old_passwords=1<\/strong><\/p>\n# generic configuration options
\n port = 3306<\/strong>
\n# Make sure this is the same location as in the [client] section
\n socket = \/tmp\/mysql.sock<\/strong><\/p>\nback_log = 50
\nmax_connect_errors = 10<\/strong><\/p>\n# Resources:
\n# The following values are liberal. You may reduce them
\n# according to your needs.
\n# NOTE: verify that your OS’s open files limit is at least as
\n# specified here.
\n# I usually set ‘ulimit -n’ to allow for 8192 files to mysql.
\nopen_files_limit = 8000
\n<\/strong># The following is usually cheap, so go ahead and set a high value.
\ntable_cache = 2048
\n<\/strong># Connections take memory, so don;t be too liberal here. A common,
\n# well tuned application, using proper connection pools, shouldn’t
\n# require more than 1000 concurrent connections.
\nmax_connections = 500<\/strong><\/p>\n# Memory:
\n# When required, the following parameters will be used to allocate
\n# more memory. The value specified is always the amount of memory
\n# allocated, regardless of the real need.
\nbinlog_cache_size = 1M
\nmax_heap_table_size = 64M
\nsort_buffer_size = 8M
\njoin_buffer_size = 8M
\n<\/strong># The following value sets an upper limit only: MySQL will only
\n# allocate as much as required. Setting this parameters to a high
\n# value may be required if you’re handling very large statement,
\n# such as queries reading\/writing BLOBs.
\nmax_allowed_packet = 16M<\/strong><\/p>\n# Even on a very busy server, a thread cache of 32 threads is
\n# usually enough to make for good thread recycling. I rarely go
\n# above this number.
\nthread_cache_size = 32
\nthread_concurrency = 8<\/strong><\/p>\n# Query cache:
\n# Make sure you really need the query cache. I’ve seen many
\n# applications where the query cache almost utterly unused
\n# (due to frequent invalidation). Check for your hit:insert ratio.
\n# A query cache type of value “2” (DEMAND) is a more programmatic
\n# oriented setting, where most queries are not cached, but a few,
\n# recognized to benefit from the query cache, are executed with
\n# the SQL_CACHE flag.
\nquery_cache_type = 1
\nquery_cache_size = 32M
\nquery_cache_limit = 2M<\/strong><\/p>\nft_min_word_len = 4
\n<\/strong># I usually go with InnoDB, which is ACID compliant and crash-safe.
\ndefault_table_type = InnoDB<\/strong><\/p>\nthread_stack = 192K<\/strong><\/p>\n
transaction_isolation = REPEATABLE-READ
\ntmp_table_size = 64M<\/strong><\/p>\n# Logs
\n# Having log-bin enabled allows for replication and for point in time
\n# recovery. It’s a good idea to always have binary logs at hand.
\n# binary logs can also be reviewed to see what kind of modifications
\n# are made on your database (the general query log also logs
\n# SELECTs and other queries, which do not appear in the binary log)
\n# I usually name the logs after my host name. When dealing with
\n# replication, having master and slave logs named after the machines
\n# on which they are running makes for clearer distinction between
\n# them.
\nlog-bin=mymachine-bin
\nrelay-log = mymachine-relay-bin
\nrelay-log-index = mymachine-relay-bin.index
\n<\/strong># Since a slave can suddenly be promoted to Master’s role, I always
\n# take care that it also has the binary logs prepared.
\nlog_slave_updates
\nlog-error=mymachine-mysql-error.log
\nlog_slow_queries
\nlong_query_time = 2
\nlog-queries-not-using-indexes
\n<\/strong># Automatically remove binary logs. Make sure this number is not too
\n# high and not too low for you, depending on your backups frequency
\n# and maximum slave lag.
\nexpire_logs_days=7<\/strong><\/p>\n# Replication related settings:
\n# Unique server identification number between 1 and 2^32-1.
\nserver-id = [a unique number]
\n<\/strong># Setting the report_host allows for SHOW SLAVE HOSTS on the master
\n# to see which slaves are connected.
\nreport_host=[machine’s ip]
\n<\/strong># Limit the total size of the relay logs: set this value to a high enough
\n# value. When the slave can’t keep up with the master, the relay logs
\n# fill up. If no limit is set, all disk space can be consumed.
\n# If a value too low is set, the master’s binary logs cannot (shouldn’t)
\n# be purged, and so the master gets to have a disk space problem. So
\n# provide a high enough value, and monitor your disk space!
\nrelay_log_space_limit=10G<\/strong><\/p>\n# MyISAM:
\nkey_buffer_size = 32M
\nread_buffer_size = 2M
\nread_rnd_buffer_size = 16M
\nbulk_insert_buffer_size = 64M
\nmyisam_sort_buffer_size = 128M
\nmyisam_max_sort_file_size = 10G
\nmyisam_max_extra_sort_file_size = 10G
\nmyisam_repair_threads = 1
\nmyisam_recover = force,backup<\/strong><\/p>\nconcurrent_insert=2
\n<\/strong><\/p>\n# INNODB:<\/p>\n
innodb_file_per_table
\n<\/strong># Your machine’s memory capacity dictates many of the following
\n# values. ***Do NOT take these values as they are. Change them***
\ninnodb_buffer_pool_size = 10G
\ninnodb_additional_mem_pool_size = 16M
\ninnodb_data_file_path = ibdata1:50M:autoextend
\ninnodb_file_io_threads = 4
\n<\/strong># Keep the following one commented, unless you need to recover
\n# from disaster.
\n#innodb_force_recovery=4
\ninnodb_thread_concurrency = 8
\n<\/strong># Set “1” for full ACID compliance. Set “2” for IO performance gain
\n# (will only flush transaction log to disk once per second, instead
\n# of at each commit). If you have battery backed disk cache ,set
\n# to “1” and enjoy both worlds.
\ninnodb_flush_log_at_trx_commit = 1
\n<\/strong># Keep the following one commented, unless you really have an
\n# emergency. Fast shutdown makes for slower start up time.
\n#innodb_fast_shutdown
\ninnodb_log_buffer_size = 8M
\n<\/strong># The default here is 5M, which is really too low for our modern
\n# machines.
\ninnodb_log_file_size = 128M
\ninnodb_log_files_in_group = 2
\ninnodb_max_dirty_pages_pct = 90
\n<\/strong># Consult The manual. The desired values for the following
\n# parameter change according to your OS type, OS version and
\n# hardware types. Benchmark after changing it!
\n#innodb_flush_method=O_DIRECT
\ninnodb_lock_wait_timeout = 120<\/strong><\/p>\n[mysqldump]
\nquick
\nmax_allowed_packet = 64M<\/strong><\/p>\ndefault-character-set=utf8<\/strong><\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
The following is a sample my.cnf configuration file. It includes some common and uncommon parameters, along with occasional comments. You can use this file, and tune as appropriate for your machine, operating system, and application needs. In particular, note the high values set for the InnoDB related parameters, below, and change them to match your […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,10],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-installation"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-6","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=6"}],"version-history":[{"count":18,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6\/revisions"}],"predecessor-version":[{"id":19,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/6\/revisions\/19"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}