Galera Cluster

Galera Cluster 介绍

Galera Cluster：集成了Galera插件的MySQL集群，是一种新型的，数据不共享的，高度冗余的高可用方案，目前Galera Cluster有两个版本，分别是Percona Xtradb Cluster及MariaDB Cluster，Galera本身是具有多主特性的，即采用multi-master的集群架构，是一个既稳健，又在数据一致性、完整性及高性能方面有出色表现的高可用解决方案

Galera Cluster特点

多主架构：真正的多点读写的集群，在任何时候读写数据，都是最新的
同步复制：改善了主从复制延迟问题，基本上达到了实时同步
并发复制：从节点APPLY数据时，支持并行执行，更好的性能
故障切换：在出现数据库故障时，因支持多点写入，切换容易
热插拔：在服务期间，如果数据库挂了，只要监控程序发现的够快，不可服务时间就会非常少。在节点故障期间，节点本身对集群的影响非常小
自动节点克隆：在新增节点，或者停机维护时，增量数据或者基础数据不需要人工手动备份提供，Galera Cluster会自动拉取在线节点数据，最终集群会变为一致
对应用透明：集群的维护，对应用程序是透明的

Galera Cluster 缺点

任何更新的事务都需要全局验证通过，才会在其他节点上执行，则集群性能由集群中最差性能节点决定（一般集群节点配置都是一样的）
新节点加入或延后较大的节点重新加入需全量拷贝数据(SST，State Snapshot Transfer),作为donor( 贡献者，如：同步数据时的提供者)的节点在同步过程中无法提供读写
只支持innodb存储引擎的表

Galera Cluster工作过程

mariadb集群的启动 mariadb 集群方案_mariadb集群的启动

Galera Cluster官方文档：

http://galeracluster.com/documentation-webpages/galera-documentation.pdf
http://galeracluster.com/documentation-webpages/index.html
https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html
https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/

Galera Cluster 包括两个组件

Galera replication library (galera-3)
WSREP：MySQL extended with the Write Set Replication

WSREP复制实现：

PXC：Percona XtraDB Cluster，是Percona对Galera的实现
参考仓库：

https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basea

MariaDB Galera Cluster：

参考仓库：
```
https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-amd64/
```
注意：两者都需要至少三个节点，不能安装mysql server 或 mariadb-server

PXC 原理

MySQL集群高可用_MySQL高可用_03

PXC最常使用如下4个端口号：

3306：数据库对外服务的端口号
4444：请求SST的端口号
4567：组成员之间进行沟通的端口号
4568：用于传输IST的端口号

PXC中涉及到的重要概念和核心参数：

（1）集群中节点的数量：整个集群中节点数量应该控制在最少3个、最多8个的范围内。最少3个节点是为了防止出现脑裂现象，因为只有在2个节点下才会出现此现象。脑裂现象的标志就是输入任何命令，返回的结果都是unknown command。节点在集群中，会因新节点的加入或故障、同步失效等原因发生状态的切换。

（2）节点状态的变化阶段：

MySQL集群高可用_MySQL高可用_04

open：节点启动成功，尝试连接到集群时的状态
primary：节点已处于集群中，在新节点加入并选取donor进行数据同步时的状态
joiner：节点处于等待接收同步文件时的状态
joined：节点完成数据同步工作，尝试保持和集群进度一致时的状态
synced：节点正常提供服务时的状态，表示已经同步完成并和集群进度保持一致
donor：节点处于为新加入的节点提供全量数据时的状态

备注：donor节点就是数据的贡献者，如果一个新节点加入集群，此时又需要大量数据的SST数据传输，就有可能因此而拖垮整个集群的性能，所以在生产环境中，如果数据量较小，还可以使用SST全量数据传输，但如果数据量很大就不建议使用这种方式，可以考虑先建立主从关系，然后再加入集群。

（3）节点的数据传输方式：

SST：State Snapshot Transfer，全量数据传输
IST：Incremental State Transfer，增量数据传输

SST数据传输有xtrabackup、mysqldump和rsync三种方式，而增量数据传输就只有一种方式xtrabackup，但生产环境中一般数据量较小时，可以使用SST全量数据传输，但也只使用xtrabackup方法。

（4）GCache模块：在PXC中一个特别重要的模块，它的核心功能就是为每个节点缓存当前最新的写集。如果有新节点加入进来，就可以把新数据的增量传递给新节点，而不需要再使用SST传输方式，这样可以让节点更快地加入集群中，涉及如下参数：

gcache.size：缓存写集增量信息的大小，它的默认大小是128MB，通过wsrep_provider_options参数设置，建议调整为2GB~4GB范围，足够的空间便于缓存更多的增量信息。
gcache.mem_size：GCache中内存缓存的大小，适度调大可以提高整个集群的性能
gcache.page_size：如果内存不够用（GCache不足），就直接将写集写入磁盘文件中

