따봉 구걸남's 블로그

mysql 에서 table 을 복사하는 몇가지 방법 및 특징을 알아보자

1. CTAS 

 CREATE TABLE NEW_TAB  AS  SELECT * FROM OLD_TAB ;

 - 테이블 구조와 모든 데이터를 복사

 - 인덱스, 기본 키, 외래 키, auto_increment 속성 등은 복사되지 않음

 - charset, collate 는 default 속성을 따라감

2. LIKE 

 CREATE TABLE NEW_TAB LIKE OLD_TAB ;

- 원본 테이블의 구조(컬럼, 인덱스, 제약 조건 등)를 그대로 복사

- FK 제약조건은 복사가 되지 않는다. 따로 추가해야 함

SELECT CONCAT( 'ALTER TABLE `B`.`', TABLE_NAME, '` ADD CONSTRAINT `', CONSTRAINT_NAME, '` ', 'FOREIGN KEY (`', COLUMN_NAME, '`) ', 'REFERENCES `B`.`', REFERENCED_TABLE_NAME, '` (`', REFERENCED_COLUMN_NAME, '`);' ) FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'A' AND REFERENCED_TABLE_SCHEMA IS NOT NULL;

3. SHOW CREATE TABLE 

 SHOW CREATE TABLE OLD_TAB; => DDL 추출해서 생성

4. 덤프로 복사

테이블이 여러개이면 dump& load 가 최고. 테이블 구조, 데이터, 인덱스, 외래 키 제약 조건이 복사된다.

mysqldump -u root -p A > A_schema_backup.sql

CREATE DATABASE B;

mysql -u root -p B < A_schema_backup.sql

Remote Dictionary Server

1. cache
cache 구현 방법 : memcached, redis, local memory cache 

 cache 전략
 - 읽기 전략
1) look aside : cache 에 없으면 db조회 
  장) 캐시 문제생기면 db 로 위임
  단) 캐시,db간 정합성 유지 어려움 
 
2) read through : 항상 캐시를 통해 
  장) 캐시,db간 정합성 유지 
  단) 캐시 죽으면 장애 
 
 - 쓰기 전략
1) write around : 캐시 우회해서 db에 바로 write  
   장) 성능 이점
   단) 캐시, db간 정합성 유지 어려움
2) write back : 캐시에 미리 write, 나중에 db에 모아서 write  
   장) 쓰기 비용 줄임
   단) 캐시 data loss 가능성

3) writh through : 항상 캐시 먼저 write , 그리고 db에 write  
장) 정합성 유지
단) 항상 두번 쓰는 성능 불리 

2. redis 

redis 용도 : 캐싱, 임시작업 큐, 실시간 채팅, 메시지 브로커 

 특징 
 1) performance
    : 메모리에 저장, get/set 초당 10만 tps
 2) single thread
   : 한번에 하나의 명령만 처리. race condition 발생 X
 3) data structure
   : 다양한 자료구조 제공 
 4) persistence 
   : 메모리 저장 데이터를 디스크 영속화. 복구 가능 
  - RDB (Redis Database Backup) 
   => 특정 간격 메모리 데이터 스냅샷 
  장) 압축,저장 -> 크기 작고 로딩/복구 빨라
  단) 백업 중 다운 되면 최신 데이터 유실 가능성 
 - AOF (Append Only File) 
 => dml 실행시 log파일에 기록 
  장) 실시간 데이터 백업, 데이터 손실 거의 없음
  단) 명령실행 모두 기록 -> 파일크기, 복원시 긴 시간
 

MySQL에서 테이블 이름의 대소문자 구분은 lower_case_table_names 시스템 변수로 제어된다.

이 설정은 운영 체제에 따라 기본값이 다르며, 주의해야 할 점들이 있다. 

1. lower_case_table_names 옵션
 - 0: 테이블 이름을 대소문자 구분 (기본값: Unix/Linux)
 - 1: 테이블 이름을 소문자로 저장하고 비교 시 대소문자 구분 안 함 (기본값: Windows, macOS)
 - 2: 테이블 이름을 지정한 대로 저장하지만 비교 시 소문자로 변환 (macOS에서 권장)

