MySQL--f配置文件模板

Introduce

The MySQL and MariaDB default configuration is not very great for production use. Some of the default values infshould be changed when you need it for business critical applications.

The following MySQL configuration file is in our opinion a good average configuration file for MySQL, Galera Cluster, MariaDB and Percona Server. For MySQL servers more performance tuning is not need in most cases.

Download the sample MySQL and MariaDB configuration file withwgetdirectly fromhere.

MySQL和MariaDB的默认配置对于生产环境使用来说并不是很好。当您的关键业务需要应用程序时，应更改f中的某些默认值。 我们认为以下MySQL配置文件是MySQL，Galera Cluster，MariaDB和Percona Server的良好平均配置文件。对于MySQL服务器，在大多数情况下不需要更多性能调整。 直接从这里下载带有wget的示例MySQL和MariaDB配置文件。

f模板

## FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server# Location: /etc/f or /etc/mysql/f# This template is intended to work with MySQL 5.6 and newer and MariaDB 10.0 and newer# Get most recent updated from here:# /mysql-configuration-file-sample#[client]port = 3306socket= /var/run/mysqld/mysql.sock# Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise[mysql]no_auto_rehashmax_allowed_packet = 16Mprompt= '\u@\h [\d]> ' # 'user@host [schema]> 'default_character_set= utf8 # Possibly this setting is correct for most recent Linux systems[mysqldump]max_allowed_packet = 16M[mysqld_safe]# Becomes sooner or later obsolete with systemdopen_files_limit= 8192 # You possibly have to adapt your O/S settings as welluser = mysqllog-error = &lt;hostname&gt;_error.log# Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld[mysqld]# Connection and Thread variablesport = 3306socket= /var/run/mysqld/mysql.sock# Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise# basedir = /usr# datadir = /var/lib/mysql# tmpdir= /tmp# innodb_tmpdir = /var/lib/mysql # MySQL 5.7max_allowed_packet = 16Mdefault_storage_engine = InnoDB# explicit_defaults_for_timestamp = 1 # MySQL 5.6, test carefully! This can have an impact on application.# disable_partition_engine_check = true # Since MySQL 5.7.17 to 5.7.20. To get rid of nasty message in error log# character_set_server = utf8 # If you prefer utf8# collation_server= utf8_general_cimax_connections= 505 # Values < 1000 are typically goodmax_user_connections = 500 # Limit one specific user/applicationthread_cache_size = 505 # Up to max_connections makes sense# Query Cachequery_cache_type= 1# Set to 0 to avoid global QC Mutexquery_cache_size= 32M # Avoid too big (> 128M) QC because of QC clean-up lock!# Session variablessort_buffer_size= 2M # Could be too big for many small sortstmp_table_size = 32M # Make sure your temporary results do NOT contain BLOB/TEXT attributesread_buffer_size= 128k # Resist to change this parameter if you do not know what you are doingread_rnd_buffer_size = 256k # Resist to change this parameter if you do not know what you are doingjoin_buffer_size= 128k # Resist to change this parameter if you do not know what you are doing# Other buffers and cachestable_definition_cache = 1400 # As big as many tables you havetable_open_cache= 2000 # connections x tables/connection (~2)table_open_cache_instances= 16 # New default in 5.7# MySQL error loglog_error = &lt;hostname&gt;_error.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld# log_timestamps = SYSTEM# MySQL 5.7, equivalent to old behaviourlog_warnings = 2# log_error_verbosity = 3# MySQL 5.7, equivalent to log_warnings = 2# innodb_print_all_deadlocks= 1# wsrep_log_conflicts = 1# for Galera only!# Slow Query Logslow_query_log_file = &lt;hostname&gt;_slow.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqldslow_query_log = 0log_queries_not_using_indexes = 0long_query_time= 0.5min_examined_row_limit = 100# General Query Loggeneral_log_file= &lt;hostname&gt;_general.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqldgeneral_log= 0# Performance Schema# performance_schema = ON # on some older MariaDB 10 releasesperformance_schema_consumer_events_statements_history_long = ON# MySQL 5.6 and newer# Binary logging and Replicationserver_id = 42log_bin = &lt;hostname&gt;_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld# master_verify_checksum = ON # MySQL 5.6binlog_cache_size = 1Mbinlog_stmt_cache_size = 1Mmax_binlog_size= 128M # Make bigger for high traffic to reduce number of filessync_binlog= 0# Set to 1 or higher to reduce potential loss of binary-log dataexpire_logs_days= 5# We will survive easter holidaysbinlog_format = ROW # Use MIXED if you experience some troubles# binlog_row_image= MINIMAL # Since 5.6# auto_increment_increment = 2# For Master/Master set-ups use 2 for both nodes# auto_increment_offset= 1# For Master/Master set-ups use 1 and 2# Slave variableslog_slave_updates = 1# Use if Slave is used for Backup and PiTRread_only = 0# Set to 1 to prevent writes on Slave# super_read_only= 0# Set to 1 to prevent writes on Slave for users with SUPER privilege. Since 5.7# skip_slave_start= 1# To avoid start of Slave thread# relay_log = &lt;hostname&gt;-relay-bin# relay_log_info_repository= table # MySQL 5.6# master_info_repository = table # MySQL 5.6# slave_load_tmpdir = '/tmp'# Crash-safe replication Master# binlog_checksum= CRC32 # default# sync_binlog= 1# default since 5.7.6, but slow!# innodb_support_xa = 1# default, depracted since 5.7.10# Crash-safe replication Slave# master_info_repository = TABLE# relay_log_info_repository= TABLE# relay_log_recovery = 1# sync_relay_log_info = 1# relay_log_purge= 1# default# slave_sql_verify_checksum= 1# default# GTID replication# gtid_mode = ON # Master and Slave# enforce_gtid_consistency = 1# Master and Slave# log_bin= %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # In 5.6 also on Slave# log_slave_updates= 1# In 5.6 also on Slave# Security variables# local_infile = 0# If you are security aware# secure_auth= 1# If you are security aware# sql_mode = TRADITIONAL,ONLY_FULL_GROUP_BY,NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER # Be careful changing this afterwards# skip_name_resolve = 0# Set to 1 if you do not trust your DNS or experience problems# secure_file_priv= '/tmp'# chmod 750, adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld# MyISAM variableskey_buffer_size= 8M # Set to 25 - 33 % of RAM if you still use MyISAMmyisam_recover_options = 'BACKUP,FORCE'# disabled_storage_engines = 'MyISAM,MEMORY' # MySQL 5.7, do NOT during/before mysql_upgrade, good for Galera!# MEMORY variablesmax_heap_table_size = 64M # Should be greater or equal to tmp_table_size# InnoDB variablesinnodb_strict_mode = ONinnodb_file_format_check = 1# innodb_file_format = Barracuda # For dynamic and compressed InnoDB tablesinnodb_buffer_pool_size = 128M # Go up to 80% of your available RAMinnodb_buffer_pool_instances = 8# Bigger if huge InnoDB Buffer Pool or high concurrencyinnodb_file_per_table= 1# Is the recommended way nowadays# innodb_flush_method = O_DIRECT # O_DIRECT is sometimes better for direct attached storage# innodb_write_io_threads = 8# If you have a strong I/O system or SSD# innodb_read_io_threads = 8# If you have a strong I/O system or SSD# innodb_io_capacity = 1000 # If you have a strong I/O system or SSDinnodb_flush_log_at_trx_commit = 2# 1 for durability, 0 or 2 for performanceinnodb_log_buffer_size = 8M # Bigger if innodb_flush_log_at_trx_commit = 0innodb_log_file_size = 256M # Bigger means more write throughput but longer recovery time# Galera specific MySQL parameter# default_storage_engine = InnoDB # Galera only works with InnoDB# innodb_flush_log_at_trx_commit = 2 # Durability is achieved by committing to the Group# innodb_autoinc_lock_mode = 2 # For parallel applying# binlog_format = row # Galera only works with RBR# query_cache_type= 0 # Use QC with Galera only in a Master/Slave set-up# query_cache_size= 0# WSREP parameter# wsrep_on = on # Only MariaDB >= 10.1# wsrep_provider = none # Start mysqld without Galera# wsrep_provider = /usr/lib/galera/libgalera_smm.so # Location of Galera Plugin on Ubuntu ?# wsrep_provider = /usr/lib64/galera-3/libgalera_smm.so # Location of Galera Plugin on CentOS 7# wsrep_provider_options = 'gcache.size = 1G' # Depends on you workload, WS kept for IST# wsrep_cluster_name = "My cool Galera Cluster" # Same Cluster name for all nodes# wsrep_cluster_address= "gcomm://"# Old bootstrap method# wsrep_cluster_address= "gcomm://192.168.0.2,192.168.0.3" # Start other nodes like this# wsrep_node_name= "Node A" # Unique node name# wsrep_node_address = 192.168.0.1# Our address where replication is done# wsrep_node_incoming_address = 10.0.0.1 # Our external interface where application comes from# wsrep_sync_wait= 1# If you need realy full-synchronous replication (Galera 3.6 and newer)# wsrep_slave_threads = 16 # 4 - 8 per core, not more than wsrep_cert_deps_distance# wsrep_sst_method= rsync # SST method (initial full sync): mysqldump, rsync, rsync_wan, xtrabackup-v2# wsrep_sst_auth = sst:secret# Username/password for sst user# wsrep_sst_receive_address= 192.168.0.1# Our address where to receive SST# Group Replication parameter# default_storage_engine = InnoDB# Group Replication only works with InnoDB# server_id = %SERVERID%# Should be different on all 3 nodes# log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld# binlog_format = ROW# binlog_checksum= NONE # not default!# gtid_mode = ON# enforce_gtid_consistency = ON# master_info_repository = TABLE# relay_log_info_repository= TABLE# log_slave_updates = ON# slave_parallel_workers = &lt;n&gt; # 1-2/core, max. 10# slave_preserve_commit_order = ON# slave_parallel_type = LOGICAL_CLOCK# transaction_write_set_extraction = XXHASH64# loose-group_replication_group_name= "$(uuidgen)" # Must be the same on all nodes# loose-group_replication_start_on_boot = OFF# loose-group_replication_local_address = "192.168.0.1"# loose-group_replication_group_seeds = "192.168.0.1,192.168.0.2,192.168.0.3" # All nodes of Cluster# loose-group_replication_bootstrap_group= OFF# loose-group_replication_single_primary_mode = FALSE # = multi-primary

转载来源：/mysql-configuration-file-sample