Galera 集群

数据库集群
- 虚拟机部署
- docker

数据库集群

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插件

添加镜像源

curl -LsS https://r.mariadb.com/downloads/mariadb_repo_setup | sudo bash -s -- --mariadb-server-version="mariadb-10.7"  --mariadb-maxscale-version="6.1"

安装软件

yum install MariaDB-server MariaDB-client MariaDB-backup -y
yum install maxscale -y

配置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

第三步：初始化集群

初始化

# 集群中的第一个节点执行,事例中我在 10.4.7.200中执行
galera_new_cluster
systemctl start mariadb && journalctl -fu mariadb

# 所有节点执行
systemctl start mariadb && journalctl -fu mariadb

检查集群状态

[root@localhost ~]#  mysql -e "show status like '%wsrep_cluster_size%';"
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

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

创建用户

mysql -e "grant all on *.* to 'root'@'%' identified by '123';flush privileges;"

负载均衡设置

 yum install ipvsadm keepalived -y

echo "net.ipv4.ip_forward = 1" >>/etc/sysctl.conf

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

负载均衡器配置

本例在 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

测试负载均衡效果

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)

实现vip故障接管

第五步：监控数据库

安装mysql_exporter
安装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

邮箱报警

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 @ 2022-11-04 17:55 mingtian是吧阅读(382) 评论(0) 收藏举报

刷新页面返回顶部