技術文章 » ProxySql實現mysql讀寫分離+負載均衡

ProxySql實現mysql讀寫分離+負載均衡

2020-08-08 12:43:53

一 試驗環境

角色

IP

主機名

埠

備註1

備註2

Proxysql

192.168.1.103

pc3

admin管理埠6032;

接收sql語句的埠6033

 

 

master

192.168.1.101

pc1

 

3307

 

普通主從複製架構。

雙向複製。keepalive浮動ip是192.168.1.104，浮動ip在master上。

 

Mysql版本是5.7.30。

已設定GTID。

 

slave1(備用master)

192.168.1.102

pc2

slave2

192.168.1.103

pc3

指向master

 

Keepalive+mysql設定可參考：https://blog.csdn.net/yabingshi_tech/article/details/50721841

二 試驗步驟

2.1 安裝ProxySQL

#新增儲存庫

cat <<EOF | tee /etc/yum.repos.d/proxysql.repo

[proxysql_repo]

name= ProxySQL YUM repository

baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever

gpgcheck=1

gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key

EOF

 

#安裝

yum install proxysql

 

#檢視版本

[root@ZooKeeper-node2 ~]# proxysql --version

ProxySQL version 2.0.13-107-g91737e0, codename Truls

 

#修改admin密碼

修改/etc/proxysql.cnf，改下admin_credentials值。

--預設值是admin:admin，表示使用者和密碼都是admin。

 

#啓動proxysql

[root@ZooKeeper-node2 ~]# service proxysql start

Starting ProxySQL: 2020-07-14 16:25:09 [INFO] Using config file /etc/proxysql.cnf

DONE!

2.2 Proxy管理介紹

2.2.1 通過管理介面設定ProxySQL

請記住，設定ProxySQL的最佳方法是通過其管理介面。 通過SQL查詢對其管理數據庫進行聯機設定（無需重新啓動代理）。 作爲設定它的輔助方法，我們也有組態檔。

[root@ZooKeeper-node2 ~]# mysql -u admin -p -h 127.0.0.1 -P6032 --prompt='Admin> '

注意：如果您的MySQL用戶端版本是8.04或更高版本，請在上述命令中新增--default-auth = mysql_native_password以連線到管理介面。

 

Admin> 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)

這將允許您控制後端伺服器的列表，如何將流量路由到它們以及其他重要設定（例如快取，存取控制等）。 修改記憶體中的數據結構後，您必須將新設定載入到runtime，或將新設定持久儲存到磁碟上（以便在重新啓動代理後它們仍然存在）

2.2.2 通過組態檔設定ProxySQL

less  /etc/proxysql.cnf

admin_variables：包含用於控制管理介面功能的全域性變數。

mysql_variables：包含全域性變數，這些全域性變數控制處理傳入MySQL流量的功能。

mysql_servers：包含admin介面中mysql_servers表的行。 基本上，它們定義了MySQL流量路由到的後端伺服器，範例：

mysql_servers =

(

{

address="127.0.0.1"

port=3306

hostgroup=0

max_connections=200

}

)

mysql_users：包含管理介面中mysql_users表的行。 基本上，這些定義了可以通過代理連線到後端伺服器的使用者。

範例：

mysql_users:

(

{

username = "root"

password = "root"

default_hostgroup = 0

max_connections=1000

default_schema="information_schema"

active = 1

}

)

mysql_query_rules：包含來自管理介面的mysql_query_rules表的行。 基本上，這些規則根據各種條件（匹配的模式，用於執行查詢的使用者等）定義用於分類和路由傳入的MySQL流量的規則。

mysql_query_rules:

(

{

rule_id=1

active=1

match_pattern="^SELECT .* FOR UPDATE$"

destination_hostgroup=0

apply=1

},

{

rule_id=2

active=1

match_pattern="^SELECT"

destination_hostgroup=1

apply=1

}

)

2.3 設定

2.3.1 設定Mysql使用者

2.3.1.1 先在Mysql上建立使用者

2.3.1.1.1建立程式使用者

mysql> grant insert,update,delete,select on *.* to 'msandbox'@'192.168.1.103' identified by 'msandbox';

2.3.1.1.2 建立監控使用者

mysql> grant usage on *.* to 'monitor'@'192.168.1.103' identified by 'moNitor@123';

2.3.1.2 在ProxySql上設定Mysql使用者

2.3.1.2.1 設定程式使用者

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);

Admin>SELECT * FROM mysql_users; 

Admin> LOAD MYSQL USERS TO RUNTIME;

Admin> SAVE MYSQL USERS TO DISK;

