Tagged: MySQL

01/12/2023

MySQL 모니터링을 위한 계정 권한

MySQL 에서는 각종 지표등을 제공하는데, 이러한 지표를 얻기 위해서는 MySQL 계정이 있어야 한다. 대부분 MySQL 계정을 생성하고 권한을 주는데, 습관처럼 “ALL PRIVLEGES” 를 주는 경우가 많다. 하지만 모든 시스템에서 계정은 최소한의 필요한 권한만 주도록 하는 것이 보안의 시작이다.

이 문서는 MySQL 모니터링을 위한 계정 권한에 대해 다룬다.

계정생성

MySQL 8.0 기준으로 계정 생성은 다음과 같다.

mysql> CREATE USER 'telegraf'@'127.0.0.1' IDENTIFIED WITH mysql_native_password by '<UNIQUEPASSWORD>';

1	mysql> CREATE USER 'telegraf'@'127.0.0.1' IDENTIFIED WITH mysql_native_password by '<UNIQUEPASSWORD>';

이렇게 생성을 하고 난 후에 이제 GRANT 명령어를 이용해서 권한을 줘야 한다.

모니링을 위한 권한 부여

‘ALL PRIVILEGES’ 로 모든 권한을 줘는 안되며 모니터링을 위한 권한만 주면 된다.

mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf'@'127.0.0.1';
mysql> GRANT PROCESS ON *.* TO 'telegraf'@'127.0.0.1';
mysql> ALTER USER 'telegraf'@'127.0.0.1' WITH MAX_USER_CONNECTIONS 5;

mysql> GRANT REPLICATION CLIENT ON *.* TO 'telegraf'@'127.0.0.1';

mysql> GRANT PROCESS ON *.* TO 'telegraf'@'127.0.0.1';

mysql> ALTER USER 'telegraf'@'127.0.0.1' WITH MAX_USER_CONNECTIONS 5;

telegraf 사용자의 경우에 최대 접속자 수를 5로 제한했다. 프로세스와 리플리케이션 상태를 보기 위해 PROCESS, REPLICATION CLIENT 권한을 부여 했다.

mysql> GRANT SELECT ON performance_schema.* TO 'telegraf'@'127.0.0.1';

1	mysql> GRANT SELECT ON performance_schema.* TO 'telegraf'@'127.0.0.1';

performance_schema 데이터베이스에 대한 SELECT 권한을 부여 한다. 성능관련 지표들을 performance 스키마에 기록하는데, 이에 대한 접근권한을 부여 한 것이다.

결론

무수히 많은 메트릭들을 제공하는 MySQL 이라 어떤 것을 수집할지에 따라서 부여해야할 권한도 달라지지만, 대략적으로 이 정도면 MySQL 을 모니터링하는데 무리는 없다.

출처: The MySQL Input Plugin for Telegraf gathers data from a MySQL server.

09/19/2018

Error – The server time zone value ‘KST’ is unrecognized

Java 와 MySQL 을 연동하는 상황에서 다음과 같은 오류를 만나기도 한다.

HTTP Status 500 – Internal Server Error

Type Exception Report

Message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value 'KST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

HTTP Status 500 – Internal Server Error

Type Exception Report

Message Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: Cannot create PoolableConnectionFactory (The server time zone value 'KST' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.)

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

자세히 보면 java.sql.SQLException 이 보인다. 이 경우는 결국에는 데이터베이스쪽에 문제가 있다는 것이며, MySQL을 사용할 경우에 보이게 된다. 이는 MySQL의 시간을 나타내는 타임존 설정이 맞지 않아 생기는 오류다.

MySQL 5.7, MariaDB 10

MySQL 5.7 과 MariaDB 10 을 사용한다면 my.cnf 에서 다음과 같이 설정함으로써 문제 해결이 가능하다.

# time zone 설정
default-time-zone=Asia/Seoul

1 2	# time zone 설정 default-time-zone=Asia/Seoul

설정할 수 있는 타임존 리스트는 MySQL 메뉴얼을 참조하기 바란다. 이렇게 했는데도 다음과 같은 오류를 만날 가능성도 있다.

[ERROR] Fatal error: Illegal or unknown default time zone 'Asia/Seoul'

1	[ERROR] Fatal error: Illegal or unknown default time zone 'Asia/Seoul'

이럴때는 다음과 같이 해준다.

/opt/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -p

1	/opt/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo \| mysql -u root mysql -p

04/22/2018

MySQL 8 설치하기

MySQL 8 은 그동안의 버저닝을 버린 최초의 메이저 버전 업데이트라고 할 수 있다. 그만큼 기념할 만큼 큰 변화를 예고했던 버전이며 실제로 많은 변화가 있었다.

MySQL 8 의 설치는 5.7 과 크게 차이가 없다.

컴파일 옵션

apt install libssl-dev libncurses5-dev libaio-dev libnuma-dev libevent-dev

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0.11 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_SYSTEMD=1 \
-WITH_EDITLINE=/usr \
-WITH_LIBEVENT=/usr \
-WITH_ZLIB=/usr \
-DWITH_BOOST=/usr/src/boost \
-DDOWNLOAD_BOOST=1

apt install libssl-dev libncurses5-dev libaio-dev libnuma-dev libevent-dev

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0.11 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_SYSTEMD=1 \

-WITH_EDITLINE=/usr \

-WITH_LIBEVENT=/usr \

-WITH_ZLIB=/usr \

-DWITH_BOOST=/usr/src/boost \

-DDOWNLOAD_BOOST=1

컴파일러 옵션중에 스토리지 엔진관련해서 변경사항이 있었다. InnoDB, MyISAM, MERGE, MEMORY, CSV 엔진은 이제 기본이 됐다. 명시적으로 지정할 필요가 없다. 엔진 옵션으로 ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED 등을 선택할 수 있다.

-DMUTEX_TYPE 옵션으로 InnoDB 에 대한 뮤텍스 타입을 지정하는 건데, 기본값으로 event 다. 이를 위해서 libevent 가 필요하며 이를 지정하기 위해서 -WITH_LIBEVENT 를 해주면 된다.

MySQL 8 에서는 대부분의 라이브러리들이 bundle, system 으로 선택을 할 수있다. 안정성을 위해서 bundle 을 이용하는 걸 권장하는 듯 하지만 system 이나 별도 컴파일 한 라이브러리 사용을 위해서 지정해 줄수도 있다.

좀 더 많은 컴파일 옵션은 “2.9.4 MySQL Source-Configuration Options” 을 참조 하면 된다.

소스를 받아 컴파일할때 주의할 점은 바로 컴파일을 위한 하드디스크 공간이다. 이전 버전 보다 컴파일을 위한 하드디스크 공간을 많이 상용한다.

Filesystem      Size  Used Avail Use% Mounted on
/dev/vdb1       9.8G  6.6G  2.7G  72% /mnt

1 2	Filesystem Size Used Avail Use% Mounted on /dev/vdb1 9.8G 6.6G 2.7G 72% /mnt

무려 6.6G 정도의 용량이 사용된다.

설치된 바이너리 용량 대략 1.3G 정도다.

# du -sh mysql-8.0.11/
1.3G	mysql-8.0.11/

1 2	# du -sh mysql-8.0.11/ 1.3G mysql-8.0.11/

설치 후 작업.

설치시에 SYSTEMD 옵션을 주었다면 이제 Systemd 를 사용할 수 있으며 MySQL 8 은 이를 위한 파일을 생성해 준다. 이 파일은 설치디렉토리에 lib/systemd/system 디렉토리에 파일이 존재한다.

심볼릭 링크를 생성해 준다.

]# ln -s mysql-8.0.11 mysql

1	]# ln -s mysql-8.0.11 mysql

계정 생성을 해줘야 하는데, 다음과 같이 해주면 된다.

/usr/sbin/groupadd -g 47 -o -r mysql 
/usr/sbin/useradd -M -g mysql -o -r -d /opt/mysql/logs -s /bin/false -c "MySQL Server" -u 47 mysql

1 2	/usr/sbin/groupadd -g 47 -o -r mysql /usr/sbin/useradd -M -g mysql -o -r -d /opt/mysql/logs -s /bin/false -c "MySQL Server" -u 47 mysql

필요한 디렉토리 생성해 준다. pid, sock 파일을 위한 run 디렉토리와 logs 디렉토리를 생성해준다. 사실 여기서 logs 디렉토리는 사용되지는 않는다.

]# mkdir /opt/mysql/run
]# mkdir /opt/mysql/logs

1 2	]# mkdir /opt/mysql/run ]# mkdir /opt/mysql/logs

소유권을 변경해준다. MySQL이 제대로 동작하기 위해서는 설치 디렉토리에 대한 소유권을 제대로 설정해 줘야 한다.

]# chgrp -R mysql mysql-8.0.11
]# cd mysql-8.0.11
]# chown mysql run logs

]# chgrp -R mysql mysql-8.0.11

]# cd mysql-8.0.11

]# chown mysql run logs

MySQL 라이브러리 시스템 인식.

echo "/opt/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
ldconfig

1 2	echo "/opt/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ldconfig

데이터 디렉토리 초기화.

다음과 같이 데이터 디렉토리를 초기화 해준다.

cd /opt/mysql
bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
# bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

cd /opt/mysql

bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

# bin/mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

03/16/2018

MySQL 5.7 소스 설치

이 문서는 MySQL 5.7 소스 설치 문서 입니다.

Boost 라이브러리

MySQL 5.7 로 넘어오면서 GIS관련해 기능과 InnoDB Engine 에서 R-tree indexes 가 포함되었다. geometry compute 위한 많은 native code 들로 작성이 되었는데 이를 위해서 Boost.Geometry 를 이용했다. 따라서 소스 설치시에 이 라이브러리를 필요로한다.

Boost 라이브러리를 컴파일 단계에서 다음의 옵션으로 알려줄 수 있다.

-DWITH_BOOST: Cmake 컴파일러에게 Boost 지점를 알려준다. Boost 지점은 다음의 셋중에 하나여야 한다.

