数据库服务读写分离架构

数据库服务读写分离架构

准备工作

1. 读写分离架构作用：主要可以实现将大量读请求，实现负载均衡，从而减少单点数据库并发访问压力
2. 读写分离架构软件：ProxySQL 数据中间件（客户端 -- 中间件软件 -- 数据库服务端）
3. 读写分离架构搭建：
4. 架构环境规划
5. 主库     10.0.0.51
6. 从库     10.0.0.52
7. 从库     10.0.0.53
8. MHA      10.0.0.54  ProxySQL
9. ProxySQL 10.0.0.55
10. ProxySQL 10.0.0.56

复制代码

开始搭建

1. 步骤一：下载中间件软件并进行安装
2. proxySQL软件下载链接：https://www.proxysql.com/documentation/installing-proxysql
3. rpm -ivh proxysql-2.4.6-1-centos7.x86_64.rpm
5. systemctl start proxysql
6. ss -lntup|grep proxysql
7. tcp    LISTEN     0      128       *:6032                  *:*                   users:(("proxysql",pid=23714,fd=46))
8. tcp    LISTEN     0      128       *:6033                  *:*                   users:(("proxysql",pid=23714,fd=34))
10. 6032：此端口表示连接访问后，可以对proxysql程序进行管理（管理员/运维人员）
11. 6033：此端口表示连接访问后，可以对proxysql程序进行应用（前端程序访问）
14. 步骤二：读写分离中间件软件功能配置
15. mysql -uadmin -padmin -h127.0.0.1 -P6032
17. 主要是对特定表做-增删改查看操作
19. mysql_replication_hostgroups   -- 可以存储读写分离分组信息
20. 作用：可以定义读写分离分组信息   www bbs game
21. -- 读组：后面会将可以进行读取操作的数据库节点划分到此分组中
22. -- 写组：后面会将可以进行存储操作的数据库节点划分到此分组中
23. insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
24. mysql> select * from mysql_replication_hostgroups;
25. +------------------+------------------+------------+---------+
26. | writer_hostgroup | reader_hostgroup | check_type | comment |
27. +------------------+------------------+------------+---------+
28. | 10               | 20               | read_only  | proxy   |
29. +------------------+------------------+------------+---------+
31. -- 在分组表中进行分组设置
32.    10 写组  10.0.0.51 主库--写 10.0.0.51      
33.    20 读组  10.0.0.51 主库--写 10.0.0.52 10.0.0.52  10.0.0.53   51 52  53     61 62 63   71....
34.    30 写组  10.0.0.61
35.    40 读组  10.0.0.62  10.0.0.63
37.    save mysql servers to disk;
38.    load mysql servers to runtime;
39. PS：以上表信息添加设置后，需要调整从库只读变量信息（set global read_only=1）
42. mysql_servers                  -- 可以定义节点信息属于哪个分组   
43. 10.0.0.51/10.0.0.50    写组-10号组
44. 10.0.0.52/10.0.0.53    读组-20号组
45. insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.51',3306);
46. insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.52',3306);
47. insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.53',3306);
49. save mysql servers to disk;
50. load mysql servers to runtime;
53. global_variables               -- 可以实现全局功能设置
55. create user monitor@'%' identified with mysql_native_password by '123';
56. grant replication client on *.* to monitor@'%';
57. -- 在主节点上创建proxysql监控用户
59. mysql> select * from global_variables where variable_name='mysql-monitor_username' or variable_name='mysql-monitor_password';
60. +------------------------+----------------+
61. | variable_name          | variable_value |
62. +------------------------+----------------+
63. | mysql-monitor_password | 123            |
64. | mysql-monitor_username | monitor        |
65. +------------------------+----------------+
67. mysql-monitor_usernam   -- proxysql监控用户信息   
68. mysql-monitor_password  -- proxysql监控用户密码   
70. update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';
71. -- 修改监控用户名称
72. update global_variables set variable_value='123' where variable_name='mysql-monitor_password';
73. -- 修改监控用户密码
75. load mysql variables to runtime;
76. save mysql variables to disk;
78. proxysql中配置监控用户：
79. 1）对后端节点做健康检测；
80. 2）对后端数据库节点会检测主从身份
81.    不会根据配置信息取识别主从节点信息 读节点或写节点 -- select @@read_only == 0 写节点  1 读节点）
84. mysql_users           -- 可以设置定义连接中间的用户信息，并且可以利用用户表中用户信息连接后端数据库
86. create user root@'%' identified with mysql_native_password by '123456';
87. grant all on *.* to root@'%';
89. insert into mysql_users(username,password,default_hostgroup) values('root','123456',10);
91. load mysql users to runtime;
92. save mysql users to disk;
95. mysql_query_rules     -- 可以进行读写分离策略配置   
96. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
97. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);
98. insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (结尾,1,'.*',10,1);
99. -- 在以上表中最后一行会有隐藏策略，将没有匹配的所有语句都会发送给写组处理
101. load mysql query rules to runtime;
102. save mysql query rules to disk;
105. 步骤三：读写分离访问测试
106. 写测试
107. mysql -uroot -p123456 -h10.0.0.54 -P6033 -e "begin;select @@server_id;commit;"
108. -- 以上语句执行完，会输出server_id=1信息
109. 读测试
110. mysql -uroot -p123456 -h10.0.0.54 -P6033 -e "select @@server_id;"
111. -- 以上语句多次执行，会输出server_id=1/2/3信息
113. proxysql读写分离架构搭建设置完毕，需要进行应用交付： proxysql用户信息  proxysql网络信息

