文档章节

Galera_Cluster_For_MySQL集群安装

白石
 白石
发布于 2015/05/05 16:04
字数 4543
阅读 612
收藏 15

Galera Cluster For MySQL简介:

Galera Cluster for MySQL是一套基于同步复制的多主MySQL集群解决方案,使用简单,没有单点故障,可用性高,能很好保证业务不断增长时我们数据的安全和随时的扩展,主要特点:

  • 同步复制
  • 多主服务器的拓扑结构
  • 可以在任意节点上进行读写
  • 自动剔除故障节点
  • 自动加入新节点
  • 真正行级别的并发复制
  • 客户端连接跟操作单台MySQL数据库的体验一致

注意: Galera Cluster需要至少三台mysql服务器,如果只有两台做cluster的话,一旦出现数据不一致的情况,就可能发生脑裂,需要一台仲裁服务器,如果直接三台做cluster的话,就不再需要仲裁

使用Galera Cluster For MySQL前注意事项:

  1. 使用Galera必须要给MySQL-Server打wsrep补丁.可以直接使用官方提供的已经打好补丁的MySQL安装包,
    如果服务器上已经安装了标准版MYSQL,需要先卸载再重新安装.卸载前注意备份数据.
  2. MySQL/Galera集群只支持InnoDB存储引擎.如果你的数据表使用的MyISAM,需要转换为InnoDB,否则记录不会在多台复制.
    可以在备份老数据时,为mysqldump命令添加-skip-create-options参数,这样会去掉表结构的声明信息,再导入集群时自动使用InnoDB引擎.
    不过这样会将AUTO_INCREMENT一并去掉,已有AUTO_INCREMENT列的表,必须在导入后重新定义.
  3. MySQL 5.5及以下的InnoDB引擎不支持全文索引(FULLTEXT indexes),如果之前使用MyISAM并建了全文索引字段的话,只能安装MySQL 5.6 with wsrep patch.
  4. 所有数据表必须要有主键(PRIMARY),如果没有主键可以建一条AUTO_INCREMENT列.
  5. MySQL/Galera集群不支持下面的查询:LOCK/UNLOCK TABLES,不支持下面的系统变量:character_set_server,utf16,utf32及ucs2.
  6. 数据库日志不支持保存到表,只能输出到文件(log_output=FILE),不能设置binlog-do-db,binlog-ignore-db.
  7. 允许最大的事务大小由wsrep_max_ws_rows和wsrep_max_ws_size定义.任何大型操作将被拒绝.如大型的LOAD DATA操作.
  8. 由于集群是乐观的并发控制,事务commit可能在该阶段中止.如果有两个事务向在集群中不同的节点向同一行写入并提交,失败的节点将中止.
    对于集群级别的中止,集群返回死锁错误代码(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).可以在写入/更新失败时,自动重试一次,再返回结果.
  9. XA事务不支持,由于在提交上可能回滚.
  10. 整个集群的写入吞吐量是由最弱的节点限制,如果有一个节点变得缓慢,那么整个集群将是缓慢的.为了稳定的高性能要求,所有的节点应使用统一的硬件.
  11. 跟其他集群一样,为了避免节点出现脑裂而破坏数据,建议Galera集群最低添加3个节点.
  12. 节点中每个节点的地位是平等的,没有主次,向任何一个节点读写效果都是一样的.实际可以配合VIP/LVS或HA使用,实现高可用性.
  13. 如果集群中的机器全部重启,如机房断电,第一台启动的服务器必须以--wsrep-new-cluster参数启动:service mysql start ----wsrep-new-cluster,
    并且wsrep_cluster_address必须设置成"gcomm://

安装环境:

  • 系统版本:CentOS 6.3 x86_64
  • 均关闭防火墙,关闭SELinux.
  • 服务器列表:

192.168.1.85 #galera.node1

192.168.1.86 #galera.node2

