• 語句中調(diào)用了uuid()函數(shù)潮尝、用戶自定義函數(shù)、CURRENT_USER或USER函數(shù)饿序、LOAD_FILE函數(shù)等

• 同一個語句更改了兩張或更多包含AUTO_INCREMENT列的表

• 語句中使用了服務器變量

• 存儲引擎不允許使用基于語句復制勉失，如：MySQL Cluster引擎

1.5 常見的mysql集群架構(gòu)

1）主從/主主復制

即數(shù)據(jù)可以從一個MySQL數(shù)據(jù)庫服務器主節(jié)點復制到一個或多個從節(jié)點，包含一主一從原探、一主多從乱凿、多主一從(Mysql5.7開始支持)等模式

• MySQL Replication：mysql(3.0版本開始)官方提供的異步復制方案；在 Master 與 Slave 之間的實現(xiàn)整個復制過程主要由三個線程來完成：
  • master端的I/O線程：生成binlog日志
  • slave端的I/O線程：從主庫讀取bin log咽弦，并存儲到relay log中繼日志文件中)
  • slave端的SQL線程：讀取中繼日志徒蟆，解析后，在從庫中重放
• MySQL Fabric：2014年5月28日Oracle發(fā)布型型，在MySQL Replication的基礎(chǔ)上段审，增加了故障檢測與轉(zhuǎn)移，自動數(shù)據(jù)分片功能闹蒜；依舊是一主多從的結(jié)構(gòu)寺枉，MySQL Fabirc只有一個主節(jié)點抑淫，區(qū)別是當該主節(jié)點掛了以后，會從從節(jié)點中選擇一個來當主節(jié)點
• MySQL Group Replication：mysql(5.7.17版本開始)官方提供的群組復制方案姥闪，以插件的方式提供的高可用始苇、高擴展、高可靠的MySQL集群服務
  • 高一致性：基于原生復制及paxos協(xié)議的組復制技術(shù)筐喳，提供一致數(shù)據(jù)安全保證
  • 高容錯性：只要不是大多數(shù)節(jié)點壞掉就可以繼續(xù)工作催式，有自動檢測機制，當不同節(jié)點產(chǎn)生資源急用沖突時時疏唾，不會出現(xiàn)錯誤蓄氧，按照先到者優(yōu)先進行處理，并且內(nèi)置了自動化腦裂防護機制
  • 高擴展性：節(jié)點的新增和移除都是自動的槐脏，新節(jié)點加入后喉童，會自動從其他節(jié)點上同步狀態(tài)，直到新節(jié)點和其他節(jié)點保持一致顿天，如果某節(jié)點被移除了堂氯，其他節(jié)點自動更新組信息，自動維護新的組信息
  • 高靈活性：有單主模式和多主模式牌废，單主模式下咽白，會自動選主，所有更新操作都在主上進行鸟缕；
  • 多主模式下：所有server都可以處理更新操作

2）雙主多從架構(gòu)(MMM)

    MMM（Master Replication Manager for MySQL）是雙主多從結(jié)構(gòu)晶框，這是Google的開源項目，使用Perl語言來**對MySQL Replication做擴展**懂从，提供一套支持雙主故障切換和雙主日常管理的腳本程序授段，主要用來監(jiān)控mysql主主復制并做失敗轉(zhuǎn)移

• 雖然是雙主節(jié)點，但是業(yè)務上同一時刻只允許對一個主進行寫入番甩，另一臺備選主上提供部分讀服務侵贵，以加速在主主切換時刻備選主的預熱
• 自動的主主Failover切換，一般3s以內(nèi)切換備機
• 多個從節(jié)點讀的負載均衡

3）高可用架構(gòu)(MHA)

    MHA（Master High Availability）是多主多從結(jié)構(gòu)缘薛，這是日本DeNA公司的youshimaton開發(fā)窍育，在MySQL Replication的基礎(chǔ)上，對其進行優(yōu)化宴胧。主要提供更多的主節(jié)點漱抓，但是缺少VIP（虛擬IP），需要配合keepalived等一起使用

• 要求一個復制集群中必須最少有三臺數(shù)據(jù)庫服務器恕齐，一主二從辽旋，即一臺充當master，一臺充當備用master，另外一臺充當從庫
• 多個復制集由MHA manager進行管理
• 可以進行故障的自動檢測和轉(zhuǎn)移补胚，具備自動數(shù)據(jù)補償能力，在主庫異常崩潰時能夠最大程度的保證數(shù)據(jù)的一致性
• 關(guān)于讀負載均衡可以使用F5追迟、LVS溶其、HAPROXY或者SQL Proxy等工具

