mysqlpump 유틸을 이용한 계정백업

1. mysqlpump란?

mysqldump와 다른, MySQL 5.7에서 추가된 백업 유틸리티

• 병렬덤프
• 테이블, 뷰, 스토어드프로그램을 개별로 필터링
• 데이터와 인덱스 복원을 동시에 진행하지 않고, 데이터복원부터 수행

구문

• GTID 환경에서는 --set-gtid-purged-ON 옵션을 사용해야 백업이 진행됨.
• 아래 구문은 시스템유저는 제외한채 백업
• 온라인 환경에서 정합성을 유지하며 백업하기위해 mysqldump와 같이 --single-transaction 옵션을 지정하면 된다.

mysqlpump --user=root --password='Study3306!' --users --exclude-databases=% --exclude-users=root,mysql.infoschema,mysql.session,mysql.sys > /home/dongle/bck_usr.sql

TMI : MySQL 8.0부터 일부 시스템 계정명이 변경되었다.

2. 계정 백업 / 복원

a. 계정 확인

• 제외 할 계정 확인 (minstone, dongle계정만 백업예정)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | minstone         |
| localhost | dongle           |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

b. 백업수행 및 백업파일 확인

mysql> mysqlpump --user=root --password='Study3306!' --users --exclude-databases=% --exclude-users=root,mysql.infoschema,mysql.session,mysql.sys > /home/dongle/bck_usr.sql
mysql> exit
Bye
[root@191 dongle]# ls -al
합계 12
drwxr-xr-x. 2 root root 4096 2022-04-25 16:46 .
drwxr-xr-x. 7 root root 4096 2022-04-25 16:31 ..
-rw-r--r--. 1 root root 1610 2022-04-25 16:45 bck_usr.sql
[root@191 dongle]# vi bck_usr.sql

bck_usr.sql 파일

-- Dump created by MySQL pump utility, version: 8.0.28, Linux (x86_64)
-- Dump start time: Mon Apr 25 16:44:59 2022
-- Server version: 8.0.28

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
CREATE USER `dongle`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT SELECT, UPDATE ON *.* TO `dongle`@`localhost`;
CREATE USER `minstone`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$  H|IE    ^VP^?^R]W`&?_Nx^XQoQEvvtYV6RvSdXybVjIpIarjDemV.hnJvj1t.S3F2S1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
GRANT USAGE ON *.* TO `minstone`@`localhost`;
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Mon Apr 25 16:45:00 2022

c. 계정 복원

• mysql_native_password 패스워드 암호화 계정 dongle 복원 성공
• caching_sha2_password 패스워드 암호화 계정 해시포맷오류로 복원 실패

mysql> CREATE USER `dongle`@`localhost` IDENTIFIED WITH 'mysql_native_password' AS '*FAAFFE644E901CFAFAEC7562415E5FAEC243B8B2' REQUIRE NONE PASSWORD EXP                                                            IRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT SELECT, UPDATE ON *.* TO `dongle`@`localhost`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER `minstone`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$  H|IE    ^VP^?^R]W`&?_Nx^XQoQEvvtYV6RvSdXybVjIpIarjDemV.hn                                                            Jvj1t.S3F2S1' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFA                                                            ULT;
ERROR 1827 (HY000): The password hash doesn't have the expected format.
mysql> GRANT USAGE ON *.* TO `minstone`@`localhost`;
ERROR 1410 (42000): You are not allowed to create a user with GRANT

'caching_sha2_password' 방식의 경우 mysqlpump를 이용한 논리백업은 불가하다.