溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇內(nèi)容介紹了“MySQL5.7 Galera Cluster的安裝搭建及高可用測試”的有關(guān)知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
---創(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服務(wù)器的ID，必須是唯一的，集群各個節(jié)點也不同
server-id=111
explicit_defaults_for_timestamp=true
basedir=/usr
##MySQL數(shù)據(jù)文件存儲路徑
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集群的名字，必須是統(tǒng)一的
wsrep_cluster_name='tangyun_cluster'
##wsrep提供者,必須配置(.so文件的路徑在哪，就配置成哪)
wsrep-provider=/usr/lib64/galera-3/libgalera_smm.so
##wsrep節(jié)點的ID，必須是唯一的，集群各個節(jié)點也不同
wsrep_node_name = db01
##集群中的其他節(jié)點地址，可以使用主機名或IP
wsrep_cluster_address=gcomm://192.168.56.111,192.168.56.112,192.168.56.113
#本機節(jié)點地址，可以使用主機名或IP
wsrep_node_address='192.168.56.111'
#指定wsrep啟動端口號
wsrep_provider_options ="gmcast.listen_addr=tcp://192.168.56.111:4567"
#一個逗號分割的節(jié)點串作為狀態(tài)轉(zhuǎn)移源，比如wsrep_sst_donor=db01,db02,如果db01可用，用db02
,如果db02不可用，用db03，最后的逗號表明讓提供商自己選擇一個最優(yōu)的。
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數(shù)據(jù)庫
安裝完成后，我們好像不知道m(xù)ysql的默認(rèn)密碼，無法登陸數(shù)據(jù)庫，其實在安裝日志中會提示數(shù)據(jù)庫的默認(rèn)密碼，可以使用默認(rèn)密碼登陸并修改密碼，這樣就不需要下面繁瑣的操作。
1、在配置文件/etc/my.cnf添加skip-grant-tables一行，跳過密碼驗證。
2、重啟mysql數(shù)據(jù)庫主進程# /etc/init.d/mysqld restart（也可以直接先停掉MySQL進程后使用skip-grant-tables參數(shù)重啟MySQL）
3、登錄數(shù)據(jù)庫修改密碼。
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、這個時候，如果你設(shè)置的密碼太簡單，則在數(shù)據(jù)庫執(zhí)行任何命令都會報類似如下錯誤：
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、注意：如果只想設(shè)置簡單密碼需要修改兩個全局參數(shù)：
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
---授權(quán)用于集群同步的用戶和密碼，創(chuàng)建的賬號在集群安裝成功后會自動同步到集群各個節(jié)點。
# 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)建數(shù)據(jù)庫并測試同步
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)建數(shù)據(jù)庫，測試集群高可用
db01創(chuàng)建數(shù)據(jù)庫：
[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)建表并插入數(shù)據(jù)
[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
解決辦法：
排除是防火墻、網(wǎng)絡(luò)不通問題后
刪除該節(jié)點及該節(jié)點前面所有節(jié)點MySQL文件安裝目錄下的兩個緩存文件及/var/lock/subsys 目錄下的mysqld 文件，然后重新啟動：
# cd /var/lock/subsys
# rm -rf mysql*
# cd /data/mysql/3306
rm -rf galera.cache grastate.dat
##第一個節(jié)點啟動
# /etc/init.d/mysqld start --wsrep-new-cluster
Starting mysqld: [ OK ]
其它節(jié)點啟動：
# /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數(shù)據(jù)庫時報錯
[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、嘗試先啟動其他節(jié)點，相同報錯
從錯誤日志提示報錯提示，當(dāng)前節(jié)點不是集群中最后離開的節(jié)點，也就是說當(dāng)前節(jié)點可能未能包含所有的更新。
如果強制啟動當(dāng)前節(jié)點，需要修改grastate.dat文件將safe_to_bootstrap的值置為1。
嘗試先啟動其他節(jié)點，相同報錯。
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 ---由原來的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的安裝搭建及高可用測試”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實用文章！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
