码迷,mobileinhere.cn
首页 > 数据库 > 详细

mysql高可用方案-pxc(percona xtradb cluster)环境部署详解

时间:2017-06-08 14:59:43      阅读:6716      评论:0      收藏:0      [点我收藏+]

标签:other   exec   member   nod   dex   color   eset   failure   style   

mysql高可用方案-pxc(percona xtradb cluster)环境部署详解


percona xtradb cluster简称pxc。percona xtradb cluster的实现是在原mysql代码上通过galera包将不同的mysql实例连接起来,实现了multi-master的集群架构。
下图中有三个实例,组成了一个集群,而这三个节点与普通的主从架构不同,它们都可以作为主节点,三个节点是对等的,这种一般称为multi-master架构,当有客户端要写入或者读取数据时,随便连接哪个实例都是一样的,读到的数据是相同的,写入某一个节点之后,集群自己会将新数据同步到其它节点上面,这种架构不共享任何数据,是一种高冗余架构。

技术分享
此方案无法解决较大mysql数据场景的数据保存问题,即不能实现分库分表,但是提供了一个高冗余的环境,适合于业务不是很大但是要求数据绝对安全的环境。

percona xtradbcluster提供的特性有:

1.同步复制,事务要么在所有节点提交或不提交。
2.多主复制,可以在任意节点进行写操作。
3.在从服务器上并行应用事件,真正意义上的并行复制。
4.节点自动配置。
5.数据一致性,不再是异步复制。

 

percona xtradbcluster完全兼容mysql和percona server,表现在:
1.数据的兼容性
2.应用程序的兼容性:无需更改应用程序


集群特点:
a.集群是有节点组成的,推荐配置至少3个节点,但是也可以运行在2个节点上。
b.每个节点都是普通的mysql/percona服务器,可以将现有的数据库服务器组成集群,反之,也可以将集群拆分成单独的服务器。
c.每个节点都包含完整的数据副本。

优点如下:

1.当执行一个查询时,在本地节点上执行。因为所有数据都在本地,无需远程访问。
2.无需集中管理。可以在任何时间点失去任何节点,但是集群将照常工作。
3.良好的读负载扩展,任意节点都可以查询。
缺点如下:
1.加入新节点,开销大。需要复制完整的数据。
2.不能有效的解决写缩放问题,所有的写操作都将发生在所有节点上。
3.有多少个节点就有多少重复的数据。

percona xtradb cluster与mysql replication区别在于:
分布式系统的cap理论:
c— 一致性,所有节点的数据一致;
a— 可用性,一个或多个节点失效,不影响服务请求;
p— 分区容忍性,节点间的连接失效,仍然可以处理请求;
任何一个分布式系统,需要满足这三个中的两个。

mysqlreplication: 可用性和分区容忍性;
percona xtradbcluster: 一致性和可用性。
因此mysql replication并不保证数据的一致性,而percona xtradb cluster提供数据一致性

下面,开始安装pxc 5.7.18

一、环境说明

技术分享

ip地址 角色 系统 主机名
192.168.3.12 pxc01 centos6.5 master
192.168.3.13 pxc02 centos6.5 slave01
192.168.3.198 pxc03 centos6.5 slave02

二、安装 percona-xtradb-cluster

1、下载依赖包percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm到各节点
下载地址:www.percona.com/downloads/xtrabackup/latest/
安装:

# yum localinstall -y percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm

 

2、下载percona-xtradb-cluster并上传到各节点
下载地址:www.percona.com/downloads/percona-xtradb-cluster-latest/

安装包列表:
percona-xtradb-cluster-57-5.7.18-29.20.1.el6.x86_64.rpm
percona-xtradb-cluster-client-57-5.7.18-29.20.1.el6.x86_64.rpm
percona-xtradb-cluster-server-57-5.7.18-29.20.1.el6.x86_64.rpm
percona-xtradb-cluster-shared-57-5.7.18-29.20.1.el6.x86_64.rpm

yum localinstall自动解决依赖关系并进行安装

# yum localinstall percona-xtradb-cluster-*.rpm

 

3、所有节点创建mysql组和用户

# groupadd mysql 
# useradd -r -g mysql -s /bin/false mysql

 

4、创建相关目录

