兜兜    2018-08-12 15:22:41    2019-11-14 14:32:31   

mysql ProxySQL
### 一、环境准备 系统: `CentOS7` 数据库: `ProxySQL:1.4.14` 服务器: `master`: `172.16.0.100/db1` `slave`: `172.16.0.101/db2` `slave/ProxySQL`: `172.16.0.102/db3`     ### 二、准备工作 `a.数据库搭建MySQL主从` `b.从库开启`**`read_only=on`**     ### 三、ProxySQL的安装 #### 安装依赖的软件包 `db3` ```bash yum -y install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL ```   #### ProxySQL软件包的两个下载地址 GitHub官网:https://github.com/sysown/proxysql/releases percona官网:https://www.percona.com/downloads/proxysql/   #### 安装ProxySQL `db3` ```bash yum install -y https://www.percona.com/downloads/proxysql/proxysql-1.4.14/binary/redhat/7/x86_64/proxysql-1.4.14-1.1.el7.x86_64.rpm ```   #### 启动ProxySQL `db3` ```bash systemctl start proxysql ```   #### 查看启动信息 `db3` ```bash netstat -tunlp |grep proxysql ``` ``` tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 13331/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 13331/proxysql ``` `6032为管理端口,6033为对外服务的端口号`   #### 登录ProxySQL `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ```   #### 查看proxysql信息 `db3` ```sql mysql> 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 | +-----+---------------+-------------------------------------+ #可见有四个库:main、disk、stats和monitor。分别说明一下这四个库的作用。 #main:内存配置数据库,即MEMORY,表里存放后端db实例、用户验证、路由规则等信息。 mysql> 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 | +--------------------------------------------+ #库下的主要表: #mysql_servers—后端可以连接MySQL服务器的列表。 #mysql_users—配置后端数据库的账号和监控的账号。 #mysql_query_rules—指定Query路由到后端不同服务器的规则列表。 #注:表名以runtime_开头的表示ProxySQL当前运行的配置内容,不能通过DML语句修改。只能修改对应的不以 runtime开头的表,然后“LOAD”使#其生效,“SAVE”使其存到硬盘以供下次重启加载。 #disk库—持久化磁盘的配置。 #stats库—统计信息的汇总。 #monitor库—一些监控的收集信息,包括数据库的健康状态等。 ```     ### 四、配置ProxySQL监控 #### Master上创建ProxySQL的监控账户和对外访问账户并赋予权限 `db1` ```sql mysql> create user 'monitor'@'172.16.0.%' identified by 'monitor'; mysql> grant all privileges on *.* to 'monitor'@'172.16.0.%' with grant option; mysql> create user 'proxysql'@'172.16.0.%' identified by 'proxysql'; mysql> grant all privileges on *.* to 'proxysql'@'172.16.0.%' with grant option; mysql> flush privileges; ```   #### ProxySQL添加主从服务器列表 `db3` ```sql mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.100',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.101',3306); mysql> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.0.102',3306); ```   #### ProxySQL查看服务器列表 `db3` ```sql mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ ```   #### ProxySQL配置监控账号 `db3` ```sql mysql> set mysql-monitor_username='monitor'; mysql> set mysql-monitor_password='monitor'; mysql> load mysql variables to runtime; mysql> save mysql variables to disk; ```   #### ProxySQL验证监控信息 `db3` ```sql mysql> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6; +--------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------+------+------------------+-------------------------+---------------+ | 172.16.0.100 | 3306 | 1565593172754235 | 2118 | NULL | | 172.16.0.101 | 3306 | 1565593172137991 | 2729 | NULL | | 172.16.0.102 | 3306 | 1565593171521858 | 773 | NULL | | 172.16.0.100 | 3306 | 1565593113076006 | 2163 | NULL | | 172.16.0.101 | 3306 | 1565593112298748 | 2377 | NULL | | 172.16.0.102 | 3306 | 1565593111521583 | 628 | NULL | +--------------+------+------------------+-------------------------+---------------+ ```     ### 五、配置ProxySQL主从分组信息 #### ProxySQL插入读写分离组 `db3` ```sql mysql> insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values (10,20,'proxy'); mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # writer_hostgroup是写入组的编号,reader_hostgroup是读取组的编号。实验中使用10作为写入组,20作为读取组编号。 mysql> select * from mysql_servers; +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | 172.16.0.100 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.101 | 3306 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 172.16.0.102 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ # ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server,master被分到编号为10的写组,read_only=1的server,slave则被分到编号为20的读组。 ```   #### ProxySQL配置对外访问账号(`默认指定主库,并对该用户开启事务持久化保护`) `db3` ```sql insert into mysql_users(username,password,default_hostgroup) values ('proxysql','proxysql',10); update mysql_users set transaction_persistent=1 where username='proxysql'; #设置为1,避免发生脏读、幻读等现象 load mysql users to runtime; save mysql users to disk; ```   #### ProxySQL验证登录的服务器 `db3` ```bash mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select @@hostname; +------------+ | @@hostname | +------------+ | db1 | +------------+ # 验证登入的服务器默认为主库(db1) ```     ### 六、ProxySQL配置读写分离策略 #### ProxySQL配置读写分离 `db3` ```sql mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT.*FOR UPDATE$',10,1); mysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup, apply) VALUES(1,'^SELECT',20,1); mysql> load mysql query rules to runtime; mysql> save mysql query rules to disk; ```   #### ProxySQL配置服务器的权重 `db3` ```sql mysql> update mysql_servers set weight=10 where hostname='172.16.0.101'; mysql> load mysql servers to runtime; mysql> save mysql servers to disk; # 172.16.0.101的权重被修改为10,172.16。0.102的默认权重为1,则请求比例为10:1 ```     ### 七、测试读写分离 #### 执行select查询 `db3` ```sql mysql -uproxysql -pproxysql -h 172.16.0.102 -P 6033 ``` ```sql mysql> select node_name from percona.example where node_id=1; ```   #### 执行select for update查询 `db3` ```sql mysql> select node_name from percona.example where node_id=1 for update; ```   #### 执行update语句 `db3` ```sql mysql> update percona.example set node_name='test' where node_id=1; ```   #### proxySQL查看统计信息 `db3` ```bash mysql -uadmin -padmin -h 127.0.0.1 -P 6032 ``` ```sql mysql> select hostgroup,digest_text from stats_mysql_query_digest; +-----------+--------------------------------------------------------------------------------------------------+ | hostgroup | digest_text | +-----------+--------------------------------------------------------------------------------------------------+ | 10 | update percona.example set node_name=? where node_id=? | | 10 | select node_name,@@hostname from percona.example where node_id=? for update | | 20 | select node_name,@@hostname from percona.example where node_id=? | +-----------+--------------------------------------------------------------------------------------------------+ ``` `从上面的输出信息可以发现读写分离成功了` 参考: https://blog.51cto.com/sumongodb/2130453
阅读 158 评论 0 收藏 0
阅读 158
评论 0
收藏 0