2uzhan.com
Advertisement
Now Place:2uzhan.com » Is it possible to take backup using tablespace offline in mysql?

Is it possible to take backup using tablespace offline in mysql?

MySQL @ July 16, 2012   Views:0

create tablespace works only for clusters? Or for standalone Mysql instances also? If its for standalone also is it possible to take mysql tablespace offline and take .ibd file for backup and restoring in a different mysql instance to pick it up?

--------------Solutions-------------

yes, create tablespace works for NDB cluster only. However you can take offline snapshot of innodb:

1. Stop mysql server (/etc/init.d/mysql stop or mysqladmin -uroot shutdown)
2. use file system commands (e.g scp/rsync) to copy all files in datadir not just .ibd files because the shared table space (e.g. ibdata) and innodb_redo log files are required too.

3. Restart the master server.

You can try Innodb hotbackup tool or percona xtrabackup tool:

http://www.percona.com/software/percona-xtrabackup/downloads/

http://www.innodb.com/wp/wp-content/uploads/2007/05/introduction-to-innodb-and-hot-backup-final.pdf

http://www.innodb.com/doc/hot_backup/manual.html

Edited 3 time(s). Last edit at 07/17/2012 03:17AM by Aftab Khan. Yeah if its for cluster only then no use for me. As I have only standalones. I was going through some docs reg online backup I found this one innobackup will call ibbackup for innodb and myisam tables backup that wouldn't require any kinda locking on tables. But its only for commercial ones I guess..

Edited 1 time(s). Last edit at 07/19/2012 03:18AM by Mannoj Kumar. xtrabackup IS free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases:

http://www.percona.com/software/percona-xtrabackup/downloads/ Thanks Aftab !! I tried xtraBackup from Percona its damn cool for innodb. But it locks the tables for myisam and other engines except xtraDB and innodb engines.

But for innodb online ibd files backups this one is awesome...

120724 20:09:28 innobackupex-1.5.1: All tables locked and flushed to disk

120724 20:09:28 innobackupex-1.5.1: Starting to backup .frm, .MRG, .MYD, .MYI,
innobackupex-1.5.1: .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV and .opt files in
innobackupex-1.5.1: subdirectories of '/var/lib/mysql'
innobackupex-1.5.1: Backing up files '/var/lib/mysql/performance_schema/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (18 files)
innobackupex-1.5.1: Backing up files '/var/lib/mysql/mysql/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (72 files)
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/mts_sub.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/db.opt'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/ids.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/longr.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/longr.MYI'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/longr.MYD'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oraclew/contentq.frm'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/drops/db.opt'
innobackupex-1.5.1: Backing up file '/var/lib/mysql/oracle/db.opt'
innobackupex-1.5.1: Backing up files '/var/lib/mysql/mts_service_delivery/*.{frm,MYD,MYI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (36 files)
120724 20:09:29 innobackupex-1.5.1: Finished backing up .frm, .MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSV, .CSM and .opt files

innobackupex-1.5.1: Resuming ibbackup

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

xtrabackup: Transaction log of lsn (122170332892) to (122170332892) was copied.
120724 20:09:33 innobackupex-1.5.1: All tables unlocked

Tags:
© 2018 2uzhan.com Contact