192.168.1.87 #galera.node3

  • mysql的root,wsrep_sst-user密码均为galera_password

Galera Cluster For MySQL的安装:

(所有节点)yum在线安装:

  • 配置repository配置文件: 编辑/etc/yum.repos.d/galera.repo文件:vi /etc/yum.repos.d/galera.repo

内容如下:

[galera]
name=Galera
gpgcheck=0
enabled=1
baseurl=http://releases.galeracluster.com/centos/6/x86_64/
  • yum安装:yum install galera-3 mysql-wsrep-5.6

(所有节点)rpm本地安装:

先删除系统自带的mysql:rpm -qa | grep -i mysql 输出:

root@pts/0 # rpm -qa | grep -i mysql
mysql-libs-5.1.73-3.el6_5.x86_64
mysql-5.1.73-3.el6_5.x86_64

然后执行:

rpm --nodeps -ev mysql-libs-5.1.73-3.el6_5.x86_64
rpm --nodeps -ev mysql-5.1.73-3.el6_5.x86_64

创建目录: mkdir -p /opt/tmp_galera/
复制文件:

boost-program-options-1.41.0-25.el6.x86_64.rpm
galera-3-25.3.10-2.el6.x86_64.rpm
mysql-wsrep-5.6-5.6.23-25.10.el6.x86_64.rpm
mysql-wsrep-client-5.6-5.6.23-25.10.el6.x86_64.rpm
mysql-wsrep-server-5.6-5.6.23-25.10.el6.x86_64.rpm
nc-1.84-22.el6.x86_64.rpm
nmap-5.51-4.el6.x86_64.rpm

/opt/tmp_galera/

安装依赖库:

cd /opt/tmp_galera/
rpm -Uvh boost-program-options-*.rpm
rpm -Uvh ./nmap-*.rpm
rpm -Uvh ./nc-*.rpm

安装galerarpm -Uvh ./galera-*.rpm
安装mysqlrpm -Uvh ./mysql-wsrep-*.rpm
输出:

warning: ./mysql-wsrep-5.6-5.6.23-25.10.el6.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID bc19ddba: NOKEY
Preparing...                ########################################### [100%]
   1:mysql-wsrep-server-5.6 ########################################### [ 33%]
warning: user mysql does not exist - using root
warning: group mysql does not exist - using root
2015-05-04 10:52:23 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-04 10:52:23 29838 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-05-04 10:52:23 29838 [Note] WSREP: wsrep_load(): loading provider library 'none'
2015-05-04 10:52:23 29838 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-05-04 10:52:23 29838 [Note] InnoDB: The InnoDB memory heap is disabled
2015-05-04 10:52:23 29838 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-04 10:52:23 29838 [Note] InnoDB: Memory barrier is not used
2015-05-04 10:52:23 29838 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-05-04 10:52:23 29838 [Note] InnoDB: Using Linux native AIO
2015-05-04 10:52:23 29838 [Note] InnoDB: Using CPU crc32 instructions
2015-05-04 10:52:23 29838 [Note] InnoDB: Initializing buffer pool, size=128.0M
2015-05-04 10:52:23 29838 [Note] InnoDB: Completed initialization of buffer pool
2015-05-04 10:52:24 29838 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!
2015-05-04 10:52:24 29838 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB
2015-05-04 10:52:24 29838 [Note] InnoDB: Database physically writes the file full: wait...
2015-05-04 10:52:24 29838 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB
2015-05-04 10:52:24 29838 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB
2015-05-04 10:52:24 29838 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
2015-05-04 10:52:24 29838 [Warning] InnoDB: New log files created, LSN=45781
2015-05-04 10:52:24 29838 [Note] InnoDB: Doublewrite buffer not found: creating new
2015-05-04 10:52:24 29838 [Note] InnoDB: Doublewrite buffer created
2015-05-04 10:52:24 29838 [Note] InnoDB: 128 rollback segment(s) are active.
2015-05-04 10:52:24 29838 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-05-04 10:52:24 29838 [Note] InnoDB: Foreign key constraint system tables created
2015-05-04 10:52:24 29838 [Note] InnoDB: Creating tablespace and datafile system tables.
2015-05-04 10:52:24 29838 [Note] InnoDB: Tablespace and datafile system tables created.
2015-05-04 10:52:24 29838 [Note] InnoDB: Waiting for purge to start
2015-05-04 10:52:24 29838 [Note] InnoDB: 5.6.23 started; log sequence number 0
A random root password has been set. You will find it in '/root/.mysql_secret'.
2015-05-04 10:52:24 29838 [Note] WSREP: Service disconnected.
2015-05-04 10:52:25 29838 [Note] WSREP: Some threads may fail to exit.
2015-05-04 10:52:25 29838 [Note] Binlog end
2015-05-04 10:52:25 29838 [Note] InnoDB: FTS optimize thread exiting.
2015-05-04 10:52:25 29838 [Note] InnoDB: Starting shutdown...
2015-05-04 10:52:27 29838 [Note] InnoDB: Shutdown completed; log sequence number 1625977


