1.MySQL主從復制集群的核心概念

1.1.什么是主從復制集群

主從復制是指將主數(shù)據(jù)庫的 DDL 和 DML 操作通過二進制日志傳到從庫服務器中，然后在從庫上對這些日志重新執(zhí)行（也叫重做），從而使得從庫和主庫的數(shù)據(jù)保持同步。

MySQL支持一臺主庫同時向多臺從庫進行復制， 從庫同時也可以作為其他從服務器的主庫，實現(xiàn)鏈狀復制。

MySQL 復制的優(yōu)點主要包含以下三個方面：

• 主庫出現(xiàn)問題，可以快速切換到從庫提供服務。
• 實現(xiàn)讀寫分離，降低主庫的訪問壓力。
• 可以在從庫中執(zhí)行備份，以避免備份期間影響主庫服務。

1.2.主從復制集群中的專業(yè)術語

在MySQL主從復制集群中，主要分為兩大部分：文件、線程。

文件：

• 主庫
  • Binlog：二進制日志。
• 從庫
  • relay-log：中繼日志，主庫推送過來的Binlog日志都存放在中繼日志中。
  • master.info：從庫復制主庫的信息文件，記錄著主庫的地址、用戶、Binlog等信息。
  • relay-log.info：中繼日志的信息文件，該文件記錄了上次執(zhí)行relay-log的位置等信息。

線程：

• 主庫
  • binlog_dump_thread：二進制日志投遞線程，主要將二進制日志發(fā)送給從庫的線程。
• 從庫
  • IO_Thread：從庫的IO線程，用于請求和接收主庫的Binlog二進制日志。
  • SQL_Thread：從庫的SQL線程，用于將主庫發(fā)來的二進制日志在數(shù)據(jù)庫中進行數(shù)據(jù)復制。

1.3.主從復制集群工作原理

MySQL主從復制集群的工作原理主要圍繞前面提到的文件和線程。

MySQL主從復制集群的原理：

1）首先從庫執(zhí)行change master to將連接主庫的配置信息記錄到master.info文件中，此時從庫上會開啟兩個線程：I/O線程和SQL線程。

2）從庫上的I/O線程會從master.info文件中讀取主庫的連接信息。

3）從庫I/O線程獲取到主庫的信息后，會與主庫進行身份認證，然后建立連接。

4）當從庫I/O線程成功連接到主庫后，主庫會立即給從庫分配一個binlog_dump_thread線程，用于推送Binlog日志到從庫。

5）從庫I/O線程會根據(jù)master.info中記錄的Binlog信息（Binlog日志文件、標識位號）與主庫的binlog_dump_thread線程請求最新的Binlog日志。

6）這時主庫的binlog_dump_thread線程就會去查詢是否產(chǎn)生了新的Binlog日志，如果產(chǎn)生了新的Binlog日志，會截取最新的Binlog日志然后推送給從庫的I/O線程。

7）從庫的I/O線程接收到主庫推送的Binlog日志后，會現(xiàn)將其存放在內(nèi)存的TCP/IP緩存中，然后告知主庫的binlog_dump_thread線程，Binlog日志已收到。

8）此時從庫的I/O線程會去更新master.info文件中的Binlog位置點信息，記錄最新的Binlog標識號。

9）然后從庫的I/O線程會將主庫推送的Binlog日志寫入到磁盤上的relay-log文件中。

10）最后由從庫的SQL線程讀取relay-log.ifno文件，獲取relay-log最新的位置點，然后根據(jù)的位置點去relay-log中執(zhí)行最新的Binlog日志，執(zhí)行完成后會再次更新relay-log.info文件中記錄的relay-log位置點。

這就是完整的主從復制工作原理。

