Galera 集群

目录

数据库集群

Galera 集群是一个针对 MariaDB 的多主（Multi-Master）高可用方案。

集群中每个 MariaDB 实例都保存数据库的一个完整副本，客户端可用通过任何一个实例读写数据。

虚拟机部署

部署架构

主机名 ip 服务
radius-db01 【hostnamectl set-hostname radius-db01】 10.4.7.200 mariadb
radius-db02 【hostnamectl set-hostname radius-db02】 10.4.7.201 mariadb
radius-db03 【hostnamectl set-hostname radius-db03】 10.4.7.202 mariadb
radius-lvs01 【vip 10.4.7.220】 10.4.7.203 keepalived
radius-lvs02 【vip 10.4.7.220】 10.4.7.204 keepalived

第一步：基础环境设置

 setenforce 0
 sed -i 's/^SELINUX=*$/SELINUX=disabled/1' /etc/selinux/config
systemctl disable firewalld --now
crontab -e
*/5 * * * * /usr/sbin/ntpdate ntp.aliyun.com >/dev/null 2>&1

第二步：安装mariadb server和 Galera插件

  1. 添加镜像源
curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.7"  --mariadb-maxscale-version="6.1"
  1. 安装软件
yum install MariaDB-server MariaDB-client MariaDB-backup -y
yum install maxscale -y

  1. 配置my.conf

    根据实际修改

    wsrep_cluster_address="gcomm://10.4.7.200,10.4.7.201,10.4.7.202"
    wsrep_node_address=10.4.7.202

    server_id = 7

[client]
port           = 3306
#socket          = /application/mysql/mysql.sock
default_character_set=utf8mb4
 
[mysqld]
user            = mysql
port            = 3306
#socket          = /application/mysql/mysql.sock
#basedir         = /application/mysql
#datadir         = /application/mysql/data
#
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci
 
open_files_limit = 1024
max_connections = 800
max_connect_errors = 3000
#table_cache    = 614
sort_buffer_size = 1M
join_buffer_size = 1M
thread_cache_size = 100
#thread_concurrency = 2
query_cache_size = 2M
query_cache_limit = 2M
query_cache_min_res_unit = 2k
tmp_table_size = 2M
max_heap_table_size = 2M
key_buffer_size = 16M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 1M
skip-name-resolve
lower_case_table_names = 1
 
 
##################################binlog
log_bin                 = /var/lib/mysql/mysql-bin
binlog_format           = row
binlog_cache_size       = 1M
max_binlog_cache_size   = 100M
max_binlog_size         = 1G
sync_binlog             = 1
expire_logs_days        = 5
server_id               = 7
 
 
############################################# slowlog
slow_query_log = on
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = on
log_throttle_queries_not_using_indexes = 5
min_examined_row_limit  = 800
 
###########################################
#innodb params
default_storage_engine = innodb
innodb_file_per_table = on
transaction_isolation = REPEATABLE-READ
innodb_data_file_path = ibdata1:12M:autoextend
innodb_thread_concurrency       = 8
innodb_read_io_threads  = 4
innodb_write_io_threads = 4
 
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 8
#innodb_additional_mem_pool_size = 4M
 
innodb_random_read_ahead    = OFF
innodb_read_ahead_threshold = 56
 
#######################################
# change_pool
#innodb_max_changed_pages = 2000000
innodb_change_buffering = all
#innodb_change_buffering = none
#innodb_change_buffering = inserts
#innodb_change_buffering = delets
#innodb_change_buffering = changes
#innodb_change_buffering = purges
 
##################################
innodb_old_blocks_pct  = 20
innodb_old_blocks_time = 1000
 
##################################
 
innodb_log_group_home_dir = ./
innodb_log_files_in_group = 2
innodb_log_file_size = 50M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit  = 1
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
 
#innodb_data_home_dir = /application/mysql/data/
#innodb_data_file_path = ibdata1:80M;ibdata2:12M:autoextend
 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_warnings = 1
 
##################################
# wsrep
##################################
skip_name_resolve = ON
#binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
#启用 wsrep 复制
wsrep_sst_method=rsync
bind-address=0.0.0.0
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
 
#Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="my_mariadb_cluster"
wsrep_on=ON
wsrep_cluster_address="gcomm://10.4.7.200,10.4.7.201,10.4.7.202"
wsrep_node_address=10.4.7.202
##
innodb_flush_log_at_trx_commit=0
##################################
##################################
 
[mysqld_safe]
 
[mysql]
no_auto_rehash
default_character_set=utf8mb4
 
[mysqldump]
quick
max_allowed_packet = 2M

第三步：初始化集群

  1. 初始化
# 集群中的第一个节点执行,事例中我在 10.4.7.200中执行
galera_new_cluster
systemctl start mariadb && journalctl -fu mariadb
# 所有节点执行
systemctl start mariadb && journalctl -fu mariadb
  1. 检查集群状态
[root@localhost ~]#  mysql -e "show status like '%wsrep_cluster_size%';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

第四步：负载均衡（可选）

  1. 创建用户
mysql -e "grant all on *.* to 'root'@'%' identified by '123';flush privileges;"
  1. 负载均衡设置
 yum install ipvsadm keepalived -y
echo "net.ipv4.ip_forward = 1" >>/etc/sysctl.conf

  1. realServer

    本例中在以下节点执行

    10.4.7.200

    10.4.7.201

    10.4.7.202

    		echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore 
    		echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
    		echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore 
    		echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
    		ip addr add 10.4.7.220/32 dev lo
    		ip route add 10.4.7.220 dev lo

  2. 负载均衡器配置

    本例在 10.4.7.203 执行

    		[root@radius-db01 ~]# cat  /etc/keepalived/keepalived.conf 
    		! Configuration File for keepalived
    		 
    		##########################################################
    		global_defs {
    		        notification_email {
    		                wangendao@.crv.com.cn
    		                email2
    		        }
    		        notification_email_from wangendao@.crv.com.cn
    		        smtp_server 218.13.52.193
    		        smtp_connect_timeout 30
    		        router_id LVS_7 # specify the name of the lvs director,master and backend use different name
    		 
    		}
    		 
    		##########################################################
    		vrrp_instance VI_1 {     
    		    state MASTER 
    		    interface eth0
    		    virtual_router_id 51 
    		    priority 150
    		    advert_int 1
    		    vrrp_garp_master_delay 2
    		    nopreempt  
    		    authentication {
    		        auth_type PASS
    		        auth_pass 1111
    		    }
    		    virtual_ipaddress {
    		        10.4.7.220
    		    }
    		}
    		##########################################################
    		virtual_server 10.4.7.220 3306 {
    		        delay_loop 6
    		        lb_algo rr
    		        lb_kind DR
    		        #persistence_timeout 50
    		        protocol TCP
    		        real_server 10.4.7.201 3306 {
    		                weight 1
    		                inhibit_on_failure
    		                TCP_CHECK {
    		                        connect_timeout 3
    		                }
    		        }
    		        real_server 10.4.7.200 3306 {
    		                weight 1
    		                inhibit_on_failure
    		                TCP_CHECK {
    		                        connect_timeout 3
    		                }
    		        }
    		        real_server 10.4.7.202 3306 {
    		                weight 1
    		                inhibit_on_failure
    		                TCP_CHECK {
    		                        connect_timeout 3
    		                }
    		        }
    		}

    启动keepalived

    		systemctl enable keepalived --now

  3. 测试负载均衡效果

    		import pymysql
    		import time
    		 
    		def c():
    		    connect=pymysql.connect(host="10.4.7.220",port=3306,user="root",passwd="123",charset="utf8")
    		    
    		    cursor=connect.cursor(pymysql.cursors.DictCursor)
    		    sql="show variables like '%hostname%';"
    		    
    		    row=cursor.execute(sql)
    		    info=cursor.fetchone()
    		    
    		    print(info)
    		    #cursor.commit()
    		    cursor.close()
    		    connect.close()
    		 
    		n=1
    		while n<100:
    		    c()
    		    n+=1
    		    time.sleep(1)

  4. 实现vip故障接管

