MySQL parameter configuration +inside recommended configuration

Source: Internet
Author: User
Tags mysql version

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.