MySQL主從複製

標籤：mysql主從複製

1.1 MySQL主從複製介紹

MySQL資料庫的主從複製方案，和使用scp/rsync等命令進行的檔案層級複製類似，都是資料的遠程傳輸，只不過MySQL的主從複製是其內建的功能，無需藉助第三方工具，而且，MySQL的主從複製並不是資料庫磁碟上的檔案直接拷貝，而是通過邏輯的binlog日誌複製到要同步的伺服器本地，然後由本地的線程讀取日誌裡面的SQL語句重新應用到MySQL資料庫中。

 

MySQL資料庫支援單向、雙向、鏈式級聯、環狀等不同業務情境的複製。在複製過程中，一台伺服器充當主伺服器（Master），接收來自使用者的內容更新，而一個或多個其他的伺服器充當從伺服器（Slave），接收來自主伺服器binlog檔案的日誌內容，解析出SQL重新更新到從伺服器，使得主從伺服器達到一致。

 

如果設定了鏈式級複製，那麼，從（slave）伺服器本身除了充當從伺服器外，也會同時充當其下面從伺服器的主伺服器。鏈式級聯複製類似A-->B-->C的複製形式。

                            650) this.width=650;" width="544" height="382" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

1.2 MySQL主從複製的公司專屬應用程式情境

MySQL主從複製叢集功能使得MySQL資料庫支援大規模高並發讀寫成為可能，同時有效地保護了物理伺服器宕機情境的資料備份。

 

應用情境一：從伺服器作為主伺服器的即時資料備份

主從伺服器架構的設定，可以大大加強MySQL資料庫結構描述的強壯性。例如：當主伺服器出現問題時，我們可以人工或設定自動切換到從伺服器繼續提供服務，此時從伺服器的資料和宕機時的主要資料庫幾乎是一致的。

這類似NFS儲存資料通過inotify+rsync同步到備份的NFS伺服器，只不過MySQL的複製方案是其內建的工具

利用MySQL的複製功能做資料備份時，在硬體故障、軟體故障的情境下，該資料備份是有效，但對於人為地執行drop、delete等語句刪除資料的情況，從庫的備份功能就沒用了，因為從伺服器也會執行刪除的語句。

 

應用情境二：主從伺服器實現讀寫分離，從伺服器實現負載平衡

主從伺服器架構可通過程式（PHP、java等）或代理軟體（mysql-proxy、Amoeba）實現對使用者（用戶端）的請求讀寫分離，即讓從伺服器僅僅處理使用者的select查詢請求，降低使用者查詢回應時間及讀寫同時在主伺服器上帶來的訪問壓力。對於更新的資料（例如uodate、insert、delete語句）仍然交給主伺服器處理，確保主伺服器和從伺服器保持即時同步。

650) this.width=650;" width="414" height="373" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

 

應用情境三：把多個從伺服器根據業務重要性進行拆分訪問

可以把幾個不同的從伺服器，根據公司的業務進行拆分。例如：有為外部使用者提供查詢服務的從伺服器，有內部DBA用來資料備份的從伺服器，還有為公司內部人員提供訪問的後台、指令碼、日誌分析及供開發人員查詢使用的從伺服器。這樣的拆分除了減輕主伺服器的壓力外，還可以使資料庫對外部使用者瀏覽、內部使用者業務處理及DBA人員的備份等互不影響。

650) this.width=650;" width="472" height="309" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

 

1.3 MySQL主從複製原理介紹

MySQL的主從複製是一個非同步複製過程（雖然一般情況下感覺是即時的），資料將從一個MySQL資料庫（我們稱之為Master）複製到另一個MySQL資料庫（我們稱之為Slave），在Master與Slave之間實現整個主從複製的過程是由三個線程參與完成的。其中有兩個線程（SQL線程和IO線程）在Slave端，另外一個線程（I/O線程）在Master端。

 

要實現MySQL的主從複製，首先必須要開啟Master端的Binlog記錄功能，否則就無法實現。因為整個複製過程實際上就是Slave從Master端擷取Binlog日誌，然後在Slave上以相同順序執行擷取的Binlog日誌中所記錄的各種SQL操作。

650) this.width=650;" width="698" height="300" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

 

下面針對MySQL主從複製原理的重點進行小結。

◆   主從複製是非同步邏輯的SQL語句級的複製。

◆   複製時，主庫有一個I/O線程，從庫有兩個線程，I/O和SQL線程。

◆   實現主從複製的必要條件是主庫要開啟記錄binlog功能。

