간단한 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 필수

참고 자료

MYSQL Dump only certain rows

사용예

모든 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

같이 보기

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table

Ref

Browser not supported