티스토리 뷰

my.cnf

1. replication용 DB 계정 생성( master/slave 둘다 )

 $ create user repl@'192.168.186.%' identified by 'repl';

 $ grant replication slave, replication client on *.* to repl@'192.168.186.%' identified by 'repl';

 

2. master 설정

1) master my.cnf 수정

[mysqld]

server-id    = 1 # master DB id for replication

....

########################################

# Replication related settings

########################################

replicate-ignore-db                            = perf_mon

replicate-ignore-db                            = sys

replicate-ignore-db                            = moniter

replicate-ignore-db                            = mysql

2) 먼저 master 의 binlog position 을 확인하고 기록해 둔다.

mysql> show master status \G

*************************** 1. row ***************************

File: mysql_bin.000002

Position: 120

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

3) master shutdown

$ service mysqld stop 또는 /etc/init.d/mysql stop

 

3. slave 설정

1) slave my.cnf 수정

[mysqld]

server-id                               = 2 # slave DB id  for replication

...

########################################

# Replication related settings

########################################

replicate-ignore-db                            = perf_mon

replicate-ignore-db                            = sys

replicate-ignore-db                            = moniter

replicate-ignore-db                            = mysql

#replicate-do-db                               = db_xxx

#

relay_log_purge                                 = OFF

relay-log                                       = /var/log/mysql/relay

#log_slave_update

read-only                                       = 1

report-host                                     = x.x.x.x

report-port                                     = 3306

2) db에 접속해서 slave 초기화

 

mysql> stop slave;

mysql> reset slave all;

3) 복제 설정

※ 기존 운영 중인 MySQL이라면 master에서 데이터를 dump 받아 slave에 넣는 작업이 필요 하다.

mysql> change master to master_host='xxx.xxx.xxx.xxx',

> master_user='repl',

> master_password='repl',

> master_log_file='mysql_bin.000002’,

> master_log_pos=120;

※ master_log_file 과 master_log_pos 는 master status의 binlog position 과 같다.

4) 복제 설정 확인

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State:

Master_Host: xxx.xxx.xxx.xxx

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 755

Relay_Log_File: testvm2-relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: No

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 755

Relay_Log_Space: 107

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 0

1 row in set (0.00 sec)

5) 복제 시작

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

6) 복제 정상 여부 확인

mysql> show slave status \G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.186.xxx

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000025

Read_Master_Log_Pos: 755

Relay_Log_File: testvm2-relay-bin.000002

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000025

Slave_IO_Running: Yes        <--------------------------   YES 여야 함

Slave_SQL_Running: Yes        <--------------------------   YES 여야 함

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 755

Relay_Log_Space: 411

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)


 

댓글
댓글쓰기 폼