MySQL Forums
Forum List  »  Replication

Detecting slave replication lag without needing SUPER privilege
Posted by: Carsten Dreesbach
Date: February 09, 2017 11:42AM

We have slave servers set up and are using them (amongst other things) for running reporting queries against them (maybe not ideal, but it's what we've got for now, so go with me on this one... ;] ). Sometimes the slave gets behind on replication if there was a particularly large import on the master (we regularly import new data on a weekly or even daily basis). When this happens, the report generated obviously could be out of date, and that becomes useless for our end users who are depending on complete information (the reports only run once a week).

I'd like to check the replication status of the slave (https://dev.mysql.com/doc/refman/5.6/en/replication-administration-status.html) before running the report to make sure that this is not an issue - specifically "Seconds_Behind_Master" is the main item of interest. However, I don't want to give the user ID running the report (these are automated) the SUPER privilege, which is needed to be able to run the SHOW SLAVE STATUS command, which is the only place I know of where to get this information.

I've read that starting with MySQL 5.6 I can put some of the replication info into the mysql.slave_master_info table (https://dev.mysql.com/doc/refman/5.6/en/slave-logs.html), but this does not contain the Seconds_Behind_Master information (https://dev.mysql.com/doc/refman/5.6/en/slave-logs-status.html - kinda makes sense, you don't wanna write that to the DB every few seconds, I guess).

So, do I have any other options to get information on whether the slave is up to date, or do I have to give my reporting user SUPER privileges (ew!) in order to do this?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.