MySQL MASTER-MASTER REPLICATION 이중화 요령

MySQL MASTER-MASTER REPLICATION 이중화 요령

setup REPLICATION : 아래가 제일 심플한 예제 소개처. 오탈자가 많은 게 흠.
http://www.billrun.net/blog/31-mysql-master-master-replication.html

[서버 A에 설정할 내용/순서]

We will add the next config snippet to the pre-installed configuration (under [mysqld] section):
# sudo nano /etc/my.cnf
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 1

# sudo service mysqld restart

CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
SHOW MASTER STATUS;

[서버 B에 설정할 내용/순서]

Let's add the MySQL configuration snippet to declare the replication settings (under [mysqld] section)
# sudo nano /etc/my.cnf
#replica settings
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
auto-increment-increment = 2
auto-increment-offset = 2

# sudo service mysqld restart

CREATE USER 'replicator'@'%' IDENTIFIED BY 'rep_pass';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST = '192.168.1.188',
    MASTER_USER = 'replicator',
    MASTER_PASSWORD = 'rep_pass',
    MASTER_LOG_FILE = 'mysql-bin.000006',
    MASTER_LOG_POS = 343;

SLAVE START;

SHOW MASTER STATUS;

[다시금 서버 A에 설정할 내용/순서]

#after B setup, from A server.

SLAVE STOP;

CHANGE MASTER TO MASTER_HOST= '127.0.0.1',
    MASTER_USER = 'replicator',
     MASTER_PASSWORD = 'rep_pass',
     MASTER_LOG_FILE = 'mysql-bin.000004',
     MASTER_LOG_POS = 343;

SLAVE START;

이로써 A=>B로의 DB 동기화가 가능해 진다.

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

clear REPLICATION : 복제 설정을 해제하고 싶은 경우
http://www.ctkn.net/tag/delete-mysql-replication/

mysql> STOP SLAVE; CHANGE MASTER TO MASTER_HOST=''; RESET SLAVE;

mysql> use mysql;
mysql> delete from user where User="replicator";
mysql> flush privileges;

Exit MySQL prompt and open up /etc/my.cnf – remove any reference to replication (eg: replicate-* master-* etc)
# vim /etc/my.cnf

Lastly, restart your MySQL instance:
# /etc/init.d/mysqld restart

댓글 쓰기

CAPTCHA
스팸방지를 위해 아래에 보이는 영문/숫자를 입력해 주세요.
Image CAPTCHA
Enter the characters shown in the image.