tarball/zip 파일
tarball/zip 파일을 포함하는 디렉토리
tarball/zip 파일을 압축해제한 디렉토리

-DDOWNLOAD_BOOST: boolean 값으로 Boost tarball/zip 파일을 자동으로 다운로드 받게 할지 말지를 결정한다.

두 옵션을 조합해서 사용하기도 한다. 우선순위는 DWITH_BOOST 이며 여기서 찾지 못할 경우에 DDOWNLOAD_BOOST 를 체크해 다운로드를 할지 말지를 결정한다.

하지만 최근의 MySQL 5.7 소스에는 boost 를 포함해서 배포한다. 따라서 DWITH_BOOST 옵션을 사용해서 포함된 boost 디렉토리를 지정해주면 된다.

준비

CentOS 가 최소설치되었다고 가정하고 시작했기 때문에 컴파일 환경을 구축해줘야 한다.

]$ yum install gcc.x86_64 gcc-c++.x86_64 wget.x86_64 bzip2-devel.x86_64 pkgconfig.x86_64 openssl-devel.x86_64 make.x86_64 man-db.x86_64 nasm.x86_64 readline-devel.x86_64 ncurses-devel.x86_64 automake* autoconf* pcre-devel.x86_64 cmake.x86_64 libaio-devel.x86_64 libevent-devel.x86_64 bison.x86_64 bison-devel.x86_64 pam-devel.x86_64

]$ yum install gcc.x86_64 gcc-c++.x86_64 wget.x86_64 bzip2-devel.x86_64 pkgconfig.x86_64 openssl-devel.x86_64 make.x86_64 man-db.x86_64 nasm.x86_64 readline-devel.x86_64 ncurses-devel.x86_64 automake* autoconf* pcre-devel.x86_64 cmake.x86_64 libaio-devel.x86_64 libevent-devel.x86_64 bison.x86_64 bison-devel.x86_64 pam-devel.x86_64

Ubuntu

apt install build-essential g++ bison libncurses5-dev libaio1 cmake libevent-2.0-5

1	apt install build-essential g++ bison libncurses5-dev libaio1 cmake libevent-2.0-5

Download and Unpack

cd /usr/src
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.21.tar.gz
tar xvzf mysql-boost-5.7.21.tar.gz
cd mysql-5.7.21

cd /usr/src

wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-boost-5.7.21.tar.gz

tar xvzf mysql-boost-5.7.21.tar.gz

cd mysql-5.7.21

Configure and Compile

cmake 를 사용하기 때문에 일반적인 configure 와는 다르다. cmake 대로 옵션을 제공한다. 다음과 같다. 보시면 무엇을 의미하는지 알수 있을 것이다.

-CMAKE_INSTALL_PREFIX:PATH=/opt/mysql5.7.21
DWITH_engine_STORAGE_ENGINE nor -WITHOUT_engine_STORAGE_ENGINE 사용하고자 하는 스토리지 엔진을 지정하는 옵션입니다. engine 에는 모두 대문자로 쓰며, MySQL에서 지원하는 엔진들을 적어주면 됩니다. ex) -WITH_INNOBASE_STORAGE_ENGINE:BOOL=ON -WITHOUT_PARTITION_STORAGE_ENGINE:BOOL=ON
-ENABLED_LOCAL_INFILE:BOOL=boolean SQL파일을 로드하게 해주는 기능을 켭니다. -ENABLED_LOCAL_INFILE:BOOL=ON
-WITH_EXTRA_CHARSETS:STRING=all 추가로 지원할 언어를 지정합니다. 기본값은 all 입니다. -WITH_EXTRA_CHARSETS:STRING=all
-WITH_SSL:STRING=(no, yes, bundled, system) SSL 지원여부 입니다. system으로 할경우에 시스템에 설치된 SSL library를 이용하게 됩니다. SSL 관련 library가 설치되어 있어야 겠죠. -WITH_SSL:STRING=system
-WITH_ZLIB:STRING=(bundled, system) system으로 할 경우에 시스템에 설치된 Library를 이용합니다. -WITH_ZLIB:STRING=system
-WITH_READLINE:BOOL=boolean readline 지원여부입니다. -WITH_READLINE:BOOL=ON

이러한 옵션들은 다음과 같이 확인이 가능하다.

cmake -LAH

1	cmake -LAH

이제 필요한 옵션들을 다음과 같이 해준다. 그리고 컴파일하고 설치해준다.

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.7.21 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_READLINE=1 \
-DWITH_SYSTEMD=1 \
-DWITH_BOOST=/usr/src/mysql-5.7.21/boost/boost_1_59_0 \
-DDOWNLOAD_BOOST=1 
make 
make install

cmake -DCMAKE_INSTALL_PREFIX=/opt/mysql-5.7.21 \

-DWITH_INNOBASE_STORAGE_ENGINE=1 \

-DWITH_PARTITION_STORAGE_ENGINE=1 \

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DWITH_EXTRA_CHARSETS=all \

-DENABLED_LOCAL_INFILE=1 \

-DWITH_READLINE=1 \

-DWITH_SYSTEMD=1 \

-DWITH_BOOST=/usr/src/mysql-5.7.21/boost/boost_1_59_0 \

-DDOWNLOAD_BOOST=1

make

make install

설치후 작업

설치가 끝난 후에는 수동으로 옮겨주어야할 파일들이 존재 한다. 그래서 설치 후 작업을 다음과 같이 진행 하면 된다.

계정을 추가

/usr/sbin/groupadd -g 27 -o -r mysql 
/usr/sbin/useradd -M -g mysql -o -r -d /opt/mysql/logs -s /bin/false -c "MySQL Server" -u 27 mysql

1 2	/usr/sbin/groupadd -g 27 -o -r mysql /usr/sbin/useradd -M -g mysql -o -r -d /opt/mysql/logs -s /bin/false -c "MySQL Server" -u 27 mysql

심볼릭 링크를 다음과 같이 생성해 줍니다.

cd /opt
ln -s mysql5.7.21 mysql

1 2	cd /opt ln -s mysql5.7.21 mysql

디렉토리 설정과 관련된 my.cnf 내용은 다음과 같다.

basedir = /opt/mysql
# 이 디렉토리의 소유권과 그룹은 user 에서 지정한 사용자와 그룹이어야 한다.
datadir = /opt/dbstorage/mysql
# 이 디렉토리는 Mysql 서버가 내부적 작업을 할때 임시로 사용하게 된다.
# 예를들어 대량의 select 처리를 위해서 임시 테이블을 만들거나 할 경우에 여기에 임시로 파일이 생성될 수 있다.
tmpdir  = /opt/dbstorage/mysql/tmp
socket  = /opt/mysql/run/mysql.sock
pid-file = /opt/mysql/run/mysqld.pid

log_error = /opt/dbstorage/mysql/logs/mysql_error.log
general_log_file = /opt/dbstorage/mysql/logs/general_query_all.log

slow_query_log_file = /opt/dbstorage/mysql/logs/slow_query.log

innodb_data_home_dir = /opt/dbstorage/InnoDB
innodb_log_group_home_dir = /opt/dbstorage/InnoDB/redoLogs
innodb_undo_directory = /opt/dbstorage/InnoDB/undoLogs

basedir = /opt/mysql

# 이 디렉토리의 소유권과 그룹은 user 에서 지정한 사용자와 그룹이어야 한다.

datadir = /opt/dbstorage/mysql

# 이 디렉토리는 Mysql 서버가 내부적 작업을 할때 임시로 사용하게 된다.

# 예를들어 대량의 select 처리를 위해서 임시 테이블을 만들거나 할 경우에 여기에 임시로 파일이 생성될 수 있다.

tmpdir = /opt/dbstorage/mysql/tmp

socket = /opt/mysql/run/mysql.sock

pid-file = /opt/mysql/run/mysqld.pid

log_error = /opt/dbstorage/mysql/logs/mysql_error.log

general_log_file = /opt/dbstorage/mysql/logs/general_query_all.log

slow_query_log_file = /opt/dbstorage/mysql/logs/slow_query.log

innodb_data_home_dir = /opt/dbstorage/InnoDB

innodb_log_group_home_dir = /opt/dbstorage/InnoDB/redoLogs

innodb_undo_directory = /opt/dbstorage/InnoDB/undoLogs

다음과 같이 필요한 디렉토리를 생성하고 소유권을 바꿔 준다.

]# cd /opt/mysql
]# mkdir run
]# chown mysql: -R run
]# cd ..
]# chgrp mysql -R mysql*
]# mkdir -p /opt/dbstorage/InnoDB/redoLogs
]# mkdir -p /opt/dbstorage/InnoDB/undoLogs
]# mkdir -p /opt/dbstorage/mysql/tmp
]# mkdir -p /opt/dbstorage/mysql/data
]# mkdir -p /opt/dbstorage/mysql/binlog
]# mkdir -p /opt/dbstorage/mysql/logs
]# chown -R mysql: /opt/dbstorage

]# cd /opt/mysql

]# mkdir run

]# chown mysql: -R run

]# cd ..

]# chgrp mysql -R mysql*

]# mkdir -p /opt/dbstorage/InnoDB/redoLogs

]# mkdir -p /opt/dbstorage/InnoDB/undoLogs

]# mkdir -p /opt/dbstorage/mysql/tmp

]# mkdir -p /opt/dbstorage/mysql/data

]# mkdir -p /opt/dbstorage/mysql/binlog

]# mkdir -p /opt/dbstorage/mysql/logs

]# chown -R mysql: /opt/dbstorage

MySQL 라이브러리 시스템 인식.

]# echo "/opt/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
]# ldconfig

1 2	]# echo "/opt/mysql/lib" > /etc/ld.so.conf.d/mysql.conf ]# ldconfig

시스템 데이터베이스 생성.

]# /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

1	]# /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

