溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“MySQL5.7 Galera Cluster的安裝搭建及高可用測試”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
---創建并配置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/
---創建并配置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，必須是唯一的，集群各個節點也不同
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節點的ID，必須是唯一的，集群各個節點也不同
wsrep_node_name = db01
##集群中的其他節點地址，可以使用主機名或IP
wsrep_cluster_address=gcomm://192.168.56.111,192.168.56.112,192.168.56.113
#本機節點地址，可以使用主機名或IP
wsrep_node_address='192.168.56.111'
#指定wsrep啟動端口號
wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.56.111:4567"
#一個逗號分割的節點串作為狀態轉移源，比如wsrep_sst_donor=db01,db02,如果db01可用，用db02
,如果db02不可用，用db03，最后的逗號表明讓提供商自己選擇一個最優的。
wsrep_sst_donor='db01,db02,db03'
##集群同步方式
wsrep_sst_method=rsync
##集群同步的用戶名密碼
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的默認密碼，無法登陸數據庫，其實在安裝日志中會提示數據庫的默認密碼，可以使用默認密碼登陸并修改密碼，這樣就不需要下面繁瑣的操作。
1、在配置文件/etc/my.cnf添加skip-grant-tables一行，跳過密碼驗證。
2、重啟mysql數據庫主進程# /etc/init.d/mysqld restart（也可以直接先停掉MySQL進程后使用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，這點和之前的版本不同。
4、這個時候，如果你設置的密碼太簡單，則在數據庫執行任何命令都會報類似如下錯誤：
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、注意：如果只想設置簡單密碼需要修改兩個全局參數：
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
---授權用于集群同步的用戶和密碼，創建的賬號在集群安裝成功后會自動同步到集群各個節點。
# 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
----到這里基本上已經安裝初始化完成，可以嘗試創建數據庫并測試同步
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)
---創建數據庫，測試集群高可用
db01創建數據庫：
[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)
---關閉db02，在db03上創建表并插入數據
[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是否同步，啟動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)
---同步正常。
---安裝及初始化遇到問題處理
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
解決辦法：
排除是防火墻、網絡不通問題后
刪除該節點及該節點前面所有節點MySQL文件安裝目錄下的兩個緩存文件及/var/lock/subsys 目錄下的mysqld 文件，然后重新啟動：
# cd /var/lock/subsys
# rm -rf mysql*
# cd /data/mysql/3306
rm -rf galera.cache grastate.dat
##第一個節點啟動
# /etc/init.d/mysqld start --wsrep-new-cluster
Starting mysqld: [ OK ]
其它節點啟動：
# /etc/init.d/mysqld start
Starting mysqld: [ OK ]
2、MySQL集群主機異常重啟后mysql無法正常啟動-edit the grastate.dat file manually and set safe_to_bootstrap to 1
1、嘗試重啟mysql數據庫時報錯
[root@galera01 ~]# /etc/init.d/mysqld start --wsrep-new-cluster
MySQL Daemon failed to start.
Starting mysqld: [FAILED]
2、查看啟動報錯日志
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、嘗試先啟動其他節點，相同報錯
從錯誤日志提示報錯提示，當前節點不是集群中最后離開的節點，也就是說當前節點可能未能包含所有的更新。
如果強制啟動當前節點，需要修改grastate.dat文件將safe_to_bootstrap的值置為1。
嘗試先啟動其他節點，相同報錯。
4、修改grastate.dat文件
該文件主要描述GALERA保持的狀態信息，按指引修改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 ---由原來的0修改為1再次啟動
5、mysql集群啟動成功
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--------------------------------------------------
“MySQL5.7 Galera Cluster的安裝搭建及高可用測試”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
