ProxySQL讀寫分離

来源:http://www.cnblogs.com/shengdimaya/archive/2017/07/11/7149854.html
-Advertisement-
Play Games

我們首先看一下自己的環境： MHA已經搭建： master：172.16.16.35:3306 slave：172.16.16.35:3307 slave：172.16.16.34:3307 MHA manager在172.16.16.34，配置文件如下： MHA manager在172.16.16 ...


我們首先看一下自己的環境： MHA已經搭建： 
master：172.16.16.35:3306
slave：172.16.16.35:3307
slave：172.16.16.34:3307

MHA manager在172.16.16.34，配置文件如下：

[root@localhost bin]# cat /etc/masterha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1.log
master_binlog_dir=/home/mysql/db3306/log/
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=root
report_script=/usr/local/bin/send_report
shutdown_script=""
ssh_user=root
user=root
[server1]
hostname=172.16.16.35
port=3306
[server2]
candidate_master=1
check_repl_delay=0
hostname=172.16.16.34
port=3306
[server3]
hostname=172.16.16.35
port=3307

 

下麵我們基於這樣一套MHA環境搭建讀寫分離。 1：安裝ProxySQL軟體，這個我們部署到172.16.16.34上 
[root@localhost bin]# sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
[root@localhost bin]# yum install proxysql

 

最後有以下提示： 
Installed:
proxysql.x86_64 0:1.3.7-1.1.el6
Complete!

 

也就是安裝完成了。然後查一下具體的文件： 
[root@localhost bin]# find / -name proxysql
/var/lib/proxysql
/var/run/proxysql
/etc/rc.d/init.d/proxysql
/usr/bin/proxysql

 

發現確實已經將ProxySQL安裝成功了 2：啟動配置ProxySQL 看一下配置文件： 
[root@localhost bin]# cat /etc/proxysql-admin.cnf
# proxysql admin interface credentials.
export PROXYSQL_USERNAME="admin"
export PROXYSQL_PASSWORD="admin"
export PROXYSQL_HOSTNAME="localhost"
export PROXYSQL_PORT="6032"
 
# PXC admin credentials for connecting to pxc-cluster-node.
export CLUSTER_USERNAME="admin"
export CLUSTER_PASSWORD="admin"
export CLUSTER_HOSTNAME="localhost"
export CLUSTER_PORT="3306"
 
# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.
export MONITOR_USERNAME="monitor"
export MONITOR_PASSWORD="monit0r"
 
# Application user to connect to pxc-node through proxysql
export CLUSTER_APP_USERNAME="proxysql_user"
export CLUSTER_APP_PASSWORD="passw0rd"
 
# ProxySQL read/write hostgroup
export WRITE_HOSTGROUP_ID="10"
export READ_HOSTGROUP_ID="11"
 
# ProxySQL read/write configuration mode.
export MODE="singlewrite"

 

啟動： 
[root@localhost bin]# proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
This script will assist with configuring ProxySQL (currently only Percona XtraDB cluster in combination with ProxySQL is supported)
ProxySQL read/write configuration mode is singlewrite
ProxySQL is not running; please start the proxysql service

 

現在來說ProxySQL 的路由已經啟動，提示我們要啟動proxysql service 
[root@localhost bin]# service proxy
proxy proxysql proxysql-admin proxysql_galera_checker proxysql_node_monitor
[root@localhost bin]# service proxysql start
Starting ProxySQL: DONE!
[root@localhost bin]# 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 1
Server version: 5.7.14 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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>

 

可以看到我們已經登錄成功了，這裡要說明的是 proxysql的預設配置文件是在： 
[root@localhost bin]# find / -name proxysql.cnf
/etc/proxysql.cnf

 

接下來我們開始配置ProxySQL： 
[root@localhost bin]# 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.7.14 (ProxySQL Admin Module)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
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;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)
 