2. 운영 체제별 기본 설정
 Unix/Linux: 0 (대소문자 구분)
 Windows: 1 (대소문자 구분 안 함)
 macOS: 2 (저장은 지정한 대로, 비교는 소문자로)

3. 설정 방법
my.cnf 또는 my.ini 파일의 [mysqld] 섹션에 추가:

[mysqld]
lower_case_table_names=1

4. 주의점
 MySQL 8.0부터는 데이터 디렉토리 초기화 후 이 값을 변경할 수 없다
일관성 유지: 모든 환경에서 동일한 설정을 사용하는 것이 좋습니다
복제 고려: 복제 환경에서는 모든 서버가 동일한 설정을 사용해야 합니다
이식성: 대소문자를 구분하지 않는 설정(1)이 가장 이식성이 좋다
기존 데이터: 설정 변경 시 기존 테이블 이름과 충돌할 수 있으므로 주의가 필요
성능: 대소문자를 구분하지 않는 설정은 약간의 성능 저하를 일으킬 수 있다
파일 시스템 고려: 대소문자를 구분하는 파일 시스템(unix/linux)에서는 0 또는 2 설정을 권장

안전하고 일관된 운영을 위해서는 모든 환경에서 lower_case_table_names=1을 사용하는 것이 좋다.
이미 운영 중인 시스템이라면 변경 시 주의가 필요하며, 새로운 시스템 구축 시 이 설정을 고려해야 한다.

mysql 터미널에서 데이터를 dump & load 하는 방법 알아보자 

아래의 환경에서 테이블을 1개 dump & load 한다고 가정하자 
source db : dumpdb
source table : dumpt
source user : mydba

1. mysqldump

 # dump 
 
    mysqldump -u유저명 -p  -h 소스디비IP -P포트번호 디비명 테이블명 > 타겟파일명.sql
ex) mysqldump -umydba -p  -h 소스디비IP -P포트번호 --no-create-db --no-create-info --set-gtid-purged-OFF --single-transaction dumpdb dumpt > dumpt_bak.sql
덤프 범위 지정 옵션
--databases: 덤프할 데이터베이스들을 지정
--all-databases: 모든 데이터베이스를 덤프
--ignore-table: 특정 테이블을 덤프에서 제외

덤프 내용 제어 옵션
--no-create-info: CREATE TABLE 문을 포함하지 않음 (데이터만 덤프)
--no-data: 테이블 구조만 덤프하고 데이터는 포함하지 않음
--add-drop-database: 덤프 파일에 DROP DATABASE 문 포함
--add-drop-table: 덤프 파일에 DROP TABLE 문 포함

성능 및 일관성 관련 옵션
--single-transaction: InnoDB 테이블에 대해 일관된 덤프를 생성 (락 없이)
--quick (-q): 대용량 테이블 덤프 시 메모리 사용을 줄임
--opt: 여러 유용한 옵션들을 한 번에 활성화 (기본값)

출력 형식 관련 옵션
--extended-insert (-e): 다중 행 INSERT 문 사용 (로드 속도 향상)
--complete-insert: INSERT 문에 컬럼명 포함

특수 목적 옵션
--master-data: 복제 설정을 위한 CHANGE MASTER TO 문 포함
--where: 특정 조건에 맞는 데이터만 덤프
--set-gtid-purged=OFF 옵션의 의미와 용도
GTID 정보 제외: 이 옵션을 사용하면 덤프 파일에 GTID 정보를 포함시키지 않습니다
데이터 이동 시 유용: 서로 다른 GTID 환경 간에 데이터를 이동할 때 유용합니다
복제 토폴로지 유지: 기존의 복제 토폴로지를 유지하면서 테이블을 복사하거나 복구할 때 사용합니다
바이너리 로깅 영향: 이 옵션을 사용하면 덤프 파일을 다시 로드할 때 바이너리 로깅을 비활성화하는 명령문(SET @@SESSION.SQL_LOG_BIN = 0;)이 추가되지 않습니다

 # load 
 
