xtrabackup --version
① sudo rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
② sudo rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
③ MYSQL 5.6인경우
- sudo yum install percona-xtrabackup-22
MYSQL 5.7인 경우
- sudo yum install percona-xtrabackup-24
vi /etc/my.cnf
- 아래 내용 추가
[mysqld]
innodb_log_file_size=5M
[xtrabackup]
datadir=/var/lib/mysql
MYSQL에 접속한 뒤에
bkpuser 계정을 생성하고 백업에 대한 권한을 부여합니다.
① mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '000000';
② mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON . TO 'bkpuser'@'localhost';
③ mysql> FLUSH PRIVILEGES;
맨 마지막 줄에 /db_backup는 백업폴더를 저장할 경로입니다. 알맞게 변경하시면 됩니다.
sudo innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --host=localhost --port=3306 --password='000000' --no-lock /db_backup
3번째에 /db_backup/2022-01-20_17-02-53는 백업받은 폴더 경로입니다.
① sudo systemctl stop mysqld
② sudo rm -rf /var/lib/mysql/
③ sudo innobackupex --defaults-file=/etc/my.cnf --copy-back /db_backup/2022-01-20_17-02-53
④ sudo chown -R mysql: /var/lib/mysql/
⑤ sudo systemctl start mysqld
① rpm -qa | grep xtrabackup
② yum remove percona-xtrabackup-22-2.2.13-1.el7.x86_64
폴더 생성
백업실행
① vi /root/crontab_shell/db_backup.sh
↓ 아래입력
#!/bin/bash
sudo innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --host=localhost --port=3306 --password='000000' --no-lock /db_backup
#!/bin/bash
today=`date +"%Y-%m-%d"`
tar cvzf /storage/backup/db/db_backup_${today}.tar.gz /db_backup/${today}*
Password! 이부분에서 서버 비밀번호를 적어 주시면 됩니다.
↓ 아래입력
#!/bin/bash
today=`date +"%Y-%m-%d"`
sshpass -pPassword! scp -o StrictHostKeyChecking=no /storage/backup/db/db_backup_${today}* root@192.168.94.160:/data/test
#!/bin/sh
find /storage/backup/db -mtime +24 -exec rm {} \;
find /db_backup -mtime +24 -exec rm {} \;
크론탭 설정
① crontab -e
↓ 아래입력
# db backup
10 1 * * * /root/crontab_shell/db_backup.sh
# db tar create
15 1 * * * /root/crontab_shell/db_tar_create.sh
# 192.168.94.160 db tar send
20 1 * * * /root/crontab_shell/db_scp_send.sh
# db backup tar 24~ remove
25 1 * * * /root/crontab_shell/db_backup_remove.sh
② systemctl restart crond