MySQL configuration file

##################### mysql5.6 configuration file #############################################

[Client]

Port= 3306

Socket=/tmp/mysql.sock

Default-character-set=utf8

[MySQL]

Default-character-set=utf8

[Mysqld]

Port= 3306

Socket=/tmp/mysql.sock

Basedir=/usr/local/mysql

DataDir=/data/mysql

Open_files_limit = 3072

Back_log = 103

Max_connections = 512

Max_connect_errors = 100000

Table_open_cache = 512

external-locking = FALSE

Max_allowed_packet = 128M

Sort_buffer_size = 2M

Join_buffer_size = 2M

Thread_cache_size = 51

Query_cache_size = 32M

Tmp_table_size = 96M

Max_heap_table_size = 96M

Slow_query_log = 1

Slow_query_log_file =/data/mysql/slow.log

Log-error =/data/mysql/error.log

Long_query_time = 0.05

Server-id = 3

Log-bin =/data/mysql/mysql-bin

Sync_binlog = 1

Binlog_cache_size = 4M

Max_binlog_cache_size = 128M

Max_binlog_size = 1024M

Expire_logs_days = 7

Key_buffer_size = 32M

Read_buffer_size = 1M

Read_rnd_buffer_size = 16M

Bulk_insert_buffer_size = 64M

Character-set-server=utf8

Default-storage-engine=innodb

Binlog_format=row

Gtid_mode=on

Log_slave_updates=1

Enforce_gtid_consistency=1

interactive_timeout=100

wait_timeout=100

Transaction_isolation = Repeatable-read

Innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 1434M

Innodb_data_file_path = Ibdata1:1024m:autoextend

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 16M

Innodb_log_file_size = 256M

Innodb_log_files_in_group = 2

innodb_max_dirty_pages_pct = 50

innodb_file_per_table = 1

Innodb_locks_unsafe_for_binlog = 0

[Mysqldump]

Quick

Max_allowed_packet = 32M

##################################### #mysql 5.7 Configuration file ######################################################

[Client]

Port= 3306

Socket=/data/mysql/mysql.sock

[MySQL]

Prompt= "\[email protected] \r:\m:\s [\d]>"

No-auto-rehash

[Mysqld]

User= mysql

Port= 3306

Basedir=/usr/local/mysql

DataDir=/data/mysql/

Socket=/data/mysql/mysql.sock

Pid-file = Db.pid

Character-set-server = Utf8mb4

Skip_name_resolve = 1

Open_files_limit = 65535

Back_log = 1024

Max_connections = 512

Max_connect_errors = 1000000

Table_open_cache = 1024

Table_definition_cache = 1024

Table_open_cache_instances = 64

Thread_stack = 512K

external-locking = FALSE

Max_allowed_packet = 32M

Sort_buffer_size = 4M

Join_buffer_size = 4M

Thread_cache_size = 768

Query_cache_size = 0

Query_cache_type = 0

Interactive_timeout = 600

Wait_timeout = 600

Tmp_table_size = 32M

Max_heap_table_size = 32M

Slow_query_log = 1

Slow_query_log_file =/data/mysql/slow.log

Log-error =/data/mysql/error.log

Long_query_time = 0.1

Server-id = 3306101

Log-bin =/data/mysql/mybinlog

Sync_binlog = 1

Binlog_cache_size = 4M

Max_binlog_cache_size = 1G

Max_binlog_size = 1G

Expire_logs_days = 7

Gtid_mode = On

Enforce_gtid_consistency = 1

Log_slave_updates

Binlog_format = row

Relay_log_recovery = 1

Relay-log-purge = 1

Key_buffer_size = 32M

Read_buffer_size = 8M

Read_rnd_buffer_size = 4M

Bulk_insert_buffer_size = 64M

Lock_wait_timeout = 3600

Explicit_defaults_for_timestamp = 1

innodb_thread_concurrency = 0

Innodb_sync_spin_loops = 100

Innodb_spin_wait_delay = 30

Transaction_isolation = Repeatable-read

#innodb_additional_mem_pool_size = 16M

Innodb_buffer_pool_size = 1024M

Innodb_buffer_pool_instances = 8

Innodb_buffer_pool_load_at_startup = 1

Innodb_buffer_pool_dump_at_shutdown = 1

Innodb_data_file_path = Ibdata1:1g:autoextend

Innodb_flush_log_at_trx_commit = 1

Innodb_log_buffer_size = 32M

Innodb_log_file_size = 2G

Innodb_log_files_in_group = 2

Innodb_max_undo_log_size = 4G

innodb_io_capacity = 4000

Innodb_io_capacity_max = 8000

innodb_flush_neighbors = 0

Innodb_write_io_threads = 8

Innodb_read_io_threads = 8

Innodb_purge_threads = 4

Innodb_page_cleaners = 4

Innodb_open_files = 65535

innodb_max_dirty_pages_pct = 50

Innodb_flush_method = O_direct

Innodb_lru_scan_depth = 4000

Innodb_checksum_algorithm = CRC32

Innodb_lock_wait_timeout = 10

Innodb_rollback_on_timeout = 1

Innodb_print_all_deadlocks = 1

innodb_file_per_table = 1

Innodb_online_alter_log_max_size = 4G

Internal_tmp_disk_storage_engine = InnoDB

Innodb_stats_on_metadata = 0

Innodb_status_file = 1

Innodb_status_output = 0

Innodb_status_output_locks = 0

#performance_schema

Performance_schema = 1

performance_schema_instrument = '%=on '

#innodb Monitor

Innodb_monitor_enable= "Module_innodb"

Innodb_monitor_enable= "Module_server"

Innodb_monitor_enable= "Module_dml"

Innodb_monitor_enable= "Module_ddl"

Innodb_monitor_enable= "Module_trx"

Innodb_monitor_enable= "Module_os"

Innodb_monitor_enable= "Module_purge"

Innodb_monitor_enable= "Module_log"

Innodb_monitor_enable= "Module_lock"

Innodb_monitor_enable= "Module_buffer"

Innodb_monitor_enable= "Module_index"

Innodb_monitor_enable= "Module_ibuf_system"

Innodb_monitor_enable= "Module_buffer_page"

Innodb_monitor_enable= "Module_adaptive_hash"

[Mysqldump]

Quick

Max_allowed_packet = 32M

MySQL configuration file