mysqlhotcopy Can't find file, only when doing all-databases

[cotton]

Hi all,

I'm new to using MySQL ZRM, but have gotten pretty familiar with the software over the past week. I'm backing up 1 MySQL slave server with 4235 databases weighing in at around 170GB in size. I completed all the setup, etc, by using just 1 database in the mysql-zrm.conf.

I have an issue with mysqlhotcopy file not found errors, but only when I have all-databases=1 (or >40 databases manually defined) in the mysql-zrm.conf on the ZRM box. If I manually set databases=dbname40, all is fine! The files DO exist in /var/lib/mysql/dbname40/tablename.frm on the MySQL server I'm backing up.

Here are the errors I'm getting:

BackupSet1:backup:INFO: PHASE START: Creating raw backup
Tue Mar 24 09:44:02 2009: BackupSet1:backup:INFO: PHASE START: Creating raw backup
Tue Mar 24 11:30:04 2009: BackupSet1:backup:ERROR: Output of command: 'mysqlhotcopy' is {
DBD::mysql::db do failed: Can't find file: './dbname40/tablename.frm' (errno: 24) at /usr/bin/mysqlhotcopy line 466.
 mysqlhotcopy on host yukon failed at /usr/share/mysql-zrm/plugins/ssh-copy.pl line 322.
}
Tue Mar 24 11:30:04 2009: BackupSet1:backup:ERROR: mysqlhotcopy did not succeed. Command used is /usr/share/mysql-zrm/plugins/ssh-copy.pl --mysqlhotcopy --user="mysql" --password="*****" --host="yukon" --quiet  dbname1 dbname2 ... etc etc ... dbname40 "/backups/mysql-zrm/BackupSet1/20090325072505" > /tmp/ZRokqiSB1B 2>&1 Return value is 65280
BackupSet1:backup:INFO: PHASE START: Cleanup
BackupSet1:backup:INFO: backup-status=Backup failed
BackupSet1:backup:INFO: Backup failed
BackupSet1:backup:INFO: PHASE END: Cleanup
BackupSet1:backup:INFO: END OF BACKUP
ERROR: /usr/bin/mysql-zrm did not finish successfully

There is plenty of room on the disks.
UID/GID of mysql user is the same on both boxes, and ssh keys work fine back and forth between them with no password.
If I specify ONLY dbname40, it works fine, but if I manually define dbname1 dbname2 ... etc etc ... dbname40 dbname41, I get the same error as all-databases=1 in the conf file.

Does anyone have a clue what's going on here? I assume mysqlhotcopy is looking for /tmp/R4NDOMN4ME/dbname40/tablename.frm, since the file DOES exist (with correct permissions) in the /var/lib/mysql/dbname40/ folder, is this correct? Why would it fail - is there a size or dbcount limit?

Regards and TIA,
Cotton

[paddy]

This is a mysqlhotcopy bug.

See [url]http://bugs.mysql.com/bug.php?id=1797[/url]

It happens only when there are lots of databases or tables. I'm not sure if this has been fixed in recent MySQL versions.

Paddy

[cotton]

Thanks for the reply.

I raised the table_cache/max_connections, as well as open_files_limit, but it seems MySQL has a [URL="http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_open-files-limit"]hard ceiling of 65535[/URL] on that variable. I have ~1 million .MY*/.frm files throughout the databases.

Is there any way that ZRM/mysqlhotcopy will work in this case?

Thanks again,
Cotton

[cotton]

Also, my OS has over 1 million for open_files and file-max settings.

[zmanda_jacob]

To properly back up this system, you will need to do a ZRM snapshot backup. This requires that your datafiles, indexes, and log files for your MySQL server reside on a logical volume(LVM for Linux or ZFS for Solaris). Provided this is the case, you can configure the ZRM to do a snapshot backup which will bypass mysqlhotcopy/MySQL's limitations on open files.