I am running an application with mysql as the database and is hosted on private servers. Since, it contains very sensitive data, I am not allowed to use any of the cloud services or third party plugins which have slightest access to data. This is a legal mandate and I cant go round it. So, options like AWS, MySQL Enterprise backup or any such service is out of question. To add to it, I cant have replicas around the database because of some legal issues.
I want to take production backups at hourly frequency. The size of data as of now is 20gb + and is expected to grow at a rate of 7-8 gbs per month. All the tables are using innodb engine.
So, I cant simply run mysqldump for taking a sql dump. I can not have downtime on my application. One approach would be to copy the entire data folder and keep it zipped.
What would be the most suitable approach to take hot database backups without affecting application transactions?
Percona's xtrabackup is widely used among database administrators. Be sure to read the manual carefully.