PHP Websockets Mysql Pub/Sub

Question

I have this "crazy" project starting, the idea behind it is quite clear: There is some software that writes to MySQL database. The interval between queries are 1 second. Now I need and web interface which loads those database records, and continues to show new records, when they happen. The technologies I am supposed to use are PHP and HTML5 WebSockets. I'v found this nice library Ratchet which I think fits my needs, however there's one problem, I am not sure how to notify PHP script, or send a message to running PHP WebSockets server when the MySQL query occurs. Now I could use Comet and send request for database record every second, but then it beats the WebSokets which I am supposed to use. So what I really need is MySQL Pub/Sub system. I'v read of MySQL triggers but I see that it possess some security risks, and thought the security in this case isn't a real concern since the system will be isolated in a VPN and only few specific people will be using it, I still would like to address every possible issue and do everything in a right way.
Then there is MySQL proxy, of which I have no knowledge, but if it could help me achieve my goal, I would very much consider using it. So in short the question is how can I notify or run PHP script when MySQL query occurs?

Answer 1

I would separate the issues a bit.

You definitely need some sort of pub/sub system. It's my understanding that Redis can act as one, but I've never used it for this and can't make a specific recommendation as to what system to use. In any case, I wouldn't attach it directly to your database. There are certainly database operations you need to do on your database for maintenance purposes and you don't want it flushing out a million rows to your clients because it's based on a trigger. Pick your pub/sub system independent of what you're doing client-side and what you're doing with your database. The deciding factors should be how it interacts with your server-side languages (PHP in this case).

Now that your pub/sub is out of the way, I would build an API server or ingest system of sorts that takes data in from wherever these transactions are coming from. It will handle these and publish messages as well as inserting them into the database at the same time.

Next, you need a way to get that to clients. Web Sockets are a good choice, as you have discovered. You can do this in PHP or anything really. I prefer Node.js with Socket.IO which provides a nice fallback to long-polling JSON (among others) for clients that don't suppot Web Sockets. Whatever you choose here needs to listen for messages on your pub/sub and send the right data for the client (likely stripping out some of the information that was published that isn't immediately needed client-side).