5

We have an Active/Passive topology where there are two x86 complexes with a shared raw storage, where only one of the nodes in a given moment has an access to the shared storage (AKA the active node). In case of a a failover in the active node, the passive node initiates a take over and becomes the active node with an access to the shared storage. Each node has its own boot device storage with a filesystem. However, the shared storage cannot have a filesystem mounted on it.

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

MySQL with InnoDB is capable of running on a raw device, and there is also a guide on how to run MySQL over a cluster similar to our topology. However, in the second example, they do have a filesystem mounted on the shared storage. The filesystem issue raises a major concern:

ib_logfile* still require a file system. So the raw MySQL feature is not exactly fully raw. Please correct me if I'm mistaken. Is there a workaround to store those files in the raw storage? We can save the redo logs (ib_logfile0, ib_logfile1) in the node's boot device and always delete those files before the server is starting (So we won't have old logfiles in case of multiple failovers). However, this might lead to uncommitted transaction to be partially committed in case of a failure in a middle of a transaction, thus contradicting the whole idea of transactions.

Are there any more files/features that might affect the behaviour of mysql in this topology?

  • So many things can break, I wonder why you are only protecting against these few things. – Rick James Oct 21 '15 at 22:08
3
+50

It's worth noting that InnoDB's write ahead log (WAL), the ib_logfile*, is not the only thing that will need a file system. You have:

  1. System tables in the mysql schema that likely use the MyISAM storage engine (.frm, .MYD, .MYI per table) (most are now using InnoDB in 5.7)
  2. .frm files for each InnoDB table, even when using the shared system tablespace (table metadata that's required)
  3. MySQL log files (error log, general log, binary log)
  4. SSL artifacts
  5. auto.cnf (where the MySQL instance UUID is generated and automatically stored)
  6. db.opt file for each schema (in /<datadir>/<schema>/)
  7. .par file if you create a partitioned table (gone in 5.7)
  8. .trn and .trg files if you create triggers
  9. InnoDB tmp tablespace (5.6+)
  10. Persisted buffer pool page map (ib_buffer_pool, 5.6+)

All of the above are typically within the data directory, so as long as you have datadir=/some/valid/fs/path -- that's also replicated (e.g. DRBD) or shared (e.g. NFS, GFS, OCFS) between the two nodes -- then you'll be fine.

It's worth noting that the .frm, .par, .trn, .trg, and .opt files will go away with the new Data Dictionary.

Stay tuned for some big announcements there in the coming months! :)

It's not clear to me why you're using the RAW device? I'm sure you have your reasons though. :)

Good luck!

| improve this answer | |
  • If I recall, if innodb_file_per_table is disabled, all of the above are saved in ibdata file. ssl,textual log files and binary log files are not not required to be persistent in our topology, because we don't plan to support replication. As for the RAW feature, we do have our reasons :) Thanks!!! – Mattan Oct 14 '15 at 14:27
  • No, that's not correct. You will not have per-table tablespaces (<tablename>.ibd) for InnoDB tables, but you will still have everything else. At the very least, you will have a .frm file per table. You have to keep in mind that the InnoDB system tablespace (ibdata*) is specific to the storage engine. The other .* files are SQL Server layer objects. You also have to keep in mind other storage engines. Again, the system tables in the mysql schema/database use the MyISAM storage engine, so you will have a .frm, .MYI, and .MYD file for each one. – Matt Lord Oct 14 '15 at 15:09
2

The raw storage technique is only good for ibdata1 with innodb_file_per_table disabled.

I have mentioned setting this up a couple of times

Note InnoDB Architecture (Picture from Percona CTO Vadim Tkachenko)

InnoDB Plumbing

With innodb_file_per_table disabled, data and indexes for all InnoDB tables would land inside the raw storage along with the double write buffer, insert buffer, data dictionary, rollback segments, and undo space.

With regard to the redo logs, you should consider having a small DRBD block device just to hold /var/lib/mysql, with ib_logfile0 and ib_logfile1 in that folder. Thus, the failover would go as follows

  • Break DRBD between the two servers
  • Set the new server's DRBD state to Primary/Unknown
  • Mount DRBD on /var/lib/mysql
  • Mount raw device with ibdata1's info
  • service mysql start
  • Sync DRBD devices
  • Setup Pacemaker/ucarp services to prep for next failover

I preferred DRBD/ucarp for failover setups. See my old posts on them

UPDATE 2015-10-14 11:30 EDT

As I already mentioned, the redo logs must be in the DRBD block device mounted on /var/lib/mysql. With regard to other essential files such as

  • binary logs
  • relay logs
  • error log
  • slow log
  • general log

all of these files must be in /var/lib/mysql as well. That way, failovers will carry everything MySQL related (minus the Raw Data which is in its own disk).

WARNING : This set up is not for MyISAM tables. If a hard failover occurs, all open MyISAM tables prior to the crash/failover will be marked as corrupt and will require running REPAIR TABLE on all affect MyISAM tables.

If all your tables are InnoDB, ignore this warning. If you convert your remaining MyISAM tables into InnoDB, then you can ignore this warning. (DO NOT CONVERT ANY MyISAM TABLES IN THE mysql SCHEMA INTO InnoDB).

| improve this answer | |
  • Regarding the 'Sync DRBD devices' stage: Please consider the following case: 1. Interlink communication fails. 2. Active continues to be the master MySQL node. 3. After a while, active fails (In a middle of transaction) 4. Passive decides to take over and becomes the active node. Between 3 and 4 the 'Sync DRBD devices' stage did not operate, thus the newly active node has now an old DRBD data. Is there a solution to this scenario? – Mattan Oct 13 '15 at 8:33
  • DRBD is synchronous, so the redo logs should still be good. – RolandoMySQLDBA Oct 13 '15 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.