MySQL数据库_ProxySQL实验

By | 2019年 11月 23日
0 Comment

实验：ProxySQL

  1. 要怎么做呢，来看图

实验开始

master: 192.168.99.102
  1. 修改配置文件
[centos]$ vim /etc/my.cnf
    [mysqld]
    server_id=2
    log_bin
  1. 启动之
[centos]$ systemctl restart mariadb
  1. 创建帐号用于主从连接的
MariaDB [(none)]> grant replication slave on *.* to repluser@'%' identified by '123';
slave: 192.168.99.103
  1. 修改配置文件
[centos]$ vim /etc/my.cnf
    [mysqld]
    server_id=3
    read_only
  1. 启动之
[centos]$ systemctl restart mariadb
  1. 连接主服务器
MariaDB [(none)]> CHANGE MASTER TO
MASTER_HOST='192.168.99.102',
MASTER_USER='repluser',
MASTER_PASSWORD='123',
MASTER_LOG_FILE='mariadb-bin.000001',
MASTER_LOG_POS=245;
proxySQL：192.168.99.101
  1. 安装前还得配置下官方的yum源
    要不就自行下载安装：https://github.com/sysown/proxysql/releases
[101]$ vim /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
  1. 安装proxySQL
[101]$ yum clean all 

[101]$ yum install proxysql
  1. 启动proxySQL
[101]$ systemctl start proxysql
  1. 端口起来了，看看
[101]$ ss -tnl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port  
LISTEN     0      128          *:6032                     *:*
LISTEN     0      128          *:6033                     *:*
LISTEN     0      128          *:6033                     *:*
LISTEN     0      128          *:6033                     *:*
LISTEN     0      128          *:6033                     *:*
......

proxySQL的配置文件：/etc/proxysql.cnf

  1. 登录到proxysql试试
[101]$ mysql -uadmin -padmin -P6032 -h127.0.0.1
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, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>

连接成功，简单看看

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 |
+-----+---------------+-------------------------------------+
  1. 大致来了解下
#查看表结构
MySQL > select * from sqlite_master where name='mysql_servers'\G

#查看你的SQL服务器有哪些，当然什么都没有，还没有添加
MySQL > select * from mysql_servers;
  1. 添加你的2台MySQL主机
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.99.102',3306);

#保证hostgroup的ID是一样的
MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.99.103',3306);
  1. 添加上了，可以看看
MySQL > select * from mysql_servers;
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 192.168.99.102 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 192.168.99.103 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
  1. 当然，这2步少不了。加载并保存到磁盘
MySQL > load mysql servers to runtime;

MySQL > save mysql servers to disk;

添加监控后端节点的用户。ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组

master：192.168.99.102
  1. 这里要在主服务器上创建帐号用来给proxySQL连接用，主服务器会同步给从服务器，所以从服务器就不需要创建了
MySQL> grant replication client on *.* to monitor@'%' identified by '123';
proxySQL: 192.168.99.101
  1. 添加监控后端节点的用户。后面ProxySQL通过每个节点的read_only值来自动调整它们是属于读组还是写组
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='123';
  1. 加载到RUNTIME，并保存到disk
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
  1. 查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常,看最后2条)
MySQL [(none)]> select * from mysql_server_connect_log;
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| hostname       | port | time_start_us    | connect_success_time_us | connect_error                                                           |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
| 192.168.99.103 | 3306 | 1562924476694583 | 0                       | Access denied for user 'monitor'@'192.168.99.101' (using password: YES) |
| 192.168.99.102 | 3306 | 1562924477887066 | 0                       | Access denied for user 'monitor'@'192.168.99.101' (using password: YES) |
| 192.168.99.103 | 3306 | 1562924536694659 | 0                       | Access denied for user 'monitor'@'192.168.99.101' (using password: YES) |
| 192.168.99.102 | 3306 | 1562924537456942 | 0                       | Access denied for user 'monitor'@'192.168.99.101' (using password: YES) |
| 192.168.99.103 | 3306 | 1562924581277763 | 1977                    | NULL                                                                    |
| 192.168.99.102 | 3306 | 1562924582415023 | 4096                    | NULL                                                                    |
+----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
  1. 也可以查看监控心跳信息 (对ping指标的监控)(如果ping_error的结果为NULL则表示正常)
MySQL> select * from mysql_server_ping_log;
  1. 还有其它的，查看read_only和replication_lag的监控日志
MySQL> select * from mysql_server_read_only_log;
MySQL> select * from mysql_server_replication_lag_log;

