AMD wrote:
I would like to have MySQL use a mapped network drive. I'd like to do
this in case there is a failure of the mysql machine, I can just replace
it with a new machine pointing to the same network drive. Is this
possible at all?
Perhaps it's possible in theory, but it is fraught with hazards:
- The network link between the MySQL server and the file server can be
interrupted more easily than a hard disk interface. Your application
needs to detect when the database disappears intermittently and fail or
retry gracefully.
- There is a performance penalty of doing I/O over a network instead of
to a local disk? Network congestion, latency differences, packet loss
rates, delays from moving packets between different subnets, etc. can
make even gigabit networks have a much lower effective data rate than an
ordinary ATA/100 bus.
- The most serious risk is that another MySQL server will map the same
network drive and write to the same MySQL databases. This is virtually
certain to corrupt the databases, because the two MySQL servers have no
way to know when another MySQL service is using that data, no way to
coordinate their changes, and they cannot ensure consistency in the
database. It's likely that the two services will overwrite each other's
work in ways that corrupt the database.
Instead of using mapped drives, I recommend keeping the database storage
local to the host running the MySQL service, taking frequent backups of
the database, and transfer those backups to the file server. That way
if the host running MySQL has a failure, your backup files are safe.
You can use the most recent backup to restore the databases on another
host running MySQL, which also uses local storage for its live databases.
Also, MySQL has a replication solution for automatically keeping a
second database continuously synchronized with the first database. Read
the chapter on Replication in MySQL at
http://dev.mysql.com/doc/mysql/en/Replication.html.
Regards,
Bill K.
