MysqlGalera集群版的安裝部署方法

下文內(nèi)容主要給大家?guī)?lái)MySQL Galera 集群版的安裝部署方法，所講到的知識(shí)，與書(shū)籍不同，都是創(chuàng)新互聯(lián)專(zhuān)業(yè)技術(shù)人員在與用戶接觸過(guò)程中，總結(jié)出來(lái)的，具有一定的經(jīng)驗(yàn)分享價(jià)值，希望給廣大讀者帶來(lái)幫助。

長(zhǎng)寧網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、響應(yīng)式網(wǎng)站等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)公司從2013年開(kāi)始到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專(zhuān)注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)公司。

Galera 是一個(gè)Mysql(Mariadb，Percona)同步多主集群軟件，本文主要講解Galera cluster安裝，MySQL Galera集群版的mysql是經(jīng)過(guò)codeship打了wsrep補(bǔ)丁的，不是普通的mysql版本。安裝時(shí)采用輕量級(jí)pssh來(lái)進(jìn)行批量安裝。
Mysql Galera 集群版的安裝部署方法

概況如下：

機(jī)器IP 結(jié)點(diǎn)名

192.168.1.42 Es-Search-A
192.168.1.43 Es-Search-B
192.168.1.44 Es-Search-C

OS版本：CentOS 7
mysql版本：mysql-5.6.43

Galera cluster安裝包：

galera-3-25.3.26-2.el7.x86_64.rpm
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64.rpm
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64.rpm

Galera cluster下載url:
http://galeracluster.com/downloads/

注：這里我下載的都是打過(guò)wsrep補(bǔ)丁的mysql安裝包，不用獨(dú)立安裝mysql數(shù)據(jù)庫(kù)，直接安裝這些包之后數(shù)據(jù)庫(kù)就安裝好了。

一、準(zhǔn)備工作
在每臺(tái)機(jī)器/下創(chuàng)建/data目錄，用于mysql數(shù)據(jù)文件目錄。
[elasticsearch@Es-Search-A ~]$ cat hosts.txt
192.168.1.42
192.168.1.43
192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo mkdir /data/"
[1] 14:37:15 [SUCCESS] 192.168.1.42
[2] 14:37:15 [SUCCESS] 192.168.1.44
[3] 14:37:15 [SUCCESS] 192.168.1.43

將mysql源碼文件復(fù)制到每臺(tái)機(jī)器

安裝依賴包
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo yum install gcc gcc-c++ perl-devel bison bison-devel ncurses-devel cmake autoconf -y"
[1] 14:51:37 [SUCCESS] 192.168.1.42
[2] 14:51:39 [SUCCESS] 192.168.1.44
[3] 14:51:41 [SUCCESS] 192.168.1.43

創(chuàng)建用戶：

