MySQL 基於主主備份

標籤：mysql

在兩個伺服器上都要開啟二進位日誌和中繼日誌

如果兩個伺服器的資料庫id啟動自動成長功能，要在設定檔中設定一個的id為奇數，另外一個的id為偶數，或者一個的id為偶數，另外一個的id為奇數，因為如果不這樣設定，有可能兩個伺服器上同時都有使用者在寫的時候會產生相同的id號造成衝突，所以一般情況下不建議資料庫的id號啟用自動成長功能，或者用id產生器進行產生。

 

一、環境說明：

IP	主機名稱	資料庫名	串連使用者	密碼
192.168.47.179	Mariadb01	retail	server01	server01
192.168.47.178	Mariadb02	retail	server02	server02

 

二、搭建步驟

2.1 建立資料的串連使用者

Mariadb01上面建立串連使用者server01，並且只能通過192.168.47.178進行串連；

指令碼：mariadb[none]> GRANT REPLICATION SLAVE ON*.* TO‘server01‘@‘192.168.47.178‘ IDENTIFIED BY ‘server01‘;

Mariadb02上面建立串連使用者server02，並且只能通過192.168.47.149進行串連；

指令碼：mariadb[none]> GRANT REPLICATION SLAVE ON*.* TO‘server02‘@‘192.168.47.179‘IDENTIFIED BY ‘server02‘;

mariadb[none]> select user,password,host from mysql.user;  ---查看一下是否有授權的使用者用於主從複製

 

2.2 修改mysql的參數檔案

修改Mariadb01的參數檔案，在MySQL的主設定檔預設為/etc/my.cnf，修改/添加如下內容

[mysql]

 skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
log_bin = bin-log  ---開啟二級制日誌
relay_log = relay-log  ---開啟中繼日誌
server_id = 1
auto_increment_offset = 1  ---表示id號從1開始
auto_increment_increment = 2 ---表示以2為步進，即為奇數

修改之後，重啟msyql生效：servicemysqld restart

修改Mariadb02的參數檔案，在MySQL的主設定檔預設為/etc/my.cnf，修改/添加如下內容

[mysql]
skip_name_resolve = on
innodb_file_per_table = on
max_connections = 20000
server_id = 2
relay_log = relay-log
log_bin = bin-log
auto_increment_offset = 2
auto_increment_increment = 2

參數說明：

a、server_id資料庫的進行資料同步的時候用於標識該語句最初是從哪個server寫入的，在進行主主或主從的搭建中，都需要填寫；

b、auto_increment_increment：在資料庫應用，我們經常要用到唯一編號，以標識記錄。在MySQL中可通過資料列的AUTO_INCREMENT屬性來自動產生。為避免兩台主要資料庫產生的編碼重複了，所以需要設定該值

2.3複製其中的一台伺服器的資料庫到另外一台伺服器

因為環境是全新搭建的，所以兩個的環境都是一樣的，並需要進行資料的初始化工作；

需要的話可以通過以下步驟進行操作，以Mariadb01為來源資料庫進行同步，如下操作：

2.3.1.鎖定Mariadb01資料庫，查看狀態

mariadb[none]> FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected(0.00 sec)

 

mariadb[none]> SHOW MASTER STATUS;

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

| File | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000008 | 107| | |

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

1 row in set (0.00 sec)

顯示來源資料庫處於8號binlog的107位置；

2.3.2.備份資料庫

[[email protected]ariadb01~]# mysqldump--all-databases --lock-tables --flush-logs> /tmp/retail.sql

[[email protected]ariadb01 ~]# scp /tmp/retail.sql 192.168.47.178:/tmp/

2.3.3.在mysql02暫停二進位日誌, 進行匯入操作；

mariadb[none]< set @@session.sql_log_bin=0;

[[email protected] ~]# < /tmp/retail.sql#匯入retail資料庫

mariadb[none]< set @@session.sql_log_bin=1;

mariadb[none]> FLUSH TABLES WITH READ LOCK;

2.4.鎖定mysql02資料庫並查看狀態

 

mysql> SHOW MASTERSTATUS;

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

| File | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000009 | 107| | |

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

1 row in set (0.00 sec)

顯示備份檔案位於9號binlog的107位置；

 

2.5 進行主主的通訊串連；

a、在Mariadb01上面進行使用者和binlog的確認

mysql> CHANGE MASTERTOMASTER_HOST=‘192.168.47.178‘,MASTER_USER=‘server02‘,MASTER_PASSWORD=‘server02‘,MASTER_LOG_FILE=‘mysql-bin.000008‘,MASTER_LOG_POS=107;

mysql>start slave;

mysql> showslave status\G

b、在Mariadb02上面進行使用者和binlog的確認

mysql> CHANGE MASTERTOMASTER_HOST=‘192.168.47.179‘,MASTER_USER=‘server01‘,MASTER_PASSWORD=‘server01‘,MASTER_LOG_FILE=‘mysql-bin.000009‘,MASTER_LOG_POS=107;

mysql>start slave;

mysql> showslave status\G

主要這兩項：

            Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

 

分別解鎖Mariadb01、Mariadb02資料庫

mariadb[none]> UNLOCK TABLES;

測試

在mysql01寫入在mysql02上有顯示

在mysql02寫入在mysql01上有顯示

複製時應該注意的問題：

1、從服務設定為“唯讀”：在從伺服器啟動read_only，但僅對非SUPER許可權的使用者有效；

要想阻止所有使用者可以申請一個全域讀鎖：mysql>FLUSH TABLES WITH READ LOCK;

2、盡量確保複製時的事務安全

在master節點啟用參數：sync_binlog=ON，表示只要當前節點有事物提交時就立即從記憶體緩衝區儲存到二進位日誌中，避免從伺服器複製時二進位日誌中沒有這個提交操作主伺服器就壞了，這樣從伺服器就不知道這個事物該不該提交了

如果用到的是InnoDB儲存引擎：

innodb_flush_logs_at_trx_commit=ON  ---每當事物提交時就同步到事物日誌中

innodb_support_xa=ON    讓innodb支援分布式事物

3、從伺服器意外中止時盡量避免自動啟動複製線程，假如從伺服器在複製一個事件的時候覆制到一半的時候從伺服器意外終止了，如果重新啟動，複製功能實是會自動開啟的，因為在/var/lib/mysql/master.info檔案中記錄了串連到主伺服器的資訊，所以啟動mariadb服務時會自動啟動複製線程，這樣就會造成問題，因為終止前的事物複製到一半，不知道該不該提交，為了避免這種事情發生，我們要把網斷掉，查看一下是否有複製到一半的事物，如果有手動刪除，然後手動加changemaster to 指向意外終止時主伺服器二進位日誌的位置，或者重新備份恢複後啟動複製功能

4、從節點：設定參數

sync_master_info=ON

sync_relay_log_info=ON

 

本文出自 “13147015” 部落格，請務必保留此出處http://13157015.blog.51cto.com/13147015/1981101

MySQL 基於主主備份