PXC某个节点异常恢复

Percona XtraDB Cluster是mysql的一种高可用架构，集群由多个节点组成，建议至少配置3个节点，其中每个节点都包含跨节点同步的相同数据集。以三节点为例，集群某个节点因硬件、网络、脑裂等原因，退出集群后，如果剩余两节点写集缓存(gcache.size)中仍然还有异常节点退出集群后执行的事务信息，则启动异常节点可以通过IST(Incremental State Transfer)方式自动恢复，如果写集缓存(gcache.size)已经不存在，则自动启动SST（State Snapshot Transfer），即从其中一个正常节点复制完整副本。
另外如果一个错误的配置更改阻止了MySQL的正常加载，Galera将删除节点的状态，并强制该节点的SST恢复 ，因此如果数据库无法正常拉起自动恢复，可尝试如下方法进行恢复。

1 创建新datadir目录

将异常节点原datadir目录备份，删除，创建新的目录并且修改目录权限，启动mysql，因新datadir目录内无任何文件，阻止了mysql正常加载，强制该节点的SST恢复。

[root@mysqlpxc3 mysql]# mv data data_backup
[root@mysqlpxc3 mysql]# mkdir -p data
[root@mysqlpxc3 mysql]# chown mysql:mysql data
[root@mysqlpxc3 mysql]# systemctl start mysql

注意一定保证此目录下为空，因为在执行systemctl start mysql命令时，会自动初始化，会检测此目录下是否存在文件，有则abort初始化进程。

2023-09-19T09:45:53.599655+08:00 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.30-22.1) initializing of server in progress as process 8085
2023-09-19T09:45:53.600192+08:00 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2023-09-19T09:45:53.600504+08:00 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2023-09-19T09:45:53.600519+08:00 0 [ERROR] [MY-013236] [Server] The designated data directory /data/mysql/data/ is unusable. You can remove all files that the server added to it.
2023-09-19T09:45:53.600561+08:00 0 [ERROR] [MY-010119] [Server] Aborting

2 复制密钥和证书文件

故障节点启动后会因为集群节点间密钥和证书文件不一致导致加入集群失败，这是预期行为。默认情况下，系统变量pxc_encrypt_cluster_traffic设置为ON，意味着所有集群流量都使用证书进行保护，所以要求所有节点使用相同的密钥和证书文件，systemctl start mysql启动初始话过程中生成默认密钥和证书文件，并将其放在datadir数据目录下，这就造成故障节点和其他节点密钥和证书文件不一致，因此需要将故障节点的*.pem全部删除，并将某一个正常节点数据目录下的 *.pem文件全部复制到故障节点。

mysql> show variables like 'pxc_encrypt_cluster_traffic';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| pxc_encrypt_cluster_traffic | ON    |
+-----------------------------+-------+
1 row in set (0.01 sec)

[root@mysqlpxc3 data]# rm -rf *.pem

通过scp从正常节点获得密钥和证书文件。

[root@mysqlpxc1 data]# scp *.pem mysqlpxc3:/data/mysql/data/
root@mysqlpxc3's password: 
ca-key.pem                                                                                                                                                                                             100% 1672   106.7KB/s   00:00    
ca.pem                                                                                                                                                                                                 100% 1120     2.3MB/s   00:00    
client-cert.pem                                                                                                                                                                                        100% 1120     1.4MB/s   00:00    
client-key.pem                                                                                                                                                                                         100% 1680     2.8MB/s   00:00    
private_key.pem                                                                                                                                                                                        100% 1680     5.4MB/s   00:00    
public_key.pem                                                                                                                                                                                         100%  452     1.9MB/s   00:00    
server-cert.pem                                                                                                                                                                                        100% 1120     4.3MB/s   00:00    
server-key.pem

因证书不一致，错误日志中报错记录如下：

2023-09-19T09:49:12.920624+08:00 0 [Warning] [MY-000000] [Galera] last inactive check more than PT1.5S (3*evs.inactive_check_period) ago (PT3.50235S), skipping check
2023-09-19T09:49:42.431993+08:00 0 [Note] [MY-000000] [Galera] PC protocol downgrade 1 -> 0
2023-09-19T09:49:42.432025+08:00 0 [Note] [MY-000000] [Galera] Current view of cluster as seen by this node
view ((empty))
2023-09-19T09:49:42.432062+08:00 0 [ERROR] [MY-000000] [Galera] failed to open gcomm backend connection: 110: failed to reach primary view (pc.wait_prim_timeout): 110 (Connection timed out)
         at gcomm/src/pc.cpp:connect():161
