如何在Ubuntu 18.04伺服器上使用MySQL配置Galera群集

介紹 (Introduction)

Clustering adds high availability to your database by distributing changes to different servers. In the event that one of the instances fails, others are quickly available to continue serving.

群集通過将更改分發到不同的伺服器來增加資料庫的高可用性。 萬一其中一個執行個體發生故障，其他執行個體很快就可以繼續使用。

Clusters come in two general configurations, active-passive and active-active. In active-passive clusters, all writes are done on a single active server and then copied to one or more passive servers that are poised to take over only in the event of an active server failure. Some active-passive clusters also allow

SELECT

operations on passive nodes. In an active-active cluster, every node is read-write and a change made to one is replicated to all.

群集有兩種正常配置， 主動-被動和主動-主動 。 在主動-被動群集中，所有寫操作均在一台主動伺服器上完成，然後複制到一個或多個被動伺服器上，這些伺服器準備僅在主動伺服器發生故障時接管。 一些主動-被動群集還允許在被動節點上執行

SELECT

操作。 在雙活群集中，每個節點都是可讀寫的，并且對一個節點所做的更改将被複制到所有節點。

MySQL is an open source relational database management system that is a popular choice for SQL databases. Galera is a database clustering solution that enables you to set up multi-master clusters using synchronous replication. Galera automatically handles keeping the data on different nodes in sync while allowing you to send read and write queries to any of the nodes in the cluster. You can learn more about Galera at the official documentation page.

MySQL是一種開放源代碼關系資料庫管理系統，是SQL資料庫的流行選擇。 Galera是一種資料庫群集解決方案，使您可以使用同步複制來設定多主群集。 Galera自動處理使不同節點上的資料保持同步的過程，同時允許您向叢集中的任何節點發送讀寫查詢。 您可以在官方文檔頁面上了解有關Galera的更多資訊。

In this guide, you will configure an active-active MySQL Galera cluster. For demonstration purposes, you will configure and test three Ubuntu 18.04 Droplets that will act as nodes in the cluster. This amount of nodes is the smallest configurable cluster.

在本指南中，您将配置雙活MySQL Galera叢集。 出于示範目的，您将配置和測試三個将充當群集中節點的Ubuntu 18.04 Droplet 。 此數量的節點是最小的可配置群集。

先決條件 (Prerequisites)

To follow along, you will need a DigitalOcean account, in addition to the following:

要繼續進行下去 ，除了以下内容之外，您還需要一個DigitalOcean帳戶 ：

• • For setting up private networking on the three Droplets, follow our Private Networking Quickstart guide.

    要在三個Droplet上設定專用網絡，請遵循我們的專用網絡快速入門指南。

  • For assistance setting up a non-root user with

    sudo

    privileges, follow our Initial Server Setup with Ubuntu 18.04 tutorial.

    要獲得設定具有

    sudo

    特權的非root使用者的幫助，請遵循我們的Ubuntu 18.04初始伺服器設定教程。
  Three Ubuntu 18.04 Droplets with private networking enabled, each with a non-root user with

  sudo

  privileges.

  三個啟用了私有網絡的Ubuntu 18.04 Droplet，每個Droplet都具有一個具有

  sudo

  特權的非root使用者。

While the steps in this tutorial have been written for and tested against DigitalOcean Droplets, much of them will also be applicable to non-DigitalOcean servers with private networking enabled.

盡管本教程中的步驟是針對DigitalOcean Droplet編寫的，并已針對它們進行了測試，但其中許多步驟也将适用于啟用了專用網絡的非DigitalOcean伺服器。

第1步-将MySQL存儲庫添加到所有伺服器 (Step 1 — Adding the MySQL Repositories to All Servers)

In this step, you will add the relevant MySQL and Galera package repositories to each of your three servers so that you will be able to install the right version of MySQL and Galera used in this tutorial.

在此步驟中，您将向三個伺服器中的每一個添加相關MySQL和Galera軟體包存儲庫，以便能夠安裝本教程中使用的正确版本MySQL和Galera。

