MySQL innodb 備份

2010102010:27

InnoDB Hot Backup
原廠,可線上備份 innodb,付費軟體 一年USD 480


XtraBackup
open source,可線上備份 innodb


ZRM for MySQL




若可以 shutdown MySQL Server 時
可直接備份二進位檔方式:

ref:
13.6.6. Backing Up and Recovering an InnoDB Database

If you are able to shut down your MySQL server, you can make a binary backup that consists of all files used by InnoDB to manage its tables. Use the following procedure:

  1. Shut down the MySQL server and make sure that it stops without errors.

  2. Copy all InnoDB data files (ibdataX files and .ibd files) into a safe place.

  3. Copy all the .frm files for InnoDB tables to a safe place.

  4. Copy all InnoDB log files (ib_logfileX files) to a safe place.

  5. Copy your my.cnf configuration file or files to a safe place.  


note:
有關 .idb 檔案:
13.2.2.1. Using Per-Table Tablespaces




但還一種最方便
直接架一台 slave 來抄寫 master 的資料
然後定期把 slave 上的資料打包~最方便~~

唯一的缺點是要多準備一台 server
但這 slave 只負責資料複製,機器效能不需太好~

另外有點奇怪的是
怎麼一堆文章教人用 mysqldump ?
若是極小量資料,用 mysqldump 還 ok
對於數百MB、GB 以上的資料庫,mysqldump 要怎麼 dump?