配置MySQL主從複製和讀寫分離

来源:https://www.cnblogs.com/connect/archive/2019/05/21/mysql-master-slave-copy-and-read-write-separation.html
-Advertisement-
Play Games

配置MySQL的主從複製與讀寫分離。MySQL主庫負責數據寫入和數據修改的操作，MySQL從庫負責資料庫讀取任務的操作。在此基礎上通過配置mysql-proxy進行MySQL資料庫的讀寫分離，將讀資料庫請求轉發到從庫伺服器，將寫操作和修改操作轉發到主庫伺服器。 ...


實驗環境

序號 主機名 IP地址 備註
1 mysql-master 192.168.204.201 MySQL主庫
2 mysql-slave 192.168.204.202 MySQL從庫
3 appserver 192.168.204.111 應用伺服器

安裝配置MySQL資料庫

1.使用yum安裝mysql和mysql-server

yum install -y mariadb mariadb-server

2.啟動mysql服務

systemctl start mariadb
systemctl enable mariadb

3.查看啟動狀態

systemctl status mariadb
netstat -anpt | grep "mysql" --color

4.允許3306埠通過防火牆

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload

5.設置MySQL密碼

mysql_secure_installation

6.在mysql-master上創建資料庫

使用root用戶登錄MySQL

mysql -uroot -p123456

創建資料庫並添加數據

create database db_test;
show databases;
use db_test;

create table if not exists user_info(
    username varchar(16) not null,
    password varchar(32) not null,
    realname varchar(16) default '',
    primary key (username)
)default charset=utf8;
show tables;

insert into user_info(username, password, realname) values
('10001', '123456', '小明'),
('10002', '123456', '小紅'),
('10003', '123456', '小王'),
('10004', '123456', '小張'),
('10005', '123456', '小李');

select * from user_info where 1;

在mysql-master上授權資料庫訪問許可權

GRANT all ON db_test.* TO 'admin'@'%' identified BY '123456';
flush privileges;

在mysql-slave、appserver上登錄mysql-master資料庫

mysql -h 192.168.204.201 -uroot -p123456

在mysql-master上撤銷資料庫訪問許可權

REVOKE all ON db_test.* FROM 'admin'@'%';
flush privileges;

配置master和slave兩台mysql伺服器的主從複製

1.在master資料庫上啟用binlog日誌，建立從庫賬號rep

查看binlog日誌狀態

show variables like 'log_bin';

更改my.cnf配置文件

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

重啟MySQL，查看binlog日誌

systemctl restart mariadb
mysql -uroot -p123456 -e "show variables like 'log_bin';"
mysql -uroot -p123456 -e "show master status;"

記住此處File和Position的值

建立從庫賬號

grant replication slave on *.* to rep@'192.168.204.202' identified by '123456';
show grants for rep@'192.168.204.%';

2.在master資料庫上備份現有資料庫

對master資料庫鎖表

flush tables with read lock;

備份master資料庫

mysqldump -uroot -p123456 --all-databases | gzip > /root/database_`date '+%Y-%m-%d'`.sql.gz

將備份文件拷貝至slave

scp database_*.sql.gz [email protected]:/root

3. 配置slave資料庫，在slave上恢複數據庫

配置slave資料庫server-id，關閉binlog日誌

cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
# vim /etc/my.cnf

#log-bin=mysql-bin
#binlog_format=mixed
server-id       = 2

4.重啟slave的mysql

重啟mysql服務

systemctl restart mariadb

查看log_bin和server_id的值

show variables like 'log_bin';
show variables like 'server_id';

5.將數據恢復至slave

gzip -d /root/database_*.sql.gz
mysql -uroot -p123456 < /root/database_*.sql
mysql -uroot -p123456 -e "show databases;"

6.在slave資料庫上配置複製參數

在slave上配置複製參數
將MASTER_LOG_FILE和MASTER_LOG_POS的值替換成上述master上查詢的值

change master to
MASTER_HOST='192.168.204.201',
MASTER_USER='rep',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=245;

在slave上配置啟用複製

start slave;

在slave上查看複製狀態

show slave status \G;

兩個均為Yes即可

重啟master和slave的mysql服務

systemctl restart mariadb

在master上為資料庫db_test增加記錄，在slave查看同步情況

-- mysql-master
insert into db_test.user_info (username, password, realname) values ('20001', '123456', 'Tom');
select * from db_test.user_info where 1;

-- mysql-slave
select * from db_test.user_info where 1;

已經實現了主從複製

在appserver上配置mysql讀寫分離

1.在appserver上安裝mysql-proxy

從https://downloads.mysql.com/archives/proxy/下載mysql-proxy

cd ~
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
tar -xzvf mysql-proxy-0.8.5-linux-sles11-x86-64bit.tar.gz
cp -r mysql-proxy-0.8.5-linux-sles11-x86-64bit /usr/local/mysql-proxy

2.在appserver上配置mysql-proxy

創建主配置文件

