How to do a page refresh on an event in MySQL?

Question

MySQL

Given:
An application that can only write to mySQL
MySQL Server 5.1
Web server (apache+nginx+php) + memcached

The application actively writes data to mySQL, inserts up to 3 records per second.

Mysql uses a trigger to store the updated data in mecache, where it is stored for the last 24 hours.

It is necessary that when updating a record in a table, the updated record is displayed on the page.

How to initiate the transfer of data from the Web server to the client (s) there is an understanding - this is either a comet or an nginx module or a self-written php script

Question: how to write data to a channel from Mysql or memcache or notify the web server that the data in the table has been added / changed?

Answer 1

1) Q4M
2) MySQL Proxy
3) Binlog Monitoring
Not?

Answer 2

Perhaps this solution will help you:
- You need to write a UDF function for MySQL.
The function is written in C and can be called from a trigger:
dev.mysql.com/doc/refman/5.5/en/adding-udf.html
dev.mysql.com/doc/refman/5.0/en/faqs-triggers.html# qandaitem-B-5-1-10 Let's
conditionally call it push_messasge(...)
The function will need to be called from a MySQL trigger
— You need to implement a message queue using the shared memory mechanism
The push_messasge function will write a message there.
Please note that the implementation must be done lock free, so as not to cause a drop in
mysql performance
- It is necessary to implement an external program that will read messages from the shared memory queue and write them
to the push server. As a push server, you can take dklab.ru/lib/dklab_realplexor/
The program can be written in python (dklab_realplexor has pythin api implemented:
github.com/DmitryKoterov/dklab_realplexor/tree/master/api/python
- Push server, in turn, will transmit JS messages to clients in the browser.

Answer 3

Here it is best to use nginx_http_push_module. Read about this module - everything is clear there.
It is better not to use self-written php scripts with longpooling, because there are problems with sessions.

Answer 4

And unless it is impossible in function. which puts a record in the database, add a call to all the data from this database, and use Ajax to load it on the page?
We get the following scheme:
- check the possibility of writing to the database
- if OK, put the entry into the database and immediately query the data we need from the database
- put the received data into a variable, using Ajax load it on the page we need, we
will get real-time tracking of the state of the database . You can additionally fasten a handler that will do something if there is no possibility of writing to the database (some kind of error, the serv has crashed, etc.).

Answer 5

Google lib_mysqludf_sys, it should help.

Answer 6

You can, as mentioned above, write a UDF or take a ready one www.mysqludf.org/lib_mysqludf_sys/index.php and use it to call an external program/script on a trigger