정상적으로 끝났다면, /opt/dbstorage/mysql/logs/mysqld_error.log 파일에 임시 루트 패스워드가 기록된다. 이를 참조해서 초기 접속시에 활용해야 한다.

Systemd 스크립트 등록

Systemd 를 사용하기 위해서는 컴파일 옵션을 -DWITH_SYSTEMD=1 를 반드시 해줘야 한다. 그리고 다음과 같이 mysqld.service 파일을 작성한다.

[Unit]
Description=MySQL Server
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
Alias=mysqld.service

[Service]
User=mysql
Group=mysql

Type=forking

# Execute pre and post scripts as root
PermissionsStartOnly=true

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

# Needed to create system tables
ExecStartPre=/opt/mysql/bin/mysqld_pre_systemd

# Start main service
ExecStart=/opt/mysql/bin/mysqld --daemonize $MYSQLD_OPTS

PIDFile=/opt/mysql/run/mysqld.pid

# Give up if ping don't get an answer
TimeoutSec=600

Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=false

[Unit]

Description=MySQL Server

After=network.target

After=syslog.target

[Install]

WantedBy=multi-user.target

Alias=mysqld.service

[Service]

User=mysql

Group=mysql

Type=forking

# Execute pre and post scripts as root

PermissionsStartOnly=true

# Use this to switch malloc implementation

EnvironmentFile=-/etc/sysconfig/mysql

# Needed to create system tables

ExecStartPre=/opt/mysql/bin/mysqld_pre_systemd

# Start main service

ExecStart=/opt/mysql/bin/mysqld --daemonize $MYSQLD_OPTS

PIDFile=/opt/mysql/run/mysqld.pid

# Give up if ping don't get an answer

TimeoutSec=600

Restart=on-failure

RestartPreventExitStatus=1

PrivateTmp=false

이것을 /usr/lib/systemd/system/mysqld.service 로 저장하고 systemd 에 등록해주고 시작해 준다.

]# systemctl enable mysqld.service
Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mysqld.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
]# systemctl start mysqld.service

]# systemctl enable mysqld.service

Created symlink from /etc/systemd/system/mysqld.service to /usr/lib/systemd/system/mysqld.service.

Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.

]# systemctl start mysqld.service

이렇게하고 제대로 구동되었는지를 살펴보면 된다.

패스워드 초기화.

MySQL 5.7 은 설치할때에 root 에 대한 임시패스워드를 발급한다. 하지만 이것은 오직 root 접속을 위한 것으로 임시패스워드 대신 다른 패스워드로 변경하기 전에는 다음과 같이 명령어가 듣질 않는다.

root@localhost:(none) 23:07:21>show tables;
ERROR 1046 (3D000): No database selected
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

root@localhost:(none) 23:07:21>show tables;

ERROR 1046 (3D000): No database selected

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

다음과 같이 패스워드를 변경해 준다.

root@localhost:(none) 23:07:22> ALTER USER 'root'@'localhost' IDENTIFIED BY '비밀번호';    
root@localhost:(none) 23:07:22> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY '비밀번호';    
root@localhost:(none) 23:07:22> ALTER USER 'root'@'%' IDENTIFIED BY '비밀번호';            
root@localhost:(none) 23:07:22> FLUSH PRIVILEGES;

혹은

root@localhost:(none) 23:07:22> SET PASSWORD = '비밀번호';
root@localhost:(none) 23:07:22> FLUSH PRIVILEGES;

root@localhost:(none) 23:07:22> ALTER USER 'root'@'localhost' IDENTIFIED BY '비밀번호';

root@localhost:(none) 23:07:22> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY '비밀번호';

root@localhost:(none) 23:07:22> ALTER USER 'root'@'%' IDENTIFIED BY '비밀번호';

root@localhost:(none) 23:07:22> FLUSH PRIVILEGES;

혹은

root@localhost:(none) 23:07:22> SET PASSWORD = '비밀번호';

root@localhost:(none) 23:07:22> FLUSH PRIVILEGES;

02/10/2018

MySQL 5.7 초기화 오류 메시지

MySQL 5.7 이 릴리즈 된지도 오래 지났다. 인기 있는 데이터베이스 시스템이라서 그런지 현장에서 많이 쓰이는 거 같다. 무엇보다도 Replication 기능의 향상이 많은 사용자를 끌어들이는 느낌이다.

이 글에서는 개인적으로 MySQL 5. 7 를 사용하면서 느낀 변화에 대해서 기술하고자 한다. 변화에 대한 기술은 MySQL 5.6 과 비교한 것이다.

데이터베이스 초기화

기존에는 mysql_install_db 를 사용했지만 이제는 mysqld 에 옵션으로 –initialize 를 주고 실행하면 시스템 데이터베이스와 테이블, innodb 저장소등을 만들어준다.

]# /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql
2018-02-08T14:20:14.336639Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2018-02-08T14:20:14.336744Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-02-08T14:20:14.456328Z 0 [Warning] option 'innodb-autoextend-increment': unsigned value 10485760 adjusted to 1000
2018-02-08T14:20:14.464747Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.
2018-02-08T14:20:14.464756Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html
 100
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
 100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000
2018-02-08T14:21:31.006740Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-02-08T14:21:31.453291Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-02-08T14:21:31.529545Z 0 [ERROR] unknown variable 'log_bin_basename=/opt/dbstorage/mysql/binlog/localhost-01-bin'
2018-02-08T14:21:31.529560Z 0 [ERROR] Aborting

]# /opt/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize --user=mysql

2018-02-08T14:20:14.336639Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.

2018-02-08T14:20:14.336744Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2018-02-08T14:20:14.456328Z 0 [Warning] option 'innodb-autoextend-increment': unsigned value 10485760 adjusted to 1000

2018-02-08T14:20:14.464747Z 0 [Warning] InnoDB: Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed.

2018-02-08T14:20:14.464756Z 0 [Warning] InnoDB: Using innodb_file_format is deprecated and the parameter may be removed in future releases. See http://dev.mysql.com/doc/refman/5.7/en/innodb-file-format.html

100

100 200 300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500 1600 1700 1800 1900 2000

2018-02-08T14:21:31.006740Z 0 [Warning] InnoDB: New log files created, LSN=45790

2018-02-08T14:21:31.453291Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.

2018-02-08T14:21:31.529545Z 0 [ERROR] unknown variable 'log_bin_basename=/opt/dbstorage/mysql/binlog/localhost-01-bin'

2018-02-08T14:21:31.529560Z 0 [ERROR] Aborting

기존의 MySQL 5.6 에서 사용하는 my.cnf 파일을 가져다 초기화를 하면 위와 같이 warining 과 함께 ERROR 가 발생하면서 중단된다.

unkown variable ‘log_bin_basename’

분명히 메뉴얼에는 존재하는데도 인식이 안된다. 알수 없는 옵션이라고 하면서 데이터베이스 초기화가 안되는데 log_bin 옵션에다가 해주면 된다.

log_bin = /opt/dbstorage/mysql/binlog/localhost-01-bin
#log_bin_basename = /opt/dbstorage/mysql/binlog/localhost-01-bin

1 2	log_bin = /opt/dbstorage/mysql/binlog/localhost-01-bin #log_bin_basename = /opt/dbstorage/mysql/binlog/localhost-01-bin

explicit_defaults_for_timestamp

TIMESTAMP 컬럼 데이터 타입에 대한 기본값에 대해 명시적으로 지정을 할지 말지를 결정하는 옵션. 기본은 OFF 이나 그럴 경우에 위와 같이 경고 메시지가 나온다.

TIMESTAMP 를 컬럼에서 사용할때 기본값을 명시하지 않으면 이전 버전에서는 “NOT NULL DEFAULT CURRENT_TIMSTAMP ON UPDATE CURRENT_TIMESTAMP” 가 된다. 하지만 5.7 에선 위 옵션을 이용해서 ON 으로 지정할 경우에 “NULL DEFAULT NULL” 값이 지정이 된다.

‘innodb-autoextend-increment’: unsigned value 10485760 adjusted to 1000

MySQL 5.7 에서 innodb-autoextend-increment 옵션 값은 1 ~ 1000 사이의 값이여야 한다. 단위는 MB 이여서 최고값 1000 은 1GB 가 된다. 이 경고 메시지는 10MB 라고 값을 입력함에 따라 값의 범위를 벗어나 생긴 것이다. 1 ~ 1000 사이의 값을 넣어주면 된다. 단, 단위는 생략.

이 옵션은 file-per-table 테이블스페이스 파일 이나 general 테이블 스페이스 파일에는 아무런 영향을 주지 않는다. 이러한 파일은 innodb-autoextend-increment 세팅과 상관없이 자동으로 확장된다. 초기의 확장은 소량으로 진행되지만 이후에는 4MB 씩 늘어난다.

Using innodb_support_xa is deprecated and the parameter may be removed in future releases. Only innodb_support_xa=ON is allowed

MySQL 5.7.10 버전 이후부터는 항상 활성화 된다. innodb_support_xa 를 비활성화 하면 replication 이 안전하지 않게 되고 바이너리 로그 그룹 커밋(Binary log group commit)과 관련된 성능 향상이 되지 않아 더이상 허용되지 않는다.

Using innodb_file_format is deprecated and the parameter may be removed in future releases.

5.7 이후에 기본값이 Barracuda 로 바뀌었으며 가까운 미래에 없어질 것이다. 원래 이 옵션은 5.1 버전으로 다운그레이드를 위해서 만들어졌으며 5.1 은 더 이상 지원을 하지 않게됨에 따라서 필요하지 않게 되었다.

옵션을 설정하지 않으면 된다.

07/01/2017

MySQL USING VS ON 차이.

MySQL에서 JOIN 을 사용할때에 USING 이나 ON 을 사용한다. 결과적으로 뽑고자 하는 데이터는 모두 동일한데 과연 이둘의 차이는 무엇일까?

첫째로 사용법에서 차이가 있다. USING 은 두 테이블간 필드이름이 같은 경우에 사용한다.

