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

환경 : Windows7 + Mysql5.5(Innodb)

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

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 나와야 정상

출처: https://applejara.tistory.com/556?category=83070 [애플자라]