PlanetMysql.ru — информация о СУБД MySQL

Yoshinori Matsunobu sparked some interest recently when he posted about his MySQL MHA HA solution, and there has been some discussion of it internally at Yahoo compared with the standard we currently have.  

Full disclosure:  I haven’t read every bit of the documentation or tried it out yet, so I apologize in advance to Yoshinori if I mistakenly represent his hard work. 

I see a lot of great ideas in Yoshinori’s release, it seems to focus on two main problems:

1. A process to monitor an active master and perform a failover when it fails
2. The bit that finds the most “caught-up” slave and distributes relay log contents from that slave to all the other slaves in the cluster

There tend to be many more pieces to performing a failover, but I’d agree that getting the slaves consistent and as up to date as possible is a great problem to solve.  I really think this is a big hole in what our current internal standard lacks, and I want to look into using Yoshinori’s code, or at least checking out his algorithms.

However, for our use there are some extra features that are crucial:

• Multi-tiered/colo architectures, specifically dual-masters
• Full HA on any HA solution.  This gets kind of meta, but the management server Yoshinori suggests needs to have redundancy.  The doc mentions running two management servers, but doesn’t elaborate on how that would actually work.  For example, do the management servers communicate?  What happens if they fall out of communication with each other?  That leads me to:
• Being immune to split brain issues.  The doc mentions the idea of scripting a remote power off of a potentially network-isolated master, which is certainly one solution.  I don’t disagree with that, but I’m not necessarily convinced the overall solution could handle odd network situations in general.  Add multi-colo into that mix, and it gets even more complicated to think about.  

My take (so far) is, like many other open sourced “HA” solutions, it tends to focus on local availability and overlooks geographical redundancy (BCP, as we call it).

By no means am I trying to be critical about MySQL MHA, and I’m looking forward to see where MHA goes from here.  

PlanetMySQL Voting: Vote UP / Vote DOWN

There are times when what you have is a partially running database and a bunch of backup innodb tablespace files (the .ibd files). If you're using innodb_file_per_table, then you have a separate .ibd file for each InnoDB table.

Now, you have your running database with a bunch of tables, and you want to replace some of them with the backup .ibd files. According to the MySQL docs, you'd do this:

1. ALTER TABLE foo DISCARD TABLESPACE; (this deletes the current .ibd file)
2. copy the old .ibd file into your database directory
3. ALTER TABLE foo IMPORT TABLESPACE;
   

Assuming your .ibd file was from the same database and you did not drop the table and recreate it sometime between when you made the backup .ibd and now, this should work.Except... if you use partitions. If your table foo uses partitions, ie, its create statement was something like this:

CREATE TABLE foo (
   ...
) PARTITION BY ... (
   PARTITION p0 ...,
);

In this case, you cannot discard the tablespace, and the first alter command throws an error:

mysql> ALTER TABLE foo DISCARD TABLESPACE;

ERROR 1031 (HY000): Table storage engine for 'foo' doesn't have this option

I have not investigated if there are workarounds for this, but I do have a little more information on what's happening.Remember that each .ibd file is a tablespace. For a partitioned table, there are multiple .ibd files, one for each partition. The table's files look like this:

foo.frm
foo.par
foo#P#p0.ibd
foo#P#p1.ibd
...

Where p0, p1, etc. are the partition names that you specified in the create statement. Each partition is a different tablespace and has its own tablespace id. When you create an InnoDB table without partitioning, the internal tablespace id counter is incremented by 1. When you create an InnoDB table with paritions, the internal tablespace id counter is incremented by the number of partitions. The actual tablespace id is stored in each partition's .ibd file somewhere within the first 100 bytes. I have not attempted to find out where exactly though.
PlanetMySQL Voting: Vote UP / Vote DOWN