SELECT 
    * 
FROM 
    employees e INNER JOIN salaries s USING(emp_no) 
WHERE 
    e.emp_no BETWEEN 100002 AND 100020 
ORDER BY e.emp_no;

SELECT

FROM

employees e INNER JOIN salaries s USING(emp_no)

WHERE

e.emp_no BETWEEN 100002 AND 100020

ORDER BY e.emp_no;

employees 와 salaries 를 조인(JOIN)하는데 emp_no 를 키가 양쪽 테이블에 모두 있기 때문에 USING 을 사용할 수 있다.
하지만 만일 조인시에 컬럼 이름이 다를 경우에는 ON 을 사용한다. 물론, 컬럼 이름이 같은 것을 기반을 조인을 할때도 ON 을 상용해도 된다.

06/18/2017

Xtrabackup 을 활용한 Replication 설정

Xtrabackup 을 이용한 Slave 추가 하기.

Master 에 Slave 를 추가해야 하는데, Online 상황에서 추가하기는 쉽지가 않다. 대부분 mysldump 를 이용해서 추가 하는 방법이나, mysql data 디렉토리를 압축해서 해제하는 방법을 쓰거나 둘중 하나이다.

하지만 이렇게하면 Online 상태를 유지할 수 없다. Online 상태를 유지면서 Master 의 데이터를 옮기는 방법으로는 Xtrabackup 을 이용하는게 제일 좋다.

먼저, Xtrabackup 툴을 Master 와 Slave 서버에 모두 설치해 준다. 그리고 Master 서버에 다음과 같이 백업을 위한 계정을 생성한다.

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';
FLUSH PRIVILEGES;

CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';

GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost';

FLUSH PRIVILEGES;

그리고 두가지를 생각해봐야 한다. 먼저 새롭게 준비된 Slave 에서 Master 에서 데이터를 땡겨올건지 아니면 Master 에서 Slave 로 쏴줄 것인지이다.

새로운 Slave -> Master 로 접속해서 데이터를 끌어오는 방법은 다음과 같다.

ssh -o StrictHostKeyChecking=no mysql@192.168.96.7 \
"innobackupex \
--host="localhost" \
--user="bkpuser" \
--password="s3cret" \
--no-lock \
--stream=xbstream \
/opt/backup | pv --rate-limit 50000000" 2> innobackupex.log \
| xbstream -x 2> xbstream.log

# 출처: http://gywn.net/2017/01/automated-recovery-scenarios-for-lazy-mysql-dba/

ssh -o StrictHostKeyChecking=no mysql@192.168.96.7 \

"innobackupex \

--host="localhost" \

--user="bkpuser" \

--password="s3cret" \

--no-lock \

--stream=xbstream \

/opt/backup | pv --rate-limit 50000000" 2> innobackupex.log \

| xbstream -x 2> xbstream.log

# 출처: http://gywn.net/2017/01/automated-recovery-scenarios-for-lazy-mysql-dba/

Master -> Slave 로 데이터를 쏴줄때는 다음과 같이 한다.

innobackupex \
--host="localhost" \
--user="bkpuser" \
--password="s3cret" \
--no-lock \
--stream=xbstream /tmp | pv --rate-limit 50000000 \
| ssh -o StrictHostKeyChecking=no mysql@192.168.96.6 \
"cat - | xbstream -x -C /opt/backup"

innobackupex \

--host="localhost" \

--user="bkpuser" \

--password="s3cret" \

--no-lock \

--stream=xbstream /tmp | pv --rate-limit 50000000 \

| ssh -o StrictHostKeyChecking=no mysql@192.168.96.6 \

"cat - | xbstream -x -C /opt/backup"

쏴줄때에 위와같이 하면 백업 진행중에 ssh 접속 패스워드 입력하라는 프롬프트가 나오고 지나가버립니다. 그래도 패스워드를 입력하면 됩니다.

백업 완료되면 디렉토리에 xtrabackup_binlog_info 파일 생성되고 다음과 같은 내용이 포함된다.

localhost-01-bin.000003	543

1	localhost-01-bin.000003 543

이 내용을 기반으로 Replication 을 할때에 활용한다.

데이터를 다 끌어오는 동안에 변경된 데이터를 적용해야하는데, 다음과 같이 한다.

innobackupex --apply-log /opt/backup

1	innobackupex --apply-log /opt/backup

그리고 다음과 같이 복원을 시행한다.

innobackupex --copy-back /opt/backup

1	innobackupex --copy-back /opt/backup

그리고 다음과 같이 리플리케이션을 걸어준다.

CHANGE MASTER TO
MASTER_HOST='MASTER_HOST',
MASTER_USER='repl',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='MASTER_LOG_FILE',
MASTER_LOG_POS=MASTER_LOG_POS; 

START slave;

CHANGE MASTER TO

MASTER_HOST='MASTER_HOST',

MASTER_USER='repl',

MASTER_PASSWORD='replpass',

MASTER_LOG_FILE='MASTER_LOG_FILE',

MASTER_LOG_POS=MASTER_LOG_POS;

START slave;

05/20/2017

[번역] 5.6과 5.7 사이의 MySQL 기본 설정 변경.

이 글은 다음의 글을 번역한 것입니다. 전문 번역자가 아니기에 오류가 있음을 미리 밝힙니다.
https://www.percona.com/blog/2016/09/14/mysql-default-configuration-changes-between-5-6-and-5-7/

이 블로그에서, 우리는 MySQL 5.6 과 5.7 사이에 기본 설정값의 차이에 대해 논의할 것이다.

MySQL 5.7 은 여러분이 기대했던 다양한 새로운 기능이 추가되었다. 하지만, 현재 변수들 또한 알게모르게 변화가 있었다. MySQL 5.7은 5.6으로부터 거의 40개의 기본값이 변경되었다. 어떤 바뀐값들은 여러분의 서버 성능에 아주 크게 영향을 줄 것이고 어떤것은 알지도 모르체 넘어갈 거다. 나는 각각의 변화가 무엇이고 어떤 의미인지를 살펴볼 것이다.

sync_binlog 와 같은 값들은 여러분의 서버에 아주 큰 영향을 줄 수 있다. 내 동료는, Roel Van de Paar, 다른 블로그 글을 통해서 아주 상세하게 sync_binlog의 영향을 깊게 다뤘다. Sync_binlog 는 어떻게 MySQL이 binlog 를 디스크로 플러시(flush)하는지를 제어한다. 새로운 값 1은 MySQL에게 커밋(Commit) 하는 동안 모든 트랜잭션들을 디스크로 쓰도록 강제한다. 이전에는 binlog 를 플러싱하도록 강제하지 못했었고 OS가 binlog를 플러시하는 시점을 결정하는걸 그져 신뢰해야만 했다.(OS 가 binlog 를 플러시하도록 내버려뒀다.)
https://www.percona.com/blog/2016/06/03/binary-logs-make-mysql-5-7-slower-than-5-6/

Variables	5.6.29	5.7.11
sync_binlog	0	1

퍼포먼스 스키마 변수들은 유용하지 않은것처럼 보이지만, 많은 것들이 기본값이 -1 이다. MySQL은 자동적으로 조정되어지는 변수들을 호출하는데 이 표기법을 사용한다. 오직 performance_schema_max_file_classes 만이 (자동으로) 조정되지 않고 바뀐다. 이것은 퍼포먼스 스키마에서 사용되어지는 파일 명령어들의 숫자다. 이것은 당신이 바꿀 필요가 없는 값이다.

Variables	5.6.29	5.7.11
performance_schema_accounts_size	100	-1
performance_schema_hosts_size	100	-1
performance_schema_max_cond_instances	3504	-1
performance_schema_max_file_classes	50	80
performance_schema_max_file_instances	7693	-1
performance_schema_max_mutex_instances	15906	-1
performance_schema_max_rwlock_instances	9102	-1
performance_schema_max_socket_instances	322	-1
performance_schema_max_statement_classes	168	-1
performance_schema_max_table_handles	4000	-1
performance_schema_max_table_instances	12500	-1
performance_schema_max_thread_instances	402	-1
performance_schema_setup_actors_size	100	-1
performance_schema_setup_objects_size	100	-1
performance_schema_users_size	100	-1

optimizer_switch 와 sql_mode 변수들은 각각 활성화될수도 있고 약간 다른 행동을 유도하게할 수도 있다. MySQL 5.7 에서는 민감도와 보안이 향상시키는데 플래그를 두가지 변수값을 활용할 수 있다.(활성화하거나 다른행동을 하게하도록 값을 지정하는 행위) 이것은 추가적으로 옵티마이저에게(Optimizer) 여러분들의 쿼리가 정확하게 해석되도록 결정하는데 좀더 효율성을 제공한다.

optimzer_switch 에 추가된 세개의 플래그들, 모두 MySQL 5.6 에 존재하며, 옵티마이저의 효율성을 높이기위한 목적으로 MySQL 5.7의 기본값으로 설정되었다. duplicateweedout=on, condition_fanout_filter=on 그리고 derived_merge=on. duplicateweedout 은 옵티 마이저의 세미 조인 구체화 전략의 일부입니다. condition_fanout_filter 은 조건 필터링 사용을 제어하고 derived_merge은 derived 테이블의 머지를 제어하고 뷰를 외부쿼리 블럭으로 제어한다.

https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

http://www.chriscalender.com/tag/condition_fanout_filter/

(위 설정들을) SQL mode 에 추가해도 직접적으로 성능에 영향을 주지 않지만 성능을 높일수 있는 쿼리 작성법을 개선해준다. 몇몇 주목할만한 변화로 select … group by 문에서 모든 필드를 요구하려면 SUM과 같은 함수를 사용하여 집계하거나 group by 절에 넣어야한다. MySQL은 그룹화해야 한다고 가정하지 않으며, 필드가 없는 경우 오류를 발생시킨다. Strict_trans_tables 는 트랜잭션 테이블과 함께 사용되는지에 따라 다른 효과를 발생시킵니다.

