UBUNTU16.04에서 MYSQL 5.7 설치하기, 튜닝, 삭제하기

우분투16.04에서 MYSQL5.7을 깔고, 외부접속허용, BUFFER POOL 및 CHANGE BUFFER의 설정값을 바꾸는 법에 대해 쓰겠다.

1. Mysql 5.7

1). mysql 검색
sudo apt-cache search mysql-server

2). mysql 설치
sudo apt-get install mysql-server-5.7


3). 외부에서 접속할 수 있도록 권한부여.

(1) mysql database에 로그인.
mysql -u root -p

(2) default db 변경
 use mysql;


(3) 권한부여
GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'password';

password => 어드민 패스워드 입력

(4) 즉시반영
flush privileges;


4).my.cnf 설정화일 변경
sudo pico /etc/mysql/mysqld.conf.d/mysqld.cnf (mysql 5.7부턴 여기서 설정변경)

bind-address = 127.0.0.1 이부분을 주석처리

5). mysql Server 재시작
sudo /etc/init.d/mysql restart


6). 한글 설정
sudo pico /etc/mysql/mysqld.conf.d/mysqld.cnf 연다.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
character-set-server=utf8
collation-server=utf8_general_ci
init_connect = set collation_connection = utf8_general_ci
init_connect = set names utf8

[mysql]
default-character-set=utf8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8

[client]
default-character-set=utf8

[mysqldump]
default-character-set=utf8

출처 : 티스토리

2 . Buffer Pool & Change Buffer 변경

기존에는(5.7.5 ver 이전에)  my.cnf파일에서 mysql의 설정변경이 가능했다.

5.7.5 이후 부터 mysql 설정변경은 /etc/mysql/mysqld.conf.d/mysqld.cnf에서 변경이 가능해졌으며, InnoDB 같은 경우 [mysqld]에서 설정하면 된다.

참고로 sql문으로도 변경가능하다.

/etc/mysql/mysql.conf.d/mysql.cnf

# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking
character-set-server=utf8
collation-server=utf8_general_ci
init_connect = set collation_connection = utf8_general_ci
init_connect = set names utf8
bind-address = 0.0.0.0
max_connections = 10000
explicit_defaults_for_timestamp = 1
innodb_buffer_pool_size=11811160064
innodb_buffer_pool_instances=11
innodb_buffer_pool_chunk_size=1073741824
#수정한 부분이다.
[client]
default-character-set=utf8

[mysqldump]
default-character-set=utf8
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size   = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

설정 변경후 systemctl restart mysql.service

재시작해줘야한다.

Buffer Pool Size 할당

SET GLOBAL innodb_buffer_pool_size=할당할 메모리(단위: Byte);

Buffer Pool은 최대 메모리의 70~80%까지 할당이 가능하다.

Buffer Pool Size 중 Change Buffer의 메모리 할당률

SET GLOBAL innodb_change_buffer_max_size=(단위 : %); 

Change Buffer의 메모리 할당률은 50% 까지 가능하다.

innodb 상태 확인

show engine innodb status\g;

출처 : 스택오버플로우

Buffer Pool

서버사양이 16G Ram라 70%크기인 11G 할당

Change Buffer

Change Buffer는 50%까지 할당

https://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os

출처: http://tristan91.tistory.com/304 [개발모음집]

https://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os

출처: http://tristan91.tistory.com/304 [개발모음집]

https://stackoverflow.com/questions/5696857/how-to-change-value-for-innodb-buffer-pool-size-in-mysql-on-mac-os

출처: http://tristan91.tistory.com/304 [개발모음집]

select @@innodb_buffer_pool_size/1024/1024/1024; 명령어로도 메모리 할당 크기를 알 수 있다.

++ 참고 ++

튜닝전 디폴트 크기이다

show global variables 명령어로 innodb 변수 현재 설정값을 알 수 있다.

참고 : 티스토리

1. ㅇㅇ

2. ㅇㅇ

3. ㅇㅇ

4. dd

5. dd

6. dd

삭제하기

    sudo apt-get purge mysql-server

    sudo apt-get purge mysql-common

    sudo apt-get remove mysql.*

    rm -rf /var/log/mysql

    rm -rf /var/log/mysql.*

    rm -rf /var/lib/mysql

    rm -rf /etc/mysql