MySQL5.7版本单节点大数据量迁移到PXC8.0版本集群全记录-3

2021年12月7日 465点热度 0人点赞 0条评论

接上文,单节点升级到80版本之后,构建新版本的80集群就水到渠成、相对简单了,详情可参见之前的集群构建博文。
本文在修改配置集群的新参数时,修改了pxc_strict_mode为默认的ENFORCING,注释掉skip_ssl以支持SSL加密同步传输。
donor节点的日志如下:

2021-12-07T12:21:56.351545Z 0 [Note] [MY-000000] [WSREP-SST] Streaming the backup to joiner at 10.124.206.18 4444
2021-12-07T13:06:19.710146Z 0 [Note] [MY-000000] [Galera] SST sent: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:19.710196Z 0 [Note] [MY-000000] [WSREP] Server status change donor -> joined
2021-12-07T13:06:19.710224Z 0 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:19.710565Z 0 [Note] [MY-000000] [Galera] 0.0 (mysqldb2): State transfer to 1.0 (mysqldb1) complete.
2021-12-07T13:06:19.710605Z 0 [Note] [MY-000000] [Galera] Shifting DONOR/DESYNCED -> JOINED (TO: 5)
2021-12-07T13:06:19.710815Z 0 [Note] [MY-000000] [Galera] Member 0.0 (mysqldb2) synced with group.
2021-12-07T13:06:19.710832Z 0 [Note] [MY-000000] [Galera] Shifting JOINED -> SYNCED (TO: 5)
2021-12-07T13:06:19.710890Z 10 [Note] [MY-000000] [Galera] Server mysqldb2 synced with group
2021-12-07T13:06:19.710931Z 10 [Note] [MY-000000] [WSREP] Server status change joined -> synced
2021-12-07T13:06:19.710944Z 10 [Note] [MY-000000] [WSREP] Synchronized with group, ready for connections
2021-12-07T13:06:19.710963Z 10 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:38.990181Z 0 [Note] [MY-000000] [Galera] async IST sender served
2021-12-07T13:06:38.990751Z 0 [Note] [MY-000000] [Galera] 1.0 (mysqldb1): State transfer from 0.0 (mysqldb2) complete.
2021-12-07T13:06:38.990963Z 0 [Note] [MY-000000] [Galera] Member 1.0 (mysqldb1) synced with group.

joiner节点的日志如下:

2021-12-07T12:21:46.624809Z 0 [Note] [MY-000000] [WSREP-SST] Proceeding with SST.........
2021-12-07T12:21:46.663620Z 0 [Note] [MY-000000] [WSREP-SST] ............Waiting for SST streaming to complete!
2021-12-07T12:21:47.528091Z 0 [Note] [MY-000000] [Galera] (3d6299de-a13c, 'ssl://0.0.0.0:4567') turning message relay requesting off
2021-12-07T13:06:19.710726Z 0 [Note] [MY-000000] [Galera] 0.0 (mysqldb2): State transfer to 1.0 (mysqldb1) complete.
2021-12-07T13:06:19.711254Z 0 [Note] [MY-000000] [Galera] Member 0.0 (mysqldb2) synced with group.
2021-12-07T13:06:19.712860Z 0 [Note] [MY-000000] [WSREP-SST] Preparing the backup at /u01/mysql/data//sst-xb-tmpdir
2021-12-07T13:06:32.139859Z 0 [Note] [MY-000000] [WSREP-SST] Moving the backup to /u01/mysql/data/
2021-12-07T13:06:32.265350Z 0 [Note] [MY-000000] [WSREP-SST] Running post-processing...........
2021-12-07T13:06:32.271818Z 0 [Note] [MY-000000] [WSREP-SST] Skipping mysql_upgrade (sst): local version (8.0.23) == donor version (8.0.23)
2021-12-07T13:06:32.363903Z 0 [Note] [MY-000000] [WSREP-SST] Waiting for server instance to start.....  This may take some time
2021-12-07T13:06:37.080878Z 0 [Note] [MY-000000] [WSREP-SST] ...........post-processing done
2021-12-07T13:06:37.082170Z 0 [Note] [MY-000000] [WSREP-SST] Galera co-ords from recovery: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:37.110761Z 3 [Note] [MY-000000] [Galera] Processing SST received
2021-12-07T13:06:37.111352Z 3 [Note] [MY-000000] [WSREP] Server status change joiner -> initializing
2021-12-07T13:06:37.111828Z 3 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:37.113486Z 0 [Warning] [MY-013245] [Server] The SSL library function CRYPTO_set_mem_functions failed. This is typically caused by the SSL library already being used. As a result the SSL memory allocation will not be instrumented.
2021-12-07T13:06:37.119056Z 4 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-12-07T13:06:38.516689Z 4 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-12-07T13:06:38.791539Z 4 [Note] [MY-000000] [WSREP] wsrep_init_schema_and_SR (nil)
2021-12-07T13:06:38.803764Z 4 [System] [MY-000000] [WSREP] PXC upgrade completed successfully
2021-12-07T13:06:38.820853Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock
2021-12-07T13:06:38.821518Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2021-12-07T13:06:38.933070Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-12-07T13:06:38.933778Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-12-07T13:06:38.951187Z 0 [Warning] [MY-010324] [Server] 'db' entry 'percona_schema mysql.pxc.sst.role@localhost' had database in mixed case that has been forced to lowercase because lower_case_table_names is set. It will not be possible to remove this privilege using REVOKE.
2021-12-07T13:06:38.966474Z 0 [Note] [MY-000000] [WSREP] Initialized wsrep sidno 4
2021-12-07T13:06:38.967977Z 0 [Note] [MY-000000] [Galera] Server initialized
2021-12-07T13:06:38.968748Z 0 [Note] [MY-000000] [WSREP] Server status change initializing -> initialized
2021-12-07T13:06:38.969468Z 0 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:38.970219Z 3 [Note] [MY-000000] [WSREP] Server status change initialized -> joined
2021-12-07T13:06:38.971005Z 3 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:38.971772Z 3 [Note] [MY-000000] [Galera] Recovered position from storage: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:38.982982Z 3 [Note] [MY-000000] [Galera] Recovered view from SST:
  id: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
  status: primary
  protocol_version: 4
  capabilities: MULTI-MASTER, CERTIFICATION, PARALLEL_APPLYING, REPLAY, ISOLATION, PAUSE, CAUSAL_READ, INCREMENTAL_WS, UNORDERED, PREORDERED, STREAMING, NBO
  final: no
  own_index: 1
  members(2):
        0: 1ad4eb4c-5757-11ec-a746-e3cb8800c1e0, mysqldb2
        1: 3d6299de-5758-11ec-a13c-63e7397f116a, mysqldb1

