=============================================
master server 설정
my.cnf 추가
[mysqld]
log-bin=mysql-bin
server-id=1
mysql 접속
mysql -uroot -p
slave서버에서 접속하는 유저에게 권한부여
grant replication slave on *.* to '유저명'@'슬레이브IP' identified by '비번';
확인
select * from user where user='replication';
select * from user where user='replication'\G;
*************************** 2. row ***************************
Host: 211.115.127.228
User: replication
Password: *EAB9E57CC91EDA4B72ABCB63E05D2A8FD113BFE4
Select_priv: N
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Reload_priv: N
Shutdown_priv: N
Process_priv: N
File_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Show_db_priv: N
Super_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Execute_priv: N
Repl_slave_priv: Y
Repl_client_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Create_user_priv: N
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
2 rows in set (0.00 sec)
권한 리로드
flush privileges;
마스터 상태확인
show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000006 | 261 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql나가기
exit;
mysql재시작 한다.
/etc/init.d/mysqld restart
---------------------------------------------
slave server 설정
master의 디비를 백업하여 slave의 디비에 복원하여 똑같은 디비상태로 만든다.
my.cnf 추가
[mysqld]
log-bin=mysql-bin
server-id=2
master-host=마스터아이피주소
master-port=3306
master-user=유저명
master-password=비밀번호
replicate_do_db=디비명
마스터 정보변경 할 수도 있다.
mysql접속
mysql -uroot -p
slave stop;
CHANGE MASTER TO
MASTER_HOST='마스터IP',
MASTER_USER='유저명',
MASTER_PASSWORD='패스워드',
MASTER_LOG_FILE='mysql-bin.000027같은 로그파일명',
MASTER_LOG_POS=4;
start slave;
확인
show slave status;
show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 121.254.231.163
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 424
Relay_Log_File: mysqld-relay-bin.000013
Relay_Log_Pos: 561
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: replication
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: 424
Relay_Log_Space: 561
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
1 row in set (0.00 sec)
mysql나가기
exit;
mysql 재시작
/etc/init.d/mysqld restart
=============================================