mysqldump를 이용한 remote 백업

* 목표
MySQL 5.1.x 를 서비스중인 db 서버에서 백업용도의 db3 서버로 remote 자동백업 실시


* 사전 준비
1. 각 서버간 ssh key 인증 설정(db에서 db3로)
2. db, db3서버의 MySQL 자동 로그인 설정(~/.my.cnf)

root@db:~# cat ~/.my.cnf

코드:

[client] user = root host = localhost password = 암호

* 스크립트 작성

1. DB list에서 목록을 가져와 백업서버인 db3에 Database를 생성한다.
2. mysqldump로 목록에 있는 순서대로 백업한 후, 파이프라인과 ssh를 이용하여 백업서버인 db3로 넘긴다.
3. db3에서는 ssh로 넘어오는 data를 받아 바로 백업한다.
4. 모든 결과는 log로 남긴다.

root@db:~# cat dbbackup-from-filelist.sh

코드:

export LOGFILE=/root/dbbackup4.log; for db in $(cat /root/backuplist); do echo "Database: $db" >> $LOGFILE; ssh root@db3 "mysql -e 'create database IF NOT EXISTS $db'"; echo "Start time: `date '+2011%m%d_%H%M'`" >> $LOGFILE; mysqldump -v --databases --opt $db --log-error=$LOGFILE | ssh root@db3 'mysql $db' 2>> $LOGFILE; echo "End time: `date '+2011%m%d_%H%M'`" >> $LOGFILE; echo "---------------------------------------------------" >> $LOGFILE; done

* 백업 대상 Database list 작성
root@db:~# cat backuplist

코드:

DB_1 DB_2 DB_3 DB_4

* 전체 DB인 경우라면

코드:

mysql -N -B -e 'show databases;' | egrep -v "information_schema|mysql" > backuplist

로 생성한다.


* 백그라운드로 실행
root@db:~# nohup dbbackup-from-filelist.sh &


* 로그 확인
root@db:~# tail -f dbbackup4.log

코드:

Database: DB_1 Start time: 20110411_0739 -- Connecting to localhost... -- Retrieving table structure for table DB1_table1... -- Sending SELECT query... -- Retrieving rows... -- Retrieving table structure for table DB1_table2... -- Sending SELECT query... -- Retrieving rows... -- Disconnecting from localhost... End time: 20110411_0926 --------------------------------------------------- Database:  DB_2 Start time: 20110411_0926 -- Connecting to localhost... -- Retrieving table structure for table DB2_table1...

* 문제해결

코드:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table 'DB_1' at row: 339

와 같이 백업 중간에 에러가 나면서 중단된다면, 다음과 같이 양쪽 서버에서 명령한다. 미리 변경해주는게 좋겠다.

기본값이 60초인 timeout값을 늘려준다.

코드:

mysql -e "set global net_read_timeout=10800;" mysql -e "set global net_write_timeout=10800;"

참고 : http://blog.naver.com/junix/80128043759

전체 1.5TB 용량의 총 303개의 MySQL Database를 원격지에 1차 백업하기 위해 작업한 내용입니다. 또한 현재 InnoDB를 MyISAM으로도 변환하고요.