2021-12-07T13:06:38.986279Z 3 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.
2021-12-07T13:06:38.986499Z 10 [Note] [MY-000000] [WSREP] Recovered cluster id 2865d710-5743-11ec-8f0c-7af1fec7bcb1
2021-12-07T13:06:38.986556Z 11 [Note] [MY-000000] [WSREP] Starting applier thread 11
2021-12-07T13:06:38.986584Z 12 [Note] [MY-000000] [WSREP] Starting applier thread 12
2021-12-07T13:06:38.986631Z 13 [Note] [MY-000000] [WSREP] Starting applier thread 13
2021-12-07T13:06:38.986647Z 14 [Note] [MY-000000] [WSREP] Starting applier thread 14
2021-12-07T13:06:38.986673Z 15 [Note] [MY-000000] [WSREP] Starting applier thread 15
2021-12-07T13:06:38.986727Z 17 [Note] [MY-000000] [WSREP] Starting applier thread 17
2021-12-07T13:06:38.986841Z 16 [Note] [MY-000000] [WSREP] Starting applier thread 16
2021-12-07T13:06:38.987236Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23-14.1'  socket: '/u01/mysql/mysql.sock'  port: 3306  Percona XtraDB Cluster (GPL), Release rel14, Revision d3b9a1d, WSREP version 26.4.3.
2021-12-07T13:06:38.988606Z 3 [Note] [MY-000000] [Galera] SST received: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:38.988640Z 3 [System] [MY-000000] [WSREP] SST completed
2021-12-07T13:06:38.988688Z 1 [Note] [MY-000000] [Galera]  str_proto_ver_: 3 sst_seqno_: 5 cc_seqno: 5 req->ist_len(): 67
2021-12-07T13:06:38.988741Z 1 [Note] [MY-000000] [Galera] Installed new state from SST: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:38.989082Z 1 [Note] [MY-000000] [Galera] Cert. index preload up to 5
2021-12-07T13:06:38.989159Z 0 [Note] [MY-000000] [Galera] ####### IST applying starts with 6
2021-12-07T13:06:38.989326Z 0 [Note] [MY-000000] [Galera] ####### IST current seqno initialized to 5
2021-12-07T13:06:38.989412Z 0 [Note] [MY-000000] [Galera] Receiving IST...  0.0% (0/1 events) complete.
2021-12-07T13:06:38.989434Z 0 [Note] [MY-000000] [Galera] IST preload starting at 5
2021-12-07T13:06:38.989454Z 0 [Note] [MY-000000] [Galera] ####### Passing IST CC 5, must_apply: 0, preload: true
2021-12-07T13:06:38.989485Z 0 [Note] [MY-000000] [Galera] First IST (CC) event for rebuilding cert index
2021-12-07T13:06:38.989501Z 0 [Note] [MY-000000] [Galera] REPL Protocols: 10 (5)
2021-12-07T13:06:38.989551Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.
2021-12-07T13:06:38.989606Z 0 [Note] [MY-000000] [Galera] ####### Assign initial position for certification: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:4, protocol version: 5
2021-12-07T13:06:38.989625Z 0 [Note] [MY-000000] [Galera] REPL Protocols: 10 (5)
2021-12-07T13:06:38.989638Z 0 [Note] [MY-000000] [Galera] ####### Adjusting cert position: 4 -> 5
2021-12-07T13:06:38.989670Z 0 [Note] [MY-000000] [Galera] Service thread queue flushed.
2021-12-07T13:06:38.989711Z 0 [Note] [MY-000000] [Galera] Recording CC from preload: 5
2021-12-07T13:06:38.989726Z 0 [Note] [MY-000000] [Galera] Lowest cert index boundary for CC from preload: 5
2021-12-07T13:06:38.989738Z 0 [Note] [MY-000000] [Galera] Min available from gcache for CC from preload: 5
2021-12-07T13:06:38.989790Z 0 [Note] [MY-000000] [Galera] Receiving IST...100.0% (1/1 events) complete.
2021-12-07T13:06:38.990155Z 1 [Note] [MY-000000] [Galera] IST received: 2865d710-5743-11ec-8f0c-7af1fec7bcb1:5
2021-12-07T13:06:38.990476Z 1 [Note] [MY-000000] [Galera] Recording CC from sst: 5
2021-12-07T13:06:38.990507Z 1 [Note] [MY-000000] [Galera] Lowest cert index boundary for CC from sst: 5
2021-12-07T13:06:38.990523Z 1 [Note] [MY-000000] [Galera] Min available from gcache for CC from sst: 5
2021-12-07T13:06:38.990704Z 0 [Note] [MY-000000] [Galera] 1.0 (mysqldb1): State transfer from 0.0 (mysqldb2) complete.
2021-12-07T13:06:38.990731Z 0 [Note] [MY-000000] [Galera] SST leaving flow control
2021-12-07T13:06:38.990747Z 0 [Note] [MY-000000] [Galera] Shifting JOINER -> JOINED (TO: 5)
2021-12-07T13:06:38.990918Z 0 [Note] [MY-000000] [Galera] Member 1.0 (mysqldb1) synced with group.
2021-12-07T13:06:38.990934Z 0 [Note] [MY-000000] [Galera] Shifting JOINED -> SYNCED (TO: 5)
2021-12-07T13:06:38.990958Z 1 [Note] [MY-000000] [Galera] Server mysqldb1 synced with group
2021-12-07T13:06:38.990980Z 1 [Note] [MY-000000] [WSREP] Server status change joined -> synced
2021-12-07T13:06:38.990990Z 1 [Note] [MY-000000] [WSREP] Synchronized with group, ready for connections
2021-12-07T13:06:38.990999Z 1 [Note] [MY-000000] [WSREP] wsrep_notify_cmd is not defined, skipping notification.

可见同步新节点时760G数据,耗时45分钟。
【至此,本系列迁移、升级集群的文章连载完毕,后续真正的割接只是一个时间选择问题了】

liking

我是雪人

文章评论