读写分离的工作原理:在大型网站业务中,当单台数据库无法满足并发需求时,通过主从同步方式同步数据。设置一台主服务器负责增、删、改,多台从服务器负责查询,从服务器从主服务器同步数据以保持一致性,从而提高数据库的并发和负载能力。
简单来说,读写分离就是将数据库操作分为“读”和“写”两部分,分别由不同的服务器处理。主服务器(通常是单台)主要负责处理写操作(如插入、更新、删除),而从服务器(通常是多台)则主要负责处理读操作(如查询)。主从服务器之间通过主从同步机制保持数据的一致性。通过这种方式,可以显著提高数据库的并发处理能力和负载能力,从而减轻单台服务器的压力。
机器名称 | IP配置 | 服务角色 | 备注 |
---|---|---|---|
proxy | 192.168.20.149 | proxysql控制器 | 用于监控管理 |
master | 192.168.20.150 | 数据库主服务器 | |
slave1 | 192.168.20.146 | 数据库从服务器 | |
slave2 | 192.168.20.148 | 数据库从服务器 |
基于GTID实现mysql8.0主从同步,配置过程略。
基本命令:
开启gtid,并设置server_id值
gtid_mode=ON
enforce-gtid-consistency=ON建立主从同步
mysql> CHANGE MASTER TO
> MASTER_HOST = host,
> MASTER_PORT = port,
> MASTER_USER = user,
> MASTER_PASSWORD = password,
> MASTER_AUTO_POSITION = 1;mysql> START SLAVE;
mysql> show slave status \G
........
Slave_IO_Running: Yes
Slave_SQL_Running: Yes..........
查看slave,双yes就代表成功
本人博客另外一篇文章可以直接拿
yum install -y proxysql
启动 ProxySQL
- [root@proxy ~]# systemctl enable --now proxysql
- #先启服务,只需要mysql客户端,直接下mariadb就行了
- # 管理员登录
- [root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
成功登录后可以查看一下库看看是否正常
在 Master (192.168.20.150) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户
- create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
- grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;
-
- #proxysql 的对外访问账户
- create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
- grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;
- MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
- Query OK, 1 row affected (0.00 sec)
-
- MySQL [(none)]> load mysql servers to runtime;
- Query OK, 0 rows affected (0.01 sec)
-
- MySQL [(none)]> save mysql servers to disk;
- Query OK, 0 rows affected (0.02 sec)
注意:ProxySQL会根据server的read_only的取值将服务器进行分组。read_only=0的server,master被分到编号为1的写组,read_only=1的server,slave则分到编号为0的读组
所以创建完成之后需要在两个从服务器配置文件(/etc/my.cnf)添加read_noly=1。
- MySQL [(none)]> select * from mysql_replication_hostgroups;
- +------------------+------------------+------------+---------+
- | writer_hostgroup | reader_hostgroup | check_type | comment |
- +------------------+------------------+------------+---------+
- | 1 | 0 | read_only | proxy |
- +------------------+------------------+------------+---------+
- 1 row in set (0.00 sec)
通过查询我们可以清晰的看到我们所分的组
在proxySQL端添加主从服务器的节点,并保存
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.20.150',3306);
- Query OK, 1 row affected (0.00 sec)
-
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.146',3306);
- Query OK, 1 row affected (0.00 sec)
-
- MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.148',3306);
- Query OK, 1 row affected (0.00 sec)
-
- MySQL [(none)]> load mysql servers to runtime;
- Query OK, 0 rows affected (0.01 sec)
-
- MySQL [(none)]> save mysql servers to disk;
- Query OK, 0 rows affected (0.00 sec)
重要的信息是要保证主从服务器都是online状态
- MySQL [(none)]> use monitor
- 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
- MySQL [monitor]> set mysql-monitor_username='monitor';
- Query OK, 1 row affected (0.00 sec)
-
- MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';
- Query OK, 1 row affected (0.00 sec)
-
- 修改后,保存到runtime和disk
- MySQL [monitor]> load mysql variables to runtime;
- MySQL [monitor]> save mysql variables to disk;
-
-
- 查看监控账号【ProxySQL】
- SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
- //也可以这样快速定位
- MySQL [(none)]> select @@mysql-monitor_username;
- +--------------------------+
- | @@mysql-monitor_username |
- +--------------------------+
- | monitor |
- +--------------------------+
- 1 row in set (0.00 sec)
- MySQL [(none)]> select @@mysql-monitor_password;
- +--------------------------+
- | @@mysql-monitor_password |
- +--------------------------+
- | Monitor@123.com |
- +--------------------------+
- 1 row in set (0.00 sec)
ProxySQL 监控模块的指标都保存在monitor库的log表中 以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常
MySQL [(none)]> select * from mysql_server_read_only_log;
Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组
一些监控的状态斗志在log相关,都在monitor库下面的 global_variables 变量。
前面已经配置:配置ProxySQL 账户,我创建的对外访问账户是:用户:proxysql,密码:123456
将对外访问账号添加到mysql_users表中:
- MySQL [monitor]> insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
- Query OK, 1 row affected (0.000 sec)
-
- MySQL [monitor]> load mysql users to runtime;
- Query OK, 0 rows affected (0.000 sec)
-
- MySQL [monitor]> save mysql users to disk;
- Query OK, 0 rows affected (0.007 sec)
-
- MySQL [monitor]> select * from mysql_users\G
- *************************** 1. row ***************************
- username: proxysql
- password: 123456
- active: 1
- use_ssl: 0
- default_hostgroup: 1
- default_schema: NULL
- schema_locked: 0
- transaction_persistent: 1
- fast_forward: 0
- backend: 1
- frontend: 1
- max_connections: 10000
- attributes:
- comment:
- 1 row in set (0.000 sec)
注:transaction_persistent 如果为1,则一个完整的SQL只可能路由到一个节点;这点非常重要,主要解决这种情况:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,那么读取到的结果必然是脏数据。所以一般情况下,该值应该设置为1,尤其是业务中使用到事务机制的情况(默认为0)
- [root@slave1 ~]# mysql -h192.168.20.149 -uproxysql -p'123456' -P 6033
- mysql: [Warning] Using a password on the command line interface can be insecure.
- 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, 2022, Oracle and/or its affiliates.
-
- 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> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | db1 |
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 5 rows in set (0.01 sec)
-
- mysql> select @@server_id;
- +-------------+
- | @@server_id |
- +-------------+
- | 21 |
- +-------------+
- 1 row in set (0.00 sec)
-
- #通过proxysql用户,创建一个keme库
- mysql> create database keme;
- Query OK, 1 row affected (0.00 sec)
在slave2:192.168.20.148上去验证一下,是否同步过去keme这个库
- MySQL [monitor]> insertintomysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);
- l query rules to runtime;
- save mysql query rulQuery OK, 1 row affected (0.000 sec)
-
- es to disk;MySQL
- [monitor]>
- MySQL [monitor]> insert into values(2,1,'^select',0,1);mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
- Query OK, 1 row affected (0.000 sec)
-
- MySQL [monitor]> load mysql query rules to runtime;
- Query OK, 0 rows affected (0.000 sec)
-
- MySQL [monitor]> save mysql query rules to disk;
- Query OK, 0 rows affected (0.006 sec)
读操作:
写操作:
简单的读写分离实验就结束了。