mysql bootstrap pxc_Percona XtraDB Cluster(PXC) 5.7的启动与关闭测试

程旭尧
2023-12-01

pxc重启一般都滚动重启,保证有一个节点存活,保证gcache不丢失,新的节点加入到cluster时就可以用ist的方式同步数据。那么如果遇到需要停电需要关闭所以有节点,该怎么重新启动pxc的节点?

目前是三点节点都正常连接的:

mysql>  show status like '%wsrep_clust%';

+--------------------------+--------------------------------------+

| Variable_name            | Value                                |

+--------------------------+--------------------------------------+

| wsrep_cluster_conf_id    | 10                                   |

| wsrep_cluster_size       | 3                                    |

| wsrep_cluster_state_uuid | a084e15e-6f18-11e8-9dc4-efc0d77a254c |

| wsrep_cluster_status     | Primary                              |

+--------------------------+--------------------------------------+

4 rows in set (0.00 sec)

将三个节点全部都关闭

[root@qht131 ~]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster)...............[  OK  ]

[root@qht132 ~]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster)...............[  OK  ]

qht133:

mysql>  show status like '%wsrep_clust%';

+--------------------------+--------------------------------------+

| Variable_name            | Value                                |

+--------------------------+--------------------------------------+

| wsrep_cluster_conf_id    | 12                                   |

| wsrep_cluster_size       | 1                                    |

| wsrep_cluster_state_uuid | a084e15e-6f18-11e8-9dc4-efc0d77a254c |

| wsrep_cluster_status     | Primary                              |

+--------------------------+--------------------------------------+

4 rows in set (0.00 sec)

在关闭最后一个节点qht133之前,插入一笔数据进去,测试重启后会不会同步到其它节点。

mysql> insert into percona.example values(5,'percona1');

Query OK, 1 row affected (0.12 sec)

mysql>  SELECT * FROM percona.example;

+---------+-----------+

| node_id | node_name |

+---------+-----------+

|       1 | percona1  |

|       2 | percona1  |

|       3 | percona1  |

|       4 | percona1  |

|       5 | percona1  |

+---------+-----------+

5 rows in set (0.00 sec)

[root@qht133 ~]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster)...............[  OK  ]

由于qht133是最后一个关闭的,重启pxc时需要先启动qht133做primary,否则会有数据丢失的风险。

而且这时相看qht133的grastage.dat,safe_to_bootstrap为1,说明这个节点是可以安全的做为主节点开启,seqno为14

[root@qht133 mysql]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    a084e15e-6f18-11e8-9dc4-efc0d77a254c

seqno:   14

safe_to_bootstrap: 1

而另外两个节点的grastate.dat的safe_to_bootstrap为0,说明如果先启动qht131和qht132是不安全的,seqno为11,通过对比seqno,看哪个是最新的也可以判定哪个节点是最后被关闭的。

[root@qht131 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    a084e15e-6f18-11e8-9dc4-efc0d77a254c

seqno:   11

safe_to_bootstrap: 0

[root@qht132 ~]# cat /var/lib/mysql/grastate.dat

# GALERA saved state

version: 2.1

uuid:    a084e15e-6f18-11e8-9dc4-efc0d77a254c

seqno:   11

safe_to_bootstrap: 0

先不冒风险,启动qht133后再启动qht131和qht132.

[root@qht133 ~]# /etc/init.d/mysql bootstrap-pxc

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster)......

[root@qht132 ~]# /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)......State transfer in progress, setting sleep higher

...                                                        [  OK  ]

[root@qht131 ~]# /etc/init.d/mysql start

Starting MySQL (Percona XtraDB Cluster)...State transfer in progress, setting sleep higher

...                                                        [  OK  ]

mysql>  show status like '%wsrep_clust%';

+--------------------------+--------------------------------------+

| Variable_name            | Value                                |

+--------------------------+--------------------------------------+

| wsrep_cluster_conf_id    | 3                                    |

| wsrep_cluster_size       | 3                                    |

| wsrep_cluster_state_uuid | a084e15e-6f18-11e8-9dc4-efc0d77a254c |

| wsrep_cluster_status     | Primary                              |

+--------------------------+--------------------------------------+

4 rows in set (0.01 sec)

mysql> SELECT * FROM percona.example;

+---------+-----------+

| node_id | node_name |

+---------+-----------+

|       1 | percona1  |

|       2 | percona1  |

|       3 | percona1  |

|       4 | percona1  |

|       5 | percona1  |

+---------+-----------+

5 rows in set (0.06 sec)

没有问题,三个节点都启动了,并且数据也是最新同步的。

再来测试一下所以节点都关闭的情况下,第一次开启的节点不是最后一个关闭的节点的情况。

先来关闭三个节点:

[root@qht131 ~]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster)...............[  OK  ]

[root@qht132 ~]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster)...............[  OK  ]

关闭qht133之前同样先插入一笔数据

mysql> insert into percona.example values(6,'percona1');

Query OK, 1 row affected (0.00 sec)

mysql> select * from percona.example;

+---------+-----------+

| node_id | node_name |

+---------+-----------+

|       1 | percona1  |

|       2 | percona1  |

|       3 | percona1  |

|       4 | percona1  |

|       5 | percona1  |

|       6 | percona1  |

+---------+-----------+

6 rows in set (0.00 sec)

[root@qht133 mysql]# /etc/init.d/mysql stop

Shutting down MySQL (Percona XtraDB Cluster).............. [  OK  ]

这时如果从qht131先启动的话,直接启动失败:

[root@qht131 ~]# /etc/init.d/mysql bootstrap-pxc

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster).......The server quit without updating PID file (/var/run[FAILED]mysqld.pid).

MySQL (Percona XtraDB Cluster) server startup failed!      [FAILED]

错误日志:

2018-06-13T22:34:05.727210Z 0 [ERROR] WSREP: It may not be safe to bootstrap the cluster from this node. It was not the last one to leave the cluster and may not contain all the updates. To force cluster bootstrap with this node, edit the grastate.dat file manually and set safe_to_bootstrap to 1 .

2018-06-13T22:34:05.727216Z 0 [ERROR] WSREP: Provider/Node (gcomm://172.17.61.131,172.17.61.132,172.17.61.133) failed to establish connection with cluster (reason: 7)

2018-06-13T22:34:05.727224Z 0 [ERROR] Aborting

参考:

https://www.cnblogs.com/zengkefu/p/5678279.html

https://blog.csdn.net/zengxuewen2045/article/details/51868976

---------------------

作者:zuozhiji

来源:CSDN

原文:https://blog.csdn.net/jolly10/article/details/80689519

版权声明:本文为博主原创文章,转载请附上博文链接!

 类似资料: