XtraBackup 으로 mysql 백업구성

XtraBackup

1. mysql 백업유저 생성 및 경로생성

  - GRANT RELOAD, SUPER, REPLICATION CLIENT, LOCK TABLES, SELECT ON *.* TO 'backup'@'localhost' IDENTIFIED BY 'password';

  - mkdir -p /var/mysql/backup

2. 필요패키지 설치

  - yum install libev4-4.15-7.1.x86_64.rpm

3. XtraBackup 다운로드

  - http://www.percona.com/downloads/XtraBackup/

  - yuminstall percona-xtrabackup-2.3.2-1.el6.x86_64.rpm

4. XtraBackup 실행

  - innobackupex --defaults-file=/etc/my.cnf --ibbackup=xtrabackup --user backup --password '패스워드' --host=localhost /var/mysql/backup

5. crontab을 통한 자동화

  - 백업스크립트 생성 (root)

    a. vi /var/mysql/backup.sh

#! /bin/bash

innobackupex --defaults-file=/etc/my.cnf --ibbackup=xtrabackup --user backup --password '패스워드' --host=localhost /var/mysql/backup

    b. chmod +x backup.sh

  - crontab 설정 (root)

    a. crontab -e

        ㄱ. 0 6 * * * /var/mysql/backup.sh > /dev/null

6. 복원

  - 백업된 폴더 복사 (root)

    a. cp -r /var/mysql/backup/2015-11-27_06-00-01/test /var/mysql/data/

  - mysql 데몬 정지 (root)

    a. service mysqld stop

  - 권한부여 (root)

    a. chown -R mysql:mysql /var/mysql/data

  - mysql 데몬 실행 (root)

    a. service mysqld start

  - database check (not root)

    a. mysqlcheck --all-databases -uroot -p