Percona XtraBackup moved to https://jira.percona.com/projects/PXB

Bug #1398994
Comment #1

Comment 1 for bug 1398994

Revision history for this message

Nilnandan Joshi (nilnandan-joshi) wrote on 2014-12-09:

I could not reproduce this with PS 5.6.21 and Xtrabackup 2.2.6. Can you able to provide exact steps for taking backup and apply-log?

Taking backup for only "nil" DB.

nilnandan@Dell-XPS:~$ sudo innobackupex --user=root --password=root --databases="nil" /home/nilnandan/backup
[sudo] password for nilnandan:

...
innobackupex: Using server version 5.6.21-70.1

innobackupex: Created backup directory /home/nilnandan/backup/2014-12-09_10-22-24

141209 10:22:24 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/nilnandan/backup/2014-12-09_10-22-24 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='nil'
innobackupex: Waiting for ibbackup (pid=5653) to suspend
innobackupex: Suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_suspended_2'

xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
>> log scanned up to (38314921)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/nilnandan/backup/2014-12-09_10-22-24/ibdata1
[01] ...done
>> log scanned up to (38314921)
xtrabackup: Creating suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_suspended_2' with pid '5654'

141209 10:22:26 innobackupex: Continuing after ibbackup has suspended
141209 10:22:26 innobackupex: Executing LOCK TABLES FOR BACKUP...
141209 10:22:26 innobackupex: Backup tables lock acquired

141209 10:22:26 innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//nil/db.opt'
innobackupex: Backing up file '/var/lib/mysql//nil/test.frm'
141209 10:22:26 innobackupex: Finished backing up non-InnoDB tables and files

141209 10:22:26 innobackupex: Executing LOCK BINLOG FOR BACKUP...
>> log scanned up to (38314921)
141209 10:22:26 innobackupex: Executing FLUSH ENGINE LOGS...
141209 10:22:26 innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '38314921'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (38314921)

xtrabackup: Creating suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_log_copied' with pid '5654'
xtrabackup: Transaction log of lsn (38314921) to (38314921) was copied.
141209 10:22:27 innobackupex: Executing UNLOCK BINLOG
141209 10:22:27 innobackupex: Executing UNLOCK TABLES
141209 10:22:27 innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/nilnandan/backup/2014-12-09_10-22-24'
141209 10:22:27 innobackupex: Connection to database server closed
141209 10:22:27 innobackupex: completed OK!
nilnandan@Dell-XPS:~$

root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24# ll
total 26692
drwxr-xr-x 3 root root 4096 Dec 9 10:22 ./
drwxrwxr-x 4 nilnandan nilnandan 4096 Dec 9 10:22 ../
-rw-r--r-- 1 root root 358 Dec 9 10:22 backup-my.cnf
-rw-r----- 1 root root 27262976 Dec 9 10:22 ibdata1
drwxr-xr-x 2 root root 4096 Dec 9 10:22 nil/
-rw-r----- 1 root root 91 Dec 9 10:22 xtrabackup_checkpoints
-rw-r--r-- 1 root root 542 Dec 9 10:22 xtrabackup_info
-rw-r----- 1 root root 2560 Dec 9 10:22 xtrabackup_logfile
root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24#

After running --apply log

nilnandan@Dell-XPS:~$ sudo innobackupex --apply-log /home/nilnandan/backup/2014-12-09_10-22-24

...
InnoDB: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Table mysql/innodb_index_stats in the InnoDB data dictionary has tablespace id 2, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_master_info in the InnoDB data dictionary has tablespace id 4, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
...

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 38330977

141209 10:23:32 innobackupex: Restarting xtrabackup with command: xtrabackup --defaults-file="/home/nilnandan/backup/2014-12-09_10-22-24/backup-my.cnf" --defaults-group="mysqld" --prepare --target-dir=/home/nilnandan/backup/2014-12-09_10-22-24
for creating ib_logfile*

xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/nilnandan/backup/2014-12-09_10-22-24
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.8
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=38330977
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 38331404

[notice (again)]
If you use binary log and don't use any hack of group commit,
the binary log position seems to be:

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 38331453
141209 10:23:34 innobackupex: completed OK!
nilnandan@Dell-XPS:~$

root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24# ll
total 127060
drwxr-xr-x 3 root root 4096 Dec 9 10:23 ./
drwxrwxr-x 4 nilnandan nilnandan 4096 Dec 9 10:22 ../
-rw-r--r-- 1 root root 358 Dec 9 10:22 backup-my.cnf
-rw-r----- 1 root root 27262976 Dec 9 10:23 ibdata1
-rw-r--r-- 1 root root 50331648 Dec 9 10:23 ib_logfile0
-rw-r--r-- 1 root root 50331648 Dec 9 10:23 ib_logfile1
drwxr-xr-x 2 root root 4096 Dec 9 10:22 nil/
-rw-r----- 1 root root 91 Dec 9 10:23 xtrabackup_checkpoints
-rw-r--r-- 1 root root 542 Dec 9 10:22 xtrabackup_info
-rw-r----- 1 root root 2097152 Dec 9 10:23 xtrabackup_logfile
root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24#

^^ here, I'm not seeing other databases.

I could not reproduce this with PS 5.6.21 and Xtrabackup 2.2.6. Can you able to provide exact steps for taking backup and apply-log?

Taking backup for only "nil" DB.

nilnandan@Dell-XPS:~$ sudo innobackupex --user=root --password=root --databases="nil" /home/nilnandan/backup
[sudo] password for nilnandan:

...
innobackupex:  Using server version 5.6.21-70.1

