Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4991

Warning: Creating default object from empty value in /www/htdocs/v030397/mysql-qotd/wp-content/plugins/sitepress-multilingual-cms/sitepress.class.php on line 4993
Question 69: Pick 2 ways of making “non-locking” backups for Innodb – MySQL Question of the Day

Skip to content

Question 69: Pick 2 ways of making “non-locking” backups for Innodb

16. June 2010
By plogi in mysql questions

1 Comment

a) mysqlhotcopy
b) bcp
c) mysqldump --single-transaction --master-data
d) ibbackup

 
[ Data Backup and Recovery Methods (15%) - Introduction ]

Tags: , ,

Comment Feed

One Response

  1. plogi — 16. June 2010 @ 18:52:04 —


    c d
     
    For Innodb tables you can quite easily take a backup, which does not place a read lock on the tables:
    You can make a text backup with mysqldump and a binary backup with ibbackup – all without readlocking the tables!
    Mysqlhotcopy works only for Myisam and Archive tables. BCP is the bulk-copy-program of mssql server.

    Filesystem snapshots are also very handy and allow to take consistent backups (of all table engines) with minimal locking:
    - “flush tables with read lock;”
    - commit a transaction on an innodb table
    - take a fs snapshot
    - “unlock tables;”
    - copy files from snapshot to tape/nfs
    - release snapshot

    Filesystem snapshots can for example be taken with the linux lvm, or with fssnap on solaris.
    Storage systems (like netapp to name only one) also offer snapshot capabilities.

You must be logged in to post a comment.