概述

MySQL的主從復(fù)制（Master-Slave Replication）是一種數(shù)據(jù)復(fù)制解決方案，將主數(shù)據(jù)庫(kù)的DDL（數(shù)據(jù)定義語(yǔ)言）和DML（數(shù)據(jù)操縱語(yǔ)言）操作通過(guò)二進(jìn)制日志傳到從庫(kù)服務(wù)器中，然后在從庫(kù)上對(duì)這些日志重新執(zhí)行（也叫重做），從而是的從庫(kù)和主庫(kù)的數(shù)據(jù)保存同步。

MySQL支持將數(shù)據(jù)從一個(gè)MySQL服務(wù)器（主服務(wù)器）復(fù)制到一個(gè)或多個(gè)其他MySQL服務(wù)器（從服務(wù)器），從庫(kù)同時(shí)也可以作為其他從服務(wù)器的主庫(kù)，實(shí)現(xiàn)鏈狀復(fù)制。

MySQL主從復(fù)制的優(yōu)點(diǎn)主要包含以下三個(gè)方面：

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

需要注意的是，MySQL的主從復(fù)制是異步的，這意味著從服務(wù)器的數(shù)據(jù)可能會(huì)與主服務(wù)器的數(shù)據(jù)存在一定的延遲。因此，在使用主從復(fù)制時(shí)，需要根據(jù)具體的業(yè)務(wù)場(chǎng)景和需求來(lái)選擇合適的配置和策略。

工作原理

從上圖來(lái)看，主從復(fù)制分成三步：

1. Master主庫(kù)在事務(wù)提交時(shí)，會(huì)把數(shù)據(jù)變更記錄在二進(jìn)制日志文件Binlog中；
2. 從庫(kù)讀取主庫(kù)的二進(jìn)制日志文件Binlog，寫(xiě)入到從庫(kù)的中繼日志Relay Log；
3. Slave重做中繼日志中的事件，將改變數(shù)據(jù)更新同步到從庫(kù)中；

說(shuō)白了就是Master主庫(kù)上執(zhí)行的增刪改的SQL語(yǔ)句同步到對(duì)應(yīng)的Slave從庫(kù)上，然后再在Slave從庫(kù)中同樣再次執(zhí)行一遍SQL語(yǔ)句以作備份。

綜合案例

前期準(zhǔn)備

準(zhǔn)備兩臺(tái)虛擬機(jī)，需要提前安裝好MySQL數(shù)據(jù)庫(kù)（必須要開(kāi)啟二進(jìn)制日志）。

如下所示：

注意：以上只是示例說(shuō)明，具體以自己的虛擬機(jī)情況為主。

例外如果克隆的兩臺(tái)虛擬機(jī)IP地址一致，可根據(jù)以下操作修改實(shí)現(xiàn)動(dòng)態(tài)ip（基于mac地址發(fā)配IP）

切換目錄到：/etc/netplan 并且編輯00-installer-config.yaml文件

如下圖指定位置加入：dhcp-identifier: mac(嚴(yán)格縮進(jìn)格式要求)

重啟網(wǎng)絡(luò)刷新修改：netplan apply

主庫(kù)配置

修改主庫(kù)服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，并添加如下配置信息（開(kāi)啟二進(jìn)制日志）：

[mysqld]
...
# 開(kāi)啟二進(jìn)制日志（必須）
log-bin = mysql-bin
# MySQL服務(wù)ID，保證整個(gè)集群環(huán)境中唯一，默認(rèn)為1（必須）
server-id = 1
# 二進(jìn)制日志格式，默認(rèn)ROW（可選）
binlog_format = ROW
# 忽略的數(shù)據(jù)，不需要同步的數(shù)據(jù)庫(kù)
# binlog-ignore-db = db1
# binlog-ignore-db = db2
# 指定同步的數(shù)據(jù)庫(kù)
# binlog-do-db = db3

• 注意：這里binlog-ignore-db和binlog-do-db配置項(xiàng)沒(méi)有指定，默認(rèn)同步所有數(shù)據(jù)庫(kù)信息。
• 從 MySQL 5.7 開(kāi)始，binlog-ignore-db 的優(yōu)先級(jí)高于 binlog-do-db。這意味著即使某個(gè)數(shù)據(jù)庫(kù)被 binlog-do-db 指定，如果它同時(shí)出現(xiàn)在 binlog-ignore-db 的列表中，那么它的更改將不會(huì)被記錄到二進(jìn)制日志中

重啟MySQL服務(wù)器。

systemctl restart mysql

（追求安全，否則可跳過(guò)）登錄MySQL數(shù)據(jù)庫(kù)，創(chuàng)建遠(yuǎn)程連接的賬號(hào)，并授予主從復(fù)制權(quán)限。

# 創(chuàng)建xx用戶(hù)，并設(shè)置密碼，該用戶(hù)可在任意主機(jī)連接該MySQL服務(wù)
create usxx'@'%' identified with mysql_native_password by 'xx1234';
# 為'xx'@'%'用戶(hù)分配主從復(fù)制權(quán)限
grant replication slave on *.* to 'zking'@'%';

通過(guò)指令，查看二進(jìn)制日志坐標(biāo)

show master status;

從庫(kù)配置

1）修改從庫(kù)服務(wù)器的MySQL核心配置文件/etc/mysql/mysql.conf.d/mysqld.cnf，并添加如下配置信息：

