[MDEV-28187] mariadb-backup doesn't utilise innodb-undo-log-directory (if specified as a relative path) during copy-back operation Created: 2022-03-28  Updated: 2023-04-05  Resolved: 2023-03-21

Status: Closed
Project: MariaDB Server
Component/s: mariabackup
Affects Version/s: 10.7.3, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4

Type: Bug Priority: Critical
Reporter: Susmeet Khaire Assignee: Vladislav Lesin
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Relates
relates to MDEV-21952 ibdata1 file size growing in MariaDB Closed

 Description   

We have noticed that if innodb-undo-log-directory is specified as a relative path, mariadb-backup is unable to utilize it during copy-back operation.

my.cnf

 
[root@localhost ~]# cat /etc/my.cnf.d/server.cnf
 
[server]
 
innodb_undo_tablespaces=2
 
innodb_undo_directory=./
 
log_error=/var/log/mysql/error.log

Initialize the data directory

 
[root@localhost mysql]# mysql_install_db
 
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
 
OK
 
 
 
To start mysqld at boot time you have to copy
 
support-files/mysql.server to the right place for your system
 
 
 
 
 
Two all-privilege accounts were created.
 
One is root@localhost, it has no password, but you need to
 
be system 'root' user to connect. Use, for example, sudo mysql
 
The second is root@localhost, it has no password either, but
 
you need to be the system 'root' user to connect.
 
After connecting you can set the password, if you would need to be
 
able to connect as any of these users with a password and without sudo
 
 
 
See the MariaDB Knowledgebase at https://mariadb.com/kb
 
 
 
You can start the MariaDB daemon with:
 
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql'
 
 
 
You can test the MariaDB daemon with mysql-test-run.pl
 
cd '/usr/mysql-test' ; perl mysql-test-run.pl
 
 
 
Please report any problems at https://mariadb.org/jira
 
 
 
The latest information about MariaDB is available at https://mariadb.org/.
 
 
 
Consider joining MariaDB's strong and vibrant community:
 
https://mariadb.org/get-involved/
 
 
 
[root@localhost mysql]# ls
 
aria_log.00000001  aria_log_control  ib_buffer_pool  ibdata1  ib_logfile0  mysql  mysql_upgrade_info  performance_schema  sys  test  undo001  undo002

Backup operation

 
[root@localhost mysql]# mariadb-backup --print-defaults -uroot --target-dir=//mnt/mariadb-backup/ --backup
 
mariadb-backup would have been started with the following arguments:
 
--innodb_undo_tablespaces=2 --innodb_undo_directory=./ --log_error=/var/log/mysql/error.log -uroot --target-dir=//mnt/mariadb-backup/ --backup
 
[root@localhost mysql]# mariadb-backup  -uroot --target-dir=//mnt/mariadb-backup/ --backup
 
[00] 2022-03-28 18:29:11 Connecting to MySQL server host: localhost, user: root, password: not set, port: not set, socket: not set
 
mariadb-backup: Warning: Charset id '33' csname 'utf8mb3' trying to replace existing csname 'utf8'
 
mariadb-backup: Warning: Charset id '83' csname 'utf8mb3' trying to replace existing csname 'utf8'
 
[00] 2022-03-28 18:29:11 Using server version 10.7.3-MariaDB
 
mariadb-backup based on MariaDB server 10.7.3-MariaDB Linux (x86_64)
 
[00] 2022-03-28 18:29:11 uses posix_fadvise().
 
[00] 2022-03-28 18:29:11 cd to /var/lib/mysql/
 
[00] 2022-03-28 18:29:11 open files limit requested 0, set to 1024
 
[00] 2022-03-28 18:29:11 mariabackup: using the following InnoDB configuration:
 
[00] 2022-03-28 18:29:11 innodb_data_home_dir =
 
[00] 2022-03-28 18:29:11 innodb_data_file_path = ibdata1:12M:autoextend
 
[00] 2022-03-28 18:29:11 innodb_log_group_home_dir = ./
 
[00] 2022-03-28 18:29:11 InnoDB: Using Linux native AIO
 
2022-03-28 18:29:11 0 [Note] InnoDB: Number of pools: 1
 
2022-03-28 18:29:11 0 [Note] InnoDB: Opened 2 undo tablespaces
 
