Detecting slave replication lag without needing SUPER privilege
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?
Subject
Views
Written By
Posted
Detecting slave replication lag without needing SUPER privilege
1122
February 09, 2017 11:42AM
653
March 10, 2017 06:10AM
528
March 10, 2017 10:09AM
614
March 17, 2017 03:09AM
463
March 17, 2017 05:20AM
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.