2023-09-19T09:49:42.432069+08:00 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs_core.cpp:gcs_core_open():219: Failed to open backend connection: -110 (Connection timed out)
2023-09-19T09:49:43.432530+08:00 0 [Note] [MY-000000] [Galera] gcomm: terminating thread
2023-09-19T09:49:43.432566+08:00 0 [Note] [MY-000000] [Galera] gcomm: joining thread
2023-09-19T09:49:43.432633+08:00 0 [ERROR] [MY-000000] [Galera] gcs/src/gcs.cpp:gcs_open():1811: Failed to open channel 'pxc-cluster' at 'gcomm://192.168.3.8,192.168.3.9,192.168.3.10': -110 (Connection timed out)
2023-09-19T09:49:43.432641+08:00 0 [ERROR] [MY-000000] [Galera] gcs connect failed: Connection timed out
2023-09-19T09:49:43.432646+08:00 0 [ERROR] [MY-000000] [WSREP] Provider/Node (gcomm://192.168.3.8,192.168.3.9,192.168.3.10) failed to establish connection with cluster (reason: 7)
2023-09-19T09:49:43.432653+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2023-09-19T09:49:43.432966+08:00 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.30-22.1)  Percona XtraDB Cluster (GPL), Release rel22, Revision aff6a8b, WSREP version 26.4.3.
2023-09-19T09:49:43.433482+08:00 0 [Note] [MY-000000] [Galera] dtor state: CLOSED

3 再次启动节点

[root@mysqlpxc3 data]# systemctl start mysql

4 同步状态监控

可通过视图performance_schema.pxc_cluster_view、dstat工具监控同步状态。

mysql> select * from performance_schema.pxc_cluster_view;
+-----------+--------------------------------------+--------+-------------+---------+
| HOST_NAME | UUID                                 | STATUS | LOCAL_INDEX | SEGMENT |
+-----------+--------------------------------------+--------+-------------+---------+
| mysqlpxc3 | 40c7a6dc-5693-11ee-a076-f77a3da62fce | JOINER |           0 |       0 |
| mysqlpxc1 | 923ea791-5687-11ee-8ac1-7214e14114e8 | SYNCED |           1 |       0 |
| mysqlpxc2 | 97508243-5687-11ee-a3da-2eec6c90e809 | SYNCED |           2 |       0 |
+-----------+--------------------------------------+--------+-------------+---------+
3 rows in set (0.00 sec)

[root@mysqlpxc3 data]#dstat
You did not select any stats, using -cdngy by default.
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw 
  0   0  99   0   0   0|  61k 3391k|   0     0 |   0     0 |2009  1892 
  0   0  99   0   0   0|   0     0 | 174B  790B|   0     0 |1988  2000 
  0   0 100   0   0   0|   0     0 | 485B 3469B|   0     0 | 571   912 
  0   0 100   0   0   0|   0   216k| 290B  584B|   0     0 | 684   997 
  0   0 100   0   0   0|   0     0 | 606B 1064B|   0     0 | 642  1034 
  0   0 100   0   0   0|   0     0 | 174B  286B|   0     0 | 632  1001 
  0   0 100   0   0   0|   0     0 | 619B 1989B|   0     0 |1210  1310 
  0   0 100   0   0   0|   0     0 | 306B  822B|   0     0 | 561   898 
  0   0 100   0   0   0|   0    44k| 246B  698B|   0     0 | 902  1125 
  0   0 100   0   0   0|   0  4096B| 688B 1522B|   0     0 | 926  1177 
  0   0 100   0   0   0|   0     0 | 174B  550B|   0     0 | 573   889 
  0   0 100   0   0   0|   0     0 | 306B  694B|   0     0 | 586   949 
  0   0 100   0   0   0|   0     0 | 174B  306B|   0     0 | 585   903 
  0   0 100   0   0   0|   0    72k| 476B  856B|   0     0 | 599   964

5 集群状态检查

通过完成过后检查数据库集群状态。

mysql> show status where Variable_name in ('wsrep_cluster_size','wsrep_cluster_status','wsrep_connected','wsrep_ready') ;
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_size   | 3       |
| wsrep_cluster_status | Primary |
| wsrep_connected      | ON      |
| wsrep_ready          | ON      |
+----------------------+---------+
4 rows in set (0.00 sec)