Okay, so I’ve been messing around with this thing called MHA, trying to get it to work for my database setup. It’s supposed to help with failover, making sure things keep running if my main database server takes a dive. Here’s how the whole messy process went down.
Getting Started
First, I had to get all the right software. I grabbed the MHA packages – there’s a manager and a node one. The manager is like the brains of the operation, and the nodes run on each database server. I used yum because that’s what I’m used to on my CentOS boxes. It went something like:
yum install mha4mysql-manageryum install mha4mysql-node
I made sure to install the node package on all my database servers – the master and all the slaves. I just copy-pasted those commands. Lazy, I know, but effective.
Setting up the SSH Keys
Next up, I learned that MHA needs to talk to all the servers without me typing in passwords all the time. So, SSH keys. I generated a key pair on the manager server using ssh-keygen. No passphrase, just hit enter a bunch of times. Then, the annoying part: I had to copy the public key (the one ending in .pub) to the authorized_keys file in the .ssh directory of the root user on every other server. Yeah, tedious. There’s probably a script for this, but I did it the old-fashioned way.
Configuring the MHA Manager
Time for the configuration file. I created a new file, /etc/mha/* (you can name it whatever, really). Inside, I had to tell MHA about my database setup. This is where things got a bit…involved. I listed out all my servers, their IP addresses, MySQL usernames, passwords, ports, and all that jazz. It looked something like this:
[server default]
mysql_user=root
mysql_password=my_super_secret_password
[server1]
hostname=192.168.1.10
…and so on for server2, server3…
I also had to specify which server was the master, and which ones were candidates to become the new master if things went south.
Testing, Testing, 1, 2, 3
Before I let this thing loose on my production databases, I wanted to make sure it actually worked. MHA comes with some handy tools for this. First, I ran masterha_check_ssh. This checks if the manager can SSH into all the nodes without a password. If this fails, you go back to the SSH key step and figure out what you messed up (probably a typo, in my experience). I spent at lease half hour to solve this ssh issue.
Then, masterha_check_repl. This one checks if MySQL replication is working correctly. It spits out a bunch of info, and you’re looking for any errors. If you see errors, fix your replication before you even think about using MHA. That’s just asking for trouble.
Starting the MHA Manager
If all the checks passed (finally!), I started the manager with masterha_manager --conf=/etc/mha/*. This starts the MHA process in the background, watching over your databases. You can check its status with masterha_check_status. It should say something like “MHA Manager is running”. If not, well, time to dig through the logs (usually in /var/log/mha).
Simulating a Failover (the Fun Part)
Now for the moment of truth. I intentionally crashed my master database server (on a test environment, of course!). I just stopped the MySQL service. Then, I watched the MHA logs. It should detect the failure, pick a new master, and do all the fancy stuff to make sure everything keeps working. It’s pretty cool to see it in action, like magic, but with more error messages.
After the dust settled, I checked the new master, made sure my applications were still connecting, and patted myself on the back. It actually worked! I’m pretty confident that MHA can save me to avoid the big disaster.
There were bumps along the way, of course. Lots of Googling, reading documentation, and swearing at my screen. But in the end, I got it working.