1-1) 타겟디비에 접속 후 load

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명
source 타겟파일명.sql

1-2) 디비접속과 load 를 한줄로 실행 

mysql -u유저명 -p  -h 타겟디비IP -P포트번호 타겟디비명 테이블명 < 타겟파일명.sql

2. UTIL (mysqlsh) 사용

2-1) mysqlsh 에 붙어서 작업

 # dump

mysqlsh mysql://유저명@소스디비IP:포트
JS > util.dumpTables(schema, tables, outputUrl[, options])
 ex) util.dumpTables (" dumpdb", ["dumpt"], "/data01/work/dumpdir", {dataOnly: true, threads: 4})

* 한줄로 실행하기
mysqlsh -h 디비ip -P포트 -u 유저명 -p --log-file=/경로/파일명.log -e "util.dumpTable(...상동)"

 - 두번째 테이블 항목에 여러개를 추가하는 것이 가능
 - 멀티스레드로 dump 가능. default 는 1이다.

 # load

mysqlsh mysql://유저명@타겟디비IP:포트
JS > util.loadDump(url[, options]) 
 ex) util.loadDump('/data01/work/dumpdir', {schema:"dumpdb", threads: 4, ignoreExistingObjects: true})

 - load 시 thread 는 디폴트가 4로 생략해도 무방하다. 
 - 타겟에 이미 테이블이 존재한다면 ignoreExistingObjects 를 사용한다. dump시 dataOnly 를 사용하면 안써도됨

3. 정리..

mysqldump 는 결과파일을 사람이 편집도 가능한 sql문을 생성하는 로지컬 백업이다. 
mysqlsh 과는 호환이 되지 않는다. 

mysql쉘의 장점 : 

 - 멀티스레드로 병렬실행이 가능 
 - 파일압축
 - 진행률 확인가능 
 
백업을 지원하는 또 다른 유틸인 util.exportTable 의 경우는 CSV, TSV 등 다양한 텍스트 형식으로 백업이 가능하지만 
단일 테이블 작업만 가능하고, util.importTable 로 로드할수 있다. 


 출처 : https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html 

AWS 프리티어로 postgresql RDS 인스턴스 생성하기 

1. 엔진

엔진버전은 암거나 해도 되는데 나는 15.6 -R3 을 선택했다.

2. 설정 

DB 인스턴스 식별자는 pgdb , 마스터 계정은 pgdba 로 설정했다.

3. 연결

 EC2 로 붙지 않고 바로 접속할거라 퍼블릭액세스 '예' 를 선택한다. 

 퍼블릭이면 누구나 들어올수 있기에 well-known port 가 아닌 나만의 포트로 수정해준다. 

보안그룹의 인바운드 규칙도 편집해준다. 

4. 원격접속하기 

  엔드포인트를 확인하고 디비버에서 접속해보자 

 잘된다. 

AWS RDS 로 Postgresql 생성하고 로컬에서 psql 로 접속하는 테스트 해보자 

1. Postgresql 설치

  AWS RDS 로 postgressql 인스턴스 생성하기 

2. client 설치

아래 링크에서 postgresql 을 다운받자

https://www.postgresql.org/download/

 DB서버를 로컬에 설치하진 않을거라 제외하고 나머지들만 선택해줬다. 

다운받고 설치가 끝나면 (재부팅이 요구된다. 조심하자) 환경변수를 편집해서 psql 을 사용할수 있게 수정하자.

3. psql 로 DB 원격 접속 

cmd 창을 열어 psql 명령어로 접속한다. 

psql -U 유저명 -d 디비명 -p 포트넘버 -h 호스트명 

4. psql 사용법 

## 데이터베이스 관련