명령문은 그것이 유효하지 않거나 데이터 변경을 위한 명령문에서 값이 누락된다면 트랜잭션 테이블은 롤백되었었다. 트랜잭션 엔진을 사용하지 않는 테이블에서 MySQL은 유효하지 않은 데이터가 발생한 레코드에 의존해 행동한다. 첫번째 row 라면 트랜잭션 엔진의 동작과 똑같이 동작하진다. 만일 그렇지 않다면 유효하지 않은 값은 가장 근사한 유효한 값 혹은 컬럼의 기본값으로 변경되어진다. 경고는 발생되지만 데이터는 insert 된다.

Variables	5.6.29	5.7.11
optimizer_switch	index_merge=on index_merge_union=on index_merge_sort_union=on index_merge_intersection=on engine_condition_pushdown=on index_condition_pushdown=on mrr=on,mrr_cost_based=on block_nested_loop=on batched_key_access=off materialization=on, semijoin=on loosescan=on, firstmatch=on subquery_materialization_cost_based=on use_index_extensions=on	index_merge=on index_merge_union=on index_merge_sort_union=on index_merge_intersection=on engine_condition_pushdown=on index_condition_pushdown=on mrr=on mrr_cost_based=on block_nested_loop=on batched_key_access=off materialization=on semijoin=on loosescan=on firstmatch=on duplicateweedout=on subquery_materialization_cost_based=on use_index_extensions=on condition_fanout_filter=on derived_merge=on
sql_mode	NO_ENGINE_SUBSTITUTION	ONLY_FULL_GROUP_BY STRICT_TRANS_TABLES NO_ZERO_IN_DATE NO_ZERO_DATE ERROR_FOR_DIVISION_BY_ZERO NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION

binlog 와 관련해 몇가지 변수가 변경되었다. MySQL 5.7 에 binlog_error_action가 업데이트 됐는데, binlog 를 쓰는중에 오류가 있다면 서버는 중단된다. 이런 일은 흔하지 않지만, 이런일이 발생하면 여러분의 애플리케이션과 리플리케이션에 큰 피해를 발생시키고 그것이 고쳐질때까지 서버는 그 어떤 추가적인 트랜잭션도 실행하지 않는다.

binlog 의 기본 포맷도 이전의 statement 대신에 ROW 로 변경되었다. Statement 는 로그에 적은 데이터를 쓴다. 하지만 많은 명령문들이, update … order by rand()을 포함해, 정확하게 복제되어지지 않았었다. 이러한 비결정적인 명령문은(non-deterministic statements) 마스터와 슬레이브에서 서로 다른 결과셋을 가진다. ROW 포맷으로 변화는 좀 더 많은 데이터를 binlog 에 쓰지만, 정보가 정확하고 올바른 복제를 보장한다.

MySQL은 전통적인 binlog 포지션 방식대신에 GTID 를 사용하는 리플리케이션에 초점을 맞추기 시작했다. MySQL이 시작하거나 재시작할때, 이전에 사용된 GTID 의 목록을 생성해야 한다. 만일 binlog_gtid_simple_recovery가 OFF거나 FALSE 라면 서버는 새로운 binlog 로 시작하고 previous_gtids_log_event 대해서 binlog 파일 검색을 거꾸로 반복한다. 만일 이것이 ON, True 라면 서버는 최신과 가장 오래된 binlog 파일을 검토하고 사용된 gtid 를 계산한다. Binlog_gtid_simple_recovery는 binlogs 를 좀 더 빠르게 파악하게 해주며, 특히 GTID 이벤트가 없는 아주 많은 수의 바이너리 로그를 빠르게 파악해준다. 하지만, 특별한 경우에 이것은 gtid_executed 와 gtid_purged 가 잘못 채워질 수도 있다. 이것은 오직 MySQL 5.7.5 나 그 이후버전에 의해서 새로운 binlog 가 생성되어질때 발생하거나 SET GTID_PURGED 명령문이 5.7.7 보다 이전버전에서 실행될때 발생된다.

5.7에서 업데이트된 또다른 리플리케이션 기반 변수는 slave_net_timeout 이다. 이것은 60s 미만이여야 한다. 이전 버전의 리플리케이션 쓰레드는 적어도 한 시간에 문제가 발생될때까지 마스터로 연결이 깨졌다고 판단하지 않았다. 이 변화는 연결에 문제가 있다는 것을 좀더 빠르게 알려주고 여러분이 이슈를 알기전에 복제가 크게 뒤쳐지지 않도록 해준다.

Variables	5.6.29	5.7.11
binlog_error_action	IGNORE_ERROR	ABORT_SERVER
binlog_format	STATEMENT	ROW
binlog_gtid_simple_recovery	OFF	ON
slave_net_timeout	3600	60

InnoDB 버퍼 풀 변경은 서버 시작 및 중지 시간에 영향을 준다.

innodb_buffer_pool_dump_at_shutdown 과 innodb_buffer_pool_load_at_startup 는 서버를 ‘warm up’ 하지 못하도록 함께 쓰인다. 이름처럼, 이것은 셧다운시에 버퍼풀 덤프를 유발하고 시작시에는 로드 된다. 비록 여러분이 100Gb 용량의 버퍼풀을 가지고 있어도, 쓰여진 데이터는 훨씬 작기 때문에 버퍼풀 용량과 똑같은 디스크 용량을 확보할 필요는 없다. 디스크에 쓰여지는 것은 실제 데이터, 테이블공간 과 페이지ID 를 찾는데 필요한 정보만이다.

Variables	5.6.29	5.7.11
innodb_buffer_pool_dump_at_shutdown	OFF	ON
innodb_buffer_pool_load_at_startup	OFF	ON

이제 MySQL 은 5.6과 그 이전 버전에 InnoDB 에 구현된 몇가지 옵션들을 기본값으로 만들었다. InnoDB 의 체크섬 알고리즘을 innodb 에서 crc32로 개선했는데, 이는 최근 Intel CPU 의 하드웨어 가속 기능을 활용할 수 있는 장점이 있다.

Barracuda 파일 포맷은 5.5 이후부터 활용되기 시작해 5.6 에서 많은 개선이 이루어졌으며 이제 5.7 에서는 기본값이 되었다. Barracuda 포맷은 압축과 동적 로우 포맷을 사용할 수 있다. 내 동료 Alexey 는 압축된 포맷의 사용과 최적화 때 그가 본 결과에 대해서 글을 썼다. https://www.percona.com/blog/2008/04/23/real-life-use-case-for-barracuda-innodb-file-format/

innodb_large_prefix 는 기본값이 ‘on’ 이며 Barracuda 파일 포맷과 조합해서 사용할 경우에 좀 더 큰 index key prefix 를(3072 bytes보다 큰) 생성할 수 있다. 이것은 아주 큰 텍스트 필드의 인덱스에서 장점이 된다. 만약 이것이 ‘off’ 라면, 로우 포맷은 동적이거나 압축이 되지 않으며, 767 bytes 보다 큰 index prefix는 자동으로 잘린다. MySQL은 5.7.6에서 더 큰 InnoDB 페이지 크기 (32k 및 64k)를 도입했다.

MySQL 5.7 은 innodb_log_buffer_size 값이 충분히 커졌다. InnoDB 는 바이네리 로그에 디스크로 쓰는 동안에 로그 트랜잭션를 위한 로그 버퍼를 사용한다. 증가된 크기는 로그를 디스크로 플러쉬하는 빈도를 줄이고, I/O를 줄이며, 커밋하기 전에 디스크에 기록 할 필요없이 로그에보다 큰 트랜잭션을 저장할 수 있다.

MySQL 5.7 은 MySQL 5.5 에서 thread contention 을 줄이기 위해서 InnoDB의 퍼지 연산들을 백그라운드 쓰레드로 옮겼다. 최신 버전에서 퍼지 쓰레드는 4개로 기본값이 늘었지만 1에서 32쓰레드까지 언제든지 변경할 수 있다.

MySQL 5.7 은 이제 innodb_strict_mode 가 기본값이 됐고 몇몇 경고들은 오류로 바뀌었다. create table, alter table, create index, optimize table 명령문에서 문법 오류는 에러를 발생시키고 실행전에 사용자가 수정해야만 한다. 또 insert 나 update 구문이 선택한 페이지 크기에 비해 아주 큰 레코드 때문에 실패하지 않도록 레코드 크기를 체크할 수 있다.

Variables	5.6.29	5.7.11
innodb_checksum_algorithm	innodb	crc32
innodb_file_format	Antelope	Barracuda
innodb_file_format_max	Antelope	Barracuda
innodb_large_prefix	OFF	ON
innodb_log_buffer_size	8388608	16777216
innodb_purge_threads	1	4
innodb_strict_mode	OFF	ON

MySQL 은 equality 범위를 평가할때 옵티마이저가 index로 넘어가는 횟수를 증가시켰다. 만약 옵티마이져가 eq_range_index_dive_limit 보다 더 많이 index로 넘어가길 원한다면, MySQL 5.7 에서 기본값 200, index 통계를 사용한다. 여러분은 이것을 0부터, 인엑스 다이빙 제한, 4294967295 까지 수정할 수 있다. 이것은 테이블 통계가 무작위 샘플의 카디널리티를 기반으로 하기 때문에 쿼리 성능에 큰 영향을 줄 수 있다. 이로 인해 최적화 프로그램은 인덱스 다이빙보다 검토 할 훨씬 많은 행 집합을 평가하고, 옵티마이 저가 쿼리를 실행하기 위해 선택한 방법을 변경하게 할 수 있다.

MySQL 5.7 에서 log_warnings 은 사라졌다. 대신 log_error_verbosity 를 사용된다. 기본값은 3이고 에러로그에 errors, warnings, notes 가 기록된다. 여러분은 이것을 1(errors 만) 이나 2(errors 와 warnings) 로 변경할 수 있다. 에러 로그를 참조할때, verbosity 는 좋은 것이다. 하지만 이것은 error 로깅을 위해서 디스크 공간과 I/O를 증가 시킨다.

