독까의 이야기

윈도우 mysql 5.6 DB 백업 스케쥴 생성

Database/MySQL2019. 7. 24. 15:13

윈도우에 설치 된 mysql 5.6 DB 백업 스케쥴 등록 필요.


1.  기본적으로 알려진 덤프 명령어를 이용 

C:/> mysqldump -uroot -p'PaSSword!@#' DBNAME > H:\mysql_Backup\DBNAME\DBNAME_%date%.sql
입력 하였으나 에러 출력

Warning: Using a password on the command line interface can be insecure.
mysqldump: Got error: 1045: Access denied for user 'root'@'::1' (using password: YES) when trying to connect



2.  구글 검색시 mysql 5.6 이상부터 발생되는 보안 이슈라고 함

mysql_config_editor 를 이용한 login-path 설정하면 해결 가능하다고 함

C:/> mysql_config_editor set --login-path=backup --host=localhost --user=root --password
Enter password : PaSSword!@#

확인 :
C:/> mysql_config_editor print --all
[backup]
user = root
password = *****
host = localhost



3.  덤프 명령어 재실행

C:/> mysqldump --login-path=backup DBNAME > H:\mysql_Backup\DBNAME\DBNAME_%date%.sql
입력 하였으나 에러 출력

mysqldump: Got error: 1045: Access denied for user 'root'@'::1' (using password: YES) when trying to connect



4.  또 구글 검색

login-path 생성시 패스워드에 특수 문자가 포함되는 경우에는 패스워드 입력 저장시 " " 큰따옴표로 묶어야 한다고 함
ex) Enter password: "PaSSword!@#"



5.  기존 login-path 삭제 후 재생성

C:/> mysql_config_editor remove --login-path=backup

C:/> mysql_config_editor set --login-path=backup --host=localhost --user=root --password
Enter password: "PaSSword!@#"



6.  덤프 명령어 재재실행

C:/> mysqldump --login-path=backup DBNAME > H:\mysql_Backup\DBNAME\DBNAME_%date%.sql

정상적으로 덤프 파일 생성 확인



7.  mysql 접속 후 등록 된 DB 리스트 확인

mysql> show databases;

출력 된 DB 리스트 취합하여 스크립트 생성 및 실행



8.  스키마 백업시 오류 발생 확인

mysqldump: Got error: 1044: Access denied for user 'root'@'::1' to database 'information_schema' when using LOCK TABLES

lock 이 걸려있는 DB 백업시에는 옵션 추가 :  --single-transaction

C:/> mysqldump --login-path=backup information_schema --single-transaction > H:\mysql_Backup\information_schema\information_schema_%date%.sql

저작자표시 비영리 동일조건

'Database > MySQL' 카테고리의 다른 글

mysql 이중화 구성 (Replication / 리눅스 + 윈도우)  (0) 2018.03.21
윈도우 mysql 서버의 my.ini 파일 위치 확인  (2) 2018.03.20
mysql 구버전 설치 링크  (0) 2018.03.20

SQL DB 암호화 / TDE(Transparent Data Encryption)

Database/MS-SQL2018. 12. 7. 15:03

MS-SQL 2008 ENT 이상 버전에서 지원되는 TDE 기능에 대한 테스트를 진행 한다. 
STD 에서도 복원은 가능하나, DB 는 사용 할 수 없다. 

OS : Windows 2016
DBMS : MS-SQL 2014 ENT