簡單來說MySQL主從復制的原理就是，從庫的I/O線程讀取連接主庫的配置信息，然后去連接主庫開始主從同步，當I/O線程連接上主庫后，主庫會立即給I/O線程分配一個Dump線程，用于推送Binlog日志到從庫，此時I/O線程會根據(jù)master.info文件中記錄的Binlog信息，向主庫的Dump線程請求最新的BInlog，Dump線程查詢到有最新的Binlog產(chǎn)生，會將最新的Binlog截取，然后推送給從庫的I/O線程，I/O線程收到Binlog日志后，將其存放在內(nèi)存的TCP/IP緩存中，然后更新master.info文件中最新的Binlog信息，緊接著將Binlog日志寫入到relay-log中，最后由從庫的SQL線程從relay-log.info中讀取relay-log的位置號，然后執(zhí)行relay-log中最新的Binlog日志，執(zhí)行完成后，再次更新relay-log.info中的relay-log位置號，以便于下次再relay-log中讀取最新的Binlog日志。

relay-log日志會通過MySQL中的其他線程定期清理。

1.4.主從復制中的小細節(jié)

從上面說的主從復制原理來看，好像每次都是從庫向主庫去請求新數(shù)據(jù)，那么什么時候從庫才應該向主庫請求呢？請求的頻率如何？

其實是這樣的：當主庫上產(chǎn)生了新的事務，更新到Binlog日之后，會給binlog_dump_thread線程發(fā)送一個“信號”，binlog_dump_thread線程會與從庫的I/O線程一直建立連接，binlog_dump_thread線程就會通知從庫的I/O線程有新數(shù)據(jù)產(chǎn)生了，這時從庫的I/O線程就帶著master.info中記錄的最新Binlog標識位號，向binlog_dump_thread線程請求最新的Binlog，然后完成數(shù)據(jù)同步。

MySQL主從復制第一次復制時，是按照上面說到的10步完成的，第二次復制時，只需要等待主庫的binlog_dump_thread線程向從庫的I/O線程發(fā)送信號，然后I/O線程去請求最新的Binlog，最后由SQL線程復制數(shù)據(jù)即可。

1.5.搭建主從復制集群的前提條件

搭建MySQL主從復制集群的前提條件如下：

• 首先需要準備多個MySQL實例，最少兩個MySQL實例，能夠?qū)崿F(xiàn)一主一從的架構，可以在多個服務器中分布部署獨立的MySQL節(jié)點，也可以在一臺服務器中部署多個MySQL實例。
• 每個MySQL實例都需要由單獨的sever_id。
• 身為主庫角色的MySQL實例需要開啟二進制日志，從庫在特定場景下需要開啟。
• 主庫需要授權一個專門的用戶作為主從復制的用戶。
• 如果主庫是一個運行很多年的數(shù)據(jù)庫，突然要升級為主從復制集群，最好先將主庫的數(shù)據(jù)同步一份到從庫中。
• 開啟專用的復制線程。

1.6.MySQL主從復制集群的架構信息

本次主從復制的架構是一主兩從的結構。

IP	主機名	端口	角色	server_id
192.168.20.11	mysql-1	3306	主庫	1
192.168.20.12	mysql-2	3306	從庫	2
192.168.20.12	mysql-2	3307	從庫	3

2.搭建MySQL多實例環(huán)境

再搭建MySQL主從復制集群之前，首先搭建出多個MySQL實例，由于服務器有限，因此通過兩臺機器模擬出一主兩從的環(huán)境。

主從復制集群中有一個節(jié)點開啟了gtid，所有的節(jié)點都需要開啟gtid，否則主從將不能同步。

2.1.在mysql-1中搭建身為主庫的MySQL實例

mysql-1服務器中的3306端口的MySQL實例再前面已經(jīng)搭建完成了，就是我們一直在使用的數(shù)據(jù)庫實例，里面有數(shù)據(jù)，更加方便演示主從集群，下面只是提供一下搭建過程的步驟。

注意每個MySQL節(jié)點的server_id都要設置成不同的，如果你的一主兩從都是單獨在不同服務器中部署的，那么直接參考本小結的數(shù)據(jù)庫實例安裝即可，不用再看多實例的步驟了。