第五步：监控数据库

  1. 安装mysql_exporter
  2. 安装prometheus grafana
docker pull prom/prometheus:v1.1.0
docker pull prom/mysqld-exporter:v0.14.0
docker pull grafana/grafana:9.2.2
docker pull prom/node-exporter:v1.4.0
docker run -d -v `pwd`/prometheus.yml:/etc/prometheus/prometheus.yml --net=host prom/prometheus:v1.1.0
#
docker run -d --net=host  grafana/grafana:9.2.2
#
docker run -d \
-v /etc/my.cnf.d/server.cnf:/etc/my.cnf.d/server.cnf:ro \
-e DATA_SOURCE_NAME="root:123@(127.0.0.1:3306)/" \
--net=host \
prom/mysqld-exporter:v0.14.0 \
--config.my-cnf="/etc/my.cnf.d/server.cnf"
#
docker run -d --net=host prom/node-exporter:v1.4.0

grafana Galera集群模板13106 7362

grafana 主机集群模板12633

grafana LVS模板14728

  1. 邮箱报警

docker

第一步：制作镜像

# cat Dockerfile 
FROM centos:centos7.9.2009
MAINTAINER wangendao(1209233066@qq.com)
 
ARG TZ=Asia/Shanghai
 
ADD entrypoint.sh /entrypoint.sh
RUN echo -e "[mariadb]\nname = MariaDB\nbaseurl = https://mirror.tuna.tsinghua.edu.cn/mariadb/mariadb-10.7.1/yum/centos7-amd64\ngpgcheck=0" >/etc/yum.repos.d/MariaDB.repo \
    && yum install MariaDB-server MariaDB-client galera-4 -y \
    && ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo $TZ > /etc/timezone \
    && chmod 755 /entrypoint.sh \
    && chown -R mysql.mysql /etc/my*
 
USER mysql
ENTRYPOINT ["/entrypoint.sh"]
# cat entrypoint.sh
#!/bin/bash
 
set -ex
 
ip=$(hostname -i)
 
 
config(){
cat > /etc/my.cnf.d/server.cnf <<EOF
[mysqld]
user=mysql
skip_name_resolve = ON
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
wsrep_sst_method=rsync
bind-address=0.0.0.0
wsrep_provider=/usr/lib64/galera-4/libgalera_smm.so
 
#Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="seafile_mariadb_cluster"
wsrep_on=ON
wsrep_cluster_address="gcomm://node1,node2,node3"
wsrep_node_address=$ip
EOF
}
 
config
 
exec "$@"

第二步：启动容器

docker network create -d bridge --subnet=172.16.100.0/24 --gateway=172.16.100.1 mybridge
docker run -it --rm \
--net=mybridge --ip 172.16.100.10 \
--add-host=node1:172.16.100.10 \
--add-host=node2:172.16.100.11 \
--add-host=node3:172.16.100.12 \
1209233066/mariadb:centos-10.7.1 mysqld --wsrep-new-cluster
 
docker run -it --rm \
--net=mybridge --ip 172.16.100.11 \
--add-host=node1:172.16.100.10 \
--add-host=node2:172.16.100.11 \
--add-host=node3:172.16.100.12 \
1209233066/mariadb:centos-10.7.1 mysqld
 
docker run -it --rm \
--net=mybridge --ip 172.16.100.12 \
--add-host=node1:172.16.100.10 \
--add-host=node2:172.16.100.11 \
--add-host=node3:172.16.100.12 \
1209233066/mariadb:centos-10.7.1 mysqld

第三步：验证集群状态

# docker exec -it 8114237cb35b mysql -e "show status like '%wsrep_cluster_size%';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

参考
https://blog.csdn.net/yabingshi_tech/article/details/109033502
https://mariadb.com/kb/en/getting-started-with-mariadb-galera-cluster/

posted @   mingtian是吧  阅读(292)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· 葡萄城 AI 搜索升级：DeepSeek 加持，客户体验更智能
· 什么是nginx的强缓存和协商缓存
· 一文读懂知识蒸馏
点击右上角即可分享
微信分享提示