Note: Codership, the company behind Galera Cluster, maintains the Galera repository, but be aware that not all external repositories are reliable. Be sure to install only from trusted sources.

注意： Galera Cluster背後的公司Codership負責維護Galera存儲庫，但請注意，并非所有外部存儲庫都是可靠的。 確定僅從受信任的來源進行安裝。

In this tutorial, you will use MySQL version 5.7. You’ll start by adding the external Ubuntu repository maintained by the Galera project to all three of your servers.

在本教程中，您将使用MySQL 5.7版 。 首先，将Galera項目維護的外部Ubuntu存儲庫添加到所有三個伺服器中。

Once the repositories are updated on all three servers, you will be ready to install MySQL along with Galera.

一旦在所有三台伺服器上更新了存儲庫，您就可以與Galera一起安裝MySQL。

First, on all three of your servers, add the Galera repository key with the

apt-key

command, which the APT package manager will use to verify that the package is authentic:

首先，在您的所有三台伺服器上，使用

apt-key

指令添加Galera存儲庫密鑰，APT軟體包管理器将使用該指令來驗證軟體包是真實的：

• sudo apt-key adv --keyserver keyserver.ubuntu.com --recv BC19DDBA

  須藤apt-key adv --keyserver keyserver.ubuntu.com --recv BC19DDBA

After a few seconds, you will receive the following output:

幾秒鐘後，您将收到以下輸出：

Output
   Executing: /tmp/apt-key-gpghome.RG5cTZjQo0/gpg.1.sh --keyserver keyserver.ubuntu.com --recv BC19DDBA
gpg: key D669017EBC19DDBA: public key "Codership Oy <[email protected]>" imported
gpg: Total number processed: 1
gpg:               imported: 1
           

Once you have the trusted key in each server’s database, you can add the repositories. To do so, create a new file called

galera.list

within the

/etc/apt/sources.list.d/

directory on each server:

在每個伺服器的資料庫中擁有受信任的密鑰後，就可以添加存儲庫。 為此，請在每台伺服器的

/etc/apt/sources.list.d/

目錄中建立一個名為

galera.list

的新檔案：

• sudo nano /etc/apt/sources.list.d/galera.list

  須藤nano /etc/apt/sources.list.d/galera.list

In the text editor, add the following lines, which will make the appropriate repositories available to the APT package manager:

在文本編輯器中，添加以下行，這将使APT包管理器可以使用适當的存儲庫：

/etc/apt/sources.list.d/galera.list /etc/apt/sources.list.d/galera.list

deb http://releases.galeracluster.com/mysql-wsrep-5.7/ubuntu bionic main
deb http://releases.galeracluster.com/galera-3/ubuntu bionic main
           

Save and close the files on each server (press

CTRL + X

,

Y

, then

ENTER

).

儲存并關閉每個伺服器上的檔案(按

CTRL + X

，

Y

，然後按

ENTER

)。

The Codership repositories are now available to all three of your servers. However, it’s important that you instruct

apt

to prefer Codership’s repositories over others to ensure that it installs the patched versions of the software needed to create a Galera cluster. To do this, create another new file called

galera.pref

within the

/etc/apt/preferences.d/

directory of each server:

現在，所有三個伺服器都可以使用Codership存儲庫。 但是，重要的是，您必須訓示

apt

使用Codership的存儲庫，而不是其他存儲庫，以確定它安裝了建立Galera叢集所需的軟體的修補版本。 為此，請在每個伺服器的

/etc/apt/preferences.d/

目錄中建立另一個名為

galera.pref

新檔案：

• sudo nano /etc/apt/preferences.d/galera.pref

  須藤納米/etc/apt/preferences.d/galera.pref

Add the following lines to the text editor:

将以下行添加到文本編輯器：

/etc/apt/preferences.d/galera.pref /etc/apt/preferences.d/galera.pref

# Prefer Codership repository
Package: *
Pin: origin releases.galeracluster.com
Pin-Priority: 1001
           