# mkdir /data/mysql/{data,logs,tmp} -p
# chown -r mysql.mysql /data

 

5、配置my.cnf文件
将/etc/percona-xtradb-cluster.conf.d/wsrep.cnf拷贝到/etc/my.cnf

# cp /etc/percona-xtradb-cluster.conf.d/wsrep.cnf /etc/my.cnf

 

编辑my.cnf
第一节点 pxc01

[mysqld]
user=mysql
innodb_buffer_pool_size = 1024m
datadir = /data/mysql/data
port = 3306
server_id = 12
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings = 2
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1

sql_mode=no_engine_substitution,strict_trans_tables

# path to galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# cluster connection url contains ips of nodes
#if no ip is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm:192.168.3.12,192.168.3.13,192.168.3.198

# in order for galera to work correctly binlog format should be row
binlog_format=row

# myisam storage engine has only experimental support
default_storage_engine=innodb

# slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# this changes how innodb autoincrement locks are managed and is a requirement for galera
innodb_autoinc_lock_mode=2

# node ip address
wsrep_node_address=192.168.3.12
# cluster name
wsrep_cluster_name=my-pxc-cluster

#if wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc01

#pxc_strict_mode allowed values: disabled,permissive,enforcing,master
pxc_strict_mode=enforcing

# sst method
wsrep_sst_method=xtrabackup-v2

#authentication for sst method
wsrep_sst_auth="sstuser:sstuser"

 

第二节点 pxc02
注意修改 server_id 、wsrep_node_name 、 wsrep_node_address

[mysqld]
user=mysql
innodb_buffer_pool_size = 1024m
datadir = /data/mysql/data
port = 3306
server_id = 13
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings = 2
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1

sql_mode=no_engine_substitution,strict_trans_tables

# path to galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# cluster connection url contains ips of nodes
#if no ip is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm:192.168.3.12,192.168.3.13,192.168.3.198

# in order for galera to work correctly binlog format should be row
binlog_format=row

# myisam storage engine has only experimental support
default_storage_engine=innodb

# slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# this changes how innodb autoincrement locks are managed and is a requirement for galera
innodb_autoinc_lock_mode=2

# node ip address
wsrep_node_address=192.168.3.13
# cluster name
wsrep_cluster_name=my-pxc-cluster

#if wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc02

#pxc_strict_mode allowed values: disabled,permissive,enforcing,master
pxc_strict_mode=enforcing

# sst method
wsrep_sst_method=xtrabackup-v2

#authentication for sst method
wsrep_sst_auth="sstuser:sstuser"

 

第三节点 pxc03
注意修改 server_id 、wsrep_node_name 、 wsrep_node_address

[mysqld]
user=mysql
innodb_buffer_pool_size = 1024m
datadir = /data/mysql/data
port = 3306
server_id = 198
socket = /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings = 2
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.1

sql_mode=no_engine_substitution,strict_trans_tables

# path to galera library
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so

# cluster connection url contains ips of nodes
#if no ip is found, this implies that a new cluster needs to be created,
#in order to do that you need to bootstrap this node
wsrep_cluster_address=gcomm:192.168.3.12,192.168.3.13,192.168.3.198

# in order for galera to work correctly binlog format should be row
binlog_format=row

# myisam storage engine has only experimental support
default_storage_engine=innodb

# slave thread to use
wsrep_slave_threads= 8

wsrep_log_conflicts

# this changes how innodb autoincrement locks are managed and is a requirement for galera
innodb_autoinc_lock_mode=2

# node ip address
wsrep_node_address=192.168.3.198
# cluster name
wsrep_cluster_name=my-pxc-cluster

#if wsrep_node_name is not specified, then system hostname will be used
wsrep_node_name=pxc03

#pxc_strict_mode allowed values: disabled,permissive,enforcing,master
pxc_strict_mode=enforcing

# sst method
wsrep_sst_method=xtrabackup-v2

#authentication for sst method
wsrep_sst_auth="sstuser:sstuser

 

三、启动pxc

1、启动第一节点

[[email protected] ~]# /etc/init.d/mysql bootstrap-pxc
bootstrapping pxc (percona xtradb cluster)initializing mysq[ ok ]se: 
starting mysql (percona xtradb cluster)....................[ ok ]

 

