文档章节

Galera_Cluster_For_MySQL集群安装

白石
 白石
发布于 2015/05/05 16:04
字数 4543
阅读 603
收藏 15
点赞 2
评论 0

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

© 著作权归作者所有

共有 人打赏支持
白石

白石

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

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

IT技术栈 ⋅ 04/24 ⋅ 0

MySQL的Galera Cluster配置

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

dodowolf ⋅ 2017/06/20 ⋅ 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

Galera Cluster 实现mysql群集

参考:http://blog.itpub.net/14431099/viewspace-1316643/ http://lovestoned.blog.51cto.com/2904444/1617817 http://www.itbaofeng.com/?p=59 http://lansgg.blog.51cto.com/5675165/1180......

linux_python ⋅ 2015/07/09 ⋅ 0

高可用的mysql galera集群部署

一:集群机器介绍四台机器:1.balance节点安装haproxy和keepalived机器名:balance01 172.18.48.1balance02 172.18.48.22.galera节点安装mysql数据库和galera机器名:galera01 172.18.48.3g...

双叶天下 ⋅ 2014/05/13 ⋅ 0

MariaDB Galera Cluster 部署(如何快速部署MariaDB集群)

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

OneAPM蓝海讯通 ⋅ 2015/07/03 ⋅ 10

haproxy+keepalived来实现mariadb galera cluster的高可用架构

haproxy+keepalived来实现mariadb galera cluster的高可用架构 网络拓扑图如下: mariadb galera cluster集群基于wsrep协议可以实现mysql多主复制架构,详细说明如下: galera cluster复制:...

zhuhc1988 ⋅ 2016/12/25 ⋅ 0

配置galera+nginx 实现 mariadb、mysql数据库多主模式

需求 、解决问题点: 1:现有的mysql数据主从模式,数据同步延迟; 2:mysql主从进程经常崩溃,数据找平困难; 3:数据库主从或者主主模式数据查询、写入慢; 4:mariadb或者mysql数据库 节点...

kjh2007abc ⋅ 2016/11/17 ⋅ 0

Percona XtraDB Cluster 构建

Percona XtraDB Cluster 构建 Percona XtraDB Cluster是MySQL高可用性和可扩展性的解决方案.底层使用Galera插件提供多主支持。应对常见的多写可以是用haproxy直接做代理,减小单节点写压力;...

wild-life ⋅ 2016/08/01 ⋅ 0

Mariadb galera cluster 安装配置

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

litterMo ⋅ 2016/08/01 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Java Web如何操作Cookie的添加修改和删除

创建Cookie对象 Cookie cookie = new Cookie("id", "1"); 修改Cookie值 cookie.setValue("2"); 设置Cookie有效期和删除Cookie cookie.setMaxAge(24*60*60); // Cookie有效时间 co......

二营长意大利炮 ⋅ 今天 ⋅ 0

【每天一个JQuery特效】淡入淡出显示或隐藏窗口

我是JQuery新手爱好者,有时间就练练代码,防止手生,争取每天一个JQuery练习,在这个博客记录下学习的笔记。 本特效主要采用fadeIn()和fadeOut()方法显示淡入淡出的显示效果显示或隐藏元...

Rhymo-Wu ⋅ 今天 ⋅ 0

Spring JDBC使用方法

普通实现: 1、创建数据表customer。 可以使用任何数据库实现,在项目中要引入相应数据库驱动包并配置相应数据库连接。 2、创建Customer pojo。 Customer类的属性对应数据库的属性,除了为每...

霍淇滨 ⋅ 今天 ⋅ 0

Contos 7 安装Jenkins

Jenkins是一款能提高效率的软件,它能帮你把软件开发过程形成工作流,典型的工作流包括以下几个步骤 开发 提交 编译 测试 发布 有了Jenkins的帮助,在这5步中,除了第1步,后续的4步都是自动...

欧虞山 ⋅ 今天 ⋅ 0

revel

revel install go get github.com/revel/revelgo get github.com/revel/cmd create new app revel new git.oschina.net/zdglf/myapp run app revel run git.oschina.net/zdglf/myapp ot......

zdglf ⋅ 今天 ⋅ 0

49. Group Anagrams - LeetCode

Question 49. Group Anagrams Solution 思路:维护一个map,key是输入数组中的字符串(根据字符排好序) Java实现: public List<List<String>> groupAnagrams(String[] strs) { Map<Strin......

yysue ⋅ 今天 ⋅ 0

spring Email

使用spring发Email其实就是使用spring自己封装携带的一个javamail.JavaMailSenderImpl类而已。这个类可以当一个普通的java对象来使用,也可以通过把它配置变成spring Bean的方式然后注入使用...

BobwithB ⋅ 今天 ⋅ 0

spark 整理的一些知识

Spark 知识点 请描述spark RDD原理与特征? RDD全称是resilient distributed dataset(具有弹性的分布式数据集)。一个RDD仅仅是一个分布式的元素集合。在Spark中,所有工作都表示为创建新的...

tuoleisi77 ⋅ 今天 ⋅ 0

思考

时间一天天过感觉自己有在成长吗?最怕的是时光匆匆而过,自己没有收获!下面总结下最近自己的思考。 认识自己 认识另一个自己,人们常说要虚心听取别人意见和建议。然而人往往是很难做到的,...

hello_hp ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部