MySQL 8 기본 설정사항

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/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 total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are 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
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqld]

### 이전버전 인증 플러그인 사용
default-authentication-plugin=mysql_native_password

datadir=/app/mysql_data
socket=/tmp/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/app/mysql/mysqld.pid

### 대소문자 구분 비활성화
lower_case_table_names=1

### Password validation (반드시 초기 구성 완료 후 활성화)
#validate_password.length=8
#validate_password.mixed_case_count=0
#validate_password.number_count=0
#validate_password.policy=LOW
#validate_password.special_char_count=0

### slow query
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_slow_rate_limit = 1
log_slow_verbosity = query_plan
log_slow_admin_statements
# slow_query_log = 1(사용), 로그파일 위치는 /var/log/mysql/mysql-slow.log
# long_query_time = 2 (수행시간이 2초 넘는 쿼리를 수집)

### tunning 
max_connections = 250
#max_connect_errors=10000
#wait_timeout = 28800
#group_concat_max_len=4M
#skip_name_resolve
#performance_schema_hosts_size=0
#host_cache_size=0


# EOF

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

# Have mysqld write its state to the systemd notify socket
Type=notify

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/app/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS 

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 10000

Restart=on-failure

RestartPreventExitStatus=1

# Set environment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1

PrivateTmp=false

chmod 644 /usr/lib/systemd/system/mysqld.service
systemctl enable mysqld.service

export PATH="/app/mysql/bin:$PATH"

user$ sudo ./mysqld \
--defaults-file=/etc/my.cnf \
--initialize \
--user=mysql \
--lower_case_table_names=1 \
--basedir=/app/mysql \
--datadir=/app/mysql_data