my.cnf-5.7配置信息[线上生产环境部署]

以下配置信息需要根据相关业务调整,如业务名称更改及innodb_buffer_pool大小设置

参数调整:

进入my.cnf目录:

cd  /data/mysql_3306_core/conf/

复制my.cnf配置信息并修改以下内容信息:

  • 修改server_id[建议IP最后一位加端口]
sed -i "s#server_id  = 2403306#server_id  = [ID号]#g"  my.cnf 
  • 修改业务名称默认 core
sed -i   "#core#[你的业务名]#" my.cnf
  • 修改innodb_buffer_pool大小[默认1GB]
sed -i "#innodb_buffer_pool_size = 1G#innodb_buffer_pool_size = [内存大小]G#" my.cnf

其它需要修改:

  • 关闭read_only[默认开启]
sed -i "@read_only = 1@#read_only = 1@"  my.cnf
  • 开启半同步插件[需要启动安装插件]
sed -i "s@#rpl_semi_sync@rpl_semi_sync@g"  my.cnf

配置内容信息:

[client]
port = 3306

[mysql]
port = 3306
no_auto_rehash
default_character_set  = utf8
prompt  = '\u@\h [\d]> '

[mysqld]
port       = 3306
user       = mysql
server_id  = 2403306
basedir    = /usr/local/mysql
datadir    = /data/mysql_3306_core/data
tmpdir     = /data/mysql_3306_core/tmp
socket     = /data/mysql_3306_core/run/mysql_3306_core.sock
pid_file   = /data/mysql_3306_core/run/mysql_3306_core.pid

character_set_server  = utf8
skip_external_locking = 1
skip_name_resolve     = 1
skip_networking       = 0
symbolic_links        = 0
skip_slave_start      = 1
secure_file_priv      = /tmp

show_compatibility_56 = on
eq_range_index_dive_limit = 100
group_concat_max_len = 10240

#innodb_flush_neighbors    = 0
innodb_online_alter_log_max_size = 512M
innodb_stats_persistent_sample_pages = 64

#query_response_time_stats = on
slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN'
optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

# perforamnce_schema settings
#performance-schema-instrument = 'memory/%=COUNTED'
performance_schema_instrument = '%=on'
performance_schema_digests_size = 40000
performance_schema_max_table_instances = 40000
performance_schema_max_sql_text_length = 4096
performance_schema_max_digest_length = 4096

back_log = 500
wait_timeout=500 
interactive_timeout = 500

max_connections = 2048
max_user_connections = 0
max_connect_errors = 100000
max_allowed_packet = 256M

lower_case_table_names = 1
explicit_defaults_for_timestamp = 1
#sql_mode="STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO"
sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

### 时区设置 ###
#default_time_zone='+00:00'

# query_cache conf #
query_cache_type=0
query_cache_size=0

# errorlog conf #
log_error = /data/mysql_3306_core/log/error_log/error.log
log_error_verbosity = 3

# binlog conf #
log_bin           = /data/mysql_3306_core/log/binary_log/core_23306_bin
log_bin_index     = /data/mysql_3306_core/log/binary_log/core_23306_bin.index
binlog_format     = ROW
sync_binlog       = 1
expire_logs_days  = 7
max_binlog_size   = 256M
binlog_cache_size = 512K
binlog_stmt_cache_size = 32K
binlog_rows_query_log_events = 1
binlog_group_commit_sync_delay = 5000
binlog_group_commit_sync_no_delay_count = 100

log_bin_trust_function_creators = 1

# slowlog conf #
slow_query_log = 1
long_query_time = 1
#log_queries_not_using_indexes
#min_examined_row_limit = 50000
slow_query_log_file = /data/mysql_3306_core/log/slow_log/slow.log
log_slow_verbosity = full
log_timestamps = system

# generallog conf #
general_log = 0
general_log_file = /data/mysql_3306_core/log/general_log/general.log

