4. XtraBackup 상황별 스크립트

2022.07.01 - [개발/MySQL] - 3. XtraBackup Master-Slave 원격 백업

 

상황별 스크립트

• xtrabackup + binlog 복구 스크립트

# xtrabackup (fullbackup) + binlog 를 이용한 복구 스크립트 

# --start-position 과 --stop-datetime 옵션으로 fullbackup 이후 시점부터 원하는 시점까지의 복구를 진행한다.
# binlog flie과 position은 xtrabackup 백업 폴더의 xtrabackup_binlog_info 파일에 저장된다.
# $backup_path/xtrabackup_binlog_info 를 참고하여 복구 대상 binlog 파일과 position을 지정한다.

# backup_path=/tmp/slave/2022-04-22_10-37-07
# binlog=`awk '{print $1}' $backup_path/xtrabackup_binlog_info`
# position=`awk '{print $2}' $backup_path/xtrabackup_binlog_info`


innobackupex --user=root --password="password" --databases=DB_NAME /backup_path

systemctl stop mysqld
mv /data/mysql /data/mysql_old

innobackupex --apply-log /backup_path/fullbackup_path/
innobackupex --copy-back /backup_path/fullbackup_path/
chown -R mysql:mysql mysql

systemctl start mysqld

mysqlbinlog bin.000001 ... bin.00000n --start_position="191940017" --stop-datetime="2022-04-25 15:32:00" -d DB_NAME > /tmp/pre_restore_789.sql

mysql -uroot -p DB_NAME < /tmp/pre_restore_789.sql



# 예시
innobackupex --user=root --password="1234" --databases=testdb /tmp/bak

systemctl stop mysqld_test
mv /data/mysql_test /data/mysql_test_old

innobackupex --apply-log /tmp/bak/2022-04-25_15-28-17/
innobackupex --defaults-file=/etc/mysql_test/my.cnf --copy-back /tmp/bak/2022-04-25_15-28-17/
chown -R mysql:mysql /data/mysql_test

systemctl start mysqld

mysqlbinlog bin.000007 bin.000008 bin.000009 --start_position="191940017" --stop-datetime="2022-04-25 15:32:00" -d testdb > /tmp/bin_220425.sql

mysql -uroot -p testdb < /tmp/bin_220425.sql

 

---

• mysqldump + binlog 복구 스크립트

# --master-data 옵션으로 binlog file과 position 정보를 저장한다.
# 해당 정보를 참고하여 복구 대상 binlog 파일과 position을 지정한다.


mysqldump -uroot -p"password" --databases DB_NAME --tables TABLE_NAME --single-transaction --master-data=2 > /backup_path/data_dump.sql

mysql -uroot -p testdb < /backup_path/data_dump.sql

mysqlbinlog bin.000001 ... bin.00000N --start-position=168753309 --stop-datetime="2022-04-25 15:32:00" --verbose -d DB_NAME > /backup_path/data_binlog.sql

mysql -uroot -p DB_NAME < /backup_path/data_binlog.sql



## 예시
mysqldump -uroot -p"password" --databases testdb --tables test_user --single-transaction --master-data=2 > /tmp/master_data.sql

mysql -uroot -p testdb < /tmp/master_data.sql

mysqlbinlog bin.000012 bin.000013 bin.000014 --start-position=168753309 --verbose -d testdb > /tmp/pre_restore_121314.sql

mysql -uroot -p testdb < /tmp/pre_restore_121314.sql

 

---

원격 백업 스크립트

• SLAVE 서버에서 MASTER 받아오기

# 설치는 epel 필요
yum install epel-release sshpass

cd /backup_path

sshpass -p "password" ssh -o StrictHostKeyChecking=no root@192.168.3.22 \
"/usr/local/xtrabackup/bin/innobackupex \
--defaults-file=/etc/mysql_testdb/my.cnf \
--host="localhost" \
--user="root" \
--password="1234" \
--databases=test \
--stream=xbstream \
/tmp/test" 2> innobackupex.log \
| /usr/local/xtrabackup/bin/xbstream -x 2> xbstream.log

• 운영중인 서버에 master 추가

#1. 21번에서 mysqldump
/usr/local/mysql_testdb/bin/mysqldump -uroot -p --databases test --single-transaction --master-data > /tmp/test/bak_test.sql

#2. 21번 -> 22번으로 파일 넘기기
scp -rp -l 300000 /tmp/test/bak_test.sql 192.168.3.22:/tmp/

#3. 22번에 데이터 올리기
/usr/local/mysql_testdb2/bin/mysql -uroot -p mysql < /tmp/bak_test.sql

#4. 22번이 21번을 바라보도록 replication 걸기, 걸고 나면 stop slave

CHANGE MASTER TO
MASTER_HOST='192.168.3.21',
MASTER_USER='replicator',
MASTER_PASSWORD='password'
MASTER_LOG_FILE='bin.000020',
MASTER_LOG_POS=205879132;

START SLAVE;


#5. 21번이 22번을 바라보도록 replication 걸기, 그 후로 22번에 데이터가 들어가지 않았기 때문에 position은 그대로일 것
## 22번
SHOW MASTER STATUS;

## 21번
CHANGE MASTER TO
MASTER_HOST='192.168.3.22',
MASTER_USER='replicator',
MASTER_PASSWORD='password' ,
MASTER_LOG_FILE='bin.000020',
MASTER_LOG_POS=205879132;

START SLAVE;

 

• 2 master 구조에서 하나의 master 가 shutdown이 되었을 때

+) fullbackup을 받은 서버가 shutdown이 된 경우

## 21번 서버
/usr/local/mysql_testdb/bin/mysqlbinlog bin.000002 bin.000003 --start-position="1791138" --stop-datetime="2022-05-09 17:45:00" --verbose > /tmp/test/bin_21_220509.sql

## 22번 서버
/usr/local/mysql_testdb/bin/mysqlbinlog bin.000003 --start-datetime="2022-05-09 17:45:00" --stop-datetime="2022-05-09 17:48:00" --verbose > /tmp/test/bin_22_220509.sql


/usr/local/mysql_xtratest/bin/mysql -uroot -p"1234" test < /tmp/test/bin_21_220509.sql

/usr/local/mysql_xtratest/bin/mysql -uroot -p"1234" test < /tmp/test/bin_22_220509.sql