MySQL5.7 Galera Cluster的安装搭建及高可用测试

本篇内容介绍了“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 |
| 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/sb免费主机域名in/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 |
| wsrep_provider_version | 3.20(r7e383f7) |
| wsrep_ready | ON |
+——————————+————————————————————-+
57 rows in set (0.00 sec)

———————————–End By TangYun————————————————–“MySQL5.7 Galera Cluster的安装搭建及高可用测试”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注云技术网站，小编将为大家输出更多高质量的实用文章！

相关推荐: PostgreSQL的pg_promote有什么作用

这篇文章主要讲解了“PostgreSQL的pg_promote有什么作用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“PostgreSQL的pg_promote有什么作用”吧！在PG 12以前的版本,备库提升为…