查看进程和端口是否启动

[[email protected] ~]# ps -ef|grep mysql
root 27910 1 0 10:21 pts/2 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/logs/mysql.pid --wsrep-new-cluster
mysql 28347 27910 22 10:21 pts/2 00:00:23 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql/data --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.so --wsrep-new-cluster --log-error=/data/mysql/logs/error.log --pid-file=/data/mysql/logs/mysql.pid --socket=/data/mysql/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1

 

[[email protected] mysql]# ss -tnlp|grep 4567
listen 0 128 *:4567 *:* users:(("mysqld",28347,11))

查看错误日志信息

[[email protected] mysql]# tail -f logs/error.log

2017-06-07t02:21:30.794514z 0 [note] wsrep: node 04b5f0f9 state primary
2017-06-07t02:21:30.794674z 0 [note] wsrep: current view of cluster as seen by this node
view (view_id(prim,04b5f0f9,1)
memb {
04b5f0f9,0
}
joined {
}
left {
}
partitioned {
}
)
2017-06-07t02:21:30.794745z 0 [note] wsrep: save the discovered primary-component to disk
2017-06-07t02:21:30.794882z 0 [note] wsrep: discarding pending addr without uuid: tcp:192.168.3.12:4567
2017-06-07t02:21:30.794926z 0 [note] wsrep: discarding pending addr proto entry 0x7f16aefcfcc0
2017-06-07t02:21:30.794988z 0 [note] wsrep: discarding pending addr without uuid: tcp:192.168.3.13:4567
2017-06-07t02:21:30.795028z 0 [note] wsrep: discarding pending addr proto entry 0x7f16aefcff00
2017-06-07t02:21:30.795060z 0 [note] wsrep: discarding pending addr without uuid: tcp:192.168.3.198:4567
2017-06-07t02:21:30.795098z 0 [note] wsrep: discarding pending addr proto entry 0x7f16aefcffc0
2017-06-07t02:21:30.795158z 0 [note] wsrep: gcomm: connected
2017-06-07t02:21:30.795328z 0 [note] wsrep: shifting closed -> open (to: 0)
2017-06-07t02:21:30.797700z 0 [note] wsrep: waiting for sst/ist to complete.
2017-06-07t02:21:31.391420z 0 [note] wsrep: new component: primary = yes, bootstrap = no, my_idx = 0, memb_num = 1
2017-06-07t02:21:32.020639z 0 [note] wsrep: starting new group from scratch: 05719dfe-4b28-11e7-a651-ab4ab48f9b60
2017-06-07t02:21:32.021473z 0 [note] wsrep: state_exchange: sent state uuid: 0571c6e9-4b28-11e7-b20d-5a8bf004e006
2017-06-07t02:21:32.021523z 0 [note] wsrep: state exchange: sent state msg: 0571c6e9-4b28-11e7-b20d-5a8bf004e006
2017-06-07t02:21:32.021547z 0 [note] wsrep: state exchange: got state msg: 0571c6e9-4b28-11e7-b20d-5a8bf004e006 from 0 (pxc01)
2017-06-07t02:21:32.021571z 0 [note] wsrep: quorum results:
version = 4,
component = primary,
conf_id = 0,
members = 1/1 (primary/total),
act_id = 0,
last_appl. = -1,
protocols = 0/7/3 (gcs/repl/appl),
group uuid = 05719dfe-4b28-11e7-a651-ab4ab48f9b60
2017-06-07t02:21:32.021599z 0 [note] wsrep: flow-control interval: [100, 100]
2017-06-07t02:21:32.021615z 0 [note] wsrep: restored state open -> joined (0)
2017-06-07t02:21:32.021976z 0 [note] wsrep: member 0.0 (pxc01) synced with group.
2017-06-07t02:21:32.022019z 0 [note] wsrep: shifting joined -> synced (to: 0)
2017-06-07t02:21:32.022101z 2 [note] wsrep: new cluster view: global state: 05719dfe-4b28-11e7-a651-ab4ab48f9b60:0, view# 1: primary, number of nodes: 1, my index: 0, protocol version 3
2017-06-07t02:21:32.023374z 0 [note] wsrep: sst complete, seqno: 0
2017-06-07t02:21:32.535748z 0 [note] innodb: punch hole support available
2017-06-07t02:21:32.535815z 0 [note] innodb: mutexes and rw_locks use gcc atomic builtins
2017-06-07t02:21:32.535826z 0 [note] innodb: uses event mutexes
2017-06-07t02:21:32.535832z 0 [note] innodb: gcc builtin __sync_synchronize() is used for memory barrier
2017-06-07t02:21:32.535837z 0 [note] innodb: compressed tables use zlib 1.2.3
2017-06-07t02:21:32.535842z 0 [note] innodb: using linux native aio
2017-06-07t02:21:32.536865z 0 [note] innodb: number of pools: 1
2017-06-07t02:21:32.536999z 0 [note] innodb: using cpu crc32 instructions
2017-06-07t02:21:33.194918z 0 [note] innodb: initializing buffer pool, total size = 1g, instances = 8, chunk size = 128m
2017-06-07t02:21:41.225447z 0 [note] innodb: completed initialization of buffer pool
2017-06-07t02:21:45.228192z 0 [note] innodb: if the mysqld execution user is authorized, page cleaner thread priority can be changed. see the man page of setpriority().
2017-06-07t02:21:45.554968z 0 [note] innodb: crash recovery did not find the parallel doublewrite buffer at /data/mysql/data/xb_doublewrite
2017-06-07t02:21:45.556158z 0 [note] innodb: highest supported file format is barracuda.
2017-06-07t02:21:47.397187z 0 [note] innodb: created parallel doublewrite buffer at /data/mysql/data/xb_doublewrite, size 31457280 bytes
2017-06-07t02:21:47.700377z 0 [note] innodb: creating shared tablespace for temporary tables
2017-06-07t02:21:47.700541z 0 [note] innodb: setting file ./ibtmp1 size to 12 mb. physically writing the file full; please wait ...
2017-06-07t02:21:48.413452z 0 [note] innodb: file ./ibtmp1 size is now 12 mb.
2017-06-07t02:21:48.414626z 0 [note] innodb: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2017-06-07t02:21:48.414664z 0 [note] innodb: 32 non-redo rollback segment(s) are active.
2017-06-07t02:21:48.416853z 0 [note] innodb: waiting for purge to start
2017-06-07t02:21:48.468115z 0 [note] innodb: percona xtradb (http:www.percona.com) 5.7.18-15 started; log sequence number 2542677
2017-06-07t02:21:48.468456z 0 [note] plugin federated is disabled.
2017-06-07t02:21:48.468710z 0 [note] innodb: loading buffer pool(s) from /data/mysql/data/ib_buffer_pool
2017-06-07t02:21:48.472685z 0 [note] innodb: buffer pool(s) load completed at 170607 10:21:48
2017-06-07t02:21:48.477798z 0 [note] found ca.pem, server-cert.pem and server-key.pem in data directory. trying to enable ssl support using them.
2017-06-07t02:21:48.477886z 0 [note] skipping generation of ssl certificates as certificate files are present in data directory.
2017-06-07t02:21:48.515627z 0 [warning] ca certificate ca.pem is self signed.
2017-06-07t02:21:48.544887z 0 [note] skipping generation of rsa key pair as key files are present in data directory.
2017-06-07t02:21:48.545210z 0 [note] server hostname (bind-address): *; port: 3306
2017-06-07t02:21:48.545499z 0 [note] ipv6 is available.
2017-06-07t02:21:48.545557z 0 [note] - :: resolves to ::;
2017-06-07t02:21:48.545717z 0 [note] server socket created on ip: ::.
2017-06-07t02:21:48.611502z 0 [note] event scheduler: loaded 0 events
2017-06-07t02:21:48.651338z 2 [note] wsrep: initialized wsrep sidno 2
2017-06-07t02:21:48.651403z 2 [note] wsrep: wsrep_notify_cmd is not defined, skipping notification.
2017-06-07t02:21:48.651443z 2 [note] wsrep: repl protocols: 7 (3, 2)
2017-06-07t02:21:48.651455z 2 [note] wsrep: assign initial position for certification: 0, protocol version: 3
2017-06-07t02:21:48.651862z 0 [note] wsrep: service thread queue flushed.
2017-06-07t02:21:48.765979z 0 [note] /usr/sbin/mysqld: ready for connections.
version: 5.7.18-15-57 socket: /data/mysql/mysql.sock port: 3306 percona xtradb cluster (gpl), release rel15, revision 7693d6e, wsrep version 29.20, wsrep_29.20
2017-06-07t02:21:48.766032z 0 [note] executing select * from information_schema.tables; to get a list of tables using the deprecated partition engine. you may use the startup option --disable-partition-engine-check to skip this check. 
2017-06-07t02:21:48.766043z 0 [note] beginning of list of non-natively partitioned tables
2017-06-07t02:21:48.766034z 2 [note] wsrep: gcache history reset: old(00000000-0000-0000-0000-000000000000:0) -> new(05719dfe-4b28-11e7-a651-ab4ab48f9b60:0)
2017-06-07t02:21:48.767985z 2 [note] wsrep: synchronized with group, ready for connections
2017-06-07t02:21:48.768012z 2 [note] wsrep: wsrep_notify_cmd is not defined, skipping notification.
2017-06-07t02:21:48.824186z 0 [note] end of list of non-natively partitioned tables

 

