Over the last year, the frustration of many of us at Percona regarding issues with MMM has grown to a level where we started looking at other ways of achieving higher availability using MySQL replication. One of the weakness of MMM is its communication layer, so instead of reinventing a flat tire, we decided, Baron Schwartz and I, to develop a solution using Pacemaker, a well known and established cluster manager with a bullet proof communication layer. One of the great thing about Pacemaker is its flexibility but flexibility may results in complexity. With the help of people from the Pacemaker community, namely Florian Haas and Raoul Bhatia, I have been able to modify the existing MySQL Pacemaker resource agent in a way that it survived our replication tests and offered a behavior pretty similar to MMM regarding Virtual IP addresses, VIPs, management. We decided to call this solution PRM for Percona Replication Manager. All the parts are opensource and available under the GPL license.
Keep in mind this solution is hot from the press, consider it alpha. Like I said above, it survived testing in a very controlled environment but it is young and many issues/bugs are likely to be found. Also, it is different from Yoshinori Matsunobu’s MHA solution and in fact it is quite a complement to it. One of my near term goal is to integrate with MHA for master promotion.
The solution is basically made of 3 pieces:
- The Pacemaker cluster manager
- A Pacemaker configuration
- A MySQL resource agent
Here I will not cover the Pacemaker installation since this is fairly straightforward and covered elsewhere. I’ll discuss the MySQL resource agent and the supporting configuration while assuming basic knowledge of Pacemaker.
But, before we start, what does this solution offers.
- Reader and writer VIPs behaviors similar to MMM
- If the master fails, a new master is promoted from the slaves, no master to master setup needed. Selection of master is based on scores published by the slaves, the more up to date slaves have higher scores for promotion
- Some nodes can be dedicated to be only slaves or less likely to become master
- A node can be the preferred master
- If replication on a slave breaks or lags beyond a defined threshold, the reader VIP(s) is removed. MySQL is not restarted.
- If no slaves are ok, all VIPs, readers and writer, will be located on the master
- During a master switch, connections are killed on the demoted master to avoid replication conflicts
- All slaves are in read_only mode
- Simple administrative commands can remove master role from a node
- Pacemaker stonith devices are supported
- No logical limits in term of number of nodes
- Easy to add nodes
In order to setup the solution you’ll need my version of the MySQL resource agent, it is not yet pushed to the main Pacemaker resource agents branch. More testing and cleaning will be needed before that happen. You can get the resource agent from here:
https://github.com/y-trudeau/resource-agents/raw/master/heartbeat/mysql
You can also the whole branch from here:
https://github.com/y-trudeau/resource-agents/zipball/master
On my Ubuntu Lucid VM, this file goes in /usr/lib/ocf/resource.d/heartbeat/ directory.
To use this agent, you’ll need a Pacemaker configuration. As a starting point, I’ll discuss the configuration I use during my tests.
node testvirtbox1 \
attributes IP="10.2.2.160"
node testvirtbox2 \
attributes IP="10.2.2.161"
node testvirtbox3 \
attributes IP="10.2.2.162"
primitive p_mysql ocf:heartbeat:mysql \
params config="/etc/mysql/my.cnf" pid="/var/run/mysqld/mysqld.pid" \
socket="/var/run/mysqld/mysqld.sock" replication_user="root" \
replication_passwd="rootpass" max_slave_lag="15" evict_outdated_slaves="false" \
binary="/usr/bin/mysqld_safe" test_user="root" \
test_passwd="rootpass" \
op monitor interval="5s" role="Master" OCF_CHECK_LEVEL="1" \
op monitor interval="2s" role="Slave" OCF_CHECK_LEVEL="1"
primitive reader_vip_1 ocf:heartbeat:IPaddr2 \
params ip="10.2.2.171" nic="eth0"
primitive reader_vip_2 ocf:heartbeat:IPaddr2 \
params ip="10.2.2.172" nic="eth0"
primitive reader_vip_3 ocf:heartbeat:IPaddr2 \
params ip="10.2.2.173" nic="eth0"
primitive writer_vip ocf:heartbeat:IPaddr2 \
params ip="10.2.2.170" nic="eth0" \
meta target-role="Started"
ms ms_MySQL p_mysql \
meta master-max="1" master-node-max="1" clone-max="3" clone-node-max="1" notify="true" globally-unique="false" target-role="Master" is-managed="true"
location No-reader-vip-1-loc reader_vip_1 \
rule $id="No-reader-vip-1-rule" -inf: readerOK eq 0
location No-reader-vip-2-loc reader_vip_2 \
rule $id="No-reader-vip-2-rule" -inf: readerOK eq 0
location No-reader-vip-3-loc reader_vip_3 \
rule $id="No-reader-vip-3-rule" -inf: readerOK eq 0
location No-writer-vip-loc writer_vip \
rule $id="No-writer-vip-rule" -inf: writerOK eq 0
colocation reader_vip_1_dislike_reader_vip_2 -200: reader_vip_1 reader_vip_2
colocation reader_vip_1_dislike_reader_vip_3 -200: reader_vip_1 reader_vip_3
colocation reader_vip_2_dislike_reader_vip_3 -200: reader_vip_2 reader_vip_3
property $id="cib-bootstrap-options" \
dc-version="1.0.11-a15ead49e20f047e129882619ed075a65c1ebdfe" \
cluster-infrastructure="openais" \
expected-quorum-votes="3" \
stonith-enabled="false" \
no-quorum-policy="ignore" \
last-lrm-refresh="1322236006"
property $id="mysql_replication" \
replication_info="10.2.2.162|mysql-bin.000090|106"
rsc_defaults $id="rsc-options" \
resource-stickiness="100"
Let’s review the configuration. First it begins by 3 node entries defining the 3 nodes I have in my cluster. One attribute is required to each node, the IP address that will be used for replication. This is a real IP address not a reader or writer VIP. This attribute allows the use of a private network for replication if needed.
Next is the mysql primitive resource declaration. This primitive defines the mysql resource on each node and has many parameters, here’s the ones I had to define:
- config: The path of the my.cnf file. Remember that Pacemaker will start MySQL, not the regular init.d script
- pid: The pid file. This is use by Pacemaker to know if MySQL is already running. It should match the my.cnf pid_file setting.
- socket: The MySQL unix socket file
- replication_user: The user to use when setting up replication. It is also currently used for the ‘CHANGE MASTER TO’ command, something that should/will change in the future
- replication_passwd: The password for the above user
- max_slave_lag: The maximum allowed slave lag in seconds, if a slave lags by more than that value, it will lose its reader VIP(s)
- evict_outdated_slaves: A mandatory to set this to false otherwise Pacemaker will stop MySQL on a slave that lags behind. This will absolutely not help its recovery.
- test_user and test_passwd: The credentials to test MySQL. Default is to run select count(*) on mysql.user table, so the user given should at least have select on that table.
- op monitor: An entry is needed for each role, Master and Slave. Intervals must not be the same.
Following the mysql primitive declaration, the primitives for 3 reader vips and one writer vip are defined. Those are straightforward so I’ll skip detailed description. The next interesting element is the master-slave “ms” declaration. This is how Pacemaker defines an asymmetrical resource having a master and slaves. The only thing that may change here is clone-max=”3″ which should match the number of database nodes you have.
The handling of the VIPs is the truly new thing in the resource agent. I am grateful to Florian Haas who told me to use node attributes to avoid Pacemaker from over reacting. The availability of a reader or writer VIPs on a node are controlled by the attributes readerOK and writerOK and the location rules. An infinite negative weight is given when a VIP should not be on a host. I also added a few colocation rules to help spread the reader VIPs on all the nodes.
As a final thought on the Pacemaker configuration, remember that in order for a pacemaker cluster to run correctly on a 2 nodes cluster, you should set the quorum policy to ignore. Also, this example configuration has no stonith devices defined so stonith is disable. At the end of the configuration, you’ll notice the replication_info cluster attribute. You don’t have to define this, the mysql RA will add it automatically when the first a node will promoted to master.
There are not many requirements regarding the MySQL configuration, Pacemaker will automatically add “skip-start-slave” for a saner behavior. One of the important setting is “log_slave_updates = OFF” (default value). In some cases, if slaves are logging replication updates, it may cause failover issues. Also, the solution relies on the read_only setting on the slave so, make sure the application database use doesn’t have the SUPER privilege which overrides read_only.
Like I mentioned above, this project is young. In the future, I’d like to integrate MHA to benefit for its capacity of bringing all the nodes to a consistent level. Also, the security around the solution should be improved, a fairly easy task I believe. Of course, I’ll work with the maintainers of the Pacemaker resources agents to include it in the main branch once it matured a bit.
Finally, if you are interested by this solution but have problems setting it up, just contact us at Percona, we’ll be pleased to help.
About Yves Trudeau
Hi again,
I still see
Master/Slave Set: ms_MySQL
p_mysql:0 (ocf::heartbeat:mysql): Slave int-ipfuie-mgmt01 (unmanaged) FAILED
p_mysql:1 (ocf::heartbeat:mysql): Slave int-ipfuie-mgmt02 (unmanaged) FAILED
and
p_mysql:0_monitor_0 (node=int-ipfuie-mgmt01, call=35, rc=1, status=complete): unknown error
p_mysql:0_stop_0 (node=int-ipfuie-mgmt01, call=37, rc=1, status=complete): unknown error
p_mysql:0_monitor_0 (node=int-ipfuie-mgmt02, call=24, rc=1, status=complete): unknown error
p_mysql:0_stop_0 (node=int-ipfuie-mgmt02, call=26, rc=1, status=complete): unknown error
There are no obvious errors in /var/log/messages. Any ideas where to look?
By the way: If this is not the right place to ask such questions, please redirect me to a more appropriate site/forum/mailing list.
Thanks,
Andreas
…and just an other tought:
Because you did not mention the prerequisites for your setup, I did it according to
http://dev.mysql.com/doc/refman/5.1/en/replication-howto.html and http://dev.mysql.com/doc/refman/5.1/de/replication-howto.html
Anything wrong with that? Do you perhaps rely on a “clean” setup without the nodes being preconfigured for replication?
Cheers,
Andreas
PS: You where right with your last comment; I forgot to copy the agent to one of my nodes.
Andreas, the Pacemaker mailing list is the best option to discuss configuration issues. http://oss.clusterlabs.org/mailman/listinfo/pacemaker
One possible alternation to your resource script (for pacemaker pacemaker-1.1.2.1 under OpenSuSE 11.3):
: ${OCF_FUNCTIONS_DIR=${OCF_ROOT}/resource.d//heartbeat/}
. ${OCF_FUNCTIONS_DIR}/.ocf-shellfuncs
The paths you provided did not work. I don’t know, if this is any relevant to other distributions.
Cheers,
Andreas
There was a changed made, when RHCS Resource-Agents were merged into Pacemaker. The path in versions before that merge of resource-agents was
: ${OCF_FUNCTIONS_DIR=${OCF_ROOT}/resource.d//heartbeat/}
. ${OCF_FUNCTIONS_DIR}/.ocf-shellfuncs
, for later versions it is
: ${OCF_FUNCTIONS_DIR=${OCF_ROOT}/lib/heartbeat}
. ${OCF_FUNCTIONS_DIR}/ocf-shellfuncs
You just have to adapt it by hand…
I found a possible bug in the script. When I call it with ocf-tester, I get:
mysql[31420]: ERROR: /usr/lib/ocf/resource.d/heartbeat/.ocf-shellfuncs: line 332: -q: command not found
Indeed, in line 889 of your script I read:
ocf_run -q $MYSQL $mysql_options \
whereas ocf_run is called without -q everywhere else in your script.
Secondly, the ocf-tester reports:
ERROR 1045 (28000): Access denied for user ‘root’@'localhost’ (using password: NO)
This happens, although I provided a password for “test_user=root”. Could it be, that the password is not read from the OCF_RESKEY_test_passwd-Parameter but instead still uses the default (which is empty)?
Cheers,
Andreas
Andreas, when you test this agent, please rebuild the resource-agents package from upstream git, or at least get a reasonably recent one that your distro may ship. Don’t expect to be able to drop this agent into an age-old install.
Hi Florian,
thanks for your suggestion. I included the HA-repo, as you advised. Still, the packages don’t look to outdated. Especially the “resource-agents package looks like it’s just a minor-minor release.
Are there any other packages, which you would consider to be too old or problematic concerning the new mysql resource script?
Package / Installed Version / Available in HA-Repo:
——————————————————————-
> resource-agents; 1.0.3-1.4; 1.0.3-2.11
> pacemaker; 1.1.2.1-2.1.1; 1.1.5-1.1
> libpacemaker3; 1.1.2.1-2.1.1; 1.1.5-1.1
> cluster-glue; 1.0.5-1.4; 1.0.6-1.14
> corosync; 1.2.1-1.2; 1.2.7-1.10
@Yves: If you find the time to write a full “howto” about your resource agent, would you mind to include the prerequisites, including the minimum version of the packages or something like a “verfied and tested on…”? That would be really nice!
Cheers,
Andreas
Andreas, I have no clue where you’re getting your reference version info from, but the current Corosync release is 1.4.2, Pacemaker is at 1.1.6, and resource-agents is at 3.9.2.
Florian, I just followed the link to the repo on http://www.clusterlabs.org/rpm-next/ for OpenSuSE 11.3. I thought this repo would carry the latest packages (even for an one year old distribution like OS 11.3). Are there better (maintained) repos? If not I’ll consider a distribution upgrade or even a change of the distribution. Is there any distribution known for it’s “bleeding edge” maintenance of the ha-packages?
Cheers,
Andreas
I thought OpenSUSE did have a pretty solid HA stack (it certainly does in 12.1). The distro that currently tracks upstream most closely is, believe it or not, Debian (via squeeze-backports).
I posted before that i got this error, thanks for pointing out the backport of squeeze, i updated from backport and the RA worked.
But i ran into a problem, which was: setting the slaves CHANGE MASTER TO command, after some debugging I found that there was data missing in:
property $id=”mysql_replication” replication_info=”192.168.5.96||”
the function “update_data_master_status()” fetches that information and saves it to a temp file, but that file was empty, all the time because it wasn’t allowed to “SHOW MASTER STATUS” .. why? $mysql_options was empty .
i copied the code from all other functions to fill mysql_options and after that it works perfect!
The final version of the function is:
# Stores data for MASTER STATUS from MySQL
update_data_master_status() {
local mysql_options
mysql_options=”$MYSQL_OPTIONS_LOCAL”
if [ -n $OCF_RESKEY_replication_user ]; then
mysql_options=”$mysql_options $MYSQL_OPTIONS_REPL”
fi
tmpfile=`mktemp ${HA_RSCTMP}/master_status.${OCF_RESOURCE_INSTANCE}.XXXXXX`
$MYSQL $mysql_options -e “SHOW MASTER STATUS\G” > $tmpfile
}
Hi – We’re working on the same path. Here is a link to a Google Doc I’m going to publish on MySQLFanBoy.com when I’m done. My knowlage of PaceMaker is week. My next step is to add MHA failover to the process.
https://docs.google.com/document/d/1UVaQcxjsZQj19BZy8ngU9hRJRHCC6vt66v4-FFjnHlU/edit
Have you started work on the MHA failover?
Very nice, Im running it in a lab setup and for the most part it is working great. the only problem I am seeing (other then the above mentioned update_data_master_status function) is the reader vip’s do not migrate over to the slaves in my setup. They remain on the writer node… even though the slaves have started and are working. Im sure there are some location/colocation restraints that are still needed.
I look forward to this making it upstream to the pacemaker repos.
I’ve changed a few more things to get this working. It’s a combination of the configurations supplied by Florian and Yves. I fiddled with the settings here and there and did the above patch in the RA itself.
The whole setup now works like a charm, resources stick correctly, reader/writer (V)IPs migrate correctly, etc.
The configuration can be found here: http://www.e-rave.nl/wp-content/uploads/2012/02/mysql-ha.txt
I’ve 3 nodes. The reader IP is a VIP placed on lo:# and my keepalived load-balancer will distribute the reads on the 3 machines. The writer IP is where ms_Mysql is master and is placed on eth1:#
ms_MySQL has preferred: meissa > tabit > cepheus
Hi Mark,
that’s excellent. Now we’ll have to get Yves’ and your patches in proper shape for upstream, which basically amounts to just a little bit of git mangling. Configuration looks good to me except for one detail: the INFINITY resource stickiness. Can you explain why you think that’s necessary?
Cheers,
Florian
Well tbh the INFINITY was more of a “sigh, I’m playing with this for ages, let’s set it to INF. and see what happens”, i knew someone was going to take notice of it
I’m not a pacemaker guru and still learning, I’ve read some stuff about the resource stickiness, but i still can’t find out how it actually works. I tried 100, 1000 and still ms_MySQL:Master switched back to my preferred server when it came back online. I want it to use the preferred server only when the current one fails. INFINITY seems to do the trick
I know not good practice ..
Now let’s see if my boss accepts my suggestion to attend “Percona Live: MySQL Conference 2012″ so you can explain me IRL
You might want to tell them that early bird registrations are still on, so if they make up their mind quickly it’s costing them less than if they take their time.
Hi Mark,
I am happy to hear it works like a charm. Maybe it is me not knowing enough about keepalived but why are you defining reader_vip on lo? I mean, if you are not using these resources, just don’t define them. I also had issues with the way you wrote the colocation rule for the writer_vip but I was using Pacemaker 1.0.11, maybe it is fine with 1.1.6.
Regards,
Yves
Hi trey85stang,
in order for the reader VIP to move to a slave, the slave need to update the readerOK attribute during a monitor call. Look at the content of you CIB with ‘cibadmin -Q > /tmp/cib.xml’ and check if the readerOK attributes are defined. If not, you’ll need to investigate why. A good way to get debuging info is to create the file /tmp/mysql.ocf.ra.debug/log with “mkdir -p /tmp/mysql.ocf.ra.debug; touch /tmp/mysql.ocf.ra.debug/log”. That’s very verbose, check for the monitor calls. Once done, delete the “log” file to stop the logging.
Regards,
Yves
Well, I use keepalived with Direct Routing for loadbalancing. We setup a vip (192.168.5.141) in the config, and attach 3 real servers to it (.74, .95 and .96). Now when there is a request on .141 it goes to the loadbalancer, he picks one of the real servers that is available, rewrites the mac address in the packet and sends it to the real server. I put the .141 VIP on the local interface so the kernel accepts this packet as being destined for him. It’s a common way to use Keepalived with DR. For MySQL you might want to use NAT, but DR is about 50x faster.
You are right about my setup, in my setup I could remove the readVIP and just set it as a default in my network config.
Let’s say I want to change my setup to have only the reader VIP up on slaves.
But that’s not fun!
I can change the clone rule with clone-max=2 and set a co location “dislike” for readervip on a master server, something like:
colocation dislike-reader-on-master -inf: reader_vip ms_MySQL:Master
so it’ll skip the master and set the other 2 nodes with a readervip (more of a hypothesis, but i am going to test it
)
Again, the whole reader VIP can be removed from the config in my particular setup, but I’d thought it was a nice exercise for me to implement the reader VIP resource in the cluster as well.
I moved the writer VIP resource assigning from the location constraint writerOK to a colocation. With your initial config I often had the writer VIP being placed on a server that was a slave, so I didn’t really trust the code behind it. (sorry
) So I moved it, so I could control it myself with the colocation “Set p_writer_vip where ms_MySQL is Master role” simple, no background code needed, just pacemaker.
I’m running everything from squeeze-backport: Pacemaker 1.1.6-2
Hi MarkG,
you still have some ground to cover, you don’t manage VIPs and replication right now. Please grab the new mysql agent and use a configuration like ours, we would then be able to work together to bring MHA in the picture to improve master promotion.
Regards,
Yves
Hi MarkS,
so, the readerVIP are independent of Pacemaker…. what if the slave is lagging or replication broke. The whole point of this config is to react to these events.
Regards,
Yves
I’m already using your latest version of the RA, I only added the mysql_options code in the update_data_master_status function (see few replies ago) so the replication data sets filled correctly in the cib.
You’re correct on when a slave is behind or broken, it shouldn’t carry a readVIP from that point on until it’s not running behind anymore, i haven’t covered that in my setup. hmm ….
I’ll read the RA code again next week, maybe it’ll get more clear to me, .. for me to understand it a bit more, can you answer me these questions:
Are you checking the health of a slave in the monitor function and if so depending on the results of the monitoring set the readerOK and writerOK?
readerOK = 0 means that a server is not allowed to run a reader_vip or that it doesn’t have a reader_vip? (same for writer i assume)
Where is decided on who get’s to have the writerVIP?
Hi MarkS,
sorry the RA version was for MarkG. Yes, in the monitor function, the state of the slave is monitor and the value of the readerOK attribute adjusted if needed. The writerOK is also set in the monitor function based on the status of the read_only variables on MySQL and also in promote and demote function. This is to avoid having a writer_vip present when the database is not ready for it. There are some required steps that are needed to enforce data integrity and at some point in time and for a short duration, no writer_vip can be present.
Regards,
Yves
Hi there!
thanks to Florian, I switched my platform to Ubuntu 11.10, which does have more recent packages of pacemaker and resource_agents.
I have two issues with the RA:
1. I used Yves mysql-RA and fixed it with Mark St.’s patch. At first glance, everything looks fine in crm_mon:
p_writer_vip (ocf::heartbeat:IPaddr2): Started ubuntu-cluster01
Master/Slave Set: ms_MySQL [p_mysql]
Masters: [ ubuntu-cluster01 ]
Slaves: [ ubuntu-cluster02 ]
Still, the replication never starts! The crm shows:
property $id=”mysql_replication” replication_info=”||”
I clearly statet replication_user=”repl” replication_passwd=”7la4epa99″ in my config, still “show slave status” gives the following output:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: ubuntu-cluster01
Master_User: test
Where does this user “test” come from?
2. If by any odd chance the replication crashes (for example, because that’s what I did, if you create a database just on the master node and then create a new table for that database), the crm get no note of that. That’s bad, because everything looks fine in crm_mon, but there’s no actual replicatin going on any more. Shouldn’t that be something the RA report for slave monitoring?
Cheers,
Andreas
PS: I’m using…
pacemaker-1.1.5-0ubuntu1
resource-agents-1:3.9.2-0ubuntu1
Hi Andreas,
I see the problem in update_data_master_status, the variables $mysql_options is not set correctly. It works by accident right now if root have access to localhost with no password. I’ll change the code, run some basic test and send a patch.
Regards,
Yves, Tnks, good to know I’m on the same path just a bit behind. I need to study Pacemaker configuration. What should I read.
Also, What do you think of using Linux Cluster Management Console (LCMC) to manage Pacemaker.
Mark, for an intro to a baseline Pacemaker configuration, perhaps you’d like to take a look at http://youtu.be/3GoT36cK6os (an LCA2012 tutorial).
Hi,
did some cleanup following MarkS patch, pushed on git here:
git://github.com/y-trudeau/resource-agents.git
or
https://y-trudeau@github.com/y-trudeau/resource-agents.git
Nothing major changed, just $mysql_option cleanup.
Hi!
Strange… with the “old” RA (patch from MarkS applied manually) I got from ocf-tester:
Beginning tests for /usr/lib/ocf/resource.d/heartbeat/mysql…
* Your agent does not support the reload action (optional)
/usr/lib/ocf/resource.d/heartbeat/mysql passed all tests
While, with the latest version you pushed to githup i receive:
Beginning tests for /usr/lib/ocf/resource.d/heartbeat/mysql…
: not foundf/resource.d/heartbeat/mysql: 53:
: not foundf/resource.d/heartbeat/mysql: 56:
.: 58: Can’t open /usr/lib/ocf/lib/heartbeat/ocf-shellfuncs
* rc=127: Your agent has too restrictive permissions: should be 755
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 53: $’\r’: Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 56: $’\r’: Kommando nicht gefunden.
: Datei oder Verzeichnis nicht gefunden: Zeile 58: /usr/lib/ocf/lib/heartbeat/ocf-shellfuncs
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 59: $’\r’: Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 61: $’\r’: Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 124: Syntaxfehler beim unerwarteten Wort `$’{\r”
‘usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 124: `usage() {
-:1: parser error : Document is empty
^
-:1: parser error : Start tag expected, ‘<' not found
^
I/O error : Invalid seek
* rc=1: Your agent produces meta-data which does not conform to ra-api-1.dtd
* rc=2: The meta-data action cannot fail and must return 0
* rc=2: Validation failed. Did you supply enough options with -o ?
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 53: $'\r': Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 56: $'\r': Kommando nicht gefunden.
: Datei oder Verzeichnis nicht gefunden: Zeile 58: /usr/lib/ocf/lib/heartbeat/ocf-shellfuncs
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 59: $'\r': Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 61: $'\r': Kommando nicht gefunden.
/usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 124: Syntaxfehler beim unerwarteten Wort `$'{\r''
'usr/lib/ocf/resource.d/heartbeat/mysql: Zeile 124: `usage() {
Aborting tests
Doesn't look to good, does it?
Cheers,
Andreas
Andreas, I just pulled the lastest RA from Yves’ git and it works without a problem, so the RA on github is fine.
I suggest you try and download it again, try:
wget https://raw.github.com/y-trudeau/resource-agents/master/heartbeat/mysql
chmod +x mysql
Hi again *sigh*,
OK, I downloaded the RA via wget instead of the whole repo via git and now it somehow “works” again (that is: I don’t see any errors in crm_mon and host02 is displayed as slave and host01 as master.
Still: host01 never really becomes slave, the command “START SLAVE” is never executed! There where no errors in the logfile, just one warning (on both nodes):
WARNING: MyRA: Attempted to unset the replication master on an instance that is not configured as a replication slave
(I put a “MyRA in front of every log message, to make them easier to grep from the logs).
After adding some further debug messages, I found out, that the function set_master() is not executed at any time (I never found the output “Changing MySQL configuration…” anywhere in the logs).
It seems, the RA never get’s past this if-construct in line 1027:
if [ "$master_host" -a "$master_host" != `uname -n` ]; then
The reason for that is, that the value of $master_host is empty and that’s (obviously) because $OCF_RESKEY_CRM_meta_notify_master_uname is not set. That’s probably related to this output from crm configure show:
property $id=”mysql_replication” \
replication_info=”|mysql-bin.000048|106″
Any idea, why that happens? And any further ideas, why this happens and STILL everything looks allright in crm_mon? Shouldn’t we get some errors, if the function set_master is never executed?
Cheers,
Andreas
PS: MarkS, would you please post (or send to me via mail) your (working) setup, so that I can duplicate it? I need:
- OS and kernel version
- package-version at least of mysql, pacemaker and resource-agents
- output of crm configure show (if possible, ony the user-generated stuff and not what’s put in there by the crm)
- output of grep -v “#” /etc/[mysql]/my.cnf | grep -v “^$”
Thanks a lot!
Hi Andreas,
you are missing the IP of the master, have you added the IP attributes in the nodes section? Like this:
node testvirtbox1 \
attributes IP=”10.2.2.160″
node testvirtbox2 \
attributes IP=”10.2.2.161″
node testvirtbox3 \
attributes IP=”10.2.2.162″
This is from where the IP to use for the master role comes from. In crm_mon, since we don’t evict slaves, the only impact of not having replication running is to have the readerOK attribute set to 0.
Regards,
Yves
Hi Yves,
thank’s a lot. Sometimes it’s the small details…
The “attributes IP” weren’t set for the nodes. No it looks better – somehow. The replication_info is set:
replication_info=”10.20.0.21|mysql-bin.000056|106″
Still, on the slave node, I find no slave configuration:
mysql -u repl -p7la4epa99 -e’show slave statusG’ returns an empty set and the master status differs:
*************************** 1. row ***************************
File: mysql-bin.000050
Position: 106
Binlog_Do_DB:
Binlog_Ignore_DB:
And again, the function “set_master” is never used and master_host stays empty.
Any further ideas?
Cheers,
Andreas
PS: Find my whole config below:
node ubuntu-cluster03 \n attributes IP=”10.20.0.21″
node ubuntu-cluster04 \n attributes IP=”10.20.0.22″
primitive p_mysql ocf:heartbeat:mysql \n params config=”/etc/mysql/my.cnf” pid=”/var/run/mysqld/mysqld.pid” socket=”/var/run/mysqld/mysqld.sock” replication_user=”repl” replication_passwd=”7la4epa99″ max_slave_lag=”15″ evict_outdated_slaves=”false” binary=”/usr/sbin/mysqld” test_user=”root” test_passwd=”r00tp877″ \n op monitor interval=”20s” role=”Master” OCF_CHECK_LEVEL=”1″ \n op monitor interval=”30s” role=”Slave” OCF_CHECK_LEVEL=”1″
primitive p_writer_vip ocf:heartbeat:IPaddr2 \n params ip=”10.20.0.20″ cidr_netmask=”16″ nic=”eth0:0″
ms ms_MySQL p_mysql \n meta master-max=”1″ master-node-max=”1″ clone-max=”2″ clone-node-max=”1″ target-role=”Master”
location pref-master-1 ms_MySQL 100: ubuntu-cluster03
location pref-master-2 ms_MySQL 50: ubuntu-cluster04
colocation writer-on-master inf: p_writer_vip ms_MySQL:Master
property $id=”cib-bootstrap-options” \n dc-version=”1.1.5-01e86afaaa6d4a8c4836f68df80ababd6ca3902f” \n cluster-infrastructure=”openais” \n expected-quorum-votes=”2″ \n stonith-enabled=”false” \n no-quorum-policy=”ignore” \n default-resource-stickiness=”1000″ \n last-lrm-refresh=”1328620089″
property $id=”mysql_replication” \n replication_info=”10.20.0.21|mysql-bin.000056|106″
Hi,
I think this has a too high traffic for blog post comments. I suggest we moved to the pacemaker mailing list, pacemaker@oss.clusterlabs.org. Andreas, I’ll reply to your email there, I see you are already registered there.
Regards,
Yves
Andreas,
You’ve set the wrong nic:
primitive p_writer_vip ocf:heartbeat:IPaddr2 params ip=”10.20.0.20″ cidr_netmask=”16″ nic=”eth0:0″
should be:
primitive p_writer_vip ocf:heartbeat:IPaddr2 params ip=”10.20.0.20″ cidr_netmask=”16″ nic=”eth0″
Pacemaker will automatically decide if it should be 0:0, 0:1 etc ..
I’m making an entry on my blog how I set this thing up. I’ll post it here when I’m done with it.
Cheers,
Mark
Hi Andreas,
the change master is done in the notify events but you forgot to include ‘notify=”true”‘ in the ms_MySQL declaration so the notify events are not generated. Please continue the discussion on pacemaker@oss.clusterlabs.org, I know I haven’t replied on the list to your emails, I’ll try to be more diligent know, although I’ll be out for 5 days starting Friday.
Regards,
Yves
Yves and Mark S.
Mark S. Do you have you resources ordered? I update mysql from github and stuff started working. However, when I add a dependency it thinks the slave is not running. Here is what I’m adding.
colocation col_ms_mysql_failover-ip inf: ms_mysql failover-ip
order ord_failover-ip_ms_mysql inf: failover-ip ms_mysql
Here is my crm config before the ordering.
node db1.grennan.com \
attributes IP=”192.168.2.201″
node db2.grennan.com \
attributes IP=”192.168.2.202″
primitive failover-ip ocf:heartbeat:IPaddr \
params ip=”192.168.2.200″ \
operations $id=”failover-ip-operations” \
op monitor start-delay=”0″ interval=”2″ \
meta target-role=”started”
primitive p_mysql ocf:heartbeat:mysql \
params binary=”/usr/bin/mysqld_safe” pid=”/var/run/mysqld/mysqld.pid” socket=”/data/mysql/mysql.sock” replication_user=”root” replication_passwd=”P@ssw0rd” \
meta target-role=”started”
ms ms_mysql p_mysql \
meta clone-max=”2″
property $id=”cib-bootstrap-options” \
expected-quorum-votes=”2″ \
stonith-enabled=”false” \
dc-version=”1.1.6-3.el6-a02c0f19a00c1eb2527ad38f146ebc0834814558″ \
no-quorum-policy=”ignore” \
cluster-infrastructure=”openais” \
last-lrm-refresh=”1328725415″
property $id=”mysql_replication” \
replication_info=”192.168.2.201|mysql-bin.000741|107″
Hi MarkG,
you too are missing the ‘notify=”true”‘ so you config cannot work, you are also missing the monitor operations for p_mysql, another show stopper. Please post to pacemaker@oss.clusterlabs.org, the comment stream is becoming huge.
Regards,
Yves
I have joined the clusterlabs mailing list. I just wanted you to know I have it working with your resource agent out of github. It is your work right?
I blogged a video example of it on mysqlfanboy.com. And I’ll be blogging my Step-by-Step document in the next week after some testing.
It’s been pretty quiet here for a while now
I’ve to add something I ran into while I had some maintenance planned on the cluster.
What I wanted to do is add another server to the cluster.
The “replication_info” config setting is only changed when a new master is elected. So how to add a new slave?
Create a new slave by dumping/binary copying or any other way your normally prepare a slave. Now change the “replication_info” manually in corosync to the new binlog and logpos position from when you took the dump/copy.
From within corosync itself you’ll get an error when you try to edit it, so use the command that the mysql RA uses, which is:
crm_attribute –type crm_config –name replication_info -s mysql_replication -v “||”
Now start corosync on your slave and let it join the cluster, it will now correctly start slaving from the position you made the snapshot.
On my boxs with MySQL 5.5 (Ubuntu Server 12.04 LTS) the pacemaker mysql plugin (ocf:heartbeat:mysql) doesn’t work, because since mysql 5.5 the statement “CHANGE MASTER TO MASTER_HOST=” ” will make an error and stop this script. As I can see, Yves fixed this in his the last version on gitub.
But in my MySQL – installation a “SHOW SLAVE STATUS” will ALWAYS produce a completely output. Maybe not initial, but if you use the server instance one time as as a slave, it will do so. In the function is_slave() the test for an output to a tmpfile, not for the content “Slave_SQL_Running: Yes” is used for to say that this server is a slave, but later the test of “Slave_SQL_Running: Yes” fails, because the server is not running as a slave. So the 2nd start of pacemaker would be fail. Yesterday, I fix this up very dirty and it works.