[MYSQL]MySQL 5.5.x Replication(MySQL 5.6 이중화)

Database 2016.09.20 14:42 Posted by 애플자라

환경 : Windows7 + Mysql5.5(Innodb)


출처 -  

http://kit2013.tistory.com/157

https://wiki.kldp.org/wiki.php/MySQL%B8%AE%C7%C3%B8%AE%C4%C9%C0%CC%BC%C7


------------------------------------------------------------------


Step4. Create a User for Replication


GRANT ALL PRIVILEGES ON *.* TO 'assetrepl'@'MYSQL_SVR1_IP' IDENTIFIED BY PASSWORD '*B59E756B80B88541E7DAD5E9BE8DB018E9AA93E1' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'assetrepl'@'%' IDENTIFIED BY PASSWORD '*B59E756B80B88541E7DAD5E9BE8DB018E9AA93E1' WITH GRANT OPTION;



mysql> CREATE USER 'assetrepl'@'%' IDENTIFIED BY '패스워드';

mysql> CREATE USER 'assetrepl'@'localhost' IDENTIFIED BY '패스워드';

mysql> CREATE USER 'assetrepl'@'MYSQL_SVR1_IP' IDENTIFIED BY '패스워드';

mysql> GRANT REPLICATION SLAVE ON *.* TO 'assetrepl'@'MYSQL_SVR1_IP';


FLUSH PRIVILEGES;



Step5. Obtaining the Replication Master Binary Log Coordinates / MASTER 실행

mysql> SHOW MASTER STATUS;


mysql> FLUSH TABLES WITH READ LOCK;


mysql> UNLOCK TABLES;





Step6. Start Replication! / SLAVE 실행


mysql> CHANGE MASTER TO

MASTER_HOST = 'MYSQL_SVR2_IP',

MASTER_USER = 'assetrepl',

MASTER_PASSWORD = '패스워드',

MASTER_PORT = 13306,

MASTER_LOG_FILE = 'mysql-bin.000003',

MASTER_LOG_POS = 1325 ,

MASTER_CONNECT_RETRY = 5;



mysql > START SLAVE;


// 정지할땐 mysql> stop slave;

// slave의 경우는 셋팅이 된 이후 mysqld 가 시작될때 자동으로 같이 동작 됨.

// 자동으로 동작되는 slave를 동작하지 않게 하기 위해서는 option을 주면 됨.



mysql> stop slave;

mysql> CHANGE MASTER TO MASTER_HOST='';

mysql> RESET SLAVE;



mysql > SHOW SLAVE STATUS \G;



Start Replication! / MASTER 실행 확인!!

mysql > show slave hosts;


mysql> show processlist; ;


해보시기 바랍니다. 

이때... 

State: Slave connection: wating for binlog update 나와야 정상

댓글을 달아 주세요