4）分布式協(xié)議方案

• MySQL InnoDB Cluster：基于群組復制，提供了易于管理的 API敦间、應用故障轉(zhuǎn)移和路由瓶逃、易于配置，提供比群組復制更高級別的可用性
• MySQL NDB Cluster：提供更高級別的可用性和冗余性廓块。適用于分布式計算環(huán)境厢绝，使用內(nèi)存型的 NDB 存儲引擎
• Zookeeper + proxy：Zookeeper使用分布式算法保證集群數(shù)據(jù)的一致性，使用zookeeper可以有效的保證proxy的高可用性带猴，可以較好的避免網(wǎng)絡分區(qū)現(xiàn)象的產(chǎn)生
  • 擴展性較好昔汉，可以擴展為大規(guī)模集群
  • 但是搭建Zookeeper 集群，并配置一套代理拴清，整個系統(tǒng)的邏輯變得非常復雜
• Paxos：分布式一致性算法靶病，Paxos 算法處理的問題是一個分布式系統(tǒng)如何就某個值（決議）達成一致。這個算法被認為是同類算法中最有效的口予。Paxos與MySQL相結(jié)合可以實現(xiàn)在分布式的MySQL數(shù)據(jù)的強一致性

5）DRDB磁盤復制

    這是linux內(nèi)核板塊實現(xiàn)的快級別的同步復制技術(shù)娄周。通過各主機之間的網(wǎng)絡，復制對方磁盤的內(nèi)容沪停。當客戶將數(shù)據(jù)寫入本地磁盤時煤辨，還會將數(shù)據(jù)發(fā)送到網(wǎng)絡中另一臺主機的磁盤上，這樣的本地主機(主節(jié)點)與遠程主機(備節(jié)點)的數(shù)據(jù)即可以保證明時同步

• 保證數(shù)據(jù)的強一致性木张，且與mysql解耦
• 對io性能影響較大众辨，且從庫不提供讀操作

6）共享存儲

    主庫從庫用的一個存儲。SAN的概念是允許存儲設(shè)施和解決器（服務器）之間建立直接的高速連接窟哺，通過這種連接實現(xiàn)數(shù)據(jù)的集中式存儲

• 保證數(shù)據(jù)的強一致性
• 與mysql解耦泻轰，不會由于mysql的邏輯錯誤發(fā)生數(shù)據(jù)不一致的情況
• 但是SAN價格昂貴

1.6 常見的mysql讀寫分離方案

1）客戶端解決方案(應用層)

• Sharding-Jdbc

• TDDL

2）中間件解決方案(代理層)

• mycat

• altas

• mysql proxy

  image-20210309133735609

二. MySQL集群實踐

2.1 原生主從復制(冷備)

基于MySQL8.0在linux環(huán)境進行搭建，主從庫需要注意如下：

1. 主從服務器操作系統(tǒng)版本和位數(shù)一致且轨；

2. Master 和 Slave 數(shù)據(jù)庫的版本要一致浮声；

3. Master 和 Slave 數(shù)據(jù)庫中的數(shù)據(jù)要一致；

4. Master 開啟二進制日志旋奢， Master 和 Slave 的 server_id 在局域網(wǎng)內(nèi)必須唯一泳挥；
5. 須確認防火墻狀態(tài)，命令systemctl [stop/start/status] firewalld.servic

1）安裝mysql

• 安裝包安裝

# 下載安裝包
wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.13-el7-x86_64.tar.gz
# 解壓
mysql tar -zxvf mysql-8.0.4-rc-linux-glibc2.12-x86_64.tar.gz -C /usr/local
# 修改文件夾名
mv mysql-8.0.4-rc-linux-glibc2.12-x86_64/ mysql
# 添加默認配置文件
vim/etc/my.cnf
# 創(chuàng)建mysql數(shù)據(jù)目錄
mkdir $MYSQL_HOME/data
# 初始化mysql
/usr/local/mysql/bin/mysqld  --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
# 初始化報錯的解決方法
yum install -y libaio
# 啟動mysql服務器
service mysqld start

• rpm安裝

