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:

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.

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

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”.

It seems to me that you shouldn’t have abandoned xtrabackup

It can create compressed backups at once

xtrabackup --backup --compress --target-dir=/data/compressed/

and also supports streaming compression.

innobackupex --stream=tar ./ | gzip - > backup.tar.gz

Also, the mysqlbackup utility that is included in Mysql Enterprise Backup supports compressed backups.

mysqlbackup --defaults-file=/etc/my.cnf --compress --backup-image=backup.img backup-to-image