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到各节点
下载地址: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 = 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、启动第一节点

[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|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

[root@master mysql]# ss -tnlp|grep 4567
LISTEN 0 128 :4567 :* users:(("mysqld",28347,11))

查看错误日志信息

[root@master 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 root@localhost: 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_nameValue
wsrep_local_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_protocol_version7
wsrep_last_committed1
wsrep_replicated1
wsrep_replicated_bytes230
wsrep_repl_keys1
wsrep_repl_keys_bytes31
wsrep_repl_data_bytes135
wsrep_repl_other_bytes0
wsrep_received2
wsrep_received_bytes141
wsrep_local_commits0
wsrep_local_cert_failures0
wsrep_local_replays0
wsrep_local_send_queue0
wsrep_local_send_queue_max1
wsrep_local_send_queue_min0
wsrep_local_send_queue_avg0.000000
wsrep_local_recv_queue0
wsrep_local_recv_queue_max1
wsrep_local_recv_queue_min0
wsrep_local_recv_queue_avg0.000000
wsrep_local_cached_downto1
wsrep_flow_control_paused_ns0
wsrep_flow_control_paused0.000000
wsrep_flow_control_sent0
wsrep_flow_control_recv0
wsrep_flow_control_interval[ 100, 100 ]
wsrep_flow_control_interval_low100
wsrep_flow_control_interval_high100
wsrep_flow_control_statusOFF
wsrep_cert_deps_distance1.000000
wsrep_apply_oooe0.000000
wsrep_apply_oool0.000000
wsrep_apply_window1.000000
wsrep_commit_oooe0.000000
wsrep_commit_oool0.000000
wsrep_commit_window1.000000
wsrep_local_state4
wsrep_local_state_commentSynced
wsrep_cert_index_size1
wsrep_cert_bucket_count22
wsrep_gcache_pool_size1590
wsrep_causal_reads0
wsrep_cert_interval0.000000
wsrep_ist_receive_status
wsrep_ist_receive_seqno_start0
wsrep_ist_receive_seqno_current0
wsrep_ist_receive_seqno_end0
wsrep_incoming_addresses192.168.3.12:3306
wsrep_desync_count0
wsrep_evs_delayed
wsrep_evs_evict_list
wsrep_evs_repl_latency1.8541e-05/1.8541e-05/1.8541e-05/0/1
wsrep_evs_stateOPERATIONAL
wsrep_gcomm_uuid04b5f0f9-4b28-11e7-aff6-522901b34a55
wsrep_cluster_conf_id1
wsrep_cluster_size1
wsrep_cluster_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_cluster_statusPrimary
wsrep_connectedON
wsrep_local_bf_aborts0
wsrep_local_index0
wsrep_provider_nameGalera
wsrep_provider_vendorCodership Oy <info@codership.com>
wsrep_provider_version3.20(r7e383f7)
wsrep_readyON

第一节点创建后,其他节点初始化,需要使用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_nameValue
wsrep_local_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_protocol_version7
wsrep_last_committed4
wsrep_replicated4
wsrep_replicated_bytes950
wsrep_repl_keys4
wsrep_repl_keys_bytes124
wsrep_repl_data_bytes570
wsrep_repl_other_bytes0
wsrep_received6
wsrep_received_bytes423
wsrep_local_commits0
wsrep_local_cert_failures0
wsrep_local_replays0
wsrep_local_send_queue0
wsrep_local_send_queue_max1
wsrep_local_send_queue_min0
wsrep_local_send_queue_avg0.000000
wsrep_local_recv_queue0
wsrep_local_recv_queue_max1
wsrep_local_recv_queue_min0
wsrep_local_recv_queue_avg0.000000
wsrep_local_cached_downto1
wsrep_flow_control_paused_ns0
wsrep_flow_control_paused0.000000
wsrep_flow_control_sent0
wsrep_flow_control_recv0
wsrep_flow_control_interval[ 141, 141 ]
wsrep_flow_control_interval_low141
wsrep_flow_control_interval_high141
wsrep_flow_control_statusOFF
wsrep_cert_deps_distance1.000000
wsrep_apply_oooe0.000000
wsrep_apply_oool0.000000
wsrep_apply_window1.000000
wsrep_commit_oooe0.000000
wsrep_commit_oool0.000000
wsrep_commit_window1.000000
wsrep_local_state4
wsrep_local_state_commentSynced
wsrep_cert_index_size1
wsrep_cert_bucket_count22
wsrep_gcache_pool_size2561
wsrep_causal_reads0
wsrep_cert_interval0.000000
wsrep_ist_receive_status
wsrep_ist_receive_seqno_start0
wsrep_ist_receive_seqno_current0
wsrep_ist_receive_seqno_end0
wsrep_incoming_addresses192.168.3.12:3306,192.168.3.13:3306
wsrep_desync_count0
wsrep_evs_delayed
wsrep_evs_evict_list
wsrep_evs_repl_latency0/0/0/0/0
wsrep_evs_stateOPERATIONAL
wsrep_gcomm_uuid04b5f0f9-4b28-11e7-aff6-522901b34a55
wsrep_cluster_conf_id2
wsrep_cluster_size2
wsrep_cluster_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_cluster_statusPrimary
wsrep_connectedON
wsrep_local_bf_aborts0
wsrep_local_index0
wsrep_provider_nameGalera
wsrep_provider_vendorCodership Oy <info@codership.com>
wsrep_provider_version3.20(r7e383f7)
wsrep_readyON

启动第三节点

/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_nameValue
wsrep_local_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_protocol_version7
wsrep_last_committed4
wsrep_replicated0
wsrep_replicated_bytes0
wsrep_repl_keys0
wsrep_repl_keys_bytes0
wsrep_repl_data_bytes0
wsrep_repl_other_bytes0
wsrep_received3
wsrep_received_bytes288
wsrep_local_commits0
wsrep_local_cert_failures0
wsrep_local_replays0
wsrep_local_send_queue0
wsrep_local_send_queue_max1
wsrep_local_send_queue_min0
wsrep_local_send_queue_avg0.000000
wsrep_local_recv_queue0
wsrep_local_recv_queue_max1
wsrep_local_recv_queue_min0
wsrep_local_recv_queue_avg0.000000
wsrep_local_cached_downto0
wsrep_flow_control_paused_ns0
wsrep_flow_control_paused0.000000
wsrep_flow_control_sent0
wsrep_flow_control_recv0
wsrep_flow_control_interval[ 173, 173 ]
wsrep_flow_control_interval_low173
wsrep_flow_control_interval_high173
wsrep_flow_control_statusOFF
wsrep_cert_deps_distance0.000000
wsrep_apply_oooe0.000000
wsrep_apply_oool0.000000
wsrep_apply_window0.000000
wsrep_commit_oooe0.000000
wsrep_commit_oool0.000000
wsrep_commit_window0.000000
wsrep_local_state4
wsrep_local_state_commentSynced
wsrep_cert_index_size0
wsrep_cert_bucket_count22
wsrep_gcache_pool_size1452
wsrep_causal_reads0
wsrep_cert_interval0.000000
wsrep_ist_receive_status
wsrep_ist_receive_seqno_start0
wsrep_ist_receive_seqno_current0
wsrep_ist_receive_seqno_end0
wsrep_incoming_addresses192.168.3.12:3306,192.168.3.13:3306,192.168.3.198:3306
wsrep_desync_count0
wsrep_evs_delayed
wsrep_evs_evict_list
wsrep_evs_repl_latency0/0/0/0/0
wsrep_evs_stateOPERATIONAL
wsrep_gcomm_uuidca7f9f30-4b2b-11e7-a0b7-e29969825862
wsrep_cluster_conf_id3
wsrep_cluster_size3
wsrep_cluster_state_uuid05719dfe-4b28-11e7-a651-ab4ab48f9b60
wsrep_cluster_statusPrimary
wsrep_connectedON
wsrep_local_bf_aborts0
wsrep_local_index2
wsrep_provider_nameGalera
wsrep_provider_vendorCodership Oy <info@codership.com>
wsrep_provider_version3.20(r7e383f7)
wsrep_readyON

简单测试:

在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;
idname
3jack
6tom
9lily
12lucy

4 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
idname
3jack
6tom
9lily
12lucy

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
idname
3jack
6tom
9lily
12lucy

[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
idname
3jack
6tom
9lily
12lucy

至此,PXC安装启动完毕。

MySQL高可用方案-PXC(Percona XtraDB Cluster)环境部署详解

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

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

linux 任务控制软件 supervisor配置详解(转)
https://www.cnblogs.com/ajianbeyourself/p/5534737.html
http://lixcto.blog.51cto.com/4834175/1539136

安装
yum install supervisor

echo_supervisord_conf > /etc/supervisord.conf

编辑 : /etc/supervisord.conf 去掉下面的注释符

[include]
files = /etc/supervisord.d/*.conf

启动supersord服务程序
supervisord -c /etc/supervisord.conf

当你修改了supervisord.conf时,要让配置立即生效,键入:
supervisorctl reload

启动某个任务
supervisorctl start xxx

stop某个任务
supervisorctl stop xxx

子进程配置例子:

[program:notify]
command=php /data/xkx/api/shell/notify.php
directory=/data/xkx/api/shell
user=root
autostart=true
autorestart=true
startretries=3
stdout_logfile=/data/xkx/data/supervisor/%(program_name)s_%(process_num)02d.log
stderr_logfile=/data/xkx/data/supervisor/err_%(program_name)s_%(process_num)02d.log

;stdout_logfile=/a/path ; 子进程的stdout的日志路径,可以指定路径,AUTO,none等三个选项。

                             设置为none的话,将没有日志产生。设置为AUTO的话,将随机找一个地方
                             生成日志文件,而且当supervisord重新启动的时候,以前的日志文件会被
                             清空。当 redirect_stderr=true的时候,sterr也会写进这个日志文件

;stdout_logfile_maxbytes=1MB ; 日志文件最大大小,和[supervisord]中定义的一样。默认为50
;stdout_logfile_backups=10 ; 和[supervisord]定义的一样。默认10
;stdout_capture_maxbytes=1MB ; 这个东西是设定capture管道的大小,当值不为0的时候,子进程可以从stdout

                             发送信息,而supervisor可以根据信息,发送相应的event。
                             默认为0,为0的时候表达关闭管道。。。非必须项

;stdout_events_enabled=false ; 当设置为ture的时候,当子进程由stdout向文件描述符中写日志的时候,将

                             触发supervisord发送PROCESS_LOG_STDOUT类型的event
                             默认为false。。。非必须设置

;stderr_logfile=/a/path ; 这个东西是设置stderr写的日志路径,当redirect_stderr=true。这个就不用

                             设置了,设置了也是白搭。因为它会被写入stdout_logfile的同一个文件中
                             默认为AUTO,也就是随便找个地存,supervisord重启被清空。。非必须设置

;stderr_logfile_maxbytes=1MB ; 这个出现好几次了,就不重复了
;stderr_logfile_backups=10 ; 这个也是
;stderr_capture_maxbytes=1MB ; 这个一样,和stdout_capture一样。 默认为0,关闭状态
;stderr_events_enabled=false ; 这个也是一样,默认为false
;environment=A="1",B="2" ; 这个是该子进程的环境变量,和别的子进程是不共享的
;serverurl=AUTO ;

我们在使用redis的时候可能一般都是用于缓存等服务。

要查询现在redis的服务是什么状态,可以用以下方式:

redis-cli 命令:
redis-cli [-h localhost] [-p 6379] monitor

Dump all the received requests in real time;
监控host为localhost,端口为6380,redis的连接及读写操作

使用这个命令会输出如下信息(实时请求):
+OK
+1289800615.808225 "monitor"
+1289800615.839079 "GET" "name"
+1289800615.853694 "PING"
+1289800615.853783 "PING"
+1289800615.854646 "PING"
+1289800615.854974 "PING"
+1289800615.857693 "PING"
+1289800615.866862 "PING"
+1289800615.871944 "PING"

redis-cli info 命令:
redis-cli info

此命令打印出当前REIDS服务的全部状态信息

三、 showlog功能
Redis 有一个实用的slowlog功能,正如你可以猜到的,可以让你检查运行缓慢的查询。

Slowlog 将会记录运行时间超过Y微秒的最后X条查询. X 和 Y 可以在 redis.conf 或者在运行时通过 CONFIG 命令:

CONFIG SET slowlog-log-slower-than 5000
CONFIG SET slowlog-max-len 25
slowlog-log-slower-than 是用来设置微秒数的, 因此上面的设置将记录执行时间超过5秒的查询. 要获取记录的日志,你可以使用 SLOWLOG GET X 命令, 这里 X 是你想要获取的记录条数:

SLOWLOG GET 10
四、Redis中统计各种数据大小的方法
Redis 内存比较大的话,我们就不太容易查出是哪些(种)键占用的空间。

有一些工具能够提供必要的帮助,比如 redis-rdb-tools 可以直接分析 RDB 文件来生成报告

五、超强、超详细Redis数据库入门教程
http://www.jb51.net/article/56448.htm

http://www.yiibai.com/redis/

六、 Redis操作命令总结
http://www.jb51.net/article/61793.htm

你可以像node npm 工具那样 使用 Composer 提供的包,放到项目中直接使用,这个工具会管理你项目中使用的各种包代码。

安装
php -r "copy('https://install.phpcomposer.com/installer', 'composer-setup.php');"
php composer-setup.php
php -r "unlink('composer-setup.php');"

要全局使用的话,再执行这个命令
sudo mv composer.phar /usr/local/bin/composer

Windows 系统:
找到并进入 PHP 的安装目录(和你在命令行中执行的 php 指令应该是同一套 PHP)。
将 composer.phar 复制到 PHP 的安装目录下面,也就是和 php.exe 在同一级目录。
在 PHP 安装目录下新建一个 composer.bat 文件,并将下列代码保存到此文件中。

@php "%~dp0composer.phar" %*

由于包的管理服务是在国外,国内用户推荐使用国内镜像,用法
https://pkg.phpcomposer.com/#how-to-use-packagist-mirror

以微信overtrue/wechat包为例:
composer.json:
{
"config": {

"vendor-dir": "packages",
"bin-dir": "packages/bin",
"preferred-install": "dist"

},
"require": {

"overtrue/wechat": "~4.0"

},
"repositories": {

"packagist": {
  "type": "composer",
  "url": "https://packagist.phpcomposer.com"
}

}
}

执行:
php composer.phar install
或者
composer install

1.分割 -- split命令

可以指定按行数分割和按字节大小分割两种模式。

(1) 按行数分割

$ split -l 300 large_file.txt new_file_prefix
加上-d,使用数字后缀;加上--verbose,显示分割进度:

$ split -l50000 -d large_file.txt part_ --verbose
(2) 按字节大小分割

$ split -b 10m large_file.log new_file_prefix

2.合并 -- cat命令

$ cat part_* > merge_file.txt

[注] split命令语法:

复制代码
$ split --h
Usage: split [OPTION]... [FILE [PREFIX]]
Output pieces of FILE to PREFIXaa, PREFIXab, ...;
default size is 1000 lines, and default PREFIX is 'x'.

With no FILE, or when FILE is -, read standard input.

Mandatory arguments to long options are mandatory for short options too.
-a, --suffix-length=N generate suffixes of length N (default 2) 后缀名称的长度 (默认为2)

  --additional-suffix=SUFFIX  append an additional SUFFIX to file names

-b, --bytes=SIZE put SIZE bytes per output file 每个输出文件的字节大小
-C, --line-bytes=SIZE put at most SIZE bytes of records per output file 每个输出文件每行的最大字节大小
-d use numeric suffixes starting at 0, not alphabetic 使用数字后缀代替字母后缀

  --numeric-suffixes[=FROM]  same as -d, but allow setting the start value

-e, --elide-empty-files do not generate empty output files with '-n' 不产生空的输出文件

  --filter=COMMAND    write to shell COMMAND; file name is $FILE           写入到shell命令行

-l, --lines=NUMBER put NUMBER lines/records per output file 设定每个输出文件的行数,默认行数是1000行
-n, --number=CHUNKS generate CHUNKS output files; see explanation below 产生chunks文件
-t, --separator=SEP use SEP instead of newline as the record separator; 使用新字符分割

                        '\0' (zero) specifies the NUL character

-u, --unbuffered immediately copy input to output with '-n r/...' 无需缓存

  --verbose           print a diagnostic just before each                  显示分割进度
                        output file is opened
  --help     display this help and exit                                    显示帮助信息
  --version  output version information and exit                           显示版本信息

The SIZE argument is an integer and optional unit (example: 10K is 10*1024).
Units are K,M,G,T,P,E,Z,Y (powers of 1024) or KB,MB,... (powers of 1000).

CHUNKS may be:
N split into N files based on size of input
K/N output Kth of N to stdout
l/N split into N files without splitting lines/records
l/K/N output Kth of N to stdout without splitting lines/records
r/N like 'l' but use round robin distribution
r/K/N likewise but only output Kth of N to stdout

GNU coreutils online help: http://www.gnu.org/software/coreutils/
Full documentation at: http://www.gnu.org/software/coreutils/split
or available locally via: info '(coreutils) split invocation'
复制代码

cat命令语法:

复制代码
$ cat --h
Usage: cat [OPTION]... [FILE]...
Concatenate FILE(s) to standard output.

With no FILE, or when FILE is -, read standard input.

-A, --show-all equivalent to -vET
-b, --number-nonblank number nonempty output lines, overrides -n
-e equivalent to -vE
-E, --show-ends display $ at end of each line
-n, --number number all output lines
-s, --squeeze-blank suppress repeated empty output lines
-t equivalent to -vT
-T, --show-tabs display TAB characters as ^I
-u (ignored)
-v, --show-nonprinting use ^ and M- notation, except for LFD and TAB

  --help     display this help and exit
  --version  output version information and exit

Examples:
cat f - g Output f's contents, then standard input, then g's contents.
cat Copy standard input to standard output.

GNU coreutils online help: http://www.gnu.org/software/coreutils/
Full documentation at: http://www.gnu.org/software/coreutils/cat
or available locally via: info '(coreutils) cat invocation'
复制代码