Created
August 21, 2018 06:51
-
-
Save kostark/7200258d337a730d681ecbc5dca76ff3 to your computer and use it in GitHub Desktop.
mysql my.ini
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # from http://www.fromdual.com/sites/default/files/my.cnf | |
| # FromDual configuration file template for MySQL, Galera Cluster, MariaDB and Percona Server | |
| # Location: /etc/my.cnf or /etc/mysql/my.cnf | |
| # This template is intended to work with MySQL 5.6 and newer and MariaDB 10.0 and newer | |
| # Get most recent updated from here: | |
| # http://www.fromdual.com/mysql-configuration-file-sample | |
| # | |
| [client] | |
| port = 3306 | |
| socket = /var/run/mysqld/mysql.sock # 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_safe] # Becomes sooner or later obsolete with systemd | |
| open_files_limit = 8192 # You possibly have to adapt your O/S settings as well | |
| user = mysql | |
| log-error = <hostname>_error.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld | |
| [mysqld] | |
| # Connection and Thread variables | |
| port = 3306 | |
| socket = /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.7 | |
| max_allowed_packet = 16M | |
| default_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_ci | |
| max_connections = 505 # Values < 1000 are typically good | |
| max_user_connections = 500 # Limit one specific user/application | |
| thread_cache_size = 505 # Up to max_connections makes sense | |
| # Query Cache | |
| query_cache_type = 1 # Set to 0 to avoid global QC Mutex | |
| query_cache_size = 32M # Avoid too big (> 128M) QC because of QC clean-up lock! | |
| # 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) | |
| table_open_cache_instances = 16 # New default in 5.7 | |
| # MySQL error log | |
| log_error = <hostname>_error.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld | |
| # log_timestamps = SYSTEM # MySQL 5.7, equivalent to old behaviour | |
| log_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 Log | |
| slow_query_log_file = <hostname>_slow.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld | |
| slow_query_log = 0 | |
| log_queries_not_using_indexes = 0 | |
| long_query_time = 0.5 | |
| min_examined_row_limit = 100 | |
| # General Query Log | |
| general_log_file = <hostname>_general.log # Adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld | |
| general_log = 0 | |
| # Performance Schema | |
| # performance_schema = ON # on some older MariaDB 10 releases | |
| performance_schema_consumer_events_statements_history_long = ON # MySQL 5.6 and newer | |
| # Binary logging and Replication | |
| server_id = 42 | |
| log_bin = <hostname>_binlog # Locate outside of datadir, adjust AppArmor configuration: /etc/apparmor.d/usr.sbin.mysqld | |
| # master_verify_checksum = ON # MySQL 5.6 | |
| binlog_cache_size = 1M | |
| binlog_stmt_cache_size = 1M | |
| max_binlog_size = 128M # Make bigger for high traffic to reduce number of files | |
| sync_binlog = 0 # Set to 1 or higher to reduce potential loss of binary-log data | |
| expire_logs_days = 5 # We will survive easter holidays | |
| binlog_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 variables | |
| log_slave_updates = 1 # Use if Slave is used for Backup and PiTR | |
| 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 | |
| # skip_slave_start = 1 # To avoid start of Slave thread | |
| # relay_log = <hostname>-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 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_strict_mode = ON | |
| innodb_file_format_check = 1 | |
| # innodb_file_format = Barracuda # For dynamic and compressed InnoDB tables | |
| innodb_buffer_pool_size = 128M # Go up to 80% of your available RAM | |
| innodb_buffer_pool_instances = 8 # Bigger if huge InnoDB Buffer Pool or high concurrency | |
| 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 = 8M # Bigger if innodb_flush_log_at_trx_commit = 0 | |
| innodb_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 = <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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment