技术文章

当前位置：首页>帮助手册>技术文章

mysql实现数据库读写分离搭建高负载o2server集群

时间：2022-04-28   
内容导读

本篇梳理 O2OA 平台在大并发场景下的数据库架构改造思路：先按应用节点、主库与从库划分角色，再打通主从复制，最后用中间件承接读写分流。搭建时要在主库开启日志、建立同步账户并记录同步起点，从库导入数据后按起点接续复制；随后配置分流服务与监听端口，把平台的连接配置指向中间件。注意主从标识不可重复，从库应设为只读，分流是否生效可通过管理命令核对。

本文主要描述：通过实现数据库读写分离，搭建高负载 o2server 集群

主机介绍

主机

地址

用途

application

172.16.91.39

o2server 应用服务器

mysqlmaster

172.16.91.40

mysql 主服务器(写)

mysqlslave01

172.16.91.41

mysql 从服务器01(读)

mysqlslave01

172.16.91.42

mysql 从服务器02(读)


安装 mysql8


本次使用 ubuntu server 20.04 运行 apt 命令查看 ppa mysql 数据库版本


sudo apt search mysql-server



已经是最新版本的 mysql 了,后面直接安装即可,不再安装 mysql ppa 仓库.


  • mysqlmaster:


sudo apt install mysql-server


  • mysqlslave01:


sudo apt install mysql-server



sudo apt install mysql-server


数据库基础配置


现在对 mysql 数据库进行基础配置,不涉及主从复制,分别对 mysqlmaster,mysqlslave01,mysqlslave02 进行相同的设置,设置完成后允许远程登录.


sudo mysql_secure_installation


按提示完成设置后,进入到 myql 客户端


sudo mysql -u root -p


执行 mysql 命令,允许 root 远程登录


use mysql;
update mysql.user set host='%', plugin='mysql_native_password' where user='root';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* to root@'%';
ALTER USER 'root'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1234abcd';
FLUSH PRIVILEGES;


修改 /etc/mysql/mysql.conf.d/mysqld.cnf 中的地址绑定为 0.0.0.0

change bind address.png



重启 mysql 生效


sudo systemctl restart mysql


完成后,我们在 mysqlmaster,mysqlslave01,mysqlslave02 三台服务器上都安装了 mysql,并完成了简单的设置,允许通过 jdbc 远程链接到这三台 mysql 服务器.


本次主要完成主从环境的搭建,不考虑 mysql 的基本调优和配置.至此我们有了三台简单的 mysql 服务器,并可以用客户端链接.

connect to mysql.png


mysql 主从设置:下面进行主从配置,先配置主库(master),再配置从库 slave01 和 slave02

master 配置：在 master 服务器上创建数据库 X，作为后续 o2server 使用的数据库

SQL复制代码

1

CREATE DATABASE `X`

修改 mysql 配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf，在 mysqld 段下添加主库配置项

Shell复制代码

1

[mysqld]
2

# master的配置
3

server-id=1                                # 服务器id(设置唯一标识)
4

binlog-do-db=X                             # 要给从机同步的库
5

binlog-ignore-db=mysql                     # 不给从机同步的库(多个写多行)
6

binlog-ignore-db=information_schema        # 不给从机同步的库
7

binlog-ignore-db=performance_schema        # 不给从机同步的库
8

binlog-ignore-db=sys                       # 不给从机同步的库
9

log-bin=mysql-bin                          # 打开日志(主机需要打开),可以指定绝对路径
10

expire_logs_days=14                        # 自动清理14天前的log文件,可根据需要修改
11

general_log=ON                             # 开启sql语句记录
12

general_log_file=/var/log/mysql/query.log  # sql语句存放位置

重启数据库服务使之生效.这里说明一下，最后的 general_log 和 general_log_file 开启了 sql 执行语句的记录，这是为了后续对读写分离进行验证而设置。再次登录到 mysql 命令行界面执行

SQL复制代码
1

show variables like '%log_bin%';

show variables log_bin.png


确认 log_bin 已经开启.


