my.cnf sample

MySQL의 Sample my.cnf file

[mysqld]

back_log = 1024
bind_address = 0.0.0.0
binlog_cache_size = 64k
binlog_format = row
binlog_stmt_cache_size = 64k
################################################################
character_set_server = utf8
collation_server =  utf8_general_ci

connect_timeout = 60

datadir = /MYSQL/data
date_format = %Y-%m-%d
datetime_format = %Y-%m-%d %H:%i:%s

default_authentication_plugin = mysql_native_password
default_password_lifetime = 365

default_storage_engine = InnoDB
default_tmp_storage_engine = InnoDB

disabled_storage_engines="MyISAM,FEDERATED"
div_precision_increment = 6

event_scheduler = OFF

general_log = OFF
general_log_file = /var/log/mysql/mysql-general-log.log

init_connect = 'SET NAMES utf8'

################################################################
# InnoDB
###############################################################
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 20
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_parts = 16

innodb_api_trx_level = 2

innodb_autoextend_increment = 128
innodb_autoinc_lock_mode = 1

innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_pct = 100
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = ON

innodb_buffer_pool_size = 2048
innodb_concurrency_tickets = 8192

innodb_data_file_path = ibdata1:2048M;ibdata2:2048M:autoextend
innodb_data_home_dir = /MYSQL/system

innodb_deadlock_detect = OFF
innodb_doublewrite = OFF
innodb_fast_shutdown = 0

innodb_file_per_table = ON

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT # 4 for 8.0
innodb_flush_neighbors = 0

innodb_flush_sync = OFF
innodb_io_capacity = 200
innodb_io_capacity_max = 400
innodb_lock_wait_timeout = 60

innodb_log_buffer_size = 32k
innodb_log_file_size = 1G
innodb_log_files_in_group = 4 
innodb_log_group_home_dir = /MYSQL/system

innodb_log_write_ahead_size = 8k
innodb_max_dirty_pages_pct = 80
innodb_max_dirty_pages_pct_lwm = 20

innodb_max_undo_log_size = 4G
innodb_monitor_enable='latch'
innodb_open_files = 8192
innodb_page_size = 8 * 1024

innodb_random_read_ahead = ON
innodb_read_io_threads = 4

innodb_sort_buffer_size = 2M

innodb_stats_persistent_sample_pages = 80
innodb_stats_transient_sample_pages = 20
innodb_sync_array_size = 768

innodb_temp_data_file_path = ibtmp1:256M:autoextend
innodb_tmpdir = /MYSQL/system
innodb_thread_concurrency = 8

innodb_undo_tablespaces = 2
innodb_write_io_threads = 4
####

interactive_timeout = 600
join_buffer_size = 1K
lock_wait_timeout = 600
#
log_bin = /MYSQL/binlog/mysql-binlog
log_error = /var/log/mysql/mysql-error.log
log_output = file
log_timestamps = system

long_query_time = 10
max_allowed_packet = 1G
max_connect_errors = 8192
max_connections = 8192

net_buffer_length = 1M
open_files_limit = 8192

performance_schema_max_sql_text_length = 8192
port = 3306

query_cache_limit = 32M
query_cache_size = 16M
query_cache_type = 2
#
schema_definition_cache = 1024
server_id = 1
skip_external_locking = ON
skip_name_resolve = ON
#
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log

socket = /tmp/mysql.sock

sort_buffer_size = 1M

sql_mode = ORACLE

#################################################################
# ssl
################################################################
ssl-ca=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-cert=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-key=/etc/my.cnf.d/ssl/ad-admindb-m-01-key.pem


stored_program_cache = 512
stored_program_definition_cache = 512

sync_binlog = 0

table_definition_cache = 2048
table_open_cache = 4096
thread_stack = 8192

max_heap_table_size = 32M
max_tmp_table_size = 32M

tmpdir = /MYSQL/db_tmp

 # set tx_isolation = 'READ-COMMITTED'
transaction-isolation           = READ-COMMITTED

wait_timeout = 600

[mysql]
socket                                          = /tmp/mysql.sock
port                                            = 3306
no-auto-rehash
show-warnings
default-character-set                          = utf8
prompt                                          =\u@\h:\d\_\R:\m:\\s>
pager                                           ="less -n -i -F -X -E"

[client]
port                                           = 3306
socket                                         = /tmp/mysql.sock

#################################################################
# ssl
################################################################
ssl-ca=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-cert=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-key=/etc/my.cnf.d/ssl/ad-admindb-m-01-key.pem

[mysqladmin]
port = 3306
socket = /tmp/mysql.sock
user = mysql
passwor = 123

[mysqld_safe]
socket                                          = /tmp/mysql.sock
port                                            = 3306
log_error                                       = /var/log/mysql/ad-admindb-m-01-error.log
basedir                                         = /usr
datadir                                         = /MYSQL/data
open-files-limit                                = 65535
malloc-lib                                      = /usr/lib64/libjemalloc.so.1