去error.log日志搜索关键字password找到默认root密码,登录后修改密码(如果不修改密码无法进行其他sql操作)
2017-06-07t02:21:11.228127z 1 [note] a temporary password is generated for [email protected]: eu1-!5wyrw<7

修改密码:

# mysql -s /data/mysql/mysql.sock -uroot -peu1-!5wyrw<7
mysql> alter user root@localhost identified by 123456;

 

第一节点启动后,检查cluster的状态。
当前第一节点是primary。

mysql> show status like %wsrep%;
+----------------------------------+--------------------------------------+
| variable_name | value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 1 |
| wsrep_replicated | 1 |
| wsrep_replicated_bytes | 230 |
| wsrep_repl_keys | 1 |
| wsrep_repl_keys_bytes | 31 |
| wsrep_repl_data_bytes | 135 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 2 |
| wsrep_received_bytes | 141 |
| 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 | 1 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 100, 100 ] |
| wsrep_flow_control_interval_low | 100 |
| wsrep_flow_control_interval_high | 100 |
| wsrep_flow_control_status | off |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | synced |
| wsrep_cert_index_size | 1 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1590 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.3.12:3306 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 1.8541e-05/1.8541e-05/1.8541e-05/0/1 |
| wsrep_evs_state | operational |
| wsrep_gcomm_uuid | 04b5f0f9-4b28-11e7-aff6-522901b34a55 |
| wsrep_cluster_conf_id | 1 |
| wsrep_cluster_size | 1 |
| wsrep_cluster_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| 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 |
+----------------------------------+--------------------------------------+

 