执行 sql 语句创建同步使用的账户.创建一个 repl 用户,并为其简单设置管理员权限,这样可以同步一些 DDL 和 DCL 语句.


CREATE USER 'repl'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1234abcd';
GRANT ALL PRIVILEGES ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;


至此 master 服务器的配置完成了.接下来需要将 master 上的 X 数据库导出,以便导入到 slave01 和 slave02.


导出 master 数据库服务器上的 X 数据库


在 mysql 命令行界面中执行命令以锁定表.


FLUSH TABLES WITH READ LOCK;


在 master 服务器的 bash 命令行中执行数据库导出.


mysqldump -u root -p --databases X >/tmp/X.sql


查看数据库当前 log_bin 状态


 show master status G


master status.png


记录这里的 File: mysql-bin.000002, Position: 469,这两个参数记录了当前 master 服务器上数据库 X 修改到的日志位置,在后面的 slave 同步中需要用到,标志从这个位置开始同步,并解除数据库锁定.


UNLOCK TABLES;


slave01配置


修改 slave01 服务器上的 mysql 配置文件,在 mysqld 段下添加从库配置项


/etc/mysql/mysql.conf.d/mysqld.cnf


[mysqld]
# slave配置
server-id=2                   # MySQLid 后面2个从服务器需设置不同
skip_slave_start=1            # 复制进程不会随着数据库的启动而启动,重启数据库后需手动启动,加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错。
read_only=1                   # 从库普通账户只读；
master_info_repository=TABLE
relay_log_info_repository=TABLE
slave-skip-errors = 1032,1062,1007


重启数据库服务使配置生效


systemctl restart mysql


进入到 slave01 数据库命令行界面执行


创建 X 数据库


create database 'X';


将从 master 导出的 X.sql 文件拷贝过来,并导入到 X 数据库


use X;
source /tmp/X.sql


至此 master 数据库和 slave01 数据库的内容一致,但是尚未开启同步.


进入到 slave01 数据库命令行界面执行同步命令使同步生效,其中 master_log_file, master_log_pos 参见 master 上执行 show status G 的结果.


stop slave;
change master to master_host='172.16.91.40', master_user='repl', master_password='1234abcd', master_log_file='mysql-bin.000002', master_log_pos=469;
start slave;


slave02配置


slave02的配置


/etc/mysql/mysql.conf.d/mysqld.cnf


中的 server-id=3  不一样以外,其他全部一致,这里不再重复.


测试复制功能


至此已完成一主两从的数据库配置,通过图形客户端在 master 的 X 数据库上创建一个表,查看从库是否工作正常.


repl started.png


结束主从配置,得到一个一主两从的 mysql 数据库环境.


读写分离配置实现


下面进行读写分离配置实现.这里有两个中间件可以选择,分别是 maxsacle 和 proxy sql,我们这里采用 maxsacle 实现读写分离.


maxscale 配置


我们从安装包开始安装 maxscale,安装完成后需要对 maxscale 进行配置,配置文件为 /etc/maxscale/maxscale.cnf


安装 maxsacle


https://mariadb.com/downloads/community/maxscale/


下载之后直接通过本地文件安装


sudo apt install ./maxscale-6.3.0-1.ubuntu.focal.x86_64.deb


配置 maxscale


配置文件位置:


/etc/maxscale.cnf


maxscale cnf 1.png


[maxscale]


[maxscale]
threads=auto
admin_host=0.0.0.0
admin_secure_gui=false


threads:maxscale 线程数,auto 表示自动根据 cpu 设置;admin_host:允许通过 web 端访问 maxscale 的管理端;admin_secure_gui:web 管理端是否开启 https


[server1]


[server1]
type=server
address=172.16.91.41
port=3306
protocol=MariaDBBackend


server1 的配置:master mysql 服务器.


[server2]


[server2]
type=server
address=172.16.91.42
port=3306
protocol=MariaDBBackend


server2 的配置:master mysql 服务器.


[server3]


[server3]
type=server
address=172.16.91.43
port=3306
protocol=MariaDBBackend


server3 的配置:master mysql 服务器.


这里指向的是前面已完成的一主两从服务器.


maxscale cnf 2.png