Save and close that file, then run the following command on each server in order to include package manifests from the new repositories:

儲存并關閉該檔案，然後在每台伺服器上運作以下指令，以包括來自新存儲庫的軟體包清單：

• sudo apt update

  sudo apt更新

Now that you have successfully added the package repository on all three of your servers, you’re ready to install MySQL in the next section.

現在，您已經在所有三台伺服器上成功添加了軟體包存儲庫，接下來就可以安裝MySQL了。

第2步-在所有伺服器上安裝MySQL (Step 2 — Installing MySQL on All Servers)

In this step, you will install the MySQL package on your three servers.

在此步驟中，您将在三台伺服器上安裝MySQL軟體包。

Run the following command on all three servers to install a version of MySQL patched to work with Galera, as well as the Galera package.

在所有三台伺服器上運作以下指令，以安裝經修補以與Galera一起使用MySQL版本以及Galera軟體包。

• sudo apt install galera-3 mysql-wsrep-5.7

  sudo apt安裝galera-3 mysql-wsrep-5.7

You will be asked to confirm whether you would like to proceed with the installation. Enter

Y

to continue with the installation. During the installation, you will also be asked to set a password for the MySQL administrative user. Set a strong password and press

ENTER

to continue.

系統将要求您确認是否要繼續安裝。 輸入

Y

繼續安裝。 在安裝過程中，還将要求您為MySQL管理使用者設定密碼。 設定一個強密碼，然後按

ENTER

繼續。

Once MySQL is installed, you will disable the default AppArmor profile to ensure that Galera functions properly, as per the official Galera documentation. AppArmor is a kernel module for Linux that provides access control functionality for services through security profiles.

安裝MySQL後，您将根據官方Galera文檔禁用預設的AppArmor配置檔案，以確定Galera正常運作。 AppArmor是Linux的核心子產品，通過安全配置檔案為服務提供通路控制功能。

Disable AppArmor by executing the following on each server:

通過在每個伺服器上執行以下操作來禁用AppArmor：

• sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

  須藤ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

This command adds a symbolic link of the MySQL profile to the

disable

directory, which disables the profile on boot.

此指令将MySQL配置檔案的符号連結添加到

disable

目錄，進而在啟動時禁用該配置檔案。

Then, run the following command to remove the MySQL definition that has already been loaded in the kernel.

然後，運作以下指令以删除核心中已加載MySQL定義。

• sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

  sudo apparmor_parser -R /etc/apparmor.d/usr.sbin.mysqld

Once you have installed MySQL and disabled the AppArmor profile on your first server, repeat these steps for your other two servers.

在安裝MySQL并在第一台伺服器上禁用AppArmor配置檔案後，對其他兩台伺服器重複這些步驟。

Now that you have installed MySQL successfully on each of the three servers, you can proceed to the configuration step in the next section.

現在，您已經在三台伺服器上成功安裝了MySQL，接下來可以進行下一節的配置步驟。

步驟3 —配置第一個節點 (Step 3 — Configuring the First Node)

In this step you will configure your first node. Each node in the cluster needs to have a nearly identical configuration. Because of this, you will do all of the configuration on your first machine, and then copy it to the other nodes.

在此步驟中，您将配置第一個節點。 叢集中的每個節點都需要具有幾乎相同的配置。 是以，您将在第一台計算機上進行所有配置，然後将其複制到其他節點。

By default, MySQL is configured to check the

/etc/mysql/conf.d

directory to get additional configuration settings from files ending in

.cnf

. On your first server, create a file in this directory with all of your cluster-specific directives:

預設情況下，MySQL配置為檢查

/etc/mysql/conf.d

目錄，以從以

.cnf

結尾的檔案中擷取其他配置設定。 在您的第一台伺服器上，使用所有特定于叢集的指令在此目錄中建立一個檔案：

• sudo nano /etc/mysql/conf.d/galera.cnf

  須藤nano /etc/mysql/conf.d/galera.cnf

Add the following configuration into the file. The configuration specifies different cluster options, details about the current server and the other servers in the cluster, and replication-related settings. Note that the IP addresses in the configuration are the private addresses of your respective servers; replace the highlighted lines with the appropriate IP addresses.

将以下配置添加到檔案中。 該配置指定不同的叢集選項，有關叢集中目前伺服器和其他伺服器的詳細資訊以及與複制相關的設定。 請注意，配置中的IP位址是您各自伺服器的專用位址。 用适當的IP位址替換突出顯示的行。

/etc/mysql/conf.d/galera.cnf /etc/mysql/conf.d/galera.cnf

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://First_Node_IP,Second_Node_IP,Third_Node_IP"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="This_Node_IP"
wsrep_node_name="This_Node_Name"
           

• The first section modifies or re-asserts MySQL settings that will allow the cluster to function correctly. For example, Galera won’t work with MyISAM or similar non-transactional storage engines, and

  mysqld

  must not be bound to the IP address for

  localhost

  . You can learn about the settings in more detail on the Galera Cluster system configuration page.

  第一部分修改或重新聲明将允許群集正常運作MySQL設定。 例如，Galera不能與MyISAM或類似的非事務性存儲引擎一起使用，并且

  mysqld

  不得綁定到

  localhost

  的IP位址。 您可以在Galera Cluster 系統配置頁面上詳細了解設定。

• The “Galera Provider Configuration” section configures the MySQL components that provide a WriteSet replication API. This means Galera in your case, since Galera is a wsrep (WriteSet Replication) provider. You specify the general parameters to configure the initial replication environment. This doesn’t require any customization, but you can learn more about Galera configuration options in the documentation.

  “ Galera Provider Configuration”部配置設定置了提供WriteSet複制APIMySQL元件。 在您的情況下，這意味着Galera，因為Galera是wsrep (WriteSet複制)提供程式。 您可以指定正常參數來配置初始複制環境。 這不需要任何自定義，但是您可以在文檔中了解有關Galera配置選項的更多資訊。

• The “Galera Cluster Configuration” section defines the cluster, identifying the cluster members by IP address or resolvable domain name and creating a name for the cluster to ensure that members join the correct group. You can change the

  wsrep_cluster_name

  to something more meaningful than

  test_cluster

  or leave it as-is, but you must update

  wsrep_cluster_address

  with the private IP addresses of your three servers.

  “ Galera群集配置”部分定義群集，通過IP位址或可解析的域名辨別群內建員，并為群集建立名稱以確定成員加入正确的組。 您可以将

  wsrep_cluster_name

  更改為比

  test_cluster

  更有意義的

  test_cluster

  或将其保留

  test_cluster

  ，但必須使用三個伺服器的專用IP位址更新

  wsrep_cluster_address

  。
• The

  Galera Synchronization Configuration

  section defines how the cluster will communicate and synchronize data between members. This is used only for the state transfer that happens when a node comes online. For your initial setup, you are using

  rsync

  , because it’s commonly available and does what you’ll need for now.

  Galera Synchronization Configuration

  部分定義了群集如何在成員之間通信和同步資料。 這僅用于節點聯機時發生的狀态轉移。 對于您的初始設定，您正在使用

  rsync

  ，因為它通常可用，并且可以滿足您目前的需求。
• The

  Galera Node Configuration

  section clarifies the IP address and the name of the current server. This is helpful when trying to diagnose problems in logs and for referencing each server in multiple ways. The

  wsrep_node_address

  must match the address of the machine you’re on, but you can choose any name you want in order to help you identify the node in log files.

  Galera Node Configuration

  部分闡明了目前伺服器的IP位址和名稱。 在嘗試診斷日志中的問題以及以多種方式引用每個伺服器時，這很有用。

  wsrep_node_address

  必須與您所在計算機的位址比對，但是您可以選擇所需的任何名稱，以幫助您在日志檔案中辨別該節點。

When you are satisfied with your cluster configuration file, copy the contents into your clipboard, then save and close the file.

對叢集配置檔案滿意後，将内容複制到剪貼闆中，然後儲存并關閉檔案。

Now that you have configured your first node successfully, you can move on to configuring the remaining nodes in the next section.

現在您已經成功配置了第一個節點，接下來可以繼續配置其餘節點。

步驟4 —配置其餘節點 (Step 4 — Configuring the Remaining Nodes)

In this step, you will configure the remaining two nodes. On your second node, open the configuration file:

在此步驟中，您将配置其餘兩個節點。 在第二個節點上，打開配置檔案：

• sudo nano /etc/mysql/conf.d/galera.cnf

  須藤nano /etc/mysql/conf.d/galera.cnf

Paste in the configuration you copied from the first node, then update the

Galera Node Configuration

to use the IP address or resolvable domain name for the specific node you’re setting up. Finally, update its name, which you can set to whatever helps you identify the node in your log files:

粘貼您從第一個節點複制的配置，然後更新

Galera Node Configuration

以将IP位址或可解析的域名用于您要設定的特定節點。 最後，更新其名稱，您可以将其設定為可幫助您在日志檔案中辨別節點的任何名稱：

/etc/mysql/conf.d/galera.cnf /etc/mysql/conf.d/galera.cnf

. . .
# Galera Node Configuration
wsrep_node_address="This_Node_IP"
wsrep_node_name="This_Node_Name"
. . .
           

Save and exit the file.

儲存并退出檔案。

Once you have completed these steps, repeat them on the third node.

完成這些步驟後，請在第三個節點上重複這些步驟。

You’re almost ready to bring up the cluster, but before you do, make sure that the appropriate ports are open in your firewall.

您幾乎已經準備好啟動群集，但是在執行此操作之前，請確定在防火牆中打開了适當的端口。

步驟5 —在每台伺服器上打開防火牆 (Step 5 — Opening the Firewall on Every Server)

In this step, you will configure your firewall so that the ports required for inter-node communication are open. On every server, check the status of the firewall by running:

在此步驟中，您将配置防火牆，以便打開節點間通信所需的端口。 在每台伺服器上，通過運作以下指令檢查防火牆的狀态：

• sudo ufw status

  sudo ufw狀态

In this case, only SSH is allowed through:

在這種情況下，僅允許通過以下方式使用SSH：

Output
   Status: active

To                         Action      From
--                         ------      ----
OpenSSH                    ALLOW       Anywhere
OpenSSH (v6)               ALLOW       Anywhere (v6)
           

Since only SSH traffic is permitted in this case, you’ll need to add rules for MySQL and Galera traffic. If you tried to start the cluster, it would fail because of these firewall rules.

由于在這種情況下僅允許SSH通信，是以您需要為MySQL和Galera通信添加規則。 如果您嘗試啟動群集，則由于這些防火牆規則，它将失敗。

Galera can make use of four ports:

Galera可以使用四個端口：

• 3306

  For MySQL client connections and State Snapshot Transfer that use the

  mysqldump

  method.

  3306

  對于使用

  mysqldump

  方法MySQL用戶端連接配接和狀态快照傳輸。

• 4567

  For Galera Cluster replication traffic. Multicast replication uses both UDP transport and TCP on this port.

  4567

  對于Galera Cluster複制通信。 多點傳播複制在此端口上同時使用UDP傳輸和TCP。

• 4568

  For Incremental State Transfer.

  4568

  用于增量狀态轉移。

• 4444

  For all other State Snapshot Transfer.

  4444

  對于所有其他狀态快照傳輸。

In this example, you’ll open all four ports while you do your setup. Once you’ve confirmed that replication is working, you’d want to close any ports you’re not actually using and restrict traffic to just servers in the cluster.

在此示例中，您将在設定時打開所有四個端口。 确認複制正常後，您将要關閉所有未使用的端口，并将流量限制為僅在群集中的伺服器。

Open the ports with the following commands:

使用以下指令打開端口：

• sudo ufw allow 3306,4567,4568,4444/tcp

  sudo ufw允許3306,4567,4568,4444 / tcp

• sudo ufw allow 4567/udp

  sudo ufw允許4567 / udp

Note: Depending on what else is running on your servers, you might want to restrict access right away. The UFW Essentials: Common Firewall Rules and Commands guide can help with this.

注意：根據伺服器上正在運作的内容，您可能希望立即限制通路。 UFW Essentials：“通用防火牆規則和指令”指南可以提供幫助。

After you have configured your firewall on the first node, create the same firewall settings on the second and third node.

在第一個節點上配置防火牆之後，在第二個和第三個節點上建立相同的防火牆設定。

Now that you have configured the firewalls successfully, you’re ready to start the cluster in the next step.

既然您已經成功配置了防火牆，那麼就可以在下一步中啟動叢集了。

步驟6 —啟動叢集 (Step 6 — Starting the Cluster)

In this step, you will start your MySQL Galera cluster. But first, you will enable the MySQL

systemd

service, so that MySQL will start automatically whenever the server is rebooted.

在此步驟中，您将啟動MySQL Galera叢集。 但是首先，您将啟用MySQL

systemd

服務，以便每當伺服器重新啟動時MySQL就會自動啟動。

使MySQL能夠在所有三台伺服器上啟動時啟動 (Enable MySQL to Start on Boot on All Three Servers)

Use the following command on all three servers to enable the MySQL

systemd

service:

在所有三台伺服器上使用以下指令來啟用MySQL

systemd

服務：

• sudo systemctl enable mysql

  sudo systemctl啟用mysql

You will see the following output, which shows that the service has been linked successfully to the startup services list:

您将看到以下輸出，顯示該服務已成功連結到啟動服務清單：

Output
   Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
           

Now that you’ve enabled

mysql

to start on boot on all of the servers, you’re ready to proceed to bring the cluster up.

既然已經啟用了

mysql

以在所有伺服器上啟動啟動，那麼您就可以繼續啟動群集了。

調出第一個節點 (Bring Up the First Node)

To bring up the first node, you’ll need to use a special startup script. The way you’ve configured your cluster, each node that comes online will try to connect to at least one other node specified in its

galera.cnf

file to get its initial state. Without using the

mysqld_bootstrap

script that allows

systemd

to pass the

--wsrep-new-cluster

parameter, a normal

systemctl start mysql

would fail because there are no nodes running for the first node to connect with.

要啟動第一個節點，您将需要使用特殊的啟動腳本。 按照配置群集的方式，每個聯機的節點都将嘗試連接配接到其

galera.cnf

檔案中指定的至少另一個節點，以擷取其初始狀态。 如果不使用允許

systemd

傳遞

--wsrep-new-cluster

參數的

mysqld_bootstrap

腳本，則正常的

systemctl start mysql

将失敗，因為第一個節點沒有與之連接配接的節點在運作。

Run the following on your first server:

在第一台伺服器上運作以下指令：

• sudo mysqld_bootstrap

  須藤mysqld_bootstrap

This command will not display any output on successful execution. When this script succeeds, the node is registered as part of the cluster, and you can see it with the following command:

成功執行後，此指令将不顯示任何輸出。 成功執行此腳本後，該節點将注冊為叢集的一部分，您可以使用以下指令檢視它：

• mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

  mysql -u root -p -e“顯示狀态類似'wsrep_cluster_size'”

After entering your password, you will see the following output, indicating that there is one node in the cluster:

輸入密碼後，您将看到以下輸出，訓示叢集中有一個節點：

Output
   +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 1     |
+--------------------+-------+

           

On the remaining nodes, you can start

mysql

normally. They will search for any member of the cluster list that is online, and when they find one, they will join the cluster.

在其餘節點上，您可以正常啟動

mysql

。 他們将搜尋群集清單中任何聯機的成員，找到後，他們将加入群集。

調出第二個節點 (Bring Up the Second Node)

Now you can bring up the second node. Start

mysql

:

現在可以啟動第二個節點。 啟動

mysql

：

• sudo systemctl start mysql

  sudo systemctl啟動mysql

