Mysql InnoDb over raw device in active / passive topology

Question

We have an Active / passive topology, where there are two x86 complexes with shared storage, in which only one of the nodes currently has access to the shared storage (AKA - active node). In the case of a transition to a backup resource in the active node, the passive node initiates the capture and becomes the active node with access to the shared storage. Each node has its own repository of boot devices with a file system, however, a file system cannot be installed on shared storage.

We are interested in installing the Mysql server on both nodes, where its data is in shared storage, and only the active node is running on the server.

Mysql with InnoDb is capable of running on a raw device , and there is also a guide on how to run Mysql on a cluster similar to our topology . However, in the second example, they have a file system mounted on shared storage. The file system issue is a major concern:

ib_logfile * still requires a file system. So the raw mysql function is not completely loaded. Please correct me if I am wrong. Is there any way around these files in raw storage? However, we can save ib_logfiles in the node boot device and always delete these files before the server starts, however, this can lead to partial incomplete transactions in the event of a failure in the middle of the transaction, which contradicts the whole idea of ​​transactions.

Are there any other files / functions that can affect mysql behavior in this topology?

Answer 1

Each mysql installation consists of 2 directories. 1. Application catalog 2. Data catalog. The data directory contains all the db data. It contains data files and log files. The data directory can be located in the shared storage and application directory on local servers, and when you want to switch from active to passive, you quit (if it did not overwhelm), and start the passive server with permission on the shared storage, since the log files are in the shared storage, a new active server restores a lost transaction. Keep in mind that in this topology the passive server is turned off, and only when it is turned on it will be started.

Answer 2

You probably won't like this answer, but here goes ...

Do not try to do what you described.

You protect only one thing: server failure (except disk). Meanwhile, disk failure, network failure, earthquake, flood, etc. They can destroy the system. Why protect only one failure scenario?

Back to the preliminary plan ... If the active server dies, you should prevent it from writing more MySQL files. Only then can passive mysqld be enabled. It (assuming you are using InnoDB) restores what it can from disk. Keep in mind that things in active mode probably expected to be reddened from RAM.

Or is your goal that a "raw" device is somehow "better"?

"Raw" is no longer useful. Decades ago, it was good, which was good. Today, drives and controllers have virtually ruled out the utility of "raw".

Just "mount" the file system and get the mysql files in places that every server knows about.

InnoDB data and indexes reside in 16K blocks, several organized into 1MB extents. But as tables change, the data is scattered, thereby eliminating any advantage that the "raw" is used to provide.

OK, however, all log files (iblog, binlog, slowlog, relaylog, generallog, etc.) benefit somewhat from "sequential" disk access. But if the OS allocates such files somewhat sequentially , you get the performance of "raw" even in the "file system".

InnoDB does not know how to access a raw device.

InnoDB makes heavy use of RAM to avoid I / O.

If you want HA (high availability), I recommend Galera, either in MariaDB or in PXC.