Percona Xtradb Cluster

Brief introduction:

Percona Server was released by Percona, a leading MySQL consulting firm. Percona Server is a standalone database product that is fully MySQL compatible and can replace the storage engine with XtraDB without changing the code.

The storage engine used in the Percona database is XtraDB, an enhanced version of the InnoDB storage engine in the MySQL database, designed to better update the new performance of the computer's hardware system while also incorporating some new features in a high-performing environment.

XtraDB is built on a solid foundation of InnoDB, using XtraDB with more features, better invocation, more parametric indicators and more extensions.

Percona XtraDB Cluster (PXC) cluster is open source, free MySQL high availability software, can be multi-master simultaneously provide database read and write service, share the database pressure and not waste resources.

Characteristics:

1, synchronous replication, transactions committed at all nodes or not committed.
2, multi-master replication, can be read on any node, write operations.
3, in the parallel application of events from the server, the true meaning of parallel replication.
4, node automatic configuration, data consistency, is no longer asynchronous replication.
5. The current version 5.6 only supports the InnoDB storage engine (the MyISAM storage engine is the experimental phase).
6, the cluster recommended at least three nodes; each node is a normal Percona server, you can make a cluster of existing database servers, or you can split the cluster into separate servers, each with a complete copy of the data.

For more features and restrictions, please refer to the official address: https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html Introduction column.

# servers: 192.168.12.128, 129, 130

First, the use of YUM installation PXC

Yum Install http://www.percona.com/downloads/percona-release/redhat/0.1-3/ percona-release-0.1-3.noarch.rpm  # Install yum source  yum Install percona-xtradb-cluster-  # # Install PXC

Second, the configuration PXC

Shell > vim/etc/My.cnf[mysqld]user=Mysqlsocket=/var/lib/mysql/Mysql.sockdatadir=/var/lib/Mysqllog-bin = mysql-Binbinlog_format=Rowdefault_storage_engine=Innodbinnodb_autoinc_lock_mode=2Wsrep_provider=/usr/lib64/libgalera_smm.sowsrep_cluster_address= Gcomm://192.168.12.128,192.168.12.129,192.168.12.130Wsrep_cluster_name=pxcwsrep_node_address=192.168.12.128Wsrep_sst_method= xtrabackup-V2wsrep_sst_auth="Sstuser:s3cret"[Mysqld_safe]log-error =/var/log/Mysqld_error.logpid-file=/var/run/mysqld/mysqld.pid

# parameter Description:

1, Innodb_autoinc_lock_mode

Self-increment field lock policy, there are three values: 0 all use table locks, 1 can be pre-sentenced to use the new method (the number of rows reserved, the assigned ID is not contiguous), non-predictable use of table locks; 2 Do not use table locks (not FOR replication)

2, Wsrep_slave_threads

The number of threads for replication transactions, which defaults to 1. The number of threads can be increased based on actual throughput, enabling multi-threaded parallel replication (show variables like ' wsrep_slave_threads ';).

3, Wsrep_causal_reads

When the query results are not synchronized to slave, this parameter allows the query action to wait for the results to be synchronized, eventually returning the results (this short delay can be resolved by increasing the number of concurrent threads, improving the performance of the server hardware, optimizing the network environment).

4, Wsrep_sst_method

When a new node in the cluster joins, the method used to transfer the snapshot (data): Xtrabackup, Mysqldump, rsync.

It is recommended to use XTRABACKUP-V2 (second edition of Xtrabackup), mysqldump the slowest, and the same as rsync need global lock, affect efficiency, cause the user cannot write data.

5, Wsrep_sst_auth

The authentication user required to transfer snapshots using Xtrabackup, mysqldump (requires a user to be established in the database, MY.CNF also indicates), rsync does not need to be set.

Third, start PXC

0 # off SELinux, recommended edit/etc/selinux/4444 -4567 -4568 -j ACCEPT

# requires an open port

1, TCP 3306

Database external service port, generally only to the company internal or designated IP open.

2, TCP 4444

SST data Full-volume transmission port, can only be open to the node server. The new node needs to synchronize data usage (default) when joining.

to:13167 [note] Wsrep:sstreceived: 4f33cb6b-598a-11e6-8738-8b3354956db1:7 # Full-volume transmission via SST (Error.log)

3, TCP 4567

The node communicates with the node port. For example, if the port is not opened, the remaining node servers cannot be connected to the cluster (cannot be started).

4, TCP 4568

The IST data Delta Transport port. The transfer data is used when the node is offline and on-line (the incremental transfer is used when the gcache.size is completely stored in the data between the node server and the line).

Gcache.size can be set by the parameter wsrep_provider_options = "gcache.size=512m" in My.cnf, the default 128M. Can be set based on the generated binlog size. The recommended size is greater than the generated binlog.

 2016 -08 - 04  02 :  : 53  13961  [Note] Wsrep: Receiving IST: 3  writesets, Seqnos 7 - Span style= "color: #800080;" >10  2016 -08 -04  02 : 01 : 53  13961  [Note] Wsrep:ist Received: 4f33cb6b-598a-11e6-8738  -8b3354956db1:10  # through IST incremental transfer (Error.log) 

