本篇內容介紹了“5.7 Galera Cluster的安裝搭建及高可用測試”的有關(guān)知識，在實(shí)際案例的操作過(guò)程中，不少人都會(huì )遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學(xué)有所成！
---創(chuàng )建并配置my.cnf文件
# cat /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
!includedir /etc/my.cnf.d/
---創(chuàng )建并配置wsrep.cnf文件
# cat /etc/my.cnf.d/wsrep.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[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
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
#MySQL的ID，必須是唯一的，集群各個(gè)節點(diǎn)也不同
server-id=111
explicit_defaults_for_timestamp=true
basedir=/usr
##MySQL數據文件存儲路徑
datadir=/data/mysql/3306
socket=/data/mysql/3306/mysql.sock
pid_file=/data/mysql/mysqld.pid
port=3306
log_error=/data/mysql/mysql.err
##galera集群的名字，必須是統一的
wsrep_cluster_name='tangyun_cluster'
##wsrep提供者,必須配置(.so文件的路徑在哪，就配置成哪)
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
##wsrep節點(diǎn)的ID，必須是唯一的，集群各個(gè)節點(diǎn)也不同
wsrep_node_name = db01
##集群中的其他節點(diǎn)地址，可以使用主機名或IP
wsrep_cluster_address=gcomm://192.168.56.111,192.168.56.112,192.168.56.113
#本機節點(diǎn)地址，可以使用主機名或IP
wsrep_node_address='192.168.56.111'
#指定wsrep啟動(dòng)端口號
wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.56.111:4567"
#一個(gè)逗號分割的節點(diǎn)串作為狀態(tài)轉移源，比如wsrep_sst_donor=db01,db02,如果db01可用，用db02
,如果db02不可用，用db03，最后的逗號表明讓提供商自己選擇一個(gè)最優(yōu)的。
wsrep_sst_donor='db01,db02,db03'
##集群同步方式
wsrep_sst_method=rsync
##集群同步的用戶(hù)名密碼
wsrep_sst_auth=tangyun:tangyun
slow_query_log=on
[client]
default-character-set=utf8
socket=/data/mysql/3306/mysql.sock
[mysql]
default-character-set=utf8
socket=/data/mysql/3306/mysql.sock
[mysqldump]
max_allowed_packet = 512M
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1
---登錄MySQL數據庫
安裝完成后，我們好像不知道mysql的默認密碼，無(wú)法登陸數據庫，其實(shí)在安裝日志中會(huì )提示數據庫的默認密碼，可以使用默認密碼登陸并修改密碼，這樣就不需要下面繁瑣的操作。
1、在配置文件/etc/my.cnf添加skip-grant-tables一行，跳過(guò)密碼驗證。
2、重啟mysql數據庫主進(jìn)程# /etc/init.d/mysqld restart（也可以直接先停掉MySQL進(jìn)程后使用skip-grant-tables參數重啟MySQL）
3、登錄數據庫修改密碼。
mysql> use mysql;
mysql> update user set authentication_string=password('mysql') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> exit
這里需要修改的字段是authentication_string，這點(diǎn)和之前的版本不同。
4、這個(gè)時(shí)候，如果你設置的密碼太簡(jiǎn)單，則在數據庫執行任何命令都會(huì )報類(lèi)似如下錯誤：
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> update user set authentication_string=password('mysql') where user='root' and host='localhost';
ERROR 1046 (3D000): No database selected
mysql> use mysql;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
5、注意：如果只想設置簡(jiǎn)單密碼需要修改兩個(gè)全局參數：
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("mysql");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
---授權用于集群同步的用戶(hù)和密碼，創(chuàng )建的賬號在集群安裝成功后會(huì )自動(dòng)同步到集群各個(gè)節點(diǎn)。
# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.17 MySQL Community Server - (GPL), wsrep_25.10.20170117.92194e2
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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.
mysql> grant usage on *.* to tangyun@'%' identified by 'tangyun';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant all privileges on *.* to tangyun@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
----到這里基本上已經(jīng)安裝初始化完成，可以嘗試創(chuàng )建數據庫并測試同步
1、檢查集群同步及初始化嘗試情況
mysql> show global status like 'wsrep%';
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |
| wsrep_protocol_version | 7 |
| 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 | 10 |
| wsrep_received_bytes | 752 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| 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_incoming_addresses | 192.168.56.111:3306,192.168.56.112:3306,192.168.56.113:3306 |
| 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_gcomm_uuid | df49c18f-701a-11e7-aaaa-9659aa7ef9f8 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |
| 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.20(r7e383f7) |
| wsrep_ready | ON |
+------------------------------+-------------------------------------------------------------+
57 rows in set (0.00 sec)
---創(chuàng )建數據庫，測試集群高可用
db01創(chuàng )建數據庫：
[root@galera01 subsys]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-log MySQL Community Server - (GPL), wsrep_25.10.20170117.92194e2
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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.
mysql> create database tangyun default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tangyun |
+--------------------+
5 rows in set (0.00 sec)
db02/db03：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tangyun |
+--------------------+
5 rows in set (0.00 sec)
---關(guān)閉db02，在db03上創(chuàng )建表并插入數據
[root@galera02 ~]# /etc/init.d/mysqld stop
Stopping mysqld: [ OK ]
[root@galera03 ~]# mysql -uroot -pmysql
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.7.17 MySQL Community Server - (GPL), wsrep_25.10.20170117.92194e2
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
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.
mysql> use tangyun;
Database changed
mysql> create table ty(tid int,tname varchar(20));
Query OK, 0 rows affected (0.08 sec)
mysql> insert into ty(tid,tname) values(1,'tangyun');
Query OK, 1 row affected (0.04 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>
----檢查db01是否同步，啟動(dòng)db02并檢查是否同步。
db01:
mysql> select * from tangyun.ty;
+------+---------+
| tid | tname |
+------+---------+
| 1 | tangyun |
+------+---------+
1 row in set (0.00 sec)
db02:
[root@galera02 ~]# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
mysql> select * from tangyun.ty;
+------+---------+
| tid | tname |
+------+---------+
| 1 | tangyun |
+------+---------+
1 row in set (0.01 sec)
---同步正常。
---安裝及初始化遇到問(wèn)題處理
1、failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
160613 9:43:01 [Note] WSREP: view((empty))
160613 9:43:01 [ERROR] WSREP: failed to open gcomm backend connection: 110: failed to reach primary view: 110 (Connection timed out)
at gcomm/src/pc.cpp:connect():162
160613 9:43:01 [ERROR] WSREP: gcs/src/gcs_core.cpp:gcs_core_open():208: Failed to open backend connection: -110 (Connection timed out)
160613 9:43:01 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel ''galera_cluster’' at 'gcomm://192.168.56.111,192.168.56.112,192.168.56.113': -110 (Connection timed out)
160613 9:43:01 [ERROR] WSREP: gcs connect failed: Connection timed out
160613 9:43:01 [ERROR] WSREP: wsrep::connect(gcomm://192.168.56.111,192.168.56.112,192.168.56.113) failed: 7
160613 9:43:01 [ERROR] Aborting
160613 9:43:01 [Note] WSREP: Service disconnected.
160613 9:43:02 [Note] WSREP: Some threads may fail to exit.
160613 9:43:02 [Note] /usr/sbin/mysqld: Shutdown complete
解決辦法：
排除是防火墻、網(wǎng)絡(luò )不通問(wèn)題后
刪除該節點(diǎn)及該節點(diǎn)前面所有節點(diǎn)MySQL文件安裝目錄下的兩個(gè)緩存文件及/var/lock/subsys 目錄下的mysqld 文件，然后重新啟動(dòng)：
# cd /var/lock/subsys
# rm -rf mysql*
# cd /data/mysql/3306
rm -rf galera.cache grastate.dat
##第一個(gè)節點(diǎn)啟動(dòng)
# /etc/init.d/mysqld start --wsrep-new-cluster
Starting mysqld: [ OK ]
其它節點(diǎn)啟動(dòng)：
# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
2、MySQL集群主機異常重啟后mysql無(wú)法正常啟動(dòng)-edit the grastate.dat file manually and set safe_to_bootstrap to 1
1、嘗試重啟mysql數據庫時(shí)報錯
[root@galera01 ~]# /etc/init.d/mysqld start --wsrep-new-cluster
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
2、查看啟動(dòng)報錯日志
2017-07-24T02:45:41.972508Z 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the
updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .
2017-07-24T02:45:41.972511Z 0 [ERROR] WSREP: wsrep::connect(gcomm://192.168.56.111,192.168.56.112,192.168.56.113) failed: 7
2017-07-24T02:45:41.972513Z 0 [ERROR] Aborting
2017-07-24T02:45:41.972516Z 0 [Note] Giving 0 client threads a chance to die gracefully
2017-07-24T02:45:41.972519Z 0 [Note] WSREP: Service disconnected.
2017-07-24T02:45:42.972895Z 0 [Note] WSREP: Some threads may fail to exit.
2017-07-24T02:45:42.972937Z 0 [Note] Binlog end
2017-07-24T02:45:42.973014Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
3、嘗試先啟動(dòng)其他節點(diǎn)，相同報錯
從錯誤日志提示報錯提示，當前節點(diǎn)不是集群中最后離開(kāi)的節點(diǎn)，也就是說(shuō)當前節點(diǎn)可能未能包含所有的更新。
如果強制啟動(dòng)當前節點(diǎn)，需要修改grastate.dat文件將safe_to_bootstrap的值置為1。
嘗試先啟動(dòng)其他節點(diǎn)，相同報錯。
4、修改grastate.dat文件
該文件主要描述GALERA保持的狀態(tài)信息，按指引修改safe_to_bootstrap的值置為1。
# cat grastate.dat
# GALERA saved state
version: 2.1
uuid: df4a1da6-701a-11e7-87fe-e6c3a440d1ec
seqno: -1
safe_to_bootstrap: 1 ---由原來(lái)的0修改為1再次啟動(dòng)
5、mysql集群?jiǎn)?dòng)成功
mysql> show global status like 'wsrep%';
+------------------------------+-------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+-------------------------------------------------------------+
| wsrep_local_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |
| wsrep_protocol_version | 7 |
| 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 | 10 |
| wsrep_received_bytes | 752 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0.000000 |
| 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_incoming_addresses | 192.168.56.111:3306,192.168.56.112:3306,192.168.56.113:3306 |
| 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_gcomm_uuid | df49c18f-701a-11e7-aaaa-9659aa7ef9f8 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | df4a1da6-701a-11e7-87fe-e6c3a440d1ec |
| 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.20(r7e383f7) |
| wsrep_ready | ON |
+------------------------------+-------------------------------------------------------------+
57 rows in set (0.00 sec)
-----------------------------------End By TangYun--------------------------------------------------
免責聲明：本站發(fā)布的內容（圖片、視頻和文字）以原創(chuàng )、來(lái)自本網(wǎng)站內容采集于網(wǎng)絡(luò )互聯(lián)網(wǎng)轉載等其它媒體和分享為主，內容觀(guān)點(diǎn)不代表本網(wǎng)站立場(chǎng)，如侵犯了原作者的版權，請告知一經(jīng)查實(shí)，將立刻刪除涉嫌侵權內容，聯(lián)系我們QQ:712375056，同時(shí)歡迎投稿傳遞力量。
Copyright ? 2009-2022 56dr.com. All Rights Reserved. 特網(wǎng)科技 特網(wǎng)云 版權所有 特網(wǎng)科技 粵ICP備16109289號
域名注冊服務(wù)機構:阿里云計算有限公司（萬(wàn)網(wǎng)） 域名服務(wù)機構：煙臺帝思普網(wǎng)絡(luò )科技有限公司（DNSPod） CDN服務(wù)：阿里云計算有限公司 百度云 中國互聯(lián)網(wǎng)舉報中心 增值電信業(yè)務(wù)經(jīng)營(yíng)許可證B2
建議您使用Chrome、Firefox、Edge、IE10及以上版本和360等主流瀏覽器瀏覽本網(wǎng)站