2015-05-04 10:52:27 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-05-04 10:52:27 29867 [Note] WSREP: Read nil XID from storage engines, skipping position init
2015-05-04 10:52:27 29867 [Note] WSREP: wsrep_load(): loading provider library 'none'
2015-05-04 10:52:27 29867 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-05-04 10:52:27 29867 [Note] InnoDB: The InnoDB memory heap is disabled
2015-05-04 10:52:27 29867 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-05-04 10:52:27 29867 [Note] InnoDB: Memory barrier is not used
2015-05-04 10:52:27 29867 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-05-04 10:52:27 29867 [Note] InnoDB: Using Linux native AIO
2015-05-04 10:52:27 29867 [Note] InnoDB: Using CPU crc32 instructions
2015-05-04 10:52:27 29867 [Note] InnoDB: Initializing buffer pool, size=128.0M
2015-05-04 10:52:27 29867 [Note] InnoDB: Completed initialization of buffer pool
2015-05-04 10:52:27 29867 [Note] InnoDB: Highest supported file format is Barracuda.
2015-05-04 10:52:27 29867 [Note] InnoDB: 128 rollback segment(s) are active.
2015-05-04 10:52:27 29867 [Note] InnoDB: Waiting for purge to start
2015-05-04 10:52:27 29867 [Note] InnoDB: 5.6.23 started; log sequence number 1625977
2015-05-04 10:52:27 29867 [Note] WSREP: Service disconnected.
2015-05-04 10:52:28 29867 [Note] WSREP: Some threads may fail to exit.
2015-05-04 10:52:28 29867 [Note] Binlog end
2015-05-04 10:52:28 29867 [Note] InnoDB: FTS optimize thread exiting.
2015-05-04 10:52:28 29867 [Note] InnoDB: Starting shutdown...
2015-05-04 10:52:30 29867 [Note] InnoDB: Shutdown completed; log sequence number 1625987




A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
You will find that password in '/root/.mysql_secret'.

You must change that password on your first connect,
no other statement but 'SET PASSWORD' will be accepted.
See the manual for the semantics of the 'password expired' flag.

Also, the account for the anonymous user has been removed.

In addition, you can run:

  /usr/bin/mysql_secure_installation

which will also give you the option of removing the test database.
This is strongly recommended for production servers.

See the manual for more instructions.

Please report any problems at http://bugs.mysql.com/

The latest information about MySQL is available on the web at

  http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

New default config file was created as /usr/my.cnf and
will be used by default by the server when you start it.
You may edit this file to change server settings

   2:mysql-wsrep-client-5.6 ########################################### [ 67%]
   3:mysql-wsrep-5.6        ########################################### [100%]

