MySQL 主主複製

標籤：

 

MySQL 主主複製搭建

 設定bin-log，並配置對方為自己的Master

 

1. 搭建

1.1 Master1配置為Master2的主

Master配置(my.cnf)

[mysqld]server_id=1
log-bin=/usr/local/mysql-5.6.16/log/mysql-bin
skip-slave-start # 啟動時不啟動slave的複製進程
auto_increment_offset=0
auto_increment_increment=2 
replicate-do-db=test
replicate-ignore-db=mysql

啟動Master1

./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.6.16/my.cnf --user=mysql &

建立用於複製的使用者

mysql > grant replication slave on *.* to ‘rep1‘@‘192.168.80.102‘ identified by ‘rep1‘;

備份資料並還原到Master2伺服器
-- 設定讀鎖，資料無法更新

mysql > flush tables with read lock;

-- 查看開始複製的位置

mysql > show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      120 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+

-- 傳輸資料

tar -czf - data/ | ssh 192.168.80.102 "tar -xzf - -C /usr/local/mysql-5.6.16"

-- 解除鎖定

mysql > unlock tables;

Master2配置(my.cnf)

[mysqld]server_id=2      # Master和Slave之間唯一
log-bin=/usr/local/mysql-5.6.16/log/mysql-bin
skip-slave-start # 啟動時不啟動slave的複製進程
auto_increment_offset=1
auto_increment_increment=2
replicate-do-db=test
replicate-ignore-db=mysql

啟動Master2

./bin/mysqld_safe --defaults-file=/usr/local/mysql-5.6.16/my.cnf --user=mysql &

配置複製參數

mysql > change master to    -> master_host=‘192.168.80.101‘,    -> master_port=3306,    -> master_user=‘rep1‘,    -> master_password=‘rep1‘,    -> master_log_file=‘mysql-bin.000001‘,    -> master_log_pos=120;

啟動Master2的IO和SQL線程

mysql > start slave;

檢查

mysql > show processlist \G

應出現以下資訊:
State: Waiting for master to send event
State: Slave has read all relay log; waiting for the slave I/O thread to update it

或

mysql > show slave status \G

應出現以下資訊:
Slave_IO_State: Waiting for master to send event
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

1.2 Master2配置為Master1的主
對Master2執行show master status操作並對Master1執行change master to操作，檢查。

 

MySQL 主主複製