Where is MyISAM Tables and DB in my Backup ?

MySQL & MariaDB Percona XtraBackup
1

Hi,
i’m tryng this XtraBackup on a Debian Squeeze 64bit with the standard MySQL-Server.
When i run:

xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/home/BACKUP/mysql_backup_locali/

ad list the target dir, i see only databases with innodb.
XtraBackup have excluded all MyISAM tables and all the BD with only MyISAM Tables:

xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/home/BACKUP/mysql_backup_locali/
xtrabackup Ver undefined Rev undefined for 5.1.55 unknown-linux-gnu (x86_64)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /var/lib/mysql/
xtrabackup: Target instance is assumed as followings.
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 5242880
xtrabackup: use O_DIRECT

log scanned up to (47933)
[01] Copying ./ibdata1
to /home/BACKUP/mysql_backup_locali//ibdata1
[01] …done
[01] Copying ./enrico/testinnodb.ibd
to /home/BACKUP/mysql_backup_locali//enrico/testinnodb.ibd
[01] …done
xtrabackup: The latest check point (for incremental): ‘47933’
log scanned up to (47933)
xtrabackup: Stopping log copying thread.
xtrabackup: Transaction log of lsn (47933) to (47933) was copied.

ls -la /var/lib/mysql/
totale 20524
drwx------ 8 mysql mysql 4096 20 giu 12.49 .
drwxr-xr-x 37 root root 4096 17 giu 16.07 …
-rw-r–r-- 1 root root 0 6 giu 11.57 debian-5.1.flag
drwx------ 2 mysql mysql 4096 20 giu 12.15 enrico
-rw-rw---- 1 mysql mysql 10485760 20 giu 12.16 ibdata1
-rw-rw---- 1 mysql mysql 5242880 20 giu 12.16 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 6 giu 11.57 ib_logfile1
drwx------ 2 mysql mysql 4096 6 giu 11.57 lost@002bfound
drwx------ 2 root root 4096 13 giu 19.09 lost+found
-rw-rw---- 1 mysql mysql 90 20 giu 12.50 master.info
drwx------ 2 mysql root 4096 6 giu 11.57 mysql
-rw------- 1 root root 6 6 giu 11.57 mysql_upgrade_info
drwx------ 2 mysql mysql 4096 17 giu 16.08 phpmyadmin
-rw-rw---- 1 mysql mysql 66 20 giu 12.50 relay-log.info
drwx------ 2 mysql mysql 4096 20 giu 12.49 testdb2

root@nodo1:/home/BACKUP/mysql_backup_locali# ls -la
totale 10260
drwxr-xr-x 3 root root 4096 20 giu 12.50 .
drwxr-xr-x 4 root root 4096 20 giu 12.18 …
drwx------ 2 root root 4096 20 giu 12.50 enrico
-rw-r–r-- 1 root root 10485760 20 giu 12.50 ibdata1
-rw-r–r-- 1 root root 73 20 giu 12.50 xtrabackup_checkpoints
-rw-r–r-- 1 root root 2560 20 giu 12.50 xtrabackup_logfile

Thanks

2

u need to use innobackupex instead of xtrabackup

3

@yena
xudba is right. xtrabackup is only for innodb. innobackupex can do what you expect. If you use xtrabackup to dump your myisam tables, you could not get any benefits in my opinion.

@xudba
mysqldump use the combination --master-data and --single-transaction to get the bullet-proof binlog position and create a consistent snapshot. I don’t know how they works. Could you please explain the internal for me?

And how about xtrabackup?

much more thanks to you.

4

–master-data is to dump full database from master and record the binlog position of the beginning of the backup. single-transaction make all innodb tables consistent (same binlog position,block all updates) during the backup. together, it can do point-in-time recovery to the beginning of the backup. For xtrabackup is different, it copies each innodb at table different timestamp (not consistent), then record the end of backup’s binlog position, and recover all innodb tables to the end point of binlog position, so the backup is consistent at the end of the backup, thus, ready for point-in-time recovery.