MySQL 연결

#핵심 요약

실습 미니 MySQL 마이그레이터는 Oracle판 미니 마이그레이터와 화면, 흐름이 같고 소스만 MySQL임

MySQL 접속에는 PyMySQL을 씀
순수 Python이라 따로 설치할 클라이언트가 없음

분석은 MySQL information_schema를 읽음
InnoDB의 tables.table_rows는 통계로 짐작한 값이라, 화면의 행 수는 테이블마다 count(*)로 다시 셈

MySQL 계정은 사용자@호스트 단위라서 PC에서 접속할 수 있는 호스트로 만든 계정이어야 함

#개념과 동작 원리

3~6단계(변환, 이행, 검증, 점검)의 흐름은 Oracle판과 같으며, MySQL에서만 달라지는 타입, 자동 증가, 인덱스, 권한, 쿼리 문법을 아래 절에서 따로 정리함

#information_schema에서 읽는 것

실습 분석 결과: cust 100,000행, orders 1,000,000행, 크기 102MB
자동 4(테이블 2, 뷰 1, 자동 증가 1), 검토 0, 수동 1(프로시저)로 자동 이행 가능 80%
뷰는 백틱을 없애고 IFNULL을 COALESCE로 바꿔 자동 변환하고, orders.order_id의 auto_increment는 다음 값 1,000,001부터 identity로 옮기며, datetime은 timestamp(0)으로 바꿈

Oracle 데이터 사전을 읽는 방식은 메타데이터와 스키마 분석에 있음

#필수 명령어 또는 SQL

#연결 전 MySQL 상태 확인

systemctl is-active mysqld
mysqladmin -h 127.0.0.1 -u <USERNAME> -p ping

결과 해석: active, mysqld is alive면 정상임
inactive면 systemctl start mysqld로 띄움
실습에서는 비밀번호를 -p 뒤에 붙여 썼고 그래서 명령줄 비밀번호 경고가 나왔음
-p만 지정하면 비밀번호를 입력받음

#라이브러리 설치와 실행

cd C:\work\mini_mysql_migrator
pip install -r requirements.txt

결과 해석: streamlit, plotly, pandas, pymysql, psycopg를 설치함
새로 설치한 Anaconda에서는 psycopg와 pymysql만 새로 설치되고, 실습 확인값은 PyMySQL 1.2.3, psycopg 3.3.6임

set SRC_HOST=<HOST>
set SRC_PORT=3306
set SRC_USER=<USERNAME>
set SRC_PASSWORD=<PASSWORD>
set TGT_HOST=<HOST>
set TGT_PORT=5432
set TGT_DB=<DB_NAME>
set TGT_USER=<USERNAME>
set TGT_PASSWORD=<PASSWORD>
python -m streamlit run app.py --server.port 8503

결과 해석: 브라우저에서 http://localhost:8503을 엶
미니 PG 게이지 8501, Oracle판 마이그레이터 8502와 겹치지 않게 8503을 쓰며 세 프로그램을 동시에 띄울 수 있음
Anaconda를 찾아 켜는 방법은 Oracle과 PostgreSQL 연결에서 다룸
메모장으로 고칠 때는 CRLF 줄바꿈으로 저장해야 한글 줄이 깨지지 않음

#접속 가능한 호스트 확인

select user, host from mysql.user;

결과 해석: 실습 계정은 어디서나 접속할 수 있는 호스트(%)로 만들어져 있음
다른 계정을 쓸 때 Access denied for user가 나면 이 조회로 호스트를 확인함

#3단계 타입 변환과 DDL (MySQL에서 달라지는 것)

create table mig_sql_pg.orders (
    order_id  bigint generated by default as identity not null,
    cust_id   bigint,
    order_dt  timestamp(0),
    amount    numeric(12,2),
    status    varchar(10),
    memo      text
);
-- 데이터 적재 후
alter table mig_sql_pg.orders add constraint orders_pkey primary key (order_id);
create index orders_cust_fk on mig_sql_pg.orders (cust_id);
alter table mig_sql_pg.orders add constraint orders_cust_fk foreign key (cust_id) references mig_sql_pg.cust (cust_id);
select setval(pg_get_serial_sequence('mig_sql_pg.orders', 'order_id'),
              greatest(coalesce((select max(order_id) from mig_sql_pg.orders), 0) + 1, 1000001), false);

결과 해석:

대상 스키마 기본값은 소스DB_pg(실습은 mig_sql_pg)이며, 제약 조건, 인덱스는 Oracle판처럼 데이터를 다 넣은 뒤 만듦

InnoDB는 FK 컬럼에 인덱스를 자동으로 만들지만 PostgreSQL은 만들지 않아, MySQL의 FK 인덱스(orders_cust_fk)를 같은 이름으로 FK보다 먼저 만듦
빠뜨리면 orders.cust_id로 찾는 쿼리가 느려짐

뷰는 백틱과 mig_sql. 접두어를 지우고 IFNULL → COALESCE로 바꾸며, 이름만 쓴 테이블이 대상 스키마를 가리키도록 set search_path to mig_sql_pg, public을 먼저 둠