No output will be displayed on successful execution. You will see your cluster size increase as each node comes online:

成功執行後将不顯示任何輸出。 随着每個節點聯機，您将看到群集大小增加：

• mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

  mysql -u root -p -e“顯示狀态類似'wsrep_cluster_size'”

You will see the following output indicating that the second node has joined the cluster and that there are two nodes in total.

您将看到以下輸出，訓示第二個節點已加入叢集，并且總共有兩個節點。

Output
   +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 2     |
+--------------------+-------+

           

調高第三個節點 (Bring Up the Third Node)

It’s now time to bring up the third node. Start

mysql

:

現在是時候提出第三個節點。 啟動

mysql

：

• sudo systemctl start mysql

  sudo systemctl啟動mysql

Run the following command to find the cluster size:

運作以下指令以查找叢集大小：

• mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

  mysql -u root -p -e“顯示狀态類似'wsrep_cluster_size'”

You will see the following output, which indicates that the third node has joined the cluster and that the total number of nodes in the cluster is three.

您将看到以下輸出，該輸出訓示第三個節點已加入叢集，并且叢集中的節點總數為三個。

Output
   +--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+
           

At this point, the entire cluster is online and communicating successfully. Next, you can ensure the working setup by testing replication in the following section.

此時，整個群集都已聯機并成功通信。 接下來，您可以通過在下一節中測試複制來確定工作設定。

第7步-測試複制 (Step 7 — Testing Replication)

You’ve gone through the steps up to this point so that your cluster can perform replication from any node to any other node, known as active-active replication. In this step, you will test and see if the replication is working as expected.

至此，您已經完成了所有步驟，以便您的叢集可以執行從任何節點到任何其他節點的複制，這稱為主動-主動複制。 在此步驟中，您将測試并檢視複制是否按預期工作。

寫入第一個節點 (Write to the First Node)

You’ll start by making database changes on your first node. The following commands will create a database called

playground

and a table inside of this database called

equipment

.

您将從在第一個節點上進行資料庫更改開始。 以下指令将建立一個名為“

playground

的資料庫，并在該資料庫内部建立一個名為

equipment

的表。

• mysql -u root -p -e 'CREATE DATABASE playground;

  mysql -u root -p -e'建立資料庫遊樂場;

• CREATE TABLE playground.equipment ( id INT NOT NULL AUTO_INCREMENT, type VARCHAR(50), quant INT, color VARCHAR(25), PRIMARY KEY(id));

  建立表park.equipment(id INT NOT NULL AUTO_INCREMENT，類型VARCHAR(50)，quant INT，顔色VARCHAR(25)，PRIMARY KEY(id));

• INSERT INTO playground.equipment (type, quant, color) VALUES ("slide", 2, "blue");'

  将INSERT插入park.equipment(類型，數量，顔色)值(“ slide”，2，“ blue”)；'

In the previous command, the

CREATE DATABASE

statement creates a database named

playground

. The

CREATE

statement creates a table named

equipment

inside the

playground

database having an auto-incrementing identifier column called

id

and other columns. The

type

column,

quant

column, and

color

column are defined to store the type, quantity, and color of the equipment respectively. The

INSERT

statement inserts an entry of type

slide

, quantity

2

and color

blue

.

在上一個指令中，

CREATE DATABASE

語句建立一個名為

playground

的資料庫。

CREATE

語句在

playground

資料庫内建立一個名為

equipment

的表，該表具有一個稱為

id

的自動遞增辨別符列和其他列。 的

type

欄，

quant

列和

color

柱被定義為分别存儲類型，數量，和裝置的顔色。

INSERT

語句将插入一個類型為

slide

，數量

2

和

blue

的條目。

You now have one value in your table.

現在，表中有一個值。

在第二個節點上讀寫 (Read and Write on the Second Node)

Next, look at the second node to verify that replication is working:

接下來，檢視第二個節點以驗證複制是否有效：

• mysql -u root -p -e 'SELECT * FROM playground.equipment;'

  mysql -u root -p -e'選擇*來自操場。裝置;'

