mysql主主複製

0.環境介紹

設計的叢集節點為master1、master2、slave1。其中master2到slave1的主從複製已經完成。現在需要配置master1和master2之間的相互複製。

1. 修改master2參數檔案（/etc/my.cnf）增加/修改如下參數：

log-bin=mysql-bin

log-bin-index=mysql-bin.index

server-id = 114

binlog-do-db=mytest_db

relay-log=relay-bin

relay-log-index=relay-bin-index

replicate-do-db=mytest_db

log_slave_updates=1

auto_increment_increment=2

auto_increment_offset=1

2. 重啟master2

# service mysqld restart

3. 修改master1參數

log-bin-index=mysql-bin.index #新增

server-id = 113 #修改

binlog-do-db=mytest_db #新增

relay-log=relay-bin #新增

relay-log-index=relay-bin-index #新增

replicate-do-db=mytest_db #新增

log_slave_updates=1 #新增

auto_increment_increment=2 #新增

auto_increment_offset=2 #新增

#auto-increment-increment= 2 #刪除

#auto-increment-offset= 2 #刪除

4. 重啟master1

# service mysqld restart

5. 在master1添加一個使用者 repl 並指定replication許可權

create user 'repl'@'master2_IP' identified by 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'master2_IP';

6. 產生master1備份資料

#鎖定表

mysql> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.00 sec)

#查看狀態

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000021 | 5337 | mytest_db | |

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

1 row in set (0.00 sec)

#匯出資料

[root@localhost ~]# mysqldump -p mytest_db > /usr/software/tianyc/mytest_db_master.dump

Enter password: 

[root@localhost ~]#

#解鎖表

mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

7. 在master2上還原資料

#拷貝檔案到master2，執行匯入

[root@localhost tianyc]# mysql -p mytest_db < /usr/software/tianyc/mytest_db_master.dump

Enter password: 

[root@localhost tianyc]#

8. 在master2上設定它的主庫為master1，設定binlog位置，並開啟複製。

mysql> change master to master_host='master1_IP',master_port=3306,master_user='repl',master_password='password',master_log_file='mysql-bin.000021',master_log_pos=5337;

Query OK, 0 rows affected (0.05 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master1_IP

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000021

Read_Master_Log_Pos: 6036

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 952

Relay_Master_Log_File: mysql-bin.000021

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: mytest_db

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: 6036

Relay_Log_Space: 1102

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: 

Replicate_Ignore_Server_Ids: 

Master_Server_Id: 113

1 row in set (0.00 sec)

9. 此時可驗證master1到master2的複製過程。在master1上建表，可以傳遞給master2，master2又產生binlog傳遞給slave1

10. 採用同樣的方式，建立master2到master1的映射。

#在master2上建立複製使用者repl，並授權給master1使用。

create user 'repl'@'master1_IP' identified by 'password';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'master1_IP';

#查看master2的master狀態：

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000006 | 610578 | mytest_db | |

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

1 row in set (0.00 sec)

#此時master1和master2資料庫一致，且master2不做更改，所以省去了master2到master1同步資料的過程。

#直接在master1上設定它的主節點：

mysql> change master to master_host='master2_IP',master_port=3306,master_user='repl',master_password='password',master_log_file='mysql-bin.000006',master_log_pos=610578;

Query OK, 0 rows affected (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: master2_IP

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 611282

Relay_Log_File: 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: mytest_db

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: 611282

Relay_Log_Space: 403

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: 

Replicate_Ignore_Server_Ids: 

Master_Server_Id: 114

1 row in set (0.00 sec)

11. 再將maste1、master2、slave1用amoeba整合成叢集，測試叢集複製情況。