MySQL Replication

General Discussion
1

Hello Forum,

We are setting up ignition critical with mysql multimaster replication.
My question here is would it be possible to monitor the replication within a project?
Somehow one could have a plugin in ignition that could be used to display the replication status on a page.
Has any one done this? Any help would be appreciated.

2

We we had to monitor a replication to ensure it wasn’t falling behind, we created a table with one record in the source db and a process then updated the time in that record.

In the replicated db we polled that record and compared it with the current time. If it was to old we sent alerts to the sysadmin.

You could do the same thing with gateway scripts.

3

Hi Robert,

That sounds great, any more tips on were to get started as i am not that familiar with the scripting for SQL. A how to would be great.

Thanks

4

Well, you can do a simple SQL query to check the MySQL replication status. On the MySQL shell, run:

SHOW SLAVE STATUS \G

If one of Slave_IO_Running or Slave_SQL_Running is set to No, then the replication is broken:

*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 1.2.3.4 Master_User: slave_user Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.001079 Read_Master_Log_Pos: 269214454 Relay_Log_File: slave-relay.000130 Relay_Log_Pos: 100125935 Relay_Master_Log_File: mysql-bin.001079 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: mydb Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1146 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate SET thread.views = thread.views + aggregate.views WHERE thread.threadid = aggregate.threadid' Skip_Counter: 0 Exec_Master_Log_Pos: 203015142 Relay_Log_Space: 166325247 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: NULL 1 row in set (0.00 sec)

5

Thanks Travis. It’s being a few years since I’ve played with MySql.

Travis’s is the correct way to know if something is broken. We didn’t use it because it doesn’t tell you if it’s keeping up. In our case we had a time critical SLA and an application that tending to flood the replication link.

On the master database box we created a cron job that ran every minute, connected to the db and ran:
update HEARTBEAT set LASTTIME = NOW();

On the slave database box the cron job ran something like:
select NOW() - LASTTIME from HEARTBEAT;

If this time was greater then the limit raise the alarm.

HEARTBEAT is a table with one record
LASTTIME is a timestamp column in that record
NOW() is the function to return the current timestamp.

Like I said, it’s being a few years so the SQL above my have to be tweaked.

6

Hi Everyone,

Thanks for all your help, great stuff. I am sorry if this appears to be a stupid question but i am all new to this.
How could i get this information into ignition and display it?
Lets say i want to display the information on a client screen:
Master_Host: ‘IPaddress’
Slave_IO_Running: Yes
Slave_SQL_Running:Yes

Furthermore generate an alarm if Slave IO or Slave SQL are not running.

Again Thanks everyone for your help.

7

The general idea would be to run a query that got you the status you needed, and then put that query into a SQL Query binding on a label in a window. If you can’t formulate a query that returns the exact result you want, you may need to write a Python script to manipulate some query results to format the information you want, and then stick it into a Label in the script.

8

Hi Gents,

When i use the SQL Query Browser in Ignition and use the Command show slave status, with or with out the delimiter \G i don’t get a result back at all. Resultset 1 = Rows -1
However if i use the same command in the mysql command prompt in windows i do get a result as per travis.cox’s post.

Were do i go from here.

Any Help on this one?

9

Hi gents,

how would i run a query using script in ignition that would give me the output:

       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes

of the SQL command
SHOW SLAVE STATUS

10

Hi Gents,

When i use the expression on a button:

Table = system.db.runQuery("SHOW SLAVE STATUS")

In the Table i get the correct data back as can be seen in the picture:

989×319 3.54 KB

MY question is, how do i extract the fields:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

out of the result set that is generated out of the above expression.

Furthermore, how would i get those two Fields into a Database Table called “MySQLRep” and update the table periodically, say every 1 minute?

I presume i use a gateway timer script. However as i said i am stuck on extracting the data out of the result set. T
his would then allow me to view the data on the client and generate an alarm in Ignition etc.

Thanks again everyone for your help.
Regards
Sascha

11

You can add two dynamic properties to your table (Slave_IO_Running and Slave_SQL_Running), and then use expression bindings to grab the data you want for each. ie:{Root Container.Table.data}[0,"Slave_IO_Running"]To avoid any errors caused by the table being empty, you will have to check the dataset length first.if(len({Root Container.Table.data})>0, {Root Container.Table.data}[0,"Slave_IO_Running"], "n/a") Once you have the data in these two properties, you can make a propertyChange event script that writes these values back to your database whenever they change.

12

Hi Colby,
i need this to be able to update when there is no client active, as this application may not be viewed all the time.
Will i be able to do this with the above in mind?

13

For Robert’s idea, you’d need a client running. You could do a similar thing in gateway-side scripting, however, using the system.db.* functions.

14

Hi Gents,

MySQL Replication monitoring script:
It gets the querry of SHOW SLAVE STATUS, then finds the COLUMS required for my monitoring database table and inserts the values into the table "mysqlreplication " with a current timestamp for later analysing in the client/gateway.

hope this helps others

def mySQLReplicationStatus():
  import system
  #variables
  dataset = system.db.runQuery("SHOW SLAVE STATUS","YOUDATABASECONNECTIONNAME") 
  #Check that there is records in the dataset
  if len(dataset)>0 :
   for rows in range(len(dataset)):
       #This will get the words as a list of strings
       Column = dataset[rows]
       #Check if there is a record in this row
       if len(Column) > 0:
          #print "Master_Host: Slave_IO_Running: Slave_SQL_Running: Seconds_Behind_Master" 
          #print "%s \t %s \t\t %s \t\t %s" % (Column["Master_Host"],Column["Slave_IO_Running"],Column["Slave_SQL_Running"],Column["Seconds_Behind_Master"])
          #for cols in range(len(Column)):
          #    print "Col %s: %s" % (cols,Column[cols])
          #Write Values to DB
          system.db.runUpdateQuery("INSERT INTO mysqlreplication (Master_Host,Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master,t_stamp) VALUES (\'%s\',\'%s\',\'%s\',%s,NOW())" % (Column["Master_Host"],Column["Slave_IO_Running"],Column["Slave_SQL_Running"],Column["Seconds_Behind_Master"]))
  return 1

Regards
Sascha

1 Like