当前位置:Gxlcms > 数据库问题 > mysql一主一从配置文件

mysql一主一从配置文件

时间:2021-07-01 10:21:17 帮助过:21人阅读

主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>

  

mysql一主一从配置文件

标签:names   table   compress   resolve   dfs   sla   flush   aci   uda   

人气教程排行