搭建MariaDB高可用集群 == 【篇三】 == MariaDB集群

篇三：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 <info@codership.com>                     |
## | 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地址提供数据库读写服务。