[00] 2022-03-28 18:29:11 mariabackup: Generating a list of tablespaces
 
[00] 2022-03-28 18:29:11 >> log scanned up to (42720)
 
[01] 2022-03-28 18:29:11 Copying ibdata1 to /mnt/mariadb-backup/ibdata1
 
[01] 2022-03-28 18:29:11         ...done
 
[01] 2022-03-28 18:29:11 Copying .//undo001 to /mnt/mariadb-backup/undo001
 
[01] 2022-03-28 18:29:12         ...done
 
[01] 2022-03-28 18:29:12 Copying .//undo002 to /mnt/mariadb-backup/undo002
 
[01] 2022-03-28 18:29:12         ...done
 
[01] 2022-03-28 18:29:12 Copying ./mysql/innodb_table_stats.ibd to /mnt/mariadb-backup/mysql/innodb_table_stats.ibd
 
[01] 2022-03-28 18:29:12         ...done
 
.....
 
[00] 2022-03-28 18:29:14 >> log scanned up to (42720)
 
[00] 2022-03-28 18:29:14 Executing BACKUP STAGE END
 
[00] 2022-03-28 18:29:14 All tables unlocked
 
[00] 2022-03-28 18:29:14 Copying ib_buffer_pool to /mnt/mariadb-backup/ib_buffer_pool
 
[00] 2022-03-28 18:29:14         ...done
 
[00] 2022-03-28 18:29:14 Backup created in directory '/mnt/mariadb-backup/'
 
[00] 2022-03-28 18:29:14 Writing backup-my.cnf
 
[00] 2022-03-28 18:29:14         ...done
 
[00] 2022-03-28 18:29:14 Writing xtrabackup_info
 
[00] 2022-03-28 18:29:14         ...done
 
[00] 2022-03-28 18:29:14 Redo log (from LSN 42708 to 42720) was copied.
 
[00] 2022-03-28 18:29:14 completed OK!

Prepare operation

 
[root@localhost mysql]# mariadb-backup  -uroot --target-dir=//mnt/mariadb-backup/ --prepare
 
mariadb-backup based on MariaDB server 10.7.3-MariaDB Linux (x86_64)
 
[00] 2022-03-28 18:29:33 cd to /mnt/mariadb-backup/
 
[00] 2022-03-28 18:29:33 This target seems to be not prepared yet.
 
mariadb-backup: Warning: Charset id '33' csname 'utf8mb3' trying to replace existing csname 'utf8'
 
mariadb-backup: Warning: Charset id '83' csname 'utf8mb3' trying to replace existing csname 'utf8'
 
[00] 2022-03-28 18:29:33 mariabackup: using the following InnoDB configuration for recovery:
 
[00] 2022-03-28 18:29:33 innodb_data_home_dir = .
 
[00] 2022-03-28 18:29:33 innodb_data_file_path = ibdata1:12M:autoextend
 
[00] 2022-03-28 18:29:33 innodb_log_group_home_dir = .
 
[00] 2022-03-28 18:29:33 InnoDB: Using Linux native AIO
 
[00] 2022-03-28 18:29:33 Starting InnoDB instance for recovery.
 
[00] 2022-03-28 18:29:33 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
 
2022-03-28 18:29:33 0 [Note] InnoDB: Uses event mutexes
 
2022-03-28 18:29:33 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
 
2022-03-28 18:29:33 0 [Note] InnoDB: Number of pools: 1
 
2022-03-28 18:29:33 0 [Note] InnoDB: Using SSE4.2 crc32 instructions
 
2022-03-28 18:29:33 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
 
2022-03-28 18:29:33 0 [Note] InnoDB: Completed initialization of buffer pool
 
2022-03-28 18:29:33 0 [Note] InnoDB: Opened 2 undo tablespaces
 
2022-03-28 18:29:33 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=42708,42708
 
2022-03-28 18:29:33 0 [Note] InnoDB: Opened 2 undo tablespaces
 
[00] 2022-03-28 18:29:33 Last binlog file , position 0
 
[00] 2022-03-28 18:29:33 completed OK!

Copy-back operation

 
[root@localhost mysql]# ls
 
aria_log.00000001  ddl.log           ib_buffer_pool  ib_logfile0  localhost.pid      mysql       mysql_upgrade_info  sys   undo001
 