- `\l` 또는 `\list`: 모든 데이터베이스 목록 표시
- `\c dbname` 또는 `\connect dbname`: 특정 데이터베이스로 연결

## 테이블 관련

- `\dt`: 현재 데이터베이스의 모든 테이블 목록 표시
- `\d tablename`: 특정 테이블의 구조 확인

## 사용자 관련

- `\du`: 모든 사용자(롤) 목록 표시

## 기타 유용한 명령어

- `\?`: psql 명령어 도움말 표시
- `\q`: psql 종료
- `\g`: 이전에 실행한 쿼리 다시 실행
- `\s`: 명령어 실행 기록 표시

## SQL 실행

- 일반적인 SQL 문은 그대로 입력하고 세미콜론(;)으로 끝내면 실행됩니다.

## 파일 실행

- `\i filename`: 파일에서 SQL 명령 실행

이 명령어들을 사용하여 PostgreSQL 데이터베이스를 효과적으로 탐색하고 관리할 수 있습니다. 필요에 따라 `\?` 명령어를 통해 더 많은 psql 메타 명령어를 확인할 수 있습니다.

Blue 와 Green 의 테이블 구조가 다른 상태에서 복제가 정상적으로 진행이 되는지 테스트 해보자 

1. mysql rds 생성 

 mysql rds 인스턴스 생성은 이전에 작성한 포스팅을 참조하자 

https://riorio.tistory.com/606

생성이 완료 되면 테스트 DB 를 생성하고 테스트 테이블을 하나 생성하자 

CREATE DATABASE TEST;

USE TEST

2. 블루그린 - 그린 인스턴스 추가 

그린 인스턴스를 생성한다. 그린 인스턴스 전용 파라미터 그룹을 새로 만들어서 설정해준다. 

그린 전용 파라미터 그룹에는 아래 두가지 파라미터가 수정이 되었다. 

read_only=0 
slave_type_conversions=ALL_NON_LOSSY

버튼을 누르면 그린 인스턴스가 생성작업이 시작된다.

생성이 완료되면 엔드포인트 정보로 접속하자. 

3. 테스트 

 테이블 구조를 다르게 유지하기 위해 그린에만 DDL 을 실행한다.

 insert 는 blue 에서 실행하고 green 에서 복제가 되는지 조회해보자

-- [G] 1. char set
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- [B] 1. char set
INSERT INTO users (username, email, age, address) VALUES ('fastlookup', 'fast@example.com','1','L.A santa monica beach');


-- [G] 2. col order
ALTER TABLE users MODIFY COLUMN email VARCHAR(100) AFTER age;
-- [B] 2. col order
INSERT INTO users (username, email, age, address) VALUES ('émilie', 'émilie@example.com','24','jeju-island');


-- [G] 3. col type varchar -> int 
ALTER TABLE users MODIFY COLUMN age int;
-- [B] 3. col type varchar -> int
INSERT INTO users (username, email, age) VALUES ('jane_smith', 'jane@example.com', 25);


-- [G] 3. col type  text -> varchar
ALTER TABLE users MODIFY COLUMN address VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- [B] 4. col type text -> varchar
INSERT INTO users (username, email, age, address) VALUES ('no_index', 'no_index@example.com','98','new-york,new-york');

블루-그린 테이블 구조가 달라도 데이터는 정상적으로 복제가 되었다. 

작업이 완료되었고, 복제가 완료되었으면 staging 을 prod 로 바꾸는 switch over 를 하자

전환이 완료되었다. 그린은 신규블루로 바뀌었고 엔드포인트도 기존 블루의 엔드포인트로 바뀌었다.

기존블루의 인스턴스 이름에는 "old" 가 붙게 된다. 

 사용자 입장에서는 잠깐의 순단이 발생하고 운영반영이 되었다. 

* 스위치오버 전후 유의점 

- 전환 전 그린 파라미터의 원복 : read_only,  slave_type_conversions ( 재부팅 필요) 

- 그린의 데이터 검증, sql 플랜 확인 