Shell > Service MySQL bootstrap->> Update mysql.user set Password=password ('  123456') where user='root'>'s3cret  '; # Create a Sync user

Iv. Test PXC

1, the remaining two servers are configured separately according to the above steps.

2, where the value of the wsrep_node_address parameter in MY.CNF changed to its own IP.

3, the last authorized user Sstuser No need to create, will automatically synchronize the data of the first node.

4. You can create a database at any node to see if you are synchronizing to the remaining nodes.

5, close any node, then the rest of the nodes write data, start the closed node to see if the data is complete.

6, the node all shut down, restart, test the cluster is normal. (This is the case for a new cluster, to ensure that the first one to start is the most recent node of the data.) )

# A tabloid is wrong:

After all the nodes are closed in the test, they are restarted. The latest node of the data starts successfully (BOOTSTRAP-PXC), and the status is OK.

However, when starting the remaining nodes, the error is as follows (Error.log):

to:12517 [ERROR] Wsrep:gcs/src/gcs _group. cpp: Group_post_state_exchange ():32173more events than the primary component. Data loss is possible. Aborting.

And the state of the first node becomes wsrep_cluster_status to Non-primary, wsrep_local_state_comment to Initialized, and Wsrep_ready to off.

The final data discovery requires deleting the/var/lib/mysql/grastate.dat file (on a node that cannot be started) and then booting. (Unknown cause)

# in real-world environments, at least one node is guaranteed to be online. For example, upgrading hardware in turn, I think most will do so.

# Also this is why the official recommends at least three nodes to do PXC, because when there are only two nodes, the failed node causes the remaining nodes to enter the Non-primary state, causing the cluster to fail.

Five, monitoring indicators

Shell > Mysql-uroot-P123456mysql> Show global Status Like'wsrep_%';+------------------------------+--------------------------------------+| variable_name | Value |+------------------------------+--------------------------------------+| Wsrep_local_state_uuid | 505b98ce-54e4-11e6-99d7-d64eb985b5d7 | | wsrep_protocol_version |7|| wsrep_last_committed |3|| 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 |2|| Wsrep_received_bytes |159|| 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 |2|| Wsrep_local_recv_queue_min |0|| Wsrep_local_recv_queue_avg |0.500000|| 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_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 | A|| Wsrep_gcache_pool_size |1320|| Wsrep_causal_reads |0|| Wsrep_cert_interval |0.000000|| wsrep_incoming_addresses |192.168.12.128: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 | 76fe2163-58DD-11e6-9409-E3323D524FDF | | wsrep_cluster_conf_id |1|| Wsrep_cluster_size |1|| Wsrep_cluster_state_uuid | 505b98ce-54e4-11e6-99d7-d64eb985b5d7 | | 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.16(R5C765EB) | | Wsrep_ready | On |+------------------------------+--------------------------------------+ -RowsinchSet (0.00Sec

# Cluster Status Monitoring

1, Wsrep_cluster_status

Monitor whether the value is Primary, and the possible values are Primary, non-primary, disconnected. Non-Primary is not normal.

2, wsrep_connected, Wsrep_ready

Whether the monitoring parameter value is on, not on is not normal.

3, Wsrep_local_cert_failures, Wsrep_local_bf_aborts

The number of failures caused by the replication conflict, which is 0 optimal.

4, Wsrep_flow_control_sent, WSREP_FLOW_CONTROL_RECV

Traffic control information, send, receive.

5, Wsrep_local_recv_queue

The queue length that is currently received.

# data that needs to be collected

1, Wsrep_local_recv_queue, Wsrep_local_send_queue # Queue Size

2, Wsrep_flow_control_sent, WSREP_FLOW_CONTROL_RECV # Incoming and outgoing traffic

3, wsrep_replicated, wsrep_received # Number of incoming and outgoing transactions

4, Wsrep_replicated_bytes, wsrep_received_bytes # Access Transaction bytes

5, Wsrep_local_cert_failures, Wsrep_local_bf_aborts # Replication conflicts

# with these metrics, you can collect data, alarms, drawings, and more through Zabbix monitoring scripts.

V. Additional

1, Wsrep_cluster_state_uuid

Cluster UUID, all nodes the value must be the same, or a node is not joined to the cluster.

2, wsrep_cluster_conf_id

The number of cluster changes, all nodes should be consistent, or a node has been quarantined.

3, Wsrep_cluster_size

This value shows how many nodes are in the current cluster.

4, Wsrep_cluster_status

Normally, all node values are Primary, which indicates that the cluster is healthy. If non-primary or disconnected, the current node cannot be manipulated.

5, Wsrep_ready

The value on indicates that the node is OK and can accept SQL query, otherwise almost all of the query will error ' ERROR 1047 (08s01) Unknown Command '.

6, wsrep_connected

This value is on for normal, proving that the node is connected to the cluster group.

7, Wsrep_local_state_comment

Typically, returning synced indicates that the node is in a working state, such as returning Initialized, that the node is no longer in normal working condition.

Percona Xtradb Cluster