aria_log_control   ddl_recovery.log  ibdata1         ibtmp1       multi-master.info  mysql.sock  performance_schema  test  undo002
 
[root@localhost mysql]# rm -fr *
 
[root@localhost mysql]# ll
 
total 0
 
[root@localhost mysql]# systemctl stop mysql
 
[root@localhost mysql]# mariadb-backup --print-defaults -uroot --target-dir=//mnt/mariadb-backup/ --copy-back
 
mariadb-backup would have been started with the following arguments:
 
--innodb_undo_tablespaces=2 --innodb_undo_directory=./ --log_error=/var/log/mysql/error.log -uroot --target-dir=//mnt/mariadb-backup/ --copy-back
 
[root@localhost mysql]# rm -fr *
 
[root@localhost mysql]# mariadb-backup -uroot --target-dir=//mnt/mariadb-backup/ --copy-back
 
mariadb-backup based on MariaDB server 10.7.3-MariaDB Linux (x86_64)
 
mariadb-backup: Can't create file './undo001' (errno: 17 "File exists")
 
[01] 2022-03-28 18:30:40 error: cannot open the destination stream for undo001
 
[01] 2022-03-28 18:30:40 Error: copy_file() failed.

Tried copy-back operation by passing innodb-undo-directory as argument

 
[root@localhost mysql]# mariadb-backup  -uroot --target-dir=//mnt/mariadb-backup/ --copy-back --innodb_undo_directory=./
 
mariadb-backup based on MariaDB server 10.7.3-MariaDB Linux (x86_64)
 
mariadb-backup: Can't create file './undo001' (errno: 17 "File exists")
 
[01] 2022-03-28 19:27:58 error: cannot open the destination stream for undo001
 
[01] 2022-03-28 19:27:58 Error: copy_file() failed.

Successful Copy-back operation when we specifiy the innodb-undo-directory as absolute path

 
[root@localhost mysql]# mariadb-backup  -uroot --target-dir=//mnt/mariadb-backup/ --copy-back --innodb_undo_directory=/var/lib/mysql/
 
mariadb-backup based on MariaDB server 10.7.3-MariaDB Linux (x86_64)
 
[01] 2022-03-28 19:26:52 Copying undo001 to /var/lib/mysql/undo001
 
[01] 2022-03-28 19:26:52         ...done
 
[01] 2022-03-28 19:26:52 Copying undo002 to /var/lib/mysql/undo002
 
[01] 2022-03-28 19:26:52         ...done
 
[01] 2022-03-28 19:26:52 Copying ibdata1 to /var/lib/mysql/./ibdata1
 
[01] 2022-03-28 19:26:52         ...done
 
[01] 2022-03-28 19:26:52 Copying ./mysql/innodb_table_stats.ibd to /var/lib/mysql/mysql/innodb_table_stats.ibd
 
[01] 2022-03-28 19:26:52         ...done
 
[01] 2022-03-28 19:26:52 Copying ./mysql/innodb_index_stats.ibd to /var/lib/mysql/mysql/innodb_index_stats.ibd
 
[01] 2022-03-28 19:26:52         ...done
 
....
 
[01] 2022-03-28 19:26:52 Copying ./ib_buffer_pool to /var/lib/mysql/ib_buffer_pool
 
[01] 2022-03-28 19:26:52         ...done
 
[01] 2022-03-28 19:26:52 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
 
[01] 2022-03-28 19:26:52         ...done
 
[00] 2022-03-28 19:26:52 completed OK!



 Comments   
Comment by Marko Mäkelä [ 2022-06-22 ]

I think that this bug should affect any version of mariadb-backup. It is a rather important one, because in MDEV-21952 our recommendation is to configure multiple innodb_undo_tablespaces.

Comment by Alexander Barkov [ 2023-03-20 ]

https://github.com/MariaDB/server/commit/00adf4583a580fcb9cc7ddaddd4540a64bcc2b84 is OK to push.
Thanks.

Comment by Vladislav Lesin [ 2023-03-21 ]

I have not pushed it to 10.3, as 10.3 will be frozen soon, and only critical bug fixes are accepted. This bug does not look critical despite its status in Jira.

Generated at Thu Feb 08 09:58:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.