mysql replication

Master 서버 10.0.0.1

mysql -uroot -p

FLUSH TABLES WITH READ LOCK;
reset master;
SHOW MASTER STATUS;

file과 position 기록
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000001 | 107 | | |
+——————+———-+————–+——————+

mkdir /root/db
cd /root/db

echo “show databases” | mysql -uroot -p | grep -v “Database”|grep -v “test”|grep -v _schema > /root/db/dblist
for i in `cat dblist`;do mysqldump -uroot -p –max-allowed-packet=167772160000 –single-transaction $i > /root/db/$i.sql;done

mysql -uroot -p

UNLOCK TABLES;

###################

Node 서버 10.0.0.2
rsync -avP 10.0.0.1::ROOT/root/db/ db
cd db
for i in `cat dblist|grep -v mysql`;do echo create database $i\ >> createdb.txt ; done

cat createdb.txt

## DB 생성
mysql -uroot -p

## 복원

for i in `ls -1 *.sql`;do mysql -uroot -p –max-allowed-packet=167772160000 $i < $i.sql;done
mysql -uroot -p
stop slave;
reset slave;

CHANGE MASTER TO MASTER_HOST=’10.0.0.1′, MASTER_USER=’replicaman’, MASTER_PASSWORD=’[email protected]#’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=107;

mysql> SHOW SLAVE STATUS \G

답글 남기기