(所有节点)初始化数据库

  • 启动mysql:service mysql start
  • 执行命令:/usr/bin/mysql_secure_installation
  • 会要求输入root的口令,此口令在/root/.mysql_secret文件里!

输出:

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] y 
New password: galera_password
Re-enter new password: galera_password
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y 
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] n
 ... skipping.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!




All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...

(所有节点)修改权限和口令

  • 用本机连接: mysql -h 127.0.0.1 -u root --password=galera_password
  • 在mysql里执行如下命令:
mysql>SET wsrep_on=OFF;
mysql>use mysql
mysql>update user set password=password("galera_password") where user="root";
mysql>GRANT ALL ON *.* TO root@localhost  IDENTIFIED BY 'galera_password' WITH GRANT OPTION;
mysql>GRANT ALL ON *.* TO root@"%"  IDENTIFIED BY 'galera_password' WITH GRANT OPTION;
mysql>DELETE FROM mysql.user WHERE user='';
mysql>GRANT ALL ON *.* TO 'wsrep_sst-user'@'%' IDENTIFIED BY 'galera_password';
mysql>flush privileges;
mysql>exit

(所有节点)配置mysql的配置文件

  • 停止mysql:service mysql stop
  • 创建/etc/mysql/conf.d目录:mkdir -p /etc/mysql/conf.d
  • 编辑/etc/my.cnf文件:vi /etc/my.cnf

内容如下:

[mysqld]
!includedir /etc/mysql/conf.d/

启动节点1:

  • 停止mysql:service mysql stop
  • 编辑/etc/mysql/conf.d/my_galera.cnf文件:vi /etc/mysql/conf.d/my_galera.cnf

内容如下:

[mysql]
#设置mysql client default character
default-character-set=utf8
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"

[mysqld]
user=mysql
bind-address=0.0.0.0
character-set-server=utf8
default-storage-engine=INNODB
default-time-zone='+8:00'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve

#slow query
slow_query_log=on
log_output=FILE
slow_query_log_file=slow_query.txt
long_query_time=1

max_connections=5000
table_open_cache=2048
sort_buffer_size=8M
thread_cache_size=16
#query_cache_size=32M  #galera: Do not use query cache
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=8

#MyISAM
key_buffer_size=512M
read_buffer_size=8M
read_rnd_buffer_size=8M

#InnoDB
# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
#innodb_data_file_path=ibdata1:${实际大小};ibdata2:4G;ibdata3:8G:autoextend

#galera innodb参数
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
transaction-isolation=READ-COMMITTED

#galera cluster参数
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"
wsrep_sst_method=rsync
wsrep_sst_auth=wsrep_sst-user:galera_password

wsrep_cluster_name=MyCluster
wsrep_cluster_address="gcomm://"
wsrep_node_name=galera.node1
wsrep_node_address="192.168.1.85"

注意: 启动galera集群的第1个节点不要设置wsrep_cluster_address地址!
"gcomm://"是特殊的地址,仅仅是Galera Cluster集群的第1个节点初始化启动时候使用.

  • 初始化集群,启动第1个节点的mysql:service mysql start --wsrep-new-cluster

初始化galera集群的第一个节点必须加上--wsrep-new-cluster参数

  • 用本机连接: mysql -h 127.0.0.1 -u root --password=galera_password
    在mysql里执行如下命令:
mysql>SHOW STATUS LIKE 'wsrep%';

如果:wsrep_ready=ON,wsrep_cluster_size=1,就说明一切正常

  • 注意: 如果集群启动以后,我们关闭了第1个节点,那么再次启动的时候必须执行service mysql start --wsrep_cluster_address=gcomm://${集群中其他任一节点的地址}

启动节点2:

  • 停止mysql:service mysql stop
  • 编辑/etc/mysql/conf.d/my_galera.cnf文件:vi /etc/mysql/conf.d/my_galera.cnf

内容如下:

[mysql]
#设置mysql client default character
default-character-set=utf8
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"

