-
MySQL 백업 및 복구 토요일, 2006/09/30 - 14:21 — yejr
MySQL 백업 및 복구
저자/번역자: 예진롱(이메일: ), 출처: http://imysql.cn .재인쇄 시 작성자/번역자 및 출처를 명시해 주시기 바랍니다.
날짜: 2006/10/01
이 문서에서는 MySQL의 백업 및 복구 메커니즘과 두 가지 주요 테이블 유형인 MyISAM 및 Innodb를 포함하여 데이터 테이블을 유지하는 방법에 대해 설명합니다. 이 문서에서 설계된 MySQL 버전은 5.0.22입니다.
현재 MySQL에서 지원하는 무료 백업 도구에는 mysqldump, mysqlhotcopy가 있습니다. 또한 백업용 SQL 구문(BACKUP TABLE 또는 SELECT INTO OUTFILE)을 사용하거나 바이너리 로그(binlog)를 백업하거나 데이터 파일 및 관련 구성 파일을 직접 복사할 수도 있습니다. MyISAM 테이블은 파일로 저장되므로 백업하기가 상대적으로 쉽습니다. 위에서 언급한 몇 가지 방법을 사용할 수 있습니다. Innodb의 모든 테이블은 동일한 데이터 파일 ibdata1(여러 파일일 수도 있고 독립적인 테이블 공간 파일일 수도 있음)에 저장되며, 이는 상대적으로 백업하기 어렵습니다. 데이터 파일을 복사하고 binlog를 백업하는 것이 있습니다. .
1.mysqldump
1.1 백업
mysqldump는 SQL 수준 백업 메커니즘을 사용하며, 이는 서로 다른 MySQL 버전 간 업그레이드에 비교적 적합합니다.
이제 mysqldump의 주요 매개변수 중 일부에 대해 이야기해 보겠습니다.
--호환=이름
내보낸 데이터가 호환되는 데이터베이스 또는 이전 버전의 MySQL 서버를 mysqldump에 알려줍니다. 값은 ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options 등이 될 수 있습니다. 여러 값을 사용하려면 쉼표로 구분하세요. 물론 완전한 호환성을 보장하지는 않지만 호환되도록 노력합니다.
--완전-삽입, -c
내보낸 데이터는 필드 이름을 포함한 완전한 INSERT 메서드를 사용합니다. 즉, 모든 값이 한 줄에 기록됩니다. 이렇게 하면 삽입 효율성을 높일 수 있지만, max_allowed_packet 매개변수의 영향을 받아 삽입 실패가 발생할 수 있습니다. 따라서 이 매개변수는 주의해서 사용해야 합니다. 적어도 권장하지는 않습니다.
--기본 문자 집합=문자 집합
데이터를 내보낼 때 사용할 문자 집합을 지정합니다. 데이터 테이블이 기본 latin1 문자 집합을 사용하지 않는 경우 내보낼 때 이 옵션을 지정해야 합니다. 그렇지 않으면 데이터를 다시 가져온 후 문자가 깨집니다.
--비활성화 키
INSERT 문의 시작과 끝 부분에 /*!40000 ALTER TABLE table DISABLE KEYS */; 및 /*!40000 ALTER TABLE table ENABLE KEYS */; 문을 추가하도록 mysqldump에 지시합니다. 왜냐하면 모든 데이터가 삽입된 후에 인덱스가 다시 작성되기 때문입니다. 이 옵션은 MyISAM 테이블에만 적합합니다.
--확장-삽입 = true|false
기본적으로 mysqldump는 --complete-insert 모드를 활성화하므로 이 모드를 사용하지 않으려면 이 옵션을 사용하고 값을 false로 설정하면 됩니다.
--16진수 덩어리
16진수 형식을 사용하여 이진 문자열 필드를 내보냅니다. 바이너리 데이터가 있는 경우 이 옵션을 사용해야 합니다. 영향을 받는 필드 유형은 BINARY, VARBINARY 및 BLOB입니다.
--모든 테이블 잠금,-x
내보내기를 시작하기 전에 데이터 일관성을 보장하기 위해 모든 데이터베이스의 모든 테이블을 잠그는 요청을 제출하세요. 이는 전역 읽기 잠금이며 --single-transaction 및 --lock-tables 옵션을 사용하면 자동으로 꺼집니다.
--잠금 테이블
--lock-all-tables와 유사하지만 데이터베이스의 모든 테이블을 한 번에 잠그는 대신 현재 내보낸 데이터 테이블을 잠급니다. 이 옵션은 MyISAM 테이블에만 적용 가능합니다. Innodb 테이블인 경우 --single-transaction 옵션을 사용할 수 있습니다.
--no-create-info, -t
CREATE TABLE 문을 추가하지 않고 데이터만 내보냅니다.
--no-데이터,-d
데이터는 내보내지지 않으며 데이터베이스 테이블 구조만 내보내집니다.
--고르다
이는 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-를 추가하는 것과 동일한 빠른 옵션일 뿐입니다. 문자셋 옵션. 이 옵션을 사용하면 mysqldump가 데이터를 빠르게 내보내고 내보낸 데이터를 빠르게 다시 가져올 수 있습니다. 이 옵션은 기본적으로 활성화되어 있지만 --skip-opt를 사용하여 비활성화할 수 있습니다. --quick 또는 --opt 옵션을 지정하지 않고 mysqldump를 실행하면 전체 결과 세트가 메모리에 저장됩니다. 대용량 데이터베이스를 내보내는 경우 문제가 발생할 수 있습니다.
--빠른,-q
이 옵션은 큰 테이블을 내보낼 때 유용합니다. 모든 레코드를 검색하고 메모리에 캐싱하는 대신 mysqldump가 서버 쿼리에서 얻은 레코드를 직접 출력하도록 합니다.
--루틴, -R
저장 프로시저 및 사용자 정의 함수를 내보냅니다.
--단일 트랜잭션
이 옵션은 데이터를 내보내기 전에 BEGIN SQL 문을 제출합니다. BEGIN은 애플리케이션을 차단하지 않으며 내보내기 중에 데이터베이스의 일관된 상태를 보장합니다. InnoDB 및 BDB와 같은 트랜잭션 테이블에서만 작동합니다.
LOCK TABLES로 인해 보류 중인 트랜잭션이 암시적으로 커밋되기 때문에 이 옵션과 --lock-tables 옵션은 상호 배타적입니다.
큰 테이블을 내보내려면 --quick 옵션을 조합하여 사용해야 합니다.
--트리거
또한 트리거를 내보냅니다. 이 옵션은 기본적으로 활성화되어 있으며, 비활성화하려면 --skip-triggers를 사용하십시오.
다른 매개변수에 대한 자세한 내용은 설명서를 참조하세요. 저는 일반적으로 MyISAM 테이블을 백업하기 위해 다음 SQL을 사용합니다.
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x db_name > db_name.sql
Innodb 테이블을 백업하려면 다음 SQL을 사용하십시오.
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --단일 트랜잭션 db_name > db_name.sql
또한 온라인 백업을 구현하려는 경우 다음과 같이 --master-data 매개변수를 사용할 수도 있습니다.
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--single-transaction --flush-logs db_name > db_name.sql
처음에만 잠금 테이블을 요청한 다음 binlog를 새로 고친 다음 내보낸 파일에 CHANGE MASTER 문을 추가하여 현재 백업의 binlog 위치를 지정하려면 이 파일을 슬레이브에 복원하려면 다음을 사용할 수 있습니다. 이렇게 하면 됩니다.
1.2 복원 mysqldump로 백업된 파일은 직접 가져올 수 있는 SQL 스크립트이며, 데이터를 가져오는 방법에는 두 가지가 있습니다.
예를 들어 mysql 클라이언트를 직접 사용하십시오.
/usr/local/mysql/bin/mysql -uyejr -pyejr db_name < db_name.sql
SOURCE 구문을 사용하는 것은 실제로 표준 SQL 구문이 아니라 mysql 클라이언트에서 제공하는 함수입니다. 예를 들면 다음과 같습니다.
소스 /tmp/db_name.sql;
여기에서 파일의 절대 경로를 지정해야 하며, 이는 mysqld 실행 사용자(예: none)가 읽을 권한이 있는 파일이어야 합니다.
2. mysql핫카피
2.1 백업
mysqlhotcopy는 원래 Tim Bunce가 작성한 PERL 프로그램입니다. LOCK TABLES, FLUSH TABLES 및 cp 또는 scp를 사용하여 데이터베이스를 빠르게 백업합니다. 데이터베이스 또는 단일 테이블을 백업하는 가장 빠른 방법이지만 데이터베이스 파일(데이터 테이블 정의 파일, 데이터 파일 및 인덱스 파일 포함)이 있는 머신에서만 실행할 수 있습니다. mysqlhotcopy는 MyISAM을 백업하는 데에만 사용할 수 있으며 Unix 계열 및 NetWare 시스템에서만 실행됩니다.
mysqlhotcopy는 한 번에 여러 데이터베이스 복사를 지원하고 정규식도 지원합니다. 다음은 몇 가지 예입니다.
루트#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp(데이터베이스 디렉터리 db_name 변경
/tmp에 복사
아래에)
루트#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name_1 ... db_name_n /tmp
루트#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name./regex/ /tmp
자세한 사용법은 매뉴얼을 참조하거나, mysqlhotcopy의 도움말을 보려면 다음 명령을 호출하십시오:
perldoc /usr/local/mysql/bin/mysqlhotcopy
mysqlhotcopy를 사용하려면 SELECT 및 RELOAD(FLUSH TABLES 실행) 권한이 있어야 하며, datadir/db_name 디렉터리를 읽을 수 있는 권한도 있어야 합니다.
2.2 복원
Mysqlhotcopy는 전체 데이터베이스 디렉터리를 백업할 때 mysqld가 지정한 datadir(여기서는 /usr/local/mysql/data/)에 직접 복사할 수 있습니다. 동시에 권한 문제에 주의해야 합니다. 다음 예와 같이:
루트#cp -rf db_name /usr/local/mysql/data/
root#chown -R none:nobody /usr/local/mysql/data/ (db_name 디렉토리의 소유자를 mysqld로 변경
실행 중인 사용자)
3. SQL 구문 백업
3.1 백업
BACKUP TABLE 구문은 실제로 mysqlhotcopy의 작동 원리와 유사합니다. 둘 다 테이블을 잠근 다음 데이터 파일을 복사합니다. 온라인 백업이 가능하지만 효과가 이상적이지 않으므로 권장하지 않습니다. 테이블 구조 파일과 데이터 파일만 복사하고 인덱스 파일은 동시에 복사하지 않으므로 복구 속도가 느려집니다.
예:
BACK TABLE tbl_name TO '/tmp/db_name/';
이 SQL을 실행하려면 FILE 권한이 있어야 하며 /tmp/db_name/ 디렉토리는 mysqld 사용자가 쓸 수 있어야 합니다. 보안 문제를 피하기 위해 내보낸 파일은 기존 파일을 덮어쓸 수 없습니다.
SELECT INTO OUTFILE은 데이터를 일반 텍스트 파일로 내보냅니다. 이 데이터 처리를 용이하게 하기 위해 필드 간격을 사용자 정의할 수 있습니다.
예:
SELECT * INTO OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
이 SQL을 실행하려면 FILE 권한이 있어야 하며 /tmp/db_name/tbl_name.txt 파일은 mysqld 사용자가 쓸 수 있어야 합니다. 보안 문제를 피하기 위해 내보낸 파일은 기존 파일을 덮어쓸 수 없습니다.
3.2 BACKUP TABLE 방법을 사용하여 백업된 파일을 복원하려면 RESTORE TABLE 문을 실행하여 데이터 테이블을 복원할 수 있습니다.
예:
'/tmp/db_name/'에서 테이블 복원;
권한 요구 사항은 위에서 설명한 것과 유사합니다.
SELECT INTO OUTFILE 메서드를 사용하여 백업된 파일의 경우 LOAD DATA INFILE 문을 실행하여 데이터 테이블을 복원할 수 있습니다.
예:
LOAD DATA INFILE '/tmp/db_name/tbl_name.txt' INTO TABLE tbl_name;
권한 요구 사항은 위에서 설명한 것과 유사합니다. 데이터를 가져오기 전에 데이터 테이블이 이미 존재해야 합니다. 데이터 중복이 걱정된다면 REPLACE 키워드를 추가하여 기존 레코드를 바꾸거나 IGNORE 키워드를 사용하여 무시할 수 있습니다.
4. 바이너리 로그(binlog) 활성화
binlog를 사용하는 방법은 상대적으로 더 유연하고 걱정과 노력을 줄여주며 증분 백업도 지원할 수 있습니다.
binlog가 활성화되면 Mysqld를 다시 시작해야 합니다. 먼저 mysqld를 닫고 my.cnf를 열고 다음 줄을 추가합니다.
서버 ID=1
로그빈 = binlog
로그 빈 인덱스 = binlog.index
그런 다음 mysqld를 시작하십시오. 작업 중에 Binlog.000001과 binlog.index가 생성됩니다. 전자는 mysqld에서 데이터에 대한 모든 업데이트 작업을 기록하고, 후자는 모든 binlog의 인덱스이므로 쉽게 삭제할 수 없습니다. binlog에 대한 자세한 내용은 설명서를 참조하세요.
백업이 필요한 경우 먼저 SQL 문을 실행하여 mysqld가 현재 binlog에 대한 쓰기를 종료하도록 한 다음 파일을 직접 백업할 수 있습니다. 이러한 방식으로 증분 백업의 목적을 달성할 수 있습니다.
FLUSH LOGS; 복제 시스템에서 슬레이브 서버를 백업하는 경우 master.info 및 Relay-log.info 파일도 백업해야 합니다.
백업된 binlog 파일은 다음과 같이 MySQL에서 제공하는 mysqlbinlog 도구를 사용하여 볼 수 있습니다.
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
이 도구를 사용하면 지정된 데이터베이스 아래의 모든 SQL 문을 표시할 수 있으며 시간 범위를 제한할 수도 있습니다. 이는 매우 편리합니다. 자세한 내용은 설명서를 참조하세요.
복원할 때 다음과 유사한 문을 사용할 수 있습니다.
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 | mysql -uyejr -pyejr db_name
mysqlbinlog의 SQL 문 출력을 직접 입력으로 사용하여 실행합니다.
유휴 머신이 있는 경우 이 방법을 사용하여 백업할 수도 있습니다. 슬레이브 머신의 성능 요구 사항이 상대적으로 낮기 때문에 비용이 저렴합니다. 증분 백업은 저렴한 비용으로 달성할 수 있으며 데이터 쿼리 압력의 일부를 공유할 수 있습니다.
5. 데이터 파일의 직접 백업 이전 방법에 비해 데이터 파일을 백업하는 것이 가장 직접적이고 빠르며 편리합니다. 단점은 기본적으로 증분 백업이 불가능하다는 것입니다. 데이터 일관성을 보장하려면 파일을 백업하기 전에 다음 SQL 문을 실행해야 합니다.
FLUSH TABLES WITH READ LOCK. 즉, 메모리의 모든 데이터를 디스크로 플러시하고 데이터 테이블을 잠가서 복사 프로세스 중에 새 데이터가 기록되지 않도록 합니다. 이 방법으로 백업된 데이터는 복원하기도 매우 간단합니다. 원래 데이터베이스 디렉터리에 다시 복사하기만 하면 됩니다.
Innodb 유형 테이블의 경우 해당 로그 파일, 즉 ib_logfile* 파일도 백업해야 합니다. Innodb 테이블이 손상된 경우 이러한 로그 파일을 사용하여 복구할 수 있기 때문입니다.
6. 백업 전략 중간 규모 규모의 시스템의 경우 백업 전략은 처음 전체 백업, 하루에 한 번 증분 백업, 일주일에 한 번 전체 백업 등으로 결정할 수 있습니다. 중요하고 사용량이 많은 시스템의 경우 하루에 한 번 전체 백업이 필요할 수도 있고, 한 시간에 한 번 또는 그보다 더 자주 증분 백업이 필요할 수도 있습니다. 온라인 비즈니스에 영향을 주지 않고 온라인 백업과 증분 백업을 달성하기 위한 가장 좋은 방법은 마스터-슬레이브 복제 메커니즘(복제)을 사용하여 슬레이브 시스템에 백업을 만드는 것입니다.
7. 데이터 유지 관리 및 재해 복구 DBA(아직은 아니네요 ㅎㅎ)로서 가장 중요한 업무 중 하나는 데이터 테이블을 안전하고 안정적으로 고속으로 사용할 수 있도록 하는 것입니다. 따라서 데이터 테이블을 정기적으로 유지 관리해야 합니다. 다음 SQL 문이 유용합니다.
CHECK TABLE 또는 REPAIR TABLE, MyISAM 테이블 확인 또는 유지 관리
OPTIMIZE TABLE, MyISAM 테이블 최적화
ANALYZE TABLE, MyISAM 테이블 분석 물론 위의 명령은 모두 myisamchk 도구를 통해 완료할 수 있으므로 여기서는 자세히 설명하지 않습니다.
Innodb 테이블은 다음 명령문을 실행하여 조각 모음을 수행하고 인덱싱 속도를 향상시킬 수 있습니다.
ALTER TABLE tbl_name ENGINE = Innodb;
이는 실제로 NULL 작업입니다. 표면적으로는 아무 작업도 수행하지 않지만 실제로는 조각을 재배열합니다.
일반적으로 사용되는 MyISAM 테이블은 위에서 언급한 방법을 사용하여 복원할 수 있습니다. 인덱스가 손상된 경우 myisamchk 도구를 사용하여 인덱스를 다시 작성할 수 있습니다. Innodb 테이블의 경우 모든 테이블을 하나의 테이블스페이스에 저장하기 때문에 그렇게 간단하지 않습니다. 그러나 Innodb에는 퍼지 체크포인트(fuzzy checkpoint)라는 검사 메커니즘이 있습니다. 로그 파일이 저장되어 있는 동안에는 로그 파일을 기반으로 오류를 복구할 수 있습니다. my.cnf 파일에 다음 매개변수를 추가하면 mysqld가 시작될 때 자동으로 로그 파일을 확인하도록 할 수 있습니다.
innodb_force_recovery = 4
이 매개변수에 대한 자세한 내용은 설명서를 참조하세요.
8. 데이터 백업을 요약하고 적절한 백업 전략을 결정하는 것은 DBA가 하는 일 중 작은 부분입니다.