table_open_cache_instances 는 MySQL 5.7.8 부터 변경 되었다. instance 의 숫자는 1에서 16까지 증가된다. 이것은 두가지 장점이 있는데, DML 구문의 contention 을 줄여주고, 캐쉬 접근의 세분화 된다. instance 수를 증가시킴으로써, DML 구문은 전체 캐쉬가 아닌 하나의 instance 만 잠글수 있다. 한가지 중요한 사실은 DDL 구문은 여전히 전체 캐쉬에 잠금(lock)를 필요로 한다. 시스템에서 많은 수의 세션이 캐쉬에 접근하면 성능은 증가 된다.

Variables	5.6.29	5.7.11
eq_range_index_dive_limit	10	200
log_warnings	1	2
table_open_cache_instances	1	16

5.7 에서 기본값에 많은 변경이 있다. 하지만 이렇게 많은 옵션들은 오랫동안 존재해왔고 사용자에게 친숙해야 한다. 많은 사람들이 이 변수들을 사용했고 그들은 MySQL을 앞으로 나가게 하는 가장 좋은 방법이다. 하지만 기억해야 할 것은, 여러분은 여전히 이러한 변수들을 편집할 수 있고 서버가 여러분의 데이터를 위해 최고 동작하도록 그들을 설정할 수 있다.

12/04/2016

Apache Tomcat JNDI 설정

Apache Tomcat 도 JNDI 설정을 할 수 있다. 특히나 데이터베이스 연결을 위해서 JNDI 설정을 사용할 수 있다. JNDI 를 사용하면 Web Application 내에서 데이터베이스 연결을 할 필요가 없이 네이밍(Naming) 을 호출함으로써 간단히 해결된다.

이 문서에서는 MySQL 을 위한 Apache Tomcat JNDI 설정 에 대한 것이다.

MySQL Connector/J 설치

JNDI 를 이용해서 MySQL 연결을 설정하기 위해서는 MySQL Connector/J 를 먼저 설치해줘야 한다. 이것은 MySQL 홈페이지에서 다운로드 가능한데 파일이 jar 확장자를 가진 하나의 파일이 필요하다. 이 파일을 Apache Tomcat 라이브러리 디렉토리에 복사해주면 끝난다.

cp mysql-connector-java-5.1.39-bin.jar /opt/apache-tomcat-8.0.30/lib

1	cp mysql-connector-java-5.1.39-bin.jar /opt/apache-tomcat-8.0.30/lib

Apache Tomcat JNDI 설정

MySQL Connector/J 를 설치했다면, 이제 Apache Tomcat JDNDI 설정을 해야 한다. 이는 Apache Tomcat 의 conf 디렉토리에 context.xml 파일을 다음과 같이 설정 한다. 이 설정을 위해서는 MySQL 연결 정보를 알고 있어야 한다.

]# vim /opt/apache-tomcat-8.0.30/conf/context.xml

    <Resource name="jdbc/MySQLDS" auth="Container" type="javax.sql.DataSource"
                           maxTotal="100" maxIdle="30" maxWaitMillis="10000"
                                          username="spring" password="ald!rjflk" driverClassName="com.mysql.jdbc.Driver"
                                          url="jdbc:mysql://192.168.96.30:3306/spring"/>

]# vim /opt/apache-tomcat-8.0.30/conf/context.xml

<Resource name="jdbc/MySQLDS" auth="Container" type="javax.sql.DataSource"

maxTotal="100" maxIdle="30" maxWaitMillis="10000"

username="spring" password="ald!rjflk" driverClassName="com.mysql.jdbc.Driver"

url="jdbc:mysql://192.168.96.30:3306/spring"/>

이렇게 해주고 Web Application 에 web.xml 파일을 설정이 필요하다.

web.xml 설정

Java Web Application 에서는 web.xml 파일이 존재한다. 여기에 JNDI 을 인식시키기 위해서 다음과 같이 설정 해줘야 한다.

]# vim web.xml

  <resource-ref>
    <description>MySQL DataSource</description>
    <res-ref-name>jdbc/MySQLDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

]# vim web.xml

<resource-ref>

<description>MySQL DataSource</description>

<res-ref-name>jdbc/MySQLDS</res-ref-name>

<res-type>javax.sql.DataSource</res-type>

<res-auth>Container</res-auth>

</resource-ref>

Spring Context 설정

Spring 을 사용한다면 다음과 같이 JNDI 를 호출 할 수 있다.

]# vim root-context.xml

	<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName" value="java:comp/env/jdbc/MySQLDS" />
		<property name="resourceRef" value="true" />		
	</bean>

]# vim root-context.xml

</bean>

여기서 주의해야할 것은 jndiName 이 ‘comp/env/’ 를 덧붙인다는데 있다. 앞에 설정을 보면 ‘jdbc/MySQLDS’ 로만 설정 된되지만 Spring 에선 ‘comp/env/’ 를 앞에서 붙여주는 것이 다르다.

07/17/2016

M-HA MySQL 설정

MySQL 은 인기있는 오픈소스 데이터베이스 시스템이다. 언제나 데이터베이스 시스템이라면 항상 따라오는 것이 Master-Slave 리플리케이션 환경이고 거기에 덧붙여 자동 페일오버(Auto FailOver) 를 어떻게 할 것이라는 골머리 앓는 주제가 따라서 나온다.

MySQL 의 경우에 자체 적인 FailOver 솔루션이 있다. Fabric 이 그것인데, 아직은 많이 쓰이지는 않는 모양이다. 대신에 AutoFailOver 를 지원하는 제3의 툴들이 존재하는데 M-HA 가 바로 이러한 솔루션이다.

M-HA 는 일본인이 Perl 을 이용해서 작성한 솔루션이다. github 저장소에 소스코드는 공개되어 있으며 저장소이름은 mha4mysql-manager 이다.

구조

M-HA 는 Manager 와 Node 로 이루어진다. Node 는 MySQL 이 설치된 서버에 모두 설치해줘야 한다. Mater/Slave 구조라면 Node 를 모두 설치해줘야 한다.

Manager 는 이들 Node 를 감시하고 이들 전체를 컨트롤하는 역활을 한다.

M-HA 특징

M-HA 를 AutoFailover 로 사용할만한 이유는 다음과 같다.

Short downtime
MySQL-Replication consistency
Easy Installation
No chagne to existing deployments

M-HA 를 고려할때에 중요한 부분이 짧은 다운타임이다. Master/Slave 구조에서 장애시에 Slave 를 Master 로 빠르게 승격을 시켜주어야 한다. 그래야만 두 서버간에 데이터가 달라지는 데이터 무결성이 깨지지 않게 된다. M-HA 는 비교적 이러한 데이터 무결성을 보장하기 위해서 많은 노력을 기울인 솔루션이라고 보면 된다.

M-HA 구성도

최소한의 환경에서 M-HA 를 구성도는 대략 다음과 같다.

M-HA Node 는 MySQL 이 설치된 서버라면 전부 설치해주어야 한다. M-HA Manager 는 외부서버에 설치해된다.

Node 설치

설치는 M-HA 구글 저장소에 가면 각 배포판별로 바이너리로 배포하고 있어 쉽게 설치가능하다. 여기서는 소스를 가지고 컴파일 설치하도록 하겠다.

git 저장소에 소스를 다운받거나 Clone 해서 가지고 와도 된다.

]# git clone https://github.com/yoshinorim/mha4mysql-node.git
Cloning into 'mha4mysql-node'...
remote: Counting objects: 374, done.
remote: Total 374 (delta 0), reused 0 (delta 0), pack-reused 373
Receiving objects: 100% (374/374), 84.63 KiB | 0 bytes/s, done.
Resolving deltas: 100% (203/203), done.
Checking connectivity... done.

]# git clone https://github.com/yoshinorim/mha4mysql-node.git

Cloning into 'mha4mysql-node'...

remote: Counting objects: 374, done.

remote: Total 374 (delta 0), reused 0 (delta 0), pack-reused 373

Receiving objects: 100% (374/374), 84.63 KiB | 0 bytes/s, done.

Resolving deltas: 100% (203/203), done.

Checking connectivity... done.

의존성 패키지를 다음과 같이 설치해준다.

]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential

1	]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential

Perl 을 이용해서 소스를 코드를 컴파일 하고 설치해주면 된다.

# cd mha4mysql-node/
# perl Makefile.PL PREFIX=/usr/local
include /usr/src/mha4mysql-node/inc/Module/Install.pm
include inc/Module/Install/Metadata.pm
include inc/Module/Install/Base.pm
include inc/Module/Install/Makefile.pm
include inc/Module/Install/Scripts.pm
include inc/Module/Install/AutoInstall.pm
include inc/Module/Install/Include.pm
include inc/Module/AutoInstall.pm
*** Module::AutoInstall version 1.16
*** Checking for Perl dependencies...
[Core Features]
- DBI        ...loaded. (1.634)
- DBD::mysql ...loaded. (4.033)
*** Module::AutoInstall configuration finished.
include inc/Module/Install/WriteAll.pm
include inc/Module/Install/Win32.pm
include inc/Module/Install/Can.pm
include inc/Module/Install/Fetch.pm
Checking if your kit is complete...
Warning: the following files are missing in your kit:
	META.yml