如果沒有針對特定查詢的匹配查詢規則，default_hostgroup將用於發送由特定使用者生成的流量。

[root@pc1 ~]# mysql -u msandbox -p -h 192.168.1.103 -P6033 -e "SELECT @@port"

+--------+

| @@port |

+--------+

|   3307 |

 

2.3.1.2.2 設定監控使用者

Admin> LOAD MYSQL VARIABLES TO RUNTIME;

Query OK, 0 rows affected (0.00 sec)

 

Admin> SAVE MYSQL VARIABLES TO DISK;

Query OK, 54 rows affected (0.02 sec)

如果不設定監控使用者，後面配完讀寫分離後proxysql日誌/var/lib/proxysql/proxysql.log會報錯：

2020-08-02 11:32:29 MySQL_Monitor.cpp:1138:monitor_read_only_thread(): [ERROR] Server 192.168.1.103:3307 missed 3 read_only checks. Assuming read_only=1

2020-08-02 11:32:29 MySQL_Monitor.cpp:982:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.104:3307 after 1ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Access denied for user 'monitor'@'192.168.1.103' (using password: YES).

……

且即使在mysql_server中設定了主節點所屬hostgroup爲1，proxysql也自動會將其變爲2。

2.3.2 設定讀寫分離

2.3.2.1 確保mysql從庫開啓了只讀read_only

mysql> show variables like 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | ON    |

+---------------+-------+

1 row in set (0.00 sec)

組態檔裡read_only=1

若未設定從庫read_only=1，則mysql_servers裡資訊會錯亂。

2.3.2.2 建立mysql replication hostgroups

Admin> SHOW CREATE TABLE mysql_replication_hostgroups ;

| mysql_replication_hostgroups | 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)) |

 

INSERT INTO mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,'cluster1');

現在，在主機組1或2中設定的所有伺服器都將被移動到正確的主機組。

read_only=0的會被移動到1寫組。

read_only=1的會被移動到2讀組

Admin> LOAD MYSQL SERVERS TO RUNTIME;

Admin> SAVE MYSQL SERVERS TO DISK;

2.3.2.3 新增後端伺服器

範例：

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port, max_connections) VALUES (1,'192.168.1.104',3307,5000);

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port, max_connections) VALUES (2,'192.168.1.102',3307,5000);

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port, max_connections) VALUES (2,'192.168.1.103',3307 ,5000);

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;

Admin> SELECT * FROM mysql_servers;

2.3.2.4 設定讀寫分離

有如下兩種讀寫分離設定方式，選取其中一種即可。

2.3.2.4.1 使用正則表達式進行讀寫分離

UPDATE mysql_users SET default_hostgroup=1 where username='msandbox';

LOAD MYSQL USERS TO RUNTIME;

SAVE MYSQL USERS TO DISK;

#設定路由規則

INSERT INTO mysql_query_rules (rule_id,username,active,match_digest,destination_hostgroup,apply)

VALUES

(1, 'msandbox',1,'^SELECT.*FOR UPDATE$',1,1),

(2, 'msandbox',1,'^SELECT',2,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

 

Admin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username='msandbox' ORDER BY rule_id;

+----------------------+-----------------------+

| match_digest         | destination_hostgroup |

+----------------------+-----------------------+

| ^SELECT.*FOR UPDATE$ | 1                     |

| ^SELECT              | 2                     |

+----------------------+-----------------------+

2 rows in set (0.00 sec)

 

第一個規則範例使用符號^和$：這些是特殊的正則表達式字元，用於標記模式的開頭和結尾。在這種情況下，這意味着match_digestormatch_模式應該完全匹配查詢。

假如未使用這些符號，表示匹配項可以在查詢中的任何位置。

2.3.2.4.2 使用regex和digest進行讀/寫拆分

將ProxySQL設定爲只將所有流量發送到一個MySQL節點，即主節點（包括寫入和讀取），檢視stats_mysql_query_digest，找到最昂貴的SELECT語句，將其遷移至從節點。

#查詢總執行時間前5的sql

Admin> SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000000 ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+-------------+----------+----------+----------+

| digest             | SUBSTR(digest_text,0,20) | count_star | sum_time    | avg_time | min_time | max_time |

+--------------------+--------------------------+------------+-------------+----------+----------+----------+

| 0x9EED412C6E63E477 | SELECT a.id as acco      | 961733     | 24115349801 | 25074    | 10994    | 7046628  |

……

 

#查詢基於count的前5個sql

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

+--------------------+--------------------------+------------+---------------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time      |

+--------------------+--------------------------+------------+---------------+

| 0x037C3E6D996DAFE2 | SELECT a.ip_id as ip_id, | 2030040688 | 1479092529369 |

……

 

#查詢按總執行時間排序的前5個查詢，平均執行時間至少爲1秒。同時顯示總執行時間的百分比：

Admin> SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;

+--------------------+--------------------------+------------+--------------+----------+-------+

| digest             | SUBSTR(digest_text,0,25) | count_star | sum_time     | avg_time | pct   |

+--------------------+--------------------------+------------+--------------+----------+-------+

| 0x36CE5295726DB5B4 | SELECT COUNT(*) as total | 146390     | 185951894994 | 1270249  | 2.11  |

 

建議將平均執行時間>1秒的遷移到從伺服器。

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply)

