篇三：mariadb叢集 【共四篇】
搭建 MariaDB高可用資料庫叢集 == 基于mariadb + galera + keepalived + nginx
一、内容介紹：
(1) 本章目的：
代理轉發：
通過nginx + keepalive叢集實作代理轉發高可用，任何一個節點故障，VIP位址自動漂移到其他節點。
資料同步：
通過galera實時同步mariadb叢集資料和日志檔案，通過選舉最新狀态的節點，同步更新叢集其他節點。
支援多個資料庫節點同時多讀和多寫，并且保證資料的一緻性和完整。
(2) 實驗環境：
實體機：VMware 虛拟機 + CentOS 7.9 x64
mariadb: mariadb-10.2
galera: mysql-galera自帶版本
(3) 機器資訊
實體機1 192.168.8.35 CNT7XMDBD01（master-01） vip = 192.168.8.31
實體機2 192.168.8.36 CNT7XMDBD02（master-02） vip = 192.168.8.32
實體機3 192.168.8.37 CNT7XMDBD03（master-03） vip = 192.168.8.33
二、安裝步驟：
在叢集三個節點，安裝 gelara + mariadb，編輯mariadb配置參數, 然後注冊和啟動mariadb服務。
檢查mariadb叢集正常，做故障模拟測試：任何n-1個節點故障，都不影響叢集提供資料庫服務。
三、步驟細節：
步驟 step 1:
安裝準備工作，安裝依賴軟體，删除系統預設安裝mariadb版本
# 設定mariadb國内鏡像位址
cat <<EOF> /etc/yum.repos.d/mariadb.repo
[mariadb]
name=MariaDB
baseurl=http://mirrors.ustc.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=http://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
# 删除系統已安裝的mariadb, 防止版本沖突
yum remove -y mariadb-*
rpm -qa |grep mariadb
# galera叢集，節點互相同步資料，需要安裝的依賴
yum install -y perl lsof rsync socat 步驟 step 2:
安裝mariadb元件
# MariaDB 10.0版本這麼安裝
# yum -y install MariaDB-Galera-server MariaDB-client galera
# MariaDB 10.1版本開始,Galera Cluster就被包含在MariaDB包裡,不需要單獨部署MariaDB-Galera-server和galera
yum -y install MariaDB-server MariaDB-client galera 初始化和配置mariadb
# 配置MariaDB
# 初始化MariaDB,每個節點都需要初始化一次,除了改密碼mariadb123456,其餘步驟都按Y
/etc/init.d/mysql start
/usr/bin/mysql_secure_installation
# 也可以使用mysqladmin設定密碼
/usr/bin/mysqladmin -u root password 'password'
/usr/bin/mysqladmin -u root -h MariaDB-Galera-31 password 'password'
# 輸出結果，如下：
## [root@CNT7XMDBD01 ~]# ls -al /etc/init.d/mysql
## -rwxr-xr-x. 1 root root 12175 May 18 2022 /etc/init.d/mysql
## [root@CNT7XMDBD01 ~]# /etc/init.d/mysql start
## Starting mysql (via systemctl): [ OK ]
## [root@CNT7XMDBD01 ~]# /usr/bin/mysql_secure_installation
##
## NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
## SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
##
## In order to log into MariaDB to secure it, we'll need the current
## password for the root user. If you've just installed MariaDB, and
## you haven't set the root password yet, the password will be blank,
## so you should just press enter here.
##
## Enter current password for root (enter for none):
## ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
## Enter current password for root (enter for none):
## ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
## Enter current password for root (enter for none):
## OK, successfully used password, moving on...
##
## Setting the root password ensures that nobody can log into the MariaDB
## root user without the proper authorisation.
##
## Set root password? [Y/n] y
## New password:
## Re-enter new password:
## Password updated successfully!
## Reloading privilege tables..
## ... Success!
##
##
## By default, a MariaDB installation has an anonymous user, allowing anyone
## to log into MariaDB without having to have a user account created for
## them. This is intended only for testing, and to make the installation
## go a bit smoother. You should remove them before moving into a
## production environment.
##
## Remove anonymous users? [Y/n] y
## ... Success!
##
## Normally, root should only be allowed to connect from 'localhost'. This
## ensures that someone cannot guess at the root password from the network.
##
## Disallow root login remotely? [Y/n] y
## ... Success!
##
## By default, MariaDB comes with a database named 'test' that anyone can
## access. This is also intended only for testing, and should be removed
## before moving into a production environment.
##
## Remove test database and access to it? [Y/n] n
## ... skipping.
##
## Reloading the privilege tables will ensure that all changes made so far
## will take effect immediately.
##
## Reload privilege tables now? [Y/n] y
## ... Success!
##
## Cleaning up...
##
## All done! If you've completed all of the above steps, your MariaDB
## installation should now be secure.
##
## Thanks for using MariaDB!
## [root@CNT7XMDBD01 ~]# 步驟 step 3: (可選)
建立同步DB的賬号，設定賬号權限
## 登入，預設沒有密碼
mysql -u root -p
# 或者，自定義指定端口
# ./mysql -u root -P 3306 -p
# ./mysql -P 3306 -u root -p
# ./mysql -P 3306 -u root@localhost -p
# ./mysql -u root@localhost -P 3306 -p
#
# 輸出結果，如下：
## [root@CNT7XMDBD01 bin]# mysql -u root -p
## WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.
## Welcome to the MariaDB monitor. Commands end with ; or \g.
## Your MariaDB connection id is 6
## Server version: 10.9.5-MariaDB MariaDB Server
##
## Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
##
## Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##
## MariaDB [(none)]> show databases;
## +--------------------+
## | Database |
## +--------------------+
## | information_schema |
## | test |
## +--------------------+
## 2 rows in set (0.003 sec)
##
## MariaDB [(none)]>
# 修改密碼
## 方式1：修改賬号密碼(方式1：指令行 連結密碼)，
# mysql >> alter user 'root'@'localhost' identified by 'password';
# mysql >> create user 'app_openldap'@'%' identified by 'password';
mysql >> create user 'app_galera'@'%' identified by 'password';
## 建立資料庫
# mysql >> create database db_openldap default charset utf8mb4 collate utf8mb4_general_ci;
mysql >> create database ldap default charset utf8mb4 collate utf8mb4_unicode_ci;
## 添加賬号的權限
## 授權，預設建立的使用者權限是usage,就是無權限，隻能登入而已，
## （all：所有權限，這裡有select,update等等權限，可以去搜一下；後面的*.*：指定資料庫.指定表，這裡是所有；to後面就是你剛才建立的使用者）
## grant all privileges on *.* to 'app_openldap'@'%';
# mysql >> grant all privileges on db_openldap.* to 'app_openldap'@'%';
mysql >> grant all privileges on *.* to 'app_galera'@'%';
## 最後，記得刷洗一下，否則用戶端無法通過新加的賬号連結MYSQL資料庫
mysql >> flush privileges;
# 賬号2：給haproxy代理mariadb使用（包括健康檢查，在haproxy-status網頁上顯示mariadb伺服器的up或down健康狀态）
mysql >> create user 'haproxy'@'%' identified by '';
mysql >> grant all privileges on *.* to 'haproxy'@'%';
mysql >> flush privileges; 步驟 step 4:
配置叢集同步參數
# 首先，停止服務
/etc/init.d/mysql stop
# 備份配置檔案
mkdir -p /etc/my.cnf.d/bak/
cp /etc/my.cnf.d/*.* /etc/my.cnf.d/bak/
# 配置叢集同步參數
# 【重點提醒】：
# 1. 首個master節點，第一次啟動時候，要設定參數為 ： wsrep_cluster_address="gcomm://"
# 首個master節點，第二次啟動時候，要設定參數為 ： wsrep_cluster_address="gcomm://192.168.8.35,192.168.8.36,192.168.8.37" （或自定義叢集的節點IP）
#
# 2. 其他master節點，在首個master啟動後再啟動時候，要設定參數為 ： wsrep_cluster_address="gcomm://192.168.8.35,192.168.8.36,192.168.8.37" （或自定義叢集的節點IP）
# ----------------------------------- 編輯内容，如下 ----------------------------- begin
cat <<EOF> /etc/my.cnf.d/server.cnf
[server]
[mysqld]
bind-address=192.168.8.35
skip-external-locking
skip-name-resolve
innodb_file_per_table=on
max_connections=10000
collation-server=utf8mb4_unicode_ci
character-set-server=utf8mb4
innodb_flush_method=O_DIRECT
wait_timeout=28800
binlog_cache_size=16M
max_allowed_packet=64M
expire_logs_days=30
sort_buffer_size=128M
innodb_buffer_pool_size=512M
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
[galera]
wsrep_on=1
wsrep_provider="/usr/lib64/galera/libgalera_smm.so"
wsrep_cluster_address="gcomm://192.168.8.35,192.168.8.36,192.168.8.37"
wsrep_cluster_name=galera_cluster
wsrep_node_address=192.168.8.35
wsrep_node_name=CNT7XMDBD01
wsrep_slave_threads=1
wsrep_causal_reads=ON
wsrep_certify_nonPK=ON
wsrep_sst_method=rsync
# wsrep_sst_method=xtrabackup-v2
# wsrep_sst_auth=app_galera:password
EOF
# ----------------------------------- 編輯内容，如下 ----------------------------- end 步驟 step 5:
啟動叢集
首先，首個啟動的mater節點
# 備注：
# 配置檔案，在叢集其他節點，隻有5個不同參數: [mysqld]的server_id、bind-address; [galera]的wsrep_node_address、wsrep_node_name、bind-address
# 在master上使用--wsrep-new-cluster啟動,第一次啟動時才使用此參數。
# 或者，使用如下指令，建立叢集
# 方式1
# galera_new_cluster
# 方式2
# mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql --wsrep-new-cluster &
# 添加mysql服務使用的賬号
# useradd -m mysql
# 1. 初始節點 （master）
# 手動啟動
mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql --wsrep-new-cluster &
# 2. 後面，其他啟動的mater節點
mysqld_safe --defaults-file=/etc/my.cnf.d/server.cnf --user=mysql & 步驟 step 6:
檢查叢集
通過mysql指令行視窗，查詢叢集連接配接資訊，如下提示
有三個節點：wsrep_cluster_size =1 （其他兩個節點還沒有啟動，啟動完畢後顯示為3）
# 登入mysql指令行
mysql -u root -p
# 然後，按照提示輸入密碼
mysql >> show status like 'wsrep%';
# 列印字段說明：wsrep_cluster_status | Primary === 叢集狀态，為主節點
# 列印字段說明：wsrep_cluster_size | 1 === 節點已加入個數
# 輸出結果，如下：（如下，表示叢集建立成功，且已經加入節點數目為1，總共3個節點）
## [root@CNT7XMDBD01 ~]# mysql -u root -p
## Enter password:
## Welcome to the MariaDB monitor. Commands end with ; or \g.
## Your MariaDB connection id is 11
## Server version: 10.2.44-MariaDB MariaDB Server
##
## Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
##
## Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
##
## unknown [(none)]> show status like 'wsrep%';
## No connection. Trying to reconnect...
## Connection id: 10
## Current database: *** NONE ***
##
## +-------------------------------+-------------------------------------------------------+
## | Variable_name | Value |
## +-------------------------------+-------------------------------------------------------+
## | wsrep_applier_thread_count | 1 |
## | wsrep_apply_oooe | 0.000000 |
## | wsrep_apply_oool | 0.000000 |
## | wsrep_apply_waits | 0 |
## | wsrep_apply_window | 0.000000 |
## | wsrep_causal_reads | 0 |
## | wsrep_cert_deps_distance | 0.000000 |
## | wsrep_cert_index_size | 0 |
## | wsrep_cert_interval | 0.000000 |
## | wsrep_cluster_conf_id | 5 |
## | wsrep_cluster_size | 3 |
## | wsrep_cluster_state_uuid | 5090ce21-aa97-11ed-9e50-6f2afccafdcf |
## | wsrep_cluster_status | Primary |
## | wsrep_cluster_weight | 3 |
## | wsrep_commit_oooe | 0.000000 |
## | wsrep_commit_oool | 0.000000 |
## | wsrep_commit_window | 0.000000 |
## | wsrep_connected | ON |
## | wsrep_desync_count | 0 |
## | wsrep_evs_delayed | |
## | wsrep_evs_evict_list | |
## | wsrep_evs_repl_latency | 0.00175987/0.00336961/0.00410462/0.000831769/5 |
## | wsrep_evs_state | OPERATIONAL |
## | wsrep_flow_control_active | false |
## | wsrep_flow_control_paused | 0.000000 |
## | wsrep_flow_control_paused_ns | 0 |
## | wsrep_flow_control_recv | 0 |
## | wsrep_flow_control_requested | false |
## | wsrep_flow_control_sent | 0 |
## | wsrep_gcomm_uuid | 508e6482-aa97-11ed-9d4c-726c41b5c959 |
## | wsrep_gmcast_segment | 0 |
## | wsrep_incoming_addresses | 192.168.8.35:3306,192.168.8.37:3306,192.168.8.36:3306 |
## | wsrep_last_committed | 1 |
## | wsrep_local_bf_aborts | 0 |
## | wsrep_local_cached_downto | 18446744073709551615 |
## | wsrep_local_cert_failures | 0 |
## | wsrep_local_commits | 0 |
## | wsrep_local_index | 0 |
## | wsrep_local_recv_queue | 0 |
## | wsrep_local_recv_queue_avg | 0.000000 |
## | wsrep_local_recv_queue_max | 1 |
## | wsrep_local_recv_queue_min | 0 |
## | wsrep_local_replays | 0 |
## | wsrep_local_send_queue | 0 |
## | wsrep_local_send_queue_avg | 0.000000 |
## | wsrep_local_send_queue_max | 1 |
## | wsrep_local_send_queue_min | 0 |
## | wsrep_local_state | 4 |
## | wsrep_local_state_comment | Synced |
## | wsrep_local_state_uuid | 5090ce21-aa97-11ed-9e50-6f2afccafdcf |
## | wsrep_open_connections | 0 |
## | wsrep_open_transactions | 0 |
## | wsrep_protocol_version | 9 |
## | wsrep_provider_name | Galera |
## | wsrep_provider_vendor | Codership Oy <[email protected]> |
## | wsrep_provider_version | 25.3.35(r545d0bf) |
## | wsrep_ready | ON |
## | wsrep_received | 2 |
## | wsrep_received_bytes | 297 |
## | wsrep_repl_data_bytes | 0 |
## | wsrep_repl_keys | 0 |
## | wsrep_repl_keys_bytes | 0 |
## | wsrep_repl_other_bytes | 0 |
## | wsrep_replicated | 0 |
## | wsrep_replicated_bytes | 0 |
## | wsrep_rollbacker_thread_count | 1 |
## | wsrep_thread_count | 2 |
## +-------------------------------+-------------------------------------------------------+
## 67 rows in set (0.00 sec)
##
## MariaDB [(none)]> 測試叢集VIP位址通路是否連通
# 1. 測試本地實體IP
# 實體節點1
mysql -h 192.168.8.35 -u app_galera -P 3306 -p
# 實體節點2
mysql -h 192.168.8.36 -u app_galera -P 3306 -p
# 實體節點3
mysql -h 192.168.8.37 -u app_galera -P 3306 -p
# 2. 測試叢集代理VIP
# 叢集代理VIP位址1
mysql -h 192.168.8.31 -u app_galera -P 13306 -p
# 叢集代理VIP位址2
mysql -h 192.168.8.33 -u app_galera -P 13306 -p
# 叢集代理VIP位址3
mysql -h 192.168.8.33 -u app_galera -P 13306 -p 測試結論：
如上無論是通過實體節點，還是叢集代理VIP位址連接配接叢集或節點，
使用者所操作的DML或DDL語句，是自動被叢集同步機制實時同步到其他實體節點上面。
并且，多個實體節點支援同時多讀和多寫，任何N-1個節點故障，
都不影響叢集VIP位址提供資料庫讀寫服務。
![國産高可用軟體，雙機熱備與冷備的差別[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)