第一节点创建后,其他节点初始化,需要使用xtrabackup工具来备份,然后恢复。
所以,需要创建一个用户用于备份。

mysql> create user sstuser@localhost identified by sstuser;
mysql> grant reload,lock tables,replication client,process on *.* to sstuser@localhost;
mysql> flush privileges;

验证sstuser登录

[[email protected] ~]# mysql -usstuser -psstuser -s /data/mysql/mysql.sock -e show databases
warning: using a password on the command line interface can be insecure.
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+

 

启动第二节点

# /etc/init.d/mysql start
确认第二节点的状态

# mysql -usstuser -psstuser -s /data/mysql/mysql.sock -e show status like "wsrep%"
warning: using a password on the command line interface can be insecure.
+----------------------------------+--------------------------------------+
| variable_name | value |
+----------------------------------+--------------------------------------+
| wsrep_local_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
| wsrep_replicated | 4 |
| wsrep_replicated_bytes | 950 |
| wsrep_repl_keys | 4 |
| wsrep_repl_keys_bytes | 124 |
| wsrep_repl_data_bytes | 570 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 6 |
| wsrep_received_bytes | 423 |
| 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 | 1 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 141, 141 ] |
| wsrep_flow_control_interval_low | 141 |
| wsrep_flow_control_interval_high | 141 |
| wsrep_flow_control_status | off |
| wsrep_cert_deps_distance | 1.000000 |
| wsrep_apply_oooe | 0.000000 |
| wsrep_apply_oool | 0.000000 |
| wsrep_apply_window | 1.000000 |
| wsrep_commit_oooe | 0.000000 |
| wsrep_commit_oool | 0.000000 |
| wsrep_commit_window | 1.000000 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | synced |
| wsrep_cert_index_size | 1 |
| wsrep_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 2561 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.3.12:3306,192.168.3.13: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 | 04b5f0f9-4b28-11e7-aff6-522901b34a55 |
| wsrep_cluster_conf_id | 2 |
| wsrep_cluster_size | 2 |
| wsrep_cluster_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| 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 |
+----------------------------------+--------------------------------------+

 

