Xtrabackup Streaming loops

1

Hello.

I have Percona PXC ( Ver 8.0.33-25.1) . Trying to create Replica Node. Everything is installed there also, but while trying to stream Xtrabackup in parallel, it’s looping on scanning logs.

230928 11:49:02  version_check Connected to MySQL server
230928 11:49:02  version_check Executing a version check against the server...
230928 11:49:02  version_check Done.
2023-09-28T11:49:02.758243-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: NODE, user: USER, password: set, port: not set, socket: not set
2023-09-28T11:49:02.764334-00:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.33-25.1
2023-09-28T11:49:02.765650-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK TABLES FOR BACKUP ...
2023-09-28T11:49:02.767257-00:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise().
2023-09-28T11:49:02.767280-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /data/mysql/
2023-09-28T11:49:02.767292-00:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 65535, set to 65535
2023-09-28T11:49:02.767722-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration:
2023-09-28T11:49:02.767732-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = .
2023-09-28T11:49:02.767736-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend
2023-09-28T11:49:02.767755-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./
2023-09-28T11:49:02.767762-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2
2023-09-28T11:49:02.767778-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 4294967296
2023-09-28T11:49:02.767785-00:00 0 [Note] [MY-011825] [Xtrabackup] using O_DIRECT
2023-09-28T11:49:02.768728-00:00 0 [Note] [MY-011825] [Xtrabackup] inititialize_service_handles suceeded
2023-09-28T11:49:02.856077-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: NODE, user: USER, password: set, port: not set, socket: not set
2023-09-28T11:49:02.862095-00:00 0 [Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
2023-09-28T11:49:02.960079-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:02.962194-00:00 0 [Note] [MY-012953] [InnoDB] Disabling background ibuf IO read threads.
2023-09-28T11:49:03.176678-00:00 0 [Note] [MY-011825] [Xtrabackup] Generating a list of tablespaces
2023-09-28T11:49:03.176735-00:00 0 [Note] [MY-012204] [InnoDB] Scanning './'
2023-09-28T11:49:03.178061-00:00 0 [Note] [MY-012208] [InnoDB] Completed space ID check of 2 files
2023-09-28T11:49:03.240343-00:00 2 [Note] [MY-011825] [Xtrabackup] Done: Compressing and streaming ./ibdata1
2023-09-28T11:49:03.960282-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:04.960557-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:05.960762-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:06.960966-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:07.961172-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:08.961362-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:09.961705-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:10.961958-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:11.962166-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:12.962386-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:13.962598-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:14.962818-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:15.963075-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:16.963283-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:17.963485-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:18.963686-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:19.963892-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)
2023-09-28T11:49:20.964194-00:00 1 [Note] [MY-011825] [Xtrabackup] >> log scanned up to (48008560)

Database itself it very small, it’s testing cluster with one little schema in it and it’s innodb, not myisam.

xtrabackup version 8.0.34-29

Xtrabackup is working well when backing up localhost. ssh is working well also.

Using this script :

sudo xtrabackup --backup --compress --stream=xbstream --user=USER --password=PASSWORD --databases-exclude="performance_schema" --host=hostname --target-dir=/data/backup/ | ssh OSUSER@IP "xbstream -x -C /data/backup/"

What can be wrong with this ?

Thanks in advance.

2

Hi @Ani_Ghoghoberidze

It seems like PXB is waiting on the network to finish streaming the file. You can do two things:

  1. Check on the receiver server, via ls -la /data/backup/ if the file ibdata1 is growing on size.
  2. Run the backup locally to eliminate network from the picture:
sudo xtrabackup --backup --compress --stream=xbstream --user=USER --password=PASSWORD --databases-exclude="performance_schema" --host=hostname --target-dir=/data/backup/ >/dev/null

Some further thoughts
Are you running xtrabackup on the same host as the server you want to backup right? I see you have --hostname set on PXB. You cannot use PXB for remote backups

Consider increasing --parallel on PXB, this way, you will have chunks from multiple files being streamed in parallel.

3

Thanks @Marcelo_Altmann for such a prompt response.

  1. No, nothing is growing on size in a receiver.
  2. Xtrabackup is working without stream, if I want to take a full backup on the same host, without streaming, everything goes well.

I’m trying execute it from source node. I tried without --parallel option, too, but without success.

4

Xtrabackup is working without stream, if I want to take a full backup on the same host, without streaming, everything goes well.

This indeed confirms the issue is in the ssh/network part.

Try to manually copy files from one side to another. Also, ensure you have xbstream on the receiver side.

5

I found the problem, it was my fault, I was missing sshpass -p . Thanks !

1 Like