mysql主從同步搭建

MySQL主從復制簡介

MySQL Replication (MySQL 主從復制) 是指數據可以從一個MySQL數據庫服務器主節點復制到一個或多個從節點。MySQL 默認采用異步復制方式（一級主從大概50~100 us），這樣從節點不用一直訪問主服務器來更新自己的數據，數據的更新可以在遠程連接上進行，從節點可以復制主數據庫中的所有數據庫或者特定的數據庫，或者特定的表。

MySQL主從復制原理

MySQL主從復制涉及到三個線程，一個運行在主節點（log dump thread），其余兩個(I/O thread, SQL thread)運行在從節點

主節點 binary log dump 線程

當從節點連接主節點時，主節點會創建一個log dump 線程，用于發送bin-log的內容。在讀取bin-log中的操作時，此線程會對主節點上的bin-log加鎖，當讀取完成，甚至在發動給從節點之前，鎖會被釋放。

從節點 I/O 線程

當從節點上執行start slave命令之后，從節點會創建一個I/O線程用來連接主節點，請求主庫中更新的bin-log。I/O線程接收到主節點binlog dump 進程發來的更新之后，保存在本地relay-log中。

從節點 SQL 線程

SQL線程負責讀取relay log中的內容，解析成具體的操作并執行，最終保證主從數據的一致性。

對于每一個主從連接，都需要三個進程來完成。當主節點有多個從節點時，主節點會為每一個當前連接的從節點建一個binary log dump 進程，而每個從節點都有自己的I/O進程，SQL進程。從節點用兩個線程將從主庫拉取更新和執行分成獨立的任務，這樣在執行同步數據任務的時候，不會降低讀操作的性能。比如，如果從節點沒有運行，此時I/O進程可以很快從主節點獲取更新，盡管SQL進程還沒有執行。如果在SQL進程執行之前從節點服務停止，至少I/O進程已經從主節點拉取到了最新的變更并且保存在本地relay日志中，當服務再次起來之后，就可以完成數據的同步。

要實施復制，首先必須打開Master 端的binary log（bin-log）功能，否則無法實現。

整個復制過程實際上就是Slave 從Master 端獲取該日志然后再在自己身上完全順序的執行日志中所記錄的各種操作。如下圖所示： 

復制的基本過程如下：

從節點上的I/O 進程連接主節點，并請求從指定日志文件的指定位置（或者從最開始的日志）之后的日志內容；主節點接收到來自從節點的I/O請求后，通過負責復制的I/O進程根據請求信息讀取指定日志指定位置之后的日志信息，返回給從節點。返回信息中除了日志所包含的信息之外，還包括本次返回的信息的bin-log file 的以及bin-log position；從節點的I/O進程接收到內容后，將接收到的日志內容更新到本機的relay log中，并將讀取到的binary log文件名和位置保存到master-info 文件中，以便在下一次讀取的時候能夠清楚的告訴Master“我需要從某個bin-log 的哪個位置開始往后的日志內容，請發給我”；Slave 的 SQL線程檢測到relay-log 中新增加了內容后，會將relay-log的內容解析成在祝節點上實際執行過的操作，并在本數據庫中執行。

MySQL主從同步配置的基本步驟

有很多種配置主從同步的方法，可以總結為如下的步驟：

在主服務器上，必須開啟二進制日志機制和配置一個獨立的ID，創建一個用來專門復制主服務器數據的賬號

在每一個從服務器上，配置一個唯一的ID

在開始復制進程前，在主服務器上記錄二進制文件的位置信息

如果在開始復制之前，數據庫中已經有數據，就必須先創建一個數據快照（可以使用mysqldump導出數據庫，或者直接復制數據文件），備份主服務器原有數據到從服務器

配置從服務器要連接的主服務器的IP地址和登陸授權，二進制日志文件名和位置

MySQL主從復制搭建

實驗環境：centos 7.9

主服務器：10.0.0.13 從服務器：10.0.0.14

數據庫文件：mysqlmbuq43458_db.sql

Master-Server 配置

1、修改主服務器配置文件my.cnf，開啟binlog ，每臺服務器設置不同的 server-id

vim /etc/mysql/my.cnf

[mysqld]

#配置唯一的server-id，不設置MySQL5.7以上會報錯

server-id=1

#mysql會根據這個配置自動設置log_bin為on狀態，即開啟binlog

log_bin=master-bin

#配置log_bin_index文件為你指定的文件名后跟.index獲得master二進制文件名及位置

#log_bin_index=master-bin.index

#需要同步的數據庫，除此之外，其他不同步

#binlog-do-db=test

#默認情況下備份是主庫的全部操作都會備份到從庫，實際可能需要忽略某些庫，可以在主庫中增加如下配置：

# 不同步哪些數據庫(多個寫多行)

#binlog-ignore-db=mysql

#binlog-ignore-db=information_schema

#binlog-ignore-db=performance_schema

#binlog-ignore-db=sys

# 只同步哪些數據庫，除此之外，其他不同步

#binlog-do-db=mysql

#自動清理 7 天前的log文件,可根據需要修改

#expire_logs_days=7

2、重啟并進入數據庫，創建用于數據同步的賬戶（目的，讓從服務器來復制數據）

# 查看主數據庫是否開啟二進制日志

# # 查看 Master-Server中的binlog File 文件名稱和 Position值位置，并且記下來 mysql-bin.000001 |       643

mysql> show master logs;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED BY '614811';

mysql> flush privileges;

Slave-Server 配置

1、修改配置文件my.cnf

[mysqld]

#配置唯一的server-id，不設置MySQL5.7以上會報錯，不能與主庫相同

server-id=2

#保證了主從數據的一致性，不論從機怎么出錯都能保證，主從一致。

relay_log_recovery        = on

relay_log_info_repository = TABLE

master_info_repository    = TABLE

#定義relay_log的位置和名稱

#relay-log=slave-relay-bin

#同bin-log-index

#relay-log-index=slave-relay-bin.index

#需要同步的數據庫名（多數據庫使用逗號，隔開）

#replicate-do-db=db_name 

#設定需要復制的表

#replicate-do-table=table_name

#設定需要忽略的復制表

#replicate-ignore-table=table_name

2、重啟并進入數據庫

要設置從庫與主庫進行通信復制，使用必要的連接信息配置從庫在從庫上執行以下代碼

# 查看change master to 使用格式

mysql> help change master to

CHANGE MASTER TO

  MASTER_HOST='10.0.0.13',

  MASTER_USER='repluser',

  MASTER_PASSWORD='614811',

  MASTER_PORT=3306,

  MASTER_LOG_FILE='mysql-bin.000001',

  MASTER_LOG_POS=154;

# 啟用從服務器復制

mysql> start slave;

#查看復制狀態命令,\\G表示換行查看

mysql> show slave status \\G;

檢查主從復制通信狀態

Slave_IO_State  #從站的當前狀態

Slave_IO_Running： Yes #讀取主程序二進制日志的I/O線程是否正在運行

Slave_SQL_Running： Yes #執行讀取主服務器中二進制日志事件的SQL線程是否正在運行。與I/O線程一樣

Seconds_Behind_Master #是否為0，0就是已經同步了

測試主從復制

創建數據庫 創建表 導入數據 測試成功同步