mysql> use admin
Database changed
mysql> show tables;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| mysql_collations |
| mysql_query_rules |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| runtime_global_variables |
| runtime_mysql_query_rules |
| runtime_mysql_replication_hostgroups |
| runtime_mysql_servers |
| runtime_mysql_users |
| runtime_scheduler |
| scheduler |
+--------------------------------------+
13 rows in set (0.00 sec)

 

下麵加入主從信息： 
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(100,'172.16.16.35',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.34',3306,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,max_connections,max_replication_lag,comment) values(101,'172.16.16.35',3307,1,1000,10,'test');
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 100 | 172.16.16.35 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.34 | 3306 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
| 101 | 172.16.16.35 | 3307 | ONLINE | 1 | 0 | 1000 | 10 | 0 | 0 | test |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

 

然後添加讀寫映射（主要是MHA後端切換的時候保證ProxySQL也能夠自動切換）： 
mysql> insert into mysql_replication_hostgroups values(100,101,'masterha') ;
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+----------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+----------+
| 100 | 101 | masterha |
+------------------+------------------+----------+
1 row in set (0.00 sec)

 

為ProxySQL添加監控賬號： 
mysql> GRANT SUPER, REPLICATION CLIENT ON *.* TO 'proxysql'@'172.16.16.%' IDENTIFIED BY 'proxysql';
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.10 sec)

 

配置監控賬號（在proxySQL當中進行配置）： 
mysql> set mysql-monitor_username='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> set mysql-monitor_password='proxysql';
Query OK, 1 row affected (0.00 sec)
mysql> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql variables to disk;
Query OK, 74 rows affected (0.02 sec)

 

PS:有時候runtime_mysql_servers的status不為ONLINE狀態的話可以通過查看monitor.mysql_server_ping_log這個表來查看具體的報錯信息。 mysql> select * from monitor.mysql_server_ping_log; 然後配置程式賬號，簡單點統一使用root：123456最高許可權來配置： 
mysql> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','123456',1,100,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| root | 123456 | 1 | 0 | 100 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

 

配置完成以後開始重新載入和保存我們的配置： 
mysql> load mysql servers to runtime;
Query OK, 0 rows affected (0.01 sec)
mysql> save mysql servers to disk;
Query OK, 0 rows affected (0.08 sec)
mysql> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
mysql> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

 

接下來開始配置路由規則： 
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT.*FOR UPDATE$',100,1);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO mysql_query_rules(active,match_pattern,destination_hostgroup,apply) VALUES(1,'^SELECT',101,1);
Query OK, 1 row affected (0.00 sec)
mysql> LOAD MYSQL QUERY RULES TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)
mysql> SAVE MYSQL QUERY RULES TO DISK;
Query OK, 0 rows affected (0.04 sec)

 

