MySql主從同步

標籤：主從同步   讀寫分離   

    在談到mysql主從同步前，我們先來瞭解下普通檔案的資料同步。

          普通檔案的資料同步

 1.NFS網路檔案分享權限設定可以同步儲存資料

 2.samba共用--windows平台

 3.定時任務或守護進程結合rsync，scp

 4.inotify+rsync即時同步

 5.ftp資料同步

 6.svn

 ......

                mysql同步

      mysql有其內建的同步功能，mysql同步不是磁碟上檔案直接同步處理。

 mysql支援單向、雙向、鏈式級聯、事實、非同步複製。在複製過程中，一台伺服器充當主伺服器Master，而一個或者多個其他伺服器充當為從伺服器Slave。

複製可以是單向：M==>S,也可以是雙向;M<==>M,當然也可以多從多M環狀同步等。

 如果設定了鏈式級聯複製，那麼，從伺服器本身除了充當從伺服器外，也會同時充當其下面從伺服器的主伺服器

 鏈式級聯複製：A-->B-->C-->D的複製形式

 在當前的生產工作中，大多數應用的mysql主從同步都是非同步複製方式，即不是嚴格即時的資料同步。

 生產環境授權

 主庫使用者：grant select, insert, update,delete on 'xx'.* to '使用者'@'網段' identified by '使用者密碼';

 從庫：grant select on 'xx'.* to '使用者'@'網段' identified by '使用者密碼';

 可以結合read-only參數共同做

 如何?上述授權方案，最簡單的辦法是在主庫配置binlog-ignore-db=mysql

 當配置好主從同步後，所有資料庫內容更新必須在主伺服器上進行，避免使用者對主伺服器的資料庫內容的更新與對從伺服器上資料更新不一致而導致衝突

 如何確保使用者在主伺服器上更新呢？

   1.防止從庫寫資料方法

       採取忽略授權表方式的同步，然後對從伺服器上的使用者僅授權select讀許可權。不同步mysql庫

  

   2.防止從庫寫資料方法

     除了select授權外，在slave伺服器啟動選項增加參數或者在my.cnf設定檔中加read-only保證從庫唯讀，兩者同時操作效果更佳

應用情境

1.主從伺服器互為備份

2.主從伺服器讀寫分離分擔網站壓力

3.根據伺服器拆分業務獨立分擔壓力

在企業生產環境中，通常採取讀寫分離策略，即主庫負責寫操作，從庫負責讀操作。一般從庫有多台，主庫可以採用高可用手段實現故障自動切換，如採用heartbeat+drbd。如果網站讀的壓力比較大，還可以利用負載平衡，分擔從程式庫伺服器讀的壓力。

         mysql讀寫分離方法

1.通過程式實現讀寫分離（效能、效率最佳），如php，java程式

2.通過軟體實現，如mysql-proxy amoeba等一些代理軟體也可以實現讀寫分離，但最常用最好用的還是程式實現讀寫分離。

   

      mysql主從複製原理

mysql主從複製是一個非同步複製過程（一般情況下感覺是即時同步），資料從master複製到slave。這個過程是由三個線程參與完成的，兩個線程（SQL線程和IO線程）在slave端，另一個線程（IO線程）在master端。slave端的IO線程負責與master端的線程打交道

 要實現mysql的主從複製，首先必須開啟master端的Binlog（mysql-bin.xxx）功能，否則無法實現主從複製。因為整個複製過程實際上是slave從master端擷取binlog日誌，然後在slave自身上以相同順序執行擷取的binlog日誌中記錄的操作。

 當使用者寫入資料到主庫中，主庫將這些sql語句（資料庫的更改語句）放到binlog中，從庫開啟複製後通過IO線程和主庫IO線程打交道，提供使用者名稱密碼，log檔案及位置資訊給主庫，主庫驗證通過後讀取binlog資訊（根據從庫IO縣線程需求）返回給從庫IO，位置資訊儲存在master-info中，sql放到relay-log（中繼日誌）中，從庫通過sql線程按照順序執行sql，從庫IO線程繼續讀取 master-info資訊，然後與主庫IO互動。

          mysql主從複製實踐

