MySQL Replication 설정하기

MySQL Replication 설정하기

1. DB Dump

- database 선택 백업

/usr/local/mysql/bin/mysqldump -h127.0.0.1 -u 계정 --databases database명 database명 --master-data=2 --add-drop-table --add-drop-database --routines --no-autocommit=1 --single-transaction=1 --extended-insert=1 --disable-keys --quick > ./dump_20131223.sql

- 전체

/usr/local/mysql/bin/mysqldump -h127.0.0.1 -u 계정 --master-data=2 --add-drop-table --add-drop-database --routines --no-autocommit=1 --single-transaction=1 --extended-insert=1 --disable-keys --quick > ./dump_20131223.sql

2. 파일 Copy

scp -P 21887 /backup/45_dump_data.sql 계정@서버:/backup

3. Dump Restore

/usr/local/mysql/bin/mysql -h127.0.0.1 -u 계정 -p < /backup/45_dump_data.sql

4. my.cnf 수정  (Replication)

5. MySql 재시작

/usr/local/mysql/share/mysql/mysql.server restart

6. Replication 설정

- position 확인

head –n30 /backup/45_dump_data.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000093', MASTER_LOG_POS=709881085;

-위의 log file명과 log pos 값을 확인하여 replication 설정시 사용

- replication 설정

/usr/local/mysql/bin/mysql -h127.0.0.1 -u계정 -p

mysql> change master to master_host=‘서버아이피’,master_user='계정’, master_password=‘암호’,master_log_file=‘mysql-bin.000040’,master_log_pos=766559555;

mysql> start slave;

7. Replication 확인

mysql> show slave status;

mysql> show master status;