至此配置已經完成了 3：測試讀寫分離 在172.16.16.35上鏈接proxySQL埠6033，並且做簡單的select操作： 
[root@localhost ~]# mysql -uroot -p123456 -h172.16.16.34 -P6033
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 22
Server version: 5.7.14 (ProxySQL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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> select * from maxiangqian.test;
+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+
5 rows in set (0.04 sec)

 

然後在進行查詢看一下： 
mysql> select * from stats_mysql_query_digest;

可以看到已經完成了讀寫分離了。 
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 353307 |
+-------------+
1 row in set (0.01 sec)

 

查看一下server ID已經路由到了172.16.16.35:3307這個從庫上了。 測一下for update： 
mysql> select * from maxiangqian.test for update;
+-----+------+
| id | name |
+-----+------+
| 1 | qq |
| 2 | qq |
| 4 | aa |
| 11 | a |
| 111 | a |
+-----+------+
5 rows in set (0.00 sec)

 

再查看 
mysql> select * from stats_mysql_query_digest;

已經自動路由到了主庫。 至此ProxySQL+MySQL MHA讀寫分離測試完成。。。未完待續 5：關於ProxySQL的思考以及簡單的命令 ProxySQL通過以上方式是可以實現讀寫分離，但是這種方式真的就沒有問題了嗎，如果是一些比如查詢訂單狀態的這種要求實時性非常高的SQL的話，似乎被路由到了從庫就會出現BUG。我們可以選擇在程式端控制這些參數，ProxySQL只作為一個負載均衡來使用，給ProxySQL創建多個賬號，一個讀寫，一個只讀。然後程式去實現讀寫分離。 ProxySQL是分三層來設計運行的，分別為RUNTIME ，MEMORY ，DISK ： RUNTIME 代表的是ProxySQL當前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。無法直接修改這裡的配置，必須要從下一層load進來。 MEMORY 是平時在mysql命令行修改的 main 裡頭配置，可以認為是SQLite資料庫在記憶體的鏡像 DISK / CONFIG FILE 持久存儲的那份配置，一般在$(DATADIR)/proxysql.db，在重啟的時候會從硬碟裡加載。 /etc/proxysql.cnf文件只在第一次初始化的時候用到，完了後，如果要修改監聽埠，還是需要在管理命令行里修改，再 save 到硬碟 常用命令： LOAD MYSQL SERVERS TO RUNTIME -- 讓修改的配置生效，也就是從MEMORY 把參數LOAD過來，等價於LOAD MYSQL USERS FROM MEMORY，這個語句的語法比較單間，FROM代表從上層LOAD過來，TO代表從本層到某一個層。比如前面我們設置了MySQL的監控賬號，但是還是要執行LOAD和SAVE保存變數並且使變數生效。

 


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

-Advertisement-
Play Games
更多相關文章
  • 更新下載庫update絕對詳解
    下載更新apk，基本上每個app都需要的功能，來看看吧，肯定有你想要的，以前都是自己寫，近期想藉助第三方的一個庫來做，功能齊全，感覺不錯，記錄使用過程，雖然官方也有使用教程，不過畢竟粗略，網上也能搜到，不過基本都是複製的 首先下載庫，地址改成我們自己的，檢查地址就讓它了，這個根據自己的業務調整，也能 ...
  • NDK配置debug環境時：Error:FAILURE: Build failed with an exception
    Error:FAILURE: Build failed with an exception. * What went wrong: Execution failed for task ':app:externalNativeBuildDebug'. > Build command failed. E ...
  • 安卓入門2
    使用Intent在活動間穿梭（Intent不僅可以指明當前組件想要執行的動作，還可以在不同組件之間傳遞數據） 1、使用顯式Intent 基於安卓入門1的內容，繼續在ActivityTest項目中再創建一個活動。右擊com.example.administrator.activitytest包->Ne ...
  • OC 小代碼塊
    1、設置導航欄標題的字體顏色和大小 方法一：（自定義視圖的方法，一般人也會採用這樣的方式） 就是在導航向上添加一個titleView，可以使用一個label，再設置label的背景顏色透明，字體什麼的設置就很簡單了。 //自定義標題視圖 UILabel *titleLabel = [[UILabel ...
  • Activity創建時佈局文件的實現原理
    setContenView（R.id.activity）實現原理 1.底層框架根據佈局ID找到佈局文件。 2.底層框架解析此佈局文件（pull解析）。 3.底層框架通過反射構建佈局文件中的元素對象（EditText，TextView等）。 4.底層框架會將元素對象（view）放到Activity中。 ...
  • 【代碼筆記】iOS-使用MD5加密
    一，代碼。 二，輸出。 ...
  • 【SqlServer系列】表連接
    1 概述 1 概述 1.1 已發佈【SqlServer系列】文章 【SqlServer系列】SQLSERVER安裝教程 【SqlServer系列】資料庫三大範式 【SqlServer系列】表單查詢 1.2 本篇文章內容概要 1.3 本篇文章內容概括 在SQL語句中，關於表連接，若按照表的數量來劃分， ...
  • MySQL手冊
    MySQL配置文件 MySQL軟體使用的配置文件名為my.ini，在安裝目錄下。 MySQL常用配置參數： 1.default-character-set：客戶端預設字元集。 2.character-set-server：伺服器端預設字元集。 3.port：客戶端和伺服器端的埠號。 4.defau ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類