MySQL 8의 information_schema.columns.column_default는 문자 기본값을 따옴표 없이(N) 보여 주므로 문자 타입이면 'N'처럼 따옴표를 붙임

함수 이름을 정규식으로 바꾸면 따옴표 안의 문자열('ifnull(x)')까지 바뀌므로, SQL을 코드와 문자열 조각으로 나눠 코드 쪽만 바꿈

#4단계 데이터 이행 (SSCursor와 COPY)

MySQL은 PyMySQL의 SSCursor(서버에서 흘려 읽기)로 읽어 100만 행을 한꺼번에 메모리에 올리지 않고, PostgreSQL에는 5000행 배치의 COPY로 넣음

실습 기록: 1,100,000행을 8.7초(초당 127,013행), cust 0.7초, orders 6.9초, 적재 후 DDL 7/7 성공

COPY는 order_id 값을 직접 넣어 identity 시퀀스가 1에 머물므로, setval을 하지 않으면 이행 뒤 새 주문에서 duplicate key value violates unique constraint "orders_pkey"가 남

MySQL TIME은 -838~838시간의 기간까지 담지만 PostgreSQL time은 하루 안의 시각만 담아, 기간 값을 담은 컬럼은 interval로 바꿈

#5단계 데이터 검증

select count(*) from cust;
select count(*) from orders;
select count(*), sum(total_amt) from v_cust_sales;
select * from v_cust_sales order by cust_id limit 3;
select table_name, auto_increment from information_schema.tables
 where table_schema = 'mig_sql' and table_type = 'BASE TABLE';
show index from orders;

\d mig_sql_pg.orders
select count(*) from mig_sql_pg.orders;
select count(*), sum(total_amt) from mig_sql_pg.v_cust_sales;
select * from mig_sql_pg.v_cust_sales order by cust_id limit 3;
select last_value, is_called from mig_sql_pg.orders_order_id_seq;

cd /tmp
export MYSQL_PWD='<PASSWORD>'
mysql -h 127.0.0.1 -u <USERNAME> mig_sql -t -vvv < my_src_check.sql
psql -h localhost -U <USERNAME> <TARGET_DB_NAME> -e -f my_pg_check.sql

결과 해석:

실습 기록: 양쪽 모두 cust 100,000, orders 1,000,000, 뷰 합계 250444357921.73과 앞 3행이 같고, MySQL AUTO_INCREMENT 1000001과 PostgreSQL last_value 1000001, is_called f가 같음(다음 번호가 1000001이라는 뜻)

화면 자동 검증은 건수, 숫자 합계, 날짜 최소최대, 문자 길이 합, TEXT 길이 합 14개 검사가 모두 일치함

MySQL length()는 바이트 수, PostgreSQL length()는 글자 수라 문자 길이는 양쪽 모두 char_length()로 셈

명령줄의 -p비밀번호는 ps에 보일 수 있어 MYSQL_PWD 환경변수나 대화형 입력을 씀

-t는 표 모양, -vvv는 실행한 SQL과 걸린 시간을 함께 찍음

#6단계 AS-IS/TO-BE 점검

실습 기록: 첫 점검은 프로시저 p_upgrade_grade 누락과 그 EXECUTE 권한 누락으로 조치 2건, Oracle판에서 변환해 둔 mig.p_upgrade_grade를 mig_sql_pg로 가져온 뒤 오브젝트 9/9, 데이터 2/2, 프로시저 1/1, 업무 쿼리 7/7, 권한 15/15로 통과

가져온 프로시저는 본문의 mig. 참조도 mig_sql_pg.로 바꿔야 다른 스키마 테이블을 고치지 않음

가져온 판의 인자 타입은 MySQL DECIMAL(14,2)에서 bigint로 바뀌어 있어, 소수점 금액을 넘길 일이 있으면 numeric(14,2)로 다시 만듦

MySQL은 기본 DB를 고르지 않으면 이름만 쓴 쿼리가 No database selected로 실패해 select_db로 지정함

MySQL은 DB 단위로 ALTER, DROP을 주지만 PostgreSQL에서는 객체 소유자만 가지므로 DDL 권한은 소유권으로 대응하고 권한 대조에서 뺌

#사용 조건과 주의사항

auto_increment 다음 값: 실습에서는 예제3에서 INSERT 후 ROLLBACK으로 번호를 한 번 썼으므로 MySQL의 실제 다음 값은 1,000,002이고, information_schema가 값을 최대 24시간 캐시해 1,000,001로 보일 수 있으며 둘 다 정상이라고 적었음
도구가 캐시된 값으로 identity를 시작할 때 생기는 영향은 기재 없음

MySQL은 PK 이름이 모두 PRIMARY임
PostgreSQL은 스키마 안에서 이름이 겹치면 안 되므로 실습 도구는 <테이블>_pkey로 바꿔 만듦

MySQL에서 PostgreSQL로 옮기는 SQL 방식은 MySQL에서 PostgreSQL로 이행에 있음