VALUES

(1,1,'0x38BE36BDFFDBE638',20,1);

LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL QUERY RULES TO DISK;

2.3.3 驗證讀寫分離及負載均衡

#在主庫上插入測試數據

mysql> use dan;

Database changed

mysql> create table t(id int primary key,name varchar(30));

Query OK, 0 rows affected (0.08 sec)

 

vi c.sh

for i in {1..200}

do

   mysql -u msandbox -pmsandbox -h 192.168.1.103 -P6033 -e "insert into dan.t(id,name) values($i,@@hostname);"

done

 

sh c.sh

[root@pc1 download]# mysql -u msandbox -pmsandbox -h 192.168.1.103 -P6033 -e "select count(*) from dan.t where name='pc1'"

mysql: [Warning] Using a password on the command line interface can be insecure.

+----------+

| count(*) |

+----------+

|      200 |

+----------+

看到插入語句都分配到了pc1上。

 

vi b.sh

for i in {1..200}

do

   mysql -u msandbox -pmsandbox -h 192.168.1.103 -P6033 -e "SELECT @@hostname"

done

 

sh b.sh > b.log

[root@pc1 download]#  cat b.log | grep pc3 | wc -l

90

[root@pc1 download]#  cat b.log | grep pc2 | wc -l

110

[root@pc1 download]#  cat b.log | grep pc1 | wc -l

0

看到讀操作分配到了pc2,pc3上，基本均衡。

2.3.4 驗證讀高可用

在有兩個從庫時，宕掉其中一個從庫，不影響業務。

範例：

關掉pc3的mysql：

[root@pc3 ~]# service mysqld stop

Shutting down MySQL.... SUCCESS!

Proxysql日誌有相關報錯：

2020-08-02 14:50:52 MySQL_Monitor.cpp:982:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 192.168.1.103:3307 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on '192.168.1.103' (115).

 

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

+---------------+------+------------------+-------------------------+--------------------------------------------------------+

| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                          |

+---------------+------+------------------+-------------------------+--------------------------------------------------------+

| 192.168.1.104 | 3307 | 1596351273368716 | 1332                    | NULL                                                   |

| 192.168.1.102 | 3307 | 1596351272893928 | 942                     | NULL                                                   |                                                 |

| 192.168.1.103 | 3307 | 1596351034321523 | 0                       | Can't connect to MySQL server on '192.168.1.103' (115) |

| 192.168.1.102 | 3307 | 1596351033607114 | 1153                    | NULL                                                   |

+---------------+------+------------------+-------------------------+--------------------------------------------------------+

 

sh b.sh > b.log

[root@pc1 download]# cat b.log | grep pc3 | wc -l

0

[root@pc1 download]# cat b.log | grep pc2 | wc -l

200

說明slave2宕機後，查詢全在slave1上進行。

假如兩個從庫都宕機了，會影響業務：

[root@pc1 download]# mysql -u msandbox -pmsandbox -h 192.168.1.103 -P6033 -e "select count(*) from dan.t where name='pc1' "

ERROR 2013 (HY000) at line 1: Lost connection to MySQL server at 'handshake: reading inital communication packet', system error: 111

2.3.5 假如主庫宕機瞭如何處理

2.3.5.1 模擬master宕機

[root@pc1 download]# service mysqld stop

Shutting down MySQL............ SUCCESS!

浮動ip切到了備用master上：

