You are here
MySQL and MariaDB Configuration File template (my.cnf/my.ini)
The MySQL and MariaDB default configuration is not very great for production use. Some of the default values in my.cnf should 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 with wget directly from here.
# # FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server # Location: %MYCNF% # This template is intended to work with MySQL 8.0 and newer and MariaDB 10.4 and newer # Get most recent updated from here: # https://www.fromdual.com/mysql-configuration-file-sample # [client] port = %PORT% # default 3306 socket = %SOCKET% # Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise [mysql] no_auto_rehash max_allowed_packet = 16M prompt = '\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] # Connection and Thread variables # mysqlx = 0 # Disable Protocol X port = %PORT% # default 3306 socket = %SOCKET% # Use mysqld.sock on Ubuntu, conflicts with AppArmor otherwise basedir = %BASEDIR% datadir = %DATADIR% # tmpdir = '%INSTANCEDIR%/tmp' # innodb_tmpdir = '%INSTANCEDIR%/tmp' max_allowed_packet = 16M default_storage_engine = InnoDB # explicit_defaults_for_timestamp = 1 # default in MySQL 8.0, and MariaDB 10.10 and newer. Test carefully! This can have an impact on application. # character_set_server = utf8mb4 # For modern applications, default in MySQL 8.0 # collation_server = utf8mb4_general_ci max_connections = 151 # Values < 1000 are typically good max_user_connections = 145 # Limit one specific user/application thread_cache_size = 151 # Up to max_connections makes sense # Query Cache (does not exist in MySQL 8.0 any more!) # query_cache_type = 1 # Set to 0 to avoid global QC Mutex, removed in MySQL 8.0 # query_cache_size = 32M # Avoid too big (> 128M) QC because of QC clean-up lock!, removed in MySQL 8.0 # Session variables sort_buffer_size = 2M # Could be too big for many small sorts tmp_table_size = 32M # Make sure your temporary results do NOT contain BLOB/TEXT attributes read_buffer_size = 128k # Resist to change this parameter if you do not know what you are doing read_rnd_buffer_size = 256k # Resist to change this parameter if you do not know what you are doing join_buffer_size = 128k # Resist to change this parameter if you do not know what you are doing # Other buffers and caches table_definition_cache = 1400 # As big as many tables you have table_open_cache = 2000 # connections x tables/connection (~2) # MySQL error log log_error = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_error.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # log_timestamps = SYSTEM # MySQL 5.7, equivalent to old behaviour # log_warnings = 2 # MariaDB equivalent to log_error_verbosity = 3, MySQL does NOT support this any more! # log_error_verbosity = 3 # MySQL 5.7 ff., equivalent to log_warnings = 2, MariaDB does NOT support this! innodb_print_all_deadlocks = 1 # wsrep_log_conflicts = 1 # for Galera only! # Slow Query Log slow_query_log_file = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_slow.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld slow_query_log = 0 log_queries_not_using_indexes = 0 # Interesting on developer systems! long_query_time = 0.5 min_examined_row_limit = 100 # General Query Log general_log_file = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_general.log # Adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld general_log = 0 # Performance Schema performance_schema = ON performance_schema_consumer_events_statements_history_long = ON # performance_schema_instrument = 'memory/%=COUNTED' # Binary logging and Replication server_id = %SERVERID% # Must be set on MySQL 5.7 and newer if binary log is enabled! log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # master_verify_checksum = ON # binlog_cache_size = 1M # For each connection! # binlog_stmt_cache_size = 1M # For each connection! max_binlog_size = 128M # Make bigger for high traffic to reduce number of files sync_binlog = 1 # Set to 0 or higher to get better write performance, default since MySQL 5.7 expire_logs_days = 5 # We will survive Easter holidays, NOT in MySQL 8.0 any more! # binlog_expire_logs_seconds = 432000 # MariaDB 10.6 and MySQL 8.0, 5 days * 86400 seconds binlog_format = ROW # Use MIXED if you want to experience some troubles, default since MySQL 5.7, MariaDB default is MIXED # 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 variables log_slave_updates = ON # Use if Slave is used for Backup and PiTR, default since MySQL 8.0 read_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, not in MariaDB # skip_slave_start = 1 # To avoid start of Slave thread # relay_log = %UNAME%_%INSTANCE%_relay-bin # slave_load_tmpdir = '%INSTANCEDIR%/tmp' # defaults to tmpdir # Crash-safe replication Master # sync_binlog = 1 # default since 5.7.6, but slow! # Crash-safe replication Slave # relay_log_recovery = 1 # sync_relay_log_info = 1 # default 10000 # relay_log_purge = 1 # default # slave_sql_verify_checksum = 1 # default # GTID replication # gtid_mode = ON # MySQL only, Master and Slave # enforce_gtid_consistency = 1 # MySQL only, Master and Slave # log_bin = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog # log_slave_updates = ON # 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 # Be careful changing this afterwards, NO_AUTO_CREATE_USER does NOT exist any more in MySQL 8.0 # skip_name_resolve = 0 # Set to 1 if you do not trust your DNS or experience problems # secure_file_priv = '%INSTANCEDIR%/tmp' # chmod 750, adjust AppArmor configuration: /etc/apparmor.d/local/usr.sbin.mysqld # MyISAM variables key_buffer_size = 8M # Set to 25 - 33 % of RAM if you still use MyISAM myisam_recover_options = 'BACKUP,FORCE' # disabled_storage_engines = 'MyISAM,MEMORY' # MySQL 5.7, do NOT during/before mysql_upgrade, good for Galera! # MEMORY variables max_heap_table_size = 64M # Should be greater or equal to tmp_table_size # InnoDB variables innodb_buffer_pool_size = 128M # Go up to 75% of your available RAM # innodb_buffer_pool_instances = 8 # Bigger if huge InnoDB Buffer Pool or high concurrency, desupported in MariaDB 10.5 ff. innodb_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 SSD innodb_flush_log_at_trx_commit = 2 # 1 for durability, 0 or 2 for performance innodb_log_buffer_size = 16M # Bigger if innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 256M # Bigger means more write throughput but longer recovery time # innodb_log_files_in_group = 2 # Desupported in MariaDB 10.6 # innodb_redo_log_capacity = 512M # Bigger means more write throughput but longer recovery time innodb_monitor_enable = all # Overhead < 1% according to PeterZ/Percona # 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, removed in MySQL 8.0 # query_cache_size = 0 # removed in MySQL 8.0 # log_slave_updates = ON # Must be enabled on ALL Galera nodes if binary log is enabled! # server_id = ... # Should be equal on all Galera nodes according to Codership CTO if binary log is enabled. # WSREP parameter # wsrep_on = on # Only MariaDB # 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 v3 Plugin on CentOS 7 # wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so # Location of Galera v4 Plugin on CentOS 7 # wsrep_provider_options = 'gcache.size = 1G' # Depends on you workload, WS kept for IST # wsrep_provider_options = 'gcache.recover = on' # Since 3.19, tries to avoid SST after crash # wsrep_cluster_name = "My cool Galera Cluster" # Same Cluster name for all nodes # wsrep_cluster_address = "gcomm://192.168.0.1,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.2.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 = n # 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