innobackupex: Created backup directory /home/nilnandan/backup/2014-12-09_10-22-24

141209 10:22:24  innobackupex: Starting ibbackup with command: xtrabackup  --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/home/nilnandan/backup/2014-12-09_10-22-24 --innodb_log_file_size="50331648" --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/tmp --extra-lsndir='/tmp' --databases='nil'
innobackupex: Waiting for ibbackup (pid=5653) to suspend
innobackupex: Suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_suspended_2'

xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
>> log scanned up to (38314921)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/nilnandan/backup/2014-12-09_10-22-24/ibdata1
[01]        ...done
>> log scanned up to (38314921)
xtrabackup: Creating suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_suspended_2' with pid '5654'

141209 10:22:26  innobackupex: Continuing after ibbackup has suspended
141209 10:22:26  innobackupex: Executing LOCK TABLES FOR BACKUP...
141209 10:22:26  innobackupex: Backup tables lock acquired

141209 10:22:26  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/mysql//nil/db.opt'
innobackupex: Backing up file '/var/lib/mysql//nil/test.frm'
141209 10:22:26  innobackupex: Finished backing up non-InnoDB tables and files

141209 10:22:26  innobackupex: Executing LOCK BINLOG FOR BACKUP...
>> log scanned up to (38314921)
141209 10:22:26  innobackupex: Executing FLUSH ENGINE LOGS...
141209 10:22:26  innobackupex: Waiting for log copying to finish

xtrabackup: The latest check point (for incremental): '38314921'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (38314921)

xtrabackup: Creating suspend file '/home/nilnandan/backup/2014-12-09_10-22-24/xtrabackup_log_copied' with pid '5654'
xtrabackup: Transaction log of lsn (38314921) to (38314921) was copied.
141209 10:22:27  innobackupex: Executing UNLOCK BINLOG
141209 10:22:27  innobackupex: Executing UNLOCK TABLES
141209 10:22:27  innobackupex: All tables unlocked

innobackupex: Backup created in directory '/home/nilnandan/backup/2014-12-09_10-22-24'
141209 10:22:27  innobackupex: Connection to database server closed
141209 10:22:27  innobackupex: completed OK!
nilnandan@Dell-XPS:~$

root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24# ll
total 26692
drwxr-xr-x 3 root      root          4096 Dec  9 10:22 ./
drwxrwxr-x 4 nilnandan nilnandan     4096 Dec  9 10:22 ../
-rw-r--r-- 1 root      root           358 Dec  9 10:22 backup-my.cnf
-rw-r----- 1 root      root      27262976 Dec  9 10:22 ibdata1
drwxr-xr-x 2 root      root          4096 Dec  9 10:22 nil/
-rw-r----- 1 root      root            91 Dec  9 10:22 xtrabackup_checkpoints
-rw-r--r-- 1 root      root           542 Dec  9 10:22 xtrabackup_info
-rw-r----- 1 root      root          2560 Dec  9 10:22 xtrabackup_logfile
root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24#

After running --apply log

nilnandan@Dell-XPS:~$ sudo innobackupex --apply-log /home/nilnandan/backup/2014-12-09_10-22-24

...
InnoDB: Restoring possible half-written data pages 
InnoDB: from the doublewrite buffer...
InnoDB: Table mysql/innodb_index_stats in the InnoDB data dictionary has tablespace id 2, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/innodb_table_stats in the InnoDB data dictionary has tablespace id 1, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_master_info in the InnoDB data dictionary has tablespace id 4, but tablespace with that id or name does not exist. Have you deleted or moved .ibd files? This may also be a table created with CREATE TEMPORARY TABLE whose .ibd and .frm files MySQL automatically removed, but the table still exists in the InnoDB internal data dictionary.
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
...

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 38330977

141209 10:23:32  innobackupex: Restarting xtrabackup with command: xtrabackup  --defaults-file="/home/nilnandan/backup/2014-12-09_10-22-24/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/home/nilnandan/backup/2014-12-09_10-22-24
for creating ib_logfile*

xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: )
xtrabackup: cd to /home/nilnandan/backup/2014-12-09_10-22-24
xtrabackup: This target seems to be already prepared.
xtrabackup: notice: xtrabackup_logfile was already used to '--prepare'.
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.8
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 48 MB
InnoDB: Setting log file ./ib_logfile1 size to 48 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=38330977
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.21 started; log sequence number 38331404

[notice (again)]
  If you use binary log and don't use any hack of group commit,
  the binary log position seems to be:

root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24# ll
total 127060
drwxr-xr-x 3 root      root          4096 Dec  9 10:23 ./
drwxrwxr-x 4 nilnandan nilnandan     4096 Dec  9 10:22 ../
-rw-r--r-- 1 root      root           358 Dec  9 10:22 backup-my.cnf
-rw-r----- 1 root      root      27262976 Dec  9 10:23 ibdata1
-rw-r--r-- 1 root      root      50331648 Dec  9 10:23 ib_logfile0
-rw-r--r-- 1 root      root      50331648 Dec  9 10:23 ib_logfile1
drwxr-xr-x 2 root      root          4096 Dec  9 10:22 nil/
-rw-r----- 1 root      root            91 Dec  9 10:23 xtrabackup_checkpoints
-rw-r--r-- 1 root      root           542 Dec  9 10:22 xtrabackup_info
-rw-r----- 1 root      root       2097152 Dec  9 10:23 xtrabackup_logfile
root@Dell-XPS:/home/nilnandan/backup/2014-12-09_10-22-24#

^^ here, I'm not seeing other databases.