MariadDB高可用方案之galera集群部署，并配置mysqld_exporter，监控数据库状态
部署galera集群
部署环境：centos
mariadb版本 10.8.3
集群机器ip假定为：192.168.0.11、192.168.0.12、192.168.0.13
下载好离线rmp包，下载链接：https://mariadb.com/download-confirmation?group-name=Community%20Server&release-notes-uri=https%3A%2F%2Fmariadb.com%2Fkb%2Fen%2Fmariadb-1083-release-notes%2F&documentation-uri=https%3A%2F%2Fmariadb.com%2Fkb%2Fen%2Fwhat-is-mariadb-108%2F&download-uri=https%3A%2F%2Fdlm.mariadb.com%2F2310410%2FMariaDB%2Fmariadb-10.8.3%2Fyum%2Fcentos%2Fmariadb-10.8.3-rhel-7-x86_64-rpms.tar&product-name=Community%20Server&download-size=661.81%20MB
在3台机器上分别执行
解压到/opt/mariadb-10.8.3-rhel-7-x86_64-rpms文件夹下
cd 到 /opt/mariadb-10.8.3-rhel-7-x86_64-rpms
# 安装需要的包
yum -y install rsync nmap lsof per-DBI socat boots-devel.x86_64
# 先清理mariadb需要的lib，怕版本太旧影响后续安装
yum -y remove mariadb-libs
# 安装mariadb包文件
rpm -ivh jemalloc-3.6.0-1.el7.x86_64.rpm jemalloc-devel-3.6.0-1.el7.x86_64.rpm
rpm -ivh MariaDB-common-10.8.3-1.el7.centos.x86_64.rpm MariaDB-compat-10.8.3-1.el7.centos.x86_64.rpm MariaDB-client-10.8.3-1.el7.centos.x86_64.rpm galera-4-26.4.11-1.el7.centos.x86_64.rpm MariaDB-server-10.8.3-1.el7.centos.x86_64.rpm
至此，mariadb数据库已经安装好了。
启动mariadb数据库
systemctl start mariadb
分别在3台机器上执行：
执行 mysql 进入数据库，默认是没有设置密码的
创建用户root 和设置密码123456
创建用户scalemon和设置密码123456
创建用户maxscale和设置密码123456
创建用户exporter和设置密码123456
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
CREATE USER scalemon@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLENT, SUPER, RELOAD ON *.* TO scalemon@'%';
CREATE USER maxscale@'%' IDENTIFIED BY '123456';
GRANT SELECT,SHOW DATABASES ON *.* TO maxscale@'%';
CREATE USER exporter@'%' IDENTIFIED BY '123456';
GRANT PROCESS,REPLICATION CLENT,SELECT ON *.* TO exporter@'%';
FLUSH PRIVILEGES;
exit 退出mysql控制
然后停止mariadb运行， systemctl stop mariadb
在3个节点上均需修改配置 vi /etc/my.cnf.d/server.cnf
[mysqld]
max_connections=5000
max_allowed_packet = 1024M
explicit_defaults_for_timestamp=1
innodb_file_per_table = 1
# 开启慢查询
slow_query_log=ON
slow_query_log_file=/var/log/mysql_slow_query.log
# galera最重要的配置
[galera]
wsrep_on=ON
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.0.11,192.168.0.12,192.168.0.13"
binlog_format=row
expire_logs_days=1
log_bin=/var/log/binlog/bin
log_slave_updates=ON
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_slave_threads=8
innodb_flush_log_at_trx_commit=0
随后在192.168.0.11上执行敏命令初始化集群
galera_new_cluster && systemctl enable mariadb
再其他节点192.168.0.12、192.168.0.13执行以下命令加入集群
systemctl start mariadb && systemctl enable mariadb
检查集群有没有成功
执行 mysql -uroot -p123456 进入 mysql 控制台
执行sql: show status like ‘wsrep_cluster_size’;
返回结果为3 则部署成功
部署maxscale
在192.168.0.14上安装maxscale
yum -y install maxscale-2.5.8-1.rhel1.7.x86_64.rpm
配置maxscale信息，vi /etc/maxscale.cnf
[maxscale]
threads=auto
logdir=/var/log/maxscale/
log_augmentation=1
admin_host=0.0.0.0
admin_secure_gui=false
[dbserv1]
type=server
address=192.168.0.11
port=3306
protocol=MariaDBBackend
[dbserv2]
type=server
address=192.168.0.12
port=3306
protocol=MariaDBBackend
[dbserv3]
type=server
address=192.168.0.13
port=3306
protocol=MariaDBBackend
[MariaDB-Monitor]
type=monitor
module=galeramon
servers=dbserv1, dbserv2, dbserv3
user=scalemon
password=123456
monitor_interval=10000
[Read-Write-Service]
type=service
router=readwritesplit
servers=dbserv1, dbserv2, dbserv3
enable_root_user=1
user=maxscale
password=123456
# 主服务器挂了，不能再写入数据
master_failure_mode=error_on_write
master_reconnection=true
# 连接失败的请求，会记录下来
log_auth_warnings=true
# 配置监听
[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
address=0.0.0.0
port=3306
启动maxscale
systemctl start maxscale && systemctl enable maxscale
执行命令查看集群状况
maxctrl list servers
后续可以直接连接192.168.0.14节点直接操作数据库mariadb集群
部署mysqld_exporter
在192.168.0.11、192.168.0.12、192.168.0.13上分别部署配置
下载安装tar.gz包并解压/usr/local下
tar xvf mysqld_exporter-0.13.1.linux-amd64.tar.gz -C /usr/local
修改文件
cd /usr/local
mv mysqld_exporter-0.13.1.linux-amd64/ mysqld_exporter
配置mysqld_exporter启动
vi /usr/local/mysqld_exporter/mysqld_exporter.cnf
[client]
host=localhost
port=3306
user=exporter
password=123456
将mysqld_exporter放在systemctl中
vi /usr/lib/systemd/system/mysqld_exporter.service
[Unit]
Description=mysqld_exporter
After=network.target
[Service]
ExecStart=/usr/local/mysqld_exporter/mysqld_exporter --web.listen-address=[0.0.0.0:9104](http://0.0.0.0:9104/) \
--config.my-cnf /usr/local/mysqld_exporter/mysqld_exporter.cnf \
--log.level=error \
--collect.info_schema.processlist \
--collect.info_schema.innodb_metrics \
--collect.info_schema.innodb_tablespaces \
--collect.info_schema.innodb_cmp \
--collect.info_schema.innodb_cmpmem \
--collect.info_schema.tables
Restart=on-failure
[Install]
WantedBy=multi-user.target
启动mysqld_exporter.service
systemctl daemon-reload && systemctl start mysqld_exporter.service && systemctl enable mysqld_exporter.service
curl {ip}:9104/metrics获取mariadb的指标数据，可以配置到prometheus监控系统中