[mysqld]
...
# 開(kāi)啟二進(jìn)制日志（必須）
log-bin = mysql-bin
# MySQL服務(wù)ID，保證整個(gè)集群環(huán)境中唯一，默認(rèn)為1（必須）
server-id = 2
# 二進(jìn)制日志格式，默認(rèn)ROW（可選）
binlog_format = ROW
# 是否只讀，1代表只讀，0代表讀寫(xiě)
read-only = 1

2）重啟MySQL服務(wù)器。

systemctl restart mysql

3）登錄MySQL數(shù)據(jù)庫(kù)，設(shè)置主庫(kù)配置。

• MySQL8.0.23之前的版本，執(zhí)行如下SQL語(yǔ)句：

change master to master_host='xxx.xxx.xxx.xxx',master_user='xxx',master_password='xxx',master_log_file='xxx',master_log_pos=xxx;
change master to master_host='192.168.111.135',master_user='root',master_password='123',master_log_file='mysql_bin.000008',master_log_pos=2756;

• MySQL8.0.23之后的版本，執(zhí)行如下SQL語(yǔ)句：

change replication source to source_host='xxx.xxx.xxx.xxx',source_user='xxx',source_password='xxx',source_log_file='xxx',source_log_pos=xxx;

參數(shù)說(shuō)明：

4）開(kāi)啟同步操作

# 8.0.22之后
start replica; 
# 8.0.22之前
start slave;

5）查看主從同步狀態(tài)

# 8.0.22之后
show replica status; 
# 8.0.22之前
show slave status;

格式化顯示：show slave status\G;

上述圖中顯示Slave_IO_Running: No，很明顯主從復(fù)制開(kāi)啟失敗。經(jīng)過(guò)問(wèn)題分析之后，發(fā)現(xiàn)是虛擬機(jī)是克隆的，導(dǎo)致主庫(kù)和從庫(kù)的MySQL的server id都是一樣的。

解決方案：修改任意主庫(kù)和從庫(kù)的server id即可解決問(wèn)題。

修改/var/lib/mysql/auto.cnf文件。將server-uuid屬性修改為唯一值即可。

[auto]
server-uuid = 任意uuid

方案二：

• 停止mysql服務(wù)
• 刪除auto.cnf
• 啟動(dòng)mysql服務(wù)

修改完畢保存并退出，最后重啟MySQL服務(wù)后，并再次登錄MySQL查看主從復(fù)制是否成功。

數(shù)據(jù)測(cè)試

1）登錄主庫(kù)MySQL，并執(zhí)行以下SQL語(yǔ)句：

# 切換數(shù)據(jù)庫(kù)
use db1;
# 創(chuàng)建數(shù)據(jù)表t_student
create table t_student(sid int primary key auto_increment,sname varchar(20) not null,sage int default 0,ssex varchar(2) default '1');
# 批量添加數(shù)據(jù)
insert into t_student(sname,sage,ssex) values('張三',26,'男'),('王五',22,'女'),('小七',23,'女');

2）登錄從庫(kù)MySQL，查看主從復(fù)制結(jié)果：

# 切換數(shù)據(jù)庫(kù)
use db1;
# 查看是否存在t_student表
show tables;
# 查看t_student表中是否存在數(shù)據(jù)
select * from t_student;

存在數(shù)據(jù)即MySQL主從復(fù)制同步成功（主庫(kù)操作，從庫(kù)也會(huì)有）。

異常處理

# 授權(quán)&創(chuàng)建用戶(hù)
mysql> grant select,insert,file on test.* to test@'%' identified by '123';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
?
Database changed
mysql> select host,user from user;(test并沒(méi)有權(quán)限)
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| %         | test          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
4 rows in set (0.00 sec)
mysql> show grants for test;
+----------------------------------+
| Grants for test@% |
+----------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |【為默認(rèn)權(quán)限，所有用戶(hù)都有】
+----------------------------------+
1 row in set (0.00 sec)

mysql> grant select,insert on test.* to test@'%' identified by '123';

Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show grants for test;
+------------------------------------------------+
| Grants for test@% |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'test'@'%' |
| GRANT SELECT, INSERT ON `test`.* TO 'test'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)

在創(chuàng)建用戶(hù)時(shí)對(duì) test 庫(kù)授予 SELECT、INSERT、FILE 權(quán)限，因 FILE 權(quán)限不能授予某個(gè)數(shù)據(jù)庫(kù)而導(dǎo)致語(yǔ)句執(zhí)行失敗。

但最終結(jié)果是：test@'%' 創(chuàng)建成功，授權(quán)部分失敗。

從上面的測(cè)試可知，使用 GRANT 創(chuàng)建用戶(hù)其實(shí)是分為兩個(gè)步驟：創(chuàng)建用戶(hù)和授權(quán)。

權(quán)限有問(wèn)題并不影響用戶(hù)的創(chuàng)建，上述語(yǔ)句會(huì)導(dǎo)致主庫(kù)在 binlog 寫(xiě) INCIDENT_EVENT，從而導(dǎo)致主從復(fù)制報(bào)錯(cuò)

故障解決

 mysql> stop slave;
 mysql> set global sql_slave_skip_counter=1; #指定跳過(guò)事務(wù)個(gè)數(shù)
 mysql> start slave;

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論