Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.6
-
None
Description
Restoring MariaDB backups taken with mariabackup utility requires to perform recovery on the backup files using the --prepare option. Using the --prepare option will make the backups consistent on the backup target, in other words "recover the database data" on the backup target.
This requires that the backups can be overwritten, and that the backup devices supports seek calls, as only portion of the backup files will be overwritten (read and apply changes from the transaction logs to the database files?)
A backup is intended to be a copy of the database taken at that time, and should be write protected hindering it from being corrupted by malwares, ransomware, bugs etc.
From wikipedia regarding what a backup is:
The general purpose of a backup is to prevent it from being destroyed, deleted, or corrupted, by placing the backups elsewhere, and protect it from being overwritten.
The general purpose of a backup:
- backup is a copy of computer data taken and stored elsewhere so that it may be used to restore the original after a data loss event.
- Backups can be used to recover data after its loss from data deletion or corruption, or to recover data from an earlier time
Other databases has two phases during a database restore.
Phase 1: restore database data
Phase 2: recover the database data
The other databases recovery phase is performed after restoration of the database files, and on the targeted location.
In MariaDB the phase looks like this:
Phase 1: recover the database on the backup location
Phase 2: restore the recovered database to the target location
Suggesting MariaDB to change the order for the --prepare, so that these steps are done on the targeted device rather than on the backup location
The utility also core dumps if the backup device is write protected.
Output from the --prepare test:
mysql@mariadb:~/::mariadb-backup --prepare --target-dir=/ptxfs_back/mysql/pf_test4
mariadb-backup based on MariaDB server 10.5.6-4-MariaDB Linux (x86_64)
[00] 2021-01-20 08:58:12 cd to /ptxfs_back/mysql/pf_test4/
[00] 2021-01-20 08:58:13 This target seems to be not prepared yet.
[00] 2021-01-20 08:58:13 mariabackup: using the following InnoDB configuration for recovery:
[00] 2021-01-20 08:58:13 innodb_data_home_dir = .
[00] 2021-01-20 08:58:13 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-01-20 08:58:13 innodb_log_group_home_dir = .
[00] 2021-01-20 08:58:13 InnoDB: Using Linux native AIO
[00] 2021-01-20 08:58:13 Starting InnoDB instance for recovery.
[00] 2021-01-20 08:58:13 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2021-01-20 8:58:13 0 [Note] InnoDB: Uses event mutexes
2021-01-20 8:58:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2021-01-20 8:58:13 0 [Note] InnoDB: Number of pools: 1
2021-01-20 8:58:13 0 [Note] InnoDB: Using SSE4.2 crc32 instructions
mariadb-backup: O_TMPFILE is not supported on /tmp (disabling future attempts)
2021-01-20 8:58:13 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2021-01-20 8:58:13 0 [Note] InnoDB: Completed initialization of buffer pool
2021-01-20 8:58:13 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2021-01-20 8:58:14 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=9504191888
2021-01-20 8:58:15 0 [Warning] InnoDB: Retry attempts for writing partial data failed.
2021-01-20 8:58:15 0 [ERROR] InnoDB: Write to file ./ib_logfile0 failed at offset 1536, 512 bytes should have been written, only 0 were written. Operating system error number 1. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2021-01-20 8:58:15 0 [ERROR] InnoDB: Error number 1 means 'Operation not permitted'
2021-01-20 8:58:15 0 [Note] InnoDB: Some operating system error numbers are described at https://mariadb.com/kb/en/library/operating-system-error-codes/
2021-01-20 8:58:15 0 [ERROR] [FATAL] InnoDB: write(./ib_logfile0) returned I/O error
210120 8:58:15 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs
We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.Server version: 10.5.6-4-MariaDB-enterprise
key_buffer_size=0
read_buffer_size=131072
max_used_connections=0
max_threads=1
thread_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 5990 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong...
stack_bottom = 0x0 thread_stack 0x49000
mariadb-backup(my_print_stacktrace+0x2e)[0x5611ee6741fe]
mariadb-backup(handle_fatal_signal+0x485)[0x5611ee15a115]
addr2line: 'mariadb-backup': No such file
sigaction.c:0(__restore_rt)[0x7f8d9fd6cdc0]
:0(__GI_raise)[0x7f8d9d9a58df]
:0(__GI_abort)[0x7f8d9d98fcf5]
addr2line: 'mariadb-backup': No such file
mariadb-backup(+0x6596cd)[0x5611eddaf6cd]Printing to addr2line failed
mariadb-backup(+0x637276)[0x5611edd8d276]
mariadb-backup(+0xd58d95)[0x5611ee4aed95]
mariadb-backup(+0xd6287f)[0x5611ee4b887f]
mariadb-backup(+0x651c27)[0x5611edda7c27]
mariadb-backup(+0x6a99bd)[0x5611eddff9bd]
mariadb-backup(main+0x177)[0x5611eddbeef7]
??:0(__libc_start_main)[0x7f8d9d991873]
addr2line: 'mariadb-backup': No such file
mariadb-backup(_start+0x2e)[0x5611eddf0c9e]
The manual page at https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains information that should help you find out what is causing the crash.Writing a core file...
Working directory at /ptxfs_back/mysql/pf_test4
Resource Limits:
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size unlimited unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 31123 31123 processes
Max open files 1024 4096 files
Max locked memory 16777216 16777216 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 31123 31123 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h %eAborted (core dumped)
Attachments
Activity
Another thought - would it be possible for the prepare function to happen prior to the data being put in the backup directory? Maybe it could be done on the fly before being written out (to avoid errors in worm filesystems). This would enable a straight copy out of the backup area into the data-dir without any further steps to make the data available to the database.
I don't quite understand what the problem is.
That's right, A backup is intended to be a copy of the database taken at that time, and should be write protected hindering it from being corrupted by malwares, ransomware, bugs etc.
But what mariabackup --backup creates is not "a copy of the database taken at that time", it's a mix of data taken at different points in time, to make it a copy of the database taken at particular point in time, one needs to run mariabackup --prepare. After that you have your backup, defined exactly according to your definition and you can store it write-protected as you want (and should, really).
Alternatively you can store the result of mariabackup --backup in a write-protected way and copy it somewhere to a writable medium before restoring.
The crash is a bug, of course, and should be fixed.
The issue for me is that the capability to "store the result of mariabackup --backup in a write-protected way and copy it somewhere to a writable medium before restoring." is not only time consuming (and therefore in a production environment a key sticking point), but in addition to potentially involve storage teams (to cut & provision the temporary luns), network teams (iscsi?), and the unix teams (mapping the new lun into the o/s) in the provisioning of temporary space turns what should be a seamless 1 team (dba) restore process, into a multi-team, possibly multi-location synchronisation of manpower. You then have the reverse to back out the changes once the database is back into production.
I'm talking of multi-terabyte databases here.
I agree with Wayne.
The issue is that --prepare needs the data to be located on a write enabled storage.
All other databases that I am aware of restores first the data back to the origin location, and in this phase the data is not consistent.
The next step is to make the data consistent, that involves rolling transaction forward to a point in time that the users wants.
But according to the manual of mariabackup utility, it mentioning that --prepare should be performed before the users executes the --copy-back
This means that the backup location has to be write enabled.
If users has to first restore the backup to a write enabled location prior just to execute the --prepare command, than this will be much longer restore time.
A better way would be to performing rollforward recovery at the target location.
Know that this works, but not for part restoration.
Step 1: stop MariaDB
Step 2: remove data files
Step 3: restore data files
Step 4: copy additional needed OS files
Step 5: prepare
Step 6: change ownerships of files
Step 7: start MariaDB
Step 1: stop MariaDB
- systemctl stop mariadb
Step 2: remove data files
- cd /var/lib/mysql
- rm -rf *
Step 3: restore data files
- mariabackup --copy-back --target-dir=/backup/m7 -V --parallel=8
mariabackup based on MariaDB server 10.5.8-MariaDB Linux (x86_64)
[01] 2021-04-12 20:41:42 Copying ibdata1 to /var/lib/mysql/ibdata1
[01] 2021-04-12 20:41:45 ...done
[01] 2021-04-12 20:41:47 Copying ./VMPTX_central/actlog#P#P202007.ibd to /var/lib/mysql/VMPTX_central/actlog#P#P202007.ibd
[01] 2021-04-12 20:42:20 ...done
[01] 2021-04-12 20:42:20 Copying ./VMPTX_central/actlog#P#P202008.ibd to /var/lib/mysql/VMPTX_central/actlog#P#P202008.ibd
[01] 2021-04-12 20:42:20 ...done
[01] 2021-04-12 20:42:20 Copying ./VMPTX_central/actlog#P#P202009.ibd to /var/lib/mysql/VMPTX_central/actlog#P#P202009.ibd
[01] 2021-04-12 20:42:21 ...done
[01] 2021-04-12 20:42:21 Copying ./VMPTX_central/actlog#P#P202010.ibd to /var/lib/mysql/VMPTX_central/actlog#P#P202010.ibd
[01] 2021-04-12 20:42:21 ...done
[01] 2021-04-12 20:42:21 Copying ./VMPTX_central/actlog#P#P202011.ibd to /var/lib/mysql/VMPTX_central/actlog#P#P202011.ibd
....
[01] 2021-04-12 21:01:47 ...done
[01] 2021-04-12 21:01:47 Copying ./mysql/time_zone_transition_type.frm to /var/lib/mysql/mysql/time_zone_transition_type.frm
[01] 2021-04-12 21:01:47 ...done
[01] 2021-04-12 21:01:47 Copying ./mysql/transaction_registry.frm to /var/lib/mysql/mysql/transaction_registry.frm
[01] 2021-04-12 21:01:48 ...done
[01] 2021-04-12 21:01:48 Copying ./mysql/transaction_registry.ibd to /var/lib/mysql/mysql/transaction_registry.ibd
[01] 2021-04-12 21:01:48 ...done
[01] 2021-04-12 21:01:48 Copying ./mysql/user.frm to /var/lib/mysql/mysql/user.frm
[01] 2021-04-12 21:01:48 ...done
[01] 2021-04-12 21:01:48 Copying ./performance_schema/db.opt to /var/lib/mysql/performance_schema/db.opt
[01] 2021-04-12 21:01:48 ...done
[01] 2021-04-12 21:01:48 Copying ./test/db.opt to /var/lib/mysql/test/db.opt
[01] 2021-04-12 21:01:49 ...done
[01] 2021-04-12 21:01:49 Copying ./testdb/db.opt to /var/lib/mysql/testdb/db.opt
[01] 2021-04-12 21:01:49 ...done
[01] 2021-04-12 21:01:49 Copying ./xtrabackup_info to /var/lib/mysql/xtrabackup_info
[01] 2021-04-12 21:01:49 ...done
[00] 2021-04-12 21:01:49 completed OK!
Step 4: Copy additional needed OS files
- cp /backup/m7/backup-my.cnf /var/lib/mysql
- cp /backup/m7/xtrabackup_checkpoints /var/lib/mysql/
Step 5: prepare
- mariabackup --prepare --target-dir=/var/lib/mysql -V
mariabackup based on MariaDB server 10.5.8-MariaDB Linux (x86_64)
[00] 2021-04-12 21:27:35 cd to /var/lib/mysql/
[00] 2021-04-12 21:27:35 This target seems to be not prepared yet.
[00] 2021-04-12 21:27:35 mariabackup: using the following InnoDB configuration for recovery:
[00] 2021-04-12 21:27:35 innodb_data_home_dir = .
[00] 2021-04-12 21:27:35 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2021-04-12 21:27:35 innodb_log_group_home_dir = .
[00] 2021-04-12 21:27:35 InnoDB: Using Linux native AIO
[00] 2021-04-12 21:27:35 Starting InnoDB instance for recovery.
[00] 2021-04-12 21:27:35 mariabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
2021-04-12 21:27:35 0 [Note] InnoDB: Uses event mutexes
2021-04-12 21:27:35 0 [Note] InnoDB: Compressed tables use zlib 1.2.7
2021-04-12 21:27:35 0 [Note] InnoDB: Number of pools: 1
2021-04-12 21:27:35 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2021-04-12 21:27:36 0 [Note] InnoDB: Initializing buffer pool, total size = 104857600, chunk size = 104857600
2021-04-12 21:27:36 0 [Note] InnoDB: Completed initialization of buffer pool
[00] 2021-04-12 21:27:36 Last binlog file , position 0
[00] 2021-04-12 21:27:36 completed OK!
Step 6: change ownerships of files
- cd /var/lib/mysql
- chown -R mysql.mysql .
Step 7: start MariaDB
- systemctl start mariadb
We have exactly the same problem. In order to restore a backup, we need space equivalent to the backup size spare and available in order to do the prepare. This isn't an issue on small databases, but in large installations, the temporary space needed can be several terabytes, which can be expensive to maintain on the rare occasion you need to perform a recovery.