MySQL Bugs: #1797: mysqlhotcopy --> mysqlbackupall

Bug #1797	mysqlhotcopy --> mysqlbackupall
Submitted:	10 Nov 2003 12:16	Modified:	22 Jun 2015 15:27
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	3.23	OS:	Linux (RedHat7.3)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Obsolete_201506

Description:
I was trying to use mysqlhotcopy to backup ALL databases, but it kept choking at the point where it attempts to execute a big fat READ LOCK on every table in every db (errcode 24: out of file resource handles).

How to repeat:
On a system with lots of dbs with lots of tables (i'm looking at about 2300 tables altogether on mine), try hotcopying everything:

mysqlhotcopy -u foo -p bar --regex . /my/backup/dir
DBD::mysql::db do failed: Can't find file: './some_database/some_table.frm' (errno: 24) at /usr/bin/mysqlhotcopy line 434

(the actual file that it chokes on isn't always the same, and it really does exist, permissions are in order, etc). I see where other people tinker with /proc/sys/fs/file-max to deal with this, but I found a different way

Suggested fix:
Even though the huge LOCK statement doesn't work, a simple "FLUSH TABLES WITH READ LOCK" does. So I borrowed from mysqlhotcopy to create the extremely simplified mysqlbackupall. All it essentially does is:

1. FLUSH TABLES WITH READ LOCK
2. cp -rp /mysql/data/dir/* /path/to/backup/dir/*
3. UNLOCK TABLES

Of course, this is nowhere near as flexible as mysqlhotcopy, but it gets the big job done without error. I post this here b/c it might be useful to someone else. Also, I'm not a perl programmer, so I kind of had to fake it.. maybe someone will want to take my idea and improve/enhance it (add scp support back in, etc)

mysqlbackupall, simplified alternative to mysqlhotcopy

Attachment: mysqlbackupall.tar.gz (application/x-gzip-compressed, text), 903 bytes.

As you correctly noticed, it's not as flexible as LOCK TABLE - READ TABLES WITH READ LOCK locks all tables at once.

Soon we will extend READ TABLES WITH READ LOCK to accept a table list as an argument - but unlike LOCK TABLES it won't actually open tables, thus it won't consume file handles.
After we'll have it, we'll change mysqlhotcopy to use this command.

It's still existing as of now?

Mysqlhotcopy was deprecated in MySQL 5.6, and removed from MySQL 5.7.5.  The rationale behind this decision is explained here:  http://www.tocker.ca/2014/04/17/proposal-to-deprecate-mysqlhotcopy.html

I am closing this bug as Won't fix.