{"id":1547,"date":"2010-04-08T18:41:48","date_gmt":"2010-04-08T10:41:48","guid":{"rendered":"http:\/\/www.orczhou.com\/?p=1547"},"modified":"2010-04-08T18:49:11","modified_gmt":"2010-04-08T10:49:11","slug":"xtrabackup-tips","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2010\/04\/xtrabackup-tips\/","title":{"rendered":"Xtrabackup Tips"},"content":{"rendered":"

Percona<\/a> are doing great job on Xtrabackup<\/a> which help us a lot<\/a>. We use it on most of our MySQL server, and it works quite fine. Xtrabckup not only helps us make the daily backup(sometimes recovery), but also help a lot setup the replication with less <\/strong>impact on the online server.<\/p>\n

It is pleasure to share my experience here,which may make Xtrabackup works better.<\/p>\n

1. Is the process of “copy-back” necessary?<\/font><\/p>\n

Although the wiki<\/a> tells us do the “copy-back” after the “apply-log”, but people found it is not necessary. Actually it will take a lot of time if you have a huge databases(e.g. > 50G). <\/p>\n

Here is the usual process to make recovery from a tar backup,which I did in the last month.<\/p>\n

* Extraction of the tar stream:<\/p>\n

date && tar -izxvf xtrabak_20100309_0200.tar.gz && date
\nWed Mar 10 15:17:32 CST 2010
\nWed Mar 10 16:10:01 CST 2010\n<\/div>\n


\nit took about 53 minutes. The tar.gz file was about 46G,and after extraction the files were total 170G.<\/p>\n

* Apply log<\/p>\n

it took about 18 minutes which depends on the server’s QPS when making the backup and the DB size.<\/p>\n

* the “copy back”<\/p>\n

Now, I did the process “copy-back”,which took about 63 minutes.(170G\/63Min = 45MB\/s). The total time spent: 53+18+63 = 134. Now we notice that “copy-back” took about half of the recovery time. <\/p>\n

After that , I do not do the “copy-back”, and try “mv” instead<\/strong>,which is quick and works fine.Since most time when recovery were doing, the situation would be an emergence. So,every minute we save will be valuable.<\/p>\n

2. Backup without any lock<\/font><\/p>\n

Xtrabackupex is cool, and can do real online backup.Innobackupex is convenient,which also make the backup of MyISAM tables,but it will set a lock, which can help u to get the master or slave position , on all the tables. Sometime, the lock last about 60 second and most time it’s ok because we always make backup at the very morning. <\/p>\n

From 0.95rc innobackupex can use option --no-lock to avoid this lock if ALL your tables are InnoDB and you DO NOT CARE about binary log position of backup. It’s awesome.<\/p>\n

3. Again do not forget the -i<\/font><\/p>\n

“You must use -i (--ignore-zeros) option for extraction of the tar stream.”<\/p>\n

tar -izxvf <\/div>\n","protected":false},"excerpt":{"rendered":"

Percona are doing great job on Xtrabackup which help us a lot. We use it on most of our MySQL server, and it works quite fine. Xtrabckup not only helps us make the daily backup(sometimes recovery), but also help a lot setup the replication with less impact on the online server. It is pleasure to […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[6],"tags":[29],"class_list":["post-1547","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-xtrabackup"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/1547","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=1547"}],"version-history":[{"count":17,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/1547\/revisions"}],"predecessor-version":[{"id":1754,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/1547\/revisions\/1754"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=1547"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=1547"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=1547"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}