mysql主從同步搭建

MySQL主從復(fù)制簡介

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

MySQL主從復(fù)制原理

MySQL主從復(fù)制涉及到三個(gè)線程，一個(gè)運(yùn)行在主節(jié)點(diǎn)（log dump thread），其余兩個(gè)(I/O thread, SQL thread)運(yùn)行在從節(jié)點(diǎn)

主節(jié)點(diǎn) binary log dump 線程

當(dāng)從節(jié)點(diǎn)連接主節(jié)點(diǎn)時(shí)，主節(jié)點(diǎn)會創(chuàng)建一個(gè)log dump 線程，用于發(fā)送bin-log的內(nèi)容。在讀取bin-log中的操作時(shí)，此線程會對主節(jié)點(diǎn)上的bin-log加鎖，當(dāng)讀取完成，甚至在發(fā)動給從節(jié)點(diǎn)之前，鎖會被釋放。

從節(jié)點(diǎn) I/O 線程

當(dāng)從節(jié)點(diǎn)上執(zhí)行start slave命令之后，從節(jié)點(diǎn)會創(chuàng)建一個(gè)I/O線程用來連接主節(jié)點(diǎn)，請求主庫中更新的bin-log。I/O線程接收到主節(jié)點(diǎn)binlog dump 進(jìn)程發(fā)來的更新之后，保存在本地relay-log中。

從節(jié)點(diǎn) SQL 線程

SQL線程負(fù)責(zé)讀取relay log中的內(nèi)容，解析成具體的操作并執(zhí)行，最終保證主從數(shù)據(jù)的一致性。

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

要實(shí)施復(fù)制，首先必須打開Master 端的binary log（bin-log）功能，否則無法實(shí)現(xiàn)。

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

復(fù)制的基本過程如下：

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

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

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

在主服務(wù)器上，必須開啟二進(jìn)制日志機(jī)制和配置一個(gè)獨(dú)立的ID，創(chuàng)建一個(gè)用來專門復(fù)制主服務(wù)器數(shù)據(jù)的賬號

在每一個(gè)從服務(wù)器上，配置一個(gè)唯一的ID

在開始復(fù)制進(jìn)程前，在主服務(wù)器上記錄二進(jìn)制文件的位置信息

如果在開始復(fù)制之前，數(shù)據(jù)庫中已經(jīng)有數(shù)據(jù)，就必須先創(chuàng)建一個(gè)數(shù)據(jù)快照（可以使用mysqldump導(dǎo)出數(shù)據(jù)庫，或者直接復(fù)制數(shù)據(jù)文件），備份主服務(wù)器原有數(shù)據(jù)到從服務(wù)器

配置從服務(wù)器要連接的主服務(wù)器的IP地址和登陸授權(quán)，二進(jìn)制日志文件名和位置

MySQL主從復(fù)制搭建

實(shí)驗(yàn)環(huán)境：centos 7.9

主服務(wù)器：10.0.0.13 從服務(wù)器：10.0.0.14

數(shù)據(jù)庫文件：mysqlmbuq43458_db.sql

Master-Server 配置

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

vim /etc/mysql/my.cnf

[mysqld]

#配置唯一的server-id，不設(shè)置MySQL5.7以上會報(bào)錯(cuò)

server-id=1

#mysql會根據(jù)這個(gè)配置自動設(shè)置log_bin為on狀態(tài)，即開啟binlog

log_bin=master-bin

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

#log_bin_index=master-bin.index

#需要同步的數(shù)據(jù)庫，除此之外，其他不同步

#binlog-do-db=test

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

# 不同步哪些數(shù)據(jù)庫(多個(gè)寫多行)

#binlog-ignore-db=mysql

#binlog-ignore-db=information_schema

#binlog-ignore-db=performance_schema

#binlog-ignore-db=sys

# 只同步哪些數(shù)據(jù)庫，除此之外，其他不同步

#binlog-do-db=mysql

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

#expire_logs_days=7

2、重啟并進(jìn)入數(shù)據(jù)庫，創(chuàng)建用于數(shù)據(jù)同步的賬戶（目的，讓從服務(wù)器來復(fù)制數(shù)據(jù)）

# 查看主數(shù)據(jù)庫是否開啟二進(jìn)制日志

# # 查看 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，不設(shè)置MySQL5.7以上會報(bào)錯(cuò)，不能與主庫相同

server-id=2

#保證了主從數(shù)據(jù)的一致性，不論從機(jī)怎么出錯(cuò)都能保證，主從一致。

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

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

#replicate-do-db=db_name 

#設(shè)定需要復(fù)制的表

#replicate-do-table=table_name

#設(shè)定需要忽略的復(fù)制表

#replicate-ignore-table=table_name

2、重啟并進(jìn)入數(shù)據(jù)庫

要設(shè)置從庫與主庫進(jìn)行通信復(fù)制，使用必要的連接信息配置從庫在從庫上執(zhí)行以下代碼

# 查看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;

# 啟用從服務(wù)器復(fù)制

mysql> start slave;

#查看復(fù)制狀態(tài)命令,\\G表示換行查看

mysql> show slave status \\G;

檢查主從復(fù)制通信狀態(tài)

Slave_IO_State  #從站的當(dāng)前狀態(tài)

Slave_IO_Running： Yes #讀取主程序二進(jìn)制日志的I/O線程是否正在運(yùn)行

Slave_SQL_Running： Yes #執(zhí)行讀取主服務(wù)器中二進(jìn)制日志事件的SQL線程是否正在運(yùn)行。與I/O線程一樣

Seconds_Behind_Master #是否為0，0就是已經(jīng)同步了

測試主從復(fù)制

創(chuàng)建數(shù)據(jù)庫 創(chuàng)建表 導(dǎo)入數(shù)據(jù) 測試成功同步