• MySQL 主从读写分离入门——基本原理以及ProxySQL的简单使用


    一、读写分离工作原理

    读写分离的工作原理:在大型网站业务中,当单台数据库无法满足并发需求时,通过主从同步方式同步数据。设置一台主服务器负责增、删、改,多台从服务器负责查询,从服务器主服务器同步数据以保持一致性,从而提高数据库的并发和负载能力。

    简单来说,读写分离就是将数据库操作分为“”和“”两部分,分别由不同的服务器处理。主服务器(通常是单台)主要负责处理写操作(如插入、更新、删除),而从服务器(通常是多台)则主要负责处理读操作(如查询)。主从服务器之间通过主从同步机制保持数据的一致性。通过这种方式,可以显著提高数据库的并发处理能力和负载能力,从而减轻单台服务器的压力。

    二、通过ProxySQL对读写分离进行浅层面的理解和运用

    1、实验环境:

    机器名称IP配置服务角色备注
    proxy192.168.20.149proxysql控制器用于监控管理
    master192.168.20.150数据库主服务器
    slave1192.168.20.146数据库从服务器
    slave2192.168.20.148数据库从服务器

     2、实现数据库主从复制

    基于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就代表成功

    3、安装ProxySQL

    本人博客另外一篇文章可以直接拿

    yum install -y proxysql

    启动 ProxySQL

    1. [root@proxy ~]# systemctl enable --now proxysql
    2. #先启服务,只需要mysql客户端,直接下mariadb就行了
    3. # 管理员登录
    4. [root@proxy ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032

     成功登录后可以查看一下库看看是否正常

    4、配置 ProxySQL 所需账户

    在 Master (192.168.20.150) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户

    1. create user 'monitor'@'192.168.%.%' identified with mysql_native_password by 'Monitor@123.com';
    2. grant all privileges on *.* to 'monitor'@'192.168.%.%' with grant option;
    3. #proxysql 的对外访问账户
    4. create user 'proxysql'@'192.168.%.%' identified with mysql_native_password by '123456';
    5. grant all privileges on *.* to 'proxysql'@'192.168.%.%' with grant option;

    5、配置proxySQL

    创建组:(定义写为1,读为0)

    1. MySQL [(none)]> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) values (1,0,'proxy');
    2. Query OK, 1 row affected (0.00 sec)
    3. MySQL [(none)]> load mysql servers to runtime;
    4. Query OK, 0 rows affected (0.01 sec)
    5. MySQL [(none)]> save mysql servers to disk;
    6. 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。

    1. MySQL [(none)]> select * from mysql_replication_hostgroups;
    2. +------------------+------------------+------------+---------+
    3. | writer_hostgroup | reader_hostgroup | check_type | comment |
    4. +------------------+------------------+------------+---------+
    5. | 1 | 0 | read_only | proxy |
    6. +------------------+------------------+------------+---------+
    7. 1 row in set (0.00 sec)

    通过查询我们可以清晰的看到我们所分的组

    添加主从服务器节点:

    在proxySQL端添加主从服务器的节点,并保存

    1. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (1,'192.168.20.150',3306);
    2. Query OK, 1 row affected (0.00 sec)
    3. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.146',3306);
    4. Query OK, 1 row affected (0.00 sec)
    5. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values (0,'192.168.20.148',3306);
    6. Query OK, 1 row affected (0.00 sec)
    7. MySQL [(none)]> load mysql servers to runtime;
    8. Query OK, 0 rows affected (0.01 sec)
    9. MySQL [(none)]> save mysql servers to disk;
    10. Query OK, 0 rows affected (0.00 sec)

     重要的信息是要保证主从服务器都是online状态

    为ProxySQL监控MySQL后端节点

    1. MySQL [(none)]> use monitor
    2. Reading table information for completion of table and column names
    3. You can turn off this feature to get a quicker startup with -A
    4. Database changed
    5. MySQL [monitor]> set mysql-monitor_username='monitor';
    6. Query OK, 1 row affected (0.00 sec)
    7. MySQL [monitor]> set mysql-monitor_password='Monitor@123.com';
    8. Query OK, 1 row affected (0.00 sec)
    9. 修改后,保存到runtime和disk
    10. MySQL [monitor]> load mysql variables to runtime;
    11. MySQL [monitor]> save mysql variables to disk;
    12. 查看监控账号【ProxySQL】
    13. SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';
    14. //也可以这样快速定位
    15. MySQL [(none)]> select @@mysql-monitor_username;
    16. +--------------------------+
    17. | @@mysql-monitor_username |
    18. +--------------------------+
    19. | monitor |
    20. +--------------------------+
    21. 1 row in set (0.00 sec)
    22. MySQL [(none)]> select @@mysql-monitor_password;
    23. +--------------------------+
    24. | @@mysql-monitor_password |
    25. +--------------------------+
    26. | Monitor@123.com |
    27. +--------------------------+
    28. 1 row in set (0.00 sec)

    验证监控信息

    ProxySQL 监控模块的指标都保存在monitor库的log表中 以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常

    心跳信息的监控

     

     查看read_only日志监控:

    MySQL [(none)]> select * from mysql_server_read_only_log;
    

    Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组 
    一些监控的状态斗志在log相关,都在monitor库下面的 global_variables 变量。 

     ProxySQL配置对外访问账号

    前面已经配置:配置ProxySQL 账户,我创建的对外访问账户是:用户:proxysql,密码:123456

    将对外访问账号添加到mysql_users表中:

    1. MySQL [monitor]> insert into mysql_users (username,password,default_hostgroup,transaction_persistent) values ('proxysql','123456',1,1);
    2. Query OK, 1 row affected (0.000 sec)
    3. MySQL [monitor]> load mysql users to runtime;
    4. Query OK, 0 rows affected (0.000 sec)
    5. MySQL [monitor]> save mysql users to disk;
    6. Query OK, 0 rows affected (0.007 sec)
    7. MySQL [monitor]> select * from mysql_users\G
    8. *************************** 1. row ***************************
    9. username: proxysql
    10. password: 123456
    11. active: 1
    12. use_ssl: 0
    13. default_hostgroup: 1
    14. default_schema: NULL
    15. schema_locked: 0
    16. transaction_persistent: 1
    17. fast_forward: 0
    18. backend: 1
    19. frontend: 1
    20. max_connections: 10000
    21. attributes:
    22. comment:
    23. 1 row in set (0.000 sec)

    注:transaction_persistent 如果为1,则一个完整的SQL只可能路由到一个节点;这点非常重要,主要解决这种情况:一个事务有混合的读操作和写操作组成,事务未提交前,如果事务中的读操作和写操作路由到不同节点,那么读取到的结果必然是脏数据。所以一般情况下,该值应该设置为1,尤其是业务中使用到事务机制的情况(默认为0)

    6、测试主从同步

    1. [root@slave1 ~]# mysql -h192.168.20.149 -uproxysql -p'123456' -P 6033
    2. mysql: [Warning] Using a password on the command line interface can be insecure.
    3. Welcome to the MySQL monitor. Commands end with ; or \g.
    4. Your MySQL connection id is 3
    5. Server version: 5.5.30 (ProxySQL)
    6. Copyright (c) 2000, 2022, Oracle and/or its affiliates.
    7. Oracle is a registered trademark of Oracle Corporation and/or its
    8. affiliates. Other names may be trademarks of their respective
    9. owners.
    10. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    11. mysql> show databases;
    12. +--------------------+
    13. | Database |
    14. +--------------------+
    15. | db1 |
    16. | information_schema |
    17. | mysql |
    18. | performance_schema |
    19. | sys |
    20. +--------------------+
    21. 5 rows in set (0.01 sec)
    22. mysql> select @@server_id;
    23. +-------------+
    24. | @@server_id |
    25. +-------------+
    26. | 21 |
    27. +-------------+
    28. 1 row in set (0.00 sec)
    29. #通过proxysql用户,创建一个keme库
    30. mysql> create database keme;
    31. Query OK, 1 row affected (0.00 sec)

    在slave2:192.168.20.148上去验证一下,是否同步过去keme这个库

    7、添加简单的读写分离规则

    1. MySQL [monitor]> insertintomysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values(1,1,'^select .* for update$',1,1);
    2. l query rules to runtime;
    3. save mysql query rulQuery OK, 1 row affected (0.000 sec)
    4. es to disk;MySQL
    5. [monitor]>
    6. MySQL [monitor]> insert into values(2,1,'^select',0,1);mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply)
    7. Query OK, 1 row affected (0.000 sec)
    8. MySQL [monitor]> load mysql query rules to runtime;
    9. Query OK, 0 rows affected (0.000 sec)
    10. MySQL [monitor]> save mysql query rules to disk;
    11. Query OK, 0 rows affected (0.006 sec)

    8.测试读写分离

    读操作:

     写操作:

    简单的读写分离实验就结束了。 

  • 相关阅读:
    suse10sp1编译glib-2
    【从0入门JVM】-01Java代码怎么运行的
    小程序开发音视频问题汇总及解决方案
    C/C++航空客运订票系统
    朋友圈大佬都去读研了,这份备考书单我码住了(文末赠书)
    MySQL常用函数
    ClickHouse技术研究及语法简介
    面试宝典-【redis】
    【服务器数据恢复】IBM服务器RAID控制器出错的数据恢复案例
    Canal
  • 原文地址:https://blog.csdn.net/2201_75878485/article/details/136400327