复制代码

扩展设置
1）基于用户实现负载均衡

1. read  用户  可以读取数据 将此用户访问请求转发给 所有节点   代码中（连接数据库信息-wp_config.php-db_user-read）
2. write 用户  可以存储数据 将此用户访问请求转发给 主库节点   代码中（连接数据库信息-wp_config.php-db_user-write）
4. insert into mysql_users(username,password,default_hostgroup) values ('write','123',10),('reader','123',20);
5. load mysql users to runtime;
6. save mysql users to disk;
8. insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply)
9. values (1,1,'write',10,1),(2,1,'reader',20,1);
10. load mysql users to runtime;
11. save mysql users to disk;
13. 写测试
14. mysql -uwrite -p123 -h10.0.0.54 -P6033 -e "select @@server_id;"
15. -- 以上语句多次执行，会输出server_id=1信息
16. 读测试
17. mysql -uread -p1234 -h10.0.0.54 -P6033 -e  "select @@server_id;"
18. -- 以上语句多次执行，会输出server_id=1/2/3信息
20. proxysql读写分离架构搭建设置完毕，需要进行应用交付： proxysql用户信息（读操作-read 写操作-write）  proxysql网络信息

复制代码

2）基于端口实现负载均衡

1. 6033 -- 读数据端口  前端访问此端口，中间件服务会将访问请求转发给所有节点
2. 6034 -- 写数据端口  前端访问此端口，中间件服务会将访问请求转发给主库节点
4. # 修改proxySQL监听SQL流量的端口号，监听多端口信息
5. set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034'
7. # 使监听端口配置信息生效
8. save mysql variables to disk;
9. systemctl restart proxysql
11. insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply)
12. values(1,1,6034,10,1),(2,1,6033,20,1);
13. load mysql query rules to runtime;
14. save mysql query rules to disk;
15. -- 除了基于端口进行分离，还可以基于监听地址（修改字段proxy_addr即可），也可以基于客户端地址（修改字段client_addr字段即可）；
17. 写测试
18. mysql -uroot -p123 -h10.0.0.54 -P6033 -e "select @@server_id;"
19. -- 以上语句多次执行，会输出server_id=1/2/3信息
20. 读测试
21. mysql -uroot -p123 -h10.0.0.54 -P6034 -e "select @@server_id;"
22. -- 以上语句多次执行，会输出server_id=1信息

复制代码

redis数据库服务管理应用

1）redis数据库服务功能作用（缓存型数据库服务 非关系型数据库服务）

1. -- 可以加快热点数据存储和调取  
2. -- 可以实现数据库页缓存/缓冲功能      高并发访问  大量写操作 磁盘IO消耗激增
3. -- 可以实现业务访问消息队列功能  

复制代码

2）redis数据库服务安装部署

1. 步骤一：下载安装软件程序
2. 下载软件链接地址
3. https://redis.io/download/#redis-downloads
4. https://download.redis.io/releases
6. tar xf redis-6.2.10.tar.gz
7. ln -s redis-6.2.10 redis
8. yum install -y gcc automake autoconf libtool make
9. cd /usr/local/redis
10. make
12. vim /etc/profile
13. export PATH="$PATH:/usr/local/mysql/bin:/usr/local/mongodb/bin:/usr/local/redis/src/"
14. source /etc/profile
16. 步骤二：编写配置信息启动redis服务
17. mkdir -p /data/6379
18. vim /data/6379/redis.conf
19. daemonize yes
20. -- 表示定义服务程序在后台以守护进程方式运行
21. port 6379
22. -- 表示定义服务的默认端口
23. logfile /data/6379/redis.log
24. -- 表示服务运行日志存放路径
25. dir /data/6379  
26. -- 表示定义数据目录路径，用于持久化应用时存储数据
27. dbfilename dump.rdb
28. -- 表示持久化存储的数据文件名称
30. redis-server /data/6379/redis.conf
32. redis-cli
33. 127.0.0.1:6379> set stu_name 'xiaoA'
34. OK
35. 127.0.0.1:6379> get stu_name
36. "xiaoA"

