Having recently added some mysql replication slaves I wanted to be sure that the slaves are always running. In order to do this I’ve selected Monit though you could do this several different ways.
What I’ve done is put a quick little bash script together that runs every minute via cron.
What this does is grabs the Slave_IQ_Running and Slave_SQL_Running from a SHOW SLAVE STATUS and if they are both Yes indicating the replication is running smoothly then it touches the /opt/slave_running file.
This is my simple monit script, I drop this into /etc/monit/conf.d on an ubuntu system and it gets included by default. Just restart monit. Monit runs every 2min and if the /opt/slave_running is a couple minutes out of date I’m alerted to take a look.
I’ve seen this idea around on other blogs using python or ruby so I can’t take credit for the idea, just dropping in my notes for how I did it.
Today an error appeared in my inbox from automysqlbackup indicating there was an issue with the backups being run from a slave machine that is used strictly for running backups.
mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when using LOCK TABLES
Unfortunately this was not overly useful.
I tracked down the particular database that was causing problems and received a new error message that was getting a lot more useful:
mysqldump: Couldn't execute 'show create table `REPORT`': SHOW VIEW command denied to user 'backup'@'localhost' for table 'REPORT' (1142)
I’m no expert but what this is roughly saying is that the ‘backup’ user @ ‘localhost’ does not have the ‘SHOW VIEW’ privileges and because ‘REPORT’ is a view and not an actual table and my backup user typically only has ‘SELECT’ this is where we were failing. Seems simple enough assign the user the correct privileges and we should be good to go.
Not so fast. I ran into one more error attempting to access this view:
mysqldump: Got error: 1449: The user specified as a definer('root'@'%') does not exist
Once again I’m far from an export on this but my understanding is that the view was created or defined by a particular user known as the definer and because this is a slave machine the user that created the view was only on the master server (as the mysql db is not replicated over) and not on the slave machine therefore the view was inaccessible.
I created the correct user (exact same username @ hostname) on the slave machine and now our backup user with correct privileges was able to read the view and perform the backup as expected.