[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo groupadd mysql"
[1] 14:58:03 [SUCCESS] 192.168.1.43
[2] 14:58:03 [SUCCESS] 192.168.1.44
[3] 14:58:03 [SUCCESS] 192.168.1.42
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo useradd -g mysql mysql -d /home/mysql -s /sbin/nologin"
[1] 14:58:16 [SUCCESS] 192.168.1.42
[2] 14:58:16 [SUCCESS] 192.168.1.43
[3] 14:58:16 [SUCCESS] 192.168.1.44

[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo mkdir /data/mysql/data -p"
[1] 14:59:00 [SUCCESS] 192.168.1.42
[2] 14:59:00 [SUCCESS] 192.168.1.43
[3] 14:59:00 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt "sudo chown -R mysql:mysql /data/mysql/data/"
[1] 15:01:13 [SUCCESS] 192.168.1.42
[2] 15:01:13 [SUCCESS] 192.168.1.43
[3] 15:01:13 [SUCCESS] 192.168.1.44

將安裝包復(fù)制到其他兩臺(tái)機(jī)器上：
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 mysql-wsrep- /home/elasticsearch/
[1] 10:07:35 [SUCCESS] 192.168.1.42
[2] 10:07:38 [SUCCESS] 192.168.1.44
[3] 10:07:38 [SUCCESS] 192.168.1.43
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 galera-3-25.3.26-2.el7.x86_64.rpm /home/elasticsearch/
[1] 10:08:01 [SUCCESS] 192.168.1.42
[2] 10:08:02 [SUCCESS] 192.168.1.43
[3] 10:08:03 [SUCCESS] 192.168.1.44
二、安裝
批量安裝：
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 "sudo rpm -ivh mysql-wsrep-"
[1] 10:09:08 [SUCCESS] 192.168.1.42
[2] 10:09:09 [SUCCESS] 192.168.1.43
[3] 10:09:10 [SUCCESS] 192.168.1.44

[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P "sudo rpm -ivh galera*"
[1] 10:10:24 [SUCCESS] 192.168.1.44
[2] 10:10:25 [SUCCESS] 192.168.1.43
[3] 10:10:28 [SUCCESS] 192.168.1.42

檢查是否成功安裝：
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P "sudo rpm -qa| grep mysql-wsrep"
192.168.1.42: mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
[1] 15:28:04 [SUCCESS] 192.168.1.42
192.168.1.43: mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
[2] 15:28:04 [SUCCESS] 192.168.1.43
192.168.1.44: mysql-wsrep-server-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-devel-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-client-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-shared-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-test-5.6-5.6.43-25.25.el7.x86_64
mysql-wsrep-5.6-5.6.43-25.25.el7.x86_64
[3] 15:28:04 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P "sudo rpm -qa| grep galera"
192.168.1.42: galera-3-25.3.26-2.el7.x86_64
[1] 10:11:25 [SUCCESS] 192.168.1.42
192.168.1.43: galera-3-25.3.26-2.el7.x86_64
[2] 10:11:25 [SUCCESS] 192.168.1.43
192.168.1.44: galera-3-25.3.26-2.el7.x86_64
[3] 10:11:25 [SUCCESS] 192.168.1.44

安裝結(jié)束。

三、mysql配置文件

第一臺(tái)：
[elasticsearch@Es-Search-A ~]$ sudo vi /etc/my.cnf

#For advice on how to change settings please see
#http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]

#Remove leading # and set to the amount of RAM for the most important data
#cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
#innodb_buffer_pool_size = 128M

#Remove leading # to turn on a very important data integrity option: logging
#changes to the binary log between backups.
#log_bin

#Remove leading # to set options mainly useful for reporting servers.
#The server defaults are faster for transactions and fast SELECTs.
#Adjust sizes as needed, experiment to find the optimal values.
#join_buffer_size = 128M
#sort_buffer_size = 2M
#read_rnd_buffer_size = 2M
#datadir=/usr/local/mysql
#socket=/usr/local/mysql/mysql.sock

#Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0

#log-error=/data/mysql/log/error.log
#pid-file=/usr/local/mysql/mysqld.pid
!includedir /etc/my.cnf.d/

[elasticsearch@Es-Search-A ~]$ sudo vi /etc/my.cnf.d/wsrep.cnf

[mysqld]
datadir=/var/lib/mysql
#basedir=/usr/share/mysql
tmpdir=/tmp ###臨時(shí)目錄
socket=/var/lib/mysql/mysql.sock
#Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#Settings user and group are ignored when systemd is used.
#If you need to run mysqld under a different user or group,
#customize your systemd unit file for mariadb according to the
#instructions in http://fedoraproject.org/wiki/Systemd
character-set-server=utf8 #字符集utf-8
collation-server=utf8_general_ci
skip-name-resolve ##跳過(guò)主機(jī)名
user=mysql
port=3306 ##端口
binlog_rows_query_log_events=OFF ###這個(gè)選項(xiàng)應(yīng)該關(guān)掉，否則會(huì)產(chǎn)生內(nèi)部錯(cuò)誤
innodb_buffer_pool_size = 4096M
max_allowed_packet = 500M
max_connections = 600
log-error=/data/mysql/log/error.log
pid-file=/var/lib/mysql/mysql.pid

skip-grant-tables ##跳過(guò)授權(quán)表
binlog_format=ROW
log-bin=mysql-bin

#####################################################################################
character-set-server=utf8 #字符集utf-8
collation-server=utf8_general_ci
binlog_rows_query_log_events=OFF ###這個(gè)選項(xiàng)應(yīng)該關(guān)掉，否則會(huì)產(chǎn)生內(nèi)部錯(cuò)誤

wsrep_on=on
innodb_autoinc_lock_mode=2
default_storage_engine=innodb
wsrep_node_name = Es-Search-A
wsrep_node_address='192.168.1.42'
wsrep_provider = /usr/lib64/galera-3/libgalera_smm.so
wsrep_cluster_address="gcomm://"
wsrep_cluster_name='Galera_cluster'
wsrep_sst_method = rsync #xtrabackup
skip-grant-tables ##跳過(guò)授權(quán)表
wsrep_sst_auth=galera:galera
binlog_format=ROW
log-bin=mysql-bin
server-id=42
log-slave-updates=1
#######################################################################################

[mysqld_safe]
log-error=/data/mysql/data/error.log
#pid-file=/usr/local/mysql/mysql.pid

#include all files from the config directory

#!includedir /etc/my.cnf.d/

配置文件各項(xiàng)配置意義：

wsrep_provider：指定Galera庫(kù)的路徑
wsrep_cluster_name：Galera集群的名稱
wsrep_cluster_address：Galera集群中各節(jié)點(diǎn)地址。地址使用組通信協(xié)議gcomm://(group communication)
wsrep_node_name：本節(jié)點(diǎn)在Galera集群中的名稱
wsrep_node_address：本節(jié)點(diǎn)在Galera集群中的通信地址
wsrep_sst_method
：state_snapshot_transfer(SST)使用的傳輸方法，可用方法有mysqldump、rsync和xtrabackup，前兩者在傳輸時(shí)都需要對(duì)Donor加全局只讀鎖(
FLUSH TABLES WITH READ LOCK)，xtrabackup則不需要(它使用percona自己提供的backup
lock)。強(qiáng)烈建議采用xtrabackup
wsrep_sst_auth：在SST傳輸時(shí)需要用到的認(rèn)證憑據(jù)，格式為："用戶：密碼"
pxc_strict_mode：是否限制PXC啟用正在試用階段的功能，ENFORCING是默認(rèn)值，表示不啟用
binlog_format：二進(jìn)制日志的格式。Galera只支持row格式的二進(jìn)制日志
default_storage_engine：指定默認(rèn)存儲(chǔ)引擎。Galera的復(fù)制功能只支持InnoDB
innodb_autoinc_lock_mode：只能設(shè)置為2，設(shè)置為0或1時(shí)會(huì)無(wú)法正確處理死鎖問(wèn)題

將配置文件復(fù)制到其他機(jī)器
[elasticsearch@Es-Search-A ~]$ pscp.pssh -h hosts.txt -t 0 /etc/my.cnf /home/elasticsearch/
[1] 11:15:22 [SUCCESS] 192.168.1.42
[2] 11:15:22 [SUCCESS] 192.168.1.43
[3] 11:15:22 [SUCCESS] 192.168.1.44
[elasticsearch@Es-Search-A ~]$ pssh -h hosts.txt -t 0 -P "sudo cp my.cnf /etc/"
[1] 11:15:59 [SUCCESS] 192.168.1.42
[2] 11:15:59 [SUCCESS] 192.168.1.43
[3] 11:15:59 [SUCCESS] 192.168.1.44

初始化第一臺(tái)mysql
[elasticsearch@Es-Search-A ~]$ sudo mysqld --initialize --user=mysql
2019-06-27 15:10:11 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-27 15:10:11 0 [Note] mysqld (mysqld 5.6.43-log) starting as process 5321 ...

[elasticsearch@Es-Search-A ~]$ ll /data/mysql/data
total 241672
-rw-rw---- 1 mysql mysql 134219048 Jun 26 17:38 galera.cache
-rw-rw---- 1 mysql mysql 113 Jun 26 17:38 grastate.dat
-rw-rw---- 1 mysql mysql 12582912 Jun 26 17:38 ibdata1
-rw-rw---- 1 mysql mysql 50331648 Jun 26 17:38 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Jun 20 17:53 ib_logfile1
drwx------ 2 mysql mysql 6 Jun 20 18:01 mysql
-rw-rw---- 1 mysql mysql 0 Jun 20 17:53 mysql-bin.index
drwx------ 2 mysql mysql 6 Jun 20 18:01 test

啟動(dòng)第一臺(tái)mysql
sudo service mysql start --wsrep-new-cluster

或使用這種方式啟動(dòng)也可以
[elasticsearch@Es-Search-A ~]$ sudo mysqld --wsrep-new-cluster --user=mysql &
[1] 16007
[elasticsearch@Es-Search-A ~]$ 2019-06-20 17:11:56 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-06-20 17:11:56 0 [Note] mysqld (mysqld 5.6.43-log) starting as process 16008 ...

在另一個(gè)終端
[elasticsearch@Es-Search-A ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.43-log MySQL Community Server (GPL), wsrep_25.25

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

查看參數(shù)：
mysql> show status like 'wsrep_%';
+------------------------------+-----------------------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------------------+
| wsrep_local_state_uuid | 73aa66b8-933b-11e9-9578-9a7df8c24dcc |
| wsrep_protocol_version | 9 |
| wsrep_last_committed | 0 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 147 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 2 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.500000 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 1 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.000000 |
| wsrep_local_cached_downto | 18446744073709551615 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_cert_deps_distance | 0.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 0.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 0.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | 192.168.1.42:3306 |
| wsrep_cluster_weight | 1 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 3.204e-06/6.0466e-06/1.0783e-05/2.89701e-06/5 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | 73a64b76-933b-11e9-99cc-337e6bf5a0f1 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 73aa66b8-933b-11e9-9578-9a7df8c24dcc |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 0 |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 3.26(rff05089) |
| wsrep_ready | ON |
+------------------------------+-----------------------------------------------+
60 rows in set (0.00 sec)

修改密碼
mysql> update mysql.user set password=PASSWORD('123') where user='root';
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.17 sec)

創(chuàng)建復(fù)制用戶
mysql> grant all on . to 'galera'@'%' identified by 'galera';
Query OK, 0 rows affected (0.12 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.28 sec)

第一個(gè)節(jié)點(diǎn)啟動(dòng)成功，再配置啟動(dòng)其他節(jié)點(diǎn)。

##第二個(gè)節(jié)點(diǎn)配置：
server-id=43
wsrep_node_name = Es-Search-B
wsrep_node_address='192.168.1.43'
wsrep_cluster_address="gcomm://192.168.1.42,192.168.1.44"
與節(jié)點(diǎn)1區(qū)別就這三個(gè)參數(shù)

啟動(dòng)第二個(gè)節(jié)點(diǎn)：
[elasticsearch@Es-Search-B ~]$ sudo service mysql start
Starting MySQL........................ SUCCESS!

##第三個(gè)節(jié)點(diǎn)配置：
server-id=44
wsrep_node_name = Es-Search-C
wsrep_node_address='192.168.1.44'
wsrep_cluster_address="gcomm://192.168.1.42,192.168.1.43"

啟動(dòng)第三個(gè)節(jié)點(diǎn)：
[elasticsearch@Es-Search-C data]$ sudo service mysql start
Starting MySQL........................... SUCCESS!

可以看到cluster有3臺(tái)，嘗試在其中任一臺(tái)上創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)，則在另外節(jié)點(diǎn)可以看到也創(chuàng)建了同樣的庫(kù)。

mysql> create database mydb;
Query OK, 1 row affected (0.16 sec)

因?yàn)閯偛诺谝慌_(tái)機(jī)器用來(lái)創(chuàng)建集群，啟動(dòng)時(shí)跳過(guò)了授權(quán)表，然后連接到庫(kù)中修改了密碼，創(chuàng)建了用戶，之后需要注釋掉跳過(guò)權(quán)限表的參數(shù)。

安裝配置完結(jié)。這里僅是使用rpm包安裝，basedir與datadir不能很靈活定義，還是比較習(xí)慣源碼安裝。之后會(huì)再探索直接在mysql上打補(bǔ)丁，然后組成Galera cluster。

對(duì)于以上關(guān)于Mysql Galera 集群版的安裝部署方法，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們創(chuàng)新互聯(lián)的行業(yè)推新，如需獲取專(zhuān)業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)?lái)一定的知識(shí)更新。

標(biāo)題名稱：MysqlGalera集群版的安裝部署方法
轉(zhuǎn)載來(lái)源：http://muchs.cn/article8/joooop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)公司、Google、靜態(tài)網(wǎng)站、外貿(mào)建站、小程序開(kāi)發(fā)、響應(yīng)式網(wǎng)站

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容