[mythtv-users] Fast DB backups (was: Re: recording not starting on time - one cause)

Keith Pyle kpyle at austin.rr.com
Tue Dec 18 04:04:32 UTC 2007


On Dec 16, 2007, at 1:50 PM, David Brodbeck wrote:

> If the time mysqldump takes is a problem, you might want to use
> mysqlhotcopy instead.  It makes a backup by copying the table files.
> The database still has to be locked, but for a much shorter time.  The
> disadvantage is it's not portable like a mysqldump backup.  If you
> have to reload the backup, you need to do it on the same version of
> MySQL.

David is correct about using mysqlhotcopy for faster backups.  If you have sufficient memory in your system running mysql, you can increase the backup speed a bit more by taking advantage of Linux read caching.  First, do a 'du -sk' on your database directory and determine the size of your database.  Then, run the 'free' command to get your memory statistics.  If the free column for '-/+ buffers/cache' is greater than your database size, then the following trick will likely work.

For this example, assume that your database is at /var/lib/mysql and you intend to make a backup in the directory /tmp/backup (which exists and is empty).  Run these commands (adjust paths as appropriate for your configuration):

cat /var/lib/mysql/mythconverg/* >/dev/null
/usr/bin/mysqlhotcopy mysql mythconverg /tmp/backup

The first command will read all of the files in your Myth database, but send the output into the bit bucket.  The effect is that all of the tables are now in the Linux read cache (i.e., in memory).  As long as the underlying files/tables do not change and there aren't a lot of other disk reads to reuse the cache, Linux will perform reads from the cached copy in memory rather than reading the disk again.  The second command copies both your mysql and Myth tables to the backup directory.  Even if you are recording, most of the tables will be sufficiently idle that mysqlhotcopy will get most of them from cache rather than disk.

My Myth database is about 125 MB.  When run as above, the mysqlhotcopy command locks the tables and completes its run in 0.65 seconds.  I do run this under cron at a time when I'm not likely recording, but I've never seen a recording problem that could related to the database being locked for backup.

Keith



More information about the mythtv-users mailing list