How to hot-backup mysql slave database with read operations

Question

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:

1. stop slave
2. flush tables
3. make snapshot
4. 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.

Answer 1

Here is how I would do it (Similar to your suggestion)

1. 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.
2. Terminal 2: Create the snapshot. Depending on what technology you use, this may vary. LVM snapshot is a common one.
3. Terminal 1: UNLOCK TABLES
4. 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

Answer 2

This is an LVM snapshot? That takes seconds, maybe a minute?

I would prefer to fully stop the slave, take the snapshot, the start the slave. There are things about leaving mysqld alive that I don't fully trust.

Consider using the settings that automatically save and restore InnoDB's buffer_pool. (Don't worry, it writes only the block numbers to disk, not the whole blocks.)

Even better is to have two (or more) Slaves and a robust load balancer: Tell the load balancer that this Slave is 'dead'; do the backup; put the Slave back "online".