0

According to documentation, if you are using MYISM, it should be possible to take physical backup of MySQL. i.e. copying data files. But in order to take "Physical Backup" for INNODB Engine, you need MysqlEnterprise. I am not using MySQL Enterprise at the moment and have Database of size 100GB. I do not want to use mysqldump but want to do a physical backup. Is there a way to achieve this ?

Muhammad Faizan Ul Haq
  • 111
  • 1
  • 9

2 Answers2

2

Percona XtraBackup is free and open-source, and works with free, open-source distributions of MySQL and its derivatives that use InnoDB.

https://www.percona.com/software/mysql-database/percona-xtrabackup

When used in combination with Percona Server for MySQL, Percona XtraBackup provides the only true non-blocking online, real-time backup of transactional systems currently available.

In fact Percona XtraBackup works fine with MySQL Community Edition too.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks for answering it instantly. I will read about it from the given link. But **does that mean, we will have to install something on mySQL server?** Oh, I am afraid that I might find it difficult to do with our production setup. I, definitely, can try this on our development node and give a proof of concept to my team. – Muhammad Faizan Ul Haq Feb 28 '17 at 08:04
  • Correct, Percona XtraBackup must run on the MySQL server. It needs to access data files directly, not over a network. This is true for MySQL Enterprise Backup too! See http://stackoverflow.com/questions/34873546/taking-backup-remotely-using-innobackupex And yes, it's definitely appropriate to use a test environment to practice using the tool and demonstrate it before you use it in production. – Bill Karwin Feb 28 '17 at 15:37
  • Yea, I skimmed through the docs and found it pretty straightforward. I think, that will serve the purpose for me. – Muhammad Faizan Ul Haq Mar 01 '17 at 06:30
  • I'm glad to help. As a reminder, it is customary on Stack Overflow to upvote and/or accept an answer that helped you! :) – Bill Karwin Mar 01 '17 at 14:56
  • I need to copy the mysql database from production server to local development machine. exact copy of latest data does not matter. So if i just copy the complete production datadir and place it on my local datadir - will i need to stop the mysql server on production? Please note that i do not need to copy the exact , latest data from production. even if data of last 1 to 2 hours is missed if i just do a copy , paste / file transfer from prod to local. so can i just copy the datadir from prod and download it on my local without needing a restart of the production mysql server ? – mrtechmaker Jul 16 '18 at 07:41
  • @KaranAhuja, No — you cannot get a consistent copy of the data without stopping mysqld. The copy will be corrupt and unable to be used. You should use `mysqldump` or Percona XtraBackup. Both of these can get a copy of data without stopping mysqld. – Bill Karwin Jul 16 '18 at 12:02
1

If you are willing to stop mysqld for long enough to copy the entire /.../mysql/ tree, then you can take a physical backup.

If you have LVM already set up, then the down time is a minute or so, regardless of the dataset size: Stop mysqld; take LVM snapshot; restart mysqld; then copy the shapshot to elsewhere; (and turn off LVM).

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • On my development node, that could be done and I just did it. I was just afraid that open files might loose data but now I know the tip is to stop the server before doing the physical backup. Thanks for help. – Muhammad Faizan Ul Haq Mar 02 '17 at 12:46
  • Correct -- The buffer_pool, especially, serves as a form of "write cache". – Rick James Mar 02 '17 at 16:18