Daily Archives: September 25, 2013

Home »  2013 »  September »  25th (Wednesday)

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 | […]