数据库如何实现读写分离实战

    作者:民工哥2020-03-28 15:51:53

    ProxySQL+MySQL实现数据库读写分离实战。今天给大家带来的是关于数据库读写分离相关的实战操作。一起来卡一下吧。

    前面也写过几篇关于Mysql数据的文章:

    •  MySQL集群高可用架构之MHA
    •  MySQL 同步复制及高可用方案总结
    •  官方工具|MySQL Router 高可用原理与实战

    今天给大家带来的是关于数据库读写分离相关的实战操作。

    ProxySQL介绍

    ProxySQL是一个高性能的MySQL中间件,拥有强大的规则引擎。具有以下特性:http://www.proxysql.com/

    1、连接池,而且是multiplexing

    2、主机和用户的最大连接数限制

    3、自动下线后端DB

    • 延迟超过阀值
    • ping 延迟超过阀值
    • 网络不通或宕机

    4、强大的规则路由引擎

    • 实现读写分离
    • 查询重写
    • sql流量镜像

    5、支持prepared statement

    6、支持Query Cache

    7、支持负载均衡,与gelera结合自动failover

    整体环境介绍

    数据库如何实现读写分离实战_数据库_MySQL_Oracle_课课家

    1、系统环境

    三台服务器系统环境一致如下

    1. [root@db1 ~]# cat /etc/redhat-release   
    2. CentOS Linux release 7.4.1708 (Core)   
    3. [root@db1 ~]# uname -r  
    4. 3.10.0-693.el7.x86_64 

    2、IP地址与软件版本

    • proxy  192.168.22.171
    • db1     192.168.22.173
    • db2    192.168.22.174
    • mysql  5.7.17
    • proxy  sql 1.4.8

    3、关闭防火墙、selinux

    1. systemctl stop firewalld  #停止防火墙服务  
    2. systemctl disable firewalld  #禁止开机自启动  
    3. sed -i 's#SELINUX=enforcing#SELINUX=disabled#g'  /etc/selinux/conf  && reboot  
    4. #用sed命令替换的试修改selinux的配置文件 

    4、mysql安装与主从同步

    安装请参考以下文章

    LAMP架构应用实战——MySQL服务

    主从同步请参以下文章

    Linux系统MySQL数据库主从同步实战过程

    安装布署过程

    1、数据库主从同步

    • 查看主从同步状态 
    1. mysql> show slave status\\G  
    2. *************************** 1. row ***************************           
    3.  Slave_IO_State: Waiting for master to send event  
    4. Master_Host: 192.168.22.173                
    5. Master_User: rep              
    6. Master_Port: 3306                 
    7. Connect_Retry: 60               
    8. Master_Log_File: master-log.000001 
    9. Read_Master_Log_Pos: 154                
    10. Relay_Log_File: db2-relay-bin.000002  
    11. Relay_Log_Pos: 321          
    12. Relay_Master_Log_File: master-log.000001   
    13. Slave_IO_Running: Yes        
    14. Slave_SQL_Running: Yes             
    15. Replicate_Do_DB:            
    16. Replicate_Ignore_DB:             
    17. Replicate_Do_Table:         
    18. Replicate_Ignore_Table:        
    19. Replicate_Wild_Do_Table:    
    20. Replicate_Wild_Ignore_Table:                     
    21. Last_Errno: 0  
    22. Last_Error:  
    23. Skip_Counter: 0  
    24. Exec_Master_Log_Pos: 154  
    25. Relay_Log_Space: 526  
    26. Until_Condition: None  
    27. Until_Log_File:  
    28. Until_Log_Pos: 0  
    29. Master_SSL_Allowed: No   
    30. Master_SSL_CA_File:   
    31. Master_SSL_CA_Path:   
    32. Master_SSL_Cert:  
    33. Master_SSL_Cipher:  
    34. Master_SSL_Key:  
    35. Seconds_Behind_Master: 0  
    36. Master_SSL_Verify_Server_Cert: No   
    37. Last_IO_Errno: 0   
    38. Last_IO_Error:   
    39. Last_SQL_Errno: 0   
    40. Last_SQL_Error:  
    41. Replicate_Ignore_Server_Ids:   
    42. Master_Server_Id: 1   
    43. Master_UUID: 70a61633-63ae-11e8-ab86-000c29fe99ea   
    44. Master_Info_File: /mysqldata/master.info  
    45. SQL_Delay: 0   
    46. SQL_Remaining_Delay: NULL  
    47. Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates   
    48. Master_Retry_Count: 86400   
    49. Master_Bind:  
    50. Last_IO_Error_Timestamp:  
    51. Last_SQL_Error_Timestamp:  
    52. Master_SSL_Crl:   
    53. Master_SSL_Crlpath:   
    54. Retrieved_Gtid_Set:   
    55. Executed_Gtid_Set:   
    56. Auto_Position: 0   
    57. Replicate_Rewrite_DB:  
    58. Channel_Name:   
    59. Master_TLS_Version:  
    60. 1 row in set (0.00 sec) 
    • 检测主从同步 
    1. [root@db1 ~]# mysql -uroot -p -e "create database testdb;  
    2. "Enter password:   
    3. [root@db1 ~]# mysql -uroot -p -e "show  databases;" |grep testdb  
    4. Enter password:   
    5. testdb  
    6. #db2上查看是否同步  
    7. mysql> show databases;  
    8. +--------------------+  
    9. | Database           |  
    10. +--------------------+  
    11. | information_schema |  
    12. | mysql              |  
    13. | performance_schema |  
    14. | sys                |  
    15. | testdb             |  
    16. +--------------------+  
    17. 5 rows in set (0.01 sec) 

    2、准备proxySQL软件

    1. [root@proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm  
    2. [root@proxy ~]# ll proxysql-1.4.8-1-centos7.x86_64.rpm   
    3. -rw-r--r-- 1 root root 5977168 Apr 10 11:38 proxysql-1.4.8-1-centos7.x86_64.rpm 

    3、安装配置

    1. [root@proxy ~]# yum install -y proxysql-1.4.8-1-centos7.x86_64.rpm  
    2. [root@proxy ~]# rpm -ql  proxysql  
    3. /etc/init.d/proxysql    #启动脚本  
    4. /etc/proxysql.cnf       #配置文件,仅在第一次(/var/lib/proxysql/proxysql.db文件不存在)启动时有效。启#动后可以在proxysql管理端中通过修改数据库的方式修改配置并生效(官方推荐方式。)  
    5. /usr/bin/proxysql       #主程序文件  
    6. /usr/share/proxysql/tools/proxysql_galera_checker.sh  
    7. /usr/share/proxysql/tools/proxysql_galera_writer.pl 

    4、配置文件详解

    1. [root@proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf  
    2. datadir="/var/lib/proxysql"     #数据目录  
    3. admin_variables=  
    4. {      
    5. admin_credentials="admin:admin"   #连接管理端的用户名与密码   
    6. mysql_ifaces="0.0.0.0:6032"       #管理端口,用来连接proxysql的管理数据库  
    7. }  
    8. mysql_variables=  
    9. {      
    10. threads=4      #指定转发端口开启的线程数量      
    11. max_connections=2048      
    12. default_query_delay=0  
    13. default_query_timeout=36000000  
    14. have_compress=true  
    15. poll_timeout=2000  
    16. interfaces="0.0.0.0:6033"        #指定转发端口,用于连接后端mysql数据库的,相当于代理作用  
    17. default_schema="information_schema"  
    18. stacksize=1048576  
    19. server_version="5.5.30"          #指定后端mysql的版本  
    20. connect_timeout_server=3000   
    21. monitor_username="monitor"  
    22. monitor_password="monitor"  
    23. monitor_history=600000  
    24. monitor_connect_interval=60000  
    25. monitor_ping_interval=10000  
    26. monitor_read_only_interval=1500   
    27. monitor_read_only_timeout=500    
    28. ping_interval_server_msec=120000   
    29. ping_timeout_server=500  
    30. commands_stats=true   
    31. sessions_sort=true   
    32. connect_retries_on_failure=10  
    33. }  
    34. mysql_servers =  
    35. (  
    36. )  
    37. mysql_users:  
    38. (  
    39. )  
    40. mysql_query_rules:  
    41. (  
    42. )  
    43. scheduler=  
    44. (  
    45. )  
    46. mysql_replication_hostgroups=  
    47. (  
    48. )  
    49. #因此我们使用官方推荐的方式来配置proxy sql 

    5、启动服务并查看

    1. [root@proxy ~]# /etc/init.d/proxysql  
    2. startStarting ProxySQL: DONE!  
    3. [root@proxy ~]# ss -lntup|grep proxy  
    4. tcp    LISTEN     0   128   *:6032      *:*    users:(("proxysql",pid=1199,fd=23))  
    5. tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=22))  
    6. tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=21))  
    7. tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=20))  
    8. tcp    LISTEN     0   128   *:6033      *:*    users:(("proxysql",pid=1199,fd=19))   
    9. #可以看出转发端口6033是启动了四个线程 

    6、在mysql上配置账号并授权

    1. mysql> GRANT ALL ON *.* TO 'proxysql'@'192.168.22.%' IDENTIFIED BY '123456';  
    2. Query OK, 0 rows affected, 1 warning (0.03 sec)  
    3. mysql> flush privileges;  
    4. Query OK, 0 rows affected (0.02 sec) 

    7、proxysql默认数据库说明

    1. [root@proxy ~]# yum install mysql -y  
    2. [root@proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032  
    3. Welcome to the MariaDB monitor.  Commands end with ; or \\g.  
    4. Your MySQL connection id is 1  
    5. Server version: 5.5.30 (ProxySQL Admin Module) 
    6. Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.  
    7. Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
    8. MySQL [(none)]> show databases;  
    9. +-----+---------------+-------------------------------------+  
    10. | seq | name          | file                                |  
    11. +-----+---------------+-------------------------------------+  
    12. | 0   | main          |                                     |  
    13. | 2   | disk          | /var/lib/proxysql/proxysql.db       |  
    14. | 3   | stats         |                                     |  
    15. | 4   | monitor       |                                     |  
    16. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |  
    17. +-----+---------------+-------------------------------------+  
    18. 5 rows in set (0.00 sec) 

    main:内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。

    disk:是持久化到硬盘的配置,sqlite数据文件。

    stats:是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。

    monitor:库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。

    8、proxysql的配置系统

    ProxySQL具有一个复杂但易于使用的配置系统,可以满足以下需求:

    1、允许轻松动态更新配置(这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它)。与MySQL兼容的管理界面可用于此目的。

    2、允许尽可能多的配置项目动态修改,而不需要重新启动ProxySQL进程

    3、可以毫不费力地回滚无效配置

    4、这是通过多级配置系统实现的,其中设置从运行时移到内存,并根据需要持久保存到磁盘。

    • 3级配置由以下几层组成:

    参考文章:https://github.com/sysown/pro...

    9、配置proxysql管理用户

    proxysql默认的表信息如下

    1. MySQL [main]> show tables;  
    2. +--------------------------------------------+  
    3. | tables                                     |  
    4. +--------------------------------------------+  
    5. | global_variables                           |  
    6. | mysql_collations                           |  
    7. | mysql_group_replication_hostgroups         |  
    8. | mysql_query_rules                          |  
    9. | mysql_query_rules_fast_routing             |  
    10. | mysql_replication_hostgroups               |  
    11. | mysql_servers                              |  
    12. | mysql_users                                |  
    13. | proxysql_servers                           |  
    14. | runtime_checksums_values                   |  
    15. | runtime_global_variables                   |  
    16. | runtime_mysql_group_replication_hostgroups |  
    17. | runtime_mysql_query_rules                  |  
    18. | runtime_mysql_query_rules_fast_routing     |  
    19. | runtime_mysql_replication_hostgroups       |  
    20. | runtime_mysql_servers                      |  
    21. | runtime_mysql_users                        |  
    22. | runtime_proxysql_servers                   |  
    23. | runtime_scheduler                          |  
    24. | scheduler                                  |  
    25. +--------------------------------------------+  
    26. 20 rows in set (0.00 sec)  
    27. #这里是使用insert into语句来动态配置,而可以不需要重启  
    28. MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'db1','3306',1,'Write Group');  
    29. Query OK, 1 row affected (0.01 sec)  
    30. MySQL [(none)]> insert intomysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'db2','3307',1,'Read Group');  
    31. Query OK, 1 row affected (0.00 sec)  
    32. MySQL [(none)]> select * from mysql_servers;  
    33. +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
    34. | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment     |  
    35. +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
    36. | 1            | db1      | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Write Group |  
    37. | 2            | db2      | 3307 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | Read Group    
    38. |+--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------------+  
    39. 2 rows in set (0.00 sec)  
    40. #接下来将刚刚在mysql客户端创建的用户写入到proxy sql主机的mysql_users表中,它也是用于proxysql客户端访问数据库,默认组是写组,当读写分离规则出现问题时,它会直接访问默认组的数据库。  
    41. MySQL [main]> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('proxysql','123456',1);  
    42. Query OK, 1 row affected (0.00 sec)  
    43. MySQL [main]> select * from mysql_users;  
    44. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 
    45. | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | 
    46. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+ 
    47. | proxysql | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           | 
    48. +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+1  
    49. row in set (0.00 sec) 
    • 在mysql上添加监控的用户 
    1. mysql> GRANT SELECT ON *.* TO 'monitor'@'192.168.22.%' IDENTIFIED BY 'monitor';  
    2. Query OK, 0 rows affected, 1 warning (0.00 sec)  
    3. mysql> flush privileges;  
    4. Query OK, 0 rows affected (0.00 sec)  
    5. #在proxysql主机端配置监控用户  
    6. MySQL [main]> set mysql-monitor_username='monitor';  
    7. Query OK, 1 row affected (0.00 sec)  
    8. MySQL [main]> set mysql-monitor_password='monitor';  
    9. Query OK, 1 row affected (0.00 sec)  
    10. #参考文章:https://github.com/sysown/proxysql/wiki/ProxySQL-Configuration 

    10、配置proxysql的转发规则

    1. MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(1,1,'^SELECT.*FOR UPDATE$',1,1);  
    2. Query OK, 1 row affected (0.01 sec)  
    3. MySQL [main]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(2,1,'^SELECT',2,1); 
    4. Query OK, 1 row affected (0.00 sec)  
    5. MySQL [main]> select rule_id,active,match_digest,destination_hostgroup,apply from mysql_query_rules;  
    6. +---------+--------+----------------------+-----------------------+-------+  
    7. | rule_id | active | match_digest         | destination_hostgroup | apply |  
    8. +---------+--------+----------------------+-----------------------+-------+  
    9. | 1       | 1      | ^SELECT.*FOR UPDATE$ | 1                     | 1     |  
    10. | 2       | 1      | ^SELECT              | 2                     | 1     |  
    11. +---------+--------+----------------------+-----------------------+-------+  
    12. 2 rows in set (0.00 sec)  
    13. #配置查询select的请求转发到hostgroup_id=2组上(读组)#征对select * from table_name  for update这样的修改语句,我们是需要将请求转到写组,也就是hostgroup_id=1#对于其它没有被规则匹配的请求全部转发到默认的组(mysql_users表中default_hostgroup) 

    11、更新配置到RUNTIME中

    由上面的配置系统层级关系可以得知所有进来的请求首先是经过RUNTIME层

    1. MySQL [main]> load mysql users to runtime;  
    2. Query OK, 0 rows affected (0.00 sec)  
    3. MySQL [main]> load mysql servers to runtime;  
    4. Query OK, 0 rows affected (0.02 sec)  
    5. MySQL [main]> load mysql query rules to runtime;  
    6. Query OK, 0 rows affected (0.00 sec)  
    7. MySQL [main]> load mysql variables to runtime; 
    8. Query OK, 0 rows affected (0.00 sec)  
    9. MySQL [main]> load admin variables to runtime;  
    10. Query OK, 0 rows affected (0.00 sec) 

    12、将所有配置保存至磁盘上

    所有配置数据保存到磁盘上,也就是永久写入/var/lib/proxysql/proxysql.db这个文件中

    1. MySQL [main]> save mysql users to disk;  
    2. Query OK, 0 rows affected (0.03 sec)  
    3. MySQL [main]> save mysql servers to disk;  
    4. Query OK, 0 rows affected (0.04 sec)  
    5. ySQL [main]> save mysql query rules to disk;  
    6. Query OK, 0 rows affected (0.03 sec)  
    7. MySQL [main]> save mysql variables to disk;  
    8. Query OK, 94 rows affected (0.02 sec) 
    9. MySQL [main]> save admin variables to disk;  
    10. Query OK, 31 rows affected (0.02 sec)  
    11. MySQL [main]> load mysql users to runtime;  
    12. Query OK, 0 rows affected (0.00 sec) 

    13、测试读写分离

    1. [root@proxy ~]# mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033  
    2. Welcome to the MariaDB monitor.Commands end with ; or \\g.  
    3. Your MySQL connection id is 2Server version: 5.5.30 (ProxySQL) 
    4. Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.  
    5. Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.  
    6. MySQL [(none)]> show databases;  
    7. +--------------------+  
    8. | Database           |  
    9. +--------------------+  
    10. | information_schema |  
    11. | mysql              |  
    12. | performance_schema |  
    13. | sys                |  
    14. | testdb             |  
    15. +--------------------+  
    16. 5 rows in set (0.02 sec)#这才是我们真正的数据库啊 
    • 创建数据与表,测试读写分离情况 
    1. MySQL [(none)]> create database test_proxysql;  
    2. Query OK, 1 row affected (0.02 sec)  
    3. MySQL [(none)]> use test_proxysql;  
    4. Database changed  
    5. MySQL [test_proxysql]> create table test_tables(name varchar(20),age int(4));  
    6. Query OK, 0 rows affected (0.07 sec)  
    7. MySQL [test_proxysql]> insert into test_tables values('zhao','30');  
    8. Query OK, 1 row affected (0.09 sec)  
    9. MySQL [test_proxysql]> select * from test_tables;  
    10. +------+------+  
    11. | name | age  |  
    12. +------+------+  
    13. | zhao |   30 |  
    14. +------+------+  
    15. 1 row in set (0.02 sec) 
    • 在proxysql管理端查看读写分离 
    1. MySQL [main]> select * from stats_mysql_query_digest;  
    2. +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 
    3. | hostgroup | schemaname         | username | digest             | digest_text                                          | count_star | first_seen | last_seen  | sum_time | min_time | max_time | 
    4. +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 
    5. | 2         | test_proxysql      | proxysql | 0x57CF7EC26C91DF9A | select * from test_tables                            |1          | 1527667635 | 1527667635 | 14253    | 14253    | 14253    | 
    6. | 1         | information_schema | proxysql | 0x226CD90D52A2BA0B | select @@version_comment limit ?                     | 1          | 1527667214 | 1527667214 | 0        | 0        | 0        | 
    7. | 1         | test_proxysql      | proxysql | 0xFF9877421CFBDA6F | insert into test_tables values(?,?)                  | 1          | 1527667623 | 1527667623 | 89033    | 89033    | 89033    | 
    8. | 1         | information_schema | proxysql | 0xE662AE2DEE853B44 | create database test-proxysql                        | 1          | 1527667316 | 1527667316 | 8470     | 8470     | 8470     | 
    9. | 1         | information_schema | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667222 | 1527667222 | 19414    | 19414    | 19414    | 
    10. | 1         | information_schema | proxysql | 0xB9EF28C84E4207EC | create database test_proxysql                        | 1          | 1527667332 | 1527667332 | 15814    | 15814    | 15814    | 
    11. | 2         | information_schema | proxysql | 0x620B328FE9D6D71A | SELECT DATABASE()                                    | 1          | 1527667342 | 1527667342 | 23386    | 23386    | 23386    | 
    12. | 1         | test_proxysql      | proxysql | 0x02033E45904D3DF0 | show databases                                       | 1          | 1527667342 | 1527667342 | 2451     | 2451     | 2451     | 
    13. | 1         | test_proxysql      | proxysql | 0x59F02DA280268525 | create table test_tables                             | 1          | 1527667360 | 1527667360 | 9187     | 9187     | 9187     | 
    14. | 1         | test_proxysql      | proxysql | 0x99531AEFF718C501 | show tables                                          | 1          | 1527667342 | 1527667342 | 1001     | 1001     | 1001     | 
    15. | 1         | test_proxysql      | proxysql | 0xC745E37AAF6095AF | create table test_tables(name varchar(?),age int(?)) | 1          | 1527667558 | 1527667558 | 68935    | 68935    | 68935    | 
    16. +-----------+--------------------+----------+--------------------+------------------------------------------------------+------------+------------+------------+----------+----------+----------+ 
    17. 11 rows in set (0.01 sec)#从上述结果就可以看出读写分离配置是成功的,读请求是转发到2组,写请求转发到1组 

    • 整个读写分离的架构配置到此就完成了,但是此架构存在需要优化的地方,那就是此架构存在单点问题。实际生产环境中可采用MHA+ProxySQL+Mysql这类架构解决此问题,请持续关注! 
      这种数据集合具有如下特点:尽可能不重复,以最优方式为某个特定组织的多种应用服务,其数据结构独立于使用它的应用程序,对数据的增、删、改、查由统一软件进行管理和控制。从发展的历史看,数据库是数据管理的高级阶段,它是由文件管理系统发展起来的。

课课家教育

未登录