MYSQL dump 주요 기능
데이터 백업 : mysqldump는 모든 데이터베이스 테이블의 내용을 SQL 명령으로 추출하여, 이를 통해 테이블 생성과 데이터 삽입을 할 수 있는 SQL 스크립트를 생성합니다.
데이터 복원 : 생성된 덤프 파일은 mysql 명령어 라인 유틸리티를 사용하여 MySQL 데이터베이스에 다시 로드할 수 있습니다. 이 과정을 통해 백업된 데이터베이스를 복원하거나 새로운 서버에 데이터를 이전할 수 있습니다.
데이터 마이그레이션 : mysqldump는 데이터베이스의 버전을 업그레이드하거나 다른 시스템으로 데이터베이스를 이전할 때 유용합니다. 데이터베이스 스키마와 데이터를 함께 백업하여 새 시스템에 쉽게 재설정할 수 있습니다.
백업 스케줄링 : 일반적으로 mysqldump 명령은 크론 작업(Cron job)과 함께 사용되어 주기적인 백업을 자동화합니다.
| 옵션 | 의미 | Default | 비고 |
|---|---|---|---|
| -A, --all-databases | 모든 db dump | ||
| --databases | 특정 데이터베이스만 덤프. 데이터베이스 이름을 공백으로 구분하여 지정 가능 | 추가 시 CREATE DATABASE 과 USE 구문이 덤프 파일의 첫 부분에 추가됨. | |
| --tables | 특정 테이블만 덤프. 이 옵션을 사용할 때는 데이터베이스 이름을 먼저 지정해야 함 | ||
| --no data | 데이터 없이 테이블 스키마(구조)만을 덤프 | X | |
| --add-drop-database | DROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제 | X | 기존 데이터가 삭제되니 주의 |
| --add-drop-table | DROP TABLE 구문 추가하여 table 생성전 기존 table 삭제 | O | drop table 을 제외하려면 --skip-add-drop-table 옵션 사용 |
| -l, --lock-tables | Lock all tables for read. | O | --skip-lock-tables 로 제외가능. 이 옵션보다는 아래의 --single-transaction 사용 추천 |
| --single-transaction | lock 을 걸지 않고도 dump 파일의 정합성 보장 | X | InnoDB 일때만 사용 가능 |
| --ignore-table | 제외할 테이블명을 주면 dump에서 제외 | X | 여러개의 테이블을 제외할 경우 테이블명마다 앞에 --ignore-table 옵션을 주어야 함. , 로 여러개의 테이블을 주어도 한 테이블만 처리 |
| -n, --no-create-db | CREATE DATABASE 구문 제외 | X | dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함 |
| -t, --no-create-info | CREATE TABLE 구문 제외 | X | dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용함 |
| -c, --complete-insert | INSERT INTO 구문 생성시 컬럼 이름도 포함 | X | dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용(Column count doesn't match value count 에러 발생시) |
| --triggers | trigger 도 dump | O | --skip-triggers 로 제외할 수 있음 |
| --routines | stored procedure 와 function 도 dump | X | MySQL 5 는 trigger는 기본적으로 덤프하나 function, procedure는 덤프하지 않음 |
| --where | 특정 테이블을 덤프할 때 조건을 지정. 예를 들어, 특정 날짜 이전의 레코드만 덤프하는 등의 조건을 설정 가능 | X |
- dump 파일 생성
dump 파일 생성 (routines 옵션 추가)
# /usr/local/mysql/bin/mysqldump --routines -u계정 -p비밀번호 --databases db명 > .경로/명칭.sql
dump 파일 생성 (정합성 보장)
# /usr/local/mysql/bin/mysqldump --routines --single-transaction -uroot -ppassword --databases mysql > /backup/$datetime/mysql.sql
dump 파일 생성 (압축파일로 생성)
# /usr/local/mysql/bin/mysqldump -uroot -ppassword --routines --single-transaction --databases DB | gzip -9 > /dump/DB.sql.gz - dump 복원
# mysql -u계정 -p비밀번호 db명 < 백업.sql- 압축 되어있는 테이블 압축 해제 없이 복원
# gunzip < table.sql.gz | mysql -uroot -p DB
압축해제 테이블 target DB
- 예시
procedure function trigger만 백업하기
# mysqldump -uroot -p --routines --no-create-info --no-data --no-create-db --events --skip-opt DB명 > 백업.sql
table1, 2만 백업하기
# mysqldump --single-transaction --databases db1 --tables table1 --tables table2 -u root -p > 백업.sql
shell에서 테이블1, 테이블2, 2004를 포함하는 모든 테이블 걸러내기
# mysql -uroot -ppassword "DB명" -e "show tables;" | grep -Ev "테이블명1|테이블명2|*2004";
sysop