How to hot-backup mysql slave database with read operations I have a slave database of about 120GB in size on which application is performing select statements.

I also have a backup system (Acronis backup) available to me from a hosting company. Unfortunately backup agent is not mysql-aware. It can perform file backups and whole machine backup - it makes a snapshot of machine. It also enables to run a script before/after a backup and before/after snapshot creation. 

I went with simple script making flush tables with read lock before making a snapshot and removing a lock after. Unfortunately, because of long-running select statements, it happens that acquiring a lock takes a lot of time. 

Script was waiting for lock for some time (300 secs) and then killing the session. I thought that I will be able to cancel acquiring a lock and allow application to run. Unfortunately it appears it is not possible (or I am unable to do it correctly).

Is there any command sequence that would allow me to create a valid snapshot and not interrupt database functions for too long? For example, would:


stop slave
flush tables
make snapshot
start slave


or similar do the trick (some data de-synchronization between master ans slave is not a big issue for my app)?

I was looking into some alternatives like xtrabackup or mysqldump to make a backup and simply copy files later but:

Xtrabackup needs at least twice as much space as database and, because of that, I cannot use it.

Mysqldump restore time is huge comparing to copying files.

Taking above under consideration I would love to stick to 'cold' backup with snapshot or a solution like xtrabackup that could provide compressed data without intermediate steps.
Here is how I would do it (Similar to your suggestion)


Terminal 1: FLUSH TABLES WITH READ LOCK It is important to keep this session/terminal open till the end of the process. Wait till you get the message the tables are now locked.
Terminal 2: Create the snapshot. Depending on what technology you use, this may vary. LVM snapshot is a common one. 
Terminal 1: UNLOCK TABLES
Copy the files from the snapshot location


It is essential not to close terminal 1 before you are done with step #2.

Notice please that there is no need to stop the slave. 

This link would be very helpful. It explains with more details, using LVM.

HTH