바로바로 백업을 위한 스크립트
master, slave 서버 셋팅
master, slave - my.cnf 수정[code]# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1[/code]
master - my.cnf 수정
[code]server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = exampledb[/code]
slave - my.cnf수정
[code]server-id=2
master-connect-retry=60
replicate-do-db=exampledb[/code]
master 에 기본 접근권한 설정
[code]GRANT REPLICATION SLAVE ON *.* TO 'master_user'@'%' IDENTIFIED BY 'master_password';
FLUSH PRIVILEGES;[/code]
master script
중간에 주석은 dump형식의 백업[code]#!/bin/sh
# usage : replicate-master.sh
# setting zone : start
ROOT_ID=[입력]
ROOT_PASSWORD=[입력]
TARGET_DB=[입력] #ex) exampledb
TARGET_PATH=[입력] #ex) /var/lib/mysql/exampledb
SLAVE_SSH_ID=[입력] #ex) ssh account id
SLAVE_IP=[입력]
# setting zone : end
mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB -e "FLUSH TABLES WITH READ LOCK"
mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB -e "SHOW MASTER STATUS"
sudo tar cvfPz mysql-snapshot.tar.gz $TARGET_PATH
scp ~/mysql-snapshot.tar.gz $SLAVE_ID@$SLAVE_IP:~
#mysqldump -u$ROOT_ID -p$ROOT_PASSWORD --opt $TARGET_DB > ~/.snapshot.sql
#scp ~/.snapshot.sql $SLAVE_SSH_ID@$SLAVE_IP:~
mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB -e "UNLOCK TABLES"
#rm .snapshot.sql
sudo rm ~/mysql-snapshot.tar.gz[/code]
slave script
중간에 주석은 dump형식의 백업[code]#!/bin/sh
# usage : replicate-slave.sh [master_log_file] [master_log_pos]
#if [ ! -e ~/.snapshot.sql ]; then
if [ ! -e ~/mysql-snapshot.tar.gz ]; then
echo "Usage: excute master-server's 'replicate-master.sh' script"
exit 1
elif [ -z $1 ] || [ -z $2 ]; then
echo "Usage: $0 [master_log_file] [master_log_pos]" 1>&2
exit 1
fi
# setting zone : start
ROOT_ID=[입력]
ROOT_PASSWORD=[입력]
TARGET_DB=[입력] #ex) exampledb
TARGET_PATH=[입력] #ex) /var/lib/mysql/exampledb
MASTER_IP=[입력]
MASTER_ID=[입력] #ex) master_user
MASTER_PASSWORD=[입력] #ex) master_password
# setting zone : end
MASTER_LOG_FILE=$1
MASTER_LOG_POS=$2
mysqladmin -u$ROOT_ID -p$ROOT_PASSWORD stop-slave
#mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB < ~/.snapshot.sql
sudo rm -rf $TARGET_PATH
sudo tar xvfPz ~/mysql-snapshot.tar.gz
#rm .snapshot.sql
rm mysql-snapshot.tar.gz
mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB -e "CHANGE MASTER TO MASTER_HOST='$MASTER_IP', MASTER_USER='$MASTER_ID', MASTER_PASSWORD='$MASTER_PASSWORD', MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS"
mysqladmin -u$ROOT_ID -p$ROOT_PASSWORD start-slave
mysql -u$ROOT_ID -p$ROOT_PASSWORD $TARGET_DB -e "SHOW SLAVE STATUS \G[/code]
적용하기
master, slave mysql 리스타팅 한뒤master에서 실행
[code]./replicate-master.sh[/code]
File, Position이 나오면
slave 에서 실행
[code]./replicate-slave.sh [File] [Position][/code]
Posted by 엽기민원