设置分组信息

  1. 需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：writer_hostgroup写组，reader_hostgroup读组，comment备注, 指定写组的id为10，读组的id为20
MySQL> insert into mysql_replication_hostgroups values(10,20,"test");
  1. 加载到RUNTIME生效并保存
MySQL> load mysql servers to runtime;
MySQL> save mysql servers to disk;
  1. Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+----------------+------+--------+--------+
| hostgroup_id | hostname       | port | status | weight |
+--------------+----------------+------+--------+--------+
| 10           | 192.168.99.102 | 3306 | ONLINE | 1      |
| 20           | 192.168.99.103 | 3306 | ONLINE | 1      |
+--------------+----------------+------+--------+--------+
master: 192.168.99.102
  1. 创建帐号用来访问的
MySQL> grant all on *.* to sqluser@'%' identified by '123';
proxySQL: 192.168.99.101
  1. 在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库
MySQL> insert into mysql_users(username,password,default_hostgroup) values('sqluser','123',10);
  1. 保存生效
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;
  1. 使用sqluser用户测试是否能路由到默认的10写组实现读、写数据。是的，我的写组就是主服务器，也就是server_id=2的主机。
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
  1. 创建个数据库，看看能不能行
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'create database testdb'
#然后去主从服务器上看看，有没有这个数据库
#这里还创建了个表，也去看看有没有
[101]$ mysql -usqluser -p123 testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'

在proxysql上配置路由规则，实现读写分离

与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组

  1. 具体可以这样写
MySQL> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
  1. 保存生效
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;

注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id

  1. 看下生效了没，已经有了2条记录了
MySQL [(none)]> 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$ | 10                    | 1     |
| 2       | 1      | ^SELECT              | 20                    | 1     |
+---------+--------+----------------------+-----------------------+-------+

到这里就可以实现读写分离了

  1. 那就来测试下吧，以事务和非事务的方式进行测试
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id'
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
+-------------+
| @@server_id |
+-------------+
|           3 |
+-------------+
#那为什么会一个是2，一个是3呢。 
#这是因为我们配置的时候，只有SELECT开头的才到从服务器访问，
#事务是以BEGIN或者START TRANSACTION开头的，所以会支访问主服务器。
  1. 创建个表，插入的内容来查查看看吧。
#前面已经把testdb这个数据库创建了
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'use testdb;create table t(id int);'
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
[101]$ mysql -usqluser -p123 -P6033 -h127.0.0.1 -e 'select id from testdb.t'
+------+
| id   |
+------+
|    1 |
+------+

在这里强调下：
(1)进入proxySQL管理界面是：mysql -uadmin -padmin -P6032 -h127.0.0.1，端口号是：6032，默认的帐号密码是admin和admin。
(2)如果使用mysql -usqluser -p123 -P6033 -h127.0.0.1则访问的是主服务器上的数据库了。

  1. 路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL [(none)]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+------------------------------------------------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text                                                                                                |
+----+----------+------------+------------------------------------------------------------------------------------------------------------+
| 10 | 24129    | 3          | show tables                                                                                                |
| 10 | 8328     | 2          | show databases                                                                                             |
| 10 | 6534     | 1          | create table t(id int)                                                                                     |
| 10 | 5587     | 1          | begin                                                                                                      |
| 20 | 4333     | 5          | select @@server_id                                                                                         |
| 10 | 4159     | 3          | show databases                                                                                             |
| 10 | 3427     | 2          | insert testdb.t values (?)                                                                                 |
| 10 | 2839     | 3          | select @@server_id                                                                                         |
| 20 | 1470     | 3          | SELECT DATABASE()                                                                                          |
| 10 | 1222     | 1          | create database testdb                                                                                     |
| 20 | 1024     | 1          | select id from testdb.t                                                                                    |
| 10 | 752      | 1          | show tables                                                                                                |
| 10 | 640      | 1          | start transaction                                                                                          |
| 10 | 581      | 2          | commit                                                                                                     |
| 10 | 543      | 1          | create table t(id int)                                                                                     |
| 20 | 498      | 1          | SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC |
| 10 | 443      | 1          | create table t                                                                                             |
| 10 | 432      | 1          | create table t(id int)                                                                                     |
| 20 | 391      | 1          | SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC |
| 10 | 0        | 19         | select @@version_comment limit ?                                                                           |
+----+----------+------------+------------------------------------------------------------------------------------------------------------+


发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注