MySQL master copy configuration file instance

1. master server configuration file

# for advice The change settings

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[Mysqld]

# Remove Leading # and set to the amount of RAM for the most important data

# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove Leading # to turn on a very important data integrity option:logging

# Changes to the binary log between backups.

# Log_bin

# These is commonly set, remove the # and set as required.

# Basedir = ...

# DataDir = ...

# port = .....

# server_id = ...

# socket = .....

# Remove Leading # To set options mainly useful for reporting servers.

# The server defaults is faster for transactions and fast selects.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

# DataDir =/usr/local/mysql/data

# Server

port=3306

Socket=/tmp/mysql.sock

Basedir=/usr/local/mysql

DataDir =/var/lib/mysql

open_files_limit=10240

Explicit_defaults_for_timestamp

Sql_mode=no_engine_substitution,strict_trans_tables

Federated

Server_id=1

max_connections=1000

max_connect_errors=100000

interactive_timeout=86400

wait_timeout=86400

Skip-name-resolve

Sync_binlog=0

# Master

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Relay-log-info-repository=table

Sync-master-info=1

slave-parallel-workers=2

Binlog-checksum=crc32

Master-verify-checksum=1

Slave-sql-verify-checksum=1

Binlog-rows-query-log_events=1

report-port=3306

# Buffer

Sort_buffer_size=2m

Read_buffer_size=2m

Read_rnd_buffer_size=2m

Join_buffer_size=2m

net_buffer_length=16k

max_allowed_packet=512m

bulk_insert_buffer_size=32m

max_heap_table_size=512m

tmp_table_size=512m

thread_cache_size=300

query_cache_size=128m

query_cache_limit=1m

query_cache_min_res_unit=4k

key_buffer_size=16m

myisam_sort_buffer_size=64m

myisam_max_sort_file_size=10g

Myisam_repair_threads=1

# Log

Log-bin=mysql-bin

binlog_cache_size=32m

max_binlog_cache_size=64m

binlog_stmt_cache_size=32m

table_open_cache=2048

max_binlog_size=512m

Binlog_format=row

Log_output=file

Slow_query_log=1

Slow_query_log_file=slow_query.log

General_log=0

General_log_file=general_query.log

expire-logs-days=2

Relay-log=relay-bin

Relay-log-index=relay-bin.index

# Innodb

Innodb_data_file_path=ibdata1:2048m:autoextend

innodb_log_file_size=128m

Innodb_log_files_in_group=3

innodb_buffer_pool_size=5g

Innodb_buffer_pool_instances=-1

Innodb_max_dirty_pages_pct=70

Innodb_thread_concurrency=8

Innodb_flush_method=o_direct

innodb_log_buffer_size=16m

innodb_flush_log_at_trx_commit=2

2. From the service configuration file

# for advice The change settings

# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[Mysqld]

# Remove Leading # and set to the amount of RAM for the most important data

# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.

# innodb_buffer_pool_size = 128M

# Remove Leading # to turn on a very important data integrity option:logging

# Changes to the binary log between backups.

# Log_bin

# These is commonly set, remove the # and set as required.

# Basedir = ...

# DataDir = ...

# port = .....

# server_id = ...

# socket = .....

# Remove Leading # To set options mainly useful for reporting servers.

# The server defaults is faster for transactions and fast selects.

# Adjust sizes as needed, experiment to find the optimal values.

# join_buffer_size = 128M

# sort_buffer_size = 2M

# read_rnd_buffer_size = 2M

# Server

port=3306

Socket=/tmp/mysql.sock

Basedir=/usr/local/mysql

Datadir=/var/lib/mysql

open_files_limit=10240

Explicit_defaults_for_timestamp

Sql_mode=no_engine_substitution,strict_trans_tables

Federated

server_id=10

max_connections=1000

max_connect_errors=100000

interactive_timeout=86400

wait_timeout=86400

Skip-name-resolve

Sync_binlog=0

# Log

Log-bin=/zabbixdb/mysql_slave_log/mysql-bin

Binlog-format=row

Binlog-checksum=crc32

Binlog-rows-query-log_events=1

binlog_cache_size=32m

binlog_stmt_cache_size=32m

max_binlog_cache_size=64m

max_binlog_size=512m

Log_output=file

table_open_cache=2048

Slow_query_log=1

Slow_query_log_file=/zabbixdb/mysql_slave_log/slow_query.log

General_log=0

General_log_file=/zabbixdb/mysql_slave_log/general.log

Relay-log=/zabbixdb/mysql_slave_log/relay-log

Relay-log-index=/zabbixdb/mysql_slave_log/relay-log.index

Relay-log-info-repository=table

Expire_logs_days=1

# Slave

Log-slave-updates=true

Gtid-mode=on

Enforce-gtid-consistency=true

Master-info-repository=table

Master-verify-checksum=1

Sync-master-info=1

slave-parallel-workers=2

Slave-sql-verify-checksum=1

report-port=3306

# Innodb

Innodb_data_file_path=ibdata1:2048m:autoextend

innodb_log_file_size=128m

Innodb_log_files_in_group=3

innodb_buffer_pool_size=2g

Innodb_buffer_pool_instances=-1

Innodb_max_dirty_pages_pct=70

Innodb_thread_concurrency=8

Innodb_flush_method=o_direct

innodb_log_buffer_size=16m

innodb_flush_log_at_trx_commit=2

MySQL master copy configuration file instance