mysql主主備份

標籤：mysql 主主複製

mysql主主配置和主從配置的不同點在於主從的master不監控slavebin-log的變化，導致slave有新資料不能更新到master

主主配置的關鍵參數：log-slave-updates,兩台mysql都需添加

測試環境：

centos 6.5 x64

主1ip:192.168.0.1

主2ip:192.168.0.2

主主配置

主1

vim /etc/my.cnf

server-id=1

bin-log=mysql-bin

binlog-format=mixed

log-slave-updates

relay-log=mysql-relay

主2

server-id=2

bin-log=mysql-bin

binlog-format=mixed

log-slave-updates

relay-log=mysql-relay

修改配置後均重啟mysql使配置生效

分別在主1和主2上做主從

一、給主1配置從

在主1上

1.授權登陸帳號(兩邊要建立可複製的帳號)

grant replication client,replication slave on *.* to ‘salve‘@‘192.168.0.*‘ identified by ‘test123‘;

2.show master status;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000002 |      653 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

在主2上

stop slave;

reset slave;

change master to master_host=‘192.168.0.1‘,master_user=‘slave‘,master_password=‘test123‘,master_log_file=‘mysql-bin.000002‘,master_log_pos=653;

start slave;

show salve status\G;

 Slave_IO_Running: Yes

 Slave_SQL_Running: Yes

即成功

一、給主2配置從

步驟通給主1配置從

測試：

分別在主1和主2添加資料，看在另一台是否能查詢

在主1上

mysql> select * from t;

+---+

| s |

+---+

| 2 |

+---+

1 row in set (0.00 sec)

mysql> insert into t values (3);

Query OK, 1 row affected (0.01 sec)

mysql> select * from t;         

+---+

| s |

+---+

| 2 |

| 3 |

+---+

2 rows in set (0.00 sec)

mysql> select * from t;

+---+

| s |

+---+

| 2 |

| 3 |

| 4 |

+---+

3 rows in set (0.00 sec)

在主2上

mysql> use proxy_test;

Database changed

mysql> select * from t;

+---+

| s |

+---+

| 2 |

+---+

1 row in set (0.00 sec)

mysql> select * from t;

+---+

| s |

+---+

| 2 |

| 3 |

+---+

2 rows in set (0.00 sec)

mysql> insert into t values (4);

Query OK, 1 row affected (0.00 sec)

mysql> select * from t;         

+---+

| s |

+---+

| 2 |

| 3 |

| 4 |

+---+

3 rows in set (0.00 sec)

主主配置到此測試成功

mysql主主備份