Medium If we have autocommit disabled for the user that takes the backup, then the PERCONA_SCHEMA.xtrabackup_history table will not be updated with the new backup entry, even if --history is used.
How to repeat:
1- Set bkpuser as mentioned in the documentation (and add ALL on the percona schema):
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 'aoeuAOEU123...'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; mysql> GRANT ALL ON PERCONA_SCHEMA.* TO 'bkpuser'@'localhost';
2- Disable autocommit:
shell> mysql -e "set global init_connect='set autocommit=0'"
3- Run backup with that user:
shell> xtrabackup --backup --target-dir=/data/backups/ --history --user=bkpuser --password='aoeuAOEU123...' 2>&1 | grep -i ok
180722 18:59:20 completed OK!
4- Check history table:
shell> mysql -e "select * from PERCONA_SCHEMA.xtrabackup_history"
... empty result ...
To check it is indeed working:
1- Enable autocommit and clear backup directory:
shell> mysql -e "set global init_connect='set autocommit=1'"
shell> rm -rf /data/backups/*
2- Run backup again:
shell> xtrabackup --backup --target-dir=/data/backups/ --history --user=bkpuser --password='aoeuAOEU123...' 2>&1 | grep -i ok
180722 19:06:10 completed OK!
3- Check history table:
mysql -Be "select * from PERCONA_SCHEMA.xtrabackup_history\G"
*************************** 1. row ***************************
uuid: 4ba1e023-8de2-11e8-a6ae-525400cae48b
name: NULL
tool_name: xtrabackup
tool_command: --backup --target-dir=/data/backups/ --history --user=bkpuser --password=...
tool_version: 2.4.12
ibbackup_version: 2.4.12
server_version: 5.7.22-22
start_time: 2018-07-22 19:06:06
end_time: 2018-07-22 19:06:10
lock_time: 0
binlog_pos: NULL
innodb_from_lsn: 0
innodb_to_lsn: 2640076
partial: N
incremental: N
format: file
compact: N
compressed: N
encrypted: N
How to fix:
Inspect code, and add an explicit commit after inserting the row to the xtrabackup_history table, so it will not depend on the autocommit functionality being enabled.
