mysqldump 사용법(db backup 및 load 하기)
- 정광섭
Owned by 정광섭
간단한 mysql 백업 솔루션인 mysqldump 주요 옵션과 사용법 정리
용법
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
주요 옵션
| 옵션 | 의미 | Default | 비고 |
|---|---|---|---|
| -A, --all-databases | 모든 db dump | ||
--databases | dump 할 db 명 기술하면 해당 db 만 dump | --database 옵션이 주어질 경우 CREATE DATABASE 과 USE 구문이 덤프 파일의 첫 부분에 추가됨. | |
| --add-drop-database | DROP DATABASE 구문 추가하여 create database 전에 기존 database 삭제 |  | 기존 데이타가 삭제되니 주의 |
| --add-drop-table | DROP TABLE 구문 추가하여 table 생성전 기존 table 삭제 |  | drop table 을 제외하려면 --skip-add-drop-table 옵션 사용 |
| -l, --lock-tables | Lock all tables for read. | | --skip-lock-tables 로 제외가능. 이 옵션보다는 아래의 --single-transaction 사용 추천 |
| --single-transaction | lock 을 걸지 않고도 dump 파일의 정합성 보장 | | InnoDB 일때만 사용 가능 |
| --ignore-table | 제외할 테이블명을 주면 dump에서 제외 | | 여러개의 테이블을 제외할 경우 테이블명마다 앞에 --ignore-table 옵션을 주어야 함. , 로 여러개의 테이블을 주어도 한 테이블만 처리 |
| -n, --no-create-db | CREATE DATABASE 구문 제외 | | dump한 파일을 load 하려는 환경의 database 명이 다를 경우 유용함 |
| -t, --no-create-info | CREATE TABLE 구문 제외 | | dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용함 |
| -c, --complete-insert | INSERT INTO 구문 생성시 컬럼 이름도 포함 | | dump한 이후에 load 하려는 환경의 table 스키마가 추가/변경되었을 경우 유용(Column count doesn't match value count 에러 발생시) |
| --max_allowed_packet | 서버와 주고 받을 최대 패킷 사이즈 설정 | 1M | 첨부파일등이 DB 에 있을 경우 이 값을 크게 해야 한다. mysqldump: ‘max_allowed_packet’ 에러 처리 참고 |
| --triggers | trigger 도 dump | | --skip-triggers 로 제외할 수 있음 |
| --routines | stored procedure 와 function 도 dump | | MySQL 5 는 trigger는 기본적으로 덤프하나 function, procedure는 덤프하지 않음 |
| -w, --where | 조건에 맞는 레코드만 덤프. quote 필수 | |
참고 자료
사용예
모든 db 및 stored procedure/function 도 백업
mysqldump --single-transaction –-routines --all-databases -h localhost -u root -pmypwd > mydump.sql
db1, db2 만 백업
mysqldump --single-transaction --databases db1 db2 -h localhost -u root -pmypwd > mydump.sql
db1만 백업하고 CREATE 구문 미포함
--no-create-db 옵션을 사용하면 CREATE SCHEMA 가 제외됨.
mysqldump --single-transaction db1 --no-create-db -h localhost -u root -pmypwd > mydump.sql
USE `DATABASE` 구문 제외
--databases 옵션을 제거하고 직접 dump 할 데이타베이스 이름(예:EXPORT_DB_NAME )을 입력하면 USE `DATABASE` 구문이 생략되서 덤프됨.
mysqldump --single-transaction db1 -u root -pmypwd EXPORT_DB_NAME > mydump.sql
CREATE 구문 미포함 + INSERT 에 컬럼명 추가
mysqldump --single-transaction --no-create-db --no-create-info --complete-insert -h localhost -u root -pmypwd > mydump.sql
컬럼명을 포함할 경우 primary key 가 auto increment 일 경우 아래 에러가 발생하고 insert 실패함. 이럴 경우 mysqldump 로는 방법이 없고 별도의 VIEW 를 만들고 view 를 export 하거나 SELECT INTO 구문을 사용해야 함
ERROR 1062 (23000) at line 445: Duplicate entry '1' for key 'PRIMARY'
primary id 빼고 export 방법
table1, table2 만 덤프
mysqldump --single-transaction --databases db1 table1 table2 -h localhost -u root -pmypwd > mydump.sql
db1만 포함하고 db내 table1, table2만 제외
mysqldump --single-transaction --databases db1 --ignore-table=db1.table1 --ignore-table=db1.table2 -h localhost -u root -pmypwd > mydump.sql
특정 조건인 데이타만 덤프
id가 10,000 이상인 데이타만 덤프
mysqldump --single-transaction --databases db1 --ignore-table=db1.table1 --ignore-table=db1.table2 -h localhost \ -u root -pmypwd \ --where='id>10000' > mydump.sql
created_at 컬럼이 어제 이후인 데이타만 덤프
mysqldump --single-transaction --databases db1 --ignore-table=db1.table1 --ignore-table=db1.table2 -h localhost \ -u root -pmypwd \ --where='created_at > subdate(current_date, 1)' > mydump.sql
실전 예
mydb의 table1, table2 의 id 가 2,000 이상만 덤프후 primary key(id) 제거
덤프
mysqldump --complete-insert --no-create-db --no-create-info --single-transaction -u root -pmypwd mydb table1 table2 --where='id > 2000' > mydump.sql
id 값 제거
sed -e "s/([0-9]*,/(/gi" mydump.sql > mydump2.sql
editor 로 mydump2.sql 열어서 insert 구문에서 `id` 삭제
dump 파일 복구
db2 에 복구
mysql -h dbhost2 -u root -pmypwd db2 < mydump.sql
cron 으로 자동 백업
mysql_backup.sh
#!/bin/sh
## yyyy-mm-dd_hh:mm format
TIME=$(date "+%Y-%m-%d_%H:%M")
mysqldump --single-transaction –-routines --all-databases -h localhost -u root -pmypwd > ${TIME}-dump.sql
crontab -e 명령어로 스케줄러 등록
## 9시부터 저녁 7시까지 매시 정각에 백업 수행
0 9-19 * * * ${HOME}/mysql_dump.sh