mysql mysql 主从配置
liangkaiping0 2011-07-13
一、先修改服务器的配置文件 1、Master服务器配置简单，修改my.cnf为: server-id = 1 log-bin=n01-bin relay-log=ns01-relay-bin set-variable=binlog-ignore-db=mysql 2、slave1的配置加入 master-host = 172.16.20.135 master-user = rep master-password = cnrep master-port = 3306 server-id = 2 log-bin=n01-bin replicate-do-db =mydnsreplicate-ignore-db=mysql log-slave-updates slave-net-timeout=60 master-connect-retry=10relay-log=ns01-relay-bin log-slave-updates read-only 3、slave2服务器的配置 master-host = 172.16.20.3 master-user = rep1 master-password = cnrep master-port = 3306 server-id = 3 log-bin=n01-bin replicate-do-db =mydnsreplicate-ignore-db=mysql log-slave-updates slave-net-timeout=60 master-connect-retry=10relay-log=ns01-relay-bin log-slave-updates read-only 二、重启master数据库 三、然后锁定master数据库的表： FLUSH TABLES WITH READ LOCK; 四、在master数据库中添加用于slave1同步的用户，并赋予相关权限： GRANT REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’; GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep@sa_cfengine1 IDENTIFIED BY ‘cnrep’; 五、在slave1数据库中添加用于slave2同步的用户，并赋予相关权限： GRANT REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’; GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO rep1@sa_cfengine2 IDENTIFIED BY ‘cnrep’; 六、同步数据库： 方法很多，可以打包之后scp，再解压，由于sa_cfengine1到mysql master服务器通道打通了，切sa_cfengine2到sa_cfengine1通道也打了，故直接scp整个数据库目录即可。 注意：此时要注意删除同步过来的日志文件，最好把与数据库无关的文件全删除（可以将非目录的文件全删了）。 七、重启salve1的mysql，起来之后锁定表 八、重启slave2的mysql，然后先后给slave1和master服务器的mysql表解锁 UNLOCK TABLES； 九、分别登录slave1和slave2的mysql，查看同步状态： SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.20.135 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000051 Read_Master_Log_Pos: 13856842 Relay_Log_File: sa_cfengine1-relay-bin.000013 Relay_Log_Pos: 624419 Relay_Master_Log_File: mysql-bin.000051 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: AliSMS,lcd,loginmanager,samis,sareport,syslog,web_speed Replicate_Ignore_DB: mysql,mysql 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: 13856842 Relay_Log_Space: 624419 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.01 sec) 注意标注为红色的地方，两个都是yes说明一切正常，否则要检查原因，可以看error log查找原因后做相应的处理。 十、测试： 在master数据库中update在同步列表中的一个表的一个字段，如果slave服务器的做相应改变，则测试用过。 实例： 67： mysql> show master status \G; ***************************1.row*************************** File:n01-bin.000001 Position:327 Binlog_Do_DB: Binlog_Ignore_DB: 1rowinset(0.00sec) ERROR: No query specified66 mysql> show slave status \G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:192.168.1.221 Master_User:reply Master_Port:3306 Connect_Retry:60 Master_Log_File:n01-bin.000001 Read_Master_Log_Pos:327 Relay_Log_File:no02-relay-bin.000039 Relay_Log_Pos:233 Relay_Master_Log_File:n01-bin.000001 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB:mydns,mydns,mydns 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:327 Relay_Log_Space:233 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 1rowinset(0.00sec) ERROR: No query specified.5 (既做67的从，有做10的主) mysql> show master status; +----------------+----------+--------------+------------------+ |File|Position|Binlog_Do_DB|Binlog_Ignore_DB| +----------------+----------+--------------+------------------+ |n01-bin.000004|220||| +----------------+----------+--------------+------------------+ 1rowinset(0.00sec) mysql>showslavestatus\G; ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:10.0.250.67 Master_User:reply Master_Port:3306 Connect_Retry:60 Master_Log_File:n01-bin.000001 Read_Master_Log_Pos:327 Relay_Log_File:ns01-relay-bin.000006 Relay_Log_Pos:355 Relay_Master_Log_File:n01-bin.000001 Slave_IO_Running:Yes Slave_SQL_Running:Yes Replicate_Do_DB:mydns,mydns,mydns 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:327 Relay_Log_Space:355 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)10. CHANGE MASTER TO MASTER_LOG_FILE='n01-bin.000004', MASTER_LOG_POS=220; |
