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.12pxc01CentOS6.5master
192.168.3.13pxc02CentOS6.5slave01
192.168.3.198pxc03CentOS6.5slave02
二、安装 Percona-XtraDB-Cluster
1、下载依赖包percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm到各节点
下载地址:https://www.percona.com/downloads/XtraBackup/LATEST/
安装:
# yum localinstall -y percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
2、下载Percona-XtraDB-Cluster并上传到各节点
下载地址:https://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= 3306server_id= 12socket= /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings= 2slow_query_log_file= /data/mysql/logs/slow.log
long_query_time= 0.1sql_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 dothat you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.3.12,192.168.3.13,192.168.3.198
# In orderforGalera 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= 8wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirementforGalera
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 hostnamewill 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
#AuthenticationforSST 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= 3306server_id= 13socket= /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings= 2slow_query_log_file= /data/mysql/logs/slow.log
long_query_time= 0.1sql_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 dothat you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.3.12,192.168.3.13,192.168.3.198
# In orderforGalera 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= 8wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirementforGalera
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 hostnamewill 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
#AuthenticationforSST 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= 3306server_id= 198socket= /data/mysql/mysql.sock
pid-file = /data/mysql/logs/mysql.pid
log-error = /data/mysql/logs/error.log
log_warnings= 2slow_query_log_file= /data/mysql/logs/slow.log
long_query_time= 0.1sql_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 dothat you need to bootstrap this node
wsrep_cluster_address=gcomm://192.168.3.12,192.168.3.13,192.168.3.198
# In orderforGalera 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= 8wsrep_log_conflicts
# This changes how InnoDB autoincrement locks are managed and is a requirementforGalera
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 hostnamewill 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
#AuthenticationforSST method
wsrep_sst_auth="sstuser:sstuser
三、启动PXC
1、启动第一节点
[root@master ~]# /etc/init.d/mysql bootstrap-pxc
Bootstrapping PXC (Percona XtraDB Cluster)Initializing MySQ[ OK ]se:
Starting MySQL (Percona XtraDB Cluster)....................[ OK ]
查看进程和端口是否启动
[root@master ~]# ps -ef|grepmysql
root27910 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
mysql28347 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
[root@master mysql]# ss -tnlp|grep 4567
LISTEN 0 128 *:4567 *:* users:(("mysqld",28347,11))
查看错误日志信息
[root@master mysql]# tail -f logs/error.log2017-06-07T02:21:30.794514Z 0[Note] WSREP: Node 04b5f0f9 state primary2017-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 disk2017-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: connected2017-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-ab4ab48f9b602017-06-07T02:21:32.021473Z 0 [Note] WSREP: STATE_EXCHANGE: sent state UUID: 0571c6e9-4b28-11e7-b20d-5a8bf004e0062017-06-07T02:21:32.021523Z 0 [Note] WSREP: STATE EXCHANGE: sent state msg: 0571c6e9-4b28-11e7-b20d-5a8bf004e0062017-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-ab4ab48f9b602017-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 available2017-06-07T02:21:32.535815Z 0[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2017-06-07T02:21:32.535826Z 0[Note] InnoDB: Uses event mutexes2017-06-07T02:21:32.535832Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used formemory barrier2017-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 AIO2017-06-07T02:21:32.536865Z 0 [Note] InnoDB: Number of pools: 1
2017-06-07T02:21:32.536999Z 0[Note] InnoDB: Using CPU crc32 instructions2017-06-07T02:21:33.194918Z 0 [Note] InnoDB: Initializing buffer pool, total size = 1G, instances = 8, chunk size =128M2017-06-07T02:21:41.225447Z 0[Note] InnoDB: Completed initialization of buffer pool2017-06-07T02:21:45.228192Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the manpage 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_doublewrite2017-06-07T02:21:45.556158Z 0 [Note] InnoDB: Highest supported fileformat is Barracuda.2017-06-07T02:21:47.397187Z 0 [Note] InnoDB: Created parallel doublewrite buffer at /data/mysql/data/xb_doublewrite, size 31457280bytes2017-06-07T02:21:47.700377Z 0 [Note] InnoDB: Creating shared tablespace fortemporary tables2017-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 12MB.2017-06-07T02:21:48.414626Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96redo 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 forpurge to start2017-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_pool2017-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 indata 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 indata 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 indata 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 0events2017-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 forconnections.
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 tables2017-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 forconnections2017-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 root@localhost: eu1-!5WyRw<7
修改密码:
# mysql -S /data/mysql/mysql.sock -uroot -peu1-!5WyRw<7mysql> 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 |
| 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登录
[root@master ~]# 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 |
| wsrep_provider_version | 3.20(r7e383f7) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------+
启动第三节点
# /etc/init.d/mysql start
确定状态
[root@slave02 ~]# 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 |
| wsrep_provider_version | 3.20(r7e383f7) |
| wsrep_ready | ON |
+----------------------------------+--------------------------------------------------------+
简单测试:
在pxc03上创建数据库tdoa,并在上面建表,且插入几条数据
mysql> create database tdoa charset='utf8mb4';
Query OK,1 row affected (0.05sec)
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.05sec)
mysql>show tables;+----------------+
| Tables_in_tdoa |
+----------------+
| user |
+----------------+
1 row in set (0.00sec)
mysql> insert into user(name) values('jack'),('tom'),('lily'),('lucy');
Query OK,4 rows affected (0.07sec)
Records:4 Duplicates: 0 Warnings: 0mysql> select *from user;+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |
+----+------+
4 rows in set (0.00sec)
mysql>commit;
Query OK,0 rows affected (0.00sec)
mysql> select *from user;+----+------+
| id | name |
+----+------+
| 3 | jack |
| 6 | tom |
| 9 | lily |
| 12 | lucy |
+----+------+
4 rows in set (0.01 sec)
分别在pxc01和pxc02上查看是否有这个表和数据,可以看到数据能够正常同步
[root@master ~]# 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 |[root@slave01~]# 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安装启动完毕。