First of all, what are the entry types that reside in ibdata1 ? Four(4) entry types:
- Table MetaData
- Table Data Pages
- Index Data Pages
- MVCC Data
Whenever an InnoDB table experiences DDL, DML, or being used in a Transaction, all four of these types of entries are either read or written. Meanwhile, if innodb_file_per_table is disabled, all these entry types live in ibdata1. If it is enabled, only the Table MetaData and the MVCC Data would reside in ibdata1 while the Table Data Pages and Index Data Pages would reside in the database subfolder as a .ibd file.
That being considered, what would happen if ibdata1 were placed in another volume and symlinked ?
For starters, how does MySQL represent a table regardless of the storage engine ? As a .frm file. Where do .frm files live ? In the datadir. What's wrong with that ?
Here is an example:
Using the default datadir of /var/lib/mysql, let's use an InnoDB table called mydb.mytable.
With innodb_file_per_table disabled, everything would sit in ibdata1 (which you are proposing to symlink and send off to another data volume). For the table mydb.mytable, this is what you would have:
- /var/lib/mysql/mydb/mytable.frm
- Everything else about the table lives in ibdata1
Picture this now: You access the table, MySQL would first hit /var/lib/mysql/mydb/mytable.frm and then hit the data and index pages in ibdata1 for mydb.mytable. This would constantly be happening with every access of mydb.mytable. This cascading back-and-forth would somehow make things a little slower and you may not get the performance you were expecting by moving ibdata1 to some other data volume. In fact, the cascading effect would now be a factor of the number of InnoDB tables multiplied by two(2).
Imagine having innodb_file_per_table enabled. Now you would have a slightly different setup:
- /var/lib/mysql/mydb/mytable.frm
- /var/lib/mysql/mydb/mytable.ibd
- MVCC data and Table MetaData would reside in ibdata1
This cascading would be a little worse because the cascading for table access would now occur among three files instead of two.
Here is one more scenario some have thought of: Instead of moving the ibdata1 to a different volume, how about enabling innodb_file_per_table and moving the .ibd files to one or more different data volumes ? The cascading effect would now be a factor of the number of InnoDB tables multiplied by three(3). Percona has expressed very good reasons for not doing this that you will find helpful.
You could experiment with innodb_flush_log_at_trx_commit
According to the MySQL Documentation on innodb_flush_log_at_trx_commit
If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
written out to the log file once per second and the flush to disk
operation is performed on the log file, but nothing is done at a
transaction commit. When the value is 1 (the default), the log buffer
is written out to the log file at each transaction commit and the
flush to disk operation is performed on the log file. When the value
is 2, the log buffer is written out to the file at each commit, but
the flush to disk operation is not performed on it. However, the
flushing on the log file takes place once per second also when the
value is 2. Note that the once-per-second flushing is not 100%
guaranteed to happen every second, due to process scheduling issues.
The default value of 1 is required for full ACID compliance. You can
achieve better performance by setting the value different from 1, but
then you can lose up to one second worth of transactions in a crash.
With a value of 0, any mysqld process crash can erase the last second
of transactions. With a value of 2, only an operating system crash or
a power outage can erase the last second of transactions. InnoDB's
crash recovery works regardless of the value.
For the greatest possible durability and consistency in a replication
setup using InnoDB with transactions, use
innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
server my.cnf file.
Caution
Many operating systems and some disk hardware fool the flush-to-disk
operation. They may tell mysqld that the flush has taken place, even
though it has not. Then the durability of transactions is not
guaranteed even with the setting 1, and in the worst case a power
outage can even corrupt the InnoDB database. Using a battery-backed
disk cache in the SCSI disk controller or in the disk itself speeds up
file flushes, and makes the operation safer. You can also try using
the Unix command hdparm to disable the caching of disk writes in
hardware caches, or use some other command specific to the hardware
vendor.
Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.
As far as innodb_flush_method, the default is best. I wrote an earlier post to describe the effects of tweeking it.
As for running multiple instances of MySQL, it's OK as along as you have enough memory for the OS and enough memory of DB Connections for the multiple instances.
Best Answer
NO! MySQL is not designed to allow two servers to access the same databases.
Or, maybe...
If your goal is to provide redundancy for the case of server failure, then maybe you could set up a non-running MySQL on a secondary server, ready to mount the disk subsystem in the case of failure.
Consider, instead, Clustering (Galera, PXC, InnoDB Cluster).