[mysqld]
user=mysql
bind-address=0.0.0.0
character-set-server=utf8
default-storage-engine=INNODB
default-time-zone='+8:00'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve

#slow query
slow_query_log=on
log_output=FILE
slow_query_log_file=slow_query.txt
long_query_time=1

max_connections=5000
table_open_cache=2048
sort_buffer_size=8M
thread_cache_size=16
#query_cache_size=32M  #galera: Do not use query cache
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=8

#MyISAM
key_buffer_size=512M
read_buffer_size=8M
read_rnd_buffer_size=8M

#InnoDB
# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
#innodb_data_file_path=ibdata1:${实际大小};ibdata2:4G;ibdata3:8G:autoextend

#galera innodb参数
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
transaction-isolation=READ-COMMITTED

#galera cluster参数
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"
wsrep_sst_method=rsync
wsrep_sst_auth=wsrep_sst-user:galera_password

wsrep_cluster_name=MyCluster
wsrep_cluster_address="gcomm://192.168.1.85"
wsrep_node_name=galera.node2
wsrep_node_address="192.168.1.86"

wsrep_cluster_address地址是第1个节点的地址!

  • 启动第2个节点的mysql:service mysql start
  • 用本机连接: mysql -h 127.0.0.1 -u root --password=galera_password
    在mysql里执行如下命令:
mysql>SHOW STATUS LIKE 'wsrep%';

如果输出里:wsrep_ready=ON,wsrep_cluster_size=2,就说明第2个节点正常!

启动节点3:

  • 停止mysql:service mysql stop
  • 编辑/etc/mysql/conf.d/my_galera.cnf文件:vi /etc/mysql/conf.d/my_galera.cnf

内容如下:

[mysql]
#设置mysql client default character
default-character-set=utf8
no-auto-rehash
prompt="\\u@\\h:\\d \\r:\\m:\\s>"

[mysqld]
user=mysql
bind-address=0.0.0.0
character-set-server=utf8
default-storage-engine=INNODB
default-time-zone='+8:00'
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-name-resolve

#slow query
slow_query_log=on
log_output=FILE
slow_query_log_file=slow_query.txt
long_query_time=1

max_connections=5000
table_open_cache=2048
sort_buffer_size=8M
thread_cache_size=16
#query_cache_size=32M  #galera: Do not use query cache
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=8

#MyISAM
key_buffer_size=512M
read_buffer_size=8M
read_rnd_buffer_size=8M

#InnoDB
# You can set .._buffer_pool_size up to 50 - 80 % of RAM but beware of setting memory usage too high
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
#innodb_data_file_path=ibdata1:${实际大小};ibdata2:4G;ibdata3:8G:autoextend

#galera innodb参数
binlog_format=ROW
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
transaction-isolation=READ-COMMITTED

#galera cluster参数
wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
wsrep_provider_options="gcache.size=300M; gcache.page_size=1G"
wsrep_sst_method=rsync
wsrep_sst_auth=wsrep_sst-user:galera_password

wsrep_cluster_name=MyCluster
wsrep_cluster_address="gcomm://192.168.1.85"
wsrep_node_name=galera.node3
wsrep_node_address="192.168.1.87"

wsrep_cluster_address地址是第1个节点的地址!

  • 启动第3节点的mysql:service mysql start
  • 用本机连接: mysql -h 127.0.0.1 -u root --password=galera_password
    在mysql里执行如下命令:
mysql>SHOW STATUS LIKE 'wsrep%';

如果输出里:wsrep_ready=ON,wsrep_cluster_size=3,就说明第3个节点正常!

至此,Galera Cluster For MySQL安装配置完毕!!!

Donor节点:

