关键词：
读写分离是借助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 |
+-------------+完工
mysql(24)—读写分离的基本概念和实现方式(代码片段)
...指将数据库的读和写操作分不到不同的数据库节点上。主服务器负责处理写操作和实时性要求较高的读操作，从服务器负责处理读操作。读写分离减缓了数据库锁的争用，可以大幅提高读性能，小幅提高写的性能... 查看详情
主从复制和读写分离(代码片段)
...扑图：思路：1.搭建Mysql主从复制1）Mysql主从服务器时间同步2)主服务器的mysql配置3）从服务器的mysql配置4）验证主从复制效果2.搭建Mysql读写分离1）Amoeba服务器配置安装java环境安装Amoeba软件2）主从服务... 查看详情
mysql主从复制与读写分离的理论+实操（配有详细图释）(代码片段)
...搭建主从复制与读写分离4.1实验环境4.2时间同步（主服务器）4.3安装MySQL4.4配置主从同步4.5验证主从复制4.5搭建读写分离4.6配置amoeba4.7MySQL客户端测试五、总结5.1一定要关闭防火墙！！！5.2主从复制5.3读写分离... 查看详情
amoeba+mysql实现读写分离
...据库前端代理层Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行。Amoeba相当于一个SQL请求的路由器，目的是为负载... 查看详情
mysql主从复制和读写分离
1.Mysql主从复制的原理：MySQL主服务器更新数据，执行命令，将会记录到自己的二进制日志里面，然后从服务器开始一个I/O线程，用于读取主服务器的已更新或以变化的二进制文件，前提是主服务器要给从服务器这个权限，读... 查看详情
amobea读写分离
...意思是变型虫，Amoeba是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy。它集中地响应应用的请求，依据用户事先设置的 规则，将SQL请求发送到特定的数据库上执行。基于此可以实现负载均衡、读写分离、... 查看详情
数据库应用——atlas代理mysql集群实现读写分离(代码片段)
...实现读写分离一、Atlas简介和架构1.1环境准备1.2配置时间服务器二、主服务器配置2.1master节点1的配置2.2master节点2的配置2.3主服务器配置完成后的测试三、从服务器配置3.1slave节点1的配置3.1slave节点2的配置3.3从服务器配置完成后... 查看详情
完结撒花mysql（二十三）主从复制(代码片段)
...读写分离除了能使数据库提高并发处理能力之外，还能对服务器进行负载均衡，能够让不同的读请求按照分配策略分布到不同的从服务器上减少了锁表的影响，主库出现写锁的时候，不会影响从库的读操作如上原因使得读操作更... 查看详情
主从复制和读写分离(代码片段)
...扑图：思路：1.搭建Mysql主从复制1）Mysql主从服务器时间同步2)主服务器的mysql配置3）从服务 查看详情
atlas读写分离(代码片段)
...加了很多功能特性。目前该项目在360公司内部得到了广泛应用，很多MySQL业务已经接入了Atlas平台，每天承载的读写请求数达几十亿条。主要功能：1.读写分离2.从库负载均衡3.IP过滤4.自动分表5.DBA可平滑上下线DB6.自动摘除宕机的D... 查看详情
mysql读写分离(代码片段)
...ySQL读写分离五.测试读写分离一.原理读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把主数据库上事务性查询导致的变更同... 查看详情
mysql读写分离实现方式
...分离的前提是：主从同步。也就是需要2台以上的数据库服务器，分别安装部署好MySQL服务。然后，再在其中一台选择为主库，另外一台选择为从库。主库可以读写，丛库只能读。然后再基于mycat中间件和mysql进行集成来实现读写... 查看详情
mysql主从复制与读写分离
文章转自：MySQL主从复制与读写分离MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的... 查看详情
mysql主从复制及读写分离实际部署与验证(代码片段)
...、主从复制1.原理：2.准备来做主从复制和读写分离的服务器如下：3.主从复制具体步骤1.关闭所有服务器的防火墙2.Mysql主从服务器都进行时间同步3.主从服务器mysql配置4.从服务器的mysql配置5.Mysql主从复制的效果二、读写... 查看详情
mysql主从复制与读写分离（原理深刻，过程详细，值得一看）(代码片段)
...实现(2)基于中间代理层实现二、MySQL主从复制架构搭建1.服务器 查看详情
mysql主从复制以及读写分离（❤❤❤❤含理论和实验❤❤❤❤大家中秋快乐！㊗）(代码片段)
...离的概念和原理2.2、进行读写分离的原因2.3、读写分离的应用场景2.4、目前较为常见的MySQL读写分离①基于程序代码内部实现 查看详情
mysql主从复制以及读写分离（❤❤❤❤含理论和实验❤❤❤❤大家中秋快乐！㊗）(代码片段)
...离的概念和原理2.2、进行读写分离的原因2.3、读写分离的应用场景2.4、目前较为常见的MySQL读写分离①基于程序代码内部实现 查看详情
mysql数据切分(分库分表),读写分离和主从复制
参考1参考2对于一个大型的互联网应用，每天几十亿的PV无疑对数据库造成了相当高的负载。对于系统的稳定性和扩展性造成了极大的问题。通过数据切分来提高网站性能，横向扩展数据层已经成为架构研发人员首选的方式。数... 查看详情