读写分离是借助 MySQL 中间件 ProxySQL 实现的
ProxySQL 有两个版本: 官方版和 percona 版, percona 版是基于官方版基础上修改 C++ 语言开发, 轻量级但性能优异 (支持处理千亿级数据) 具有中间件所需的绝大多数功能, 包括:
多种方式的读 / 写分离
定制基于用户, 基于 schema, 基于语句的规则对 SQL 语句进行路由
缓存查询结果
后端节点监控
准备
实现读写分离前, 先实现主从复制
注意: slave 节点需要设置 read_only=1
主机 | IP 地址 | 类型 |
---|---|---|
CentOS7.6 | 192.168.36.101 | Master |
CentOS7.6 | 192.168.36.103 | Slave |
CentOS7.6 | 192.168.36.104 | ProxySQL |
注: 实验之前为保障实验顺利进行, 请关闭主机的 selinux 以及防火墙服务
开始搭建
Master 节点修改数据库配置文件
- [[email protected] ~]#cat /etc/my.cnf
- [mysqld]
- server_id=1 # 为 Master 节点设置一个全局唯一的 ID 号
- binlog_format=row # 基于行复制的数据库语句
- log-bin=/data/bin/MySQL-bin # 启用二进制日志
重新启动数据库服务
- [[email protected] ~]#service mysqld restart
- Restarting mysqld (via systemctl): [ OK ]
Master 节点上创建带有复制权限的用户账号
- MariaDB [(none)]> grant replication slave on *.* to [email protected]'192.168.36.%' identified by 'centos';
- Query OK, 0 rows affected (0.00 sec)
查看 Master 的日志位置信息
- MariaDB [MySQL]> show master logs;
- +------------------+-----------+
- | Log_name | File_size |
- +------------------+-----------+
- | MySQL-bin.000001 | 912372 |
- +------------------+-----------+
- 1 row in set (0.00 sec)
Slave 节点修改配置文件
- [[email protected] ~]#cat /etc/my.cnf
- [mysqld]
- server_id=2 # Slave 节点设置全局唯一的 ID 号
- read_only # 只读
重新启动数据库服务
[[email protected] ~]#systemctl restart mariadb
使用 Master 创建的复制权限的用户账号进行同步
- MariaDB [(none)]> CHANGE MASTER TO
- -> MASTER_HOST='192.168.36.101',
- -> MASTER_USER='repluser',
- -> MASTER_PASSWORD='centos',
- -> MASTER_PORT=3306,
- -> MASTER_LOG_FILE='mysql-bin.000001',
- -> MASTER_LOG_POS=245;
- Query OK, 0 rows affected (0.01 sec)
启动 Slave 线程
- MariaDB [(none)]> slave start;
- Query OK, 0 rows affected (0.00 sec)
查看线程是否启动
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.36.101
- Master_User: repluser
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000001
- Read_Master_Log_Pos: 7389
- Relay_Log_File: mariadb-relay-bin.000002
- Relay_Log_Pos: 7673
- Relay_Master_Log_File: MySQL-bin.000001
- Slave_IO_Running: Yes # 从节点的 IO 线程
- Slave_SQL_Running: Yes # 从节点的 SQL 线程
- ....
- Seconds_Behind_Master: 0 # Master 与 SLave 服务器差别延迟
- .....
- Master_Server_Id: 1
- 1 row in set (0.00 sec)
检查数据同步情况
- MariaDB [(none)]> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | hellodb |
- | MySQL |
- | performance_schema |
- | test |
- +--------------------+
- 5 rows in set (0.00 sec)
配置 ProxySQL 的 YUM 仓库
- [[email protected] ~]#cat <<EOF | tee /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
- > EOF
- [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
安装 ProxySQL
[[email protected] ~]#yum install -y proxysql mariadb
启动 ProxySQL
- [[email protected] ~]#rpm -ql proxysql
- /etc/init.d/proxysql
- ...
由于 proxysql 启动脚本在 init.d 文件中, 所以需要使用 service 启动
- [[email protected] ~]#service proxysql start
- Starting ProxySQL: 2019-05-08 17:58:16 [INFO] Using config file /etc/proxysql.cnf
- DONE!
端口查看
- [[email protected] ~]#netstat -at
- Active Internet connections (servers and established)
- Proto Recv-Q Send-Q Local Address Foreign Address State
- tcp 0 0 0.0.0.0:MySQL 0.0.0.0:* LISTEN
- tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN
- tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN
- # 6033 端口是接收远程用户的连接, 6032 端口是连接, 管理接口
连接 proxysql 管理接口
- [[email protected] ~]#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)]>
ProxySQL 相当于小型的 MySQL, 自带: 用户名 admin, 密码 admin
添加主从节点的地址
- # 添加 Master 节点
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.101',3306);
- Query OK, 1 row affected (0.00 sec)
- # 添加 Slave 节点
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.36.103',3306);
- Query OK, 1 row affected (0.00 sec)
- # 查看添加信息
- MySQL [(none)]> 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.36.101 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- | 10 | 192.168.36.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
- 2 rows in set (0.00 sec)
在 Master 上创建 ProxySQL 管理的账号
- MariaDB [(none)]> grant replication client on *.* to [email protected]'192.168.36.%' identified by 'magedu';
- Query OK, 0 rows affected (0.00 sec)
ProxySQL 配置监控的用户名和密码设置, 使其自动连接主从节点进行调整
- MySQL [(none)]> set MySQL-monitor_username='monitor';
- Query OK, 1 row affected (0.00 sec)
- MySQL [(none)]> set MySQL-monitor_password='magedu';
- Query OK, 1 row affected (0.00 sec)
使配置加载到内存中生效, 并保存到磁盘中
- MySQL [(none)]> load MySQL variables to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [(none)]> save MySQL variables to disk;
- Query OK, 97 rows affected (0.01 sec)
查看监控连接是否正常
- MySQL [(none)]> select *from mysql_server_connect_log;
- +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
- | hostname | port | time_start_us | connect_success_time_us | connect_error |
- +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
- | 192.168.36.101 | 3306 | 1557312316296707 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.101 | 3306 | 1557312557263893 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.101 | 3306 | 1557312616308042 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.103 | 3306 | 1557312617121004 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.103 | 3306 | 1557312676308396 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.101 | 3306 | 1557312676936371 | 0 | Access denied for user 'monitor'@'192.168.36.104' (using password: YES) |
- | 192.168.36.101 | 3306 | 1557312694163848 | 2228 | NULL |
- | 192.168.36.103 | 3306 | 1557312695077512 | 4613 | NULL |
- | 192.168.36.103 | 3306 | 1557312754164398 | 1370 | NULL |
- | 192.168.36.103 | 3306 | 1557312874168899 | 2204 | NULL |
- | 192.168.36.101 | 3306 | 1557312874890981 | 2939 | NULL |
- +----------------+------+------------------+-------------------------+-------------------------------------------------------------------------+
- 22 rows in set (0.00 sec)
设置分组信息
- MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,"test");
- Query OK, 1 row affected (0.00 sec)
查看读写组信息
- MySQL [(none)]> select *from mysql_replication_hostgroups;
- +------------------+------------------+---------+
- | writer_hostgroup | reader_hostgroup | comment |
- +------------------+------------------+---------+
- | 10 | 20 | test |
- +------------------+------------------+---------+
- 1 row in set (0.00 sec)
配置生效并保存到磁盘
- MySQL [(none)]> save MySQL servers to disk;
- Query OK, 0 rows affected (0.02 sec)
查看信息
- MySQL [(none)]> 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.36.101 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- | 20 | 192.168.36.103 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
- +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
- 2 rows in set (0.00 sec)
在 master 上进行创建设置账户, 让客户端连接中间的调度器
- MariaDB [(none)]> grant all on *.* to [email protected]'192.168.36.%' identified by 'magedu';
- Query OK, 0 rows affected (0.00 sec)
ProxySQL 添加记录, 将用户添加到 mysql_users 表中
- MySQL [(none)]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','magedu',10);
- Query OK, 1 row affected (0.00 sec)
查看是否添加成功
- 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|
- +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
- | sqluser | magedu | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000|
- +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
- 1 row in set (0.00 sec)
保存配置
- MySQL [(none)]> load MySQL users to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [(none)]> save MySQL users to disk;
- Query OK, 0 rows affected (0.01 sec)
启用一个客户端连接 ProxySQL 进行测试
- [[email protected] ~]# MySQL -usqluser -pmagedu -h192.168.36.104 -P6033
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 3
- Server version: 5.5.30 (ProxySQL)
- Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- MySQL>
查看连接到哪个主机
- MySQL> select @@server_id;
- +-------------+
- | @@server_id |
- +-------------+
- | 1 |
- +-------------+
- 1 row in set (0.00 sec)
ProxySQL 上定义调度规则
- MySQL [(none)]> insert into mysql_query_rules
- -> (rule_id,active,match_digest,destination_hostgroup,apply) values
- -> (1,1,'^select.*from update$',10,1),(2,1,'^select',20,1);
- Query OK, 2 rows affected (0.00 sec)
生效并存盘
- MySQL [(none)]> load MySQL query rules to runtime;
- Query OK, 0 rows affected (0.00 sec)
- MySQL [(none)]> save MySQL query rules to disk;
- Query OK, 0 rows affected (0.01 sec)
客户端进行读写分离测试
- [[email protected] ~]# MySQL -usqluser -pmagedu -h192.168.36.104 -P6033 -e 'select @@server_id;'
- +-------------+
- | @@server_id |
- +-------------+
- | 2 |
- +-------------+
- [[email protected] ~]# MySQL -usqluser -pmagedu -h192.168.36.104 -P6033 -e 'begin;use db1;insert t1 values(1);select @@server_id;'
- +-------------+
- | @@server_id |
- +-------------+
- | 1 |
- +-------------+
完工
来源: http://www.bubuko.com/infodetail-3051888.html