...
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#
...
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
...
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:
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' nilnandan/ backup/ 2014-12- 09_10-22- 24/xtrabackup_ suspended_ 2'
innobackupex: Waiting for ibbackup (pid=5653) to suspend
innobackupex: Suspend file '/home/
xtrabackup version 2.2.6 based on MySQL server 5.6.21 Linux (x86_64) (revision id: ) data_home_ dir = ./ data_file_ path = ibdata1: 12M:autoextend log_group_ home_dir = ./ log_files_ in_group = 2 log_file_ size = 50331648 /backup/ 2014-12- 09_10-22- 24/ibdata1 nilnandan/ backup/ 2014-12- 09_10-22- 24/xtrabackup_ suspended_ 2' with pid '5654'
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_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
>> log scanned up to (38314921)
xtrabackup: Generating a list of tablespaces
[01] Copying ./ibdata1 to /home/nilnandan
[01] ...done
>> log scanned up to (38314921)
xtrabackup: Creating suspend file '/home/
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 mysql// nil/db. opt' mysql// nil/test. frm'
innobackupex: in subdirectories of '/var/lib/mysql/'
innobackupex: Backing up file '/var/lib/
innobackupex: Backing up file '/var/lib/
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' Dell-XPS: ~$
141209 10:22:27 innobackupex: Connection to database server closed
141209 10:22:27 innobackupex: completed OK!
nilnandan@
root@Dell- XPS:/home/ nilnandan/ backup/ 2014-12- 09_10-22- 24# ll checkpoints XPS:/home/ nilnandan/ backup/ 2014-12- 09_10-22- 24#
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_
-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-
After running --apply log
nilnandan@ Dell-XPS: ~$ sudo innobackupex --apply-log /home/nilnandan /backup/ 2014-12- 09_10-22- 24
... 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. dev.mysql. com/doc/ refman/ 5.6/en/ innodb- troubleshooting -datadict. html 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. dev.mysql. com/doc/ refman/ 5.6/en/ innodb- troubleshooting -datadict. html 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: Restoring possible half-written data pages
InnoDB: from the doublewrite buffer...
InnoDB: Table mysql/innodb_
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/innodb_
InnoDB: It will be removed from the data dictionary.
InnoDB: Please refer to
InnoDB: http://
InnoDB: for how to resolve the issue.
InnoDB: Table mysql/slave_
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: ) /backup/ 2014-12- 09_10-22- 24 data_home_ dir = ./ data_file_ path = ibdata1: 12M:autoextend log_group_ home_dir = ./ log_files_ in_group = 2 log_file_ size = 50331648 data_home_ dir = ./ data_file_ path = ibdata1: 12M:autoextend log_group_ home_dir = ./ log_files_ in_group = 2 log_file_ size = 50331648
xtrabackup: cd to /home/nilnandan
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_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
xtrabackup: innodb_
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 Dell-XPS: ~$
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 38331453
141209 10:23:34 innobackupex: completed OK!
nilnandan@
root@Dell- XPS:/home/ nilnandan/ backup/ 2014-12- 09_10-22- 24# ll checkpoints XPS:/home/ nilnandan/ backup/ 2014-12- 09_10-22- 24#
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_
-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-
^^ here, I'm not seeing other databases.