◆   作為複製的所有MySQL節點的server-id都不能相同。

◆   binlog檔案只記錄對資料有更改的SQL語句（來自主要資料庫內容的變更），不記錄任何查詢（select，show）語句。

 

忘了資料庫密碼

mysqld_safe--defaults-file=/data/3306/my.cnf --skip-grant-table --user=mysql &

然後不用輸入密碼進行登入

mysql-uroot -p -S /data/3306/mysql.sock

進入資料庫後設定密碼

updatemysql.user set password=password(‘oldboy123‘) where user=‘root‘ andhost=‘localhost‘;

重新整理許可權

flushprivileges;

 

 

 

 

 

 

 

 

1.4 MySQL主從複製操作步驟

架構實踐:

3306---->3307

主--->從

 

1.開啟主庫binlog,配置server-id

[[email protected]~]# egrep -i "server-id|log-bin" /data/3306/my.cnf

log-bin= /data/3306/mysql-bin

server-id= 6

重啟服務

/data/3306/mysqlrestart

從庫

[[email protected]~]# egrep -i "server-id|log-bin" /data/3307/my.cnf

#log-bin= /data/3307/mysql-bin

server-id= 7

 

2.主庫建立使用者

grantreplication slave on *.* to ‘rep‘@‘172.16.1.%‘ identified by ‘oldboy123‘;

mysql>grant replication slave on *.* to ‘rep‘@‘172.16.1.%‘ identified by ‘oldboy123‘;

Query OK, 0rows affected (0.04 sec)

 

mysql>select user,host from mysql.user;

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

| user |host       |

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

| root |127.0.0.1  |

| rep  | 172.16.1.% |

 

3.從主庫匯出資料

按照我們講過的內容,直接取今天00點的備份就可以.

先鎖表flush table with read lock;

mysql>flush table with read lock;

Query OK, 0rows affected (0.00 sec)

 

mysql> showmaster status;

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

| File             | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |

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

|mysql-bin.000001 |      120 |              |                  |                   |

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

1 row in set(0.00 sec)

mysqldump

cp/tar

xtrabackup

拿到位置點是關鍵[A1]  sed -n ‘22p‘all_2017-06-28.sql

 

主庫全備

[[email protected] ~]# mysqldump -B --master-data=2 --single-transaction-S /data/3306/mysql.sock  -A|gzip>/data/backup/all_$(date+%F).sql.gz

[[email protected] ~]#ls -l /data/backup/

總用量 228

-rw-r--r-- 1root  root  178468 6月  28 11:11 all_2017-06-28.sql.gz

 

主庫解鎖:

mysql> unlock table;

Query OK, 0rows affected (0.00 sec)

 

 

4.從庫匯入全備的資料

[[email protected]]# cd /data/backup/

[[email protected]]# gzip -d all_2017-06-28.sql.gz

[[email protected]]# mysql -S /data/3307/mysql.sock<all_2017-06-28.sql

 

5.找位置點,然後change master從庫

[[email protected]]# sed -n ‘22p‘ all_2017-06-28.sql

-- CHANGEMASTER TO MASTER_LOG_FILE=‘mysql-bin.000001‘,MASTER_LOG_POS=120;

 

change master從庫

 

CHANGE MASTER TO 

MASTER_HOST=‘172.16.1.52‘,

MASTER_PORT=3306,

MASTER_USER=‘rep‘,

MASTER_PASSWORD=‘oldboy123‘,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=120;

開啟slave

mysql> start slave;

Query OK, 0rows affected (0.03 sec)

 

mysql> show slave status\G

顯示如下就說明成功

[[email protected]]# mysql -S /data/3307/mysql.sock -e"show slave status\G"|egrep "_Running|Behind_Master"|head-3

             Slave_IO_Running:Yes

             Slave_SQL_Running:Yes

             Seconds_Behind_Master:0

 

 

3306查看管理的主機

mysql> show slave hosts;

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

| Server_id |Host | Port | Master_id | Slave_UUID                           |

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

|         7 |      | 3307 |         6 |295750c8-54c1-11e7-80dd-000c29fc02ee |

|         8 |      | 3308 |         6 |328e8c80-54c1-11e7-80dd-000c29fc02ee |

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

2 rows in set(0.00 sec)

 

 

 

 

 [A1]

本文出自 “李松陽” 部落格，請務必保留此出處http://lsy666.blog.51cto.com/11729318/1942931

MySQL主從複製