# relay_log conf  #
relay_log_purge = 0
log_slave_updates = 1
relay_log_recovery = 1
relay_log_purge    = 1
relay_log = /data/mysql_3306_core/log/relay_log/core_relay_bin
relay_log_index = /data/mysql_3306_core/log/relay_log/core_relay_bin.index
relay_log_info_repository = TABLE
master_info_repository = TABLE
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8 
slave_preserve_commit_order = 1

# gtid #
gtid_mode = on
enforce_gtid_consistency = 1
binlog_gtid_simple_recovery = 1
read_only = 1

# semi sync replication #
loose-rpl_semi_sync_master_enabled = 1
loose-rpl_semi_sync_master_timeout = 3000
loose-rpl_semi_sync_slave_enabled = 1
loose-rpl_semi_sync_master_wait_point = AFTER_SYNC

#审计日志配置[默认关闭]
#audit_log_file = /data/mysql_3306_core/log/audit_log/audit.log
#audit_log_format = NEW
#audit_log_policy = NONE 

transaction_isolation = REPEATABLE-READ

innodb_data_home_dir  = /data/mysql_3306_core/log/ibdata_log/
innodb_data_file_path = ibdata1:4096M:autoextend
innodb_log_group_home_dir = /data/mysql_3306_core/log/redo_log/
innodb_log_file_size = 4096M
innodb_log_files_in_group = 4
innodb_log_buffer_size = 16M
innodb_file_per_table=1

# undo log config
# undolog初始大小由innodb_page_size决定,默认16K page size对应undo log 出事大小为10M
# innodb_undo_logs = 128 5.7.19已废弃,由rollback segments代替
innodb_undo_directory = /data/mysql_3306_core/log/undo_log/
innodb_max_undo_log_size = 2G
innodb_rollback_segments = 128
innodb_undo_tablespaces = 4
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 64

# 刷新脏页的线程数,默认是4;
# 若page cleaner大于ibp_instances,则page cleaner自动设置为ibp_instances数
innodb_page_cleaners = 4

# buffer pool size & instance & chunk配置(size必须是instance * chunk的整数倍)
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances  = 1
innodb_buffer_pool_chunk_size = 256M

# Save & Restore Buffer Pool配置
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_pct = 40 
innodb_buffer_pool_filename = ib_buffer_pool

innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout = 30
innodb_sync_spin_loops = 40
innodb_max_dirty_pages_pct = 50
innodb_support_xa = 1
innodb_thread_concurrency=0
#innodb_adaptive_max_sleep_delay = 500
innodb_thread_sleep_delay = 500
innodb_concurrency_tickets = 1000
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_write_io_threads = 16

innodb_io_capacity = 3000
innodb_io_capacity_max = 5000

innodb_strict_mode = 1
innodb_purge_threads = 4
innodb_purge_batch_size = 32
innodb_old_blocks_pct = 75
innodb_change_buffering = all
innodb_stats_on_metadata = OFF
innodb_fast_shutdown = 1
innodb_force_recovery = 0
innodb_autoinc_lock_mode = 2
innodb_print_all_deadlocks = 1
innodb_numa_interleave = 1
innodb_kill_idle_transaction = 15


key_buffer_size = 8M
table_open_cache = 4096
table_definition_cache = 4096
max_tmp_tables = 256
tmp_table_size = 32M
max_heap_table_size = 32M
table_open_cache_instances = 8
open_files_limit = 204800

join_buffer_size = 1M
sort_buffer_size = 2M
read_buffer_size = 64K
read_rnd_buffer_size = 2M
bulk_insert_buffer_size = 128K

#thread pool #
#thread_handling = pool_of_threads
#thread_pool_oversubscribe = 15

#配置extra port 防止too many connection 问题
extra_port = 3336
extra_max_connections = 10

#percona server user stat 设置
thread_statistics = 1
userstat = 1

[mysqld_safe]
#malloc_lib= tcmalloc
log_error = /data/mysql_3306_core/log/error_log/error.log
pid_file  = /data/mysql_3306_core/run/mysql_3306_core.pid

[mysqldump]
max_allowed_packet = 64M
点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注