1. DB 생성
DB 명 : gunnm 
USE master;
CREATE DATABASE [gunnm]
ON
( NAME = gunnm, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf')
LOG ON
( NAME = gunnm_log, FILENAME = 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf')
GO




2. 암호화 진행

마스터 키 생성
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord!2#'
GO

마스터 키로 보호 된 인증서 생성
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'gunnm_cert'
GO

마스터 키 백업
USE master
BACKUP SERVICE MASTER KEY TO FILE = 'H:\service_master.key' ENCRYPTION BY PASSWORD = 'Service_PW@1119'
GO
BACKUP MASTER KEY TO FILE = 'H:\db_master.key' ENCRYPTION BY PASSWORD = 'DB_PW#1119'
GO

DB 암호화 키 생성
USE gunnm
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE gunnm_cert 
GO

실행 메시지 
경고: 데이터베이스 암호화 키를 암호화하는 데 사용된 인증서가 백업되지 않았습니다. 인증서와 인증서에 연결된 개인 키를 즉시 백업해야 합니다. 인증서를 사용할 수 없게 되거나 다른 서버에서 데이터베이스를 복원하거나 연결해야 할 경우 인증서와 개인 키의 백업본이 있어야 합니다. 그렇지 않으면 데이터베이스를 열 수 없습니다.

DB 암호화 적용
USE gunnm
GO
ALTER DATABASE gunnm
SET ENCRYPTION ON
GO

DB 암호화 키 백업
USE master
BACKUP CERTIFICATE gunnm_cert TO FILE = 'H:\gunnm_cert.cer' WITH PRIVATE KEY ( FILE = 'H:\gunnm_cert.pvk' , ENCRYPTION BY PASSWORD = 'gunnm_PW$1119' )
GO

DB 백업
BACKUP DATABASE [gunnm] TO  DISK = 'H:\gunnm.bak';
GO



3. 대상 서버에서 DB 복원 진행

원본 서버에서 생성 된 DB 인증서 파일 (.cer / .pvk) 및 DB 백업 파일 (.bak) 을 복사한다. 

마스터 키 생성 : 원본 서버와 동일한 패스워드를 입력할 필요는 없다. 
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PassWord1@3'
GO
CREATE CERTIFICATE gunnm_cert WITH SUBJECT = 'DB_Enc'
GO

원본 서버 인증서 파일을 통한 인증서 생성 및 DB 복원
CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

실행 메시지 : 
메시지 15232, 수준 16, 상태 1, 줄 2
이름이 'gunnm_cert'인 인증서가 이미 있거나 이 인증서가 데이터베이스에 이미 추가되었습니다.
테스트이므로 서버간 이동을 하지 않고, 동일 서버에서 진행되어 발생 된 오류로 추정

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;


CREATE CERTIFICATE gunnm_cert
  FROM FILE = 'H:\gunnm_cert.cer'
  WITH PRIVATE KEY ( 
    FILE = 'H:\gunnm_cert.pvk',
 DECRYPTION BY PASSWORD = 'gunnm_PW$1119'
  );

RESTORE DATABASE [gunnm]
  FROM DISK = 'H:\gunnm.bak'
  WITH MOVE 'gunnm' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm.mdf',
       MOVE 'gunnm_log' TO 'D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\gunnm_log.ldf';
 



4. TDE 모니터링 스크립트
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
         WHEN '0'  THEN  'No database encryption key present, no encryption'
         WHEN '1'  THEN  'Unencrypted'
         WHEN '2'  THEN  'Encryption in progress'
         WHEN '3'  THEN  'Encrypted'
         WHEN '4'  THEN  'Key change in progress'
         WHEN '5'  THEN  'Decryption in progress'
         WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
         ELSE 'No Status'
         END,
percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys 


USE master
GO
SELECT * FROM sys.certificates

-- encryption_state = 5 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3; 



5. 암호화 제거

DB 암호화 비활성화
USE gunnm
ALTER DATABASE gunnm SET ENCRYPTION OFF
GO

DB 암호화 키 제거
USE gunnm
GO
DROP DATABASE ENCRYPTION KEY  

마스터 키 및 인증서 제거
-- 마스터 키 제거
USE master;
drop master key;
GO

-- 등록 인증서 제거
USE master; 
DROP CERTIFICATE gunnm_cert;
GO  



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 2 : Windows Server 2016 + MS SQL 2016 / None AD  (1) 2020.07.07
AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
MS-SQL CPU 사용량이 높은 쿼리문 확인  (0) 2018.06.22
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25

MS-SQL CPU 사용량이 높은 쿼리문 확인

Database/MS-SQL2018. 6. 22. 14:37

MS-SQL DB 를 이용하는 서버의 CPU 사용량이 100% 가 출력되어 점검을 진행했다.


윈도우에서 성능 모니터에 카운터 추가를 해서 했는데 손이 너무 많이 간다. 


성능 모니터에서 카운터 추가

성능 개체 : Thread

카운터 : % Processor Time / ID Thread 

인스턴스 : sqlservr 리스트 전체 선택

CPU 사용량이 높은 SQL 스레드 ID 확인 : 4280

스레드 ID 로 spid 확인 

SELECT spid, kpid, dbid, cpu, memusage FROM sysprocesses WHERE kpid=4280

SPID : 240

spid 로 쿼리문 확인 

dbcc inputbuffer (240)


이렇게 했는데, 일을 두 번 할 필요는 없으니깐, SSMS 에서 한 번에 확인이 가능한 방법을 검색해서 정리해 보았다. 

SSMS 에 접속, tempdb 에서 새쿼리 실행 후 진행을 한다. 

그러면 tempdb 의 임시 테이블에 해당 테이블이 저장이 되는데, 다 확인하고 나면 맨 하단 drop 명령어로 테이블 제거를 하면 된다. 

use tempdb
CREATE TABLE #sp_who2 (SPID INT,Status VARCHAR(255),
      Login  VARCHAR(255),HostName  VARCHAR(255),
      BlkBy  VARCHAR(255),DBName  VARCHAR(255),
      Command VARCHAR(255),CPUTime INT,
      DiskIO INT,LastBatch VARCHAR(255),
      ProgramName VARCHAR(255),SPID2 INT,
      REQUESTID INT)
INSERT INTO #sp_who2 EXEC sp_who2
SELECT      *
FROM        #sp_who2
-- Add any filtering of the results here :
WHERE       Status = 'RUNNABLE'
-- Add any sorting of the results here :
ORDER BY    CPUTime ASC

CPU 사용량이 높은 spid 가 확인 됐으면, dbcc inputbuffer (SPID) 를 실행하여 쿼리문을 확인하면 된다. 

다 완료 되었으면 아래의 명령어를 통해 임시 테이블을 제거 한다. 

DROP TABLE #sp_who2


# 현재 실행 중인 쿼리문 확인하는 명령어

SELECT
   sqltext.TEXT,
   req.session_id,
   req.status,
   req.command,
   req.cpu_time,
   req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 


위에 생성 된 임시테이블의 select 구문과 현재 실행 중인 쿼리문을 조회하는 실행문을 동시에 실행해서 일치하는 spid 를 찾는 것도 좋은 방법일 것 같다. 



저작자표시 비영리 동일조건

'Database > MS-SQL' 카테고리의 다른 글

AlwaysOn 가용성 그룹 구축 # 1 : Windows Server 2016 + MS SQL 2016 / None AD  (6) 2020.07.03
SQL DB 암호화 / TDE(Transparent Data Encryption)  (0) 2018.12.07
현재 사용 중인 DB 중 제한 없는 DB 확인 쿼리  (0) 2016.07.25
DDL, DML, DCL  (0) 2016.07.25
X 행에 데이터가 커밋되지 않았습니다.  (0) 2016.07.25

티스토리툴바