- 전환 작업은 DB연결 수가 적은 시점에 해야 원활하게 작업가능

    (리소스사용의 최소화, 빠른 전환, 데이터 불일치, 충돌 리스크 감소) 

mysql 복제 상태 show replication status\G 를 주기적으로 반복 실행하는 쉘 만들어보자 

원격서버의 상태를 체크한다고 하면 

mysql -h [호스트명] -P[포트번호] -u [사용자명] -p[패스워드] -e "SHOW SLAVE STATUS\G"

============================================================== 

#!/bin/bash

MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="your_username"
MYSQL_PASS="your_password"

# 중요한 상태 항목들
ITEMS_TO_SHOW="Source_Log_File|Read_Source_Log_Pos|Exec_Source_Log_Pos|Replica_IO_Running|Replica_SQL_Running|Seconds_Behind_Source|Last_IO_Error|Last_SQL_Error"

while true
do
  echo "==== $(date) ===="
  mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep -E "$ITEMS_TO_SHOW"
  
  # 복제 지연 확인
  SECONDS_BEHIND=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASS" -e "SHOW REPLICA STATUS\G" | grep "Seconds_Behind_Source" | awk '{print $2}')
  if [ "$SECONDS_BEHIND" != "NULL" ] && [ "$SECONDS_BEHIND" -gt 0 ]; then
    echo "경고: 복제 지연이 $SECONDS_BEHIND 초 발생했습니다."
  fi
  
  echo ""
  sleep 10
done

==============================================================

===== window 버전 =============================

set HOST=[호스트명]
set PORT=[포트번호]
set USER=[사용자명]
set PASS=[비밀번호]

:loop
cls
echo Checking replication status...
mysql -h %HOST% -P %PORT% -u %USER% -p%PASS% -e "SHOW SLAVE STATUS\G" | findstr /C:"Slave_IO_Running" /C:"Slave_SQL_Running" /C:"Seconds_Behind_Master"
timeout /t 5
goto loop

AWS DMS 로 데이터를 옮기는 테스트를 해보자

1. Replication Instance 생성

 - subnet group 생성

- 복제 인스턴스 생성 

RI 가 속할 vpc 를 선택하고 위에 만들어둔 서브넷그룹을 선택한다. 

2. 데이터 마이그레이션 태스크 생성

 - 엔드포인트 생성

소스, 타겟 엔드포인트를 생성하고 연결테스트까지 해본다 

대상RDS 의 보안그룹의 인바운드 규칙에 DMS RI 의 주소를 추가해준다. 

연결테스트가 성공이 되면 테스크를 생성하자 

 - 태스크 생성

태스크가 생성되면 자동으로 데이터가 복제된다. 

## 테스트 스크립트 

create database dmstest;

use dmstest;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    hire_date DATE,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);
  
  INSERT INTO employees (first_name, last_name, email, hire_date, department, salary) VALUES
('John', 'Doe', 'john.doe@example.com', '2020-01-15', 'IT', 75000.00),
('Jane', 'Smith', 'jane.smith@example.com', '2019-05-22', 'HR', 65000.00),
('Michael', 'Johnson', 'michael.johnson@example.com', '2021-03-10', 'Marketing', 70000.00),
('Emily', 'Brown', 'emily.brown@example.com', '2018-11-30', 'Finance', 80000.00),
('David', 'Lee', 'david.lee@example.com', '2022-02-01', 'IT', 72000.00),
('Sarah', 'Wilson', 'sarah.wilson@example.com', '2020-09-18', 'Sales', 68000.00),
('Robert', 'Taylor', 'robert.taylor@example.com', '2019-07-05', 'Marketing', 71000.00),
('Lisa', 'Anderson', 'lisa.anderson@example.com', '2021-11-12', 'HR', 67000.00),
('William', 'Martinez', 'williahttp://m.martinez@example.com', '2018-04-20', 'Finance', 82000.00),
('Jennifer', 'Garcia', 'jennifer.garcia@example.com', '2022-01-03', 'Sales', 69000.00);