[root@pc2 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN

    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

    inet 127.0.0.1/8 scope host lo

    inet6 ::1/128 scope host

       valid_lft forever preferred_lft forever

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

    link/ether 08:00:27:04:05:16 brd ff:ff:ff:ff:ff:ff

    inet 192.168.1.102/24 brd 192.168.1.255 scope global eth0

    inet 192.168.1.104/32 scope global eth0

    inet6 fe80::a00:27ff:fe04:516/64 scope link tentative dadfailed

       valid_lft forever preferred_lft forever

此時proxysql日誌報錯：

無法正常寫入數據：

2.3.5.2 關閉備用主庫的read_only

關閉備庫read_only之前需要先確保備庫(show slave status)無延遲。

爲了使新主庫能寫入數據，需要關閉read_only:

mysql> set global read_only=0;

mysql> show variables like 'read_only';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| read_only     | OFF   |

可以正常插入數據了:

將該步驟設定成自動化任務：

#先給使用者授權

grant super on *.* to 'CheckUser'@'192.168.1.%';

 

修改keepalive裡巢狀的/root/check_mysql.sh，修改後內容如下：

MYSQL=/usr/local/mysql/bin/mysql

MYSQL_HOST='192.168.1.101'

STANDBY_HOST='192.168.1.102'

PORT='3307'

MYSQL_USER=test

MYSQL_PASSWORD=123456

date=`date +%Y%m%d:`

LogName='/root/check_mysql.log'

echo $date > /root/check_mysql.log

str="SELECT MASTER_POS_WAIT('"



$MYSQL  -u $MYSQL_USER -p$MYSQL_PASSWORD -e "show master status;" > /root/master_status.log

if [ $? == 0 ]

then

    echo $MYSQL_HOST "mysql login successfully." >> $LogName

    cat /root/master_status.log  | grep -v 'File' > /root/master_status.log2

    exit 0

else

    echo $MYSQL_HOST "mysql login faild" >> $LogName



    #檢查備選主庫主從複製是否有延遲，無延遲情況下再將浮動ip切到從庫

    v1=$(cat /root/master_status.log2 | awk -F " " '{print $1}') #取FILE

    v2=$(cat /root/master_status.log2 | awk -F " " '{print $2}') #取Position

    sql=$str$v1"',"$v2,120')'  #拼sql:SELECT MASTER_POS_WAIT(file, position,120);

    v3=$($MYSQL -h $STANDBY_HOST  -u $MYSQL_USER -p$MYSQL_PASSWORD -P $PORT -e "$sql")

    v4=$(echo "$v3" | grep -v 'MASTER')

    echo $sql'值爲'$v4 >> $LogName

    if [ $v4 -ge 0 ]

    then

        echo '備選主庫主從複製無延遲,開始進行主從切換...' >> $LogName

        #關閉備選主庫的只讀模式

        $MYSQL -h $STANDBY_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD -P $PORT -e "set global read_only=off;" >> $LogName

        if [ $? == 0 ]

        then

            /etc/init.d/keepalived stop

            echo '主從切換成功' >> $LogName

        fi

    else

        echo '備選主庫主從延遲,暫時無法進行主從切換,請注意.' >> $LogName #這裏最好設定成一個郵件或其他方式告警，即使知曉該情況。

    fi

    exit 2

fi

指令碼邏輯：

檢查能否連線mysql執行基本命令，若不能正常執行該命令，則先確保備選主庫主從複製無延遲(SELECT MASTER_POS_WAIT)，然後進行主從切換：

① 關閉備選主庫的只讀模式

② 漂移浮動ip到備選主庫上

 

可以通過/root/check_mysql.log檢視指令碼執行情況。

2.3.5.3 設定原主庫上的其他從庫的主從複製關係

先執行show slave status檢查slave2主從同步是否存在延遲情況。

確保主從完全同步後，將slave2指向新主庫進行主從複製：

stop slave;

CHANGE MASTER TO MASTER_HOST='192.168.1.102',MASTER_USER='RepUser',MASTER_PASSWORD='beijing',MASTER_PORT=3307;

start slave;

show slave status \G;

2.3.5.3 啓動原主庫

service mysqld start

service keepalived start

2.3.5.4 回切主從

回切主從的話，在業務低峯期做比較合適。

① 關閉現主庫192.168.1.102的keepalive，漂移浮動ip

service keepalived stop

② 開啓192.168.1.102的只讀模式

set global read_only=on;

③ 將192.168.1.103指向192.168.1.101進行復制：

stop slave;

CHANGE MASTER TO MASTER_HOST='192.168.1.101',MASTER_USER='RepUser',MASTER_PASSWORD='beijing',MASTER_PORT=3307;

start slave;

show slave status \G;

2.3.5.5 還原mysql_servers設定

主庫宕機後，proxysql連線不上該主庫，會將該主庫假定爲read_only=1，放入讀組，因此，在把原主庫啓動後，需要確保mysql_servers是正確設定：

否則，執行delete命令進行刪除，如

delete from mysql_servers where hostname='192.168.1.101' and hostgroup_id=1;

LOAD MYSQL SERVERS TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;