mysql replication
1. OS : Centos 5.8 64 bit
2. 구성
> mysql 설치는 마음것 설치하시길~
> my.cnf 설정
>reblication 의 핵심은 중복되지 않은 서버 ID 와 bin로그를 기록하게 해야 됨
master slave
log-bin=mysql-bin server-id = 10
server-id = 1 relay-log=slave1-relay-bin
  skip_slave_start
  read_only
  slave-skip-errors = all
> 원격지 접속 권한부여 (master 에만해당)
INSERT INTO mysql.user (host,user,password) VALUES (‘192.168.1.%’,’root’,password(‘password’));
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.1.%’;
flush privileges;
> 확인
| root | 192.168.1.% | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
> replication 설정 (슬레이브전체)  
usr/local/mysql/bin/mysql -u root -p -h 192.168.1.101 // 마스터쪽으로 접속되는지먼저 확인  
   
slave stop;  
   
change master to master_host=’192.168.1.101′, master_user=’root’, master_password=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=4;
// 최초 구성이기 때문에 첫번째 로그파일에서 첫번째 POS 지점으로 연결함
slave start;  
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 590
Relay_Log_File: slave1-relay-bin.000004
Relay_Log_Pos: 736
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
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: 590
Relay_Log_Space: 1036222
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)
ERROR:
No query specified
> master 쪽에서 최종확인
[MMM_1, /usr/local/mysql/data >/usr/local/mysql/bin/mysqladmin -u root -p proc stat -i1
Enter password:
+—-+——+———————+—-+————-+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+——+———————+—-+————-+——+———————————————————————–+——————+
| 9 | root | 192.168.1.102:49151 | | Binlog Dump | 52 | Master has sent all binlog to slave; waiting for binlog to be updated | |
| 10 | root | 192.168.1.103:60589 | | Binlog Dump | 52 | Master has sent all binlog to slave; waiting for binlog to be updated | |
| 11 | root | 192.168.1.104:49238 | | Binlog Dump | 51 | Master has sent all binlog to slave; waiting for binlog to be updated | |
| 14 | root | localhost | | Query | 0 | | show processlist |
+—-+——+———————+—-+————-+——+———————————————————————–+——————+
Uptime: 1328 Threads: 4 Questions: 38 Slow queries: 0 Opens: 33 Flush tables: 1 Open tables: 26 Queries per second avg: 0.028
> 끝~ 참쉽조잉~

Leave your Comment

Your email address will not be published. Required fields are marked *