技术文章

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

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

时间:2022-04-28   

本文主要描述:实现数据库读写分离搭建高负载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


  • mysqlslave02:


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配置文件,在mysqld段下添加主库配置项

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

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]实现数据的只读访问,用于报表软件统计,数据仓库拉取数据等只读数据访问.





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

下一篇:SmartBI集成版快速部署及使用(O2OA容器化部署)