mysql PXC配置
1. 下载
wget https://www.percona.com/downloads/Percona-XtraDB-Cluster-LATEST/Percona-XtraDB-Cluster-5.7.28-31.41/binary/tarball/Percona-XtraDB-Cluster-5.7.28-rel31-31.41.1.Linux.x86_64.ssl101.tar.gz
2 . 环境
IP host port
172.17.0.2 master 3306
172.17.0.3 slave1 3306
172.17.0.4 slave2 3306
3. 使用端口
- 3306
- 4444
- 4567
- 4568
4. 安装依赖
yum install -y git scons gcc gcc-c++ openssl check cmake bison \ boost-devel asio-devel libaio-devel ncurses-devel readline-devel pam-devel \ socat libcurl-devel socat nc perl-IO-Socket-SSL perl-Time-HiRes rsync lsof
5. SST和IST 区别
SST是全量传输
IST 是增量传输
6. my.cnf 配置文件
master my.cnf [client] port = 3306 socket = /data/mysql/mysql3306/tmp/mysql3306.sock [mysql] prompt="\\u@\\h:\p \\R:\\m:\\s [\\d]>" no-auto-rehash [mysqld] user = mysql basedir = /usr/local/mysql datadir = /data/mysql/mysql3306/data port = 3306 socket = /data/mysql/mysql3306/tmp/mysql3306.sock event_scheduler = 0 #tmp tmpdir=/data/mysql/mysql3306/tmp #timeout interactive_timeout = 300 wait_timeout = 300 #character set character-set-server = utf8mb4 open_files_limit = 65535 max_connections = 100 max_connect_errors = 100000 skip-name-resolve = 1 #logs log-output=file slow_query_log = 1 slow_query_log_file = /data/mysql/mysql3306/logs/slow.log log-error = /data/mysql/mysql3306/logs/error.log log_error_verbosity = 3 pid-file = mysql3306.pid long_query_time = 1 #log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1 log-slow-slave-statements = 1 #binlog binlog_format = row server-id = 1 log-bin = /data/mysql/mysql3306/logs/mysql-bin log-bin-index = /data/mysql/mysql3306/logs/mysql-bin.index binlog_cache_size = 4M max_binlog_size = 1G max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 90 #relay log skip_slave_start = 1 max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery = 1 log_slave_updates #slave-skip-errors=1032,1053,1062 explicit_defaults_for_timestamp=1 #buffers & cache table_open_cache = 2048 table_definition_cache = 2048 table_open_cache = 2048 max_heap_table_size = 96M sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size = 256 query_cache_size = 0 query_cache_type = 0 query_cache_limit = 256K query_cache_min_res_unit = 512 thread_stack = 192K tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M #myisam myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 #innodb innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1 innodb_data_file_path = ibdata1:1G:autoextend innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 64M innodb_log_file_size = 500M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 50 innodb_file_per_table = 1 innodb_rollback_on_timeout innodb_status_file = 1 innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED innodb_flush_method = O_DIRECT gtid_mode = ON enforce_gtid_consistency = ON master_info_repository = TABLE relay-log-info-repository = TABLE binlog_checksum = NONE log_slave_updates = ON # Two-Master configure #server-1 #auto-increment-offset = 1 #auto-increment-increment = 2 #server-2 #auto-increment-offset = 2 #auto-increment-increment = 2 slave_preserve_commit_order = 1 slave_transaction_retries = 128 log_timestamps = system show_compatibility_56 = on slave_parallel_workers = 4 slave_parallel_type = LOGICAL_CLOCK wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address = gcomm://172.17.0.2,172.17.0.3,172.17.0.4 wsrep_node_name = master wsrep_node_address = 172.17.0.2 wsrep_cluster_name = pxc-cluster wsrep_sst_auth = sstuser:passw0rd wsrep_sst_method = xtrabackup-v2
#wsrep_sst_method = rsync wsrep_slave_threads = 2 pxc_strict_mode = ENFORCING innodb_autoinc_lock_mode = 2 wsrep_provider_options = "debug=1;gcache.size=1G"
参数说明
配置参数说明 wsrep_provider 指定 Galera 库的路径。 wsrep_cluster_name 指定集群的逻辑名称，集群内的所有节点，这个名称必须一致。 wsrep_cluster_address 指定集群内节点的 IP 地址，建议将集群节点都配上。 wsrep_node_name 指定单个节点的逻辑名称，如果没有指定，将使用 hostname 作为逻辑名称。 wsrep_node_address 指定此特定节点的 IP 地址。 wsrep_sst_method 默认的使用 Percona Xtrabackup 进行 State Snapshot Transfer (SST)，强烈建议使用 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth 指定 sst 的身份验证凭据，使用<sst_user>:<sst_pass> 这种格式，启动第一个节点时必须创建，并且提供相应的权限。 pxc_strict_mode 关闭实验性的或者不支持的特性 binlog_format Galera 只支持 row-level replication，所以设置为 binlog_format=ROW。 default_storage_engine Galera 只支持 InnoDB 引擎，所以设置为 default_storage_engine=InnoDB。 innodb_autoinc_lock_mode Galera 只支持 lock mode 为 2 的 InnoDB 引擎，所以设置为 innodb_autoinc_lock_mode=2。
7. 启动 master 第一个节点
初始化 mysqld --defaults-file=/etc/my.cnf --initialize
启动 mysqld --defaults-file=/etc/my.cnf --wsrep-new-cluster &
# 登陆修改密码
mysql > alter user 'root'@'localhost' identified by '123456';
8. sst全量传输授权账号。
Before adding other nodes to your new cluster, create a user for SST and provide necessary privileges for it. The
credentials must match those specified when Configuring Nodes for Write-Set Replication.
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON * . * TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
9. 常看PXC状态
mysql@master> show status like 'wsrep%';
+----------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cluster_size | 1 |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_ready | ON
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)
slave1 my.cnf 配置文件
server-id=2 # server-id 不能相同
wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address = gcomm://172.17.0.2,172.17.0.3,172.17.0.4 wsrep_node_name = slave1 wsrep_node_address = 172.17.0.3 wsrep_cluster_name = pxc-cluster wsrep_sst_auth = sstuser:passw0rd wsrep_sst_method = xtrabackup-v2 wsrep_slave_threads = 2 pxc_strict_mode = ENFORCING innodb_autoinc_lock_mode = 2 wsrep_provider_options = "debug=1;gcache.size=1G"
slave2 my.cnf 配置文件
server-id=3 # server-id 不能相同 wsrep_provider = /usr/local/mysql/lib/libgalera_smm.so wsrep_cluster_address = gcomm://172.17.0.2,172.17.0.3,172.17.0.4 wsrep_node_name = slave2 wsrep_node_address = 172.17.0.4 wsrep_cluster_name = pxc-cluster wsrep_sst_auth = sstuser:passw0rd wsrep_sst_method = xtrabackup-v2 wsrep_slave_threads = 2 pxc_strict_mode = ENFORCING innodb_autoinc_lock_mode = 2 wsrep_provider_options = "debug=1;gcache.size=1G"
9 slave1 slave2 直接启动，不用初始化
需要安装 percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
yum -y install percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
启动
mysqld --defaults-file=/etc/my.cnf &
10. 登陆mysql
所有节点登陆密码都是 123456
mysql -u root -p
11. 绕开SST通过IST方式添加Node到Percona XtraDB Cluster
slave3 172.17.0.5 为新加入pxc 服务器 11.1
master 172.17.0.2 物理备份 innobackupex --defaults-file=/etc/my.cnf -S /data/mysql/mysql3306/tmp/mysql3306.sock --galera-info -u root -p123456 /data/backup/ 但是没生成x
cd /data/backup/
innobackupex --apply-log /data/backup/2020-03-16_13-57-24/
11.2
tar -zcvf 2020-03-16_13-57-24.tar.gz 2020-03-16_13-57-24/ scp 2020-03-16_13-57-24.tar.gz 172.17.0.5:/data/ 11.3
my.cnf 配置文件注意 server_id 和 pxc相关配置 11.4 cp -r /data/backup/2020-03-16_13-57-24/* /data/mysql/mysql3306/data/ 11.5 查询master 172.17.0.2 服务器pxc ssid 和 xid show global status like '%wsrep%'; | Variable_name | Value | +----------------------------------+-----------------------------------------------------------------+ | wsrep_local_state_uuid | 118c0013-6748-11ea-9d09-aa0d37bc770a wsrep_last_committed | 72 11.6
172.17.0.5 新建 grastate.data文件 cat grastate.dat # GALERA saved state version: 2.1 uuid: 118c0013-6748-11ea-9d09-aa0d37bc770a seqno: 72 #查出来的 wsrep_last_committed safe_to_bootstrap: 0
11.7 授权
chown -R mysql.mysql /data/mysql/mysql3306/data 11.8 启动 172.17.0.5 mysql mysqld --defaults-file=my.cnf &
| wsrep_last_applied | 134 |
| wsrep_last_committed | 134
12. PXC 原理
用户发起Commit，在收到Ok之前 集群每次发起一个动作，都会有一个唯一的编号 PXC独有的Global Trx Id 动作发起者: commit_cb 其它节点多了一个动作： apply_cb 上面的这些动作，是通过那个端号交互的？ 4567 4568端口 IST 只是在节点下线，重启加入那一个时间有用 4444 只会在新节点加入进来时起作用 pxc结构里面 如果主节点写入过大 apply_cb 时间会不会跟不上 wsrep_slave_threads参数 解决apply_cb跟不上问题 配置成和CPU的个数相等或是1.5倍 当前节点commit_cb 后就可以返回了 推过去之后，验证通过就行了可以返回客户端了 cb==commit block 提交数据块
报错
which: no xtrabackup in (/usr/sbin:/sbin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql/bin/) 2020-03-15T12:36:01.910260Z WSREP_SST: [ERROR] ******************* FATAL ERROR ********************** 2020-03-15T12:36:01.912921Z WSREP_SST: [ERROR] xtrabackup not in path: /usr/sbin:/sbin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql/bin/ 2020-03-15T12:36:01.915665Z WSREP_SST: [ERROR] ****************************************************** yum -y install percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm
2020-03-15T12:40:14.467346Z WSREP_SST: [ERROR] socat not found in path: /usr/sbin:/sbin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/local/mysql/bin/ 2020-03-15T12:40:14.470076Z WSREP_SST: [ERROR] ******************************************************
yum -y install socat
参考：
