mysql主主配置文件_mysql一主一从配置文件

主3306my.cnf

[client]

port = 3306

socket = /var/lib/mysql/3306-mysql.sock

[mysqld]

user = hdfs

port = 3306

socket = /var/lib/mysql/3306-mysql.sock

datadir = /export/data/mysql/data/3306/data

pid_file = /export/mysql/pids/3306-mysqld.pid

bind-address = 0.0.0.0

lower_case_table_names = 1

collation_server = utf8_bin

character_set_server = utf8

skip-name-resolve

skip-external-locking

explicit_defaults_for_timestamp

tmp_table_size = 24M

tmpdir = /export/data/mysql/data/3306/tmp

back_log = 1024

max_connections = 1000

max_user_connections = 0

max_connect_errors = 2000

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache_instances = 16

open_files_limit = 65534

binlog_cache_size = 512K

bulk_insert_buffer_size = 64M

ft_min_word_len = 4

join_buffer_size = 6M

key_buffer_size = 32M

max_allowed_packet = 1024M

max_heap_table_size = 32M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_sort_buffer_size = 24M

query_cache_limit = 2M

query_cache_size = 64M

read_buffer_size = 2M

read_rnd_buffer_size = 3M

sort_buffer_size = 4M

thread_cache_size = 512

thread_stack = 192K

sql_mode = NO_ENGINE_SUBSTITUTION

# log

general_log = off

general_log_file = /export/mysql/logs/3306-general-log.log

log_error_verbosity = 2

log_error = /export/mysql/logs/3306-mysqld-err.log

log_timestamps = SYSTEM

slow_query_log = on

slow_query_log_file = /export/mysql/logs/3306-mysqld-slow.log

long_query_time = 0.1

log_slow_admin_statements = on

log_slow_slave_statements = on

binlog_rows_query_log_events = on

# replication

#read_only

#super_read_only

log_slave_updates

skip-slave-start

slave_compressed_protocol = off

slave_parallel_type = database

binlog_group_commit_sync_delay = 50

binlog_group_commit_sync_no_delay_count =100

binlog_format = row

binlog_row_image = full

expire_logs_days = 10

gtid_mode = on

enforce_gtid_consistency = on

gtid_executed_compression_period = 1000

log-bin = /export/data/mysql/data/3306/binlog/mysql-bin

master_info_repository = table

relay_log = /export/data/mysql/data/3306/binlog/mysqld-relay-bin

relay_log_info_repository = table

server-id = 1

slave-net-timeout = 60

slave_checkpoint_group = 512

slave_checkpoint_period = 300

slave_load_tmpdir = /export/data/mysql/data/3306/tmp

slave_parallel_workers = 5

slave_pending_jobs_size_max = 128M

sync_binlog = 1

report_port = 3306

report_host = 192.168.0.2

# innodb

innodb_print_all_deadlocks

default-storage-engine = InnoDB

innodb_autoextend_increment = 64

innodb_buffer_pool_dump_at_shutdown = on

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = on

innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:64M:autoextend

innodb_data_home_dir = /export/data/mysql/data/3306/ibdata

innodb_fast_shutdown = 1

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 1

innodb_flush_method = O_DIRECT

innodb_flush_neighbors = 0

innodb_io_capacity = 3000

innodb_io_capacity_max = 5000

innodb_lock_wait_timeout = 50

innodb_log_buffer_size = 32M

innodb_log_file_size = 1024M

innodb_log_files_in_group = 4

innodb_log_group_home_dir = /export/data/mysql/data/3306/iblog

innodb_max_dirty_pages_pct = 75

innodb_online_alter_log_max_size = 1342177280

innodb_open_files = 64535

innodb_purge_threads = 1

innodb_read_io_threads = 16

innodb_sort_buffer_size = 4M

innodb_thread_concurrency = 2

innodb_undo_directory = /export/data/mysql/data/3306/iblog

innodb_undo_logs = 128

innodb_undo_tablespaces = 2

innodb_write_io_threads = 16

transaction-isolation = READ-COMMITTED

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_large_prefix = 1

# Plugin

# Semi_sync

plugin-load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_timeout = 1000

# Server_audit

# loose_server_audit_events = CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL

# loose_server_audit_excl_users = monitor

# loose_server_audit_file_path = pattern_logdir/mysqld-audit.log

# loose_server_audit_file_rotate_size = 524288000

# loose_server_audit_file_rotations = 20

# loose_server_audit_query_log_limit = 10240

# loose_server_audit_logging = 1

[mysqldump]

quick

max_allowed_packet = 256M

[mysql]

no-auto-rehash

prompt=\\u@\\v \\d \\r:\\m:\\s>

default-character-set=utf8

3307my.cnf

[client]

port = 3307

socket = /var/lib/mysql/3307-mysql.sock

[mysqld]

user = hdfs

port = 3307

socket = /var/lib/mysql/3307-mysql.sock

datadir = /export/data/mysql/data/3307/data

