No announcement yet.

Where is MyISAM Tables and DB in my Backup ?

  • Filter
  • Time
  • Show
Clear All
new posts

  • Where is MyISAM Tables and DB in my Backup ?

    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


  • #2
    u need to use innobackupex instead of xtrabackup


    • #3
      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.

      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.


        • #5
          thanks xudba I got your point as follow:

          --master-data and --single-transaction
          This way will get the consistent point in the beginning of backup.It is ONLY for InnoDB which supports MVCC.

          However, it firstly copy the datafiles and logfiles of innodb and at last when it finishes copying ibdata/ibd files, just to get the consistent snapshot(current position) according to iblogfile.

          So the dump from xtrabackup is more closer to production data than mysqldump with the --master-data and --single-transaction option.

          XtraBackup is faster and better in practice,indeed.