实战案例：Percona XtraDB Cluster(PXC 5.7）

1 环境准备

四台主机：

pxc1:10.0.0.7
pxc2:10.0.0.17
pxc3:10.0.0.27
pxc4:10.0.0.37

OS 版本目前不支持CentOS 8

[root@pxc1 ~]#cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)

关闭防火墙和SELinux，保证时间同步
注意：如果已经安装MySQL，必须卸载

2 安装 Percona XtraDB Cluster 5.7

此处使用清华大学yum源，官方源太慢了
[root@pxc1 ~]#vim /etc/yum.repos.d/pxc.repo
[percona]
name=percona_repo
baseurl =
https://mirrors.tuna.tsinghua.edu.cn/percona/release/$releasever/RPMS/$basearch
enabled = 1
gpgcheck = 0

[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.17:/etc/yum.repos.d
[root@pxc1 ~]#scp /etc/yum.repos.d/pxc.repo 10.0.0.27:/etc/yum.repos.d

#在三个节点都安装好PXC 5.7
[root@pxc1 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc2 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc3 ~]#yum install Percona-XtraDB-Cluster-57 -y

[root@pxc1 ~]#rpm -ql Percona-XtraDB-Cluster-server-57
/etc/logrotate.d/mysql
/etc/my.cnf
/etc/my.cnf.d
/etc/percona-xtradb-cluster.cnf
/etc/percona-xtradb-cluster.conf.d
/etc/percona-xtradb-cluster.conf.d/mysqld.cnf
/etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
/etc/percona-xtradb-cluster.conf.d/wsrep.cnf
/etc/sysconfig/mysql.bootstrap
/etc/xinetd.d/mysqlchk
/usr/bin/clustercheck
/usr/bin/innochecksum
/usr/bin/lz4_decompress
/usr/bin/my_print_defaults
/usr/bin/myisam_ftdump
/usr/bin/myisamchk
/usr/bin/myisamlog
/usr/bin/myisampack
/usr/bin/mysql-systemd
/usr/bin/mysql_install_db
/usr/bin/mysql_plugin
/usr/bin/mysql_secure_installation
/usr/bin/mysql_ssl_rsa_setup
/usr/bin/mysql_tzinfo_to_sql
/usr/bin/mysql_upgrade
/usr/bin/mysqld_multi
/usr/bin/mysqld_safe
/usr/bin/mysqldumpslow
/usr/bin/mysqltest
/usr/bin/perror
/usr/bin/ps_mysqld_helper
/usr/bin/pyclustercheck
/usr/bin/replace
/usr/bin/resolve_stack_dump
/usr/bin/resolveip
/usr/bin/wsrep_sst_common
/usr/bin/wsrep_sst_mysqldump
/usr/bin/wsrep_sst_rsync
/usr/bin/wsrep_sst_rsync_wan
/usr/bin/wsrep_sst_xtrabackup-v2
/usr/bin/zlib_decompress
/usr/lib/systemd/system/mysql.service
/usr/lib/systemd/system/mysql@.service
/usr/lib64/galera3/libgalera_smm.so
/usr/lib64/libgalera_smm.so
/usr/lib64/mysql/mecab
/usr/lib64/mysql/mecab/dic
/usr/lib64/mysql/mecab/dic/ipadic
/usr/lib64/mysql/mecab/dic/ipadic/char.bin
/usr/lib64/mysql/mecab/dic/ipadic/dicrc
/usr/lib64/mysql/mecab/dic/ipadic/left-id.def
/usr/lib64/mysql/mecab/dic/ipadic/matrix.bin
/usr/lib64/mysql/mecab/dic/ipadic/pos-id.def
/usr/lib64/mysql/mecab/dic/ipadic/rewrite.def
/usr/lib64/mysql/mecab/dic/ipadic/right-id.def
/usr/lib64/mysql/mecab/dic/ipadic/sys.dic
/usr/lib64/mysql/mecab/dic/ipadic/unk.dic
/usr/lib64/mysql/mecab/etc
/usr/lib64/mysql/mecab/etc/mecabrc
/usr/lib64/mysql/plugin/adt_null.so
/usr/lib64/mysql/plugin/audit_log.so
/usr/lib64/mysql/plugin/auth.so
/usr/lib64/mysql/plugin/auth_pam.so
/usr/lib64/mysql/plugin/auth_pam_compat.so
/usr/lib64/mysql/plugin/auth_socket.so
/usr/lib64/mysql/plugin/auth_test_plugin.so
/usr/lib64/mysql/plugin/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/connection_control.so
/usr/lib64/mysql/plugin/daemon_example.ini
/usr/lib64/mysql/plugin/data_masking.ini
/usr/lib64/mysql/plugin/data_masking.so
/usr/lib64/mysql/plugin/debug/adt_null.so
/usr/lib64/mysql/plugin/debug/audit_log.so
/usr/lib64/mysql/plugin/debug/auth.so
/usr/lib64/mysql/plugin/debug/auth_pam.so
/usr/lib64/mysql/plugin/debug/auth_pam_compat.so
/usr/lib64/mysql/plugin/debug/auth_socket.so
/usr/lib64/mysql/plugin/debug/auth_test_plugin.so
/usr/lib64/mysql/plugin/debug/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/debug/connection_control.so
/usr/lib64/mysql/plugin/debug/data_masking.so
/usr/lib64/mysql/plugin/debug/dialog.so
/usr/lib64/mysql/plugin/debug/group_replication.so
/usr/lib64/mysql/plugin/debug/ha_example.so
/usr/lib64/mysql/plugin/debug/innodb_engine.so
/usr/lib64/mysql/plugin/debug/keyring_file.so
/usr/lib64/mysql/plugin/debug/keyring_udf.so
/usr/lib64/mysql/plugin/debug/keyring_vault.so
/usr/lib64/mysql/plugin/debug/libdaemon_example.so
/usr/lib64/mysql/plugin/debug/libfnv1a_udf.so
/usr/lib64/mysql/plugin/debug/libfnv_udf.so
/usr/lib64/mysql/plugin/debug/libmemcached.so
/usr/lib64/mysql/plugin/debug/libmurmur_udf.so
/usr/lib64/mysql/plugin/debug/libpluginmecab.so
/usr/lib64/mysql/plugin/debug/libtest_framework.so
/usr/lib64/mysql/plugin/debug/libtest_services.so
/usr/lib64/mysql/plugin/debug/libtest_services_threaded.so
/usr/lib64/mysql/plugin/debug/libtest_session_detach.so
/usr/lib64/mysql/plugin/debug/libtest_session_in_thd.so
/usr/lib64/mysql/plugin/debug/libtest_session_info.so
/usr/lib64/mysql/plugin/debug/libtest_sql_2_sessions.so
/usr/lib64/mysql/plugin/debug/libtest_sql_all_col_types.so
/usr/lib64/mysql/plugin/debug/libtest_sql_cmds_1.so
/usr/lib64/mysql/plugin/debug/libtest_sql_commit.so
/usr/lib64/mysql/plugin/debug/libtest_sql_complex.so
/usr/lib64/mysql/plugin/debug/libtest_sql_errors.so
/usr/lib64/mysql/plugin/debug/libtest_sql_lock.so
/usr/lib64/mysql/plugin/debug/libtest_sql_processlist.so
/usr/lib64/mysql/plugin/debug/libtest_sql_replication.so
/usr/lib64/mysql/plugin/debug/libtest_sql_shutdown.so
/usr/lib64/mysql/plugin/debug/libtest_sql_sqlmode.so
/usr/lib64/mysql/plugin/debug/libtest_sql_stored_procedures_functions.so
/usr/lib64/mysql/plugin/debug/libtest_sql_views_triggers.so
/usr/lib64/mysql/plugin/debug/libtest_x_sessions_deinit.so
/usr/lib64/mysql/plugin/debug/libtest_x_sessions_init.so
/usr/lib64/mysql/plugin/debug/locking_service.so
/usr/lib64/mysql/plugin/debug/mypluglib.so
/usr/lib64/mysql/plugin/debug/mysql_no_login.so
/usr/lib64/mysql/plugin/debug/mysqlx.so
/usr/lib64/mysql/plugin/debug/qa_auth_client.so
/usr/lib64/mysql/plugin/debug/qa_auth_interface.so
/usr/lib64/mysql/plugin/debug/qa_auth_server.so
/usr/lib64/mysql/plugin/debug/query_response_time.so
/usr/lib64/mysql/plugin/debug/replication_observers_example_plugin.so
/usr/lib64/mysql/plugin/debug/rewrite_example.so
/usr/lib64/mysql/plugin/debug/rewriter.so
/usr/lib64/mysql/plugin/debug/scalability_metrics.so
/usr/lib64/mysql/plugin/debug/semisync_master.so
/usr/lib64/mysql/plugin/debug/semisync_slave.so
/usr/lib64/mysql/plugin/debug/test_security_context.so
/usr/lib64/mysql/plugin/debug/test_udf_services.so
/usr/lib64/mysql/plugin/debug/udf_example.so
/usr/lib64/mysql/plugin/debug/validate_password.so
/usr/lib64/mysql/plugin/debug/version_token.so
/usr/lib64/mysql/plugin/dialog.so
/usr/lib64/mysql/plugin/group_replication.so
/usr/lib64/mysql/plugin/ha_example.so
/usr/lib64/mysql/plugin/innodb_engine.so
/usr/lib64/mysql/plugin/keyring_file.so
/usr/lib64/mysql/plugin/keyring_udf.so
/usr/lib64/mysql/plugin/keyring_vault.so
/usr/lib64/mysql/plugin/libdaemon_example.so
/usr/lib64/mysql/plugin/libfnv1a_udf.so
/usr/lib64/mysql/plugin/libfnv_udf.so
/usr/lib64/mysql/plugin/libmemcached.so
/usr/lib64/mysql/plugin/libmurmur_udf.so
/usr/lib64/mysql/plugin/libpluginmecab.so
/usr/lib64/mysql/plugin/libtest_framework.so
/usr/lib64/mysql/plugin/libtest_services.so
/usr/lib64/mysql/plugin/libtest_services_threaded.so
/usr/lib64/mysql/plugin/libtest_session_detach.so
/usr/lib64/mysql/plugin/libtest_session_in_thd.so
/usr/lib64/mysql/plugin/libtest_session_info.so
/usr/lib64/mysql/plugin/libtest_sql_2_sessions.so
/usr/lib64/mysql/plugin/libtest_sql_all_col_types.so
/usr/lib64/mysql/plugin/libtest_sql_cmds_1.so
/usr/lib64/mysql/plugin/libtest_sql_commit.so
/usr/lib64/mysql/plugin/libtest_sql_complex.so
/usr/lib64/mysql/plugin/libtest_sql_errors.so
/usr/lib64/mysql/plugin/libtest_sql_lock.so
/usr/lib64/mysql/plugin/libtest_sql_processlist.so
/usr/lib64/mysql/plugin/libtest_sql_replication.so
/usr/lib64/mysql/plugin/libtest_sql_shutdown.so
/usr/lib64/mysql/plugin/libtest_sql_sqlmode.so
/usr/lib64/mysql/plugin/libtest_sql_stored_procedures_functions.so
/usr/lib64/mysql/plugin/libtest_sql_views_triggers.so
/usr/lib64/mysql/plugin/libtest_x_sessions_deinit.so
/usr/lib64/mysql/plugin/libtest_x_sessions_init.so
/usr/lib64/mysql/plugin/locking_service.so
/usr/lib64/mysql/plugin/mypluglib.so
/usr/lib64/mysql/plugin/mysql_no_login.so
/usr/lib64/mysql/plugin/mysqlx.so
/usr/lib64/mysql/plugin/qa_auth_client.so
/usr/lib64/mysql/plugin/qa_auth_interface.so
/usr/lib64/mysql/plugin/qa_auth_server.so
/usr/lib64/mysql/plugin/query_response_time.so
/usr/lib64/mysql/plugin/replication_observers_example_plugin.so
/usr/lib64/mysql/plugin/rewrite_example.so
/usr/lib64/mysql/plugin/rewriter.so
/usr/lib64/mysql/plugin/scalability_metrics.so
/usr/lib64/mysql/plugin/semisync_master.so
/usr/lib64/mysql/plugin/semisync_slave.so
/usr/lib64/mysql/plugin/test_security_context.so
/usr/lib64/mysql/plugin/test_udf_services.so
/usr/lib64/mysql/plugin/udf_example.so
/usr/lib64/mysql/plugin/validate_password.so
/usr/lib64/mysql/plugin/version_token.so
/usr/sbin/mysqld
/usr/sbin/mysqld-debug
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32/INFO_BIN
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32/INFO_SRC
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32/README
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32/README-wsrep
/usr/share/doc/Percona-XtraDB-Cluster-server-57-5.7.32/wsrep.cnf
/usr/share/doc/percona-xtradb-cluster-galera
/usr/share/doc/percona-xtradb-cluster-galera/COPYING
/usr/share/doc/percona-xtradb-cluster-galera/LICENSE.asio
/usr/share/doc/percona-xtradb-cluster-galera/LICENSE.chromium
/usr/share/doc/percona-xtradb-cluster-galera/README
/usr/share/doc/percona-xtradb-cluster-galera/README-MySQL
/usr/share/man/man1/innochecksum.1.gz
/usr/share/man/man1/lz4_decompress.1.gz
/usr/share/man/man1/my_print_defaults.1.gz
/usr/share/man/man1/myisam_ftdump.1.gz
/usr/share/man/man1/myisamchk.1.gz
/usr/share/man/man1/myisamlog.1.gz
/usr/share/man/man1/myisampack.1.gz
/usr/share/man/man1/mysql.server.1.gz
/usr/share/man/man1/mysql_install_db.1.gz
/usr/share/man/man1/mysql_plugin.1.gz
/usr/share/man/man1/mysql_secure_installation.1.gz
/usr/share/man/man1/mysql_ssl_rsa_setup.1.gz
/usr/share/man/man1/mysql_tzinfo_to_sql.1.gz
/usr/share/man/man1/mysql_upgrade.1.gz
/usr/share/man/man1/mysqld_multi.1.gz
/usr/share/man/man1/mysqld_safe.1.gz
/usr/share/man/man1/mysqldumpslow.1.gz
/usr/share/man/man1/mysqlman.1.gz
/usr/share/man/man1/perror.1.gz
/usr/share/man/man1/replace.1.gz
/usr/share/man/man1/resolve_stack_dump.1.gz
/usr/share/man/man1/resolveip.1.gz
/usr/share/man/man1/zlib_decompress.1.gz
/usr/share/man/man8/mysqld.8.gz
/usr/share/percona-xtradb-cluster
/usr/share/percona-xtradb-cluster/bulgarian
/usr/share/percona-xtradb-cluster/bulgarian/errmsg.sys
/usr/share/percona-xtradb-cluster/charsets
/usr/share/percona-xtradb-cluster/charsets/Index.xml
/usr/share/percona-xtradb-cluster/charsets/README
/usr/share/percona-xtradb-cluster/charsets/armscii8.xml
/usr/share/percona-xtradb-cluster/charsets/ascii.xml
/usr/share/percona-xtradb-cluster/charsets/cp1250.xml
/usr/share/percona-xtradb-cluster/charsets/cp1251.xml
/usr/share/percona-xtradb-cluster/charsets/cp1256.xml
/usr/share/percona-xtradb-cluster/charsets/cp1257.xml
/usr/share/percona-xtradb-cluster/charsets/cp850.xml
/usr/share/percona-xtradb-cluster/charsets/cp852.xml
/usr/share/percona-xtradb-cluster/charsets/cp866.xml
/usr/share/percona-xtradb-cluster/charsets/dec8.xml
/usr/share/percona-xtradb-cluster/charsets/geostd8.xml
/usr/share/percona-xtradb-cluster/charsets/greek.xml
/usr/share/percona-xtradb-cluster/charsets/hebrew.xml
/usr/share/percona-xtradb-cluster/charsets/hp8.xml
/usr/share/percona-xtradb-cluster/charsets/keybcs2.xml
/usr/share/percona-xtradb-cluster/charsets/koi8r.xml
/usr/share/percona-xtradb-cluster/charsets/koi8u.xml
/usr/share/percona-xtradb-cluster/charsets/latin1.xml
/usr/share/percona-xtradb-cluster/charsets/latin2.xml
/usr/share/percona-xtradb-cluster/charsets/latin5.xml
/usr/share/percona-xtradb-cluster/charsets/latin7.xml
/usr/share/percona-xtradb-cluster/charsets/macce.xml
/usr/share/percona-xtradb-cluster/charsets/macroman.xml
/usr/share/percona-xtradb-cluster/charsets/swe7.xml
/usr/share/percona-xtradb-cluster/czech
/usr/share/percona-xtradb-cluster/czech/errmsg.sys
/usr/share/percona-xtradb-cluster/danish
/usr/share/percona-xtradb-cluster/danish/errmsg.sys
/usr/share/percona-xtradb-cluster/dictionary.txt
/usr/share/percona-xtradb-cluster/dutch
/usr/share/percona-xtradb-cluster/dutch/errmsg.sys
/usr/share/percona-xtradb-cluster/english
/usr/share/percona-xtradb-cluster/english/errmsg.sys
/usr/share/percona-xtradb-cluster/errmsg-utf8.txt
/usr/share/percona-xtradb-cluster/estonian
/usr/share/percona-xtradb-cluster/estonian/errmsg.sys
/usr/share/percona-xtradb-cluster/fill_help_tables.sql
/usr/share/percona-xtradb-cluster/french
/usr/share/percona-xtradb-cluster/french/errmsg.sys
/usr/share/percona-xtradb-cluster/german
/usr/share/percona-xtradb-cluster/german/errmsg.sys
/usr/share/percona-xtradb-cluster/greek
/usr/share/percona-xtradb-cluster/greek/errmsg.sys
/usr/share/percona-xtradb-cluster/hungarian
/usr/share/percona-xtradb-cluster/hungarian/errmsg.sys
/usr/share/percona-xtradb-cluster/innodb_memcached_config.sql
/usr/share/percona-xtradb-cluster/install_rewriter.sql
/usr/share/percona-xtradb-cluster/italian
/usr/share/percona-xtradb-cluster/italian/errmsg.sys
/usr/share/percona-xtradb-cluster/japanese
/usr/share/percona-xtradb-cluster/japanese/errmsg.sys
/usr/share/percona-xtradb-cluster/korean
/usr/share/percona-xtradb-cluster/korean/errmsg.sys
/usr/share/percona-xtradb-cluster/magic
/usr/share/percona-xtradb-cluster/mysql-log-rotate
/usr/share/percona-xtradb-cluster/mysql.server
/usr/share/percona-xtradb-cluster/mysql_security_commands.sql
/usr/share/percona-xtradb-cluster/mysql_sys_schema.sql
/usr/share/percona-xtradb-cluster/mysql_system_tables.sql
/usr/share/percona-xtradb-cluster/mysql_system_tables_data.sql
/usr/share/percona-xtradb-cluster/mysql_test_data_timezone.sql
/usr/share/percona-xtradb-cluster/mysqld_multi.server
/usr/share/percona-xtradb-cluster/norwegian
/usr/share/percona-xtradb-cluster/norwegian-ny
/usr/share/percona-xtradb-cluster/norwegian-ny/errmsg.sys
/usr/share/percona-xtradb-cluster/norwegian/errmsg.sys
/usr/share/percona-xtradb-cluster/polish
/usr/share/percona-xtradb-cluster/polish/errmsg.sys
/usr/share/percona-xtradb-cluster/portuguese
/usr/share/percona-xtradb-cluster/portuguese/errmsg.sys
/usr/share/percona-xtradb-cluster/pxc_cluster_view.sql
/usr/share/percona-xtradb-cluster/romanian
/usr/share/percona-xtradb-cluster/romanian/errmsg.sys
/usr/share/percona-xtradb-cluster/russian
/usr/share/percona-xtradb-cluster/russian/errmsg.sys
/usr/share/percona-xtradb-cluster/serbian
/usr/share/percona-xtradb-cluster/serbian/errmsg.sys
/usr/share/percona-xtradb-cluster/slovak
/usr/share/percona-xtradb-cluster/slovak/errmsg.sys
/usr/share/percona-xtradb-cluster/spanish
/usr/share/percona-xtradb-cluster/spanish/errmsg.sys
/usr/share/percona-xtradb-cluster/swedish
/usr/share/percona-xtradb-cluster/swedish/errmsg.sys
/usr/share/percona-xtradb-cluster/ukrainian
/usr/share/percona-xtradb-cluster/ukrainian/errmsg.sys
/usr/share/percona-xtradb-cluster/uninstall_rewriter.sql
/usr/share/percona-xtradb-cluster/wsrep_notify
/var/lib/galera
/var/lib/mysql
/var/lib/mysql-files
/var/lib/mysql-keyring
/var/run/mysqld

3 在各个节点上分别配置mysql及集群配置文件

/etc/my.cnf为主配置文件，当前版本中，其余的配置文件都放在/etc/percona-xtradb-cluster.conf.d目录里，包括mysqld.cnf，mysqld_safe.cnf，wsrep.cnf 三个文件

#主配置文件不需要修改
[root@pxc1 ~]#cat /etc/my.cnf
# The Percona XtraDB Cluster 5.7 configuration file.
...省略...
!includedir /etc/my.cnf.d/
!includedir /etc/percona-xtradb-cluster.conf.d/

[root@pxc1 ~]#ls /etc/my.cnf.d/
[root@pxc1 ~]#ls /etc/percona-xtradb-cluster.conf.d/
mysqld.cnf mysqld_safe.cnf wsrep.cnf

#下面配置文件不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld.cnf
...省略...
[client]
socket=/var/lib/mysql/mysql.sock
[mysqld]
server-id=1 #建议各个节点不同
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
log-bin #建议启用，非必须项
log_slave_updates
expire_logs_days=7

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

#下面配置文件不需要修改
[root@pxc1 ~]#cat /etc/percona-xtradb-cluster.conf.d/mysqld_safe.cnf
...省略...
[mysqld_safe]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/lib/mysql/mysql.sock
nice = 0

#PXC的配置文件必须修改
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc1 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27 #三个节点的IP
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.7 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-1 #各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释,同一集群内多个节点的验证用户和密码信息必须一致


[root@pxc2 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.17 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-2 #各个节点，指定自已节点名称
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释

[root@pxc3 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.27 #各个节点，指定自已的IP
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-3 #各个节点，指定自已的IP
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass" #取消本行注释

注意：尽管Galera Cluster不再需要通过binlog的形式进行同步，但还是建议在配置文件中开启二进制日志功能，原因是后期如果有新节点需要加入，老节点通过SST全量传输的方式向新节点传输数据，很可能会拖垮集群性能，所以让新节点先通过binlog方式完成同步后再加入集群会是一种更好的选择

配置文件各项配置意义

配置项目	说明
wsrep_provider	指定 Galera 库的路径
wsrep_cluster_name	Galera 集群的名称
wsrep_cluster_address	Galera 集群中各节点地址。地址使用组通信协议 `gcomm://(group communication)`
wsrep_node_name	本节点在 Galera 集群中的名称
wsrep_node_address	本节点在 Galera 集群中的通信地址
wsrep_sst_method	State Snapshot Transfer (SST) 使用的传输方法。可用方法有 `mysqldump`、`rsync` 和 `xtrabackup`。前两者在传输时都需要对 Donor 加全局只读锁 (`FLUSH TABLES WITH READ LOCK`)，`xtrabackup` 则不需要（它使用 Percona 自己提供的 backup lock）。强烈建议采用 `xtrabackup`
wsrep_sst_auth	在 SST 传输时需要用到的认证凭据，格式为："用户:密码"
pxc_strict_mode	是否限制 PXC 启用正在试用阶段的功能，`ENFORCING` 是默认值，表示不启用
binlog_format	二进制日志的格式。Galera 只支持 `row` 格式的二进制日志
default_storage_engine	指定默认存储引擎。Galera 的复制功能只支持 `InnoDB`
innodb_autoinc_lock_mode	只能设置为 `2`，设置为 `0` 或 `1` 时会无法正确处理死锁问题

4 启动PXC集群中第一个节点

root@pxc1 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port
Peer Address:Port
udp UNCONN 0 0 127.0.0.1:323
*:*
udp UNCONN 0 0 ::1:323
:::*
tcp LISTEN 0 128 *:22
*:* tcp LISTEN 0 100
127.0.0.1:25 *:*
tcp LISTEN 0 128 :::22
:::*
tcp LISTEN 0 100 ::1:25
:::*

#启动第一个节点
[root@pxc1 ~]#systemctl start mysql@bootstrap.service
[root@pxc1 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer
Address:Port
udp UNCONN 0 0 127.0.0.1:323
*:*
udp UNCONN 0 0 ::1:323
:::*
tcp LISTEN 0 128 *:22
*:*
tcp LISTEN 0 128 *:4567
*:*
tcp LISTEN 0 100 127.0.0.1:25
*:*
tcp LISTEN 0 80 :::3306
:::*
tcp LISTEN 0 128 :::22
:::*
tcp LISTEN 0 100 ::1:25
:::*

#查看root密码
[root@pxc1 ~]#grep "temporary password" /var/log/mysqld.log
2019-11-30T02:53:54.292659Z 1 [Note] A temporary password is generated for root@localhost: =tWFP0oRJl8t

[root@pxc1 ~]#mysql -uroot -p'=tWFP0oRJl8t'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.27-30-57-log

Copyright (c) 2009-2019 Percona LLC and/or its affiliates
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

#修改root密码
mysql> alter user 'root'@'localhost' identified by 'ayaka';
Query OK, 0 rows affected (0.01 sec)

#创建相关用户并授权
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cretPass';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
'sstuser'@'localhost';
Query OK, 0 rows affected (0.01 sec)

#查看相关变量
mysql> SHOW VARIABLES LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_OSU_method
Value: TOI
*************************** 2. row ***************************
Variable_name: wsrep_RSU_commit_timeout
Value: 5000
*************************** 3. row ***************************
Variable_name: wsrep_auto_increment_control
Value: ON
*************************** 4. row ***************************
Variable_name: wsrep_causal_reads
alue: OFF
*************************** 5. row ***************************
Variable_name: wsrep_certification_rules
Value: strict
*************************** 6. row ***************************
Variable_name: wsrep_certify_nonPK
Value: ON
*************************** 7. row ***************************
Variable_name: wsrep_cluster_address
Value: gcomm://10.0.0.7,10.0.0.17,10.0.0.27
*************************** 8. row ***************************
Variable_name: wsrep_cluster_name
Value: pxc-cluster
*************************** 9. row ***************************
Variable_name: wsrep_convert_LOCK_to_trx
Value: OFF
*************************** 10. row ***************************
Variable_name: wsrep_data_home_dir
Value: /var/lib/mysql/
*************************** 11. row ***************************
Variable_name: wsrep_dbug_option
Value:
*************************** 12. row ***************************
Variable_name: wsrep_debug
Value: OFF
*************************** 13. row ***************************
Variable_name: wsrep_desync
Value: OFF
*************************** 14. row ***************************
Variable_name: wsrep_dirty_reads
Value: OFF
*************************** 15. row ***************************
Variable_name: wsrep_drupal_282555_workaround
Value: OFF
*************************** 16. row ***************************
Variable_name: wsrep_forced_binlog_format
Value: NONE
*************************** 17. row ***************************
Variable_name: wsrep_load_data_splitting
Value: ON
*************************** 18. row ***************************
Variable_name: wsrep_log_conflicts
Value: ON
*************************** 19. row ***************************
Variable_name: wsrep_max_ws_rows
Value: 0
*************************** 20. row ***************************
Variable_name: wsrep_max_ws_size
Value: 2147483647
*************************** 21. row ***************************
Variable_name: wsrep_node_address
Value: 10.0.0.7
*************************** 22. row ***************************
Variable_name: wsrep_node_incoming_address
Value: AUTO
*************************** 23. row ***************************
Variable_name: wsrep_node_name
Value: pxc-cluster-node-1
*************************** 24. row ***************************
Variable_name: wsrep_notify_cmd
Value:
*************************** 25. row ***************************
Variable_name: wsrep_on
Value: ON
*************************** 26. row ***************************
Variable_name: wsrep_preordered
Value: OFF
*************************** 27. row ***************************
Variable_name: wsrep_provider
Value: /usr/lib64/galera3/libgalera_smm.so
*************************** 28. row ***************************
Variable_name: wsrep_provider_options
Value: base_dir = /var/lib/mysql/; base_host = 10.0.0.7; base_port =
4567; cert.log_conflicts = no; cert.optimistic_pa = yes; debug = no;
evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask =
0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S;
evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S;
evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period =
PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window
= 10; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S;
evs.use_aggregate = true; evs.user_send_window = 4; evs.version = 0;
evs.view_forget_timeout = P1D; gcache.dir = /var/lib/mysql/;
gcache.freeze_purge_at_seqno = -1; gcache.keep_pages_count = 0;
gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name =
/var/lib/mysql//galera.cache; gcache.page_size = 128M; gcache.recover = no;
gcache.size = 128M; gcomm.thread_prio = ; gcs.fc_debug = 0; gcs.fc_factor = 1;
gcs.fc_limit = 100; gcs.fc_master_slave = no; gcs
*************************** 29. row ***************************
Variable_name: wsrep_recover
Value: OFF
*************************** 30. row ***************************
Variable_name: wsrep_reject_queries
Value: NONE
*************************** 31. row ***************************
Variable_name: wsrep_replicate_myisam
Value: OFF
*************************** 32. row ***************************
Variable_name: wsrep_restart_slave
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_retry_autocommit
Value: 1
*************************** 34. row ***************************
Variable_name: wsrep_slave_FK_checks
Value: ON
*************************** 35. row ***************************
Variable_name: wsrep_slave_UK_checks
Value: OFF
*************************** 36. row ***************************
Variable_name: wsrep_slave_threads
Value: 8
*************************** 37. row ***************************
Variable_name: wsrep_sst_auth
Value: ********
*************************** 38. row ***************************
Variable_name: wsrep_sst_donor
Value:
*************************** 39. row ***************************
Variable_name: wsrep_sst_donor_rejects_queries
Value: OFF
*************************** 40. row ***************************
Variable_name: wsrep_sst_method
Value: xtrabackup-v2
*************************** 41. row ***************************
Variable_name: wsrep_sst_receive_address
Value: AUTO
*************************** 42. row ***************************
Variable_name: wsrep_start_position
Value: 00000000-0000-0000-0000-000000000000:-1
*************************** 43. row ***************************
Variable_name: wsrep_sync_wait
Value: 0
43 rows in set (0.01 sec)


#查看相关状态变量
mysql> SHOW STATUS LIKE 'wsrep%'\G
*************************** 1. row ***************************
Variable_name: wsrep_local_state_uuid
Value: aad2c02e-131c-11ea-9294-b2e80a6c08c4
*************************** 2. row ***************************
Variable_name: wsrep_protocol_version
Value: 9
*************************** 3. row ***************************
Variable_name: wsrep_last_applied
Value: 3
*************************** 4. row ***************************
Variable_name: wsrep_last_committed
Value: 3
*************************** 5. row ***************************
Variable_name: wsrep_replicated
Value: 3
*************************** 6. row ***************************
Variable_name: wsrep_replicated_bytes
Value: 760
*************************** 7. row ***************************
Variable_name: wsrep_repl_keys
Value: 3
*************************** 8. row ***************************
Variable_name: wsrep_repl_keys_bytes
Value: 96
*************************** 9. row ***************************
Variable_name: wsrep_repl_data_bytes
Value: 465
*************************** 10. row ***************************
Variable_name: wsrep_repl_other_bytes
Value: 0
*************************** 11. row ***************************
Variable_name: wsrep_received
Value: 2
*************************** 12. row ***************************
Variable_name: wsrep_received_bytes
Value: 150
*************************** 13. row ***************************
Variable_name: wsrep_local_commits
Value: 0
*************************** 14. row ***************************
Variable_name: wsrep_local_cert_failures
Value: 0
*************************** 15. row ***************************
Variable_name: wsrep_local_replays
Value: 0
*************************** 16. row ***************************
Variable_name: wsrep_local_send_queue
Value: 0
*************************** 17. row ***************************
Variable_name: wsrep_local_send_queue_max
Value: 1
*************************** 18. row ***************************
Variable_name: wsrep_local_send_queue_min
Value: 0
*************************** 19. row ***************************
Variable_name: wsrep_local_send_queue_avg
Value: 0.000000
*************************** 20. row ***************************
Variable_name: wsrep_local_recv_queue
Value: 0
*************************** 21. row ***************************
Variable_name: wsrep_local_recv_queue_max
Value: 2
*************************** 22. row ***************************
Variable_name: wsrep_local_recv_queue_min
Value: 0
*************************** 23. row ***************************
Variable_name: wsrep_local_recv_queue_avg
Value: 0.500000
*************************** 24. row ***************************
Variable_name: wsrep_local_cached_downto
Value: 1
*************************** 25. row ***************************
Variable_name: wsrep_flow_control_paused_ns
Value: 0
*************************** 26. row ***************************
Variable_name: wsrep_flow_control_paused
Value: 0.000000
*************************** 27. row ***************************
Variable_name: wsrep_flow_control_sent
Value: 0
*************************** 28. row ***************************
Variable_name: wsrep_flow_control_recv
Value: 0
*************************** 29. row ***************************
Variable_name: wsrep_flow_control_interval
Value: [ 100, 100 ]
*************************** 30. row ***************************
Variable_name: wsrep_flow_control_interval_low
Value: 100
*************************** 31. row ***************************
Variable_name: wsrep_flow_control_interval_high
Value: 100
*************************** 32. row ***************************
Variable_name: wsrep_flow_control_status
Value: OFF
*************************** 33. row ***************************
Variable_name: wsrep_cert_deps_distance
Value: 1.000000
*************************** 34. row ***************************
Variable_name: wsrep_apply_oooe
Value: 0.000000
*************************** 35. row ***************************
Variable_name: wsrep_apply_oool
Value: 0.000000
*************************** 36. row ***************************
Variable_name: wsrep_apply_window
Value: 1.000000
*************************** 37. row ***************************
Variable_name: wsrep_commit_oooe
Value: 0.000000
*************************** 38. row ***************************
Variable_name: wsrep_commit_oool
Value: 0.000000
*************************** 39. row ***************************
Variable_name: wsrep_commit_window
Value: 1.000000
*************************** 40. row ***************************
Variable_name: wsrep_local_state
Value: 4
*************************** 41. row ***************************
Variable_name: wsrep_local_state_comment
Value: Synced
*************************** 42. row ***************************
Variable_name: wsrep_cert_index_size
Value: 1
*************************** 43. row ***************************
Variable_name: wsrep_cert_bucket_count
Value: 22
*************************** 44. row ***************************
Variable_name: wsrep_gcache_pool_size
Value: 2200
*************************** 45. row ***************************
Variable_name: wsrep_causal_reads
Value: 0
*************************** 46. row ***************************
Variable_name: wsrep_cert_interval
Value: 0.000000
*************************** 47. row ***************************
Variable_name: wsrep_open_transactions
Value: 0
*************************** 48. row ***************************
Variable_name: wsrep_open_connections
Value: 0
*************************** 49. row ***************************
Variable_name: wsrep_ist_receive_status
Value:
*************************** 50. row ***************************
Variable_name: wsrep_ist_receive_seqno_start
Value: 0
*************************** 51. row ***************************
Variable_name: wsrep_ist_receive_seqno_current
Value: 0
*************************** 52. row ***************************
Variable_name: wsrep_ist_receive_seqno_end
Value: 0
*************************** 53. row ***************************
Variable_name: wsrep_incoming_addresses
Value: 10.0.0.7:3306
*************************** 54. row ***************************
Variable_name: wsrep_cluster_weight
Value: 1
*************************** 55. row ***************************
Variable_name: wsrep_desync_count
Value: 0
*************************** 56. row ***************************
Variable_name: wsrep_evs_delayed
Value:
*************************** 57. row ***************************
Variable_name: wsrep_evs_evict_list
Value:
*************************** 58. row ***************************
Variable_name: wsrep_evs_repl_latency
Value: 0/0/0/0/0
*************************** 59. row ***************************
Variable_name: wsrep_evs_state
Value: OPERATIONAL
*************************** 60. row ***************************
Variable_name: wsrep_gcomm_uuid
Value: aad1f935-131c-11ea-910a-ce3ee95c675e
*************************** 61. row ***************************
Variable_name: wsrep_cluster_conf_id
Value: 1
*************************** 62. row ***************************
Variable_name: wsrep_cluster_size
Value: 1
*************************** 63. row ***************************
Variable_name: wsrep_cluster_state_uuid
Value: aad2c02e-131c-11ea-9294-b2e80a6c08c4
*************************** 64. row ***************************
Variable_name: wsrep_cluster_status
Value: Primary
*************************** 65. row ***************************
Variable_name: wsrep_connected
Value: ON
*************************** 66. row ***************************
Variable_name: wsrep_local_bf_aborts
Value: 0
*************************** 67. row ***************************
Variable_name: wsrep_local_index
Value: 0
*************************** 68. row ***************************
Variable_name: wsrep_provider_name
Value: Galera
*************************** 69. row ***************************
Variable_name: wsrep_provider_vendor
Value: Codership Oy <info@codership.com>
*************************** 70. row ***************************
Variable_name: wsrep_provider_version
Value: 3.39(rb3295e6)
*************************** 71. row ***************************
Variable_name: wsrep_ready
Value: ON
71 rows in set (0.00 sec)


#重点关注下面内容
mysql> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | aad2c02e-131c-11ea-9294-b2e80a6c08c4 |
| ... | ... |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| ... | ... |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| ... | ... |
| wsrep_ready | ON |
+----------------------------+--------------------------------------+

说明：
wsrep_cluster_size表示，该Galera集群中只有一个节点

wsrep_local_state_comment 状态为Synced(4)，表示数据已同步完成(因为是第一个引导节点，无数据需要同步)。如果状态是Joiner, 意味着 SST 没有完成. 只有所有节点状态是Synced，才可以加新节点

wsrep_cluster_status为Primary，且已经完全连接并准备好

5 启动PXC集群中其它所有节点

[root@pxc2 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port Peer
Address:Port
udp UNCONN 0 0 127.0.0.1:323
*:*
udp UNCONN 0 0 ::1:323
:::*
tcp LISTEN 0 128 *:22
*:*
tcp LISTEN 0 100 127.0.0.1:25
*:*
tcp LISTEN 0 128 :::22
:::*
tcp LISTEN 0 100 ::1:25
:::*

[root@pxc2 ~]#systemctl start mysql
[root@pxc2 ~]#ss -ntulp
Netid State Recv-Q Send-Q Local Address:Port Peer
Address:Port
udp UNCONN 0 0 127.0.0.1:323
*:* users:(("chronyd",pid=6289,fd=1))
udp UNCONN 0 0 ::1:323
:::* users:(("chronyd",pid=6289,fd=2))
tcp LISTEN 0 128 *:22
*:* users:(("sshd",pid=6617,fd=3))
tcp LISTEN 0 128 *:4567
*:* users:(("mysqld",pid=7754,fd=11))
tcp LISTEN 0 100 127.0.0.1:25
*:* users:(("master",pid=6752,fd=13))
tcp LISTEN 0 80 :::3306
:::* users:(("mysqld",pid=7754,fd=34))
tcp LISTEN 0 128 :::22
:::* users:(("sshd",pid=6617,fd=4))
tcp LISTEN 0 100 ::1:25
:::* users:(("master",pid=6752,fd=14))

[root@pxc3 ~]#systemctl start mysql

6 查看集群状态，验证集群是否成功

#在任意节点，查看集群状态
[root@pxc1 ~]#mysql -uroot -payaka
mysql> SHOW VARIABLES LIKE 'wsrep_node_name';
+-----------------+--------------------+
| Variable_name | Value |
+-----------------+--------------------+
| wsrep_node_name | pxc-cluster-node-1 |
+-----------------+--------------------+
1 row in set (0.00 sec)


mysql> SHOW VARIABLES LIKE 'wsrep_node_address';
+--------------------+----------+
| Variable_name | Value |
+--------------------+----------+
| wsrep_node_address | 10.0.0.7 |
+--------------------+----------+
1 row in set (0.01 sec)


mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | ON |
+---------------+-------+
1 row in set (0.00 sec)


mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)


#在任意节点查看数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

#在任意节点创建数据库
mysql> create database testdb1;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
5 rows in set (0.00 sec)



#在任意其它节点验证数据是否同步
[root@pxc2 ~]#mysql -uroot -payaka
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
+--------------------+
5 rows in set (0.01 sec)

#利用Xshell软件，同时在三个节点数据库，在其中一个节点成功
mysql> create database testdb2;
Query OK, 1 row affected (0.01 sec)

#在其它节点都提示失败
mysql> create database testdb2;
ERROR 1007 (HY000): Can't create database 'testdb2'; database exists

7 在PXC集群中加入节点

一个节点加入到Galera集群有两种情况：新节点加入集群、暂时离组的成员再次加入集群

1）新节点加入Galera集群

新节点加入集群时，需要从当前集群中选择一个Donor节点来同步数据，也就是所谓的state_snapshot_tranfer(SST)过程。SST同步数据的方式由选项wsrep_sst_method决定，一般选择的是xtrabackup。

必须注意，新节点加入Galera时，会删除新节点上所有已有数据，再通过xtrabackup(假设使用的是该方式)从Donor处完整备份所有数据进行恢复。所以，如果数据量很大，新节点加入过程会很慢。而且，在一个新节点成为Synced状态之前，不要同时加入其它新节点，否则很容易将集群压垮。

如果是这种情况，可以考虑使用wsrep_sst_method=rsync来做增量同步，既然是增量同步，最好保证新节点上已经有一部分数据基础，否则和全量同步没什么区别，且这样会对Donor节点加上全局read only锁。

2）旧节点加入Galera集群

如果旧节点加入Galera集群，说明这个节点在之前已经在Galera集群中呆过，有一部分数据基础，缺少的只是它离开集群时的数据。这时加入集群时，会采用IST(incremental snapshot transfer)传输机制，即使用增量传输。

但注意，这部分增量传输的数据源是Donor上缓存在GCache文件中的，这个文件有大小限制，如果缺失的数据范围超过已缓存的内容，则自动转为SST传输。如果旧节点上的数据和Donor上的数据不匹配(例如这个节点离组后人为修改了一点数据)，则自动转为SST传输。

#在PXC集群中再加一台新的主机PXC4：10.0.0.37
[root@pxc4 ~]#yum install Percona-XtraDB-Cluster-57 -y
[root@pxc4 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc4 ~]#grep -Ev "^#|^$" /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[mysqld]
wsrep_provider=/usr/lib64/galera3/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37
binlog_format=ROW
default_storage_engine=InnoDB
wsrep_slave_threads= 8
wsrep_log_conflicts
innodb_autoinc_lock_mode=2
wsrep_node_address=10.0.0.37
wsrep_cluster_name=pxc-cluster
wsrep_node_name=pxc-cluster-node-4
pxc_strict_mode=ENFORCING
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth="sstuser:s3cretPass"

[root@pxc4 ~]#systemctl start mysql
[root@pxc4 ~]#mysql -uroot -payaka
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30,
Revision
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
| testdb3 |
+--------------------+
8 rows in set (0.00 sec)

#将其它节点的配置文件加以修改
[root@pxc1 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
wsrep_cluster_address=gcomm://10.0.0.7,10.0.0.17,10.0.0.27,10.0.0.37
[root@pxc2 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf
[root@pxc3 ~]#vim /etc/percona-xtradb-cluster.conf.d/wsrep.cnf

8 在PXC集群中修复故障节点

#在除第一个启动节点外的任意节点停止服务
[root@pxc4 ~]#systemctl stop mysql

#在其它任意节点查看wsrep_cluster_size变量少了一个节点
[root@pxc1 ~]#mysql -uroot -payaka
Server version: 5.7.27-30-57-log Percona XtraDB Cluster (GPL), Release rel30,
Revision
mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.01 sec)

mysql> create database testdb4；

#在其它任意节点可看到数据已同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
| testdb3 |
| testdb4 |
+--------------------+
10 rows in set (0.00 sec)

#恢复服务，数据同步
[root@pxc4 ~]#systemctl start mysql
[root@pxc4 ~]#mysql -uroot -payaka
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb1 |
| testdb2 |
| testdb3 |
| testdb4 |
+--------------------+
10 rows in set (0.00 sec)

mysql> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 4 |
+--------------------+-------+
1 row in set (0.01 sec)

实战案例：MariaDB Galera Cluste

范例：在centos8 实现MariaDB Galera Cluster

在三个节点上都实现
[root@centos8 ~]#dnf install mariadb-server-galera -y
[root@centos8 ~]#vim /etc/my.cnf.d/galera.cnf
#wsrep_cluster_address="dummy://" 在此行下面加一行
wsrep_cluster_address="gcomm://10.0.0.8,10.0.0.18,10.0.0.28"

#启动第一节点
[root@centos8 ~]#galera_new_cluster
[root@centos8 ~]#systemctl enable mariadb

#再启动其它节点
[root@centos8 ~]#systemctl enable --now mariadb
[root@centos8 ~]#ss -ntul
Netid State Recv-Q Send-Q Local Address:Port
Peer Address:Port
tcp LISTEN 0 128 0.0.0.0:22
0.0.0.0:* tcp LISTEN 0 128
0.0.0.0:4567 0.0.0.0:*
tcp LISTEN 0 80 0.0.0.0:3306
0.0.0.0:*
tcp LISTEN 0 128 [::]:22
[::]:*

[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> show status like "wsrep_ready";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready | ON |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.001 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_%'\G
MariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%';

范例：CentOS 7 实现 MariaDB Galera Cluster 5.5

#参考仓库：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.X/yum/centos7-
amd64/
yum install MariaDB-Galera-server
vim /etc/my.cnf.d/server.cnf
[galera]
wsrep_provider = /usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://10.0.0.7,10.0.0.17,10.0.0.27"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#下面配置可选项
wsrep_cluster_name = 'mycluster' 默认my_wsrep_cluster
wsrep_node_name = 'node1'
wsrep_node_address = '10.0.0.7'

#首次启动时，需要初始化集群，在其中一个节点上执行命令
/etc/init.d/mysql start --wsrep-new-cluster
#而后正常启动其它节点
service mysql start
#查看集群中相关系统变量和状态变量
SHOW VARIABLES LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_%';
SHOW STATUS LIKE 'wsrep_cluster_size';

TiDB 概述

TiDB 是 PingCAP 公司受 Google Spanner / F1 论文启发而设计的开源分布式 HTAP (Hybrid Transactional and Analytical Processing) 数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB和MySQL几乎完全兼容

TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP联机事务处理场景还适合 OLAP联机分析处理场景的混合数据库。TiDB年可用性达到99.95%

TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。

TiDB 核心特点

高度兼容 MySQL 大多数情况下，无需修改代码即可从 MySQL 轻松迁移至 TiDB，分库分表后的MySQL 集群亦可通过 TiDB 工具进行实时迁移
水平弹性扩展通过简单地增加新节点即可实现 TiDB 的水平扩展，按需扩展吞吐或存储，轻松应对高并发、海量数据场景
分布式事务 TiDB 100% 支持标准的 ACID 事务
真正金融级高可用相比于传统主从 (M-S) 复制方案，基于 Raft 的多数派选举协议可以提供金融级的 100% 数据强一致性保证，且在不丢失大多数副本的前提下，可实现故障的自动恢复 (auto-failover)，无需人工介入
一站式 HTAP 解决方案 TiDB 作为典型的 OLTP 行存数据库，同时兼具强大的 OLAP 性能，配合TiSpark，可提供一站式 HTAP解决方案，一份存储同时处理OLTP & OLAP(OLAP、OLTP的介绍和比较 )无需传统繁琐的 ETL（数据搬运工）过程
云原生 SQL 数据库 TiDB 是为云而设计的数据库，同 Kubernetes 深度耦合，支持公有云、私有云和混合云，使部署、配置和维护变得十分简单。TiDB 的设计目标是 100% 的 OLTP 场景和 80%的 OLAP 场景，更复杂的 OLAP 分析可以通过 TiSpark（TiDB 专门为 Apache Spark（一个非常流行的大数据计算框架）打造的连接器。）项目来完成。 TiDB 对业务没有任何侵入性，能优雅的替换传统的数据库中间件、数据库分库分表等 Sharding 方案。同时它也让开发运维人员不用关注数据库 Scale 的细节问题，专注于业务开发，极大的提升研发的生产力

TiDB整体架构

TiDB 与 MySQL 的架构差异_b+tree_07

TiDB Server

TiDB Server 负责接收SQL请求，处理SQL相关的逻辑，并通过PD找到存储计算所需数据的TiKV地址，与TiKV交互获取数据，最终返回结果。TiDB Server 是无状态的，其本身并不存储数据，只负责计算，可以无限水平扩展，可以通过负载均衡组件（LVS、HAProxy或F5）对外提供统一的接入地址。

PD Server

Placement Driver（简称PD）是整个集群的管理模块，其主要工作有三个：一是存储集群的元信息（某个Key存储在那个TiKV节点）；二是对TiKV集群进行调度和负载均衡（如数据的迁移、Raft group leader的迁移等）；三是分配全局唯一且递增的事务ID

PD 是一个集群，需要部署奇数个节点，一般线上推荐至少部署3个节点。PD在选举的过程中无法对外提供服务，这个时间大约是3秒

TiKV Server

TiKV Server 负责存储数据，从外部看TiKV是一个分布式的提供事务的Key-Value存储引擎。存储数据的基本单位是Region，每个Region负责存储一个Key Range（从StartKey到EndKey的左闭右开区间）的数据，每个TiKV节点会负责多个Region。TiKV使用Raft协议做复制，保持数据的一致性和容灾。副本以Region为单位进行管理，不同节点上的多个Region构成一个Raft Group，互为副本。数据在多个TiKV之间的负载均衡由PD调度，这里也就是以Region为单位进行调度