The data you entered on the first node will be visible here on the second, proving that replication is working:

您在第一個節點上輸入的資料将在第二個節點上可見，這證明複制正常進行：

Output
   +----+-------+-------+-------+
| id | type  | quant | color |
+----+-------+-------+-------+
|  1 | slide |     2 | blue  |
+----+-------+-------+-------+
           

From this same node, write data to the cluster:

從同一節點将資料寫入叢集：

• mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("swing", 10, "yellow");'

  mysql -u root -p -e'在操場上插入裝置。(類型，數量，顔色)值(“ swing”，10，“ yellow”)；'

在第三個節點上讀寫 (Read and Write on the Third Node)

From the third node, you can read all of this data by querying the table again:

從第三個節點，您可以通過再次查詢表來讀取所有這些資料：

• mysql -u root -p -e 'SELECT * FROM playground.equipment;'

  mysql -u root -p -e'選擇*來自遊樂場。裝置;'

You will see the following output showing the two rows:

您将看到以下輸出，顯示兩行：

Output
      +----+-------+-------+--------+
   | id | type  | quant | color  |
   +----+-------+-------+--------+
   |  1 | slide |     2 | blue   |
   |  2 | swing |    10 | yellow |
   +----+-------+-------+--------+
           

Again, you can add another value from this node:

同樣，您可以從該節點添加另一個值：

• mysql -u root -p -e 'INSERT INTO playground.equipment (type, quant, color) VALUES ("seesaw", 3, "green");'

  mysql -u root -p -e'在操場上插入裝置(類型，數量，顔色)值(“跷跷闆”，3，“綠色”)；'

在第一個節點上閱讀 (Read on the First Node)

Back on the first node, you can verify that your data is available everywhere:

回到第一個節點，您可以驗證資料在任何地方都可用：

• mysql -u root -p -e 'SELECT * FROM playground.equipment;'

  mysql -u root -p -e'選擇*來自遊樂場。裝置;'

You will see the following output, which indicates that the rows are available on the first node.

您将看到以下輸出，訓示行在第一個節點上可用。

Output
      +----+--------+-------+--------+
   | id | type   | quant | color  |
   +----+--------+-------+--------+
   |  1 | slide  |     2 | blue   |
   |  2 | swing  |    10 | yellow |
   |  3 | seesaw |     3 | green  |
   +----+--------+-------+--------+
           

You’ve now verified successfully that you can write to all of the nodes and that replication is being performed properly.

現在，您已經成功驗證可以寫入所有節點，并且複制正在正确執行。

結論 (Conclusion)

At this point, you have a working three-node Galera test cluster configured. If you plan on using a Galera cluster in a production situation, it’s recommended that you begin with no fewer than five nodes.

至此，您已經配置了一個有效的三節點Galera測試叢集。 如果計劃在生産環境中使用Galera群集，則建議您至少從五個節點開始。

Before production use, you may want to take a look at some of the other state snapshot transfer (sst) agents like xtrabackup, which allows you to set up new nodes quickly and without large interruptions to your active nodes. This does not affect the actual replication, but is a concern when nodes are being initialized.

在生産環境投入使用之前，您可能需要看一下其他一些狀态快照傳輸(sst)代理，例如xtrabackup ，它使您可以快速設定新節點，而不會對活動節點造成大的幹擾。 這不會影響實際的複制，但是在初始化節點時會引起關注。

You might also be interested in other clustering solutions for MySQL, in which case you can check out our How To Create a Multi-Node MySQL Cluster on Ubuntu 18.04 tutorial. If you are looking to try out a managed database solution, see our DigitalOcean Managed Databases documentation.

您可能還對MySQL的其他群集解決方案感興趣，在這種情況下，可以檢視我們的《 如何在Ubuntu 18.04上建立多節點MySQL群集》教程。 如果您想嘗試使用托管資料庫解決方案，請參閱我們的DigitalOcean托管資料庫文檔 。

翻譯自: https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mysql-on-ubuntu-18-04-servers