1.定義伺服器角色

 主庫（mysql master）：ip 192.168.132.10 連接埠：3306

從庫（mysql slave）： ip 192.168.132.20 連接埠： 3306

2.資料庫環境準備

實踐環境以兩台虛擬機器為實踐對象，進行單向主從複製。

虛擬機器均安裝mysql 5.1.72版本且資料庫已啟動

3.主庫上執行操作

設定server-id值並且開啟binlog參數

vim /etc/my.cnf

開啟log-bin

[mysqld]

server-id       = 1

 

# Uncomment the following if you want to log updates

log-bin=mysql-bin

由於虛擬機器mysql安裝路徑為/usr/local/mysql，資料路徑為/usr/local/mysql/data,即記錄檔在該路徑下。

server-id不能一樣

提示：

1.    server-id與log-bin必須放在mysqld模組裡

2.   server-id的值使用伺服器ip地址的最後8位如10目的是避免不同機器或執行個體重複（不適合多執行個體）

3.   現在設定檔中尋找相關參數，不存在時再添加，參數不要重複

4.   修改設定檔中需要重啟資料庫

檢查是否生效:

[[email protected] data]# mysql -uroot -p'123456' -e "show variables like 'log_bin';"

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

| Variable_name | Value |

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

| log_bin       | ON    |

4.建立用於從庫複製的帳號

登陸主庫mysql

grant replication slave on *.* to [email protected]'192.168.132.%' identified by 'rep99';

重新整理許可權flush privileges;

檢查使用者

select user,host from mysql.user;

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

| user | host          |

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

| root | %             |

| root | 127.0.0.1     |

| rep  | 192.168.132.% |

| root | localhost     |

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

4 rows in set (0.00 sec)

5.對資料庫鎖表唯讀（當前視窗不要關掉）

因為要備份資料庫，鎖表後能保證資料匯出時一致

flush tables  with read lock;

目前狀態，即當前binlog記錄檔名和二進位binlog日誌位移量，後續從庫同步時需要用到。

show master status;

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000001 |     1336 |              |                  |

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

1 row in set (0.00 sec)

6.匯出Database Backup

開啟新的視窗，匯出資料庫資料，如果資料很大（100G+），並且允許停機，可以停庫直接打包資料檔案遷移

mysqldump  -uroot -p'123456' -A -B|gzip>/home/tuwei/new.sql.gz

-A表示備份所有庫 -B表示增加use DB和drop等（導庫時會直接覆蓋原有的）

7.  解鎖主庫

解鎖主庫，恢複可寫

 mysql> unlock table;

Query OK, 0 rows affected (0.00 sec)

8. 從庫執行操作

設定server-id值並關閉binlog參數

這裡將從庫server-id值設為2，log-bin注釋掉，注意要在mysqld模組裡設定

有兩種情況需要開啟binlog：

1.    級聯同步A->B->C中間的B就要開啟

2.   從庫做Database Backup，Database Backup需要全備及binlog

設定完後重啟資料庫

9.  匯入資料庫到從庫

將主庫上備份的資料庫檔案傳送到從庫上進行匯入，可以利用scp，sftp等命令進行傳送。

解壓資料庫檔案

gzip -d new.sql.gz

匯入資料庫

mysql -uroot -p'123456' <new.sql

10.   配置從庫同步參數

可以不用登陸資料庫裡面進行操作，快速執行change master語句（適合指令碼裡操作）

cat |mysql -uroot -p'123456' <<EOF

> CHANGE MASTER TO

> MASTER_HOST='192.168.132.10',

> MASTER_PORT=3306,

> MASTER_USER='rep',

> MASTER_PASSWORD='rep99',

> MASTER_LOG_FILE='mysql-bin.000001',

> MASTER_LOG_POS=1336;

> EOF

 

11. 啟動從庫同步開關

slave start;

然後查看從庫狀態

show slave status\G;

查看關鍵的三點

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0

表示同步完成

MySql主從同步