20191023 [mysql] mysql data export/import to remote from local

개요.

- 로컬 윈도우 환경에 있는 mysql db data 를 덤프뜬 후, 데이터를 이관에 관한 내용.

- 이관할 DB 의 테이블은 해당링크에 있으며, mwb 파일로 존재. 워크벤치를 통한 포워드 엔지니어링 수행.

 

환경.

• local os & local mysql version
  • window 10
  • mysql version 8.0.13 for win64
• remote os & remote mysql version
  • centos 7
  • mysql version 5.7.17
  • 네이버 클라우드 이미지 타입 mysql 이용 링크

 

순서.

 

현재 로컬 피씨에서 mysql workbench 실행이후 server 탭에 두 가지를 확인한다.

1. Data Export
2. Data Import

Data Export 는 말 그대로 mysql 의 특정 데이터베이스의 테이블을 추출할 수 있다. (전체 테이블 또는 일부 테이블 가능) 해당 내용을 클릭하면 여러가지 옵션이 나오는데 내가 아는 것만 서술하려고 한다.

 

• 덤프 방식 3가지
  • Dump Structure and Data (데이터 및 테이블 구조 같이 추출)
  • Dump Data Only (데이터만 추출 : INSERT 구문이 있다.)
  • Dump Structure Only (테이블 구조만 추출)
• Export to Dump Project Folder
  - 해당 경로에 폴더를 만들고 그 안에 .sql 파일들을 생성한다. 체크박스 선택한 테이블에 관한 .sql 파일이 생성
• Export to Self-Cotained File
  - 해당 경로에 .sql 파일 하나만 생성된다.
  
  • Create Dump in a Single Transaction (self-contained file only)
    - 체크박스 설정/해제로 생성된 .sql 을 비교해보았지만 차이점이 발견되지 않는다. 
  • Include Create Schema
    
    

 Include Create Schema 를 체크박스 한 경우 나타남. 해당 데이터베이스 미존재시 생성 및 use 명령어가 발동된다.

CREATE DATABASE  IF NOT EXISTS `pasudo123_test` 
/*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `pasudo123_test`;

 

원격환경에서는 따로 mysql workbench 를 쓸 수 없기 때문에 터미널 상에서 mysql import 명령어를 입력할 수 밖에 없었다. 하지만 그 이전에 몇가지 확인해야할 것들이 있다.

 

 

local mysql 과 remote mysql character set 맞추기

요즘 흔히 이모지까지 표현하다보니 가변 3바이트인 utf8 보단 가변 4바이트인 utf8mb4 를 선호된다. 이모지 표현은 단순 utf8 에선 되지 않는다. 여기다 테이블 정렬 collation 도 맞추어주어야 한다.

 

centos 7 기준으로 mysql 설정파일은 /etc/my.cnf 에 존재하며 아래의 내용과 같이 맞추는 것이 최적이다.

[mysqld]
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
skip-character-set-client-handshake

위와 같이 변경하고 mysql 을 재시작한다. 이후 mysql 접속 뒤에 status 명령어를 입력했을때 캐릭터셋이 제대로 변경되었는지 확인해야한다.

 

 

외래키 설정 해제.

export 하기 위한 디비와 테이블의 캐릭터 셋이 옮길 디비와 테이블의 캐릭터 셋과 맞지 않기 때문에 맞추어주었다. 하지만 그 이전에 연관관계로 매핑되어있는 테이블들의 연관관계를 끊어줄 필요가 있었다. 그래서 아래와 같은 절차를 수행하였다.

 

테이블에서 컬럼의 캐릭터 셋을 변경하는 명령어는 두가지가 존재한다. 처음에 암만 워크벤치로 해도 변경이 되지 않았는데 기존 데이터 캐릭터 셋과 새롭게 들어오는 캐릭터 셋을 바꾸는 명령어가 달랐다.

SET FOREIGN_KEY_CHECKS=0;

// 신규 데이터
alter table {테이블 명} character set utf8mb4 collate utf8mb4_unicode_ci;

// 기존 데이터 : 해당 것으로 수행하기.
alter table {테이블 명} convert to character set utf8mb4 collate utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;

 

 

mysql data import

scp 프로토콜로 우선 데이터를 mysql 서버쪽으로 이동한 뒤, 해당 내용은 간단하게 아래와 같은 명령어 입력을 통해 수행하였다.

// 두 번에 걸쳐 수행
// (1) 테이블 구조 및 관계.
// (2) 데이터.
mysql -u root -p {데이터 이관 테이블 명} < {파일 명}.sql

위의 명령을 작성하면 root 계정에 대한 패스워드를 입력하였고, 이후에 제대로 이관되어있는 것을 확인할 수 있었다.

 

+)

(1) mysql 에서 .sql 파일 읽어들이기

mysql> source {mysql .sql 파일 절대경로 입력}

ex)
mysql> source /etc/data/mysql/insert.sql

• 상대경로로 입력할 수 있지만(mysql 실행한 곳에 sql 파일이 있는 경우), 안전하게 절대경로를 통해서 실행하도록 하자.

 

참고링크

https://www.lesstif.com/pages/viewpage.action?pageId=20775198

https://kwonnam.pe.kr/wiki/database/mysql/charset