MySQL双主机双Master方案测试
必须避免系统中任何一处出现单点故障。?
?
如果主MySQL所在的物理机器出现硬件故障，就是一个单点故障，虽然可以迅速的用一台从属机(Slave)升级为主机（Master). 但是也会比较慢。 最好的方法还是用双Master方案避免出现单点故障。
?
此试验预计花费时间 2天。?
?
?
需要解决下面二个问题
?
1.实现二台主数据库的循环同步,即Ａ库有变化,Ｂ库同步;同理Ｂ库有变化,Ａ库同步;
?
2.当Ａ库发生故障后,及时报警,同时Ｂ库替换为Ａ库为主库,与所有slave服务器通讯正常;
?
?
准备工作
因为服务器Ａ及服务器Ｂ互为主从,所以要分别为其建立同步帐号,都授予REPLIATION SLAVE权限.Ａ:mysql>grant replication slave on *.* to 'replicate'@'192.168.0.8' identified by '123456';mysql>flush privileges;B:mysql>grant replication slave on *.* to 'replicate'@'192.168.0.219' identified by '123456';mysql>flush privileges;如果不行直接进phpmyadmin去创建也一样;测试一下.Ａ：mysql -h192.168.0.8 -ureplicate -p123456 能进入mysql> OK!!B:mysql -h192.168.0.219 -ureplicate -p123456 能进入mysql> OK!!?
修改配置文件my.cnf
A:[mysqld] server-id = 1 log-bin = mysql-bin binlog-do-db = test-xf binlog-ignore-db = mysql #主-主需要多添加的部分 replicate-do-db = test-xf replicate-ignore-db = mysql,information_schema log-slave-updates #如果一个master挂掉,另一个马上接管 #下面3句,服务器频繁的刷新日志,这个保证了在其中一台挂掉的话,日志刷新到另外一台,从而保证了数据的同步. sync-binlog = 1 auto_increment_offset = 1 auto_increment_increment = 2重启mysql服务: sudo /etc/init.d/mysql restart查看master状态mysql> show master status\G;*************************** 1. row *************************** File: mysql-bin.000006 Position: 977 Binlog_Do_DB: test-xfBinlog_Ignore_DB: mysql1 row in set (0.00 sec)ERROR: No query specifiedB:[mysqld] server-id = 2 log-bin = mysql-bin binlog-do-db = test-xf binlog-ignore-db = mysql #主-主需要多添加的部分 replicate-do-db = test-xf replicate-ignore-db = mysql,information_schema log-slave-updates #如果一个master挂掉,另一个马上接管 #下面3句,服务器频繁的刷新日志,这个保证了在其中一台挂掉的话,日志刷新到另外一台,从而保证了数据的同步. sync-binlog = 1 auto_increment_offset = 2 auto_increment_increment = 2重启mysql服务: sudo /usr/local/etc/rc.d/mysql-server restart查看master状态:mysql> show master status\G;*************************** 1. row *************************** File: mysql-bin.000005 Position: 348 Binlog_Do_DB: test-xfBinlog_Ignore_DB: mysql1 row in set (0.00 sec)ERROR: No query specified
?
指定同步位置
?
A:mysql> change master to -> master_host = '192.168.0.8', -> master_user = 'replicate', -> master_password = '123456', -> master_log_file = 'mysql-bin.000005', -> master_log_pos = 348;Query OK, 0 rows affected (0.44 sec)B:mysql> change master to -> master_host = '192.168.0.219', -> master_user = 'replicate', -> master_password = '123456', -> master_log_file = 'mysql-bin.000006', -> master_log_pos = 977;Query OK, 0 rows affected (0.05 sec)
?
?
?
重启A,B服务器上的从服务线程
A:mysql> start slave;Query OK, 0 rows affected (0.03 sec)mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.8 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000005 Read_Master_Log_Pos: 348 Relay_Log_File: xiaofei-desktop-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test-xf Replicate_Ignore_DB: mysql,information_schema 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: 107 Relay_Log_Space: 419 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: 0Master_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: 21 row in set (0.01 sec)ERROR: No query specifiedB:mysql> start slave;Query OK, 0 rows affected (0.02 sec)mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.219 Master_User: replicate Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000006 Read_Master_Log_Pos: 977 Relay_Log_File: queen-relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000006 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test-xf Replicate_Ignore_DB: mysql,information_schema 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: 107 Relay_Log_Space: 409 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: 0Master_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: 11 row in set (0.00 sec)ERROR: No query specified二台服务器的： Slave_IO_Running: Yes Slave_SQL_Running: Yes均为Yes表示启动成功;
?
?
开始测试数据
??
???
主从复制如何提高可靠性
写道主从单向复制，从服务器只是实时的保存了主服务器的一个副本。当主服务器发生故障时，可以切换到从服务器继续做查询，但不能更新。?
?
?
带从服务器的MySql主主复制
http://www.litvip.com/2011/06/29/326
?
http://www.cnblogs.com/czh-liyu/archive/2012/06/01/2530482.html
