MySQL 主从架构之读写分离
本实验在配置好的主从架构基础上实现
实验准备
- 主机角色划分:
MySQL主节点 :192.168.50.9 node9
MySQL从节点 :192.168.50.10 node10
MySQL从节点 :192.168.50.16 node16
R/W Splitter:172.16.50.17(外网) 192.168.50.17(内网) node17
- 拓扑图如下
使用ProxySQL实现读写分离
注意:ProxySQL可以同时实现对多个组的读写分离调度,本实验仅测试对一个组进行读写分离调度
- 安装proxysql程序
yum install ./proxysql-1.3.6-1-centos7.x86_64.rpm
- 修改配置文件
cp /etc/proxysql.cnf{,.bak}
vim /etc/proxysql.cnf
#修改mysql_variables配置段
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:3306;/tmp/proxysql.sock"
default_schema="mydb"
stacksize=1048576
server_version="5.5.30"
connect_timeout_server=3000
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
#修改mysql_servers配置段
mysql_servers =
(
{
address = "192.168.50.9" # no default, required . If portis 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_replication_lag = 200 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
},
{
address = "192.168.50.10" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is0 , address is interpred as a Unix Socket Domain
hostgroup = 1 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_replication_lag = 500 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
},
{
address = "192.168.50.16" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is0 , address is interpred as a Unix Socket Domain
hostgroup = 1 # no default, required
status = "ONLINE" # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
max_replication_lag = 500 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned
}
)
#修改mysql_user配置段
mysql_users:
(
{
username = "myadmin" # no default , required
password = "mypass" # default: ''
default_hostgroup = 0 # default: 0
active = 1 # default: 1
default_schema="mydb"
}
)
#定义mysql_query_rules配置段,定义查询规则可实现防火墙的功能,如:对未设置 WHERE 子句的 SELECT 语句,将该危险操作调度到一个不存在的组,从而避免危险操作。
mysql_query_rules:
(
)
#定义读写分离配置段
mysql_replication_hostgroups=
(
{
writer_hostgroup=0
reader_hostgroup=1
comment="test repl 1"
}
)
- 在node9(MySQL主节点)服务器上为ProxySQL代理节点建立用户并授权
注意:修改主节点数据库中的数据,从节点会自动同步修改数据
GRANT ALL ON *.* TO 'myadmin'@'192.168.50.%' IDENTIFIED BY'mypass';
FLUSH PRIVILEGES;
- 在node17启动proxysql服务并连接本地mysql服务
service proxysql start:ss -tnl
#使用预先建立的账号密码登录node9服务器
mysql -umyadmin -pmypass -h192.168.50.17
#登录成功后会显示下面提示信息
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL)
读写分离操作至此已经完成。
- 在node17登录proxysql的内置管理接口
mysql -uadmin -hlocalhost -padmin -S '/tmp/proxysql_admin.sock'
#登录成功显示如下内容
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL Admin Module)
执行下面命令,查看proxysql的代理信息
MySQL [(none)]> SHOW DATABASES;
+-----+---------+-------------------------------+
| seq | name | file |
+-----+---------+-------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
+-----+---------+-------------------------------+
MySQL [(none)]> use monitor;
MySQL [(none)]> SELECT * FROM mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0 | 192.168.50.9 | 3306 | ONLINE | 1 | 0 | 1000 | 200 | 0 | 0 | |
| 1 | 192.168.50.10 | 3306 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
| 1 | 192.168.50.16 | 3306 | ONLINE | 1 | 0 | 1000 | 500 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
MySQL [monitor]> SELECT * FROM runtime_mysql_replication_hostgroups;
+------------------+------------------+-------------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+-------------+
| 0 | 1 | test repl 1 |
+------------------+------------------+-------------+
- ProxySQL支持运行时修改代理配置,可以在内置管理接口登录后修改数据库内容实现增删组成员,定义读写分离组等配置。