1.解壓MySQL
[root@mysql-1 ~]# tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mysql-1 ~]# mv /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql2.設置MySQL的環(huán)境變量
[root@mysql-1 ~]# vim /etc/profile
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=:/usr/local/mysql/lib3.創(chuàng)建mysql用戶
[root@mysql-1 ~]# groupadd -r mysql
[root@mysql-1 ~]# useradd -M -r -s /sbin/nologin -g mysql mysql4.準備數(shù)據(jù)目錄
[root@mysql-1 ~]# mkdir /data/mysql
[root@mysql-1 ~]# chown -R mysql. /data/mysql5.初始化數(shù)據(jù)庫
[root@mysql-1 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql6.準備mysql配置文件
[root@mysql-1 ~]# vim /etc/my.cnf
[mysqld]	
user=mysql								
port=3306							    
server_id=1							#每個MySQL數(shù)據(jù)庫的server_id都設置成不同的
basedir=/usr/local/mysql				
datadir=/data/mysql	
log_bin=mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
socket=/tmp/mysql.sock
log_error=/data/mysql/mysql_err.log
character-set-server=utf8[mysql]
socket=/tmp/mysql.sock7.準備服務管理腳本
[root@mysql-1 ~]# vim /etc/systemd/system/mysqld.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 50008.啟動數(shù)據(jù)庫
[root@mysql-1 ~]# systemctl daemon-reload 
[root@mysql-1 ~]# systemctl start mysqld9.設置root密碼
[root@mysql-1 ~]# mysqladmin -u root -P 3306  password '123456'10.登陸數(shù)據(jù)庫
[root@mysql-1 ~]# mysql -uroot -p123456
mysql>

2.2.在mysql-2中搭建MySQL多實例

由于服務器數(shù)量有限，在mysql-2這臺服務器中分別搭建兩個從庫。

2.2.1.安裝數(shù)據(jù)庫軟件

1.解壓MySQL
[root@mysql-2 ~]# tar xf mysql-5.7.36-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mysql-2 ~]# mv /usr/local/mysql-5.7.36-linux-glibc2.12-x86_64 /usr/local/mysql2.設置MySQL的環(huán)境變量
[root@mysql-2 ~]# vim /etc/profile
export MYSQL_HOME=/usr/local/mysql
export PATH=$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=:/usr/local/mysql/lib3.創(chuàng)建mysql用戶
[root@mysql-2 ~]# groupadd -r mysql
[root@mysql-2 ~]# useradd -M -r -s /sbin/nologin -g mysql mysql

2.2.1.搭建第一個3306從庫的MySQL實例

搭建第一個從庫實例，端口號為3306，server_id為2，數(shù)據(jù)路徑為/data/mysql3306。

1.創(chuàng)建3306從庫的數(shù)據(jù)目錄
[root@mysql-2 ~]# mkdir /data/mysql3306
[root@mysql-2 ~]# chown -R mysql.mysql /data/mysql3306/2.準備3306從庫的配置文件
[root@mysql-1 ~]# vim /etc/my3306.cnf
[mysqld]	
user=mysql								
port=3306							    
server_id=2							#每個MySQL數(shù)據(jù)庫的server_id都設置成不同的
basedir=/usr/local/mysql				
datadir=/data/mysql3306	
log_bin=/data/mysql3306/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
socket=/data/mysql3306/mysql.sock
log_error=/data/mysql3306/mysql_err.log
character-set-server=utf8[mysql]
socket=/data/mysql3306/mysql.sock3.初始化3306從庫
[root@mysql-2 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql33064.準備服務管理腳本
[root@mysql-1 ~]# vim /etc/systemd/system/mysqld3306.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf
LimitNOFILE = 50005.啟動數(shù)據(jù)庫
[root@mysql-1 ~]# systemctl daemon-reload 
[root@mysql-1 ~]# systemctl start mysqld33066.設置root密碼
[root@mysql-2 ~]# mysqladmin -u root -P 3306 -S /data/mysql3306/mysql.sock password '123456'7.登陸數(shù)據(jù)庫
[root@mysql-2 ~]# mysql -uroot -p123456 -P3306 -S /data/mysql3306/mysql.sock
mysql>

2.2.2.搭建第二個3307從庫的MySQL實例

搭建第一個從庫實例，端口號為3306，server_id為2，數(shù)據(jù)路徑為/data/mysql3306。

1.創(chuàng)建3307從庫的數(shù)據(jù)目錄
[root@mysql-2 ~]# mkdir /data/mysql3307
[root@mysql-2 ~]# chown -R mysql.mysql /data/mysql3307/2.準備3307從庫的配置文件
[root@mysql-1 ~]# vim /etc/my3307.cnf
[mysqld]	
user=mysql								
port=3307							    
server_id=3							#每個MySQL數(shù)據(jù)庫的server_id都設置成不同的
basedir=/usr/local/mysql				
datadir=/data/mysql3307	
log_bin=/data/mysql3307/mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
socket=/data/mysql3307/mysql.sock
log_error=/data/mysql3307/mysql_err.log
character-set-server=utf8[mysql]
socket=/data/mysql3307/mysql.sock3.初始化3307從庫
[root@mysql-2 ~]# mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql33074.準備服務管理腳本
[root@mysql-1 ~]# vim /etc/systemd/system/mysqld3307.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf
LimitNOFILE = 50005.啟動數(shù)據(jù)庫
[root@mysql-1 ~]# systemctl daemon-reload 
[root@mysql-1 ~]# systemctl start mysqld33076.設置root密碼
[root@mysql-2 ~]# mysqladmin -u root -P 3307 -S /data/mysql3307/mysql.sock password '123456'7.登陸數(shù)據(jù)庫
[root@mysql-2 ~]# mysql -uroot -p123456 -P3307 -S /data/mysql3307/mysql.sock
mysql>

2.3.MySQL多個節(jié)點搭建完畢

MySQL多個節(jié)點已經(jīng)搭建完畢了，下面來查詢每個實例的server_id。

3.配置MySQL主從復制集群

MySQL數(shù)據(jù)庫的多個實例節(jié)點已經(jīng)搭建完成了，下面將這些搭建的數(shù)據(jù)庫實例配置成主從復制集群（一主兩從）。

IP	主機名	端口	角色
192.168.20.11	mysql-1	3306	主庫
192.168.20.12	mysql-2	3306	從庫
192.168.20.12	mysql-2	3307	從庫

配置MySQL主從復制集群的大致步驟：

• 主庫開啟Binlog日志，從庫復制主庫數(shù)據(jù)要通過Binlog進行復制。
• 主庫創(chuàng)建專門用作主從復制的用戶。
• 將主庫數(shù)據(jù)進行備份，再從庫中恢復。
• 配置從庫連接主庫的復制信息。
• 啟動主從復制集群。

3.1.在主庫開啟Binlog二進制日志

主庫已經(jīng)開啟了Binlog二進制日志，我們來查一下。

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /data/mysql/mysql-bin       |
| log_bin_index                   | /data/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

從庫復制主庫數(shù)據(jù)，都是通過Binlog進行傳輸和恢復的。

3.2.在主庫上創(chuàng)建主從復制的用戶

mysql> grant replication slave on *.* to replicas@'192.168.20.%' identified by '123456';

3.3.將主庫數(shù)據(jù)備份并在從庫中進行還原

如果是運行很久的主庫，要升級為主從復制集群，那么建議將主庫上的數(shù)據(jù)備份還原到從庫上，避免從庫一次性同步很多數(shù)據(jù)，浪費性能。

[root@mysql-1 ~]# mysqldump -uroot -p123456 -A --master-data=2 -R -E --triggers --single-transaction > all_db.sql

在192.168.20.12的兩個從庫中還原主庫的備份數(shù)據(jù)。

1.將備份上傳到從庫的服務器上
[root@mysql-1 ~]# scp -rp all_db.sql root@192.168.20.12:/root2.3306從庫還原主庫備份數(shù)據(jù)
[root@mysql-2 ~]# mysql -uroot -p123456 -P3306 -S /data/mysql3306/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /root/all_db.sql;3.3307從庫還原主庫備份數(shù)據(jù)
[root@mysql-2 ~]# mysql -uroot -p123456 -P3307 -S /data/mysql3307/mysql.sock 
mysql> set sql_log_bin=0;
mysql> source /root/all_db.sql;

3.4.配置從庫連接主庫的復制信息

接下來我們需要配置從庫連接主庫的信息，包括讓從庫知道主庫的IP、端口號、復制的用戶密碼、Binlog相關的信息。

1）獲取主庫Binlog的信息

從庫是通過Binlog復制主庫數(shù)據(jù)的，首先要獲取主庫Binlog日志的一些信息，包括主庫的Binlog日志使用的是哪個、要從Binlog日志中的哪一個事件標識號處開始復制數(shù)據(jù)。

從Binlog的哪一個事件標識號處開始復制數(shù)據(jù)，尤為關鍵，因為主庫可能運行的時間長了，也有很多的數(shù)據(jù)，一定要從合適的位置處開始復制數(shù)據(jù)，要不然也會產(chǎn)生很大的資源浪費。

我們是用主庫頭一天的全庫備份，然后還原到從庫上的，在主庫的備份文件中就記錄了從庫應該從哪一個Binlog事件標識位處開始復制數(shù)據(jù)。

vim all_db.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=452;

從備份文件中得知，我們應該讓從庫從主庫Binlog事件標識位號452處，開始復制數(shù)據(jù)。

如果主庫不是運行很久的，不需要備份數(shù)據(jù)還原到從庫，而是直接搭建的主從復制集群，那么直接可以從主庫狀態(tài)信息那里，獲取Binlog的標識位號，在配置主從時，一定要指對Binlog標識位號，否則主從將會失敗。

mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000001 |      452 |              |                  | 4f87bad8-fc67-11ec-be7b-005056b791aa:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

2）配置從庫連接主庫進行復制的參數(shù)信息

配置從庫連接主庫進行復制的參數(shù)有很多，不過只需要記住這一條命令：help change master to即可，在MySQL交互式下執(zhí)行此命令可以打印出從庫連接主庫的所有配置參數(shù)。

1.3306從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3306 -S /data/mysql3306/mysql.sock 
mysql> CHANGE MASTER TOMASTER_HOST='192.168.20.11',MASTER_USER='replicas',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=452,MASTER_CONNECT_RETRY=10;1.3307從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3307 -S /data/mysql3307/mysql.sock 
mysql> CHANGE MASTER TOMASTER_HOST='192.168.20.11',MASTER_USER='replicas',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=452,MASTER_CONNECT_RETRY=10;  

參數(shù)解釋：

參數(shù)	含義
MASTER_HOST	主庫的地址
MASTER_USER	主從復制的專用用戶
MASTER_PASSWORD	主從復制用戶的密碼
MASTER_PORT	主庫的端口號
MASTER_LOG_FILE	主庫的Binlog日志名
MASTER_LOG_POS	從主庫BInlog日志的哪一個標識位處開始復制
MASTER_CONNECT_RETRY	主從連接失敗的重試時間間隔

3.5.啟動主從復制線程

1.3306從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3306 -S /data/mysql3306/mysql.sock 
mysql> start slave;1.3307從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3307 -S /data/mysql3307/mysql.sock 
mysql> start slave;

4.查看主從復制集群狀態(tài)

1.3306從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3306 -S /data/mysql3306/mysql.sock 
mysql> show slave status\G;1.3307從庫配置
[root@mysql-2 ~]# mysql -uroot -p123456 -P3307 -S /data/mysql3307/mysql.sock 
mysql> show slave status\G;

當每個從庫的IO線程和SQL線程的狀態(tài)都是Yes，就表示主從復制集群搭建完畢了。

MySQL主從配置過程中從庫連接主庫的配置寫錯了應該如何解決？

只要不執(zhí)行start slave，再執(zhí)行一次change master to會覆蓋上一次配置。

1.停止主從復制
mysql>stop slave;2.清空從庫連接主庫的信息
mysql> reset slave all;3.重新設置
mysql> CHANGE MASTER TOMASTER_HOST='192.168.20.11',MASTER_USER='replicas',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=452,MASTER_CONNECT_RETRY=10;