pid_file = /export/mysql/pids/3307-mysqld.pid

bind-address = 0.0.0.0

lower_case_table_names = 1

collation_server = utf8_bin

character_set_server = utf8

skip-name-resolve

skip-external-locking

explicit_defaults_for_timestamp

tmp_table_size = 24M

tmpdir = /export/data/mysql/data/3307/tmp

back_log = 1024

max_connections = 1000

max_user_connections = 0

max_connect_errors = 2000

table_open_cache = 2048

table_definition_cache = 2048

table_open_cache_instances = 16

open_files_limit = 65534

binlog_cache_size = 512K

bulk_insert_buffer_size = 64M

ft_min_word_len = 4

join_buffer_size = 6M

key_buffer_size = 32M

max_allowed_packet = 1024M

max_heap_table_size = 32M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_sort_buffer_size = 24M

query_cache_limit = 2M

query_cache_size = 64M

read_buffer_size = 2M

read_rnd_buffer_size = 3M

sort_buffer_size = 4M

thread_cache_size = 512

thread_stack = 192K

sql_mode = NO_ENGINE_SUBSTITUTION

# log

general_log = off

general_log_file = /export/mysql/logs/3307-general-log.log

log_error_verbosity = 2

log_error = /export/mysql/logs/3307-mysqld-err.log

log_timestamps = SYSTEM

slow_query_log = on

slow_query_log_file = /export/mysql/logs/3307-mysqld-slow.log

long_query_time = 0.1

log_slow_admin_statements = on

log_slow_slave_statements = on

binlog_rows_query_log_events = on

# replication

read_only

super_read_only

log_slave_updates

skip-slave-start

slave_compressed_protocol = off

slave_parallel_type = database

binlog_group_commit_sync_delay = 50

binlog_group_commit_sync_no_delay_count =100

binlog_format = row

binlog_row_image = full

expire_logs_days = 10

gtid_mode = on

enforce_gtid_consistency = on

gtid_executed_compression_period = 1000

log-bin = /export/data/mysql/data/3307/binlog/mysql-bin

master_info_repository = table

relay_log = /export/data/mysql/data/3307/binlog/mysqld-relay-bin

relay_log_info_repository = table

server-id = 1

slave-net-timeout = 60

slave_checkpoint_group = 512

slave_checkpoint_period = 300

slave_load_tmpdir = /export/data/mysql/data/3307/tmp

slave_parallel_workers = 5

slave_pending_jobs_size_max = 128M

sync_binlog = 1

report_port = 3306

report_host = 192.168.0.2

# innodb

innodb_print_all_deadlocks

default-storage-engine = InnoDB

innodb_autoextend_increment = 64

innodb_buffer_pool_dump_at_shutdown = on

innodb_buffer_pool_instances = 8

innodb_buffer_pool_load_at_startup = on

innodb_buffer_pool_size = 2G

innodb_data_file_path = ibdata1:64M:autoextend

innodb_data_home_dir = /export/data/mysql/data/3307/ibdata

innodb_fast_shutdown = 1

innodb_file_per_table = 1

innodb_flush_log_at_trx_commit = 1

innodb_flush_method = O_DIRECT

innodb_flush_neighbors = 0

innodb_io_capacity = 3000

innodb_io_capacity_max = 5000

innodb_lock_wait_timeout = 50

innodb_log_buffer_size = 32M

innodb_log_file_size = 1024M

innodb_log_files_in_group = 4

innodb_log_group_home_dir = /export/data/mysql/data/3307/iblog

innodb_max_dirty_pages_pct = 75

innodb_online_alter_log_max_size = 1342177280

innodb_open_files = 64535

innodb_purge_threads = 1

innodb_read_io_threads = 16

innodb_sort_buffer_size = 4M

innodb_thread_concurrency = 2

innodb_undo_directory = /export/data/mysql/data/3307/iblog

innodb_undo_logs = 128

innodb_undo_tablespaces = 2

innodb_write_io_threads = 16

transaction-isolation = READ-COMMITTED

innodb_file_format = Barracuda

innodb_file_format_max = Barracuda

innodb_large_prefix = 1

# Plugin

# Semi_sync

plugin-load = rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_master_enabled = 1

rpl_semi_sync_slave_enabled = 1

rpl_semi_sync_master_timeout = 1000

# Server_audit

# loose_server_audit_events = CONNECT,QUERY,TABLE,QUERY_DDL,QUERY_DML,QUERY_DCL

# loose_server_audit_excl_users = monitor

# loose_server_audit_file_path = pattern_logdir/mysqld-audit.log

# loose_server_audit_file_rotate_size = 524288000

# loose_server_audit_file_rotations = 20

# loose_server_audit_query_log_limit = 10240

# loose_server_audit_logging = 1

[mysqldump]

quick

max_allowed_packet = 256M

[mysql]

no-auto-rehash

prompt=\\u@\\v \\d \\r:\\m:\\s>