Building MariaDB Galera Cluster 10.3.8

Building MariaDB Galera Cluster 10.3.8

Single machine installation and configuration (yum)

According to the official website, since MariaDB 10.1 version, the version has its own Galera cluster solution plug-in.
The operating system of the installation environment is CentOS 7, the virtual host of openstack.
Baidu search has some problems using keepalive in the openstack environment, so directly use nginx's stream to poll the database cluster.

graph LR
Nginx-->MariaDB-a01
Nginx-->MariaDB-a02
Nginx-->MariaDB-a03

Let's start with the stand-alone installation.

Configure to use Ali mirror

yum install wget
mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
wget -P /etc/yum.repos.d/ http://mirrors.aliyun.com/repo/epel-7.repo
yum clean all;yum makecache

Configure MariaDb Mirror

Use domestic mirror

vi /etc/yum.repos.d/MariaDB.repo
 Paste the following, save and exit :wq! ï¼›refresh, yum clean all;yum makecache


[mariadb]
name = MariaDB
baseurl = https://ipv4.mirrors.ustc.edu.cn/mariadb/yum/10.3/centos7-amd64
gpgkey=https://ipv4.mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1

Configure the proxy

Permanently configure yum proxy

vi /etc/yum.conf
 Add a local proxy (the local environment cannot access the external network)

proxy=http://10.18.xx.xx:808

Or temporarily configure the proxy

export http_proxy=http://10.18.xxx.xxx:808
export https_proxy=http://10.18.xxx.xxx:808

Install MariaDB

yum install MariaDB-server MariaDB-client -y

Configure MariaDB

service mariadb start
mysql_secure_installation
 Configure as prompted

Completely delete the MariaDB database (reset the configuration)

Here is a record of how to reset the database. Before setting up, there are sometimes configuration errors and other problems, which need to be overturned.

  • close mysql
[root@tyrzpt2 etc]# ps -ef|grep mysql 
mysql     3548     1  0 Jul26 ?        00:01:26 /usr/sbin/mysqld --wsrep-new-cluster --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root     14693  1739  0 11:42 pts/0    00:00:00 grep --color=auto mysql

[root@tyrzpt2 etc]# kill -9 3548
  • Uninstall MariaDB
yum -y remove Maria*
  • delete data content
rm -rf /var/lib/mysql/*
rm -rf /etc/my.cnf.d/
rm -rf /etc/my.cnf

Gelera cluster build

Network configuration preparation

Modify /etc/hosts and add the following configuration

132.xx.xx.11 mariadb-a01
132.xx.xx.18 mariadb-a02
132.xx.xx.35 mariadb-a03

Send configuration to other machines in the cluster

scp /etc/hosts root@132.xx.xx.18:/etc/hosts
scp /etc/hosts root@132.xx.xx.35:/etc/hosts

Galera configuration (3 hosts)

Modify /etc/my.cnf.d/server.cnf
The machine configuration of mariadb-a01 is as follows, other hosts should be modified accordingly

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names = 1
server_id=6011 #Custom, or the combination of the last two digits of the ip address

[galera]
wsrep_causal_reads=ON
wsrep_provider_options="gcache.size=4G"
wsrep_certify_nonPK=ON
query_cache_size=0
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_name=MariaDB-Galera-Cluster #cluster name
wsrep_cluster_address="gcomm://132.xx.xx.11,132.xx.xx.18,132.xx.xx.35" #IP address of the entire cluster
wsrep_node_name=mariadb-a01 #hostname, corresponding to the previous network configuration /etc/hosts
wsrep_node_address=132.97.54.11 #Machine IP address
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
wsrep_slave_threads=8
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=2G
wsrep_sst_method=rsync #Synchronously

start the cluster

# mariadb-a01 is the first to start, the command is
galera_new_cluster
# Start mariadb-a02, mariadb-a03 respectively, the command is
service mariadb start

#View logs
service mariadb status

test

Query the number of nodes

Database client such as navicat, connect to mariadb-a01 and view the cluster status
show status like 'wsrep%';
wsrep_cluster_size=3 indicates the current number of clusters

mysql -p
MariaDB [(none)]> show status like 'wsrep%';
+------------------------------+-------------------------------------------------------+
| Variable_name                | Value                                                 |
+------------------------------+-------------------------------------------------------+
| wsrep_apply_oooe             | 0.000000                                              |
| wsrep_apply_oool             | 0.000000                                              |
| wsrep_apply_window           | 1.000000                                              |
| wsrep_causal_reads           | 249                                                   |
| wsrep_cert_deps_distance     | 1.000000                                              |
| wsrep_cert_index_size        | 62                                                    |
| wsrep_cert_interval          | 0.000000                                              |
| wsrep_cluster_conf_id        | 5                                                     |
| wsrep_cluster_size           | 3                                                     |
| wsrep_cluster_state_uuid     | 37040e50-90c1-11e8-8a67-5fb4478cc753                  |
| wsrep_cluster_status         | Primary                                               |
| wsrep_commit_oooe            | 0.000000                                              |
| wsrep_commit_oool            | 0.000000                                              |
| wsrep_commit_window          | 1.000000                                              |
| wsrep_connected              | ON                                                    |

Nginx TCP load balancing configuration

nginx acts as the entry point and forwards the request poll to either database.
Machine IP: 132.xx.xx.100:3399

Install Nginx

yum install nginx -y

nginx configuration

vi /etc/nginx/nginx.conf

events {
    ........................
}

stream {
    upstream MariaDB_Cluster {
        server mariadb-a01:3306;
        server mariadb-a02:3306;
        server mariadb-a03:3306;
    }
    #Forward database port
    server {
        listen 3306;
        proxy_pass MariaDB_Cluster;
        proxy_connect_timeout 1s; # detect failure quickly
    }
}


http {
    ........................
}

Test verification

navicat client connection 132.xx.xx.100:3399, new query, hostname will change
SHOW VARIABLES WHERE Variable_name = 'hostname';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| hostname      | a02   |
+---------------+-------+


Reprinted from:
Author: gdxieyue
Link: https://www.jianshu.com/p/a11bbca92004

Posted by mewright on Sat, 14 May 2022 08:11:50 +0300