配置YUM源
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 -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
rpm -ivh openssl-libs-1.0.2k-16.el7.x86_64.rpm --force
下载proxysql
wget https://codeload.github.com/sysown/proxysql/tar.gz/v2.0.6
解压并安装
tar zxvf proxysql-2.0.6.tar.gz
cd proxysql-2.0.6
make -j 4 && make install DESTDIR=/app/proxysql
/etc/init.d/proxysql start
查看proxysql的监控端口
ss -lntup|grep proxy
登录主库的mysql shell进行授权
grant all privileges on . to proxysql@‘172.16.210.%’ identified by ‘Proxy@2019’;
grant select on . to ‘monitor’@‘172.16.210.%’ identified by ‘monitor’;
flush privileges;
登录proxysql主机操作以下：
mysql -uadmin -padmin -h127.0.0.1 -P6032
admin@6032 [(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：内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
disk：是持久化到硬盘的配置，sqlite数据文件。
stats：是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
monitor：库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
#使用insert into语句来动态配置，而不需要重启
use admin
insert into admin.mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,‘db1’,‘3306’,1,‘Write Group’);
insert into admin.mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,‘db2’,‘3306’,1,‘Read Group’);
admin@6032 [main]>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 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | Read Group |
±-------------±---------±-----±-------±-------±------------±----------------±--------------------±--------±---------------±------------+
#接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中，它也是用于proxysql客户端访问数据库，默认组是写组，当读写分离规则出现问题时，它会直接访问默认组的数据库。
MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (‘proxysql’,‘Proxy@2019’,1);
#在proxysql主机端配置监控用户
MySQL [main]> set mysql-monitor_username=‘monitor’;
MySQL [main]> set mysql-monitor_password=‘monitor’;
配置proxysql的转发规则
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,’^SELECT.*FOR UPDATE$’,1,1);
MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,’^SELECT’,2,1);
MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;
#更新配置到RUNTIME中
由上面的配置系统层级关系可以得知所有进来的请求首先是经过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、将所有配置保存至磁盘上
所有配置数据保存到磁盘上，也就是永久写入/var/lib/proxysql/proxysql.db这个文件中
MySQL [main]> save mysql users to disk;
MySQL [main]> save mysql servers to disk;
MySQL [main]> save mysql query rules to disk;
MySQL [main]> save mysql variables to disk;
MySQL [main]> save admin variables to disk;
MySQL [main]> load mysql users to runtime;
#测试读写分离
[root@proxy ~]# mysql -uproxysql -pProxy@2019 -h 127.0.0.1 -P 6033
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 12
Server version: 5.6.40 (ProxySQL Admin Module)
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)
#这才是我们真正的数据库啊
创建数据与表，测试读写分离情况
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)
begin;
insert into test_tables values(‘kk3’,‘88’);
select * from test_tables;
commit;
在proxysql管理端查看读写分离
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)
#从上述结果就可以看出读写分离配置是成功的，读请求是转发到2组，写请求转发到1组
961
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
