Multi-state MySQL master/slave pacemaker resource fails to launch on cluster nodes

Solution

Thanks to the folks that investigated with me, I was able to find the solution to my problem and I do now have a working setup. If you feel brave enough, you can read the comments on the original question, but here is a summary of the steps that helped me solve my issue.

Read the source

First thing to do when setting up HA resources, will sound typical, but RTFM. No seriously, learn how the software you're planning to use works. In that particular case, my first mistake was not to read and understand carefully enough how the resource agent (RA) works. Since I was using the mysql RA provided by Heartbeat, the RA source script was available on ClusterLabs' resource-agents GitHub repo.

Do not forget to read the source of included files!

Make sure your software is up-to-date

Was not clearly identified as an issue in my particular case, but as @gf_ & @remote mind suggested, it is always a good thing to have a version of your RA that works with your software version.

Fill-in the damn params

Number one rule in HA: do not rely on default values.

That's not true, sometimes you can, but honestly, if I had provided every optional parameter that I could to the RA, I would have fixed my issue way quicker.

This is actually where the Read the source part is important, since it will allow you to truly understand why there are parameters needed. However, since they are often only briefly described, you may need to go further than the meta-data and find where are the parameters used. In my case, the thing did not work for several reasons:

• I did not provide the socket path, and the default one for the script did not match the default one for my system (Debian 8).
• I did not provide test_user, test_passwd: these were present in the meta-data but I thought that I did not needed this. After I decided to look what it was used for, I simply found out that these parameters were used to perform a simple select count(*) on the database. And since the defaults are set to use user root without password, it did not work in my case (because on my databases, root needs a password to connect the database). This particular step prevented the RA from performing the check if the current node was a slave or not.
• Some other params were also missing, and I knew I needed them only after I discovered where the damn default settings were hidden.

Final word

Again, thanks a lot to @gf_ for taking the time to investigate with me and provide leads in order to debug my setup.

Good HA setups are not that easy to achieve (especially when starting from scratch), but if well configured can be really powerful and provide peace of mind.

Note: peace of mind not guaranteed ;)