Please inform the author.
Generating a Unix-style Makefile
Writing Makefile for mha4mysql::node
Writing MYMETA.yml and MYMETA.json
Writing META.yml
# make
cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm
cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm
cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm
cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm
cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm
cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm
cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm
cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm
cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm
cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog
cp bin/save_binary_logs blib/script/save_binary_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs
cp bin/purge_relay_logs blib/script/purge_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs
cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs
"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs
Manifying 4 pod documents
# make install
Manifying 4 pod documents
Installing /usr/local/share/perl/5.22.1/MHA/BinlogHeaderParser.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinder.pm
Installing /usr/local/share/perl/5.22.1/MHA/SlaveUtil.pm
Installing /usr/local/share/perl/5.22.1/MHA/NodeConst.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFindManager.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderElp.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogManager.pm
Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderXid.pm
Installing /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm
Installing /usr/local/man/man1/save_binary_logs.1p
Installing /usr/local/man/man1/purge_relay_logs.1p
Installing /usr/local/man/man1/filter_mysqlbinlog.1p
Installing /usr/local/man/man1/apply_diff_relay_logs.1p
Installing /usr/local/bin/purge_relay_logs
Installing /usr/local/bin/filter_mysqlbinlog
Installing /usr/local/bin/apply_diff_relay_logs
Installing /usr/local/bin/save_binary_logs
Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.22.1/perllocal.pod

# cd mha4mysql-node/

# perl Makefile.PL PREFIX=/usr/local

include /usr/src/mha4mysql-node/inc/Module/Install.pm

include inc/Module/Install/Metadata.pm

include inc/Module/Install/Base.pm

include inc/Module/Install/Makefile.pm

include inc/Module/Install/Scripts.pm

include inc/Module/Install/AutoInstall.pm

include inc/Module/Install/Include.pm

include inc/Module/AutoInstall.pm

*** Module::AutoInstall version 1.16

*** Checking for Perl dependencies...

[Core Features]

- DBI ...loaded. (1.634)

- DBD::mysql ...loaded. (4.033)

*** Module::AutoInstall configuration finished.

include inc/Module/Install/WriteAll.pm

include inc/Module/Install/Win32.pm

include inc/Module/Install/Can.pm

include inc/Module/Install/Fetch.pm

Checking if your kit is complete...

Warning: the following files are missing in your kit:

META.yml

Please inform the author.

Generating a Unix-style Makefile

Writing Makefile for mha4mysql::node

Writing MYMETA.yml and MYMETA.json

Writing META.yml

# make

cp lib/MHA/NodeUtil.pm blib/lib/MHA/NodeUtil.pm

cp lib/MHA/BinlogHeaderParser.pm blib/lib/MHA/BinlogHeaderParser.pm

cp lib/MHA/BinlogPosFinder.pm blib/lib/MHA/BinlogPosFinder.pm

cp lib/MHA/BinlogPosFinderElp.pm blib/lib/MHA/BinlogPosFinderElp.pm

cp lib/MHA/BinlogPosFindManager.pm blib/lib/MHA/BinlogPosFindManager.pm

cp lib/MHA/BinlogManager.pm blib/lib/MHA/BinlogManager.pm

cp lib/MHA/SlaveUtil.pm blib/lib/MHA/SlaveUtil.pm

cp lib/MHA/NodeConst.pm blib/lib/MHA/NodeConst.pm

cp lib/MHA/BinlogPosFinderXid.pm blib/lib/MHA/BinlogPosFinderXid.pm

cp bin/filter_mysqlbinlog blib/script/filter_mysqlbinlog

"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/filter_mysqlbinlog

cp bin/save_binary_logs blib/script/save_binary_logs

"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/save_binary_logs

cp bin/purge_relay_logs blib/script/purge_relay_logs

"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/purge_relay_logs

cp bin/apply_diff_relay_logs blib/script/apply_diff_relay_logs

"/usr/bin/perl" "-Iinc" -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/apply_diff_relay_logs

Manifying 4 pod documents

# make install

Manifying 4 pod documents

Installing /usr/local/share/perl/5.22.1/MHA/BinlogHeaderParser.pm

Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinder.pm

Installing /usr/local/share/perl/5.22.1/MHA/SlaveUtil.pm

Installing /usr/local/share/perl/5.22.1/MHA/NodeConst.pm

Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFindManager.pm

Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderElp.pm

Installing /usr/local/share/perl/5.22.1/MHA/BinlogManager.pm

Installing /usr/local/share/perl/5.22.1/MHA/BinlogPosFinderXid.pm

Installing /usr/local/share/perl/5.22.1/MHA/NodeUtil.pm

Installing /usr/local/man/man1/save_binary_logs.1p

Installing /usr/local/man/man1/purge_relay_logs.1p

Installing /usr/local/man/man1/filter_mysqlbinlog.1p

Installing /usr/local/man/man1/apply_diff_relay_logs.1p

Installing /usr/local/bin/purge_relay_logs

Installing /usr/local/bin/filter_mysqlbinlog

Installing /usr/local/bin/apply_diff_relay_logs

Installing /usr/local/bin/save_binary_logs

Appending installation info to /usr/local/lib/x86_64-linux-gnu/perl/5.22.1/perllocal.pod

위와 같은 설치를 Master/Slave 모든 MySQL 서버에 설치해 준다.

그리고 각 서버에 M-HA 가 접속하기 위한 MySQL 계정을 생성해 준다.

mysql> grant all on *.* to 'mhauser'@'192.168.96.%'  identified by '14321';
mysql> flush privileges;

1 2	mysql> grant all on . to 'mhauser'@'192.168.96.%' identified by '14321'; mysql> flush privileges;

MySQL 각 서버에 /var/log/masterha 디렉토리를 만들어 준다.

]# mkdir -p /var/log/masterha

1	]# mkdir -p /var/log/masterha

Manager 설치

한가지 주의해야 할 것은 Manager 를 설치하기 전에 Node 도 같이 설치해줘야 한다는 것이다. Manager 설치도 소스코드를 다운받아 설치한다. 그 전에 다음과 같이 의존성 패키지를 설치해준다.

]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl

1	]# apt-get install libdbd-mysql-perl libmodule-install-perl build-essential libconfig-tiny-perl liblog-dispatch-perl libparallel-forkmanager-perl

소스코드를 다운받는다.

# git clone https://github.com/yoshinorim/mha4mysql-manager.git
Cloning into 'mha4mysql-manager'...
remote: Counting objects: 1383, done.
remote: Total 1383 (delta 0), reused 0 (delta 0), pack-reused 1383
Receiving objects: 100% (1383/1383), 371.44 KiB | 305.00 KiB/s, done.
Resolving deltas: 100% (830/830), done.

# git clone https://github.com/yoshinorim/mha4mysql-manager.git

Cloning into 'mha4mysql-manager'...

remote: Counting objects: 1383, done.

remote: Total 1383 (delta 0), reused 0 (delta 0), pack-reused 1383

Receiving objects: 100% (1383/1383), 371.44 KiB | 305.00 KiB/s, done.

Resolving deltas: 100% (830/830), done.

컴파일 설치해 준다.

]# perl Makefile.PL PREFIX=/usr/local
]# make
]# make install

]# perl Makefile.PL PREFIX=/usr/local

]# make

]# make install

Manager 에 mha.cnf 파일 작성

Manager 의 mha.cnf 파일을 다음과 같이 작성한다.

[server default]
# mysql user and password
user=mhauser
password=14321
# working directory on the manager
manager_workdir=/var/log/masterha
# working directory on MySQL servers
remote_workdir=/var/log/masterha

[server1]
hostname=192.168.96.30

[server2]
hostname=192.168.96.32

[server default]

# mysql user and password

user=mhauser

password=14321

# working directory on the manager

manager_workdir=/var/log/masterha

# working directory on MySQL servers

remote_workdir=/var/log/masterha

[server1]

hostname=192.168.96.30

[server2]

hostname=192.168.96.32

SSH 접속 설정

M-HA 는 내부적으로 SSH를 이용해서 M-HA Node 의 릴레이 로그들을 전송하도록 한다. 그런데 자동으로 이게 되게 할라면 M-HA Manager 와 Node 들간 모두 SSH 를 무인증으로 접속이 가능해야 한다.

SSH RSA 를 생성하는데, 패스워드를 입력하지 않는다. 그러면 무인증 접속이 가능해진다.

# ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:Ac6WpLXcWE58hFcxb3RPiALuejV2XZzMhn5oRb4qbxk root@192.168.96.30
The key's randomart image is:
+---[RSA 2048]----+
|      +.++..+o.oo|
|     B @o + .+B+o|
|    . O =o . .oB+|
|     . . .  .o+..|
|        S + .+.o |
|       . o o.Eo  |
|      . .  . .o  |
|       .    oo   |
|            ..   |
+----[SHA256]-----+

# ssh-keygen -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Created directory '/root/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:Ac6WpLXcWE58hFcxb3RPiALuejV2XZzMhn5oRb4qbxk root@192.168.96.30

The key's randomart image is:

+---[RSA 2048]----+

| +.++..+o.oo|

| B @o + .+B+o|

| . O =o . .oB+|

| . . . .o+..|

| S + .+.o |

| . o o.Eo |

| . . . .o |

| . oo |

| .. |

+----[SHA256]-----+

각각에 생성한 키들은 ~/.ssh/authorized_keys 파일에 추가해준다. 중요한 것은 각각 서버들은 서로서로 모두 무인증 SSH 접속이 가능해야 한다는 것이다.

SSH 무인증 테스트를 다음과 같이 할 수 있다.

masterha_check_ssh --conf=/usr/local/etc/mha.cnf
Sun Jul 17 21:01:07 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jul 17 21:01:07 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:01:07 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:01:08 2016 - [info] Starting SSH connection tests..
Sun Jul 17 21:01:09 2016 - [debug]
Sun Jul 17 21:01:08 2016 - [debug]  Connecting via SSH from root@192.168.96.30(192.168.96.30:22) to root@192.168.96.32(192.168.96.32:22)..
Sun Jul 17 21:01:08 2016 - [debug]   ok.
Sun Jul 17 21:01:09 2016 - [debug]
Sun Jul 17 21:01:08 2016 - [debug]  Connecting via SSH from root@192.168.96.32(192.168.96.32:22) to root@192.168.96.30(192.168.96.30:22)..
Sun Jul 17 21:01:09 2016 - [debug]   ok.
Sun Jul 17 21:01:09 2016 - [info] All SSH connection tests passed successfully.

