25th
Sep
master
No Comments
| 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 | |
| > 끝~ 참쉽조잉~ | |