Re: On fighting with master-slave replication lag
Hi Xu, The check_mysql plugin is part of the standard plugins package (see the downloads page at http://www.nagios.org/download/download.php.) At a very minimum this plugin will check that the slave's SQL thread is running and compare the number of seconds it is behind the master, allowing you to set a threshold on how long is a warning, and how long is a failure (error.) I would hazard a guess that it uses the output of show slave status; to gather its information. Andy xufeng wrote: Hi All On http://www.nagiosexchange.org/cgi-bin/search.cgi?d=1query=check_mysqlGo=Go I found some useful scripts to do the work and there are some scripts that do the work as you have described. The most important lesson I have learned is to know how theses various tools achieve the goals (not only the tools themselves). Thank you all for your great help. Yours Xu Feng -Original Message- From: xufeng [mailto:xuf...@yuanjie.net] Sent: 2008年12月25日 10:13 To: 'Baron Schwartz'; 'Jake Maul'; claudio.na...@gmail.com; andy-li...@networkmail.eu; claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: On fighting with master-slave replication lag -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: 2008年12月24日 22:06 To: Jake Maul Cc: mysql@lists.mysql.com Subject: Re: On fighting with master-slave replication lag On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. I have read some stuff on http://www.maatkit.org/doc/mk-heartbeat.html and am interested in this tool. I guess in reality the mk-heartbeat tool checks the output of show master status on the master with focus on the File and Position fileds. mysql show master status \G *** 1. row *** File: mysql-bin.04 Position: 3037 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) And it can give the delay alert or calculating the delay by checking the output of show slave status on the slave with the same focus on the Master_Log_File and Exec_Master_Log_Pos fileds.It compares the two fields from slave with the two fields from master. mysql show slave status \G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.15.120 Master_User: replication1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.04 Read_Master_Log_Pos: 3037 Relay_Log_File: localhost-relay-bin.49 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test_db1 Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 3037 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) Am I right? Or can you provide some info on the detailed description of mk-heartbeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=xuf...@yuanjie.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:
Re: On fighting with master-slave replication lag
Xu, Slightly off-topic, but you might also want to look at your DNS set up as you're not capable of receiving e-mails: --- This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. The following address(es) failed: xuf...@yuanjie.net all relevant MX records point to non-existent hosts or (invalidly) to IP addresses --- Here's the problem: ;; QUESTION SECTION: ;yuanjie.net. IN MX ;; ANSWER SECTION: yuanjie.net. 3600 IN MX 1 221.130.195.83. 1 221.130.195.83. is not a valid IP address anyway, but MX records must also be the hostname of an existing A record, not an IP address. Regards, Andy Andy Shellam wrote: Hi Xu, The check_mysql plugin is part of the standard plugins package (see the downloads page at http://www.nagios.org/download/download.php.) At a very minimum this plugin will check that the slave's SQL thread is running and compare the number of seconds it is behind the master, allowing you to set a threshold on how long is a warning, and how long is a failure (error.) I would hazard a guess that it uses the output of show slave status; to gather its information. Andy xufeng wrote: Hi All On http://www.nagiosexchange.org/cgi-bin/search.cgi?d=1query=check_mysqlGo=Go I found some useful scripts to do the work and there are some scripts that do the work as you have described. The most important lesson I have learned is to know how theses various tools achieve the goals (not only the tools themselves). Thank you all for your great help. Yours Xu Feng -Original Message- From: xufeng [mailto:xuf...@yuanjie.net] Sent: 2008年12月25日 10:13 To: 'Baron Schwartz'; 'Jake Maul'; claudio.na...@gmail.com; andy-li...@networkmail.eu; claudio.na...@gmail.com Cc: mysql@lists.mysql.com Subject: RE: On fighting with master-slave replication lag -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: 2008年12月24日 22:06 To: Jake Maul Cc: mysql@lists.mysql.com Subject: Re: On fighting with master-slave replication lag On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. I have read some stuff on http://www.maatkit.org/doc/mk-heartbeat.html and am interested in this tool. I guess in reality the mk-heartbeat tool checks the output of show master status on the master with focus on the File and Position fileds. mysql show master status \G *** 1. row *** File: mysql-bin.04 Position: 3037 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) And it can give the delay alert or calculating the delay by checking the output of show slave status on the slave with the same focus on the Master_Log_File and Exec_Master_Log_Pos fileds.It compares the two fields from slave with the two fields from master. mysql show slave status \G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.20.15.120 Master_User: replication1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.04 Read_Master_Log_Pos: 3037 Relay_Log_File: localhost-relay-bin.49 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.04 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test_db1 Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error:
Question on default database for stored functions
Hi According to the MySQL manual, By default, a routine is associated with the default database.When the routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates) However, when I tried accessing a stored routine from another database, I received an error. Output below: mysql USE test mysql DELIMITER // mysql CREATE FUNCTION get_area(radius INT) - RETURNS FLOAT - BEGIN - RETURN PI() * radius * radius; - END - // Query OK, 0 rows affected (0.13 sec) mysql DELIMITER ; mysql USE test2 Database changed mysql select get_area(11); ERROR 1305 (42000): FUNCTION test2.get_area does not exist Can someone tell me what I'm doing wrong? Thanks. Vikram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: On fighting with master-slave replication lag
2008/12/24 xufeng xuf...@yuanjie.net: -Original Message- From: baron.schwa...@gmail.com [mailto:baron.schwa...@gmail.com] On Behalf Of Baron Schwartz Sent: 2008年12月24日 22:06 To: Jake Maul Cc: mysql@lists.mysql.com Subject: Re: On fighting with master-slave replication lag On Wed, Dec 24, 2008 at 2:31 AM, Jake Maul jakem...@gmail.com wrote: Slightly more complicated (and also probably more accurate- the time reported by show slave status is known to be unreliable in some cases) would be a script that inserts a row into a table, then check the slave over and over till it arrives. Or even better, insert 2 values... a timestamp that *you* provide (in a shell script, something like $(date) would work) and a timestamp generated by MySQL assuming the times are syncronized on the master, slave, and the box you're inserting from, when the insert hits the slave it'll generate it's own timestamp, which you can then subtract *your* timestamp from. There's also a tool in maatkit which does replication tracking, although I've not yet used it. Judging by the other tools in that package though, it's probably pretty decent :). It is mk-heartbeat, and it does pretty much what you described, although it's been tweaked to be slightly more complex to suit various real-world scenarios. I have read some stuff on http://www.maatkit.org/doc/mk-heartbeat.html and am interested in this tool. I guess in reality the mk-heartbeat tool checks the output of show master status on the master with focus on the File and Position fileds. If you really read that link, it puzzles me how you could come to that conclusion about the tool. It does no such thing and I think http://www.maatkit.org/doc/mk-heartbeat.html#DESCRIPTION describes that pretty clearly. Let me know if the documentation needs to be clarified.