[MariaDB-Monitor]


[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=server1,server2,server3
user=repl
password=1234abcd
monitor_interval=2000


监视器用于监视 3 个数据库服务器的状态,这里直接使用之前配置的 repl 账户,就不再创建账户了.


[Read-Only-Service]


#[Read-Only-Service]
#type=service
#router=readconnroute
#servers=server1
#user=myuser
#password=mypwd
#router_options=slave


Read-Only 默认已配置,由于本方案采用读写分离,需要将整个 Read-Only-Service 注释掉


maxscale cnf 3.png



[Read-Write-Service]


[Read-Write-Service]
type=service
router=readwritesplit
servers=server1,server2,server3
user=repl
password=1234abcd
enable_root_user=1


这是需要配置的核心服务,提供读写分离服务,这里直接使用之前配置的 repl 账户,就不再创建账户了.enable_root_user=1 允许 root 访问,否则需要另外创建用户才可以访问.


[Read-Write-Listener]


[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006


设置 maxsacle 自动启动,重启服务使之生效


sudo systemctl enable maxscale 
sudo systemctl restart maxscale


最后将服务在 4006 端口对外提供,配置至此完成,我们就可以在 o2server 服务器上修改配置,将数据库链接指向 172.16.91.40:4006 端口,就可以实现读写分离了.前面我们已经开启的 gui 管理端口:


http://172.16.91.40:8989
默认用户名/密码(admin/mariadb)

maxscale gui.png


修改 o2server 配置接入环境


修改 config/externalDataSources.json


[
	{
		"url":"jdbc:mysql://172.16.91.40/X?autoReconnect=true&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8",      
		"username" : "root",
		"password" :"password",
		"includes": [],
		"excludes": [],
		"enable" : true
	}
]


[
	{
		"url":"jdbc:mysql://127.0.0.1:4008/X?autoReconnect=true&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8",      
		"username" : "root",
		"password" :"1234abcd",
		"includes": [],
		"excludes": [],
		"enable" : true
	}
]

也可以通过数据日志验证读写分离.

或者通过 maxctrl list servers 查看分发情况:

splitted.png


报表和数据仓库接入

同理,可以配置 [Read-Only-Service] 实现数据的只读访问,用于报表软件统计、数据仓库拉取数据等只读数据访问场景.




相关问题讨论

o2oa 连接 mysql 数据时报 com.mysql.cj.jdbc.Driver 的问题求解

完成后我们在 mysqlmaster,mysqlslave01,mysqlslave02,三台服务器上都安装了 mysql,并且完成了简单的设置,允许远程 jdbc 链接到上面三台 mysql 服务器.

查看原帖讨论 · 18,599 次浏览

请问 o2oa 是不是在 MySQL 数据库下没办法创建自建表啊？

现在我们对 mysql 数据库进行基础配置,不涉及到主从复制,一次对 mysqlmaster,mysqlslave01,mysqlslave02进行同样的设置,设置完成后允许远程登陆.

查看原帖讨论 · 18,698 次浏览

o2oa 安装 sqlserver2008r2后无法启动

本次使用的是 ubuntu server 20.04运行 apt 命令查看 ppa mysql 数据库版本

查看原帖讨论 · 10,075 次浏览

相关内容

O2OA 翱途 企业应用开发平台

兰德网络O2OA平台软件

开源、自主可控的协同办公底座

研发与运营

纵横翱途（杭州）科技有限公司

企业级协同应用 · 工作流引擎 · AI 智能体

www.o2oa.net ｜ 400-888-0545

上一篇：安装nfs服务启用目录共享作为o2server集群环境下文件存储服务

下一篇：SmartBI集成版快速部署及使用（O2OA容器化部署）

浙江兰德纵横网络技术股份有限公司
联系我们
  • 电话：400-888-0545     0571-88480535
  • 邮箱：support@o2oa.net
  • 官网：https://www.o2oa.net

联系顾问

联系顾问

Copyright © 2020-2030 浙江兰德纵横网络技术股份有限公司 浙ICP备2025171672号-1 备案浙公网安备33010602013943号