Just read about the benefits of mysqlhotcopy over mysqldump. I ran a mysqlhotcopy for one of my databases and I see that I have a number of binary files. If I were to lose my database in some way and I had these binary files as a backup, how do I restore them?

Thank you,

Jack

I had never heard of mysqlhotcopy before now, but was curious. From what I can tell, it simply copies the databases in-place, but first logs in and locks the tables to avoid data corruption without having to shut down the MySQL server (hence the name, hot copy).

Grepping through the docs I found this section (http://www.basiclinuxhost.com/docs/MySQL-3.23.36/manual.html#mysqlhotcopy), and also typing "perldoc mysqlhotcopy" shows quite a bit of information (the program is just a Perl script).

I don't see any specific mention of how to restore, but I believe you would just restore the files directly into the MySQL data directory, with the MySQL server shut down of course.

Hope this helps.

I see what's happening here now. The MySQLHotcopy utility is making an exact backup of all the mysql binary files for a particular database. The in order to restore, I simply cp my backup files to the /mysql/databasename directory. But you say that I first have to shut down the MySQL server before I restore these files. Since I'm new at this, I'm wondering why this is necessary. For example, I've always used mysqldump in the past, which creates a text file of the database and all its tables, etc. A restore is done simply with the following syntax

mysql -u username -ppassword < backup.sql

where backup.sql is the name of the dumped file. And I don't have to shut off the mysql server to do this. Perhaps this is a benefit of a mysqldump over mysqlhotcopy. Any thoughts?

Also, would mysqldump be more reliable (that is to say, no corruption of data) if I were to use the argumen to lock tables during the dump?

Thanks again for your advice,

Jack

There is a huge difference between mysqldump and mysqlhotcopy.

mysqldump does not touch the files; it sends a command to the MySQL server process, and retreives the output. Likewise when restoring. As long as you don't touch the actual data files, you don't need to kill the MySQL server.

This is why you don't need any read/write access to the data files, only the server process needs actual access to the files. Any tool that logs into the mysql server is simply sending commands and fetching the result, it is not touching any files.

The issue comes in when you attempt to copy the actual data files while MySQL is running. There's always a chance that a file can be changed just as you are copying it. There's also cache/buffer issues, etc.

So they whipped up mysqlhotcopy that first locks the table (prevents any action on the file), then copies the file, then unlocks it, in order to allow you to copy without killing the server process.

Restoring, OTOH, is a different story for several reasons. MySQL holds a lot of things in memory, and no changes should happen to any of its files while it is running. You must shut it down, copy in the new files, and then restart MySQL.

Thanks again, Jman,

That really helps me to better understand the differences between the two as well as the importance of starting and stopping MySQL when restoring.

I really appreciate your help and your fast response.

Jack

I presume mysqlhotcopy is faster than mysqldump as well.

Thanks for giving the knowledge of Mysqlhotcopy and Mysqldumpcopy.