Not the answer you need?
Register and ask your own question!

Backup is locking InnoDB tables

seqizzseqizz EntrantInactive User Role Beginner

I've set up a bacula to get backup of a master-master percona cluster, which works at (mostly) unused time of the DB by running a script. I'm using unused master node for it because It'd be a read-only job.
Backup is OK. Yet I'm getting lock errors with few cron sql updates meantime on the InnoDB tables:

I've tried "--no-lock" (which later I saw is for MyISAM) and even "--safe-slave-backup" with no luck. Somehow it is locking.

Backup script is originated from and getting backups is done by (at my instance):

innobackupex --no-lock --galera-info --user="$user" --password="$pass" (--incremental --incremental-basedir= if incr.) /dir/to/backup

Here is a part from backup log:

innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/root/mysql-backups/incr/2014-07-07_04-05-23 --tmpdir=/tmp --incremental-basedir='/root/mysql-backups/full/2014-07-06_09-08-57'
innobackupex: Waiting for ibbackup (pid=25079) to suspend
innobackupex: Suspend file '/root/mysql-backups/incr/2014-07-07_04-05-23/xtrabackup_suspended_2'
xtrabackup_55 version 2.1.6 for Percona Server 5.5.31 Linux (x86_64) (revision id: 702)
incremental backup from 87217402617 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = /var/lib/mysql
xtrabackup: innodb_data_file_path = ibdata1:1000M:autoextend
xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 10737418240
>> log scanned up to (87687025914)
140707 4:05:24 InnoDB: Warning: allocated tablespace 1965, old maximum was 9
>> log scanned up to (87687031390)
xtrabackup: using the full scan for incremental backup
[01] Copying /var/lib/mysql/ibdata1 to /root/mysql-backups/incr/2014-07-07_04-05-23/
>> log scanned up to (87687032998)
>> log scanned up to ....

I'd appreciate if you can point me to the right direction..

//edit: tried with --lock-wait-threshold=5 --lock-wait-query-type=all --lock-wait-timeout=60 option, no change. Like the locking process is the backup itself..


  • psongpsong Contributor Inactive User Role Beginner
    XtraBackup does a binary copy of the Innodb files and doesn't hold any innodb row locks during the backup process. You could try the following to get more information about the lock waits:

    1) Check innodb_lock_wait_timeout setting:

    mysql> show global variables like 'innodb_lock_wait_timeout';

    2) Run the command in parallel with the innobackupex and the cron jobs.
    while true; do (date; echo "select l.requesting_trx_id, r.trx_mysql_thread_id, r.trx_query, l.blocking_trx_id, b.trx_mysql_thread_id, b.trx_query FROM information_schema.INNODB_LOCK_WAITS l INNER JOIN information_schema.INNODB_TRX r on l.requesting_trx_id=r.trx_id INNER JOIN information_schema.INNODB_TRX b on l.blocking_trx_id=b.trx_id \G" | mysql ) | tee -a trx_locks.out; sleep 10; done

    It would show each lock wait with the requesting trx's id, query, and the blocking trx's id, query. It would then become clear which query is blocking others.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.