cd /usr/local/mysql-proxy
mkdir lua        #創建腳本存放目錄
mkdir logs       #創建日誌目錄
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/  #複製讀寫分離配置文件
cp share/doc/mysql-proxy/admin-sql.lua ./lua/     #複製管理腳本
vim /etc/mysql-proxy.cnf     #創建配置文件

主配置文件內容

使用前，請去掉註釋

#vim /etc/mysql-proxy.cnf

[mysql-proxy]
user=root #運行mysql-proxy用戶
admin-username=myproxy #主從mysql共有的用戶
admin-password=123456 #用戶的密碼
proxy-address=127.0.0.1:3306 #mysql-proxy運行ip和埠，不加埠預設4040
proxy-read-only-backend-addresses=192.168.204.202 #指定後端從slave讀取數據
proxy-backend-addresses=192.168.204.201 #指定後端master寫入數據
proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua #指定讀寫分離配置文件位置
admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql/lua #指定管理腳本
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log #日誌位置
log-level=info #定義log日誌級別，由高到低分別(error|warning|info|message|debug)

修改許可權

chmod 660 /etc/mysql-proxy.cnf

3.在appserver上修改讀寫分離配置文件

vim /usr/local/mysql-proxy/lua/rw-splitting.lua

修改以下內容

--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,  -- 預設超過4個連接數時才開始讀寫分離
                max_idle_connections = 1,  -- 預設為8

                is_debug = false
        }
end

4.在appserver上啟動mysql-proxy

啟動

/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon

查看進程

netstat -anpt | grep 3306

5.在mysql-master和mysql-slave上分別給myproxy授權

在mysql-master和mysql-slave上授權給mysql-proxy

grant all on *.* to 'myproxy'@'192.168.204.%' identified by '123456';
flush privileges;

6.在appserver上連接mysql-proxy，測試讀寫分離

在appserver上通過mysql-proxy操作資料庫

mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "insert into db_test.user_info (username, password, realname) values ('30001', '123456', 'Jack');"
mysql -h 127.0.0.1 -umyproxy -p123456 -e "select * from db_test.user_info where 1;"

在mysql-master上查詢

mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

在mysql-master上查詢

mysql -uroot -p123456 -e "select * from db_test.user_info where 1;"

經驗證，已實現讀寫分離


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • （2）Asp.Net Core 依賴關係註入（服務）
    1.前言 面向對象設計（OOD）里有一個重要的思想就是依賴倒置原則（DIP），並由該原則牽引出依賴註入（DI）、控制反轉（IOC）及其容器等老生常談的概念，初學者很容易被這些概念搞暈（包括我在內），在學習Core依賴註入服務之前，下麵讓我們先瞭解下依賴倒置原則（DIP）、依賴註入（DI）、控制反轉（ ...
  • Curl-用法
    1 -x 指定訪問IP與埠號curl -x 192.168.4.12:80 http://www.jackyops.com2 響應時長 curl -o /dev/null -s -w "time_connect: %{time_connect}\ntime_starttransfer: %{tim ...
  • 特殊許可權SUID
    特殊許可權SUID SUID : 運行某程式時，相應進程的屬主是程式文件自身的屬主，而不是啟動者： chmod u+s File chmod u-s File 如果 FileB本身原來就有執行許可權，則SUID顯示為s；否則顯示S SGID : 運行某程式時，相應進程的屬組是程式文件自身的屬組，而不是啟 ...
  • 創建服務開機自啟文件
    eg： vim /lib/systemd/system/nginx.service [Unit] Description=nginx After=network.target [Service] Type=forking ExecStart=/usr/local/nginx/sbin/nginx # ...
  • 微信授權錯誤："errcode":40163,"errmsg":"codebeenused
    轉自：微信授權錯誤："errcode":40163,"errmsg":"codebeenused 微信網頁授權獲取code值回調兩次的問題 1.說是功能變數名稱原因，目前未測試，沒有正確的功能變數名稱 問題描述：在調用微信網頁授權獲取openid值時，先獲取的code值，但是code值的介面 會走兩次回調。而cod ...
  • 配置keepalived支持nginx高可用
    配置KeepAlive支持雙節點nginx高可用。本文從nginx的安裝、keepalived的安裝與配置、nginx-keepalived高可用等三個過程進行實驗。 ...
  • Linux從入門到精通總結
    文章大綱 一、課程內容總結二、課程學習地址三、學習資料下載四、參考文章 一、課程內容總結 二、課程學習地址 第一天：https://www.cnblogs.com/WUXIAOCHANG/p/10893343.html第二天：https://www.cnblogs.com/WUXIAOCHANG/p ...
  • Yum項目上線實戰 （網站運維）-Linux從入門到精通第十一天
    文章大綱 一、編譯安裝與卸載Nginx二、關於LAMP三、LAMP環境部署四、學習資料下載五、參考文章 一、編譯安裝與卸載Nginx Nginx：是一款比較流行的web伺服器軟體，類似於Apache。 1. Ngnix基礎知識與Windows版本安裝 https://www.cnblogs.com/ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類