ProxySQL+MySQL實(shí)現(xiàn)數(shù)據(jù)庫(kù)讀寫(xiě)分離實(shí)戰(zhàn)
前面也寫(xiě)過(guò)幾篇關(guān)于Mysql數(shù)據(jù)的文章:
- MySQL集群高可用架構(gòu)之MHA
- MySQL 同步復(fù)制及高可用方案總結(jié)
- 官方工具|MySQL Router 高可用原理與實(shí)戰(zhàn)
今天給大家?guī)?lái)的是關(guān)于數(shù)據(jù)庫(kù)讀寫(xiě)分離相關(guān)的實(shí)戰(zhàn)操作。
ProxySQL介紹
ProxySQL是一個(gè)高性能的MySQL中間件,擁有強(qiáng)大的規(guī)則引擎。具有以下特性:http://www.proxysql.com/
1、連接池,而且是multiplexing
2、主機(jī)和用戶的最大連接數(shù)限制
3、自動(dòng)下線后端DB
- 延遲超過(guò)閥值
- ping 延遲超過(guò)閥值
- 網(wǎng)絡(luò)不通或宕機(jī)
4、強(qiáng)大的規(guī)則路由引擎
- 實(shí)現(xiàn)讀寫(xiě)分離
- 查詢重寫(xiě)
- sql流量鏡像
5、支持prepared statement
6、支持Query Cache
7、支持負(fù)載均衡,與gelera結(jié)合自動(dòng)failover
整體環(huán)境介紹
1、系統(tǒng)環(huán)境
三臺(tái)服務(wù)器系統(tǒng)環(huán)境一致如下
- [root@db1 ~]# cat /etc/redhat-release
- CentOS Linux release 7.4.1708 (Core)
- [root@db1 ~]# uname -r
- 3.10.0-693.el7.x86_64
2、IP地址與軟件版本
- proxy 192.168.22.171
- db1 192.168.22.173
- db2 192.168.22.174
- mysql 5.7.17
- proxy sql 1.4.8
3、關(guān)閉防火墻、selinux
- systemctl stop firewalld #停止防火墻服務(wù)
- systemctl disable firewalld #禁止開(kāi)機(jī)自啟動(dòng)
- sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/conf && reboot
- #用sed命令替換的試修改selinux的配置文件
4、mysql安裝與主從同步
安裝請(qǐng)參考以下文章
LAMP架構(gòu)應(yīng)用實(shí)戰(zhàn)——MySQL服務(wù)
主從同步請(qǐng)參以下文章
Linux系統(tǒng)MySQL數(shù)據(jù)庫(kù)主從同步實(shí)戰(zhàn)過(guò)程
安裝布署過(guò)程
1、數(shù)據(jù)庫(kù)主從同步
- 查看主從同步狀態(tài)
- mysql> show slave status\G
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.22.173
- Master_User: rep
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: master-log.000001
- Read_Master_Log_Pos: 154
- Relay_Log_File: db2-relay-bin.000002
- Relay_Log_Pos: 321
- Relay_Master_Log_File: master-log.000001
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table:
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 154
- Relay_Log_Space: 526
- Until_Condition: None
- Until_Log_File:
- Until_Log_Pos: 0
- Master_SSL_Allowed: No
- Master_SSL_CA_File:
- Master_SSL_CA_Path:
- Master_SSL_Cert:
- Master_SSL_Cipher:
- Master_SSL_Key:
- Seconds_Behind_Master: 0
- Master_SSL_Verify_Server_Cert: No
- Last_IO_Errno: 0
- Last_IO_Error:
- Last_SQL_Errno: 0
- Last_SQL_Error:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 1
- Master_UUID: 70a61633-63ae-11e8-ab86-000c29fe99ea
- Master_Info_File: /mysqldata/master.info
- SQL_Delay: 0
- SQL_Remaining_Delay: NULL
- Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
- Master_Retry_Count: 86400
- Master_Bind:
- Last_IO_Error_Timestamp:
- Last_SQL_Error_Timestamp:
- Master_SSL_Crl:
- Master_SSL_Crlpath:
- Retrieved_Gtid_Set:
- Executed_Gtid_Set:
- Auto_Position: 0
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
- 1 row in set (0.00 sec)
- 檢測(cè)主從同步
- [root@db1 ~]# mysql -uroot -p -e "create database testdb;
- "Enter password:
- [root@db1 ~]# mysql -uroot -p -e "show databases;" |grep testdb
- Enter password:
- testdb
- #db2上查看是否同步
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | testdb |
- +--------------------+
- 5 rows in set (0.01 sec)
2、準(zhǔn)備proxySQL軟件
- [root@proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
- [root@proxy ~]# ll proxysql-1.4.8-1-centos7.x86_64.rpm
- -rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm
3、安裝配置
- [root@proxy ~]# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm
- [root@proxy ~]# rpm -ql proxysql
- /etc/init.d/proxysql #啟動(dòng)腳本
- /etc/proxysql.cnf #配置文件,僅在第一次(/var/lib/proxysql/proxysql.db文件不存在)啟動(dòng)時(shí)有效。啟#動(dòng)后可以在proxysql管理端中通過(guò)修改數(shù)據(jù)庫(kù)的方式修改配置并生效(官方推薦方式。)
- /usr/bin/proxysql #主程序文件
- /usr/share/proxysql/tools/proxysql_galera_checker.sh
- /usr/share/proxysql/tools/proxysql_galera_writer.pl
4、配置文件詳解
- [root@proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf
- datadir="/var/lib/proxysql" #數(shù)據(jù)目錄
- admin_variables=
- {
- admin_credentials="admin:admin" #連接管理端的用戶名與密碼
- mysql_ifaces="0.0.0.0:6032" #管理端口,用來(lái)連接proxysql的管理數(shù)據(jù)庫(kù)
- }
- mysql_variables=
- {
- threads=4 #指定轉(zhuǎn)發(fā)端口開(kāi)啟的線程數(shù)量
- max_connections=2048
- default_query_delay=0
- default_query_timeout=36000000
- have_compress=true
- poll_timeout=2000
- interfaces="0.0.0.0:6033" #指定轉(zhuǎn)發(fā)端口,用于連接后端mysql數(shù)據(jù)庫(kù)的,相當(dāng)于代理作用
- default_schema="information_schema"
- stacksize=1048576
- server_version="5.5.30" #指定后端mysql的版本
- connect_timeout_server=3000
- monitor_username="monitor"
- monitor_password="monitor"
- monitor_history=600000
- monitor_connect_interval=60000
- monitor_ping_interval=10000
- monitor_read_only_interval=1500
- monitor_read_only_timeout=500
- ping_interval_server_msec=120000
- ping_timeout_server=500
- commands_stats=true
- sessions_sort=true
- connect_retries_on_failure=10
- }
- mysql_servers =
- (
- )
- mysql_users:
- (
- )
- mysql_query_rules:
- (
- )
- scheduler=
- (
- )
- mysql_replication_hostgroups=
- (
- )
- #因此我們使用官方推薦的方式來(lái)配置proxy sql
5、啟動(dòng)服務(wù)并查看
- [root@proxy ~]# /etc/init.d/proxysql
- startStarting ProxySQL: DONE!
- [root@proxy ~]# ss -lntup|grep proxy
- tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=1199,fd=23))
- tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=22))
- tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=21))
- tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=20))
- tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=1199,fd=19))
- #可以看出轉(zhuǎn)發(fā)端口6033是啟動(dòng)了四個(gè)線程
6、在mysql上配置賬號(hào)并授權(quán)
- mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.22.%' IDENTIFIED BY '123456';
- Query OK, 0 rows affected, 1 warning (0.03 sec)
- mysql> flush privileges;
- Query OK, 0 rows affected (0.02 sec)
7、proxysql默認(rèn)數(shù)據(jù)庫(kù)說(shuō)明
- [root@proxy ~]# yum install mysql -y
- [root@proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
- 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, 2017, 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:內(nèi)存配置數(shù)據(jù)庫(kù),表里存放后端db實(shí)例、用戶驗(yàn)證、路由規(guī)則等信息。表名以 runtime_開(kāi)頭的表示proxysql當(dāng)前運(yùn)行的配置內(nèi)容,不能通過(guò)dml語(yǔ)句修改,只能修改對(duì)應(yīng)的不以 runtime_ 開(kāi)頭的(在內(nèi)存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盤(pán)以供下次重啟加載。
disk:是持久化到硬盤(pán)的配置,sqlite數(shù)據(jù)文件。
stats:是proxysql運(yùn)行抓取的統(tǒng)計(jì)信息,包括到后端各命令的執(zhí)行次數(shù)、流量、processlist、查詢種類匯總/執(zhí)行時(shí)間等等。
monitor:庫(kù)存儲(chǔ) monitor 模塊收集的信息,主要是對(duì)后端db的健康/延遲檢查。
8、proxysql的配置系統(tǒng)
ProxySQL具有一個(gè)復(fù)雜但易于使用的配置系統(tǒng),可以滿足以下需求:
1、允許輕松動(dòng)態(tài)更新配置(這是為了讓ProxySQL用戶可以在需要零宕機(jī)時(shí)間配置的大型基礎(chǔ)架構(gòu)中使用它)。與MySQL兼容的管理界面可用于此目的。
2、允許盡可能多的配置項(xiàng)目動(dòng)態(tài)修改,而不需要重新啟動(dòng)ProxySQL進(jìn)程
3、可以毫不費(fèi)力地回滾無(wú)效配置
4、這是通過(guò)多級(jí)配置系統(tǒng)實(shí)現(xiàn)的,其中設(shè)置從運(yùn)行時(shí)移到內(nèi)存,并根據(jù)需要持久保存到磁盤(pán)。
- 3級(jí)配置由以下幾層組成:
參考文章:https://github.com/sysown/pro...
9、配置proxysql管理用戶
proxysql默認(rèn)的表信息如下
- MySQL [main]> show tables;
- +--------------------------------------------+
- | tables |
- +--------------------------------------------+
- | global_variables |
- | mysql_collations |
- | mysql_group_replication_hostgroups |
- | mysql_query_rules |
- | mysql_query_rules_fast_routing |
- | mysql_replication_hostgroups |
- | mysql_servers |
- | mysql_users |
- | proxysql_servers |
- | runtime_checksums_values |
- | runtime_global_variables |
- | runtime_mysql_group_replication_hostgroups |
- | runtime_mysql_query_rules |
- | runtime_mysql_query_rules_fast_routing |
- | runtime_mysql_replication_hostgroups |
- | runtime_mysql_servers |
- | runtime_mysql_users |
- | runtime_proxysql_servers |
- | runtime_scheduler |
- | scheduler |
- +--------------------------------------------+
- 20 rows in set (0.00 sec)
- #這里是使用insert into語(yǔ)句來(lái)動(dòng)態(tài)配置,而可以不需要重啟
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');
- Query OK, 1 row affected (0.01 sec)
- MySQL [(none)]> insert intomysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');
- Query OK, 1 row affected (0.00 sec)
- MySQL [(none)]> select * from mysql_servers;
- +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
- | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
- +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
- | 1 | db1 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Write Group |
- | 2 | db2 | 3307 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group
- |+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+
- 2 rows in set (0.00 sec)
- #接下來(lái)將剛剛在mysql客戶端創(chuàng)建的用戶寫(xiě)入到proxy sql主機(jī)的mysql_users表中,它也是用于proxysql客戶端訪問(wèn)數(shù)據(jù)庫(kù),默認(rèn)組是寫(xiě)組,當(dāng)讀寫(xiě)分離規(guī)則出現(xiàn)問(wèn)題時(shí),它會(huì)直接訪問(wèn)默認(rèn)組的數(shù)據(jù)庫(kù)。
- MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);
- Query OK, 1 row affected (0.00 sec)
- MySQL [main]> select * from mysql_users;
- +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
- | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
- +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
- | proxysql | 123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 |
- +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1
- row in set (0.00 sec)
- 在mysql上添加監(jiān)控的用戶
- mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%' IDENTIFIED BY 'monitor';
- Query OK, 0 rows affected, 1 warning (0.00 sec)
- mysql> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
- #在proxysql主機(jī)端配置監(jiān)控用戶
- MySQL [main]> set mysql-monitor_username='monitor';
- Query OK, 1 row affected (0.00 sec)
- MySQL [main]> set mysql-monitor_password='monitor';
- Query OK, 1 row affected (0.00 sec)
- #參考文章:https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration
10、配置proxysql的轉(zhuǎn)發(fā)規(guī)則
- MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);
- Query OK, 1 row affected (0.01 sec)
- MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1);
- Query OK, 1 row affected (0.00 sec)
- MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
- +---------+--------+----------------------+-----------------------+-------+
- | rule_id | active | match_digest | destination_hostgroup | apply |
- +---------+--------+----------------------+-----------------------+-------+
- | 1 | 1 | ^SELECT.*FOR UPDATE$ | 1 | 1 |
- | 2 | 1 | ^SELECT | 2 | 1 |
- +---------+--------+----------------------+-----------------------+-------+
- 2 rows in set (0.00 sec)
- #配置查詢select的請(qǐng)求轉(zhuǎn)發(fā)到hostgroup_id=2組上(讀組)#征對(duì)select * from table_name for update這樣的修改語(yǔ)句,我們是需要將請(qǐng)求轉(zhuǎn)到寫(xiě)組,也就是hostgroup_id=1#對(duì)于其它沒(méi)有被規(guī)則匹配的請(qǐng)求全部轉(zhuǎn)發(fā)到默認(rèn)的組(mysql_users表中default_hostgroup)
11、更新配置到RUNTIME中
由上面的配置系統(tǒng)層級(jí)關(guān)系可以得知所有進(jìn)來(lái)的請(qǐng)求首先是經(jīng)過(guò)RUNTIME層
- MySQL [main]> load mysql users to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [main]> load mysql servers to runtime;
- Query OK, 0 rows affected (0.02 sec)
- MySQL [main]> load mysql query rules to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [main]> load mysql variables to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [main]> load admin variables to runtime;
- Query OK, 0 rows affected (0.00 sec)
12、將所有配置保存至磁盤(pán)上
所有配置數(shù)據(jù)保存到磁盤(pán)上,也就是永久寫(xiě)入/var/lib/proxysql/proxysql.db這個(gè)文件中
- MySQL [main]> save mysql users to disk;
- Query OK, 0 rows affected (0.03 sec)
- MySQL [main]> save mysql servers to disk;
- Query OK, 0 rows affected (0.04 sec)
- ySQL [main]> save mysql query rules to disk;
- Query OK, 0 rows affected (0.03 sec)
- MySQL [main]> save mysql variables to disk;
- Query OK, 94 rows affected (0.02 sec)
- MySQL [main]> save admin variables to disk;
- Query OK, 31 rows affected (0.02 sec)
- MySQL [main]> load mysql users to runtime;
- Query OK, 0 rows affected (0.00 sec)
13、測(cè)試讀寫(xiě)分離
- [root@proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033
- Welcome to the MariaDB monitor.Commands end with ; or \g.
- Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL)
- Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MySQL [(none)]> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | testdb |
- +--------------------+
- 5 rows in set (0.02 sec)#這才是我們真正的數(shù)據(jù)庫(kù)啊
- 創(chuàng)建數(shù)據(jù)與表,測(cè)試讀寫(xiě)分離情況
- MySQL [(none)]> create database test_proxysql;
- Query OK, 1 row affected (0.02 sec)
- MySQL [(none)]> use test_proxysql;
- Database changed
- MySQL [test_proxysql]> create table test_tables(name varchar(20),age int(4));
- Query OK, 0 rows affected (0.07 sec)
- MySQL [test_proxysql]> insert into test_tables values('zhao','30');
- Query OK, 1 row affected (0.09 sec)
- MySQL [test_proxysql]> select * from test_tables;
- +------+------+
- | name | age |
- +------+------+
- | zhao | 30 |
- +------+------+
- 1 row in set (0.02 sec)
- 在proxysql管理端查看讀寫(xiě)分離
- MySQL [main]> select * from stats_mysql_query_digest;
- +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
- | hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
- +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
- | 2 | test_proxysql | proxysql | 0x57CF7EC26C91DF9A | select * from test_tables |1 | 1527667635 | 1527667635 | 14253 | 14253 | 14253 |
- | 1 | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1527667214 | 1527667214 | 0 | 0 | 0 |
- | 1 | test_proxysql | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values(?,?) | 1 | 1527667623 | 1527667623 | 89033 | 89033 | 89033 |
- | 1 | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql | 1 | 1527667316 | 1527667316 | 8470 | 8470 | 8470 |
- | 1 | information_schema | proxysql | 0x02033E45904D3DF0 | show databases | 1 | 1527667222 | 1527667222 | 19414 | 19414 | 19414 |
- | 1 | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql | 1 | 1527667332 | 1527667332 | 15814 | 15814 | 15814 |
- | 2 | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE() | 1 | 1527667342 | 1527667342 | 23386 | 23386 | 23386 |
- | 1 | test_proxysql | proxysql | 0x02033E45904D3DF0 | show databases | 1 | 1527667342 | 1527667342 | 2451 | 2451 | 2451 |
- | 1 | test_proxysql | proxysql | 0x59F02DA280268525 | create table test_tables | 1 | 1527667360 | 1527667360 | 9187 | 9187 | 9187 |
- | 1 | test_proxysql | proxysql | 0x99531AEFF718C501 | show tables | 1 | 1527667342 | 1527667342 | 1001 | 1001 | 1001 |
- | 1 | test_proxysql | proxysql | 0xC745E37AAF6095AF | create table test_tables(name varchar(?),age int(?)) | 1 | 1527667558 | 1527667558 | 68935 | 68935 | 68935 |
- +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+
- 11 rows in set (0.01 sec)#從上述結(jié)果就可以看出讀寫(xiě)分離配置是成功的,讀請(qǐng)求是轉(zhuǎn)發(fā)到2組,寫(xiě)請(qǐng)求轉(zhuǎn)發(fā)到1組
- 整個(gè)讀寫(xiě)分離的架構(gòu)配置到此就完成了,但是此架構(gòu)存在需要優(yōu)化的地方,那就是此架構(gòu)存在單點(diǎn)問(wèn)題。實(shí)際生產(chǎn)環(huán)境中可采用MHA+ProxySQL+Mysql這類架構(gòu)解決此問(wèn)題,請(qǐng)持續(xù)關(guān)注!