在Galera Cluster中,新接入的节点叫Joiner(结合者),给Joiner提供复制的节点叫Donor(供体).
在生产环境中,建议设置一个专用的Donor,集群中其它所有节点的集群ip都指向这个Donor,这个专用的Donor不执行任何来自客户端的SQL请求,也不放在负载均衡里;这样做有以下几点好处:

  1. 数据的一致性:
    因为donor本身不执行任何客户端SQL,所以在这个节点上发生事务冲突的可能性最小,因此,如果发现集群有数据不一致时,donor上的数据应该是整个集群中最准确的.
  2. 数据安全性:
    因为专用donor本身不执行任何客户端SQL,所以在这个节点上发生灾难事件的可能性最小,因此当整个集群宕掉的时候,该节点应该是恢复集群的最佳节点.
  3. 高可用性:
    专用donor可以作为专门的state snapshot donor.因为该节点不服务于客户端,因此当使用此节点进行sst的时候,不影响用户体验,并且前端的负载均衡设备也不需要重新配置.

指定集群ip的方式有三种:

  1. 如上所示,在mysql启动时,加--wsrep_cluster_address参数指定
  2. 在my.cnf中配置wsrep_cluster_address
  3. 直接修改全局变量:set global wsrep_cluster_address="gcomm://192.168.1.85:4567" 另外需要说明的是,gcomm://的值可以有多个,彼此间用逗号隔开,例:"gcomm://192.168.1.85:4567,192.168.1.86:4567"

监控:

[+] 集群完整性检查:

wsrep_cluster_state_uuid:在集群所有节点的值应该是相同的,有不同值的节点,说明其没有连接入集群.
wsrep_cluster_conf_id:正常情况下所有节点上该值是一样的.如果值不同,说明该节点被临时"分区"了.当节点之间网络连接恢复的时候应该会恢复一样的值.
wsrep_cluster_size:如果这个值跟预期的节点数一致,则所有的集群节点已经连接.
wsrep_cluster_status:集群组成的状态.如果不为"Primary",说明出现"分区"或是"split-brain"状况.

[+] 节点状态检查:

wsrep_ready: 该值为ON,则说明可以接受SQL负载.如果为Off,则需要检查wsrep_connected.
wsrep_connected: 如果该值为Off,且wsrep_ready的值也为Off,则说明该节点没有连接到集群.
wsrep_local_state_comment:如果wsrep_connected为On,但wsrep_ready为OFF,则可以从该项查看原因.

[+] 复制健康检查:

wsrep_flow_control_paused:表示复制停止了多长时间.即表明集群因为Slave延迟而慢的程度.值为0~1,越靠近0越好,值为1表示复制完全停止.可优化wsrep_slave_threads的值来改善.
wsrep_cert_deps_distance:有多少事务可以并行应用处理.wsrep_slave_threads设置的值不应该高出该值太多.
wsrep_flow_control_sent:表示该节点已经停止复制了多少次.
wsrep_local_recv_queue_avg:表示slave事务队列的平均长度.slave瓶颈的预兆.
最慢的节点的wsrep_flow_control_sentwsrep_local_recv_queue_avg这两个值最高.这两个值较低的话,相对更好.

[+] 检测慢网络问题:

wsrep_local_send_queue_avg:网络瓶颈的预兆.如果这个值比较高的话,可能存在网络瓶.

[+] 冲突或死锁的数目:

wsrep_last_committed:最后提交的事务数目.
wsrep_local_cert_failureswsrep_local_bf_aborts:回滚,检测到的冲突数目.

备注:

[+] "gcomm://":

"gcomm://"是特殊地址,仅仅是galera cluster初始化启动时候使用,再次启动时需要使用具体的ip地址

[+] 4567端口:

wsrep默认使用4567端口,mysql启动后,除了检查mysql的3306端口外,还要检查此端口!

[+] 删除Galera:

执行以下指令:

service mysql stop

rpm -ev mysql-wsrep-5.6-5.6.23-25.10.el6.x86_64
rpm -ev mysql-wsrep-client-5.6-5.6.23-25.10.el6.x86_64
rpm -ev mysql-wsrep-server-5.6-5.6.23-25.10.el6.x86_64

