Master-Master互为主备
1:按照主从配置步骤将MasterB配置成MasterA的从库;
2:确保MasterB没有写入,通过show master status命令在MasterB上得到其同步点,再将MasterA配置成MasterB的从库。
通常,为了简化逻辑,其中一个Master会设置为只读,正常只通过另外一个Master进行读写。 若要两边都写,为了避免自增id冲突,一般会设置奇偶错开,即一台的自增ID均为奇数,另一台均为偶数。
这里假设你已经按照我第一篇文章做了主从。
配置实例:
1:在Master B(Slave)的配置文件中添加
port = 3307
log_bin = /var/lib/mysql/mysql-binlog
binlog_do_db = testSM
2:在MasterA(Master)的配置文件中添加
prot=3306
master-host = 10.4.5.9
master-user = gechong1
master-password = gechong1
master-port = 3307
master-connect-retry = 5
replicate-do-db = testSM
3:在Master B(Slave)上创建复制账号gechong1
GRANT REPLICATION SLAVE ON *.* TO gechong1@'%' IDENTIFIED BY 'gechong1';
4:两边都重启服务登陆数据库
在Master A(Master)上查看
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000002 | 106 | test | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 10.4.5.9
Master_User: gechong1
Master_Port: 3306
Connect_Retry: 5
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mysqld-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 0
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master 'gechong1@10.4.5.9:3306' - retry-time: 5 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
在MasterB(Slave)上查看
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.14.168
Master_User: gechong
Master_Port: 3306
Connect_Retry: 5
Master_Log_File: mysql-binlog.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000006
Relay_Log_Pos: 254
Relay_Master_Log_File: mysql-binlog.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 106
Relay_Log_Space: 558
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:
1 row in set (0.00 sec)
mysql> show master status;
+---------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 | 327 | | |
+---------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
可以看到 Master A:Slave_IO_Running: No
可以参照第一篇文档,重新配置参数即可。