of their own:
[Mysqld]
Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306
server_id = 2
Socket =/tmp/mysql.sock
Pid-file=/tmp/mysql_3306.pid
Binlog_cache_size = 1M
Binlog_format=row
Log-error =/data1/log/3306.err
Log-bin=/data1/binlog/mysql-bin
Skip-name-resolve
Sync_binlog=1
Explicit_defaults_for_timestamp
Sql_mode=strict_trans_tables,no_engine_substitution,no_zero_date,no_zero_in_date,error_for_division_by_zero,no _auto_create_user
Expire_logs_days = 7
Default-storage-engine=innodb
Max_connect_errors = 100000
max_connections=3000
##### Global Buffers ###########################################
innodb_autoinc_lock_mode=2
Innodb_file_per_table=1
innodb_flush_log_at_trx_commit=2
Innodb_buffer_pool_size = 93G
Innodb_log_buffer_size = 64M
Innodb_log_file_size = 512M
Innodb_lock_wait_timeout = 120
Innodb_data_home_dir =/data/mysql
Innodb_data_file_path = Ibdata1:1g:autoextend
Innodb_log_group_home_dir =/data1/redolog
Innodb_io_capacity = 30000
innodb_max_dirty_pages_pct = 75
Innodb_open_files = 4096
Innodb_page_size = 16k
Innodb_purge_threads=4
Innodb_large_prefix = 1
innodb_thread_concurrency = 128
Innodb_write_io_threads=64
Innodb_read_io_threads=64
Innodb_print_all_deadlocks = 1
Innodb_strict_mode = 1
Innodb_sort_buffer_size = 60M
innodb_online_alter_log_max_size=1g
Innodb_use_native_aio=1
Transaction-isolation = read-committed
##### table Cache Performance Settings ############################
Table_open_cache = 4096
Table_definition_cache = 4096
Table_open_cache_instances = 64
##### Thread Buffers ###########################################
Sort_buffer_size = 16M
Myisam_sort_buffer_size = 256M
Read_buffer_size = 8M
Join_buffer_size = 16M
Read_rnd_buffer_size = 64M
Max_heap_table_size = 32M
Tmp_table_size = 32M
Thread_cache_size = 16
Wait_timeout = 30
Open_files_limit = 65535
Max_allowed_packet = 20M
Wsrep_provider=/usr/lib64/libgalera_smm.so
wsrep_cluster_address=gcomm://172.31.101.29,172.31.101.30,172.31.101.31
wsrep_node_address=172.31.101.29
Wsrep_sst_method=xtrabackup-v2
Wsrep_cluster_name=my_centos_cluster
Wsrep_sst_auth= "Sstuser:sstuser"
[Mysqld_safe]
Malloc-lib=/usr/lib64/libjemalloc.so.2
Inside a copy of the latest MySQL 5.6 and 5.7-based profile templates, basically covering 90% of tuning options, users only need to modify their server configuration slightly, such as the size of the InnoDB buffer pool, IO capability (innodb_buffer_ pool_size,innodb_io_capacity). It is important to note that this configuration file is not modified and can be run directly in MySQL version 5.6 and 5.7, where a small trick is used to see the configuration file. If there is a problem with configuration parameters, can also be timely feedback inside, we grow together.
Trigger inside do this because most of the network MySQL configuration files are very very old, mostly based on the MySQL 5.1 version, which led to the vast majority of MySQL is not running in the optimal environment, resulting in a number of wrong use, or catastrophic accident occurred, such as data loss, master-slave data inconsistency and so on. These issues were resolved as early as the 5.6 and later versions.
[client]user=davidpassword=88888888[mysqld]####### #basic settings####### #server-id = 11 port = 3306user = mysqlbind_address = 10.166.224.32autocommit = 0character_set_server=utf8mb4skip_name_resolve = 1max_connections = 800max_connect_errors = 1000datadir = /data/mysql_datatransaction_isolation = read-committedexplicit_ defaults_for_timestamp = 1join_buffer_size = 134217728tmp_table_size = 67108864tmpdir = /tmpmax_allowed_packet = 16777216sql_mode = "STRICT_TRANS_ Tables,no_engine_substitution,no_zero_date,no_zero_in_date,error_for_division_by_zero,no_auto_create_user " Interactive_timeout = 1800wait_timeout = 1800read_buffer_size = 16777216read_rnd _buffer_size = 33554432sort_buffer_size = 33554432####### #log settings####### #log_ Error = error.logslow_query_loG = 1slow_query_log_file = slow.loglog_queries_not_using_indexes = 1log_slow_ Admin_statements = 1log_slow_slave_statements = 1log_throttle_queries_not_using_indexes = 10expire_logs_days = 90long_query_time = 2min_examined_row_limit = 100####### #replication settings####### #master_info_repository = tablerelay_log_info_repository = tablelog_bin = bin.logsync_binlog = 1gtid_mode = onenforce_gtid_ consistency = 1log_slave_updatesbinlog_format = row relay_log = relay.logrelay_log_recovery = 1binlog_gtid_simple_recovery = 1slave_skip_errors = ddl_exist_errors####### #innodb settings####### #innodb_page_size = 8192innodb_buffer_pool_ size = 6ginnodb_buffer_pool_instances = 8innodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown&Nbsp;= 1innodb_lru_scan_depth = 2000innodb_lock_wait_timeout = 5innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_flush_method = o_directinnodb_file_ format = barracudainnodb_file_format_max = barracudainnodb_log_group_home_dir = /redolog/innodb_undo_directory = /undolog/innodb_undo_logs = 128innodb_undo_tablespaces = 3innodb_flush_neighbors = 1innodb_log_file_size = 4ginnodb_log_buffer_size = 16777216innodb_purge_threads = 4innodb_large_prefix = 1innodb_thread_ Concurrency = 64innodb_print_all_deadlocks = 1innodb_strict_mode = 1innodb_sort_ buffer_size = 67108864 ####### #semi sync replication settings####### #plugin_dir = /usr/local/mysql/lib/pluginplugin_load = "Rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_ Slave=semisync_slave.so "LOOSE_RPL_semi_sync_master_enabled = 1loose_rpl_semi_sync_slave_enabled = 1loose_rpl_semi_sync_ Master_timeout = 5000[mysqld-5.7]innodb_buffer_pool_dump_pct = 40innodb_page_cleaners = 4innodb_undo_log_truncate = 1innodb_max_undo_log_size = 2ginnodb_purge_rseg_ Truncate_frequency = 128binlog_gtid_simple_recovery=1log_timestamps=systemtransaction_write_set_ Extraction=murmur32show_compatibility_56=on
This article is from the "12729686" blog, please be sure to keep this source http://12739686.blog.51cto.com/12729686/1908825
MySQL parameter configuration +inside recommended configuration