rpm -ev galera-3-25.3.10-2.el6.x86_64

rm -rf /var/lib/mysql/*
rm -rf /etc/mysql/*
rm /etc/my.cnf
rm /root/.mysql_history
rm /root/.mysql_secret

© 著作权归作者所有

共有 人打赏支持
白石

白石

粉丝 63
博文 28
码字总数 34178
作品 1
海淀
程序员
MariaDB Galera Cluster部署实践

官方文档: 一、 Galera Cluster的工作原理 主要关注点是数据一致性。 事务既可以应用于每个节点,也可以不全部应用。 所以,只要它们配置正确,数据库保持同步。 Galera复制插件不同于传统的...

IT技术栈
04/24
0
0
MySQL的Galera Cluster配置

一、Galera Cluster介绍 Galera是一个MySQL(也支持MariaDB,Percona)的同步多主集群软件。 从用户视角看,一组Galera集群可以看作一个具有多入口的MySQL库,用户可以同时从多个IP读写这个库。...

dodowolf
2017/06/20
0
0
Galera集群---搭建(2个数据节点+1个仲裁)

安装准备 安装包: mariadb-galera-10.0.15-linux-x86_64.tar.gz 测试环境 ip hostname 备注 192.168.1.111 test1 节点1 192.168.1.112 test2 节点2 192.168.1.113 test3 仲裁节点 1,关闭sel......

super李导
2017/06/13
0
0
MySQL集群搭建--多主模式

MySQL集群搭建--多主模式 本文使用的是Galera搭建的MYSQL集群,实现的是每个MYSQL都是主服务器,不存在主从之分。 环境: 三台mysql5.7服务器。 安装mysql集群: 安装前准备: # echo ‘192.1...

jay_zhao
07/26
0
0
MariaDB Galera Cluster 部署(如何快速部署MariaDB集群)

MariaDB作为Mysql的一个分支,在开源项目中已经广泛使用,例如大热的openstack,所以,为了保证服务的高可用性,同时提高系统的负载能力,集群部署是必不可少的。 MariaDB Galera Cluster 介...

OneAPM蓝海讯通
2015/07/03
4.5K
10

没有更多内容

加载失败,请刷新页面

加载更多

下一页

移除或自定义 WordPress 仪表盘欢迎面板

第一次登录 WordPress 后台仪表盘页面,默认都会显示 WordPress 的欢迎面板: 如果我们要移除这个面板,在主题的 functions.php 中添加下面的代码即可: 12 //移除 WordPress 仪表盘欢迎面...

james_laughing
15分钟前
0
0
HashMap实现原理及源码分析

HashMap实现原理及源码分析   哈希表(hash table)也叫散列表,是一种非常重要的数据结构,应用场景及其丰富,许多缓存技术(比如memcached)的核心其实就是在内存中维护一张大的哈希表,...

DemonsI
19分钟前
0
0
eggjs学习笔记

快速初始化 生成项目(要求最低的node版本8.x) npm i egg-init -gegg-init egg-example --type=simplecd egg-examplenpm i 启动项目 npm run dev 配置 环境配置会覆盖默认配置 config...

别人说我名字很长
22分钟前
1
0
Winform Timer控件时间间隔

sender as System.Timers.Timer).Interval = 23 * 60 * 60 * 1000.0;//将时间间隔改为23小时,23小时后重新发生timer_Elapsed事件。 //60000:时间间隔1分钟,300000:时间间隔5分钟,600000:...

笑丶笑
23分钟前
0
0
在win10系统下怎样快速切换任务视图

切换窗口:Alt + Tab 任务视图:Win + Tab (松开键盘界面不会消失) 切换任务视图:Win + Ctrl +左/右 创建新的虚拟桌面:Win + Ctrl + D 关闭当前虚拟桌面:Win + Ctrl + F4...

SummerGao
26分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部