实验前准备： 要实现MySQL/MariaDB的读写分离，首先要准备好MySQL/MariaDB主从复制架构： 主服务器：172.16.75.1，写操作服务器 两台从服务器：172.16.75.2和172.16.74.2，读操作服务器 除主从复制架构外，还需要准备一台布置中间件的主机，这里是172.16.74.1
172.16.75.1主服务器中/etc/my.cnf中[mysqld]配置段配置： [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 log_bin=/var/lib/mysql/binlog server_id=101 innodb_file_per_table=ON skip_name_resolve=ON sync_binlog=1 innodb_flush_log_at_trx_commit=1 relay_log=/var/lib/mysql/slavelog
172.16.75.2从服务器中/etc/my.cnf中[mysqld]配置段配置： [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 innodb_file_per_table=ON skip_name_resolve=ON server_id=201 relay_log=/var/lib/mysql/slavelog set @@global.read_only=ON sync_binlog=1 innodb_flush_log_at_trx_commit=1
172.16.74.2从服务器中/etc/my.cnf中[mysqld]配置段配置： [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 innodb_file_per_table=ON skip_name_resolve=ON server_id=301 relay_log=/var/lib/mysql/slavelog set @@global.read_only=ON sync_binlog=1 innodb_flush_log_at_trx_commit=1
为了试验的精确性，四台主机均关闭防火墙以及排除SELinux的影响。
一、MySQL Router实现MySQL/MariaDB读写分离：
MySQLRouter一般不用于生产环境当中，端口实现读写分离，需要在应用程序端配置端口，会造成一系列问题，所以，测试环境中体验一下就可以了
原理：MySQL Router能够分析来自于前端应用程序或客户端发送来的SQL请求是读请求还是写请求，从而决定该SQL请求是发送给后端的Master还是Slave，以及发送给哪个Master或者哪个Slave；
MySQL Router实现路由功能，要通过一个的特有的模块routing.so模块实现 MySQL Router读写分离的实现方式：通过不同的端口接收用户的请求，从而实现简单的读写分离；而且对于读请求的调度算法，只能使用默认的RR（轮询）算法
安装方法： 从MySQL官方站点获取安装包：https://dev.mysql.com/downloads/router/
安装：[root@mysqlrouter ~]# yum install mysql-router-2.1.6-1.el7.x86_64.rpm
启动：[root@mysqlrouter ~]# systemctl start mysqlrouter.service
查看启动状态：
[root@mysqlrouter ~]# ss -tnl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 172.16.74.1:40081 :
LISTEN 0 128 172.16.74.1:40082 :
在 /etc/mysqlrouter/mysqlrouter.conf 中配置如下： [root@mysqlrouter ~]# vim /etc/mysqlrouter/mysqlrouter.conf
[DEFAULT] logging_folder = /var/log/mysqlrouter/ plugin_folder = /usr/lib64/mysqlrouter runtime_folder = /var/run/mysqlrouter config_folder = /etc/mysqlrouter
[logger] level = info
[keepalive] interval = 60
[routing:masters] #主服务器配置段# #Router监听的前端SQL请求的地址和端口40081（非著名端口号即可）# bind_address = 172.16.74.1:40081 #定义routing规则的转发目标地址及端口号（可以监听多个写服务器）# destinations = 172.16.75.1:3306 #模式：只写# mode = read-write #为防止网络原因影响超时错判，所以讲连接超时时间设置为2s（大于1s即可）# connect_timeout = 2
[routing:slaves] #从服务器配置段# #监听# bind_address = 172.16.74.1:40082 destinations = 172.16.75.2:3306,172.16.74.2:3306 #模式：只读# mode = read-only connect_timeout = 1
注意：目前的MySQL Router 2.1.6版本中，可以实现后端服务器的健康状态检测；一旦发现后端的Master或Slave不可用，则会将其暂时移除出可调度列表；而检测到服务可用之后，再自动添加进可调度列表；
然后在主服务器Master中授权测试用户，这次测试用来体现MySQL Router基于端口实现路由转发： MariaDB [mysql]> grant all on . to 'test'@'172.16.74.%' identified by '123'; 刷新授权表： MariaDB [mysql]> flush privileges;
测试读写分离： [root@mysqlrouter ~]# mysql -utest -h172.16.74.1 -P40081 -p123 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 101 | +-------------+ [root@mysqlrouter ~]# mysql -utest -h172.16.74.1 -P40082 -p123 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 201 | +-------------+
二、使用ProxySQL实现MySQL/MariaDB读写分离： 功能强大的MySQL中间件； 官方站点：http://www.proxysql.com 官方的github：https://github.com/sysown/proxysql/releases/tag/v1.4.9
Percona ProxySQL的文档手册：https://www.percona.com/doc/percona-xtradb-cluster/5.7/howtos/proxysql.html
ProxySQL是使用C++语言开发的，轻量级的产品，但性能非常好，功能很多，几乎能够满足中间件所需的绝大多数的功能，主要包括： 1.读/写分离，且支持多种方式； 2.可以定义基于用户、基于Schema、基于SQL语句的规则对SQL语句进行路由转发；规则很灵活； 3.基于schema和语句级别的规则，可以实现简单的数据库分片功能；(Sharding) 4.可以通过查询缓存来缓存查询结果； 5.监控后端服务器节点：后端服务器的心跳信息，后端服务器的read-only/read-write信息，Slave和Master的数据同步的延迟性(replication lag)；
获取安装程序包： 两个发行版本： 官方的ProxySQL Percona的ProxySQL
通过官方站点或官方的github项目，或Percona官方及Percona资源的镜像站点；
安装ProxySQL：[root@proxysql ~]# yum install proxysql
启动和关闭服务：[root@proxysql ~]# service proxysql start/stop
验证服务启动的结果：
[root@proxysql ~]# ss -tnlp
LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=35296,fd=23))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=22))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=21))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=20))
LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=35296,fd=19))
看到上述结果，说明proxysql服务启动成功；
登录方式：
可以使用mysql客户端工具连接到proxysql的管理接口，该管理接口有默认的管理员账户和密码，都是admin；
[root@localhost ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
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)]>
** 实验中所涉及到的库表的含义：** 默认情况下，ProxySQL提供了几个库，每个库都有各自的意义；查看所有库的方法： 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 | +-----+---------------+-------------------------------------+
主要介绍main库和monitor库相关的表的功能；
MySQL [main]> show tables from main;
+--------------------------------------------+
| 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 [main]> show tables from monitor;
+------------------------------------+
| tables |
+------------------------------------+
| mysql_server_connect_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+------------------------------------+
注意：
1.所有的以"runtime_"开头的表都是运行时的配置，是不能修改的。要完成ProxySQL的配置，仅能修改那些不是以"runtime_"开头的表；
2.在执行表的修改之后，必须手动的将修改结果同步至运行时环境和持久化保存到磁盘；
LOAD ... TO RUNTIME;
将指定的表中的修改结果同步至运行时环境；
SAVE ... TO DISK;
将指定的表中的修改结果同步至磁盘以实现持久化存储；
注意：“...”表示表名，去掉真实表名中的"_"符号用空白字符代替；
示例：修改了msyql_servers表，
load mysql servers to runtime;
save mysql servers to disk;
mysql_servers表中有很多个字段，每个字段都有十分重要的意义；
MySQL [main]> show create table mysql_servers;
字段 数据类型 是否为空 字段默认值
hostgroup_id INT NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL
port INT NOT NULL DEFAULT 3306,
status VARCHAR NOT NULL DEFAULT 'ONLINE',
weight INT NOT NULL DEFAULT 1,
compression INT NOT NULL DEFAULT 0,
max_connections INT NOT NULL DEFAULT 1000,
max_replication_lag INT NOT NULL DEFAULT 0,
use_ssl INT NOT NULL DEFAULT 0,
max_latency_ms INT NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
各字段的含义：
hostgroup_id：
后端MySQL实例所在的服务器所在的主机组的编号；注意：一台主机可能有多种角色，可能存在于多个主机组中；
hostname：
后端的MySQL实例所在的服务器监听的IP地址；该字段没有默认值，意味着在添加一个新的后端服务器节点时，必须指定该字段的值；
port：
后端的MySQL实例所在的服务器监听的端口号；默认值为3306，通常使用默认值即可；
status：
后端MySQL服务器的运行时状态，有四种状态值：'ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD'
ONLINE：此状态表示后端MySQL服务器节点完全正常；
SHUNNED：此状态表示后端MySQL服务器节点暂时被ProxySQL自动忽略，可能的原因是在一个较短时间内发生了大量的连接错误，也可能是Slave端与Master端之间的数据延迟过大；
OFFLINE_SOFT：此状态表示ProxySQL不会再向此服务器转发任何请求，但此服务器上尚未完成的事务会继续执行，直到所有事务执行完毕之后，会进入"非活跃状态"；graceful stop；
'OFFLINE_HARD：此状态表示ProxySQL不会再向此服务器转发任何请求，同时此服务器上正在执行的事务立即中断结束，ProxySQL也会临时将其移除出主机组，常用于维护操作；
weight：对应后端服务器在主机组中的权重，权重值越高，ProxySQL就会向其转发越多的请求；默认值为1；
compression：标记ProxySQL和后端MySQL服务器之间建立的连接中，是否会先压缩数据，再传输；默认值为0；
如果该字段值为0，则不压缩传输；
如果该字段值大于0，则压缩后传输；
max_connections：表示ProxySQL与后端MySQL服务器之间允许建立的最大连接数；一旦达到最大值，即使后端服务器的权重值再大，也不会再建立新的连接；默认值为1000，表示每个后端MySQL服务器最多同时接受1000个来自于ProxySQL的连接；
通过合理的定义该字段的值，可以避免后端MySQL服务器超负荷运转；
max_replication_lag：用于表示后端的SLave端在复制时延后于Master多长时间，如果超出该字段定义的上限值，proxySQL会暂时忽略此服务器，直到Slave的复制内容赶上Master为止(数据一致)；
use_ssl：表示是否允许ProxySQL和后端MySQL服务器之间基于SSL协议建立连接；
max_latency_ms：监控模块定期向后端MySQL服务器发起ping(SELECT)检查操作的延迟时间；
comment：注释信息，说明信息，可以随意定义的内容，主要起辅助提示作业；
向ProxySQL插入监控节点： MySQL [main]> insert into mysql_servers (hostgroup_id,hostname,port) values (10,'172.16.75.3',3306),(10,'172.16.75.4',3306),(10,'172.16.75.5',3306);
监控后端的MySQL节点： 添加节点完成之后，更重要的是监控各后端节点；后端必须是主从复制的环境；而ProxySQL会读取后端MySQL服务器的read_only服务器参数的值，以区分该服务器节点是属于"读组"还是"写组"；
在后端的Master服务器上创建一个用于监控功能的用户，该用户仅需要有"USAGE"权限即可；如果想要监控在复制结构中Slave端是否与Master端存在验证的延迟或滞后的状态，即replication lag状态，还需要让该监控用户具备"REPLICARION CLIENT"权限；
**创建监控用户的方法：**
在Master端执行下列SQL语句：
MariaDB [(none)]> grant replication client,replication slave on *.* to 'mmonitor'@'172.16.74.%' identified by '123';
在ProxySQL端执行下列SQL语句：
MySQL [main]> set mysql-monitor_username='mmonitor';
MySQL [main]> set mysql-monitor_password='qhdlink';
实际上是在修改global_variables表中对应的变量的值，所以可以直接使用UPDATE语句来修改对应的字段的值：
UPDATE global_variables SET variable_value='mmonitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='qhdlink' WHERE variable_name='mysql-monitor_password';
让此前添加的后端MySQL服务器节点及用于监控各节点的用户生效：
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
MySQL [main]> load mysql variables to runtime;
MySQL [main]> save mysql variables to disk;
** 验证监控的结果：** ProxySQL监控模块的指标都保存在monitor库的各log表中； 如果想查看连接是否正常的监控信息，在mysql_server_connect_log表中 MySQL [(none)]> select * from mysql_server_connect_log limit 3; +-------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +-------------+------+------------------+-------------------------+---------------+ | 172.16.74.2 | 3306 | 1533700332922373 | 1945 | NULL | | 172.16.75.1 | 3306 | 1533700332933127 | 1689 | NULL | | 172.16.75.2 | 3306 | 1533700332944270 | 50012 | NULL | +-------------+------+------------------+-------------------------+---------------+ 3 rows in set (0.00 sec)
可能会看到一些connect_error信息，这是因为此前并没有设置正确的用于监控的用户账户信息；在成功的配置监控用户之后，connect_error自动的值应该是"NULL"，表示连接正常；
如果想查看后端MySQL服务器的心跳信息的监控，在mysql_server_ping_log表中；
MySQL [(none)]> select * from mysql_server_ping_log limit 3;
+-------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+-------------+------+------------------+----------------------+------------+
| 172.16.74.2 | 3306 | 1533700268376052 | 649 | NULL |
| 172.16.75.1 | 3306 | 1533700268377872 | 602 | NULL |
| 172.16.75.2 | 3306 | 1533700268379770 | 824 | NULL |
+-------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)
由于尚未对后端MySQL服务器进行明确的节点分组，所以mysql_server_read_only_log和mysql_server_replication_lag_log都是空的；
要想定义后端MySQL服务器的分组，在mysql_replication_hostgroups表中添加对应组的ID即可；
writer_hostgroup
reader_hostgroup
默认mysql_replication_hostgroups表是空表，想要定义读组和写组的ID，需要向该表中插入数据：
MySQL [main]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values (10,2);
** 注意：应该保证插入的组的ID应该与此前插入的主机所定义的组ID相同；**
此时并不能立即生效，查看mysql_servers表中各主机所属组的时候，看到的是旧的数据；
MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
+--------------+-------------+------+
| hostgroup_id | hostname | port |
+--------------+-------------+------+
| 10 | 172.16.75.1 | 3306 |
| 10 | 172.16.75.2 | 3306 |
| 10 | 172.16.74.2 | 3306 |
+--------------+-------------+------+
将此前修改的内容加载到runtime之后，才能使其生效；
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
再查看主机分组的信息：
MySQL [main]> select hostgroup_id,hostname,port from mysql_servers;
+--------------+-------------+------+
| hostgroup_id | hostname | port |
+--------------+-------------+------+
| 10 | 172.16.75.1 | 3306 |
| 2 | 172.16.75.2 | 3306 |
| 2 | 172.16.74.2 | 3306 |
+--------------+-------------+------+
Monitor模块会根据后端MySQL服务器上的read_only服务器变量的值判断并将对应的节点自动移动到读组或写组中；
在monitor库的mysql_server_read_only_log表中就会有后端MySQL服务器的read_only属性；
MySQL [main]> select * from mysql_server_read_only_log limit 3;
+-------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+-------------+------+------------------+-----------------+-----------+-------+
| 172.16.75.1 | 3306 | 1533627317448205 | 2403 | 0 | NULL |
| 172.16.75.2 | 3306 | 1533627317449233 | 2670 | 1 | NULL |
| 172.16.74.2 | 3306 | 1533627317450825 | 2213 | 1 | NULL |
+-------------+------+------------------+-----------------+-----------+-------+
管理mysql_users 上述所有配置都是与后端MySQL服务器节点相关的，除此之外，ProxySQL还可以配置与SQL语句相关的内容，包括：发送SQL请求的用户，SQL语句路由规则，SQL的查询缓存，SQL语句的重写等；
发送SQL请求的用户的相关配置：
root
sqlsender
在后端的Master服务器上创建对应用户账户：
MariaDB [(none)]> grant all on *.* to 'root'@'172.16.74.%' identified by '123';
MariaDB [(none)]> grant all on *.* to 'sqlsender'@'172.16.74.%' identified by '123';
在ProxySQL上，需要向mysql_users表中添加用户账户；
MySQL [main]> insert into mysql_users (username,password,default_hostgroup) values ('root','123',10),('sqlsender','123',2);
添加用户之后，需要将修改后的数据加载至runtime及持久化存储至磁盘：
MySQL [main]> load mysql users to runtime;
MySQL [main]> save mysql users to disk;
注意：在向mysql_users表中添加用户时，至少要指定username，password和default_hostgroup三个字段；
username：前端连接到ProxySQL以及ProxySQL将SQL语句路由至MySQL时所使用的用户名；
password：用户对应的密码，可以是明文保存，也可以使用hash加密的密码存放，如果想要使用hash密码，需要借助于PASSWORD('password_string')函数；
default_hostgroup：用于为用户名设置默认的路由目标；
其他的字段的含义：
active：对应的用户是否为有效用户；
transaction_persistent：是否启用事务持久化，如果值为1，意味着当某连接使用该用户开启了一个事务之后，此事务在提交或回滚之前，所有的语句都被路由到同一组中，以避免同一事务的SQL语句分散到不同组，从而引发数据混乱或事务无法正常提交执行；默认值为1；此字段在较早的proxySQL版本中有默认值为0的情况，如果出现此类情况，在添加用户时必须手动设置其值为1，而不使用默认值；
查看mysql_users表的内容：
MySQL [(none)]> select * from mysql_users; +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ | root | 123 | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | sqlsender | 123 | 1 | 0 | 2 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | +-----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 2 rows in set (0.00 sec)
使用主机172.16.74段的任意主机（这里使用172.16.74.1），测试基于用户账户的读写分离机制，读请求访问由结果可知默认的访问后台读服务器是随机访问而不是轮询方式访问： [root@mysqlrouter ~]# mysql -uroot -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 101 | +-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 201 | +-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.75.2 -P6033 -p123456 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 301 | +-------------+
基于SQL语句实现读写分离 需要添加路由规则： 与查询规则有关的表有两个： mysql_query_rules mysql_query_rules_fast_routing
mysql_query_rules_fast_routing是mysql_query_rules的扩展表，在proxysql 1.4.7以后才出现，支持快速路由表
mysql_query_rules常用的配置字段：
rule_id：规则编号，自动增长的整数，可以不指定；
active：规则是否有效，默认值为0，表示无效，需要在定义规则时，将其设置为1；
match_digest：定义规则的具体匹配内容；由正则表达式元字符组成，用来匹配SQL语句；
destination_hostgroup：对于符合规则的请求，设置目标主机组，从而实现路由转发；
apply：是否有效提交；默认值为0，表示无效，需要在定义规则时，将其值设置为1；
添加查询路由规则：
MySQL [none]> insert into mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) values (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',2,1);
查看新创建的路由规则：
MySQL [(none)]> select * from mysql_query_rules\G;
*************************** 1. row *************************** rule_id: 1 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: ^SELECT.*FOR UPDATE$ match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULL destination_hostgroup: 10 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL log: NULL apply: 1 comment: NULL *************************** 2. row *************************** rule_id: 2 active: 1 username: NULL schemaname: NULL flagIN: 0 client_addr: NULL proxy_addr: NULL proxy_port: NULL digest: NULL match_digest: ^SELECT match_pattern: NULL negate_match_pattern: 0 re_modifiers: CASELESS flagOUT: NULL replace_pattern: NULL destination_hostgroup: 2 cache_ttl: NULL reconnect: NULL timeout: NULL retries: NULL delay: NULL next_query_flagIN: NULL mirror_flagOUT: NULL mirror_hostgroup: NULL error_msg: NULL OK_msg: NULL sticky_conn: NULL multiplex: NULL log: NULL apply: 1 comment: NULL 2 rows in set (0.00 sec)
ERROR: No query specified
将规则的修改加载值runtime并持久化存储至磁盘： MySQL [main]> load mysql query rules to runtime; MySQL [main]> save mysql query rules to disk;
测试事务持久化： 在Master主服务器的MySQL交互模式中： 先创建数据库mytest： MariaDB [mysql]> use mytest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed MariaDB [mytest]> show tables; +------------------+ | Tables_in_mytest | +------------------+ | t1 | +------------------+ 1 row in set (0.00 sec)
[root@mysqlrouter ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'set @@autocommit=0;
start transaction;
use mytest;
insert into t1 values (1000),(2000);
select @@server_id;
commit;
'
+-------------+
| @@server_id |
+-------------+
| 101 |
+-------------+
测试proxysql基于SQL请求实现读写分离： -P指定6032是管理端口，指定6033是查询端口
[root@proxysql ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id for update;' +-------------+ | @@server_id | +-------------+ | 101 | +-------------+
[root@proxysql ~]# mysql -uroot -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 101 | +-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 201 | +-------------+
[root@mysqlrouter ~]# mysql -usqlsender -h172.16.74.1 -P6033 -p123 -e 'select @@server_id;' +-------------+ | @@server_id | +-------------+ | 301 | +-------------+
修改proxysql默认端口的办法： proxysql一旦启动起来，其数据库文件已经形成的情况下，配置文件的内容将会被跳过，要是想修改内容的话，必须在运行时环境设置，并且保存到文件才可以。
步骤：
先在运行时环境（proxysql的交互模式中）设置如下：
MySQL [(none)]> set mysql-interfaces='0.0.0.0:3306';
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 95 rows affected (0.03 sec)
MySQL [(none)]> select @@mysql-interfaces;
+--------------------+
| @@mysql-interfaces |
+--------------------+
| 0.0.0.0:3306 |
+--------------------+
1 row in set (0.00 sec)
然后重启proxysql服务就可以了
[root@mysqlrouter ~]# service proxysql stop
Shutting down ProxySQL: DONE!
[root@mysqlrouter ~]# service proxysql start
Starting ProxySQL: DONE!
查看监听端口状态：
[root@mysqlrouter ~]# ss -tnlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=22))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=21))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=20))
LISTEN 0 128 *:3306 *:* users:(("proxysql",pid=36322,fd=19))
LISTEN 0 128 *:111 *:* users:(("rpcbind",pid=758,fd=8))
LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=36322,fd=23))
至此，MySQLRouter和Proxysql实现MySQL/MariaDB读写分离部署完成。