复制代码

3）redis数据库基础管理

1. -- 远程连接管理（安全配置管理）
2. redis-cli -h 10.0.0.51 -p 6379
4. DENIED Redis is running in protected mode because protected mode is enabled, no bind address was specified, no authentication password is requested to clients.
5. redis启动运行后，redis服务会默认开启安全保护模式（远程连接redis不能进行任何管理操作），也没有做地址绑定和密码认证
7. In this mode connections are only accepted from the loopback interface.
8. 在这个安全模式下，可以利用loopback接口连接数据库（本地登录），进行管理操作
10. If you want to connect from external computers to Redis you may adopt one of the following solutions:
11. 如果想实现外部设备远程连接管理redis数据库服务，可以按照以下建议做设置调整
13. 1) Just disable protected mode sending the command 'CONFIG SET protected-mode no' from the loopback interface by connecting to Redis from the same host the server is running, however MAKE SURE Redis is not publicly accessible from internet if you do so. Use CONFIG REWRITE to make this change permanent.
14. 2) Alternatively you can just disable the protected mode by editing the Redis configuration file, and setting the protected mode option to 'no', and then restarting the server.
15. 3) If you started the server manually just for testing, restart it with the '--protected-mode no' option.
16. 以上三个建议都表示可以禁用安全保护功能
18. 4) Setup a bind address or an authentication password.
19. 进行地址绑定设置或进行密码认证设置
20. NOTE: You only need to do one of the above things in order for the server to start accepting connections from the outside.
22. vim /data/6379/redis.conf
23. bind 10.0.0.51 127.0.0.1
24. requirepass 123456
26. redis-cli -h 10.0.0.51 -p 6379
27. 10.0.0.51:6379> set stu_name02 xiaoB
28. (error) NOAUTH Authentication required.
29. 10.0.0.51:6379> auth 123456
30. OK
31. -- 需要完成密码认证
32. redis-cli -h 10.0.0.51 -p 6379 -a 123456
33. -- 利用-a选项参数进行远程登录认证
34. Warning: Using a password with '-a' or '-u' option on the command line interface may not be safe.
35. 10.0.0.51:6379> set stu_name03 xiaoC
36. OK
38. -- 数据库持久化存储设置  
39. RDB方式实现持久化存储（默认选择）
40. 存储特点：类似于将内存中数据信息做了快照处理，将生成快照信息保存到文件中（rdb）
41. 应用特点：数据存储恢复效率快/可以用于主从同步/存储数据安全性较低
43. dir /data/6379
44. dbfilename dump.rdb
46. 手工保存数据：
47. save/bgsave
49. 自动保存数据（触发性）
50. save 900 1   
51. -- 表示900秒（15分钟）内有1个更改
52. save 300 10
53. -- 表示300秒（5分钟）内有10个更改
54. save 60 10000  
55. -- 表示60s内有10000个更改
57. AOF方式实现持久化存储
58. 存储特点：类似于mysql中的binlog日志，会将数据库中所有操作信息记录到文件中（aof）
59. 应用特点：可以灵活进行数据读取或恢复/存储数据安全性更高
61. dir /data/6379
62. appendonly yes
63. -- 是否打开aof日志功能
65. appendfsync always  （安全选择）              
66. -- 表示每1个命令，都立即同步到AOF         
67. appendfsync everysec  
68. -- 表示每秒写1次
69. appendfsync no      （性能选择）
70. -- 由操作系统来决定是否将操作语句信息写入文件
73. -- 数据库服务配置管理  
74. 方式一：永久配置
75. vim redis.conf
76. 配置项 配置内容
78. 方式二：临时配置/动态配置
79. CONFIG SET 配置项 配置内容  
80. config get *
81. -- 查看redis数据库中所有配置项信息

复制代码

4）redis数据库架构搭建

[code]-- 主从同步架构环境规划主库  10.0.0.51  6380  从库  10.0.0.51  6381从库  10.0.0.51  6382步骤一：主从数据库实例环境准备mkdir -p /data/{6380..6382}主库配置cat > /data/6380/redis.conf