在今天的教程中，我将引导我们逐步完成在Debian 10(Buster)上设置MariaDB Galera集群的步骤。对于在MariaDB中运行大量生产工作负载的人来说，数据库服务器的高可用性需要部署Galera集群。

MariaDB Galera群集是MariaDB的同步多主群集，具有对XtraDB/InnoDB存储引擎的支持。它具有以下主要功能：提供主动-主动多主站拓扑结构我们可以读写任何集群节点具有自动节点加入功能自动成员资格控制，失败的节点从集群中删除具有真正的并行复制，行级直接客户端连接

我们假设我们已经准备好数据库服务器，并且我们以具有sudo特权的用户身份登录服务器。满足这些基本要求后，请继续在Debian 10(Buster)上安装MariaDB Galera Cluster。

更新所有服务器

让我们确保所有服务器都已更新。

sudo apt update && sudo apt -y upgrade
sudo reboot

设定主机名称和DNS

让我们在服务器中配置适当的主机名。

# DB1
sudo hostnamectl set-hostname db1.theitroad.local --static

# DB2
sudo hostnamectl set-hostname db2.theitroad.local --static

# DB3
sudo hostnamectl set-hostname db3.theitroad.local --static

如果我们有DNS服务器，请为所有计算机配置A记录DNS条目。对于基本设置，请在每台计算机上编辑/etc/hosts文件，并填充所需的DNS整体。

sudo tee -a /etc/hosts<<EOF
10.0.0.2 db1.theitroad.local db1
10.0.0.3 db2.theitroad.local db2
10.0.0.4 db3.theitroad.local db3
EOF

尝试从一台服务器向另一台服务器ping DNS名称。

# DB1
$ping -c 1 db2
$ping -c 1 db3

在所有节点上安装MariaDB

Debian 10仓库上可用的MariaDB版本是10.3. 如果要使用MariaDB 10.4，请从MariaDB存储库安装它。

sudo apt update
sudo apt -y install mariadb-server mariadb-client

通过运行初始配置脚本来保护每个数据库服务器。

$sudo 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): 
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] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

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用户身份连接到MariaDB进行测试。

# Without password

$mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

# With Authentication

$mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 59
Server version: 10.3.15-MariaDB-1 Debian 10

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

配置MariaDB Galera群集

默认情况下，MariaDB服务绑定到127.0.0.1 IP地址。注释掉配置文件/etc/mysql/mariadb.conf.d/50-server.cnf上的绑定行。

$sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
#bind-address            = 127.0.0.1

配置第一个集群节点DB1

编辑MariaDB配置文件，并将以下内容添加到文件末尾。

$sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="db1"

初始化Galera集群：

sudo galera_new_cluster
sudo systemctl restart mariadb

配置其他数据库节点

配置DB2

$sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Specify cluster nodes
wsrep_cluster_address="gcomm://db1,db2,db3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="db2"

重新启动mariadb：

sudo systemctl restart mariadb

配置DB3

$sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

[galera]
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Specify cluster nodes
wsrep_cluster_address="gcomm://db1,db2,db3"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
wsrep_cluster_name="galera_cluster"
wsrep_node_address="db3"

重新启动MariaDB

systemctl restart mariadb

确认MariaDB Galera群集设置

以root用户从集群中的节点登录到MySQL控制台。

$mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.15-MariaDB-1 Debian 10

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

确认群集设置是否正确。

$MariaDB [(none)]> show status like 'wsrep_%'; 
+------------------------------+--------------------------------------+
| Variable_name                | Value                                |
+------------------------------+--------------------------------------+
| wsrep_apply_oooe             | 0.000000                             |
| wsrep_apply_oool             | 0.000000                             |
| 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        | 3                                    |
| wsrep_cluster_size           | 3                                    |
| wsrep_cluster_state_uuid     | 0f6dbe29-bec4-11e9-a243-eb8c0dc70c76 |
| 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/0/0/0/0                            |
| wsrep_evs_state              | OPERATIONAL                          |
| wsrep_flow_control_paused    | 0.000000                             |
| wsrep_flow_control_paused_ns | 0                                    |
| wsrep_flow_control_recv      | 0                                    |
| wsrep_flow_control_sent      | 0                                    |
| wsrep_gcomm_uuid             | 0f6d51e5-bec4-11e9-bd50-52974fa5f2b4 |
| wsrep_incoming_addresses     | db1:3306,db2:3306,db3:3306           |
| wsrep_last_committed         | 0                                    |
| 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.100000                             |
| wsrep_local_recv_queue_max   | 2                                    |
| 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       | 0f6dbe29-bec4-11e9-a243-eb8c0dc70c76 |
| wsrep_open_connections       | 0                                    |
| wsrep_open_transactions      | 0                                    |
| wsrep_protocol_version       | 9                                    |
| wsrep_provider_name          | Galera                               |
| wsrep_provider_vendor        | Codership Oy <theitroad@localhost>    |
| wsrep_provider_version       | 3.25(rddf9876)                       |
| wsrep_ready                  | ON                                   |
| wsrep_received               | 10                                   |
| wsrep_received_bytes         | 790                                  |
| 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_thread_count           | 2                                    |
+------------------------------+--------------------------------------+
61 rows in set (0.002 sec)

我们可以确认群集大小为3. 群集中有三个节点。

wsrep_cluster_size    3

让我们在db1上创建一个测试数据库，并确认其他节点上的状态。

theitroad@localhost:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.15-MariaDB-1 Debian 10

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE test_db;
Query OK, 1 row affected (0.012 sec)

theitroad@localhost:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.3.15-MariaDB-1 Debian 10

Copyright (c) 2000, 2016, 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 |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.002 sec)

theitroad@localhost:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 10.3.15-MariaDB-1 Debian 10

Copyright (c) 2000, 2016, 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 |
| mysql              |
| performance_schema |
| test_db            |
+--------------------+
4 rows in set (0.001 sec)

检查Galera Cluster命令以监视集群。

使用HAProxy配置Galera群集负载平衡

配置HAproxy以确保数据库服务始终可用。这样，我们可以轻松隔离节点以进行维护。