启动第三节点

# /etc/init.d/mysql start

确定状态

[[email protected] ~]# mysql -s /data/mysql/mysql.sock -usstuser -psstuser -e show status like "wsrep%";
warning: using a password on the command line interface can be insecure.
+----------------------------------+--------------------------------------------------------+
| variable_name | value |
+----------------------------------+--------------------------------------------------------+
| wsrep_local_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| wsrep_protocol_version | 7 |
| wsrep_last_committed | 4 |
| 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 | 3 |
| wsrep_received_bytes | 288 |
| 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 | 0 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0.000000 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_interval | [ 173, 173 ] |
| wsrep_flow_control_interval_low | 173 |
| wsrep_flow_control_interval_high | 173 |
| wsrep_flow_control_status | off |
| 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_cert_bucket_count | 22 |
| wsrep_gcache_pool_size | 1452 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0.000000 |
| wsrep_ist_receive_status | |
| wsrep_ist_receive_seqno_start | 0 |
| wsrep_ist_receive_seqno_current | 0 |
| wsrep_ist_receive_seqno_end | 0 |
| wsrep_incoming_addresses | 192.168.3.12:3306,192.168.3.13:3306,192.168.3.198: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 | ca7f9f30-4b2b-11e7-a0b7-e29969825862 |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | 05719dfe-4b28-11e7-a651-ab4ab48f9b60 |
| wsrep_cluster_status | primary |
| wsrep_connected | on |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 2 |
| wsrep_provider_name | galera |
| wsrep_provider_vendor | codership oy <info@codership.com> |
| wsrep_provider_version | 3.20(r7e383f7) |
| wsrep_ready | on |
+----------------------------------+--------------------------------------------------------+

 


简单测试:

在pxc03上创建数据库tdoa,并在上面建表,且插入几条数据

mysql> create database tdoa charset=utf8mb4;
query ok, 1 row affected (0.05 sec)

mysql> use tdoa;
database changed
mysql> create table user(id int unsigned primary key auto_increment,name varchar(30));
query ok, 0 rows affected (0.05 sec)

mysql> show tables;
+----------------+
| tables_in_tdoa |
+----------------+
| user |
+----------------+
1 row in set (0.00 sec)

mysql> insert into user(name) values(jack),(tom),(lily),(lucy);
query ok, 4 rows affected (0.07 sec)
records: 4 duplicates: 0 warnings: 0

mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |
+----+------+
4 rows in set (0.00 sec)

mysql> commit;
query ok, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |
+----+------+
4 rows in set (0.01 sec)

分别在pxc01和pxc02上查看是否有这个表和数据,可以看到数据能够正常同步

[[email protected] ~]# mysql -s /data/mysql/mysql.sock -uroot -p123456 -e use tdoa;show tables;select * from user;
warning: using a password on the command line interface can be insecure.
+----------------+
| tables_in_tdoa |
+----------------+
| user |
+----------------+
+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |


[[email protected] ~]# mysql -s /data/mysql/mysql.sock -uroot -p123456 -e use tdoa;show tables;select * from user;
warning: using a password on the command line interface can be insecure.
+----------------+
| tables_in_tdoa |
+----------------+
| user |
+----------------+
+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |
+----+------+

至此,pxc安装启动完毕。

mysql高可用方案-pxc(percona xtradb cluster)环境部署详解

标签:other   exec   member   nod   dex   color   eset   failure   style   

原文地址:www.cnblogs.com/reblue520/p/6962599.html

(2)
(3)
   
举报
评论 一句话评论(0
0条  
登录后才能评论!
2014 mobileinhere.cn 版权所有 京icp备13008772号-2
华人娱乐注册