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

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

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

• Kalavan: No, it;s a vmware filesystem. my concern is not in snapshot creation time (I treat it as "instant"). Problem is in waiting for a lock. What do you mean by "fully stop a slave"? Do you mean stopping replication aka stop slave or shutting down database?
• Rick James: Shutdown the database (stop mysqld). If you have something that is hanging onto a lock for minutes, then I recommend you find that villain and give it a lesson in civility.
• Rick James: If it is a "report" that is doing a huge group-by on a huge table, let's talk about Summary Tables.
• Kalavan: maybe I really should turn the table and eliminate long queries. I don't know why I didn't think about it earlier. Turning on slow query log :)

Answer 3

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

• Kalavan: Maybe I did not mentioned it clear enough. This is a solution I am currently using. But acquiring a read lock while there is a long sql query can take as much time as query needs to finish. In my case it stopped an application for about half an hour before lock was in place. That's why I am looking for some variant of this method, I simply hope that it is possible to get another solution basing on fact that it is a slave database.