MySQL 5.5 Replication
## Master : 계정 추가 ##
// Slave Server에서 Master Server에 접근할 수 있는 계정 필요
// user rep_admin
// slave ip 192.168.0.20
// password 123456
mysql> grant replication slave on *.* to 'rep_admin'@'192.168.0.20' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
## Master : DB 복사 ##
// Slave Server에 Master Server의 데이터를 백업한다.
// mysqldump등 이용… (알아서)
## Master : 설정 ##
// my.cnf 설정
[root@master] # vi /etc/my.cnf
server-id = 1 // 서버 식별 값
binlog-do-db = database_name // 복제할 DB
binlog-ignore-db= database_name // 복제하지 않을 DB
log-bin = mysql-master-bin // bin log file name
// my.cnf 재시작 및 확인
[root@master] # service mysqld restart
// 마스터 상태 확인
mysql> show master status;
+-------------------------+----------+------------------------+-------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------------+----------+------------------------+-------------------------+
| mysql-master-bin.000009 | 107 | backup_test,check_test | mysql,infomation_schema |
+-------------------------+----------+------------------------+-------------------------+
1 row in set (0.00 sec)
## Slave : DB 복제 ##
// Slave Server에 Master Server의 데이터를 복제한다.
// mysqldump등 이용… (알아서)
## Slave : 설정 ##
[root@slave etc]# service mysqld restart
mysqld 를 정지 중: [ OK ]
mysqld (을)를 시작 중: [ OK ]
[root@master] # vi /etc/my.cnf
server-id = 2
relay-log = slave-relay-bin
[root@slave etc]# service mysqld restart
mysqld 를 정지 중: [ OK ]
mysqld (을)를 시작 중: [ OK ]
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='192.168.0.15',
-> master_user='rep_admin',
-> master_password='123456',
-> master_log_file='mysql-master-bin.000009',
-> master_log_pos=107;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
// 상태 확인
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.0.15
Master_User: rep_admin
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000009
Read_Master_Log_Pos: 107
Relay_Log_File: slave-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-master-bin.000009
Slave_IO_Running: Connecting
Slave_SQL_Running: 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: 107
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: 1130
Last_IO_Error: error connecting to master 'rep_admin@192.168.0.15:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
/*
[참고] 끝에 \G를 하는 것과 하지 않은 것의 차이
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2015-01-20 23:04:16 |
+---------------------+
1 row in set (0.00 sec)
mysql> select now()\G;
*************************** 1. row ***************************
now(): 2015-01-20 23:04:24
1 row in set (0.00 sec)
*/
// 시작
mysql> start slave;
## Slave : 설정 초기화 ##
// MySQL 5.5부터 가능, 이전 버전에서는 stop slave; change to master master_host=‘';
mysql> stop slave; reset slave all;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
// 초기화 상태 확인
mysql> show slave status\G;
Empty set (0.00 sec)
