[draft] MySQL MHA 원복

MySQL MHA 원복(mha failback)

마스터 서버

슬레이브 호스트 목록 확인

mysql -h localhost -uroot -p'mysqlpassword' -e "SHOW SLAVE HOSTS"

$ mysql -h localhost -uroot -p'mysqlpassword' -e "SHOW SLAVE HOSTS"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         3 |      | 3306 |         2 | 3ebc580f-f8f7-11ed-adcf-080027704bff |
+-----------+------+------+-----------+--------------------------------------+

bin-log(File) 파일 및 Position 확인

mysql -h localhost -uroot -p'mysqlpassword' -e "show master status\G" | egrep 'File|Position'

$ mysql -h localhost -uroot -p'mysqlpassword' -e "show master status\G" | egrep 'File|Position'
mysql: [Warning] Using a password on the command line interface can be insecure.
             File: mysql-bin.000009
         Position: 455

슬레이브 서버

슬레이브 복제 설정

mysql -h localhost -uroot -p'mysqlpassword' -e "
CHANGE MASTER TO
MASTER_HOST='192.168.56.102',
MASTER_USER='replication_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000009',
MASTER_LOG_POS=455;
"

$ mysql -h localhost -uroot -p'mysqlpassword' -e "
> CHANGE MASTER TO
> MASTER_HOST='192.168.56.102',
> MASTER_USER='replication_user',
> MASTER_PASSWORD='password',
> MASTER_LOG_FILE='mysql-bin.000009',
> MASTER_LOG_POS=455;
> "
mysql: [Warning] Using a password on the command line interface can be insecure.

슬레이브 서버 다시 시작

mysql -h localhost -uroot -p'mysqlpassword' -e "start slave"

$ mysql -h localhost -uroot -p'mysqlpassword' -e "start slave"
mysql: [Warning] Using a password on the command line interface can be insecure.

슬레이브 상태 확인

mysql -hlocalhost -uroot -p'mysqlpassword' -e "show slave status\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master'

$ mysql -hlocalhost -uroot -p'mysqlpassword' -e "show slave status\G" | egrep 'Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master'
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

마스터 서버

슬레이브 호스트 확인

mysql -h localhost -uroot -p'mysqlpassword' -e "SHOW SLAVE HOSTS"

$ mysql -h localhost -uroot -p'mysqlpassword' -e "SHOW SLAVE HOSTS"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         1 |      | 3306 |         2 | 3ef5caf4-f619-11ed-96e6-080027704bff |
|         3 |      | 3306 |         2 | 3ebc580f-f8f7-11ed-adcf-080027704bff |
+-----------+------+------+-----------+--------------------------------------+