MySQL 백업 및 복구
- 高洛峰원래의
- 2016-12-02 14:53:39847검색
Mysql 백업 분류:
Mysql 데이터 백업:
논리적 백업, 물리적 백업
데이터 세트에 따른 MySQL 분류:
전체 백업
증분 백업
차등 백업
일반적으로 이를 조합하여 사용하는데, 예를 들어 전체 백업은 일주일에 한 번, 증분 백업
, 차등 백업은 하루에 한 번만 수행할 수 있지만 이는 기업의 비즈니스 로직에 따라 다르므로
적절한 선택은 데이터 복구 중 손실을 최소화합니다.
MySQL은 서버 다운 여부에 따라 분류됩니다.
콜드 백업: 서비스가 완전히 중지됩니다.
웜 백업: 서비스가 종료되지 않지만 백업 중에는 다른 트랜잭션에서는 쿼리 작업만 수행됩니다.
핫 백업: 서비스가 온라인이며 읽기 및 쓰기에 영향을 주지 않습니다(그러나 비트랜잭션 스토리지 엔진의 경우 핫 백업을 구현하기는 어렵지만
LVM을 사용하여 거의 핫 백업을 수행할 수 있음)
필요한 사항 백업 대상:
1. 데이터 파일
2. 로그 파일(트랜잭션 로그, 바이너리 로그)
3. 저장 프로시저, 저장 함수, 트리거
4. 구성 파일
로그 분류 :
1. 트랜잭션 로그:
랜덤 IO를 순차 IO로 변환
이벤트 재생(undo)을 위한 기반 제공
2. 이벤트 트랜잭션:
기록 시간 기록
MySQL 로그 파일
1. 오류 로그 오류 로그
오류 정보, 서버 프로세스가 시작되거나 중지될 때 생성되는 정보입니다. 마스터가 복제를 추가하면
,
기본 구성 파일 시작 오류 로그도 기록됩니다.
log_error=/var/log/mysqld.err (mysqld에 대한 쓰기 권한이 있는지 확인)
2. 바이너리 로그 바이너리 로그
증분 백업 제공
특정 시점 복구
복제 아키텍처의 기초를 제공합니다.
log_bin=/data/logs/binary/changelog(데이터 파일의 기본값)
log_bin_index=/data/logs/relay/binarylog는 바이너리의
인덱스 위치를 정의합니다. 파일
3. 일반 쿼리 로그 일반적으로 열지 않는 것이 좋습니다
General_log= ON|OFF
General_log_file=/PATH
4. 느린 쿼리 로그 느린 쿼리 로그
Slow_query_log=ON
Slow_query_log_file=/PATH
long_query_time=NUM은 시간 초과를 정의합니다.
5.relay 로그 릴레이 로그
기본적으로 Mysql은 로그를 활성화하지 않지만 오류 로그(
오류 로그)
Mysql에서 일반적으로 사용되는 백업 도구:
mysqldump
SELECT INTO OUTFILE
mk-parallel-backup
ibbackup
mysqlhostcopy(MyISAM 스토리지 엔진의 거의 콜드 백업, 이름은 hot Backup과 비슷함) )
다음으로 간단한 데이터베이스를 사용하여 데이터 백업 및 복원 과정을 보여드리겠습니다.
1. mysqldump를 사용하여 백업
mysqldump의 백업은 논리적 백업입니다. > 백업 형식은 다음과 같습니다.
#mysqldump db_name [table_name] > /PATH
물론 mysqldump 백업에는 다양한 옵션이 있습니다.
--all-databases 모든 데이터베이스 백업
--events 백업 이벤트
--routines 백업 저장 기능
--triggers 백업 트리거
--extended-insert 백업 확장 삽입
--skip-extended-insert 백업 확장 삽입 끄기
--lock-all-tables 잠금, 이것이 필요합니다.
--flush-logs는 로그 파일을 디스크에 새로 고칩니다. 또한 필요합니다.
--master-data=n{0|1|2} 복제를 수행할 때 사용됩니다. (2)
--no-data는 데이터베이스의 테이블 구조만 백업합니다.
--여기서 "WHERE CLAUSE"는 지정된 데이터를 백업합니다.
mysql> CREATE DATABASE luowei;
mysql> SHOW DATABASES;//모든 데이터베이스 보기
+-------+
| 데이터베이스 |
+-- ---+
| information_schema |
| mysql |
| 테스트 |
+-------+
mysql> USE luowei;
mysql> CREATE TABLE st (
-> ID INT UNIQUE AUTO_INCREMENT,
-> 이름 VARCHAR(30) );
mysql> //테이블 보기
+------+
| Tables_in_luowei |
+------+
| st |
+------------ -----+
mysql> INSERT INTO st(이름) VALUE ('a'),('b'),('c');
mysql> SELECT * FROM st;
+----+------+
| ID 이름 |
+----+------+
|
| 2 | b |
| 3 | c |
+----+------+
나중에 필요하기 때문에 이것은 내 데이터베이스에 있는 원본 정보입니다. 데이터베이스와 테이블을 삭제하므로 나중에 확인하기 위해 먼저
의 데이터를 살펴보세요.
[root@nfs ~]# mysqldump -uroot -p luowei st >
백업 테이블 st.
비밀번호 입력:
이제 백업이 완료되었습니다.
열어서 살펴볼 수 있습니다
[root@nfs ~]# cat /root/luoweidb | grep -v "^/" | grep -v "^$"
-- MySQL 덤프 10.11
--
-- 호스트: localhost 데이터베이스: luowei
--------------- ------ ----------
-- 서버 버전 5.0.77
--
-- 현재 데이터베이스: `luowei`
--
데이터베이스 생성 /*!32312 존재하지 않는 경우*/ `luowei` /*!40100 DEFAULT
CHARACTER SET latin1 */;
USE `luowei`;
--
-- `st` 테이블의 테이블 구조
--
DROP TABLE IF EXISTS `st`;
SET @saved_cs_client = @@character_set_client;
SET Character_set_client = utf8 ;
CREATE TABLE ` st` (
`ID` int(11) NOT NULL auto_increment,
`Name` varchar(30) 기본값 NULL,
UNIQUE KEY `ID` (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
SET Character_set_client = @saved_cs_client;
--
-- `st` 테이블에 대한 데이터 덤프
--
LOCK 테이블 `st` 쓰기;
`st` 값에 삽입 (1,'a'),(2,'b'),(3,'c');
테이블 잠금 해제;
-- 2011-09-05 8:00:08에 덤프 완료
여기 데이터베이스 생성, 테이블 생성, 데이터 삽입을 위해 방금 수행한 전체 명령문이 있습니다.
;
다음으로 해보자 일부 데이터베이스 복구:
[root@nfs ~]# mysql -uroot -e "DROP DATABASE luowei;" -p //
데이터베이스 삭제
비밀번호 입력:
[root@nfs ~]# mysql -uroot -e " SHOW DATABASES;" -p //삭제된 데이터베이스 보기
비밀번호 입력:
+------ -+
| 데이터베이스 | 테스트 | root@nfs ~]# mysql -uroot -p < /root/luoweidb
비밀번호 입력:
[root@nfs ~]# my sql -e "데이터베이스 표시;"
+-- --- ---------------+
| 데이터베이스 |
+--------- -----+
| information_schema |
| luowei |
| ndodb |
+-- --------- -------+
좋아, 데이터베이스가 복원되었습니다. 다음 단계는 테이블의 정보가 완전한지 확인하는 것입니다
[root@nfs ~ ]# mysql -e "SELECT * FROM luowei.st " -uroot -p
비밀번호 입력:
+----+------+
| ID | +----+------ +
| 2 | b |
+--+-- --+
자, 함께 잘 지내요. 하하. 물론 다른 매개변수에 대해서는 자세히 소개하지 않겠습니다.
HELP 명령을 사용하여 도움을 요청할 수 있습니다.
다음은 로컬에서 Mysql 비밀번호를 입력하지 않아도 되는 방법을 소개합니다:
#vim /root/.my.cnf
[client]
user = root
비밀번호 = redhat
저장해두면 다음번에 데이터베이스를 조작할 때 mysql 명령어를 직접 사용하게 되며 비밀번호를 입력할 필요가 없습니다
다음 실험에서는 비밀번호를 입력하지 않겠습니다
2. SELECT INTO OUTFILE 사용
계속 백업에는 위의 표를 사용하세요.
[root@nfs ~]# mysql -e "SELECT * FROM luowei.st"
+----+- -----+
| 아이디 |
+----+------+
| 1 |
| 3 | c |
ID가 2 이하인 파일을 백업하려면 이 방법을 사용하여 백업할 수 있습니다.
mysql> SELECT * INTO OUTFILE '/tmp/st .bak' FROM st WHERE ID <=2;
[root@nfs ~]# cat /tmp/st.bak
1 a
2 b
테이블과 유사하게 텍스트 파일을 볼 수 있지만 이 방법은 데이터 자체만 백업하고
테이블 구조를 백업하지 않으므로 복원 시 새로 생성해야 합니다. 구조에 따라
restore
mysql> CREATE TABLE mt LIKE st; //st 테이블 형식을 기반으로 새
테이블 mt를 생성한 후 새로 백업한 데이터를 복원합니다. 새로운 테이블로 mt. 이건 원래 테이블로 복원하는거랑 비슷하네요 ㅎㅎ
하하 그냥 게을러지세요! ! !
mysql> LOAD 데이터 INFILE '/tmp/st.bak' INTO TABLE mt;
mysql> SELECT * FROM mt;
+----+------+
| ID | 이름 |
+----+------+
| 1 |
| 2 |
+----- -+
자, 이제 이 두 가지 방법을 요약해보겠습니다. 먼저 mysqldump로 백업한 데이터를 사용하여
백업되는 것은 데이터베이스에서 작동하는 일부 명령문이며 복구는 간단합니다.
를 사용하는 동안 SELECT INTO OUTFILE을 사용하면 지정된 데이터를 백업할 수 있고, 선택적으로 데이터를 백업할 수 있습니다.
그러나 복구 중에는 조금 더 번거로우며, 테이블을 생성해야 합니다.
3. LVM 기반 스냅샷 백업:
작업 단계:
a. 데이터 파일이 LV에 있는지 확인
b. 데이터베이스의 테이블에 읽기 잠금을 적용합니다. 🎜> c. 데이터가 있는 LV의 스냅샷을 찍습니다.
d. 데이터베이스의 읽기 잠금을 해제합니다.
e. 스냅샷 볼륨을 마운트하고 데이터 파일을 백업합니다(InnoDB 스토리지 엔진의 경우에도 마찬가지). 트랜잭션 로그
파일 백업)
f. 스냅샷 삭제
스냅샷 백업을 위해 LVM을 사용하는 조건은 다음과 같습니다.
>SHOW VARIABLES LIKE 'sync_binlog'가 1로 변경됨
>SHOW VARIABLES LIKE 'innodb_support_xa'가 ON 상태입니다
LV 스냅샷 백업
#mysql
>FLUSH TABLES WITH READ LOCK; //읽기 추가 lock
# lvcreate -L 50M -n mysnap -s /dev/ myvg/mydata //스냅샷 생성
>UNLOCK TABLES; //잠금 해제
#mount /dev/myvg/mysnap /mnt // 스냅샷 볼륨 마운트
#cd /mnt/data
# tar -jcf /root/mysql.tar.bz2 luowei/ ibdata1 ib_logfile* mysql-
bin.* //데이터 백업 및 압축
#umount /mnt //스냅샷 제거
#lvremove --force / dev/myvg/mysnap //스냅샷 제거
LV 복구
cd /usr/local/mysql
#chown -R mysql:mysql . //권한 수정
#scripts/mysql_install_db --user=mysql --datadir=/mydata/data/ //
재생성
#chown -R 루트 .
# killall mysqld //모든 mysql 프로세스 종료
#cd /mydata/data
#tar xf /root/mysql.tar.bz2 -C ./ //백업을 mysql 데이터 파일로 복원
그런 다음 언제 Mysql에 로그인하면 원본 데이터가 여전히 존재한다는 것을 알 수 있습니다
4. 특정 시점 복구를 위해 바이너리 로그 파일 사용
현재 사용 중인 트랜잭션 파일 표시
mysql> FLUSH TABLES WITH READ LOCK;
mysql> +-- --+------------+---------------+---- -- ----------+
| 파일 | Binlog_Do_DB |
+----+-- - -------+---------------+------+
| bin.000006 | 330 | ----+------+
mysql> //로그 파일을 스크롤하여 봅니다. 이 Position을 클릭하면 됩니다
바이너리 로그 파일은 데이터 수정 정보만 기록하므로
각 수정에 해당하는 위치가 변경됩니다
이제 바이너리 로그 파일에 데이터 행을 삽입합니다. table
mysql> INSERT INTO st(이름) VALUE('d');
mysql> INSERT INTO st(이름) VALUE('E');
mysql> SHOW MASTER STATUS;
+ -------- ----------+----------+------------+------ ------- -----+
| 파일 | Binlog_Do_DB |
+----+--- ------- -+---------------+------+
| .000007 | 553
+------+----------- ---+- ----+
이미 전체 백업을 했으니 이제
330개# mysqlbinlog --start-position 330 mysql-bin.000007
>/root/mysql.incr.sql //추가된 바이너리 파일 백업
그럼 이번에는 mysql에 로그인한다
mysql>DROP DATABASE luowei; 그런 다음 데이터베이스를 복원합니다
# service mysqld stop
# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
# cd /mydata/ data/
# tar xf /root/mysql.incr.sql -C .
# mysql < /root/mysql.incr.sql
# service mysqld start
데이터베이스에 로그인합니다.
데이터가 정상으로 복원되었음을 확인할 수 있습니다