masterha_check_ssh --conf=/usr/local/etc/mha.cnf

Sun Jul 17 21:01:07 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sun Jul 17 21:01:07 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..

Sun Jul 17 21:01:07 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..

Sun Jul 17 21:01:08 2016 - [info] Starting SSH connection tests..

Sun Jul 17 21:01:09 2016 - [debug]

Sun Jul 17 21:01:08 2016 - [debug] Connecting via SSH from root@192.168.96.30(192.168.96.30:22) to root@192.168.96.32(192.168.96.32:22)..

Sun Jul 17 21:01:08 2016 - [debug] ok.

Sun Jul 17 21:01:09 2016 - [debug]

Sun Jul 17 21:01:08 2016 - [debug] Connecting via SSH from root@192.168.96.32(192.168.96.32:22) to root@192.168.96.30(192.168.96.30:22)..

Sun Jul 17 21:01:09 2016 - [debug] ok.

Sun Jul 17 21:01:09 2016 - [info] All SSH connection tests passed successfully.

Replication 체크

다음과 같에 Replication 체크를 해본다.

# masterha_check_repl --conf=/usr/local/etc/mha.cnf
Sun Jul 17 21:26:00 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sun Jul 17 21:26:00 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:26:00 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..
Sun Jul 17 21:26:00 2016 - [info] MHA::MasterMonitor version 0.57.
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations.  at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329.
Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Sun Jul 17 21:26:01 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

# masterha_check_repl --conf=/usr/local/etc/mha.cnf

Sun Jul 17 21:26:00 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Sun Jul 17 21:26:00 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..

Sun Jul 17 21:26:00 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..

Sun Jul 17 21:26:00 2016 - [info] MHA::MasterMonitor version 0.57.

Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/ServerManager.pm, ln188] There is no alive server. We can't do failover

Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/local/share/perl5/MHA/MasterMonitor.pm line 329.

Sun Jul 17 21:26:01 2016 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.

Sun Jul 17 21:26:01 2016 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!

여기서 버그가 있다. /usr/local/share/perl5/MHA/DBHelper.pm 파일 198 라인에 $host 변수에 문제가 있다.

- 155     $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";
+ 155     $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";
- 198     $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";
+ 198     $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";

- 155 $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";

+ 155 $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";

- 198 $self->{dsn} = "DBI:mysql:;host=[$host];port=$port;mysql_connect_timeout=4";

+ 198 $self->{dsn} = "DBI:mysql:;host=$host;port=$port;mysql_connect_timeout=4";

위와같이 수정한 후에 다시 한번 해보면 오류를 해결할 수 있다.

이러한 문제는 호스트 이름에 ‘[‘, ‘]’ 를 붙임으로 나타는 현상이다. 이러한 문제는 다음의 파일에도 있다.

 가 있는 오류"]HealthCheck.pm:98:    "DBI:mysql:;host=[$self->{ip}];"
NodeUtil.pm:85:  my $ssh_user_host = $ssh_user . '@[' . $ssh_host . ']';

1 2	가 있는 오류"]HealthCheck.pm:98: "DBI:mysql:;host=[$self->{ip}];" NodeUtil.pm:85: my $ssh_user_host = $ssh_user . '@[' . $ssh_host . ']';

Manager 실행 시키기.

반드시 위에 버그를 제거한 후에 다음과 같이 실행을 한다.

# masterha_manager --conf=/usr/local/etc/mha.cnf
Mon Jul 18 01:30:43 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jul 18 01:30:43 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..
Mon Jul 18 01:30:43 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..

# masterha_manager --conf=/usr/local/etc/mha.cnf

Mon Jul 18 01:30:43 2016 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.

Mon Jul 18 01:30:43 2016 - [info] Reading application default configuration from /usr/local/etc/mha.cnf..

Mon Jul 18 01:30:43 2016 - [info] Reading server configuration from /usr/local/etc/mha.cnf..

위와같이 실행을 한 후 로그를 보면 다음과 같이 나와야 정상이다.

Mon Jul 18 01:30:49 2016 - [info]
192.168.96.32(192.168.96.32:3306) (current master)
 +--192.168.96.30(192.168.96.30:3306)

Mon Jul 18 01:30:49 2016 - [warning] master_ip_failover_script is not defined.
Mon Jul 18 01:30:49 2016 - [warning] shutdown_script is not defined.
Mon Jul 18 01:30:49 2016 - [debug]  Disconnected from 192.168.96.30(192.168.96.30:3306)
Mon Jul 18 01:30:49 2016 - [debug]  Disconnected from 192.168.96.32(192.168.96.32:3306)
Mon Jul 18 01:30:49 2016 - [debug] SSH check command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/log/mysql --output_file=/var/log/masterha/save_binary_logs_test --manager_version=0.57 --binlog_prefix=mysql-bin --debug
Mon Jul 18 01:30:49 2016 - [info] Set master ping interval 3 seconds.
Mon Jul 18 01:30:49 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Mon Jul 18 01:30:49 2016 - [info] Starting ping health check on 192.168.96.32(192.168.96.32:3306)..
Mon Jul 18 01:30:49 2016 - [debug] Connected on master.
Mon Jul 18 01:30:49 2016 - [debug] Set short wait_timeout on master: 6 seconds
Mon Jul 18 01:30:49 2016 - [debug] Trying to get advisory lock..
Mon Jul 18 01:30:49 2016 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

Mon Jul 18 01:30:49 2016 - [info]

192.168.96.32(192.168.96.32:3306) (current master)

+--192.168.96.30(192.168.96.30:3306)

Mon Jul 18 01:30:49 2016 - [warning] master_ip_failover_script is not defined.

Mon Jul 18 01:30:49 2016 - [warning] shutdown_script is not defined.

Mon Jul 18 01:30:49 2016 - [debug] Disconnected from 192.168.96.30(192.168.96.30:3306)

Mon Jul 18 01:30:49 2016 - [debug] Disconnected from 192.168.96.32(192.168.96.32:3306)

Mon Jul 18 01:30:49 2016 - [debug] SSH check command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/log/mysql --output_file=/var/log/masterha/save_binary_logs_test --manager_version=0.57 --binlog_prefix=mysql-bin --debug

Mon Jul 18 01:30:49 2016 - [info] Set master ping interval 3 seconds.

Mon Jul 18 01:30:49 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.

Mon Jul 18 01:30:49 2016 - [info] Starting ping health check on 192.168.96.32(192.168.96.32:3306)..

Mon Jul 18 01:30:49 2016 - [debug] Connected on master.

Mon Jul 18 01:30:49 2016 - [debug] Set short wait_timeout on master: 6 seconds

Mon Jul 18 01:30:49 2016 - [debug] Trying to get advisory lock..

Mon Jul 18 01:30:49 2016 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..

내용을 보면 192.168.96.32 서버가 현재 Master 고 30번 서버가 Slave 로 인식하고 있다.

FailOver 테스트.

간단한 FailOver 테스트를 해보자. 시나리오는 간단하다. 현재 Master 서버를 정지 시켜 보는 것이다. 그러면 다음과 같이 Manager 로그가 올라온다.

Mon Jul 18 01:27:19 2016 - [debug]  Disconnected from 192.168.96.32(192.168.96.32:3306)
Mon Jul 18 01:27:19 2016 - [info]

----- Failover Report -----

mha: MySQL Master failover 192.168.96.30(192.168.96.30:3306) to 192.168.96.32(192.168.96.32:3306) succeeded

Master 192.168.96.30(192.168.96.30:3306) is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/mha.log for details.

Started automated(non-interactive) failover.
The latest slave 192.168.96.32(192.168.96.32:3306) has all relay logs for recovery.
Selected 192.168.96.32(192.168.96.32:3306) as a new master.
192.168.96.32(192.168.96.32:3306): OK: Applying all logs succeeded.
Generating relay diff files from the latest slave succeeded.
192.168.96.32(192.168.96.32:3306): Resetting slave info succeeded.
Master failover to 192.168.96.32(192.168.96.32:3306) completed successfully.

Mon Jul 18 01:27:19 2016 - [debug] Disconnected from 192.168.96.32(192.168.96.32:3306)

Mon Jul 18 01:27:19 2016 - [info]

----- Failover Report -----

mha: MySQL Master failover 192.168.96.30(192.168.96.30:3306) to 192.168.96.32(192.168.96.32:3306) succeeded

Master 192.168.96.30(192.168.96.30:3306) is down!

Check MHA Manager logs at localhost.localdomain:/var/log/masterha/mha.log for details.

Started automated(non-interactive) failover.

The latest slave 192.168.96.32(192.168.96.32:3306) has all relay logs for recovery.

Selected 192.168.96.32(192.168.96.32:3306) as a new master.

192.168.96.32(192.168.96.32:3306): OK: Applying all logs succeeded.

Generating relay diff files from the latest slave succeeded.

192.168.96.32(192.168.96.32:3306): Resetting slave info succeeded.

Master failover to 192.168.96.32(192.168.96.32:3306) completed successfully.

위와같이 로그가 온다면 FailOver 가 정상적으로 된 것이다. 또, 로그 디렉토리 /var/log/masterha 디렉토리에 mha.failover.complete 파일이 생성되어 있을 것이다. 또한, manager 가 죽어 있다. 그러니까 FailOver 가 발생되면 다음과 같이 실행이 된다.

Slave 를 Master 로 승격시킨다.
Manager 에 지정한 로그 디렉토리에 mha.failover.complete 파일을 생성한다.
Manager 가 죽는다.

FailOver 가 발생할때마다 Manager 는 정해진 동작을 수행하고 자동으로 죽게 된다.

승격된 Master 서버에서는 다음을 체크해봐야 한다.

show slave status 명령을 입력했을때 아무것도 나오지 말아야 한다.
show variables like ‘%read%’ 명령어를 입력했을때에 read_only 값이 OFF 여야 한다.

위 두가지가 정상적으로 나온다면 M-HA 가 정상적으로 동작한 것이다.