MYsql主主循环同步

1、MYSQL的MASTER到MASTER的主主循环同步刚刚抽空做了一下MYSQL的主主同步。把步骤写下来，至于会出现的什么问题，以后随时更新。这里我同步的数据库是TEST1、环境描述。主机：192.168.0.231(A)主机：192.168.0.232(B)MYSQL版本为5.1.212、授权用户。A：mysqlgrantreplicationslave,fileon*.*torepl1192.168.0.232identifiedby123456;QueryOK,0rowsaffected(0.00sec)mysqlflushprivileges;QueryOK,0rowsaffected(

2、0.00sec)B：mysqlgrantreplicationslave,fileon*.*torepl2192.168.0.231identifiedby123456;QueryOK,0rowsaffected(0.00sec)mysqlflushprivileges;QueryOK,0rowsaffected(0.00sec)然后都停止MYSQL服务器。3、配置文件。在两个机器上的f里面都开启二进制日志。A：user=mysqllog-bin=mysql-binserver-id=1binlog-do-db=testbinlog-ignore-db=mysqlreplicate-do-db

3、=testreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increment_increment=2auto_increment_offset=1B：user=mysqllog-bin=mysql-binserver-id=2binlog-do-db=testbinlog-ignore-db=mysqlreplicate-do-db=testreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_bin

4、log=1auto_increment_increment=2auto_increment_offset=2至于这些参数的说明具体看手册。红色的部分非常重要，如果一个MASTER挂掉的话，另外一个马上接管。紫红色的部分指的是服务器频繁的刷新日志。这个保证了在其中一台挂掉的话，日志刷新到另外一台。从而保证了数据的同步。4、重新启动MYSQL服务器。在A和B上执行相同的步骤rootlocalhost#/usr/local/mysql/bin/mysqld_safe&14264rootlocalhost#07121314:53:20mysqld_safeLoggingto/usr/local/mys

5、ql/data/localhost.localdomain.err./usr/local/mysql/bin/mysqld_safe:line366:-eq:unaryoperatorexpected07121314:53:20mysqld_safeStartingmysqlddaemonwithdatabasesfrom/usr/local/mysql/data5、进入MYSQL的SHELL。A：mysqlflushtableswithreadlockGQueryOK,0rowsaffected(0.00sec)mysqlshowmasterstatusGFile:mysql-bin.000

6、007Position:528Binlog_Do_DB:testBinlog_Ignore_DB:mysql1rowinset(0.00sec)B：mysqlflushtableswithreadlock;QueryOK,0rowsaffected(0.00sec)mysqlshowmasterstatusGFile:mysql-bin.000004Position:595Binlog_Do_DB:testBinlog_Ignore_DB:mysql1rowinset(0.00sec)然后备份自己的数据，保持两个机器的数据一致。方法很多。完了后看下一步。6、在各自机器上执行CHANGEMAST

7、ERTO命令。A：mysqlchangemasterto-master_host=192.168.0.232,-master_user=repl2,-master_password=123456,-master_log_file=mysql-bin.000004,-master_log_pos=595;QueryOK,0rowsaffected(0.01sec)mysqlstartslave;QueryOK,0rowsaffected(0.00sec)B：mysqlchangemasterto-master_host=192.168.0.231,-master_user=repl1,-mast

8、er_password=123456,-master_log_file=mysql-bin.000007,-master_log_pos=528;QueryOK,0rowsaffected(0.01sec)mysqlstartslave;QueryOK,0rowsaffected(0.00sec)7、查看各自机器上的10进程和SLAVE进程是否都开启。A：mysqlshowprocesslistGId:2User:replHost:192.168.0.232:54475db:NULLCommand:BinlogDumpTime:1590State:Hassentallbinlogtoslave

9、;waitingforbinlogtobeupdatedInfo:NULLId:3User:systemuserHost:db:NULLCommand:ConnectTime:1350State:WaitingformastertosendeventInfo:NULL*3.row*Id:4User:systemuserHost:db:NULLCommand:ConnectTime:1149State:Hasreadallrelaylog;waitingfortheslaveI/OthreadtoupdateitInfo:NULLId:5User:rootHost:localhostdb:tes

10、tCommand:QueryTime:0State:NULLInfo:showprocesslist4rowsinset(0.00sec)B：mysqlshowprocesslistGId:1User:systemuserHost:db:NULLCommand:ConnectTime:2130State:WaitingformastertosendeventInfo:NULLId:2User:systemuserHost:db:NULLCommand:ConnectTime:1223State:Hasreadallrelaylog;waitingfortheslaveI/Othreadtoup

11、dateitInfo:NULL*3.row*Id:4User:rootHost:localhostdb:testCommand:QueryTime:0State:NULLInfo:showprocesslistId:5User:repl2Host:192.168.0.231:50718db:NULLCommand:BinlogDumpTime:1398State:Hassentallbinlogtoslave;waitingforbinlogtobeupdatedInfo:NULL4rowsinset(0.00sec)如果红色部分没有出现，检查DATA目录下的错误文件。8、释放掉各自的锁，然后

12、进行插数据测试。mysqlunlocktables;QueryOK,0rowsaffected(0.00sec)插入之前两个机器表的对比：A：mysqlshowtables;+|Tables_in_test|+|t11_innodb|t22|+B：mysqlshowtables;TOC o 1-5 h z+|Tables_in_test|+|t11_innodb|t22|+从A机器上进行插入A：mysqlcreatetablet11_replicas-(idintnotnullauto_incrementprimarykey,-strvarchar(255)notnull)enginemyis

13、am;QueryOK,0rowsaffected(0.01sec)mysqlinsertintot11_replicas(str)values-(Thisisamastertomastertesttable);QueryOK,1rowaffected(0.01sec)mysqlshowtables;TOC o 1-5 h z+|Tables_in_test|+|t11_innodb|t11_replicas|t22|+3rowsinset(0.00sec)mysqlselect*fromt11_replicas;+|id|str|+|1|Thisisamastertomastertesttab

14、le|+1rowinset(0.00sec)现在来看B机器：mysqlshowtables;+|Tables_in_test|t11_innodb|t11_replicas|t22|TOC o 1-5 h z+3rowsinset(0.00sec)mysqlselect*fromt11_replicas;+|id|str|+|1|Thisisamastertomastertesttable|+1rowinset(0.00sec)现在反过来从B机器上插入数据:B：mysqlinsertintot11_replicas(str)values(Thisisatest2);QueryOK,1rowaffected(0.00sec)mysqlselect*fromt