select * from employees;

정상적으로 데이터가 복제되었다.

GIN(Generalized Inverted Index) 인덱스란 ? 
PostgreSQL에서 복합적인 데이터를 효율적으로 검색하기 위해 사용되는 특수한 인덱스 유형
주요 특징은 아래와 같다. 

1. 복합 데이터 처리: 배열, JSON, hstore, tsvector 등 복합적인 데이터 타입에 적합
2. 키워드 기반 검색: 문서나 텍스트에서 특정 단어를 빠르게 찾을 수 있다.
3. 역인덱스 구조: 키워드나 토큰을 기반으로 데이터를 인덱싱
4. 전문 검색(Full-text search) 지원: 텍스트 검색 속도를 크게 향상
5. 다양한 연산자 지원: LIKE, ILIKE, ~, ~* 등 다양한 검색 연산자를 지원

GIN 인덱스는 특히 대량의 텍스트 데이터를 처리할 때 B-tree 인덱스보다 훨씬 효과적이며,
 검색 성능을 크게 향상시킬 수 있다.
  다만, 인덱스 생성과 업데이트에는 상대적으로 많은 시간이 소요될 수 있습니다

## 복합 데이터 처리

테스트 테이블을 만들고 GIN 인덱스 사용 전후의 플랜을 비교해보자 

-- Create a table with a JSONB column
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    details JSONB
);

-- Insert sample data
INSERT INTO products (name, details) VALUES
    ('Laptop', '{"brand": "TechCo", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "tags": ["electronics", "computer"]}'),
    ('Smartphone', '{"brand": "Gadgetron", "specs": {"ram": "8GB", "storage": "256GB"}, "tags": ["electronics", "mobile"]}'),
    ('Headphones', '{"brand": "AudioPro", "specs": {"type": "over-ear", "wireless": true}, "tags": ["audio", "accessories"]}');


-- Query using the GIN index to find products with specific attributes
EXPLAIN ANALYZE
SELECT name, details
FROM products
WHERE details @> '{"brand": "TechCo"}';

-- Query to find products with specific tags
EXPLAIN ANALYZE
SELECT name, details
FROM products
WHERE details->'tags' @> '"electronics"'::jsonb;

-- Create a GIN index on the JSONB column
CREATE INDEX idx_product_details ON products USING GIN (details);

## 전문검색 처리 

-- 문서 테이블 생성 
CREATE TABLE  documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    content_tsv tsvector
);

-- 함수 생성: 랜덤 문장 생성
CREATE FUNCTION random_sentence() RETURNS TEXT AS $$
DECLARE
    words TEXT[] := ARRAY['PostgreSQL', 'database', 'index', 'query', 'performance', 'GIN', 'B-tree', 'full-text', 'search', 'optimization', 'SQL', 'data', 'management', 'system', 'relational', 'ACID', 'transaction', 'concurrency', 'scalability', 'replication'];
    sentence TEXT := '';
    word_count INT;
BEGIN
    word_count := 5 + random() * 10; -- 5에서 15 단어 사이의 문장 생성
    FOR i IN 1..word_count LOOP
        sentence := sentence || ' ' || words[1 + random() * (array_length(words, 1) - 1)];
    END LOOP;
    RETURN trim(sentence);
END;
$$
 LANGUAGE plpgsql;

-- 10,000개의 샘플 문서 생성
INSERT INTO documents (content, content_tsv)
SELECT 
    random_sentence(),
    to_tsvector('english', random_sentence())
FROM generate_series(1, 10000);

-- GIN 인덱스 생성 
CREATE INDEX  idx_gin_content ON documents USING gin(content_tsv);

-- 통계 업데이트
ANALYZE documents;

-- 검색 쿼리 실행 및 실행 계획 확인
EXPLAIN ANALYZE
SELECT id, content
FROM documents
WHERE content_tsv @@ to_tsquery('english', 'postgresql & index');