patternsqlMinor
Two MySQL Server accessing same database over NFS
Viewed 0 times
samedatabasetwomysqlnfsserveroveraccessing
Problem
I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following:
On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.
http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html
- one MySQL instance will be a producer
- one MySQL will be read only consumer
On the MySQL site, it is mentioned this possible with MyISAM and merge storage engine only, but they never recommend it.
http://dev.mysql.com/doc/refman/5.6/en/multiple-data-directories.html
Solution
I just want to check if it is technically possible or not,
recommendation is other story. specially my application assured
write/read will not conflict
As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.
Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.
MyISAM doesn't have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you'll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.
But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That's a more common practice, and it works well.
Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.
recommendation is other story. specially my application assured
write/read will not conflict
As @RolandoMySQLDBA said, this is not possible with InnoDB or MyISAM.
Stated more simply: some of the pages of data you modify are not written to disk immediately. This is done in the background, and not all at once. So if your second read-only instance tries to read data through NFS from the same data directory, it is certain to read changes that have been partially written to disk. Basically, garbage.
MyISAM doesn't have a buffer pool, but it does make use of the filesystem as a write-back cache, so you never know whether all your changes have reached the disk or not. In this case, you'll also get an unpredictable mish-mash of data if you try to read the files through NFS from another host.
But why do you want to do this in the first place? Is it to gain greater capacity from running a large number of queries per second? You can run more than one instance of MySQL with data that is kept more-or-less in sync, by using replication. That's a more common practice, and it works well.
Is your reason to have a failover capability? In that case, you might use DRBD for block-level replication. And there are quite a few other failover solutions besides that.
Context
StackExchange Database Administrators Q#63832, answer score: 3
Revisions (0)
No revisions yet.