mysqldump 는 기본적으로 --lock-tables 로 동작해 덤프하는 동안 대상 테이블에 읽기 잠금을 건다. 운영 중인 InnoDB 데이터베이스에서는 --single-transaction 을 쓴다.
mysqldump --defaults-extra-file=/etc/mysql/backup.cnf \
--single-transaction --quick \
appdb > /backup/appdb.sql
--single-transaction 은 덤프 시작 시점에 REPEATABLE READ 트랜잭션을 열어 그 시점의 일관된 스냅샷을 읽는다. 잠금을 걸지 않으므로 다른 세션은 그대로 쓰기를 계속한다.
한계가 분명하다. 트랜잭션을 지원하는 스토리지 엔진에서만 일관성이 보장된다. MyISAM 테이블이 섞여 있으면 그 테이블은 스냅샷 밖에서 읽히므로 시점이 어긋난다. 또 덤프 도중 ALTER TABLE · TRUNCATE 같은 DDL 이 실행되면 스냅샷이 깨진다.
--quick 은 결과를 한 행씩 흘려보내 클라이언트 메모리에 전체를 올리지 않게 한다. 큰 테이블에서는 사실상 필수다.
# 데이터베이스 여러 개. CREATE DATABASE / USE 문이 함께 들어간다
mysqldump --single-transaction --databases db_1 db_2 db_3 > dbs.sql
# 특정 테이블만. CREATE DATABASE 문은 들어가지 않는다
mysqldump --single-transaction appdb users orders > tables.sql
# 스키마만
mysqldump --no-data appdb > schema.sql
# 데이터만
mysqldump --no-create-info --single-transaction appdb > data.sql
--databases 를 쓰지 않고 데이터베이스 이름 하나만 적으면 CREATE DATABASE 문이 들어가지 않는다. 복원할 대상 데이터베이스를 미리 만들어야 한다.
-- Warning: column statistics not supported by the server.
MySQL 8.0 계열의 mysqldump 로 MariaDB 나 MySQL 5.7 서버를 덤프할 때 나온다. MySQL 8.0 클라이언트는 히스토그램 통계를 함께 받으려고 --column-statistics=1 로 동작하는데, 서버가 그 기능을 모르기 때문이다. 덤프 자체는 정상이며 옵션으로 끌 수 있다.
mysqldump --column-statistics=0 --single-transaction appdb > appdb.sql
MariaDB 가 제공하는 mariadb-dump 에는 이 옵션이 아예 없다. 서버와 같은 배포판의 클라이언트를 쓰는 편이 가장 안전하다.
MySQL 8.0 은 테이블스페이스 정보를 읽기 위해 PROCESS 권한을 요구한다. 백업 계정에 주기 싫으면 --no-tablespaces 를 붙인다.
mysql --defaults-extra-file=/etc/mysql/backup.cnf appdb < /backup/appdb.sql
압축한 덤프는 풀면서 바로 넣는다.
gunzip -c /backup/appdb.sql.gz | mysql --defaults-extra-file=/etc/mysql/backup.cnf appdb
복원 중 오류가 나도 기본값으로는 중간에 멈추지 않고 계속 진행한다. 실패 시 멈추게 하려면 mysql --init-command="SET SESSION sql_mode=''" --abort-source-on-error 처럼 옵션을 주거나, 복원 로그를 반드시 확인한다.
find /backup -type f -name 'appdb_*.sql.gz' -mtime +30 -delete
-mtime +30 은 수정 시각이 30일보다 오래된 파일을 고른다. 날짜를 파일명에서 파싱하지 말고 파일 시각을 쓰는 편이 안전하다. 다만 파일을 복사해 옮기면 시각이 갱신될 수 있으므로 cp -p 또는 rsync -a 로 시각을 보존한다.