MySQLDBAdmTech: Percona Toolkit

MySQL 서버의 유저와 권한을 백업하는 여러 방법 중, Percona Toolkit을 이용한 방법을 소개한다.

pt-show-grants는 MySQL 서버의 유저와 유저 권한을 실행가능한 SQL형태로 보여주는 툴이다.

pt-show-grants : https://www.percona.com/doc/percona-toolkit/2.2/pt-show-grants.html

1. pt-show-grants 사용법

기본적인 옵션을 주고 pt-show-grants 툴로 MySQL 서버의 유저 및 권한을 추출해본다.

host=localhost : MySQL 서버 호스트이름
user=root : MySQL 서버 접속 유저 이름
port=13326 : MySQL 서버 접속 포트 번호
socket=/tmp/mysql_13326.sock : MySQL 서버 접속 unix socket
ask-pass : MySQL 서버 접속 시, 수동으로 암호 입력
flush : 유저 권한 추출 결과 최후에 "flush privileges;" 명령어 추가
drop : 유저 생성 전에 "DROP USER" 명령어 추가
charset=utf8

[mysql@testserver ~]$ pt-show-grants --host=localhost --user=root --port=13326 --socket=/tmp/mysql_13326.sock --ask-pass --flush --drop --charset=utf8

Enter password:

-- Grants dumped by pt-show-grants

-- Dumped from server Localhost via UNIX socket, MySQL 5.6.33-log at 2017-01-12 10:51:00

DROP USER 'monitoruser'@'127.0.0.1';

DELETE FROM `mysql`.`user` WHERE `User`='monitoruser' AND `Host`='127.0.0.1';

-- Grants for 'monitoruser'@'127.0.0.1'

GRANT PROCESS, SUPER ON *.* TO 'monitoruser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*DCA31DD086BF1A7KKKKDD3021DEE02882649B4D3';

GRANT SELECT ON `testdb`.* TO 'monitoruser'@'127.0.0.1';

DROP USER 'monitoruser'@'192.168.0.11';

DELETE FROM `mysql`.`user` WHERE `User`='monitoruser' AND `Host`='192.168.0.11';

-- Grants for 'monitoruser'@'192.168.0.11'

GRANT PROCESS, SUPER ON *.* TO 'monitoruser'@'192.168.0.11' IDENTIFIED BY PASSWORD '*DCA31DD086BF1A7KKKKDD3021DEE02882649B4D3';

GRANT SELECT ON `testdb`.* TO 'monitoruser'@'192.168.0.11';

DROP USER 'monitoruser'@'localhost';

DELETE FROM `mysql`.`user` WHERE `User`='monitoruser' AND `Host`='localhost';

-- Grants for 'monitoruser'@'localhost'

GRANT PROCESS, SUPER ON *.* TO 'monitoruser'@'localhost' IDENTIFIED BY PASSWORD '*DCA31DD086BF1A7KKKKDD3021DEE02882649B4D3';

GRANT SELECT ON `testdb`.* TO 'monitoruser'@'localhost';

..........

..........

..........

..........

..........

FLUSH PRIVILEGES;

Colored by Color Scripter

추출한 유저 및 권한을 sql파일로 dump하려면 다음과 같이 실행한다.

[mysql@testserver ~]$ pt-show-grants --host=localhost --user=root --port=13326 --socket=/tmp/mysql_13326.sock --password="xxxxxxxx" --flush --drop --charset=utf8 > /home/mysql/showgrants_result.sql

다양한 추가 옵션들과 그 응용법은 Percona 공식 사이트를 참고한다.

[주의] pt-show-grant 사용 시, 유의사항

간과할 수 있는 중요한 사항이 root등의 관리자(superuser) 계정과 권한까지 dump했을 때다. 만일, 해당 sql파일을 제 3의 MySQL 서버에 복원용으로 실행한다면 기존의 root 의 암호나 권한이 변경될 수(덮어 씌워질 수) 있다.
따라서 pt-show-grant를 사용하여 유저 및 권한을 추출할때는 "--ignore" 옵션을 사용하여 root등의 관리자를 제외하고 추출하거나, "--only" 옵션을 사용하여 특정 유저와 권한만을 추출하는 방법을 검토해보길 바란다.

[mysql@testserver ~]$ pt-show-grants --host=localhost --user=root --port=13326 --socket=/tmp/mysql_13326.sock --ask-pass --flush --drop --charset=utf8 --ignore=root

허나, 아쉽게도 최신버전인 2.2.20에서도 --ignore 옵션은 제대로 동작하지 않는다.

https://bugs.launchpad.net/percona-toolkit/+bugs?field.tag=pt-show-grants

현재로서는 "--only" 옵션을 사용하여 특정 유저 및 권한만 백업하거나, 추출결과인 sql 파일을 직접 수정하여 root계정의 내용을 삭제하는 번거로운 작업을 해야한다.

pt-show-grants - backup MySQL users privileges/grants