MySQL主從復制基本要義和總結

本文主要給大家介紹MySQL主從復制基本要義和總結，希望可以給大家補充和更新些知識，如有其它問題需要了解的可以持續在億速云行業資訊里面關注我的更新文章的。

1.1 MySQL主從復制介紹

MySQL數據庫的主從復制方案，和使用scp/rsync等命令進行的文件級別復制類似，都是數據的遠程傳輸，只不過MySQL的主從復制是其自帶的功能，無需借助第三方工具，而且，MySQL的主從復制并不是數據庫磁盤上的文件直接拷貝，而是通過邏輯的binlog日志復制到要同步的云服務器本地，然后由本地的線程讀取日志里面的SQL語句重新應用到MySQL數據庫中。

MySQL數據庫支持單向、雙向、鏈式級聯、環狀等不同業務場景的復制。在復制過程中，一臺服務器充當主服務器（Master），接收來自用戶的內容更新，而一個或多個其他的服務器充當從服務器（Slave），接收來自主服務器binlog文件的日志內容，解析出SQL重新更新到從服務器，使得主從服務器達到一致。

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

1.2 MySQL主從復制的企業應用場景

MySQL主從復制集群功能使得MySQL數據庫支持大規模高并發讀寫成為可能，同時有效地保護了物理服務器宕機場景的數據備份。

應用場景一：從服務器作為主服務器的實時數據備份

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

這類似NFS存儲數據通過inotify+rsync同步到備份的NFS服務器，只不過MySQL的復制方案是其自帶的工具

利用MySQL的復制功能做數據備份時，在硬件故障、軟件故障的場景下，該數據備份是有效的，但對于人為地執行drop、delete等語句刪除數據的情況，從庫的備份功能就沒用了，因為從服務器也會執行刪除的語句。

應用場景二：主從服務器實現讀寫分離，從服務器實現負載均衡

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

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

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

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操作。

下面針對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

[root@db02~]# egrep -i "server-id|log-bin" /data/3306/my.cnf

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

server-id= 6

重啟服務

/data/3306/mysqlrestart

從庫

[root@db02~]# 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

主庫全備

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

[root@db02 ~]#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.從庫導入全備的數據

[root@db02scripts]# cd /data/backup/

[root@db02backup]# gzip -d all_2017-06-28.sql.gz

[root@db02backup]# mysql -S /data/3307/mysql.sock<all_2017-06-28.sql

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

[root@db02backup]# 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

顯示如下就說明成功

[root@db02backup]# 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)

看了以上關于MySQL主從復制基本要義和總結，希望能給大家在實際運用中帶來一定的幫助。本文由于篇幅有限，難免會有不足和需要補充的地方，如有需要更加專業的解答，可在官網聯系我們的24小時售前售后，隨時幫您解答問題的。