# 下載rpm
wget https://dev.mysql.com/get/mysql80-community-release-el8-1.noarch.rpm
# 升級安裝包
# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm
# 安裝mysql
yum install mysql-community-server
# 啟動mysql
systemctl start mysqld

# 查詢默認密碼
grep 'temporary password' /var/log/mysqld.log

2）主庫配置同步賬號

# 修改默認賬號密碼
use mysql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
# 創(chuàng)建同步賬號
CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
# 為同步賬號授權(quán)
GRANT REPLICATION SLAVE ON *.* TO 'meitian_slave'@'%';
FLUSH PRIVILEGES;
# 設(shè)置全局編碼
SET CHARACTER_SET_DATABASE = 'UTF8';

3）修改主庫配置文件

[mysqld]
# 服務器編號至朗，注意主備編號不要重復
server_id       = 1
# binlog日志存放目錄
log_bin         = /var/log/mysql/mysql-bin.log
# 開啟Binlog同步屉符，在每次提交事務前會將二進制日志同步到磁盤
sync_binlog     = 1
# binlog的日志格式，ROW-基于行的復制
binlog_format   = ROW
# 需要記錄Binlog的DB，有多個的話逗號分隔
binlog_do_db    = test
# 忽略的DB
binlog_ignore_db= mysql,information_schema
# 配置客戶端連接的認證方式配置矗钟，非必須唆香，當發(fā)生備庫無法連接主庫時可能需要配置
default_authentication_plugin = mysql_native_password

4）修改從庫配置文件

[mysqld]
server_id               = 2
log_slave_updates       = 1
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_format           = ROW
replicate_do_db         = test
replicate_ignore_db     = mysql,information_schema

5）設(shè)置從庫與主庫的連接

# 啟動主庫，并查看主庫狀態(tài)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 156 | test         | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 復制位置MASTER_LOG_POS默認為0
# MASTER_LOG_FILE需要和通過查看主庫狀態(tài)的binlog文件一致
mysql> CHANGE MASTER TO
    -> MASTER_HOST='${主機地址}',
    -> MASTER_USER='slave',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=0;

# 啟動從庫
start slave;
# 等價于下面兩個命令
# 啟動I/O線程(從主庫讀取bin log,并存儲到relay log中繼日志文件中)
start slave sql_thread;
# 啟動 SQL線程(讀取中繼日志吨艇，解析后躬它，在從庫重放)
start slave io_thread;

# 查看從庫狀態(tài)【\G：表示查詢結(jié)果按列展示】
mysql> SHOW SLAVE STATUS\G;

# 驗證：查看主從庫的processlist
# 主庫應有：Command:Binlog Dump
# 從庫應有：State:Waiting for master to send event
mysql> show processlist;

2.2 原生雙主互備(熱備)

1）安裝mysql

同 '2.1 原生主從復制(冷備)' ，略

2）雙主庫配置同步賬號

同'2.1 原生主從復制(冷備)' 东涡，略

3）修改雙主庫配置文件

• 主庫1配置

server-id = 1
log-bin=mysql-bin
binlog-do-db = meitian
binlog-ignore-db = mysql
#主－主形式需要多添加的部分
log-slave-updates = 1
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
gtid-mode=on
enforce-gtid-consistency=true
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
change master to master_host='172.16.0.14',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=986;

• 主庫2配置

server-id = 2
log-bin=mysql-bin
binlog-do-db = meitian
binlog-ignore-db = mysql
#主－主形式需要多添加的部分
log-slave-updates = 1
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2
replicate-do-db = test
replicate-ignore-db = mysql,information_schema
gtid-mode=on
enforce-gtid-consistency=true
character_set_server=utf8
init_connect='SET NAMES utf8'
lower_case_table_names=1
change master to master_host='172.16.0.13',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=2568;

4）啟動&驗證

# 啟動I/O線程(從主庫讀取bin log,并存儲到relay log中繼日志文件中)和SQL線程(讀取中繼日志冯吓，解析后，在從庫重放)
start slave;
# 等價于下面兩個命令
# start slave sql_thread;
# start slave io_thread;
# 查詢狀態(tài)
show slave status \G

2.3 MySQL Group Replication

參考：https://dwj999.github.io/MySQL-Group-Replication%E5%88%9D%E6%8E%A2.html

2.4 MySQL InnoDB Cluster

參考：https://jeremyxu2010.github.io/2019/05/mysql-innodb-cluster%E5%AE%9E%E6%88%98/

參考：https://www.modb.pro/db/15033