MySQL5.7 Galera Cluster安裝搭建及高可用測試
# rpm -ivh mysql-wsrep-common-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-common-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-common-5.7 ########################################### [100%]
# rpm -ivh mysql-wsrep-libs-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-libs-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-libs-5.7 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
error: Failed dependencies:
mysql-wsrep-client-5.7(x86-64) >= 5.7.9 is needed by mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64
[root@mysql01 local]# rpm -ivh galera-3-25.3.20-2.el6.x86_64.rpm
warning: galera-3-25.3.20-2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:galera-3 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-client-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-client-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-client-5.7 ########################################### [100%]
[root@mysql01 local]#
[root@mysql01 local]# rpm -ivh mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-server-5.7 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-libs-compat-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-libs-compat########################################### [100%]
[root@mysql01 local]#
---建立並配置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--------------------------------------------------
warning: mysql-wsrep-common-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-common-5.7 ########################################### [100%]
# rpm -ivh mysql-wsrep-libs-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-libs-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-libs-5.7 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
error: Failed dependencies:
mysql-wsrep-client-5.7(x86-64) >= 5.7.9 is needed by mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64
[root@mysql01 local]# rpm -ivh galera-3-25.3.20-2.el6.x86_64.rpm
warning: galera-3-25.3.20-2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:galera-3 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-client-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-client-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-client-5.7 ########################################### [100%]
[root@mysql01 local]#
[root@mysql01 local]# rpm -ivh mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-server-5.7 ########################################### [100%]
[root@mysql01 local]# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm
warning: mysql-wsrep-libs-compat-5.7-5.7.17-25.10.20170117.92194e2.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing... ########################################### [100%]
1:mysql-wsrep-libs-compat########################################### [100%]
[root@mysql01 local]#
---建立並配置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--------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24930246/viewspace-2142522/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL高可用之GC-Galera Cluster for MySQLMySqlGC
- 基於Centos7.x 搭建MySQL Galera Cluster高可用架構CentOSMySql架構
- MySQL高可用之MGC--MariaDB Galera ClusterMySqlGC
- MySQL Galera Cluster全解析 Part 4 Galera Cluster 安裝前準備MySql
- 5. MySQL Galera Cluster全解析 Part 5 Galera Cluster軟體安裝MySql
- MySQL——MHA高可用群集部署及故障測試MySql
- MySQL高可用群集MHA部署及故障測試分析MySql
- 9. MySQL Galera Cluster全解析 Part 9 監控Galera ClusterMySql
- 7. MySQL Galera Cluster全解析 Part 7 Galera Cluster部署指南MySql
- 1. MySQL Galera Cluster全解析 Part 1 Galera Cluster 簡介MySql
- MHA安裝搭建和測試
- kaldi 的安裝及測試
- 6. MySQL Galera Cluster全解析 Part 6 Galera Cluster引數設定MySql
- windows安裝及配置mysql5.7WindowsMySql
- MySQL Galera叢集搭建流程(Percona XtraDB Cluster 5.7)MySql
- MySQL5.7高可用版釋出MySql
- 【工具-Nginx】從入門安裝到高可用叢集搭建Nginx
- JDK安裝及測試檔案JDK
- MySQL Cluster7.2.15原始碼安裝部署效能測試MySql原始碼
- Redis5.0 主從模式和高可用 搭建和測試報告Redis模式測試報告
- 8. MySQL Galera Cluster全解析 Part 8 Galera Cluster和傳統MySQL的不同點MySql
- MySQL Cluster7.2.4初步安裝部署新特性效能測試等MySql
- 三十七、Flume的安裝及測試
- Redis操作及叢集搭建以及高可用配置Redis
- mysql5.7安裝及問題處理MySql
- MYSQL5.7.22 原始碼安裝 主從搭建 + KEEPALIVED高可用MySql原始碼
- Redis之Sentinel高可用安裝部署Redis
- MySQL高可用MMM方案安裝部署MySql
- MySQL InnoDB Cluster環境搭建和簡單測試MySql
- Centos7.5基於MySQL5.7的 InnoDB Cluster 多節點高可用叢集環境部署記錄CentOSMySql
- 【DB寶40】MySQL高可用管理工具Orchestrator簡介及測試MySql
- yum方式安裝及配置最新的mysql5.7MySql
- MySQL5.7高可用版商業化釋出MySql
- MySQL NDB Cluster,Galera Cluster和Percona XtraDB Cluster優缺點總結MySql
- 【DB寶60】PG12高可用之1主2從流複製環境搭建及切換測試
- ProxySQL Cluster 高可用叢集環境部署記錄SQL
- 6.linux->MySQL 安裝及效能測試LinuxMySql
- Bash on Windows 搶鮮測試 -- 介紹及安裝Windows