MySQL主從同步的幾種實現(xiàn)方式
更新時間：2025年02月16日 09:53:47 作者：致奮斗的我們
本文主要介紹了MySQL主從同步的幾種實現(xiàn)方式,文中通過示例代碼介紹的非常詳細,對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
一、MySQL主從同步
- MySQL內(nèi)建的復(fù)制功能是構(gòu)建大型，高性能應(yīng)用程序的基礎(chǔ)
- 通過將MySQL的某一臺主機（master）的數(shù)據(jù)復(fù)制到其他主機（slaves）上，并重新執(zhí)行一遍來執(zhí)行 復(fù)制過程中一臺服務(wù)器充當(dāng)主服務(wù)器，而其他一個或多個其他服務(wù)器充當(dāng)從服務(wù)器
1、MySQL支持的復(fù)制類型
- 基于語句（statement）的復(fù)制
- 在主服務(wù)器上執(zhí)行SQL語句，在從服務(wù)器上執(zhí)行同樣的語句。MySQL默認采用基于語句的復(fù)制，效率比較高。
- 基于行（row）的復(fù)制
- 把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍。從MySQL 5.0開始支持。
- 混合型（mixed）的復(fù)制
- 默認采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句的無法精確復(fù)制時，就會采用基于行的復(fù)制。
2、為什么要做主從復(fù)制
- 災(zāi)備
- 數(shù)據(jù)分布
- 負載平衡
- 讀寫分離
- 提高并發(fā)能力
3、主從復(fù)制原理
主要基于MySQL二進制日志 主要包括三個線程（2個I/O線程，1個SQL線程）
1、MySQL將數(shù)據(jù)變化記錄到二進制日志中；
2、Slave將MySQL的二進制日志拷貝到Slave的中繼日志中；
3、Slave將中繼日志中的事件在做一次，將數(shù)據(jù)變化，反應(yīng)到自身（Slave）的數(shù)據(jù)庫
詳細步驟：
1、從庫通過手工執(zhí)行change master to 語句連接主庫，提供了連接的用戶一切條件（user 、password、port、ip），并且讓從庫知道，二進制日志的起點位置（file名 position 號）； start slave
2、從庫的IO線程和主庫的dump線程建立連接。
3、從庫根據(jù)change master to 語句提供的file名和position號，IO線程向主庫發(fā)起binlog的請求。
4、主庫dump線程根據(jù)從庫的請求，將本地binlog以events的方式發(fā)給從庫IO線程。
5、從庫IO線程接收binlog events，并存放到本地relay-log中，傳送過來的信息，會記錄到master.info中
6、從庫SQL線程應(yīng)用relay-log，并且把應(yīng)用過的記錄到relay-log.info中，默認情況下，已經(jīng)應(yīng)用過的relay 會自動被清理purge
4、MySQL復(fù)制常用的拓撲結(jié)構(gòu)
- 主從類型（Master-Slave）
- 主主類型（Master-Master）
- 級聯(lián)類型（Master-Slave-Slave）
1、基于binlog的主從同步
主庫配置
配置文件： [root@localhost ~]# tail -1 /etc/my.cnf server_id=1 [root@localhost ~]# systemctl restart mysqld 備份： [root@localhost ~]# mysqldump --opt -B db1 it school > db.sql 授權(quán)用戶： mysql> create user rep@'172.16.%.%' identified with mysql_native_password by '123456'; mysql> grant replication slave on *.* to rep@'172.16.%.%'; mysql> show master status; +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+ | binlog.000015 | 756 | | | 2db179b1-cf96-11ee-8b00-2e6ff2d90c84:1-14, d09e219c-cec9-11ee-baf1-2e6ff2d90c84:1-46 | +---------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
從庫配置：
配置文件： [root@localhost ~]# tail -1 /etc/my.cnf server_id=2 [root@localhost ~]# systemctl restart mysqld [root@localhost ~]# tail -1 /etc/my.cnf server_id=3 [root@localhost ~]# systemctl restart mysqld 還原主庫備份： # scp db.sql 172.16.100.22:/root/ # scp db.sql 172.16.100.23:/root/ mysql -uroot -pMySQL@123 < db.sql
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to source
Master_Host: 192.168.150.21
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 157
Relay_Log_File: localhost-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...
Last_IO_Error: error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.150.21:3306' (111)
[root@localhost ~]# mysqladmin -urep -p123456 -h192.168.150.21 ping
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqld is alive
# 查詢?nèi)罩?
[root@localhost ~]# tail /var/log/mysql/mysqld.log
...
2023-12-02T03:17:53.631813Z 5 [ERROR] [MY-010584] [Repl] Slave I/O for channel '': error connecting to master 'rep@192.168.150.21:3306' - retry-time: 60 retries: 3 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. Error_code: MY-002061
解決方法：
方案一：修改master庫的密碼加密方式
mysql> alter user 'rep'@'192.168.150.%' identified with mysql_native_password by '123456'; Query OK, 0 rows affected (0.01 sec)
方案二：設(shè)置從庫的change master 時加get_master_public_key=1參數(shù)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to
-> master_host='172.16.100.21',
-> master_user='rep',
-> master_password='123456',
-> master_log_file='binlog.000015',
-> master_log_pos=756,
-> get_master_public_key=1;
Query OK, 0 rows affected, 9 warnings (0.00 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)可選配置
#[可選] 0（默認）表示讀寫（主機），1表示只讀（從機） read-only=0 #設(shè)置日志文件保留的時長，單位是秒 binlog_expire_logs_seconds=6000 #控制單個二進制日志大小。此參數(shù)的最大和默認值是1GB max_binlog_size=200M #[可選]設(shè)置不要復(fù)制的數(shù)據(jù)庫 binlog-ignore-db=test #[可選]設(shè)置需要復(fù)制的數(shù)據(jù)庫,默認全部記錄。 binlog-do-db=需要復(fù)制的主數(shù)據(jù)庫名字 #[可選]設(shè)置binlog格式 binlog_format=STATEMENT
2、基于gtid的主從同步配置
開啟gtid
gtid_mode=ON
enforce-gtid-consistency=ON
mysql> CHANGE MASTER TO
> MASTER_HOST = host,
> MASTER_PORT = port,
> MASTER_USER = user,
> MASTER_PASSWORD = password,
> MASTER_AUTO_POSITION = 1;
Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO
> SOURCE_HOST = host,
> SOURCE_PORT = port,
> SOURCE_USER = user,
> SOURCE_PASSWORD = password,
> SOURCE_AUTO_POSITION = 1;
mysql> START SLAVE;
Or from MySQL 8.0.22:
mysql> START REPLICA;GTID 從庫誤寫入操作處理
查看監(jiān)控信息:
Last_SQL_Error: Error 'Can't create database 'db4'; database exists' on query. Default database: 'db4'. Query: 'create database db4'
Retrieved_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-3
Executed_Gtid_Set: 71bfa52e-4aae-11e9-ab8c-000c293b577e:1-2,
7ca4a2b7-4aae-11e9-859d-000c298720f6:1
注入空事務(wù)的方法：
stop slave;
set gtid_next='99279e1e-61b7-11e9-a9fc-000c2928f5dd:3';
begin;commit;
set gtid_next='AUTOMATIC';
這里的xxxxx:N 也就是你的slave sql thread報錯的GTID，或者說是你想要跳過的GTID。
最好的解決方案：重新構(gòu)建主從環(huán)境二、MySQL 主從讀寫分離實現(xiàn)方案
(1).MySQL Router：MySQL官方提供的輕量級MySQL代理(路由)，只提供讀寫分離功能，前身為SQL Proxy。
(2).ProxySQL：類似于MySQL Router，輕量級MySQL代理，提供讀寫分離功能，也支持一些sharding功能。有percona版和官方版兩個版本。
(3).MaxScale：MariaDB的中間件，和MySQL Router、ProxySQL類似。
這三者類似，都是輕量級數(shù)據(jù)庫中間件。
(4).Amoeba、Cobar、MyCAT：提供很多功能，最主要的功能包括讀寫分離、sharding。
這三者的淵源較深，都是開源的。Amoeba后繼無人，于是Cobar出來，Cobar后繼無人，加上2013年出現(xiàn)了一次較嚴重的問題，于是MyCAT站在Cobar的肩膀上出來了。
( 1 ）通過程序?qū)崿F(xiàn)讀寫分離（需要程序支持）
php和Java 程序都可以通過設(shè)置多個連接文件輕松地實現(xiàn)對數(shù)據(jù)庫讀寫分離，當(dāng)語句關(guān)鍵字為 select 時，就去連接讀庫的連接文件，若為 update、 insert、 delete 時，連接寫庫的連接文件
通過程序?qū)崿F(xiàn)讀寫分離的缺點就是需要開發(fā)人員對程序進行改造，程序本身無法直接支持讀寫分離
( 2）通過開源的軟件實現(xiàn)讀寫分離
Maxscale 、Atlas 、Mycat 等代理軟件也可以實現(xiàn)讀寫分離的功能，并且無須對應(yīng)用程序做任何修改，而且它們還支持負載均衡等功能 ；缺點是又引入了單點服務(wù)，并且穩(wěn)定性不如程序?qū)崿F(xiàn)好
( 3）大型門戶獨立開發(fā) DAL 綜合軟件
百度、阿里等大型門戶都有開發(fā)牛人，會花大力氣開發(fā)適合自己業(yè)務(wù)的讀寫分離、負載均衡、監(jiān)控報警、自動擴容、自動收縮等一系列功能的 DAL 層軟件
MySQL 寫分離的基本邏輯圖如圖
2.1 ProxySQL實現(xiàn)mysql8主從同步讀寫分離
1、ProxySQL基本介紹
1.1 前言
ProxySQL是 MySQL 的高性能、高可用性、協(xié)議感知代理。以下為結(jié)合主從復(fù)制對ProxySQL讀寫分離、黑白名單、路由規(guī)則等做些基本測試。
1.2 基本介紹
先簡單介紹下ProxySQL及其功能和配置，主要包括：
- 最基本的讀/寫分離，且方式有多種；
- 可定制基于用戶、基于schema、基于語句的規(guī)則對SQL語句進行路由，規(guī)則很靈活；
- 動態(tài)加載配置，即絕大部分的配置可以在線修改，但有少部分參數(shù)還是需要重啟來生效；
- 可緩存查詢結(jié)果。雖然緩存策略比較簡陋，但實現(xiàn)了基本的緩存功能；
- 過濾危險的SQL，增加防火墻等功能；
- 提供連接池、日志記錄、審計日志等功能；
1.2.1 請求流程
流量從客戶端發(fā)出 → ProxySQL進行處理轉(zhuǎn)發(fā) → 后端處理 → ProxySQL的前端連接 → 返回客戶端的基本流程
1.2.2 核心功能
- 讀寫分離：可查詢走從庫，寫入走主庫
- 簡單Sharding：ProxySQL的sharding是通過正則匹配來實現(xiàn)的，對于需要拆分SQL以及合并SQL執(zhí)行結(jié)果的不能支持，所以寫了簡單sharding
- 連接池管理：常規(guī)功能，為了提高SQL執(zhí)行效率。
- 多路復(fù)用：主要優(yōu)化點在后端mysql連接的復(fù)用，對比smart client，中間層不僅對前端建連也會對后端建連，可自行控制后端連接的復(fù)用邏輯。
- 流量管控：kill連接和kill query；whitelist配置。
- 高可用：底層mysql，如果從庫掛了，自動摘除流量；主庫掛了暫不處理。proxysql自身高可用提供cluster的功能，cluster內(nèi)部會自行同步元數(shù)據(jù)以及配置變更信息。
- 查詢緩存：對username+schema+query的key進行緩存，設(shè)置ttl過期，不適合寫完就查的場景，因為在數(shù)據(jù)在未過期之前可能是臟數(shù)據(jù)。
- 動態(tài)配置：大部分的配置可動態(tài)變更，先load到runtime，在save到disk，通過cluster的功能同步到其他的節(jié)點。
- 流量鏡像：同一份流量可以多出寫入，但是并不保證mirror的流量一定成功。
- SQL改寫：在query rules中配置replace規(guī)則，可以對指定的SQL進行改寫。
當(dāng)ProxySQL啟動后，將監(jiān)聽兩個端口：
(1).admin管理接口，默認端口為6032。該端口用于查看、配置ProxySQL。
(2).接收SQL語句的接口，默認端口為6033，這個接口類似于MySQL的3306端口。
ProxySQL的admin管理接口是一個使用MySQL協(xié)議的接口，所以，可以直接使用mysql客戶端、navicat等工具去連接這個管理接口
2、ProxySQL結(jié)構(gòu)
Qurey Processor 用于匹配查詢規(guī)則并根據(jù)規(guī)則決定是否緩存查詢或者將查詢加入黑名單或者重新路由、重寫查詢或者鏡像查詢到其他hostgroup。
User Auth 為底層后端數(shù)據(jù)庫認證提供了用戶憑證。
Hostgroup manager – 負責(zé)管理發(fā)送SQL請求都后端數(shù)據(jù)庫并跟蹤SQL請求狀態(tài)。
Connection pool – 負責(zé)管理后端數(shù)據(jù)庫連接，連接池中建立的連接被所有的前端應(yīng)用程序共享。
Monitoring – 負責(zé)監(jiān)控后端數(shù)據(jù)庫健康狀態(tài)主從復(fù)制延時并臨時下線不正常的數(shù)據(jù)庫實例。
1.啟動過程
RUNTIME層：代表的是ProxySQL當(dāng)前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。無法直接修改這里的配置，必須要從下一層load進來
MEMORY層：是平時在mysql命令行修改的 main 里頭配置，可以認為是SQLite數(shù)據(jù)庫在內(nèi)存的鏡像。該層級的配置在main庫中以mysql_開頭的表以及global_variables表，這些表的數(shù)據(jù)可以直接修改；
DISK|CONFIG FILR層：持久存儲的那份配置，一般在
$(DATADIR)/proxysql.db，在重啟的時候會從硬盤里加載。/etc/proxysql.cnf文件只在第一次初始化的時候用到，完了后，如果要修改監(jiān)聽端口，還是需要在管理命令行里修改，再 save 到硬盤。
注意：
- 如果找到數(shù)據(jù)庫文件（proxysql.db），ProxySQL 將從 proxysql.db 初始化其內(nèi)存中配置。因此，磁盤被加載到 MEMORY 中，然后加載到 RUNTIME 中。
- 如果找不到數(shù)據(jù)庫文件（proxysql.db）且存在配置文件（proxysql.cfg），則解析配置文件并將其內(nèi)容加載到內(nèi)存數(shù)據(jù)庫中，然后將其保存在 proxysql.db 中并在加載到 RUNTIME。 請務(wù)必注意，
- 如果找到 proxysql.db，則不會解析配置文件。也就是說，在正常啟動期間，ProxySQL 僅從持久存儲的磁盤數(shù)據(jù)庫初始化其內(nèi)存配置。
2.數(shù)據(jù)庫結(jié)構(gòu)
ProxySQL自身共有5個庫，分別為3個保存在內(nèi)存中的庫，和2個保存在磁盤的SQLite庫。 通過6032管理端口登入后，默認就是main庫，所有的配置更改都必須在這個庫中進行，disk存檔庫不會直接受到影響。
# mysql -uadmin -padmin -h127.0.0.1 -P6032 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.5.30 (ProxySQL Admin Module) mysql> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec)
main：內(nèi)存配置數(shù)據(jù)庫，表里存放后端db實例、用戶驗證、路由規(guī)則等信息。表名以 runtime開頭的表示proxysql當(dāng)前運行的配置內(nèi)容，不能通過dml語句修改，只能修改對應(yīng)的不以 runtime 開頭的（在內(nèi)存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盤以供下次重啟加載。
disk：是持久化到硬盤的配置，sqlite數(shù)據(jù)文件。SQLite3 數(shù)據(jù)庫，默認位置為 $（DATADIR）/proxysql.db,在重新啟動時，未保留的內(nèi)存中配置將丟失。因此，將配置保留在 DISK 中非常重要。(SQLite是一個進程內(nèi)的庫，實現(xiàn)了自給自足的、無服務(wù)器的、零配置的、事務(wù)性的 SQL 數(shù)據(jù)庫引擎)
stats：proxysql運行抓取的統(tǒng)計信息，包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時間等等。
monitor：庫存儲 monitor 模塊收集的信息，主要是對后端db的健康/延遲檢查。
stats_history:統(tǒng)計信息歷史庫
3.核心配置表
4.命令
5.小結(jié)
這些數(shù)據(jù)庫的功能實現(xiàn)了實用化內(nèi)容：
允許輕松動態(tài)更新配置，便于運維管理，與MySQL兼容的管理界面可用于此目的。
允許盡可能多的配置項目動態(tài)修改，而不需要重新啟動ProxySQL進程
可以毫不費力地回滾無效配置
通過多級配置系統(tǒng)實現(xiàn)的，其中設(shè)置從運行時移到內(nèi)存，并根據(jù)需要持久保存到磁盤
3、實驗環(huán)境
| 機器名稱 | IP配置 | 服務(wù)角色 | 備注 |
|---|---|---|---|
| proxy | 192.168.150.24 | proxysql控制器 | 用于監(jiān)控管理 |
| master | 192.168.150.21 | 數(shù)據(jù)庫主服務(wù)器 | |
| slave1 | 192.168.150.22 | 數(shù)據(jù)庫從服務(wù)器 | |
| slave2 | 192.168.150.23 | 數(shù)據(jù)庫從服務(wù)器 |
4、實現(xiàn)數(shù)據(jù)庫主從復(fù)制
基于GTID實現(xiàn)mysql8.0主從同步，配置過程略。
5、安裝ProxySQL
# 配置yum源 cat > /etc/yum.repos.d/proxysql.repo << EOF [proxysql] name=ProxySQL YUM repository baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/centos/\$releasever gpgcheck=1 gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key EOF # 安裝proxysql yum install -y proxysql
啟動 ProxySQL
[root@proxy ~]# systemctl enable --now proxysql # 管理員登錄 [root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec) 可見有五個庫： main、disk、stats 、monitor 和 stats_history main: 內(nèi)存配置數(shù)據(jù)庫，即 MEMORY，表里存放后端 db 實例、用戶驗證、路由規(guī)則等信息。main 庫中有如下信息： MySQL [(none)]> show tables from main; +----------------------------------------------------+ | tables | +----------------------------------------------------+ | coredump_filters | | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_firewall_whitelist_rules | | mysql_firewall_whitelist_sqli_fingerprints | | mysql_firewall_whitelist_users | | mysql_galera_hostgroups | | mysql_group_replication_hostgroups | | mysql_hostgroup_attributes | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | restapi_routes | | runtime_checksums_values | | runtime_coredump_filters | | runtime_global_variables | | runtime_mysql_aws_aurora_hostgroups | | runtime_mysql_firewall_whitelist_rules | | runtime_mysql_firewall_whitelist_sqli_fingerprints | | runtime_mysql_firewall_whitelist_users | | runtime_mysql_galera_hostgroups | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_hostgroup_attributes | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_restapi_routes | | runtime_scheduler | | scheduler | +----------------------------------------------------+ 36 rows in set (0.00 sec) 庫下的主要表： mysql_servers: 后端可以連接 MySQL 服務(wù)器的列表 mysql_users: 配置后端數(shù)據(jù)庫的賬號和監(jiān)控的賬號。 mysql_query_rules: 指定 Query 路由到后端不同服務(wù)器的規(guī)則列表。 注： 表名以 runtime_開頭的表示 ProxySQL 當(dāng)前運行的配置內(nèi)容，不能通過 DML 語句修改。 只能修改對應(yīng)的不以 runtime 開頭的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盤以供下次重啟加載。 disk ：持久化的磁盤的配置 stats： 統(tǒng)計信息的匯總 monitor：一些監(jiān)控的收集信息，比如數(shù)據(jù)庫的健康狀態(tài)等 stats_history: 這個庫是 ProxySQL 收集的有關(guān)其內(nèi)部功能的歷史指標(biāo)
配置 ProxySQL 所需賬戶
在 Master (172.16.100.21) 的MySQL 上創(chuàng)建 ProxySQL 的監(jiān)控賬戶和對外訪問賬戶
create user 'monitor'@'172.16.%.%' identified with mysql_native_password by 'Monitor@123.com'; grant all privileges on *.* to 'monitor'@'172.16.%.%' with grant option; #proxysql 的對外訪問賬戶 create user 'proxysql'@'172.16.%.%' identified with mysql_native_password by '123456'; grant all privileges on *.* to 'proxysql'@'172.16.%.%' with grant option;
6、配置ProxySQL
使用proxysql，主要需要完成以下幾項內(nèi)容的配置：
- 配置監(jiān)控賬號。監(jiān)控賬號用于檢測后端mysql實例是否健康（是否能連接、復(fù)制是否正常、復(fù)制是否有延遲等）。
- 到后端mysql實例創(chuàng)建監(jiān)控賬號。
- 配置后端mysql實例連接信息。實例連接信息存儲在mysql_servers表。
- 配置連接proxysql和后端實例的賬號。賬號信息存儲在mysql_users表。
- 配置查詢路由信息。路由信息存儲在mysql_query_rules表。
- 配置后端mysql集群信息。根據(jù)后端mysql集群架構(gòu)，配置分別存儲在mysql_replication_hostgroups、mysql_group_replication_hostgroups、runtime_mysql_galera_hostgroups、runtime_mysql_aws_aurora_hostgroups等表中。
- 根據(jù)具體需要，調(diào)優(yōu)相關(guān)參數(shù)。參數(shù)存儲在global_variables表。
一、配置ProxySQL主從分組信息
1.用到這個表：mysql_replication_hostgroup，表結(jié)構(gòu)如下：
MySQL [(none)]> show create table mysql_replication_hostgroups \G
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only','read_only|innodb_read_only','read_only&innodb_read_only')) NOT NULL DEFAULT 'read_only',
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)
注：writer_hostgroup 和reader_hostgroup 寫組和讀組都要大于等于0且不能相同2.創(chuàng)建組：(定義寫為1，讀為0)
MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy'); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.02 sec) 注意：ProxySQL會根據(jù)server的read_only的取值將服務(wù)器進行分組。read_only=0的server，master被分到編號為1的寫組，read_only=1的server，slave則分到編號為0的讀組 MySQL [(none)]> select * from mysql_replication_hostgroups; +------------------+------------------+------------+---------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+---------+ | 1 | 0 | read_only | proxy | +------------------+------------------+------------+---------+ 1 row in set (0.00 sec)
3.添加主從服務(wù)器節(jié)點：
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'172.16.100.21',3306); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.22',3306); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'172.16.100.23',3306); Query OK, 1 row affected (0.00 sec) MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.00 sec) MySQL [(none)]> select * from mysql_servers; +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 192.168.150.21 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.22 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 0 | 192.168.150.23 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
4.為ProxySQL監(jiān)控MySQL后端節(jié)點
MySQL [(none)]> use monitor 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 [monitor]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.00 sec) MySQL [monitor]> set mysql-monitor_password='Monitor@123.com'; Query OK, 1 row affected (0.00 sec) 上面這兩句是修改變量的方式還可以在main庫下面用sql語句方式修改 在main下修改： MySQL [monitor]> use main 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 [main]> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Query OK, 1 row affected (0.00 sec) MySQL [main]> UPDATE global_variables SET variable_value='Monitor@123.com' WHERE variable_name='mysql-monitor_password'; Query OK, 1 row affected (0.00 sec) 修改后，保存到runtime和disk MySQL [monitor]> load mysql variables to runtime; MySQL [monitor]> save mysql variables to disk; 查看監(jiān)控賬號【ProxySQL】 SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%'; //也可以這樣快速定位 MySQL [(none)]> select @@mysql-monitor_username; +--------------------------+ | @@mysql-monitor_username | +--------------------------+ | monitor | +--------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select @@mysql-monitor_password; +--------------------------+ | @@mysql-monitor_password | +--------------------------+ | Monitor@123.com | +--------------------------+ 1 row in set (0.00 sec) MySQL [(none)]> select * from monitor.mysql_server_connect_log; +----------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +----------------+------+------------------+-------------------------+---------------+ | 192.168.150.21 | 3306 | 1701659547601785 | 2126 | NULL | | 192.168.150.23 | 3306 | 1701659548266990 | 1883 | NULL | | 192.168.150.22 | 3306 | 1701659548932828 | 1626 | NULL | | 192.168.150.21 | 3306 | 1701659607601353 | 1672 | NULL | | 192.168.150.22 | 3306 | 1701659608066386 | 1649 | NULL | | 192.168.150.23 | 3306 | 1701659608531392 | 1962 | NULL | | 192.168.150.21 | 3306 | 1701659667601720 | 1283 | NULL | | 192.168.150.23 | 3306 | 1701659668394216 | 2033 | NULL | | 192.168.150.22 | 3306 | 1701659669186458 | 1350 | NULL | +----------------+------+------------------+-------------------------+---------------+ 9 rows in set (0.00 sec) 驗證監(jiān)控信息，ProxySQL 監(jiān)控模塊的指標(biāo)都保存在monitor庫的log表中 以下是連接是否正常的監(jiān)控，對connect指標(biāo)的監(jiān)控 ，在前面可能會有很多connect_error，這是因為沒有配置監(jiān)控信息時的錯誤，配置后如果connect_error的結(jié)果為NULL則表示正常
5.對心跳信息的監(jiān)控：
MySQL [(none)]> select * from mysql_server_ping_log limit 10; +----------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +----------------+------+------------------+----------------------+------------+ | 192.168.150.21 | 3306 | 1701659547602344 | 460 | NULL | | 192.168.150.23 | 3306 | 1701659547604095 | 245 | NULL | | 192.168.150.22 | 3306 | 1701659547604690 | 258 | NULL | | 192.168.150.23 | 3306 | 1701659557601548 | 471 | NULL | | 192.168.150.21 | 3306 | 1701659557601738 | 313 | NULL | | 192.168.150.22 | 3306 | 1701659557601713 | 349 | NULL | | 192.168.150.22 | 3306 | 1701659567602161 | 572 | NULL | | 192.168.150.23 | 3306 | 1701659567602361 | 405 | NULL | | 192.168.150.21 | 3306 | 1701659567602318 | 460 | NULL | | 192.168.150.22 | 3306 | 1701659577602909 | 434 | NULL | +----------------+------+------------------+----------------------+------------+ 10 rows in set (0.00 sec)
6.查看read_only日志監(jiān)控：
MySQL [(none)]> select * from mysql_server_read_only_log limit 5; +----------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +----------------+------+------------------+-----------------+-----------+-------+ | 192.168.150.23 | 3306 | 1701659547606174 | 373 | 1 | NULL | | 192.168.150.22 | 3306 | 1701659547606768 | 634 | 1 | NULL | | 192.168.150.21 | 3306 | 1701659547606639 | 858 | 0 | NULL | | 192.168.150.21 | 3306 | 1701659549101574 | 657 | 0 | NULL | | 192.168.150.22 | 3306 | 1701659549101863 | 422 | 1 | NULL | +----------------+------+------------------+-----------------+-----------+-------+ 5 rows in set (0.00 sec) Monitor 模塊就會開始監(jiān)控后端的read_only值，當(dāng)監(jiān)控到read_only值，就會按照read_only的值將某些節(jié)點自動移到讀寫組 一些監(jiān)控的狀態(tài)斗志在log相關(guān)，都在monitor庫下面的 global_variables 變量。
7.ProxySQL配置對外訪問賬號：（要在MySQL節(jié)點上創(chuàng)建）
前面已經(jīng)配置：配置ProxySQL 賬戶，我創(chuàng)建的對外訪問賬戶是：用戶：proxysql，密碼：123456
mysql_users表結(jié)構(gòu)如下：
MySQL [(none)]> show create table mysql_users\G
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
attributes VARCHAR CHECK (JSON_VALID(attributes) OR attributes = '') NOT NULL DEFAULT '',
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.00 sec)
將對外訪問賬號添加到mysql_users表中：
insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
load mysql users to runtime;
save mysql users to disk;
MySQL [(none)]> select * from mysql_users\G
*************************** 1. row ***************************
username: proxysql
password: 123456
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
attributes:
comment:
1 row in set (0.00 sec)
注：transaction_persistent 如果為1，則一個完整的SQL只可能路由到一個節(jié)點；這點非常重要，主要解決這種情況：一個事務(wù)有混合的讀操作和寫操作組成，事務(wù)未提交前，如果事務(wù)中的讀操作和寫操作路由到不同節(jié)點，那么讀取到的結(jié)果必然是臟數(shù)據(jù)。所以一般情況下，該值應(yīng)該設(shè)置為1，尤其是業(yè)務(wù)中使用到事務(wù)機制的情況（默認為0）
mysql_users 表有不少字段，最主要的三個字段username,password,default_hostgroup
A.username: 前端鏈接ProxySQL ，以及ProxySQL 將SQL 語句路由給MySQL所使用的的用戶名
B.password：用戶名對應(yīng)的密碼，?？梢允敲魑拿艽a，也可以是hash密碼。如果想使用hash密碼，可以先在某個MySQL節(jié)點上執(zhí)行select password(PASSWORD)，然后將加密結(jié)果復(fù)制到該字段。
C.default_hostgroup：該用戶名默認的路由目標(biāo)。例如，指定root用戶的該字段值為1時，則使用 proxysql 用戶發(fā)送的SQL語句默認情況下將路由到hostgroup_id=1 組中的某個在從庫端172.16.100.22上通過對方訪問賬號proxy連接，測試是否路由能默認到hostgroup_id=1，它是一個寫組
[root@slave1 ~]# mysql -h172.16.100.24 -uproxysql -p'123456' -P 6033 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.01 sec) mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 21 | +-------------+ 1 row in set (0.00 sec) #通過proxysql用戶，創(chuàng)建一個keme庫 mysql> create database keme; Query OK, 1 row affected (0.00 sec)
在slave2：172.16.100.23 上去驗證一下，是否同步過去keme這個庫
mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 23 | +-------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | db1 | | information_schema | | keme | | mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.01 sec)
8.添加讀寫分離規(guī)則(mysql_query_rules)
proxysql支持正則，這里添加兩條匹配規(guī)則
1) 表示像select * from xxx for update這種語句都會分到到寫組
2）表示像select這種語句都會被分配到讀組。
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1); insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(2,1,'^select',0,1); load mysql query rules to runtime; save mysql query rules to disk;
9.測試讀寫分離
[root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "select @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "select @@server_id" mysql: [Warning] Using a password on the command line interface can be insecure. +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ [root@slave1 ~]# mysql -uproxysql -p123456 -h 172.16.100.24 -P 6033 -e "begin;select @@server_id commit;" mysql: [Warning] Using a password on the command line interface can be insecure. +--------+ | commit | +--------+ | 1 | +--------+
到此這篇關(guān)于MySQL主從同步的幾種實現(xiàn)方式的文章就介紹到這了,更多相關(guān)MySQL主從同步內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要給大家介紹了MySQL中聯(lián)表更新與刪除的語法,文中通過示例代碼介紹的很詳細，有需要的朋友可以參考借鑒，下面來跟著小編一起學(xué)習(xí)學(xué)習(xí)吧。2017-01-01
在向表中插入數(shù)據(jù)的時候，經(jīng)常遇到這樣的情況：1. 首先判斷數(shù)據(jù)是否存在； 2. 如果不存在，則插入；3.如果存在，則更新。2014-06-06
這篇文章主要介紹了MySQL存儲結(jié)構(gòu)用法,結(jié)合具體案例形式分析了mysql存儲結(jié)構(gòu)相關(guān)使用方法與操作注意事項,需要的朋友可以參考下2018-07-07
本文介紹了兩種mysql對自增id重新從1排序的方法，簡少了對于某個項目初始化數(shù)據(jù)的工作量，感興趣的朋友可以參考下2015-07-07
這篇文章主要給大家介紹了關(guān)于mysql索引失效的情況，文中通過圖文介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-03-03
這篇文章主要介紹了MySQL定時備份數(shù)據(jù)庫操作,結(jié)合實例形式分析了MySQL定時備份數(shù)據(jù)庫相關(guān)命令、原理、實現(xiàn)方法及操作注意事項,需要的朋友可以參考下2020-03-03
解決mysql報錯:Data?source?rejected?establishment?of?connect
這篇文章主要給大家介紹了關(guān)于如何解決mysql報錯:Data?source?rejected?establishment?of?connection,?message?from?server:?\"Too?many?connectio的相關(guān)資料,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下2023-02-02
mysql8.0.11數(shù)據(jù)目錄遷移的實現(xiàn)
這篇文章主要介紹了mysql8.0.11數(shù)據(jù)目錄遷移的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
MySQL 數(shù)據(jù)庫雙向鏡像、循環(huán)鏡像(復(fù)制)
在MySQL數(shù)據(jù)庫鏡像的貼子中，主數(shù)據(jù)庫A 的數(shù)據(jù)鏡像到從數(shù)據(jù)庫B，是單向的，Zen Cart網(wǎng)店的數(shù)據(jù)讀寫都必須在數(shù)據(jù)庫A進行，結(jié)果會自動鏡像到數(shù)據(jù)庫B中。但是對數(shù)據(jù)庫B的直接操作，不會影響數(shù)據(jù)庫A。2011-05-05
本文章將介紹什么是通配符、如何使用通配符以及怎樣使用LIKE操作符進行通配搜索，以便對數(shù)據(jù)進行復(fù)雜過濾，感興趣的小伙伴跟著小編一起來學(xué)習(xí)吧2023-07-07