MySQL Replication 깨졌을때 복구는 어떻게 하나요?

mysql을  master-slave 방식으로 replication해서 사용하고 있습니다.

만약 리플리케이션이 깨졌을때 어떻게 복구해야 하나요?

Default Asked on 2015년 7월 11일 in 데이터베이스.
Add Comment
1 Answer(s)

마스터서버가 A

슬래이브서버가 B라고 가정했을 때

1. Maser 서버의 전체 MySQL 데이터 덤프

#/usr/local/mysql/bin/mysqladmin -uroot -p flush-logs
#/usr/local/mysql/bin/mysqladmin -uroot -p -C "flush tables read lock"
#/usr/local/mysql/bin/mysqldump --single-transaction --all-databases --extended-insert=FALSE -c -uroot -p -R > all-databases.sql

2. Slave 서버에 모든 데이타 베이스 삭제
(아예 mysql 저장경로의 모든 파일을 삭제한후 mysql-install-db 명령으로 초기화하는 것이 좋음)

3. dump된 파일로 B서버 restore

Slave 서버에 MySQL Replication 설정이 되어 있는지 확인

/etc/my.cnf 파일에

server-id = 2
#
# The replication master for this slave - required
master-host = 61.100.5.12
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user = <계정>
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = <비밀번호>

위 내용이 있는지 확인

Slave 서버의 MySQL 시작

시작 후 Replication 중지

mysql> stop slave;
mysql> reset slave;

복사한 Dump파일 Restore

mysql -uroot -p < all-databases.sql

Replication 시작

mysql> start slave;

Replicatrion 확인

mysql> show slave status;
mysql> mysql> show slave status;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Slave_IO_State                   | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
| Waiting for master to send event | 61.100.5.12 | repl        |        3306 |            60 | mysql-bin.000097 |              631426 | hsmdb2-relay-bin.000198 |        510603 | mysql-bin.000097      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |              631426 |          510603 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+
1 row in set (0.00 sec)

Waiting for master to send event가 나오면 성공 다른 메시지가 나오면 위 과정 처음부터 다시 진행

Master 서버 Table Lock 해제

UNLOCK TABLES;
리플리케이션 성공

Default Answered on 2015년 7월 12일.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.