MySQL 基于主主備份

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

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

一、環境說明：

192.168,47.179 (ip)----mariadb01(主機名)-------server01（連接配接使用者）------server01（密碼）

192.168,47.178 (ip)----mariadb02(主機名)-------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

轉載于:https://blog.51cto.com/13157015/1981101