MYSQL REPLICATION 동기화 설정

http://juhyunsik.blogspot.kr/2012/05/mysql-replication.html

http://sangchul.kr/429

http://linuxism.tistory.com/846

http://yes.imhappyo.com/385

http://oops.org/?t=lecture&sb=mysql&n=1

http://cloudless.tistory.com/73

http://blog.hibrainapps.net/130

blnlog_do_db = DB1명    ※ 생략시 전체 DB 리플리케이션함

blnlog_do_db = DB2명

* 참고(binlog_do_db/replicate-do-db 차이)

- binlog_do_db : Master 기준 어떤 DB를 Replication 허용할 것인지 설정(제외조건 binlog_ignore_db)

- replicate-do-db : Slave 기준 어떤 DB를 Replication 할 것인지 설정(제외조건 replicate-ignore-db)

Master 설정

[root@www ~]# vi /etc/my.cnf

[mysqld]

server-id=3 중복안됨

log-bin=test-bin 이름설정

binlog-ignore-db=mysql 제외

binlog-do-db=test 허용

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@hisct mysql]# systemctl restart mysqld.service

[root@www ~]# ll /var/lib/mysql/                파일 생성 확인

test-bin.000001

test-bin.index

…..

슬레이브용 사용자 생성

[root@hisct mysql]# mysql -u root -p

mysql> grant all privileges on *.* to 'slave'@'%' identified by 'password' with grant option;

데이터베이스 백업

mysql-> mysql> flush tables with read lock;

shell-> [root@hisct mysql]# mysqldump -u root -ppassword --databases test > test.sql

mysql-> mysql> show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+----------+--------------+------------------+-------------------+

| test-bin.000001 |      1477 | test   | mysql            |                   |

+----------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

mysql-> mysql> unlock tables;

slave 설정

DB 복구 [root@hisct mysql]# mysql -uroot -ppassword < test.sql

[root@www ~]# vi /etc/my.cnf

[mysqld]

server-id=12 중복안됨

master-host=x.x.x.x

master-port=3306

master-user=slave

master-password=password

replicate-do-db=test 허용

replicate-ignore-db=mysql 제외

 [root@hisct mysql]# systemctl restart mysqld.service

mysql> stop slave;

mysql> change master to

master_host = 'x.x.x.x',

master_user = 'user',

master_password = 'password',

master_port = 3306,

master_log_file = 'test-bin.00001' master의 File 정보

master_log_pos = 1477, master의 Position 정보

master_connect_retry = 10;

mysql> flush privileges;

mysql> start slave;